13 October 2008

Creating DIA diagrams from mysql via XSLT

During a conversation on FriendFeed about using inkscape and Dia, Chris Lasher asked me if I tried to use inkscape to create diagrams in SVG format. This gave me the idea to have a new/fresh look at Dia and see if I could use it for my self-interest (I should soon manage a mysql database with plenty of tables but I'm missing such schema). Dia (http://www.gnome.org/projects/dia/ ) can be used to draw many different kinds of diagrams. It currently has special objects to help draw entity relationship diagrams, UML diagrams, flowcharts, network diagrams, and many other diagrams.. A Dia diagram is formatted as a gzipped xml file. Today I created a XSLT stylesheet transforming the XML description of a table in mysql to a basic (no layout, no links) diagram in Dia. This stylesheet sql2dia is available here:

Usage:In the following example, I ask for the structure of four tables at the UCSC. Mysql adds a xml declaration after each query so we need to grep -v this header and surround the queries with an extra element:
(echo "<root>";
mysql --user=genome --host=genome-mysql.cse.ucsc.edu -A -D hg18 -e 'desc snp129; desc snpSeq; desc snpArrayAffy5; desc knownGene' -X |\
grep -v "<?xml" ;\
echo "</root>") > /tmp/tmp.xml
xsltproc sql2dia.xsl /tmp/tmp.xml |\
gzip -c > ~/file.dia

And here is a screenshot of the ouput.

That's it



wiselynx said...

wow! exactly what I was looking for.. and because I found it really useful, I put some bash code around it to make it a bit more user friendly:


one thing.. I noticed primary keys are not tagged as such, and relations are not built.. I suppose it should be a quite easy task to add these two features..

Pierre Lindenbaum said...

Thanks ! Your right, adding links would be easy (with xslt ? hum... I'm not so sure) if the links are described as foreign keys or if the column are verbose enough to be computed. Feel free to write it ;-)

Anonymous said...

Nice work. I need to learn to scroll. I wrote an almost exact replica of wiselynx' script that I used. I agree the relationships would be really helpful. If I figure it out I will share it back -- but may just draw them in on the small db I'm working on in the meantime.

Artur Kaminski said...

Very nice script. I'm looking after foreign keys now :-)
@wiselynx, thanks for the wrapper.

Anonymous said...

Awesome, thank you!

hoyhabloyo said...

You and wiselynx save me a bunch of hours of work.

Thank you very very much :)