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