Monday, December 14, 2009

Grails and Legacy Database

Recently I played around Grails with a legacy database. Here are some tips I gathered around the journey:
1. I chose to use SpringSource Tool Suite, an IDE based on Eclipse. I love its feature to bring up Grails command prompt with a convenient short cut of 'ctrl-alt-g'.
2. Google 'GRAG' and use its latest version to reverse engineer your legacy database. GRAG certainly has room for improvement but it does a decent job to create the domain objects and save you a lot of typing. BTW, it's totally fine if you don't want to use GRAG and prefer to manually generate the domain classes.
3. Refine the domain definition. Note that Grails/Hibernate uses 'id' as the default field name for primary keys. You may not wish to change this convention or define another field as existingId. Unfortunately that's just the default behavior of GRAG 1.1.

static mapping = {
table 'Legacy_Table'
version false
id column:'Existing_ID'
} ...

4. If the existing primary key is in the type of a String the domain definition needs additional tweak:

static mapping = {
table 'Legacy_Table'
version false
id column:'String_ID', generator:'assigned'
}
String id
An exception of 'Fail to convert to internal representation' will be thrown if you fail to declare the id as a String

5. If you are careful enough you may find that the generator is defined as 'assigned' instead of the default value. This means the application will generate the primary key. Accordingly the save() operation has to explicitly set the String 'id'. Controller's save operation will look like:

def save = {
def legacyTable = new legacyTable(params)
legacyTable.id = params.id
if(!legacyTable.hasErrors() && legacyTable.save())
...
}


Please let me know if it works. I will be surprised if not.

One of the limitations Grails have is that, the 'version false' statement basically turns off the versioning from Gorm and its underline Hibernate implementation. If two users update the same database entry simultaneously, the system's behavior may not be predictable. In another word, we are counting on the optimistic lock on the data integrity.

6 comments:

  1. It's not that it doesn't work at all, but it doesn't work out of the box. If you use this technique, then generate your views and controllers, the generated create.gsp page doesn't include a field for the id property (which seems to me to be a pretty obvious requirement when the id generator is 'assigned'). Grails' support for legacy databases is just spotty...

    ReplyDelete
  2. I agree. It's easier to use Grails in a middle-out approach (from domain to controller/view and database schema) instead of bottom up (from legacy db to domain/controller/view). Hope this post is helpful to those enterprise developers who do not have the luxury to work on brand new code/database but are still bold enough to try out Grails.

    ReplyDelete
  3. thanks for this post denis.
    tips 4 and 5 have been really helpful

    ReplyDelete
  4. I saw this post an tried out GRAG. While not OUT-of-the-box, then ALMOST. There were some small issues where Grails complained about some composite keys must implement Serializable. Also I needed to escape cloumns named by "reserved" names (eg. 'key'). When I implemented Serializable and escaping, it worked. No fuzz...

    Also when I generate controllers and add scaffolding, I see an ID field.

    Btw - I'm using Grails 1.2.0 and GRAG 1.1 - maybe this has improved within the last year??

    I see GRAG doing 95% of the boilerplatework for you creating the domain classes. This is pain when you are trying to model an existing schema. Especially if you do not have access to the DDL.

    I 'praise' GRAG ;-) go ahead and try it...

    Christian Sonne Jensen

    ReplyDelete
  5. Hi Denis,
    I need to map to a legacy table which does not have a "primary key". Please help me, how we do the mapping.

    ReplyDelete