Off-Topic: Groovy and Grails on Raspberry Pi

It works! Received my Raspberry Pi this week and had to see if it runs Java, Groovy and Grails.

The ressources (256MB memory) of the Raspberry are very limited, so I didn't really expect to make it work, but instead it was easy.

a simple

apt-get install openjdk-7-jdk

installs java. The same for groovy:

apt-get install groovy

It takes some seconds until the groovy shell starts up, but it makes a good impression.

Installing Grails was a little bit harder since it can't be installed through apt-get. But a

wget http://dist.springframework.org.s3.amazonaws.com/release/GRAILS/grails-2.0.4.zip
unzip grails-2.0.4.zip

nearly did the job. You still have to set some environment variables and lower the memory needs through GROOVY_OPTS (I personally changed the settings directy in the startGroovy). After that it works! Slowly, but it works. Generating a page takes aprox 6 minutes :-)

Week 4 / Day 2: Last Chance for CouchDB

Never give up, never surrender.

I give CouchDB another chance. When you experience problems with open source software, you should at least create some bug entries in order to tell the developer that something does not work.

So I searched for the grom-couchdb bugtracker and stumbled upon the code base on git. Hm. It looks like some files are newer than what I've got... And there seems to be even a patch for the 2.0 problem!

https://github.com/coryhacking/gorm-couchdb
=> https://github.com/MalteJ/gorm-couchdb/tree/

but now that I've take a look at the issue tracker, I see that it would be too much work to do in order to get it up and running: this issue explains inderectly, that the plugin needs a js-view in order to work and it is not generated if you do a generate-all on a no-couch domain.

It seems that you may use the plugin if you already have a better knoledge of couchDB, but for me, that's it. No couchDB for my grails project!

Week 4 / Day 1 CouchDB

Today I did some experiments with CouchDB. The daabase is easy to install, has a clean web interface and makes jsut a good expression.

So I searched for a Grails plugin and found a 2 years old one: Grom-CouchDB.

Installing the plugin wasn't easy. I first tried my Grails 2.0.x installation - it easily picks up the plugin, but when I try to run it, I get a strange compilation error:

