--- a/buildout.cfg	Wed Feb 15 16:09:26 2012 +0100
+++ b/buildout.cfg	Thu Apr 26 19:03:39 2012 +0200
@@ -14,7 +14,7 @@
 [instance]
 recipe = zc.recipe.egg:script
 eggs = ${install:eggs}
-scripts = run admin
+scripts = run admin sql2dot
 arguments = '${buildout:directory}/etc/ltpdarepo'
 
 # generate wsgi application

--- a/setup.py	Wed Feb 15 16:09:26 2012 +0100
+++ b/setup.py	Thu Apr 26 19:03:39 2012 +0200
@@ -53,6 +53,7 @@
       entry_points={
           'console_scripts': [
               'run = ltpdarepo:main',
-              'admin = ltpdarepo.admin:main', ]
+              'admin = ltpdarepo.admin:main',
+              'sql2dot = ltpdarepo.sql2dot:main' ]
           }
       )

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/src/ltpdarepo/sql2dot.py	Thu Apr 26 19:03:39 2012 +0200
@@ -0,0 +1,71 @@
+# Copyright 2011 Daniele Nicolodi <nicolodi@science.unitn.it>
+#
+# This software may be used and distributed according to the terms of
+# the GNU General Public License version 3 or any later version.
+
+import sys
+import sqlalchemy
+
+from operator import itemgetter
+
+FONTSIZE = 10
+NODEWIDTH = 150
+RANKSEP = 1.5
+CELLPADDING = 3
+
+def dump(username, password, hostname, database, out=sys.stdout):
+    url = sqlalchemy.engine.url.URL('mysql',
+                                    username=username, password=password,
+                                    host=hostname, database=database)
+    engine = sqlalchemy.engine.create_engine(url)
+    inspector = sqlalchemy.engine.reflection.Inspector.from_engine(engine)
+    dot_database_tables(inspector, out)
+
+
+def dot_database_tables(inspector, out):
+    out.write('digraph g {\n')
+    out.write('  splines=spline\n')
+    out.write('  overlap=false\n')
+    out.write('  rankdir=LR\n')
+    out.write('  ranksep={}\n'.format(RANKSEP))
+    out.write('  node [shape=plaintext fontsize={}]\n'.format(FONTSIZE))
+    tables = sorted(inspector.get_table_names() + inspector.get_view_names())        
+    for table in tables:
+        dot_table_structure(inspector, table, out)
+    out.write('}\n')
+
+
+def dot_table_structure(inspector, table, out):
+    # each table is node
+    out.write('  "{}" [\n'.format(table))
+
+    # primary keys
+    pks = inspector.get_primary_keys(table)
+
+    # table name
+    rows = []
+    rows.append('<tr><td width="{}">{}</td></tr>'.format(NODEWIDTH, table))
+
+    # columns
+    for column in sorted(inspector.get_columns(table), key=itemgetter('name')):
+        port = column['name']
+        name = '<font>{}</font>'.format(port)
+        if port in pks:
+            name = '<font color="firebrick">{}</font>'.format(port)
+        rows.append('<tr><td align="left" port="{}">{} : {}</td></tr>'.format(port, name, column['type'].__visit_name__))
+        
+    out.write('    label=<<table border="0" cellborder="1" cellspacing="0" cellpadding="{}">{}</table>>\n'.format(CELLPADDING, ''.join(rows)))
+    out.write('  ]\n')
+
+    # foreign keys represented as node relations
+    fks = inspector.get_foreign_keys(table)
+    for fk in fks:
+        src = '{}:{}'.format(fk['referred_table'], ''.join(fk['referred_columns']))
+        # special case to link xxdata tables to the ao table
+        if table.endswith('data'):
+            src = 'ao:obj_id'
+        out.write('  {} -> {}:{}\n'.format(
+            src, table, ''.join(fk['constrained_columns'])))
+
+def main(*args, **kwargs):
+    dump(*sys.argv[1:])