Week 2 / Day 2: more Riak

Time to do some more tests.
Now that the Riak plugin is up and running, it mostly works like a charm.

Simple links and datatypes work just the same as with those relational databases from week 1. But what about my DataTypes class? I gave it a first try and immediatly got an error while trying to save:

Error 500: Executing action [save] of controller [sevendatabases.DataTypesController] caused exception: No converter found capable of converting from 'byte[]' to 'java.lang.String'
Servlet: grails
URI: /riak4/grails/dataTypes/save.dispatch
Exception Message: No converter found capable of converting from 'byte[]' to 'java.lang.String'
Caused by: No converter found capable of converting from 'byte[]' to 'java.lang.String'
Class: DataTypesController
At Line: [24] 

hm. Somehow strange since Riak can work with binary datatypes. On a second though, I used a domain class crafted for the hibernate plugin and expect it to work with the riak plugin.

After removing the Byte[] - Property, all other datatypes work perfectly!

Riak-Roundup

Time was too short to check out all Riak features, but I learnt a lot...

• the riak plugin is based on the Spring-Data project. The goal of this project is to make it easier "to use new data access technologies such as non-relational databases". So I guess I will revisit Spring Data the next weeks.
• the version of the riak plugin is 1.0.0M - for a first milestone release it is forgiveable that not everything works as smooth as it could be. The documentation is already quite good, support is fast (see my Stack Overflow question) and it works better than other plugins with higher version numbers ;-)
• I only found some time to test some simple domain features, but the documentation promises that mapreduce and other features are accessible through the plugin!

Week 2 / Day 1: Riak

The Riak chapter is great! Riak seems to be a wonderful database - exactly what every web developer is looking for. But it is the first database which is a NoSQL database and hence I have my doubts that it will work nicely with Grails.

So if first searched for a Grails plugin and found one! http://grails.org/plugin/riak

The documentation is fine and you get a really quick start with Riak. Even building Riak from source is no paint with the finde documentation available.

So I've installed Erland, Riak and the Riak-Plugin, started 3 Riak servers and tryed to run a simple application. That's where the problems started. I didn't know how to tell Grails that it should use Riak instead of the normal Gorm mapping via Hibernate. The answer was simple: just remove the hibernate plugin!

This works finde for Grails 1.3.8, but not for Grails 2.0.8 - it seems that Grails 2.0.8 automatically reinstalls the Hibernate plugin. So let's try to go on with Grails 1.3.8...

Now I get at least an error message saying I can't connect... I have the riak servers up and running and can connect through a browser, but not through the application.

After debugging a while, I notice that my riak config is not visible to the application... strange... after a while I have to reboot my virtual machine. After rebooting, my dev-riak servers are down and the app sees the riak configuration... but: the app connects to riak and runs! But it still seems to ignore my configuration...

Doesn't matter this time. At least, it connects and I can do some tests. I create an Object and it is somehow funny to see a riak hash code as ID:

Since Grails can connect to the Riak database, but it isn't running on the configured port, I redirect my browser to the standard Riak port 8098 et voilá! There it is.

Grails creates buckets as

projectname.Domain

and keys as hashes. So my just created object is accessible as

http://127.0.0.1:8098/riak/riak4.Book/-9011180415092426943

in my browser.

Week 1 / Day 3: compare PostgreSQL to MySQL and Oracle XE

In order to compare PostgreSQL with other databases, I install a MySQL and Oracle Express Edition. This time, I use -- since I've them already isntalled -- both on Windows 7.

Here is the DataSource for MySQL:

        dataSource {
            dbCreate = "create-drop"
            url = "jdbc:mysql://localhost:3306/sevendatabases"
            driverClassName = "com.mysql.jdbc.Driver"
            dialect = org.hibernate.dialect.MySQL5InnoDBDialect
            username = "grailsdbuser"
            password = "grailsdbuser"
        }

And this is it for Oracle:

        dataSource {
            dbcreate = "create-drop"
            url = "jdbc:oracle:thin:@127.0.0.1:1521:XE"
            driverClassName = "oracle.jdbc.driver.OracleDriver"
            dialect = 'org.hibernate.dialect.OracleDialect'
            username = "grailsdbuser"
            password = "grailsdbuser"
        }

In addition to the Book and Author domains, I create another DataTypes test-domain through which I try to persist some more properties of different types:

grails create-domain-class DataTypes
grails create-scaffold-controller sevendatabases.DataTypes

I edit the DataTypes.groovy domain class to look like this:

package sevendatabases

class DataTypes {
    String  _string
    String  _string80
    String  _bigString
    String  _bigString2
    String  _inList
    Integer _integer
    Double  _double
    Float   _float
    BigDecimal  _bigdecimal
    byte[]  _binaryDate
    Boolean _bolean
    Date    _date
    
