Script to generate a select resolving all child tables?-CodePudding

I'm looking for a way to generate a script that would generate an SQL query that would select all child tables columns from a parent table.

Let's say you have a table Class (teacher, room, program) and a table Student (firstname, lastname, age, score, email).

Let's say you want to get a select of all students in Class.

Sure you could write the query manually.

But now imagine you have a complex table with dozens of child tables, how do you do this efficiently/programmatically ?

This is something that all programmers would like to have, no ? I can't believe no one has ever done that.

I understand the answer may depend on the DBMS vendor, I'm personally looking for a solution for Oracle.

Questions that are a bit similar :

And here is an idea to solve this partially : use a tool such as PowerBi or Visual Studio to generate Model from database in ASP.NET MVC. You won't get the SQL query but you will get the data.

CodePudding user response：

You can create your own entity relationship model metadata and write PL/SQL that will traverse it and assemble SQL intelligently. I've done this myself to avoid having to hard-code SQL in my front-end apps. But it is highly complex and involves a lot of coding, far more than can be shared in a forum like this. But to give you the general gist, I have the following metadata tables that describe my model:

sql_statements - associates a logical entity with a primary table, and specifies the PK column.

sql_statement_parents - defines the parent entity and the child attribute used to join to the parent's PK.

sql_attribute_dictionary - lists every available attribute for every statement, the source column, its datatype, plus optional derived column expressions.

attribute_dependencies - used for derived column expressions, specifies which attributes are needed by the derived attribute.

Then you write code that takes a sql_statement name and a list of desired attributes and a set of optional filters, and it builds a list of needed source tables/columns using the data relationships in the metadata, and then using the parent-child relationships recursively builds SQL (using nested query blocks) from the child to whatever parent ancestor(s) it needs to obtain the required columns, intelligently aliasing everything and joining in the write way to be performant. It can then pass back the finished SQL as a REF CURSOR which you can then parse, open and fetch from to get results. It works great for me, but it did take weeks of work to perfect, and that's with decades of experience in SQL and PL/SQL. This is no simple task, but it is doable. And of course there are always complex needs that defy the capabilities of our metadata model, and so for those we end up either creating views or pipeline functions, and registering those in our metadata so that generated SQL can invoke them when needed.

But in the end, however you do it, you will not get away from having to describe your data model in detail so that code can walk it.

CodePudding user response：

You can start with this POC:

select 
    juc.table_name as parent_table, 
    /*
    uc.table_name as child_table, uc.constraint_name, uc.r_constraint_name, 
    juc.constraint_type,
    uccc.column_name as parent_col_name, uccc.position as parent_col_position,
    uccp.column_name as child_col_name, uccp.position as child_col_position,
    */
    'SELECT c.* FROM ' || juc.table_name || ' p JOIN ' || uc.table_name || ' c ON '
    || 
    LISTAGG( 'c.' || uccp.column_name || ' = p.' || uccc.column_name, ' AND ' ) WITHIN GROUP(order by uccc.position)
    as sql
from user_constraints uc
    join user_constraints juc on juc.constraint_name = uc.r_constraint_name 
    join user_cons_columns uccc on uccc.constraint_name = uc.r_constraint_name
    join user_cons_columns uccp on uccp.constraint_name = uc.constraint_name and uccc.position = uccp.position
where uc.constraint_type = 'R'
group by uc.table_name, juc.table_name, uc.constraint_name
;