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