Menu ▾ ▴

Home

Timotheus Pokorra

sql2diagram converts sql scripts to diagrams in the XML format dia, which can be viewed and further maintained with the dia drawing program. It is a good help for the documentation of a database structure. It provides HTML output as well.

Code

The code lives now at https://github.com/tpokorra/sql2diagram

Convert SQL Create Statements into a Database Schema Diagram and HTML representation

This program can be used to produce diagrams in a XML format that is used by Dia, which is a free diagram editor for Linux and Windows.

Sql2Dia can be compiled on Windows with Dev-C++ / Mingw, and on Linux with normal make and gcc.

I had my problems with autodia (http://www.aarontrevena.co.uk/opensource/autodia/), because back then there were no constraints inserted into the diagram, and I also wanted to have a linked HTML output (and I am not so good with Perl either).

Example to demonstrate functionality

A good example can be found using the data structure of Compiere, because there are several modules with each containing many tables.

The goal is to reflect the database structure in diagrams and in HTML documentation.
A requirement is that you need the "Create Table" SQL statements, which you could also generate with the export functionality of phpMyAdmin etc.

You can run the program like this:
sql2dia Compiere.sql A

This will search for all tables in the SQL file that start with A_; You will have to run the call for all the modules, each with its own prefix.

  • These tables will be added to the dia file called A.dia
  • They will be integrated into an HTML site
  • You need to move the tables from the invisible layer to the background layer and arrange them. (see the more detailed explanation below, in the example)
  • You need to run the sql2dia program again, that will add the constraints to the diagram.
  • Rearrange the tables, run sql2dia again, till you are happy with the result.
  • You should export the dia file into a PNG file, and save it in the img directory. The A.png looks like this. There is an HTML image map created for it automatically.

The result is:

Things to note in the diagram:

  • The table http://sql2diagram.sourceforge.net/example/img/img_A.html?C_AcctSchema is printed on the diagram of module A, because it is referenced from A_Asset_Change_Amt. But because C_AcctSchema does not belong to this module, only the primary key is displayed. The blue colour shows that the table is not complete, and the "..." show that not all fields are displayed.
  • The table http://sql2diagram.sourceforge.net/example/img/img_A.html?A_Asset is also displayed in blue colour. That means, that not all constraints have been drawn. The table R_Request references A_Asset, but is not on the diagram A. All tables that are referenced by A_Asset are drawn on the diagram A, but in the short version with the "...".
  • Constraints are only drawn once between 2 tables, otherwise the diagram would be really cluttered.
  • To read the diagram, you will see a table referencing another table: One field of the key will have a line to the title row of the other table.

New functionality: grouping of tables

  • When you have a big database, you might want to group the tables somehow.
  • This does not always work with the prefixes, and some people don't even use prefixes for table names.
  • The solution looks like this: in the SQL statements, you can have a comment anyways for each table. Now just add another line for the comment, and write eg. GROUP: account
  • this will mark this table to belong the group "account"
  • When you run sql2dia -g -f mydb.sql then you will get some more html files with the groups

Users Reference

Functionality

The program parses a number of text files which hold SQL CREATE TABLE statements.
A module is a set of tables, which names start with the same prefix plus underscore.
It creates HTML files for each module that clearly show the links to other tables, and the references onto a table.
It creates a DIA file that can be edited with the Dia diagram editor. The diagrams can hold tables from different modules. Tables can be hidden.
It can read already existing DIA files and reuse the positions already assigned.
It can be rerun and automatically adds new constraints and new tables to the diagram, which then can be positioned in Dia diagram editor.
It creates an HTML image map that can be used with the png file that can be exported from Dia diagram editor.
It creates a batch file that holds statements to call ImageMagick Convert to tile/crop the big images to prepare them for printing.

Interpretation of the diagrams

Colour: The tables that are shown on the current diagram without all their constraints or references are displayed with a blue background colour.

Constraints: The constraint starts at one attribute that is part of a foreign key. All attributes that are part of a foreign key are marked with a "FK".
There is only one constraint painted between a pair of tables, even if there are several references.

Example Usage

Howto use Sql2Dia:

  • run sql2dia Compiere.sql A
  • The parameter A means that all tables with this prefix are added to the diagram A.
  • open A.dia
  • All tables are on the "invisible" layer. You can open with a right click the menu and go to Dialog/Layers. Hide the "background" layer, and bring the "invisible" layer to the front.
  • Switch to 50% Zoom.
  • Select 5 or more tables from the invisible layer, cut them, hide the invisible layer, show the background layer, and switch to background. Insert the tables and move them to the correct position.
  • After having put all necessary tables on the background layer, save the A.dia file: Make sure you disable "compress diagram files".
  • close the A.dia
  • Run sql2dia.exe again.
  • It creates a backup file in the bak directory.
  • If you forgot disabling compression, the current file is again a new start and has lost all your positions. You can open the file in the bak directory, and save it without compression.
  • It adds the constraints to the tables that are on the background layer.
  • It moves the end points of the constraint to the correct side of the table, left or right.
  • You open A.dia again, and align the constraints nicely.
  • To insert new segments into a constraint, move the mouse over the line, hold the CTRL key, and press the right mouse button. *Then comes a menu with "Add new segment". Make sure you add segments only behind the first right angle from any table. That helps so that the constraints joins correctly when the tables are moved around.
  • Attention: only the position of the tables, as well as the relation to one of the 2 layers, and the position of constraints is restored, nothing else.

If there is a new situation in the sql create statements, new tables are added in the upper left corner of the background layer.
New constraints are added all over the diagram, you need to adjust them.
If constraints or tables are removed from the sql statements, then they also are not displayed on the diagram anymore.

To export the diagram to an image format, choose File/Export from the menu and select GdkPixBuf Bitmap, and let the filename have the extension png. This is the best way pngs are created running Dia on Windows at the moment. (The direct png export did not work for me on 0.91). Page setup should be 100 %.

The program creates a batch file called tile.bat. You call it with the first parameter as the path to your current location of ImageMagick convert.exe. A number of small images is created, eg. diagram0.png, diagram1.png, ....

Don'ts

  • Don't add new tables / constraints in the DIA editor.
  • Don't change constraints; even not from the left side of the table to the right side.
    Only move around the tables and constraints, and add/remove segments to the constraint lines.

Dia Diagram Editor Tipps

  • For quickly moving around on the diagram use the cursor keys. it is much faster than move around with the mouse on the scrollbars.
  • Also for moving the lines: click the point, and then hold it with the mouse, and press the cursor keys to move around on the diagram

To insert new segments into a constraint, move the mouse over the line, hold the CTRL key, and press the right mouse button. Then comes a menu with "Add new segment". Make sure you add segments only behind the first right angle from any table. That helps so that the constraints joins correctly when the tables are moved around.

Files Reference

compile sql2dia with gcc and makefile, should work out of the box.
cd sql2diagram/src
make

compile with Dev-C++/MinGW:
make sure you have the LibXML DevPak 2.6.19 and the LibIConv DevPak 1.9.1 installed.
For running sql2diagram on another machine, you will also need the dlls: iconv.dll, libxml2.dll, zlib1.dll
The IDE file is stored in sql2diagram/win32/sql2diagram.dev

to tile/crop/dice the image with ImageMagick convert, just call
convert -crop <width>x<height>+<x>+<y> fullimage partimage

use 140% smaller when printing the resulting tiles

There are some template files in the sql2diagram/template directory that you will need and can modify for your own projects.

They are called:

  • index.html
  • table-doc-top.html
  • table-doc.js
  • table-doc-sub.js
  • index.css
  • table-doc.css

Classes

  • There is a Parser class that is able to parse text files.
  • ParserSQL and ParserDIA are derived from that class.
  • A DataBase object holds several Table objects, the tables on the current diagram are stored in the tables vector, and all tables read from sql statements are stored in the allTables vector.
  • A Table object holds Constraints and Attributes. And it knows about which other tables are referencing it.
    There are derivations of DataBase, Table and Attribute for both HTML and DIA.