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>"""

Do you use Grails in your project?

As an experiment, I recently converted a web application that I wrote 2 years ago from Struts to Grails.

Some of the immediate rewards:

1. Code size is reduced significantly, especially codes that populate the beans with request parameters. Less code means easier to debug and read.
   
2. I can specify all the constraints in the entity class and don't have to create another XML file for validation purpose.
3. Taglib is very easy to write in Grails. I practically got rid of 80% of the Java scriptlets by using taglibs. My JSPs (GSPs actually) are much easier to read now.
   
4. It runs on standard web container. I have tested it on JBoss, Geronimo and Glassfish. No problem.

Are you using Grails in your project? If yes, what makes you decide on it? If no, what are the concerns?

List of question marks

Using only what a programming language standard API can provide, how do you generate a comma-separated list of question marks to be used as placeholders in a typical JDBC prepared statement? You know, the kind of "select * from tbl where code in (?, ?, ?, ?, ?)".

I tried to do it with the languages that I know. Say I want n question marks...

Java:

StringBuffer buf = new StringBuffer("?");
for (int i = 2; i <= n; i++) {
  buf.append(", ?");
}
result = buf.toString();

Groovy:

def s = ('?' * n).toList().join(', ');

Ruby:

s = ('?' * n).split(//).join(', ')