95% of all code makes me cry. The other 5% are white space.
-- "graph" denotes an undirected foreign key reference graph -- for schema "TEST" with graph as ( select c1.table_name t1, c2.table_name t2 from all_constraints c1 join all_constraints c2 on c1.owner = c2.r_owner and c1.constraint_name = c2.r_constraint_name where c1.owner = 'TEST' union all select c2.table_name t1, c1.table_name t2 from all_constraints c1 join all_constraints c2 on c1.owner = c2.r_owner and c1.constraint_name = c2.r_constraint_name where c1.owner = 'TEST' ), -- "paths" are all directed paths within that schema -- as a #-delimited string paths as ( select sys_connect_by_path(t1, '#') || '#' path from graph connect by nocycle prior t1 = t2 ), -- "subgraph" are all those directed paths that go trough -- a given table T_AUTHOR subgraph as ( select distinct t.table_name, regexp_replace(p.path, '^#(.*)#$', '\1') path from paths p cross join all_tables t where t.owner = 'TEST' and p.path like '%#' || t.table_name || '#%' ), -- This XML-trick splits paths and generates rows for every distinct -- table name split_paths as ( select distinct table_name origin, cast(t.column_value.extract('//text()') as varchar2(4000)) table_names from subgraph, table(xmlsequence(xmltype( '
').extract('//x/*'))) t ), -- "table_graphs" lists every table and its associated graph table_graphs as ( select origin, count(*) graph_size, listagg(table_names, ', ') within group (order by 1) table_names from split_paths group by origin ) select origin, graph_size "SIZE", dense_rank() over (order by table_names) id, table_names from table_graphs order by origin ' || replace(path, '#', ' ') || '
This monster piece of SQL calculates a convex hull of inter-connected tables in a relational database. I.e. pick any table and find the set of tables that are directly or indirectly connected to it through inbound or outbound foreign keys. Sigh. Why?comments powered by Disqus
Send us your own example of code that made you cry! Please include:
Note that we may choose not to publish some submissions if we feel they're not appropriate. We hope you understand.
Use jOOQ, instead of JDBC.
jOOQ generates Java code from your database and lets you build typesafe SQL queries through its fluent API.