Code That Made Me Cry, #CTMMC™

95% of all code makes me cry. The other 5% are white space.

Convex Hull Calculation (SQL, #9 / 50)

-- "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(
      '' || replace(path, '#', '') ||
'').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        

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?

Source: http://blog.jooq.org/2012/08/20/serious-sql-a-convex-hull-of-correlated-tables/

comments powered by Disqus

Submit your own Code That Made You Cry

Send us your own example of code that made you cry! Please include:

Send the above to ctmmc@datageekery.com. All accepted submissions will be licensed as CC BY-SA 3.0. Submit only code of a compatible license.

Note that we may choose not to publish some submissions if we feel they're not appropriate. We hope you understand.

Don't let your SQL code make you cry!

Use jOOQ, instead of JDBC.

jOOQ generates Java code from your database and lets you build typesafe SQL queries through its fluent API.

Don't let your SQL code make you cry! Get back in control of your SQL with jOOQ.