other register

Friday, July 03, 2009

Migrate Access to MySQL to Fit in Grails

1. Access Tables Preparation

Open the Access database and modify the table names and column names with the following convention:
  • Modify the table names to table_name (e.g. account_manager) format if necessary. GRAG will generate a domain class named: TableName.
  • Modify column names to column_name (e.g. contact_address) format if necessary. GRAG will generate a field named: columnName.
  • Create an autoNumber id as the primary key if the table doesn't have one
  • If the table's primary key is not named id, then change it to id. GRAG will generate an id field with the mapping like: id generator:'identity', column:'id'
  • Modify foreign keys in other tables as table_name_id. GRAG will create a relation for this.

2. Migrate Access to MySQL

MySQL Migration Toolkit to migrate MS Access to MySQL ( It is an automatic process with step by step instructions. The tool will generate dbCreate.sql and dbInserts.sql, remember to save them. Then:
  • Modify the dbCreate.sql to add foreign keys which GRAG will create relations for the generated domain class, and add version int(10) field, otherwise a 'version false' statement will be generated in the mapping, because this isn't available by default for legacy databases
  • Re run the dbCreate.sql script to re-generate the tables.
  • Re run the dbInserts.sql script to populate the data.

3. Generate the Domain Classes

Then you can start using Grails Application Generator (GRAG) ( to generate the domain models. It is not perfect, but at least it saves some manul typings.

Open the generated domain classes to:
  • check the generated field to see wheter it comforms to the lowerCaseCapital format (e.g. contactAddress)
  • If the id is an auto_increment field, then comment out the id field, and comment out id generator:'identity', column:'id' statement.
  • If the id is a user assigned value field (e.g. characters), then don't comment out the id field. Also, change the id generator in the mapping to: id generator:'assigned', column:'id'
  • modify relations if necessary (i.e. hasMany, belongsTo, or association like: OtherDomain otherDomain)
  • comment out constraint for a start
  • modify the toString method to return some meaningful value.

4. Configuration

Change dbCreate to 'update' in the DataSource.groovy so that the data

5. Testing

runt he "grails console", then for each domain class, test it with the following script:

def a = DomainClass.get('KK')

6. Generate Skeletons Code

grails generate-all *

This will generate the controllers and gsp view pages for all the domain classes.

7. Modify assigned id field for controller and gsp

If the id is a user assigned id rather than an auto incremented id, then for the controller, explicitly assign the id:

def save = {
def nameInstance = new Name(params) =
if(! nameInstance.hasErrors() && {
For the create.gsp page, add the id input field:

<tr class='prop'>
<td valign='top' style='text-align:left;' width='20%'>
<label for='id'>ID:</label>
<td valign='top' style='text-align:left;' width='80%'
<input type='text' name='id' value='${nameInstance?.id}' />

There is also a very good post regarding Hosting Grails to your Legacy Database

No comments: