Improving Wetware

Because technology is never the issue

Drawing ER Diagrams with GraphViz

Posted by Pete McBreen Sat, 19 Dec 2015 22:46:00 GMT

Recently as part of an archaeology task of understanding how some SQL queries were working, I needed to draw an ERD to help with my understanding of the database. After contemplating drawing the diagram by hand for a few seconds, I decided to leverage GraphViz and just draw a diagram of all of the foreign key relationships between the tables.

Since it was an Oracle database, the queries to read the relationships were not that complex ErdCrearion-specific.sql is designed to run in SQLDeveloper and prompt for the :OWNER tablespace name to pick the tables from, and limit the selection to the names mentioned in the tablelist CTE (unfortunately duplicated as I have not rewritten this to make it simpler).

From that query the output is pasted into the starter GraphViz .gv file scottsimple.gv

digraph ERD {
  graph [   rankdir = "LR" ];
  ranksep=2;

  "SCOTT.DEPT" [ label="<SCOTT.DEPT> SCOTT.DEPT|<PK_DEPT>DEPTNO \l |DNAME \l LOC \l " shape = "record" ];
  "SCOTT.EMP" [ label="<SCOTT.EMP> SCOTT.EMP|<FK_DEPTNO>DEPTNO \l |EMPNO \l ENAME \l JOB \l MGR \l STARTDATE \l SAL \l COMM \l " shape = "record" ];


  "SCOTT.DEPT":"PK_DEPT"->"SCOTT.EMP":"FK_DEPTNO" [arrowhead = crow];
}

Which after being put through GraphViz

$ dot -Tsvg scottsimple.gv -o scottsimple.svg

gives us a nice image of the relationship. The table name is prefixed with the schema to make sure that you can identify the table correctly for those cases where the same table exists in multiple schemas, and the columns involved in the relationships are highlighted in their own box. The non-relationship columns appear at the bottom of the symbol (column ordering is maintained and hidden columns are not shown).

scottsimple.svg

For a more interactive experience run the SVG file through this converter, svgconverter.rb which converts the SVG file to a HTML file with some D3JS JavaScript that highlights relationships and tables as you click on them - I found that this made it easier to trace through a model when there are 80+ tables.

$ ruby svgconverter.rb scottsimple.svg scottsimple.html

The resulting file when uploaded to a webserver that has d3.js in the right place is interactive - see scottsimple.html unlike the image above it can be clicked on to highlight the symbols or relationships - only the outer line of the table is clickable - the rest is left as an exercise for the reader.

Back to the future (again)

Posted by Pete McBreen Thu, 03 Dec 2015 04:27:00 GMT

Delving into the archives, Leslie Lamport wrote about the Future of Computing back in 2003. There are still too many programs that are not understood fully by their developers or maintainers.