Grails: Migrating enum mapping from 1.0 to 1.2 or newer

We have an ongoing long-term web application running on Grails. It started with Grails 1.0.3 and Grails moved on to 1.3.3 in the meantime. Due to time constraints and lack of resources we were not able to update to each new major version. Now, some years later, the time has finally come for us to benefit from all the new features, bugfixes and improvements of the platform. There were quite some changes in behaviour and one of the biggest is the change of how enums are mapped to the database.

In Grails 1.0.x and 1.1.x Java enums were mapped as int values in the database. Starting with Grails 1.2 they are mapped as varchars containing the enum name. Now you have the problem to migrate your existing data over to the new mapping style of the framework. One solution is to use autobase or liquibase migrations to port the enum values for the new mapping style.
Suppose we have the enum Coolness:

pulic enum Coolness {
    COOL, UNCOOL;
}

The SQL for migrating it on the PostgreSQL DBMS looks as simple like this:

alter table foo alter column bar type varchar
    using case when bar = 0 then 'COOL'
            when bar = 1 then 'UNCOOL'
            end

and as an autobase migration it becomes something like this:

changeSet(id: 'change foo bar from int to varchar', author: 'me') {
    preConditions(onFail: "CONTINUE") {
        dbms(type: "postgresql")
    }
    sql("""alter table foo alter column bar type varchar
            using case when bar = 0 then 'COOL'
                    when bar = 1 then 'UNCOOL'
                    end""")
}

We use the precondition to skip our non-persistent in-memory databases we use in development and only apply the change set with persistent test or production databases.

For Oracle and maybe other database systems which do not support altering the column type with the using-clause it may look like

alter table foo add bar_new varchar(255);
update foo set bar_new =
    (case when type = 0 then 'COOL'
            when type = 1 then 'UNCOOL'
      end);
alter table foo drop column bar;
alter table foo rename column bar_new to bar;

I hope this helps when you have to perfom similar migrations some time.

Stay tuned for other changes in API and behaviour between the different Grails framework versions.