Welcome, guest | Sign In | My Account | Store | Cart

The following snippet will output a spec suitable for the graphviz dot program. It will go through the database specified and for all the tables that start with any of the names in table_prefixes, it will create a dependency graph. A dependency in this context is when a field of one table has a foreign key reference to another. The edges are labelled with the actual field names.

It can be used like this

python deps.py | dot -Tpng | display

This requires the InnoDB engine (i.e. something that honours foreign keys. MyISAM doesn't do so).

Python, 43 lines
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
import _mysql

table_prefixes = ["users","activities"]

def writedeps(db, tbl):
    db.query("show create table %s"%tbl)
    r = db.store_result()
    row = r.fetch_row()
    while row:
        for i in (x.strip() for x in row[0][1].split("\n")):
            if i.startswith("CONSTRAINT"):
                pieces = i.split()
                local = remote = None
                for idx, j in enumerate(pieces):
                    if j == "KEY":
                        local = pieces[idx+1].replace("`","").replace("(","").replace(")","")
                    if j == "REFERENCES":
                        remote = pieces[idx+1].replace("`","")
                print '"%s" -> "%s" [label="%s"];'%(tbl,remote,local)
        row = r.fetch_row()

def get_tables(db):
    db.query("show tables")
    r = db.store_result()
    row = r.fetch_row()
    while row:
        prefix = row[0][0].split("_")[0]
        if prefix in table_prefixes:
            yield row[0][0]
        row = r.fetch_row()    
    
def main():
    db=_mysql.connect("localhost","nibrahim","foo","sample")
    print "Digraph F {\n"
    print 'ranksep=1.5; size = "17.5,7.5";rankdir=LR;'
    for i in get_tables(db):
        writedeps(db, i)
    print "}"
        

if __name__ == "__main__":
    import sys
    sys.exit(main())

This is useful to get an overall view of a database which you have to first look at. Many tables will be system generated so you can use the prefixes to exclude them from the list.

The ranksep, size and rankdir parameters will probably have to be tweaked to make the thing look "nice".