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:

http://code.google.com/p/lindenb/source/browse/trunk/src/xsl/sql2dia.xsl

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

Pierre