Over the Christmas break I had plenty of time to enjoy the company of my family, and occasionally escape the chaos of young kids with a little reading.
One book I enjoyed was fairly easy reading (if you’re into coding)
The Productive Programmer (2008 Neal Ford).
Follow the link, it seems like the reviews already say what is obvious. For my part, it was definitely an interesting read as I did go from cover to cover, which must be a good measure!
One example caught my attention in his discussion of DRY. Neal also seemed interested in Groovy, as I have been since my introduction to it by Bob Brown over at Transentia.
Neal gave examples of how he had used the DB schema to generate the base classes for a project’s Java code. A project I’m on at the moment breaks the DRY principle in this respect, and one has to both create changes in a Postgres Schema and then repeat oneself in the Java / hibernate code, which is a tad error prone if not simply a little tedious.
Anyway I decided I’d write a little code in Groovy to experiment with the idea that, if I were using the schema as a start point (and not just the DB as a bit bucket with Hibernate generated tables), then I might use the same approach but with much greater emphasis on Groovy rather than Java. Here is the code, starting with the helper classes, the next just to get me hooked up to my schema
// COMPILE: $ groovyc DBUtil.groovy
package DBUtil
import groovy.grape.Grape
@Grapes([
@Grab(group='postgresql', module='postgresql', version='8.4-701.jdbc4')
])
import groovy.sql.Sql
// could get password from config...
class DBBuilder {
def static setupDB() {
def driver = new org.postgresql.Driver()
// println "Driver = " + driver
Sql.newInstance("jdbc:postgresql://localhost:5432/my_DB","my_DB","sorry_my_secret")
}
}
and a simple lister of the tables I wish to generate code for, they happen to reside in Postgresql
import DBUtil.*
class TableLister {
def getList(pattern) {
def _sql = DBBuilder.setupDB()
def Qry = "SELECT tablename FROM pg_tables where tablename like ${pattern};"
def tableList = []
_sql.eachRow(Qry) {
// println "${it.tablename}"
tableList+=it.tablename
}
tableList
}
static void main(args) {
def list = new TableLister().getList('action_%')
list.each { println it }
}
}
Nothing flash, all I wanted was the table names matching a pattern
$ groovy TableLister.groovy
action_projects
action_projects_bay_areas
action_projects_organisations
action_themes
action_projects_catchments
action_organisations
action_project_images
Oh and to get some mappings that were sufficient for my tables
class TypeMapper {
def getMap() {
['integer':'Integer',
'serial':'Integer',
'character varying':'String',
]}
static main(args) {
def typeMap = new TypeMapper().getMap()
println "integer = " + typeMap['integer']
assert 'Integer' == typeMap['integer']
assert 'String' == typeMap['character varying']
}
}
The main generator of the Groovy classes (and virtually Java Classes if you wanted to go that way) is this bit of code.
I think my code is REALLY SIMPLE and stands as a PoC, but I’ve taken advantage of Groovy and it’s serious advantages in programming productivity over Java. It’s worth serious thought if you have a new project and you’re not blinded by the “I get more performance out of Java” thinking. I’d still be programming in Forth and C if I’d kept that as my only rule of thumb for development planning! Anyway the code to ponder
// If one creates a DB schema manually
// then might as well generate the base classes automatically
// NB Groovy also generates getters and setters
// Useful stuff can be added after extending from the generated code.
// (NB can be extended from either Groovy or Java code).
//
// generate the classes
// $ groovy generateBaseClasses.groovy
// and test one
// $ groovy ActionProjects.groovy
import DBUtil.*
// Generate Groovy classes from selected tables in the schema
// the emitter can be modified to generate Java if do not wish to inherit from Groovy
//
class ClassFromTable {
def _sql = DBBuilder.setupDB()
def _typeMap = new TypeMapper().getMap()
def packageName = "com.alex.actions"
def _tableName
def _className
def _newClassFile
def _tableDataQry
ClassFromTable(tableName) {
_tableName = tableName
_className = upperCaseFirstChar(camelCase(_tableName))
_newClassFile = new File("${_className}.groovy")
setColumnQry()
}
// A query that is virtually 'as used' in SQL interpreter
private setColumnQry() {
_tableDataQry = """
SELECT
a.attname as "Column",
pg_catalog.format_type(a.atttypid, a.atttypmod) as "Datatype"
FROM
pg_catalog.pg_attribute a
WHERE
a.attnum > 0
AND NOT a.attisdropped
AND a.attrelid = (
SELECT c.oid
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ~ '^(""" +
_tableName +
""")\$'
AND pg_catalog.pg_table_is_visible(c.oid)
) ;
"""
}
def camelCase(name) {
def newName = name.toLowerCase().split("_").collect() {
it.substring(0, 1).toUpperCase() + it.substring(1, it.length())
}.join()
newName.substring(0, 1).toLowerCase() +
newName.substring(1, newName.length())
}
def upperCaseFirstChar(name) {
name.substring(0, 1).toUpperCase() + name.substring(1, name.length())
}
// A class from a DB table
def emitClassFromTable () {
_newClassFile.write "package ${packageName}\n"
_newClassFile.append "\n"
_newClassFile.append "class " + _className + " { \n"
_sql.eachRow(_tableDataQry) {
_newClassFile.append " " + _typeMap[it.Datatype] + " ${it.Column}\n"
}
_newClassFile.append "\n"
_newClassFile.append " static void main(String[] args) {\n"
_newClassFile.append " def type = new " + _className + "()\n"
_newClassFile.append " assert type != null\n"
_newClassFile.append " println \"generated Groovy base class from postgres table metadata, and it compiles / runs!\"\n "
_newClassFile.append " }\n"
_newClassFile.append "}\n"
}
def static void main(args) {
new TableLister().getList('action_%').each { table ->
new ClassFromTable(table).emitClassFromTable()
}
}
}
When run it generates for me the base classes. A next step would be to add all the hibernate code too rather than leaving the extending classes to do it! Hmmm, if I do a new project…
$ cat ActionProjects.groovy
package com.alex.actions
class ActionProjects {
Integer id
Integer year
Integer theme_id
String project_number
String title
String description
Integer source_id
String hyperlink
static void main(String[] args) {
def type = new ActionProjects()
assert type != null
println "generated Groovy base class from postgres table metadata, and it compiles / runs!"
}
}
Which is valid groovy, if I take one of the generated classes and run (or compile) it
$ groovy ActionProjects.groovy
generated Groovy base class from postgres table metadata, and it compiles / runs!
Now all I need is a new project :-)
