1

Let's say I have to create tons of different SQL views. Having actual and target table DDLs, is it possible to programmatically automate view creation?

Example: I have 2 table DDLs: 

CREATE TABLE target_ddl (
  invoice_sum INTEGER,
  date date DATE
);

CREATE TABLE actual_ddl (
  amount INTEGER,
  year INTEGER,
  months INTEGER,
  day INTEGER
);

What I need to do is to make actual_ddl data match the target_ddl schema, so obvious answer is view creation: 

CREATE VIEW actual_ddl_as_target_ddl AS
SELECT amount AS invoice_sum, 
DATE(CONCAT_WS('-', year, months, day)) AS date FROM actual_ddl;

I wrote this one manually. Thought I feel like it's possible to write java/python library which will "create SQL views based on actual and target schema". What algorithms/best practices should I look into to achieve it?

Improve this question
3
  • And how would your program know how to create the view? Commented Nov 29, 2019 at 22:51
  • No the "actual DDL" contains insufficient information to establish how the tables should be trabsformed and/or joined. The naming convention in this case makes some suggestion to a human reader as to how the tables can be joined in a natural way, but that relies on a conceptual understanding of the code which the computer does not have (even the unfamiliar human reader can only speculate as to whether a simple join between the dates would be a correct use of the tables). Commented Nov 29, 2019 at 22:54
  • To be more specific: I have a user input: which columns have to be mapped to which. E.g: (year, month, day) to date, OR (datetime) to date. So there is a limited number of possible input column formats. It is definitely possible but requires a lot of wotk. I'm concerned about the complexity of algorithms and whether there are some patterns I might use. Commented Nov 30, 2019 at 7:04

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.