From Mysql to Oracle: Grails application migration
Today I have finished migration of our Grails prototype. Originally we did develop it for MySQL, but the final system have to work with Oracle. This post summarize troubles and differences I was facing.
User creation
Since I am not Oracle administrator, it took me some time to put together script that creates user and appropriate rights for the Grails application. Use the script for development only.
CREATE user grails IDENTIFIED BY grassword; GRANT connect TO habilion; GRANT CREATE TABLE TO habilion; GRANT CREATE sequence TO habilion; GRANT unlimited tablespace TO habilion;
Identifier length limitation
I usually do not use identifiers that are 30 characters long. But, there is a trick.
Imagine two classes: Author and Content. Author having multiple Contents.
Now, imagine there is several types of Content – Book, Newspaper, ElectronicNewspaper, ScientificArticle…
So, we can model the situation using following classes:
Class Content{ static belongsTo = [ contentAuthor:Author ] } Class Book extends Content {} Class Newspaper extends Content {} Class ElectronicNewspaper extends Content {} Class ScientificArticle extends Content {} Class Author{ static hasMany = [ contents: Content ] }
And now comes the trick. The ‘content’ table in the database contains long columns:
content_author_id newspaper_content_author_id electronic_newspaper_content_author_id book_content_authot_id scientific_article_author_id
As you can see, it is easy to hit the 30 characters limit. If you look deeper, you will see the solution. The column names are composed of class name of the Content descendants and variable name pointing to the master entity.
So, I renamed the contentAuthor to ca, created getter and setter for contentAuthor and checked all “new Book…” snippets. God bless MVC!
Column size
The second major issue was a String column. I had a string column of 10000 characters.
Class Content { String abstract static constraints = { abstract(maxSize:10000) } }
It was translated to Long data type. Unfortunately there is a bug in Oracle JDBC driver that causes nasty stack traces when processing Long data type. There is a workaround on the Internet, but it is working only for some versions of Oracle. Moreover, there is just one Long column allowed per table. So, changed the size to 4000 characters and the column was created as VARCHAR2.
And thats it!
Update: Empty strings
One thing to mention is a fact that Oracle does not distinguish between null and an empty string. So, if you try to store an empty string to Oracle DB, it actually stores it as a null value.
The problem arises, if you have not null column and you try to store empty string ‘’. For Grails it is a not null value, so it does not apply defaults. For Oracle it is null and thus constraint violation…
![]() | Published on May 22nd, 2008 | | 2 Comments | | Posted by Roman Mackovcak |