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).
Download
Copy to clipboard1 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".
