5 ms·

My most useful GraphViz hack so far has been a script that reads the schema description out of a PostgreSQL database and turns it into a vaguely Access/SQL Serv

My most useful GraphViz hack so far has been a script that reads the schema description out of a PostgreSQL database and turns it into a vaguely Access/SQL Server-like database diagram (a node for each table, arrows representing foreign-key relations). Someday I'd like to set up a script that takes the query-plan returned by PostgreSQL's EXPLAIN statement and turns it into a diagram with a node for each operation and arrows for dependencies, where the arrow width is proportional to the number of rows involved (again, much like SQL Server's query plan results). This would make it easier to spot the hotspots in a complex plan. My favourite GraphViz tool would probably be XDot: http://code.google.com/p/jrfonseca/wiki/XDot Fast, interactive viewing of GraphViz source files without having to convert them to PNG every time, and can be embedded in your own Python/GTK+ apps.

hippee-lee 17y ago
Would you mind sharing your Script for parsing the db schema?
- thristian 17y ago
  I'd like to, but I wrote it on company time and hence don't actually have the right to distribute it. I will say that the trickiest bits were extracting the exact information from the database, and figuring out the how to make arrows point to and from individual fields, instead of from the table in general. Getting tables and fields from PostgreSQL: select table_schema, table_schema || '.' || table_name as qualified_table, column_name, data_type from information_schema.columns order by table_schema, table_name, ordinal_position; Getting foreign-key information from PostgreSQL: select kcu.table_schema, kcu.table_schema || '.' || kcu.table_name as qualified_table, kcu.column_name, ccu.table_schema as referenced_table_schema, ccu.table_schema || '.' || ccu.table_name as referenced_qualified_table, ccu.column_name as referenced_column_name from information_schema.constraint_column_usage as ccu join information_schema.key_column_usage as kcu using (constraint_catalog, constraint_schema, constraint_name) where ccu.table_schema != kcu.table_schema or ccu.table_name != kcu.table_name; As for the DOT output, I wound up using their HTML table support, generating a unique name for each cell in the table (port="blah" on each td element) and defining the foreign key arrows as going from table1:field1:e to table2:field2:w when table1.field1 is a foreign key referencing table2.field2.
  - hippee-lee 17y ago
    I am using mysql. Maybe I can figure something out and post it back here ... Thanks for the hints.
ananthrk 17y ago
Did the same thing (plotting tables and relation betweeen them) for the C# DataSet object.

hippee-lee 17y ago

Would you mind sharing your Script for parsing the db schema?

thristian 17y ago
I'd like to, but I wrote it on company time and hence don't actually have the right to distribute it. I will say that the trickiest bits were extracting the exact information from the database, and figuring out the how to make arrows point to and from individual fields, instead of from the table in general. Getting tables and fields from PostgreSQL: select table_schema, table_schema || '.' || table_name as qualified_table, column_name, data_type from information_schema.columns order by table_schema, table_name, ordinal_position; Getting foreign-key information from PostgreSQL: select kcu.table_schema, kcu.table_schema || '.' || kcu.table_name as qualified_table, kcu.column_name, ccu.table_schema as referenced_table_schema, ccu.table_schema || '.' || ccu.table_name as referenced_qualified_table, ccu.column_name as referenced_column_name from information_schema.constraint_column_usage as ccu join information_schema.key_column_usage as kcu using (constraint_catalog, constraint_schema, constraint_name) where ccu.table_schema != kcu.table_schema or ccu.table_name != kcu.table_name; As for the DOT output, I wound up using their HTML table support, generating a unique name for each cell in the table (port="blah" on each td element) and defining the foreign key arrows as going from table1:field1:e to table2:field2:w when table1.field1 is a foreign key referencing table2.field2.
- hippee-lee 17y ago
  I am using mysql. Maybe I can figure something out and post it back here ... Thanks for the hints.

ananthrk 17y ago

Did the same thing (plotting tables and relation betweeen them) for the C# DataSet object.