Commits

Julio Biason committed d567c4e

Imported from svn by Bitbucket

  • Participants

Comments (0)

Files changed (1)

+#!/usr/bin/python
+
+import _mysql
+import pprint
+import re
+import sys
+
+from optparse import OptionParser
+
+# regular expressions
+column_info = re.compile('  `([a-zA-Z_]*)` (.*)')
+primary_key = re.compile('  PRIMARY KEY  \(`(.*)`\)')
+normal_key = re.compile('  KEY `(.*)` \(`(.*)`\)')
+references = re.compile('  CONSTRAINT `.*` FOREIGN KEY ' \
+        '\(`(.*)`\) REFERENCES `(.*)` \(`(.*)`\)')
+
+def get_options():
+    """Get the options passed in the command line. Returns the options."""
+    parser = OptionParser()
+    parser.add_option('--host',
+            dest='host',
+            help='MySQL host',
+            default='localhost')
+    parser.add_option('-d', '--database',
+            dest='database',
+            help='Database name',
+            default='')
+    parser.add_option('-u', '--user',
+            dest='user',
+            help='Username',
+            default='')
+    parser.add_option('-p', '--password',
+            dest='password',
+            help='Password',
+            default='')
+    parser.add_option('-o', '--output',
+            dest='output',
+            help='Output to file (default to stdout)',
+            default='')
+    (options, _) = parser.parse_args()
+
+    # check options (if needed)
+    return options
+
+def build_table_scheme(connection, table_name):
+    """Get the information about a table. Return list of tuples"""
+    connection.query('SHOW CREATE TABLE %s' % (table_name))
+    result = connection.store_result()
+    table_info = result.fetch_row(how=1, maxrows=0)
+    info = table_info[0]['Create Table'].splitlines()
+
+    columns = {}
+    for line in info:
+        match = column_info.match(line)
+        if match:
+            # tuple is: description, index, references
+            columns[match.group(1)] = [match.group(2)[:-1], '|', '']
+            # the pipe will force items without indexes to be in the bottom
+            # (when we list the columns)
+
+        match = primary_key.match(line)
+        if match:
+            # the column will exist because MySQL shows the columns first and
+            # then the keys (so there will be no KeyError expcetions on this)
+            columns[match.group(1)][1] = 'PRIMARY'
+
+        match = normal_key.match(line)
+        if match:
+            index_name = match.group(1)
+            # an index can have multiple columns
+            columns_name = match.group(2) 
+            for column in columns_name.split(','):
+                column_name = column.strip('` ')
+                columns[column_name][1] = index_name
+
+        match = references.match(line)
+        if match:
+            this_table_column = match.group(1)
+            other_table = match.group(2)
+            other_table_column = match.group(3)
+            columns[this_table_column][2] = (other_table, other_table_column)
+
+    return columns
+
+def get_tables(connection, database):
+    connection.query('SHOW TABLES')
+    result = connection.store_result()
+    table_list = result.fetch_row(how=1, maxrows=0)
+    return [tables['Tables_in_%s' % (database)] for tables in table_list]
+
+def generate_dot(graph, database, output):
+    output.write('digraph %s {\n' % (database))
+    output.write('\t/* nodes (a.k.a. tables) */\n')
+
+    relations = []
+
+    for table in graph:
+        output.write('\t%s [\n' % (table))
+        output.write('\t\tshape=plaintext,\n')
+        output.write('\t\tlabel=<\n')
+        output.write('<table border="0" cellborder="1" cellspacing="0">\n')
+        output.write('\t<tr><td bgcolor="#aeaeae" colspan="3">%s</td>' \
+                '</tr>\n' % (table))
+        output.write('\t<tr><td bgcolor="#cecece">Column</td>' \
+                '<td bgcolor="#cecece">Description</td>' \
+                '<td bgcolor="#cecece">Index</td></tr>\n')
+
+        # remember that pipe in the index? well, here we force that to be in
+        # the bottom
+        order = [(index, column) for column, (_, index, _) in
+                graph[table].items()]
+        for column in [column for (_, column) in sorted(order)]:
+            (description, index, references) = graph[table][column]
+            if index == '|':
+                # the pipe is just for ordering, not to show it.
+                index = ''
+
+            output.write('\t<tr><td PORT="%s">%s</td>' \
+                    '<td>%s</td>' \
+                    '<td>%s</td></tr>\n' % (column, column, description,
+                        index))
+
+            # check for relationships using the reference (contraints)
+            if references:
+                (other_table, other_column) = references
+                relations.append((table, column, other_table, other_column))
+
+        output.write('</table>\n')
+        output.write('\t\t>\n')
+        output.write('\t]\n')
+
+    output.write('\n/* Relationships */\n')
+    for reference in relations:
+        (table, column, other_table, other_column) = reference
+        output.write('\t%s:%s:w -> %s:%s:w;\n' % (table, column, other_table,
+            other_column))
+
+    output.write('}\n')
+
+def main():
+    options = get_options()
+    db = _mysql.connect(options.host, options.user, options.password,
+            options.database)
+    table_list = get_tables(db, options.database)
+
+    graph = {}
+    for table in table_list:
+        info = build_table_scheme(db, table)
+        graph[table] = info
+
+    if options.output:
+        output = file(options.output, 'w')
+    else:
+        output = sys.stdout
+    generate_dot(graph, options.database, output)
+
+if __name__ == '__main__':
+    main()