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")
  }
  keyRS.close()
}

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 ""
tableRS.close()

sql.connection.close()

println """<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<graphml xmlns="http://graphml.graphdrawing.org/xmlns/graphml" 
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xmlns:y="http://www.yworks.com/xml/graphml" 
  xsi:schemaLocation="http://graphml.graphdrawing.org/xmlns/graphml 
  http://www.yworks.com/xml/schema/graphml/1.0/ygraphml.xsd">
  <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:ShapeNode>
<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"/>
</y:ShapeNode>
</data>
</node>"""
}

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:PolyLineEdge>
<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"/>
</y:PolyLineEdge>
</data>
</edge>"""
  }
}

println """<data key="d4">
    <y:Resources/>
  </data>
  </graph>
</graphml>"""