    static constraints = {
        _string80(maxSize:80)
        _bigString(maxSize:8000)
        _bigString2(maxSize:8000)
        _inList(inList:['one','two','three'])
    }
}

I've used only common data types which just came to my mind. Float and Double might look a bit outdated... byte[] is often used for file uploads. _inList checks if any 'in list' contraint will be created in the database or if this is just a gails thing. _string will check the default size for a String and should never be used in a real app!^*. _bigString will check what happens to long text fields (like comments). _bigString2 will check for a strange behaviour I've already encountered with Oracle.

Problems

1. I started out prefixing the property names with an underscore '_'. This seems to work fine for PostgreSQL and MySQL, but Oracle's conventions don not allow this.
2. the byte[] is not automatically mapped to a blob or something equal. So if you forget to specify a maxSize, you will get strange exceptions from your database layer (see Stack Overflow).
3. Hibernate maps a long String for Oracle to the datatype 'Long'. If you have two long Strings in one domain, you will get the following error message:
   ORA-01754: a table may contain only one column of type LONG
   Even with this workaround from Stack Overflow (Using Oracle clob with Grails), I can't make it work. The only solution I see is to use only one big String porperty in a domain or to restrict it to a sie which can be mapped to a varchar2 (4000 bytes).

Here is the fixed source code which works with all three databases:

package sevendatabases
class DataTypes {
    String  myString
    String  myBigString
    String  myBigString2
    String  myInList
    Integer myInteger
    Double  myDouble
    Float   myFloat
    BigDecimal  myBigdecimal
    byte[]  myBinaryData
    Boolean myBoolean
    Date    myDate
    
    static constraints = {
        myBigString(maxSize:4000)
        myBigString2(maxSize:4000)
        myInList(inList:['one','two','three'])
        myBinaryData(maxSize: 20 * 1024 * 1024)
    }
}

Results

Property	MySQL	PostgreSQL	Oracle EX
myBigString	varchar(4000)	character varying(4000)	VARCHAR2(4000)
myBigString2	varchar(4000)	character varying(4000)	VARCHAR2(4000)
myBigdecimal	decimal(19,2)	numeric(19,2)	DECIMAL(19)
myBinaryData	tinyblob	bytea	LONGVARBINARY
myBoolean	bit(1)	boolean	DECIMAL(1)
myDate	datetime	timestamp without time zone	TIMESTAMP(7)
myDouble	double	double precision	FLOAT(126)
myFloat	float	real	FLOAT(126)
myInList	varchar(5)	character varying(5)	VARCHAR2(5)
myInteger	int(11)	integer	DECIMAL(10)
myString	varchar(255)	character varying(255)	VARCHAR2(255)
myString80	varchar(80)	character varying(80)	VARCHAR2(80)

Lessons learnt

• it is always good practice to set a size contraint on all String or Byte[] properties. Otherwise you can get strange results.
• Grails plays nicely together with relational databases but you still habe to know about some specific behaviour if you need to create code which runs on several databases. Oracle seems to be more restrictive than MySQL or PostgreSQL

^* If you define a String property without a size contraint, you might run into the following problem: Grails only checks the constraints defined in the domain class before trying to save the object. If you now try to store a String longer than 255 characters, hibernate will throw an ungly exception. http://jira.grails.org/browse/GRAILS-8770

Week 1 / Day 2: Advanced Database Stuff

While searching the internet for Grails and PostgreSQL stuff, you cenrtainly stumbled upon warning like "don't create a 'User' domain! This will lead to an exception!".

These warnings are true, but they are not PostgreSQL specific. GORM uses the name of the domain class as table names and the name of the properties as column names. And if you use complex relationships, it will even create table names with concatenated domain class names.
This will result in two problems:

1. Some databases like oracle and PostgreSQL are picky about table and column names which are reserved keywords like SELECT, TABLE, FROM, USER etc. Grails will try to create those tables and columns but will fail with a database exception. This is very ugly when you develop with a database which is not so picky and deploy to another one which will crash your app.
2. There are contraints regarding the length of table names. So if you create a BooksWithHsitoricalValue Domain which references a AuthorsWhoDiedALongTimeAgo Domain in a many-to-many fashion, it is likely that you will scratch this limit. But the good news is that this will not happen too often :-)

Grails has a simple solution for (mainly) the first problem: you can specify two simple methods which create a table or column name from the class name. This way, you can prepend a string which turns all reserved keywords in non-reserved ones. "tbl_" and "col_" for example.

The solution is to create a ImprovedNamingStrategy and tell hibernate to use it: http://grails.org/doc/latest/guide/GORM.html#customNamingStrategy
Create a class like the following in your /src/groovy folder:

package com.blogspot.parzeval

