Adding jar to classpath at run time

Whenever I want to test some groovy ideas, I use groovyConsole. However, I don't want to copy the dependent jar files into my $HOME/.groovy/lib, and as of groovy 1.5.6, it is still not possible to add -classpath to the command line of groovyConsole.bat.

I found a solution on the web. Just add this line into the start of the code:

this.getClass().classLoader.rootLoader.addURL(new File("file.jar").toURL())

Better still, use a list:

[ "file1.jar", "file2.jar" ].each {
  this.getClass().classLoader.rootLoader.addURL(new File(it).toURL())
}

SQL Keywords As Column Names

Once in a while, I got caught by my own mistake by using SQL keywords as column names. For example, this looks pretty harmless (but it fails in GORM):

class Person {
  String userName
  String password
  String group
}

There are 2 problems:

1. password is a keyword in MySQL.


2. group is an SQL keyword.

Although it usually doesn't take me too long to realize my mistake, it is a nuisance that I'd rather live without. There is a more systematic way to detect such problem.

Create recursive markup with MarkupBuilder

I just found a way use Groovy's MarkupBuilder to build an XML file with nested elements. Assuming I have a container with a collection of X objects and a Y object. The XML output of the container can be generated as follows:

import groovy.xml.*

class X {
  private int id
  private String name

  def toXML = { builder ->
    builder.x(id: id, name)
  }
}

class Y {
  private Date date = new Date()
  def toXML = { builder ->
    final sdf = new java.text.SimpleDateFormat("yyyyMMdd'T'HH:mm:ssZ")
    builder.y(date: sdf.format(date))
  }
}

def w = new StringWriter()
def xml = new MarkupBuilder(w)
xml.doubleQuotes = true
def xa = [ new X(id: 1, name: "x-name"), new X(id: 2, name: 'x-name2') ]
def yObj = new Y()
xml.root() {
  agent(id: 1, name: 'agent1', "X")
  xa.each { xo ->
    xo.toXML(xml)
  }
  yObj.toXML(xml)
}
w.close()

The variable w is equivalent to:

<root>
 <agent id="1" name="agent1">X</agent>
 <x id="1">x-name</x>
 <x id="2">x-name2</x>
 <y date="20080318T23:56:54+0800" />
</root>

Notice that to get the builder to output double quotes for attributes, I set the doubleQuotes property to true.

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(', ')

The Haze Is Gone

This haze has almost cleared. According to The Star, the haze was "caused by almost 600 hot spots in Indonesia and a peat fire in Cyberjaya".

This photo was taken at around 11:00 a.m. today. Compare it to this post

Hazy Day

The haze came back this afternoon. This photo was taken at around 6:15 p.m. from my house in Puchong.