user@ubuntu:~/grails/couch3$ grails run-app
| Compiling 53 source files.
| Error Compilation error: startup failed:
Compile error during compilation with javac.
/home/user/.grails/2.0.4/projects/couch3/plugins/gorm-couchdb-0.9.2/
src/java/org/codehaus/groovy/grails/plugins/couchdb/domain
/CouchDomainClass.java:49: 
error: CouchDomainClass is not abstract and does not override 
abstract method getPersistentProperty(String) in GrailsDomainClass
public class CouchDomainClass extends AbstractGrailsClass implements
ExternalGrailsDomainClass {
       ^
/home/user/.grails/2.0.4/projects/couch3/plugins/gorm-couchdb-0.9.2/
src/java/org/codehaus/groovy/grails/plugins/couchdb/domain
/CouchDomainClassProperty.java:37: 
error: CouchDomainClassProperty is not abstract and does not 
override abstract method isDerived() in GrailsDomainClassProperty
public class CouchDomainClassProperty implements 
GrailsDomainClassProperty {
       ^
Note: /home/user/.grails/2.0.4/projects/couch3/plugins/
gorm-couchdb-0.9.2/src/java/org/codehaus/groovy/grails/plugins/
couchdb/domain/CouchDomainClass.java uses or overrides a 
deprecated API.

Not a good start. Especially when I can't imagine that Grails 1.3.x should not bring up the same exception. Nevertheless, I'll give it a try.

Somehow, I really ran into problem when I even just tried to install the plugin with Grails 1.3.x. The plugin is in the list when I do a <pre>grails install-plugin gorm-couchdb</pre> but when the script runs, it tells me that it can't find the plugin.

So I tried to download and install it but I've got the same result. So I made use of my last special trick: I copied and renamed the downloaded zip:

cp gorm-couchdb-0.9.2.zip grails-gorm-couchdb-0.9.2.zip

and tried to install it:

grails install-plugin grails-gorm-couchdb-0.9.2.zip

It is very interesting, that if you try to install it with the grails-prefix, it seems to pick up the file without the prefix. But it does not work the other way around. Another interesting thing is that I only ran into this problem on my linux machine. Netbeans managed to install it without any problem. I guess that this is fixed in Grails 2.0.x

Ok. So now, we have an installed plugin. If you read through the documentation, you'll find that you don't have to remove the hibernate plugin. Even better, you can configure that plugin to use anotation in order to mix hibernate and couchDB. But beware: if you want to make use of

grails generate-all domain

You first have to execute this script and only afterwards anotate your domain class - otherwise it will tell you that it can't map your domain:

Running script /usr/share/grails/1.3.8/scripts/GenerateAll.groovy
Environment set to development
Domain class not found in grails-app/domain, trying hibernate 
mapped classes...
No domain class found for name sevendatabases.DataTypes. Please 
try again and enter a valid domain class name
Application context shutting down...
Application context shutdown.

By working around this, I managed to setup my test project, but unfortunately, as soon as I try to access the list controller, I get the following Exception:

org.jcouchdb.exception.DataAccessException: error querying view: code 404
 at org.jcouchdb.db.Database.queryViewInternal(Database.java:848)
 at org.jcouchdb.db.Database.queryView(Database.java:656)
 at org.jcouchdb.db.Database$queryView.call(Unknown Source)
 at org.jcouchdb.db.Database$queryView.call(Unknown Source)
 at sevendatabases.DataTypesController$_closure2.doCall(DataTypesController.groovy:13)
 at sevendatabases.DataTypesController$_closure2.doCall(DataTypesController.groovy)
 at java.lang.Thread.run(Thread.java:722)

So, that's it for now. I guess this problem is beacuse I use the current version of CouchDB, but it makes no sense for me to downgrade to a 2 year old version just to make the plugin work.
For me, CouchDB seems to be great, but there is no easy way to use it with a standard Grails CRUD application... :-(

Transactions

I knew that it would take more than 7 weeks to write this blog :-)

Today I would like to focus on another aspect of those databases - how do they handle transactions?

For an RDBMS it is quite common to support transactions, but I haven't read about transactions in the 7db7w book yet that those key-value databases support them.

Let's write a test for transactions:

package sevendatabases

import static org.junit.Assert.*
import org.junit.*

class TransactionTests extends GroovyTestCase {

    static transactional = false; // 1
   
    void setUp() {
    }
   
    @Test
    void testSomething() {
        def numBooks = Book.count()
        Book.withTransaction { status ->
            new Book (name:'test',isbn:'test').save(flush:true,failOnError:true)
        }
        assert Book.count() == numBooks+1
        Book.withTransaction { status ->
            new Book (name:'test',isbn:'test').save(flush:true,failOnError:true)
            status.setRollbackOnly()
        }
        assert Book.count() == numBooks+1
    }
}

Have you noticed the line marked with "// 1"? In grails, integration tests are executed in a transactional context by default. This causes some problems while testing the transaction itself. So I disabled the default transactional context.

Now I run this test against all of my configured databases:

MS SQL Server: works
MySQL: works
Oracle Express: works
Riak: fail! No transactionManager bean configured

...ok. I could have used the overview chart from the book to get the same results. But this way I had to figure out how to test transactiosn ins Grails :-)

MS SQL Server Express

Shame on me! I forgot to include the MS SQL Server Express in my comparison!

But as any RDMS, MS SQL Server Express seems to be no problem. Installing it is easy, getting the jdbc-driver is easy, too. Connecting is a little bit hards, but you'll find enough resources on the web. Hint: I had to activate the TCP-Connection and h ad to disable the dynamic ports.

After getting the connection ready, the reast was easy - everything works as expected. Here is the updated comparison table:

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

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 :-)