Grails and Legacy Data – Composite Keys in your Tables

When developing a Grails app, you don’t always have the luxury of using GORM (the Grails ORM) ‘out of the box’ against new schemas. Sometimes you just have to work with legacy data as you get it with little modification (if any). Today I’m going to show some of the tricks/traps for working with legacy data when creating a new grails application. There are a few pitfalls that aren’t well-documented with the help of this article you should be able to avoid them.

Mapping Metadata to your Domain Class

GORM is really powerful, but it imposes its own “world view” on table and metadata structures. That can be fine when you start from scratch on a new application. But what happens when you have data that doesn’t match that world view? You could consider revising the table structure and impact all of your other applications. Or you can simply structure your Grails domain class to fit the data you’re working with.

Consider the following table. For this example I’m using MySQL but any popular SQL server  would work:

CREATE TABLE `application_config` (
  `CONFIG_NAME` varchar(128) NOT NULL,
  `KEYNAME` varchar(128) NOT NULL,
  `VALUE` varchar(1024) NOT NULL,
  `DESCRIPTION` mediumtext,
  `LAST_UPDATED` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `VERSION` decimal(10,0) NOT NULL DEFAULT '0',
  PRIMARY KEY (`CONFIG_NAME`,`KEYNAME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

This looks simple enough, but Grails will puke on it if you try to use it as-is for a couple of reasons:

  • GORM wants an auto-incrementing large integer field called ‘id’ to be the primary and unique key for the table. A primary key is required by GORM and quite frankly you shouldn’t even bother doing work in SQL databases if you’re not going to use them.
  • GORM also wants a version field for managing changes via optimistic locking. This isn’t a requirement, but I’d strongly recommend adding this field to your tables. Even a simple non-null field defaulting to a value of ‘1’ would be sufficient and your grails app will be more robust for it.

According to the Grails documentation there is support for Composite Primary Keys.  From their website:

GORM supports the concept of composite identifiers (identifiers composed from 2 or more properties). It is not an approach we recommend, but is available to you if you need it.

They aren’t kidding.  They not only don’t recommend it but they don’t document very well how to do it, either.

First step is to create your domain class.  For this we’ll call it AppConfigInfo:

grails create-domain-class AppConfigInfo

After being greeted with your empty domain class, lets start adding the data members that will be mapped to the database:

class AppConfigInfo implements Serializable {
	String configName
	String propertyName
	String propertyValue
	String description
	Date updated

	static constraints = {
    }
}

I’ve added the reference to implements Serializable because as we add the rest of the mapping information to the class serialization will be required.

The next thing we need to do is configure constraints for the table.  While not required I’d strongly recommend it.  This way you don’t have issues with your primary key having blank entries.

/*
 * basic constraints to specify the field order. needed when
 * views are generated for the controller
 *
 */
 static constraints = {
	configName (blank:false)
	propertyName (blank:false)
	propertyValue (blank:false)
	description (maxSize:80)
	updated()
 }

Now we’ve enforced basic input validation, but we still need to map the data attributes of this class back to the columns in the table. That’s what the mapping block is for:

/*
 * this mapping configures the columns to match up to the legacy
 * database table.
 */
static mapping = {
	table 'APPLICATION_CONFIG'
	// this line will disable the version number support required
	// for GORM optimistic locking.  comment out or remove
	// if you want to support locking (recommended, not required)
	version false
	id composite:['configName','propertyName']
		
	//maps the columns to the properties in the domain
	columns {
		configName column:'CONFIG_NAME'
		propertyName column:'KEY_NAME'
		propertyValue column:'VALUE'
		description column:'DESCRIPTION', type:'text'
		updated column:'LAST_UPDATED'
	}
}

 

Note the columns block above. That’s the final piece for the mapping. With that block, grails knows which fields will be mapped to your legacy table. That leaves us with only a couple other lines tasks left. We need the code to handle the field called updated and the code to return the composite key to your controller. Also if you have two different fields for creation and update timestamps instead of one, you can skip the handling code below and just write the code that returns the primary key:

//if you have separate columns for created and updated timestamps
//you can skip this code and map the above columns accordingly
//most of the time in my experience, schemas have only one column for this
	
def beforeInsert = {
	updated = new Date()
}
	
def beforeUpdate = {
	updated = new Date()
}
	
def getPK() {
	["configName":configName, "propertyName":propertyName]
}

At this point if you got the idea that you could just generate a controller and use the built-in dynamic scaffolding like this to test out the design so far:

class ConfigureController {
    def scaffold=AppConfigInfo
}

It’ll sort of work, but you’ll have some hiccups. For example: you will be able to create new records like this

Grails Create Screen1

But you won’t be able to edit the entry because the scaffold only recognizes the GORM model of using the standard ‘id’ unique key. Note the empty column for ID:

Grails List Screen1

This fix for this requires generating the full set of views and the fully implemented controller. From there we can modify the views and controller to do what we need. Besides, you’d never actually use the dynamic scaffolding for any real work, anyway

grails generate-all AppConfigInfo

There’s a few other things that have to be updated also. We’ll have to replace the code that supports the GORM id field with code that enables the use of our composite key. When you try to start your application, list will work but clicking the ‘Details’ link generate exceptions like this:

Error 500: Executing action [show] of controller [example.AppConfigInfoController] caused
 exception: groovy.lang.MissingMethodException: No signature of method: example.AppConfigInfo.getPK() 
is applicable for argument types: (org.codehaus.groovy.grails.web.servlet.mvc.GrailsParameterMap) 
values: [[propertyName:english.language.error.expired.password, configName:I18N, action:show, 
controller:appConfigInfo]] Possible solutions: getPK(), getId(), get(java.lang.Object), 
getAt(java.lang.String), getAll(), getLog()

On the bright side, create may just work for you immediately so that’s at least one area you’ll be able to leave alone. Fortunately, Grails actually is attempting to give you a hint on how to resolve this as well with the following line:

Possible solutions: getPK(), getId(), get(java.lang.Object), 
getAt(java.lang.String), getAll(), getLog()

The use of get(java.lang.Object) is the hint. Note that your controller for the ‘show’ action has a line like this:

def appConfigInfoInstance = AppConfigInfo.get(params.id)

In order to leverage the composite key we’ll need to modify two files, the controller and the views for the show and list actions. First change your controllers show action to something like this:

def appConfigInfoInstance = AppConfigInfo.get(new AppConfigInfo(params))

This fulfills the contract suggested by grails stacktrace.

Next the view for the list action (list.gsp) has to be modified so that we can click on the “Details” link and correctly load the show action. Modifying the line shown below is the trick:

<g:each in="${appConfigInfoInstanceList}" status="i" var="appConfigInfoInstance">
    <tr class="${(i % 2) == 0 ? 'odd' : 'even'}">
    
        <td><g:link action="show" id="${appConfigInfoInstance.getPK()}">Details</g:link></td>
    
…

</g:each>

Changing the id attribute to a params attribute bridges the gap to the controller:

<g:each in="${appConfigInfoInstanceList}" status="i" var="appConfigInfoInstance">
    <tr class="${(i % 2) == 0 ? 'odd' : 'even'}">
    
        <td><g:link action="show" params="${appConfigInfoInstance.getPK()}">Details</g:link></td>
    
…

</g:each>

Next, we need to modify the views for Edit and Delete. Let’s tackle Edit first. GORM supports optimistic locking via a numeric “version” field for tables that are set up to support it. For this demo we’ve disabled it. So change edit.gsp from this:

<g:form method="post" >
    <g:hiddenField name="id" value="${appConfigInfoInstance?.id}" />
    <g:hiddenField name="version" value="${appConfigInfoInstance?.version}" />

To this:

<g:form method="post" >
    <g:hiddenField name="id" value="${appConfigInfoInstance.configName}" />
    <g:hiddenField name="value" value="${appConfigInfoInstance.propertyName}" />

To complete the change we also need to the show.gsp view:

<div class="buttons">
    <g:form>
        <g:hiddenField name="id" value="${appConfigInfoInstance?.id}" />
        <span class="button"><g:actionSubmit class="edit" action="edit" value="${message(code: 'default.button.edit.label', default: 'Edit')}" /></span>
        <span class="button"><g:actionSubmit class="delete" action="delete" value="${message(code: 'default.button.delete.label', default: 'Delete')}" onclick="return confirm('${message(code: 'default.button.delete.confirm.message', default: 'Are you sure?')}');" /></span>
    </g:form>
</div>

To reflect the composite key as shown:

<div class="buttons">
    <g:form>
        <g:hiddenField name="id" value="${appConfigInfoInstance.configName}" />
        <g:hiddenField name="value" value="${appConfigInfoInstance.propertyName}" />
        <span class="button"><g:actionSubmit class="edit" action="edit" value="${message(code: 'default.button.edit.label', default: 'Edit')}" /></span>
        <span class="button"><g:actionSubmit class="delete" action="delete" value="${message(code: 'default.button.delete.label', default: 'Delete')}" onclick="return confirm('${message(code: 'default.button.delete.confirm.message', default: 'Are you sure?')}');" /></span>
    </g:form>
</div> 

For the sake of completeness I’ve also included the sample code from my application here. This application was built in SpringSource Tools Suite – if you use the same toolset you should have no problem importing it and playing with it.

AppConfigTool.zip

Grails is a powerful framework, geared toward maximizing web application productivity in the Java ecosphere. With a few adjustments you can leverage this powerful framework in a variety of situations.