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
Pierre
6 comments:
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:
http://wiselynx.interfree.it/stuff/soft/sql2dia.sh
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..
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 ;-)
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.
Very nice script. I'm looking after foreign keys now :-)
.
@wiselynx, thanks for the wrapper.
Awesome, thank you!
You and wiselynx save me a bunch of hours of work.
Thank you very very much :)
Post a Comment