import org.hibernate.cfg.ImprovedNamingStrategy
import org.hibernate.util.StringHelper
import org.codehaus.groovy.grails.commons.*

class SafeNamingStrategy extends ImprovedNamingStrategy {
    def config = ConfigurationHolder.config
    String classToTableName(String className) { 
        config.dataSource.tablePrefix + StringHelper.unqualify(className) 
    }

    String propertyToColumnName(String propertyName) { 
        config.dataSource.columnPrefix + StringHelper.unqualify(propertyName) 
    }
}

and configure your prefixes in your DataSource.groovy:

dataSource {
    tablePrefix = 'tbl_' 
    columnPrefix = 'col_'
}

A better solution would be if hibernate would handle those reserved keywords in the right way (most databases allow you to set column and table names in quotation marks or something equal). But I haven't found a switch for that yet :-(

PS: there seems to be a setting for hibernate 3.5+ to automatically escape all column and table names:

hibernate.globally_quoted_identifiers=true

Unfortunately, I couldn't get it to work. And there seems to be a reason for not beeing a default setting...

Week 1 / Day 1: PostgreSQL

Ubuntu is up and running in a virtual machine, Grails 2.0.3 is installed and I am ready for the first experiments.

PostgreSQL
This is my first encounter with PostgreSQL, but not my first one with a relational database. So I guess this will be pretty easy:

$ sudo apt-get install postgresql
$ sudo apt-get install postgresql-client
$ sudo apt-get install pgadmin3
$ grails create-app SevenDatabases
$ cd SevenDatabases

pgadmin3 is a graphical admin console which is very useful to get a start. Hardest part^* to get PostgeSQL up and running was to change the password of the root user "postgres". The following lines did the trick for me:

$ sudo -u postgres psql postgres
# \password postgres

Now it is easy to connect to your database through pgadmin3 and create a new user ("grailsdbuser") and database ("mydb") for your Grails application.

If you search for "Grails PostgreSQL" on the net, you'll find a lot of good resources on how to configure your project. For my tests, I use the following DataSource.groovy:

dataSource {
    pooled = true
    driverClassName = "org.postgresql.Driver"
    username = "grailsdbuser"
    password = "grailsdbuser"
}
hibernate {
    cache.use_second_level_cache = true
    cache.use_query_cache = false
    cache.region.factory_class = 
         'net.sf.ehcache.hibernate.EhCacheRegionFactory'
}

environments {
    development {
        dataSource {
            dbCreate = "create-drop" 
            url = "jdbc:postgresql://localhost:5432/mydb"
            dialect = org.hibernate.dialect.PostgreSQLDialect
        }
    }
}

This specifies the jdbc driver and connection, but before you can use it, you have to download the jdbc driver and drop it into your /lib folder.
A first test domain can be created through

$ grails create-domain Book
$ grails create-domain Author 
$ grails create-scaffold-controller sevendatabases.Book
$ grails create-scaffold-controller sevendatabases.Author

remember: without a specified package, the create-domain script will create the domain within a package with the same name as the project - 'sevendatabases' in this case. Any following script will need the full name of the domain class - that's why we have to specify 'sevendatabases.Book' for the create-scaffold-controller script.
let's add some properties to the Book.groovy and Author.groovy domain classes:

package sevendatabases

class Book {
    String title
    String isbn
    Author author
    static constraints = {
       title (maxSize:80)
       author ()
       isbn (maxSize:13)
    }
    String toString() {title}
}

package sevendatabases

class Author {
    String name
    Integer age
    static hasMany=[books:Book]
    static constraints = {
      name(maxSize:80)
      age()
      books()
    }
    String toString() {name}
}

Et voilá! grails run-app will show you a nice Grails application with PostgrSQL support!

^* it turned out that changing the password was only a problem because I used the Ubuntu terminal in vmware's unity mode. In this mode, the virtual machine displays all Ubuntu windows mixed with your other windows applications. Unfortunately, this mode has some problems with special characters like the backslash '\'. And hence I've got the problem to type in the password command...

Welcome

A new book called "Seven Databases in Seven Weeks" has just been released. I was reading the first chapters when I though "wouldn't it be interesting to add some Grails specific knowledge to the book?". If I learn about the great concepts behind those seven databases, it would also be interesting to check how they work together with Grails.

I will document my findings in this blog. Mybe I will not only cover those 7 databases mentioned in the book, but also add those databases which I use in my daily work: MySQL and Oracle.

In theory, Grails uses hibernate to abstract the differences between the different databases you use. This works fine for relational databases, but even here are some traps hidden. When it comes to other types of databases like key-value or big data, I have no clue how they will play together with Grails.

Feel free to join me on my journey through the wonderful world of database programming!

Ralf

PS: please don't blame me if I do not manage to write about all databases in 7 weeks :-)