Tuesday, November 20, 2007

Reverse engineer the ER diagram

In every single project that I have been involved in, there is also a need to generate the ER diagram from the database. There are tools to do the reverse-engineering part. However, I have not come across a tool (and free one) that can rearrange the rectangles nicely.

Out of frustration, I wrote a script in Groovy to generate GraphML file from the DB schema.

The script gets a java.sql.Connection and use the DatabaseMetaData to find out the dependencies of the tables. Using the dependency information, it then produces GraphML elements.

I open the XML file in yEd, and use one of the layout engines in yEd and, there you have it - an ER diagram with edges indicating the foreign key constraints.

92 LOC to solve a decade-old problem. Not bad at all...

Here's the code:

import groovy.sql.*

def tables = [:]

def visitTable = { dbmd, schema, tableName ->
if (!tables[tableName]) {
tables[tableName] = new HashSet()
def keyRS = dbmd.getExportedKeys(null, schema, tableName)
while (keyRS.next()) {
tables[tableName] << keyRS.getString("FKTABLE_NAME")

def config = [
host: "localhost", port: 3306,
dbname: "mydb", username: "myname", password: "mypass",
driver: "com.mysql.jdbc.Driver",
schema: "myschema" ]
def url = "jdbc:mysql://${config.host}/${config.dbname}"

def sql = Sql.newInstance(url, config.username, config.password, config.driver)
def dbmd = sql.connection.metaData

def tableRS = dbmd.getTables(null, config.schema, null, "TABLE")
while (tableRS.next()) {
visitTable(dbmd, config.schema, tableRS.getString("TABLE_NAME"))
System.err.print "."
System.err.println ""


println """<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<graphml xmlns="http://graphml.graphdrawing.org/xmlns/graphml"
<key for="node" id="d0" yfiles.type="nodegraphics"/>
<key attr.name="description" attr.type="string" for="node" id="d1"/>
<key for="edge" id="d2" yfiles.type="edgegraphics"/>
<key attr.name="description" attr.type="string" for="edge" id="d3"/>
<key for="graphml" id="d4" yfiles.type="resources"/>
<graph id="${config.schema}" edgedefault="directed">"""

tables.each { k,v ->
nodeId = "${config.schema}_${k}"
println """<node id="${nodeId}">
<data key="d0">
<y:Geometry height="30.0" width="${nodeId.length() * 8}.0" x="0.0" y="0.0"/>
<y:Fill color="#CCFFFF" transparent="false"/>
<y:BorderStyle color="#000000" type="line" width="1.0"/>
<y:NodeLabel alignment="center" autoSizePolicy="content"
fontFamily="Dialog" fontSize="13" fontStyle="plain"
hasBackgroundColor="false" hasLineColor="false"
height="19.92626953125" modelName="internal" modelPosition="c"
textColor="#000000" visible="true" width="37.0"
x="5.5" y="5.036865234375">${k}</y:NodeLabel>
<y:Shape type="rectangle"/>
<y:DropShadow color="#B3A691" offsetX="2" offsetY="2"/>

tables.each { k,v ->
v.each { referer ->
edgeId = "${config.schema}_${referer}_${k}"
println """<edge id="${edgeId}" source="${config.schema}_${referer}" target="${config.schema}_${k}">
<data key="d2">
<y:Path sx="0.0" sy="13.5" tx="0.0" ty="-15.0"/>
<y:LineStyle color="#000000" type="line" width="1.0"/>
<y:Arrows source="none" target="standard"/>
<y:EdgeLabel alignment="center" distance="2.0" fontFamily="Dialog"
fontSize="12" fontStyle="plain" hasBackgroundColor="false"
hasLineColor="false" height="4.0" modelName="six_pos"
modelPosition="tail" preferredPlacement="anywhere" ratio="0.5"
textColor="#000000" visible="true" width="4.0"
x="2.0000069969042897" y="18.5"/>
<y:BendStyle smoothed="false"/>

println """<data key="d4">