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:

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.

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

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:

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:

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:

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:

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.

Very useful. I’m working on prototype grails app that needs to talk to a legacy database and it’s not easy. I’m starting with a table that has a string key, but it isn’t a composite key. I set it up like this

id column:'sys_id', generator: 'assigned'

in the AccountController, instead of changing

def accountInstance = Account.get(id)

I changed the signature of show() from show(Long id) toshow(String id) and that got the details link working. Lots more to do, but it’s a start.

I got create working after adding an id field to _form.gsp and adding accountInstance.id = params.id to save(), but for some reason when I update it say “Account not found with id null”. I’m a bit mystified right now.

Very useful. Thanks. I got this working with Grails 2.2.3, but I had to change a few things because one of the fields in my composite key was also a reference to another domain object. Otherwise, this was spot on for my use case. Thank you so much. Saved me a lot of time.