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...