Reverse Snowflake Joins

Reverse Snowflake Joins (abbreviated as revj) is a tool to understand SQL and document SQL queries. It is good for maintaining big queries written by somebody else. "Big SQL" can be as many as 20 tables, joined by multiple fields.

Parts of the query appear directly in the diagram: Ex age=30 , GROUP BY (year), HAVING MIN(age) > 18 .

Getting these queries right is difficult because many tools generate poor SQL when dealing with complex joins. Manually written SQL is also error-prone, for example one might forget to remove a table if it is no longer needed to filter/group by one field in that table.

If there are disconnected tables it means there is a cartesian product. There are legitimate cases for cartesian products, however these cases are not common.

Same way, loops are usually not desired.

What it isn't?

It's not an EXPLAIN tool. If you write by mistake a cartezian join, no matter how good the indexes are, it will remain incorrect.

It's not a query execution tool. Because there are thousands of those, with drag-and-drop and predictive typing, and for sure everyone has it's favorite.

What is new in this release:

• This version fixes "cast(x as integer)", which previously produced an error.

What is new in version 0.48:

• This version fixes "union all" (e.g., "select t1.a from T1 union all select t2.b from T2 union all ..") and fixes functions without parameters and parens, (e.g., "Select .. where t1.x=sysdate").

What is new in version 0.47:

• Fixed a bug where UNION SELECT was case sensitive.

What is new in version 0.44:

• Fixed anonymous subselects. Ex: select * from (select A.id from A) Long IN clauses are truncated. Ex: table.field in (1,2,3,4...999)

What is new in version 0.42:

• This version has an improved simplifier (e.g. not complaining about the column concat, which is a function).
• Improved subselects, e.g. "select * from (select * from ta A,tb B where A.x=B.y) D1, (select * from ta A,tb B where A.x=B.y) D2".

What is new in version 0.38:

• bugfix: support for Oracle bind variables ex a = :1
• bugfix: "select field from schema.table" failed when there was no table alias or explicit table.field

What is new in version 0.37:

• A bug in which "select distinct a.id from a" did not display the id field in a graph was fixed.

What is new in version 0.36:

• Bugfix: remove multiple /* */ comments

What is new in version 0.35:

• If aggregates are found in multiple tables than issue warning for fan and/or chasm trap. Bugfixes related to ORDER BY alias

Requirements:

• GraphViz
• Python