Grails and Microsoft SQL Server - Not so painless when using Unique Constraints - A Pragmatic Work-Around

Posted on December 18th, 2011 by Reiner.
Categories: Hibernate, Grails, English.

When porting your Grails application from just about any database to MS SQL Server, four areas come to my mind that require your immediate attention:

  • Primary key generation strategy (not covered here)
  • Case sensitivity and collation issues (not covered here)
  • Storage and retrieval of Unicode data (not covered here)
  • Handling of unique constraints on columns that allow nulls

With MS SQL Server you’ll notice your unique constraints defined within Grails domain classes may no longer work as expected if null values are allowed (either explicitly or when using table per hierarchy inheritance).

Today I’ll present a work-around for MS SQL Server 2008 that does not require any manual intervention.

When using unique clauses within the constraints sections of your domain classes, Grails will do both:

  • Perform lookups on every insert or update in order to be able to create meaningful messages within the errors collection, and
  • by virtue of Hibernate hbm2ddl, create unique constraints within your database to enforce uniqueness even when validation is bypassed.

What’s so special about MS SQL Server?

Microsoft SQL Server has always been special when it comes to null values. There are several options (suspiciously named ANSI_xxx) controlling the handling of null values, e.g. whether null = null yields true a.s.o., but all those fail to take any effect on unique constraints.

With Microsoft SQL Server unique constraints treat null values just as if they were ordinary values - a uniquely constrained column does at most allow for a single row to have the value null. This appears to be in violation of ANSI SQL standards and there are (as yet futile) requests to (optionally) alter MS SQL Server behavior.

Depending on your business rules, not-null properties (e.g. nullable:false, blank:false, unique:true) may be appropriate. But watch out for object inheritance when using table-per-hierarchy mapping (Grails default). As a single table is used to store different kinds of objects, a property defined as nullable:false within a sub class must be mapped to a nullable table column as other sub classes do not share this property (i.e. they have no value for this particular column at all). And if such a property has been constrained by unique:true, your application is bound to fail with SQL Server, as it will at most allow a single row where the value of this property is null.

There are several work-arounds in the wild, most of them exceedingly awkward to handle and maintain - with one notable exception: Starting with MS SQL Server 2008 indexes can be filtered, i.e. a (simple) where clause can be applied to the definition of an index. Thus a unique index that ignores null values can be used in place of a unique constraint to enforce uniqueness of non-null values.

What you’ll need to run or use the Grails MsSqlConstraints project sample code

  • The Grails demo project MsSqlConstraints (Select File, Download original to get hold of the .zip on your PC).
  • MS SQL Server 2008 (express edition will do, I used 2008 R2) allowing SQL Server authentication (i.e. user and password). The demo project accesses a database named demo at your local SQL Server using demo for both login and password. Best to have a look at DataSource.groovy first.
  • Grails 1.3.7
  • Java 6 - Java 6 Update 29 has a bug that prevents it to connect to MS SQL Server 2008 R2. Either upgrade to Java 6 Update 30 or replace jsse.jar with the one included in Update 30 (as I had to because Update 30 was not yet available from Apple).
  • Springsource Tool Suite 2.8+ or IntelliJ 10+ (not really required, but allow attached demo project to be run out-of-the-box).
  • Disclaimer: Note that the demo project is provided as is without any warranty or obligation and (to give you a head start) as well includes a copy of the Microsoft JDBC Type 4 Driver Version 3 which is to be used with this demo project only and whose use is restricted as explained in MICROSOFT SQL SERVER JDBC DRIVER REDISTRIBUTION LICENSE.

How to change unique constraints to unique indexes?

Alter database DDL within BootStrap.groovy

  • Having looked into Hibernate 3.3.1 code (as used by Grails 1.3.7) it appears hard if not impossible to change the DDL generated by Hibernate’s hbm2ddl routines. Thus I resorted to altering table definitions within the BootStrap.groovy code. Please note that the code has been hacked away in a hurry and might fail with advanced usage patterns such as tables belonging to different owners a.s.o. For us it worked out-of-the-box, but use at your own risk.
  • The code within BootStrap.groovy is straight forward. It queries constraint data from MS SQL Server information schema and replaces unique constraints that encompass nullable columns with unique filtered indexes.

Supply Hibernate dialect for MS SQL Server 2008

  • When a unique index violation occurs, an Exception different from the one when violating a unique constraint will be thrown. MinimalSQLServer2008Dialect contains code to map unique index violations to DataIntegrityViolationExceptions just as if a constraint violation had occurred.
  • The demo project has been implemented using Microsoft JDBC Type 4 Driver for SQL Server. This driver returns JDBC type codes (e.g. NVARCHAR for certain information schema columns) Hibernate 3.3 fails to understand. Thus the MinimalSQLServer2008Dialect supplied with this demo project as well provides minimal type declarations in order to successfully query SQL Server information schemas. Another post within this blog will focus on both how to store Unicode strings and how to take advantage of MS SQL Server BIT and xxx(MAX) columns using an enhanced Hibernate dialect.
  • The code referenced from DataSource.groovy is contained within class demo.MinimalSQLServer2008Dialect which derives from org.hibernate.dialect.SQLServerDialect. It defines NCHAR and NVARCHAR data types and translates unique index violations to (Spring) ConstraintViolationExceptions.

How to run the code?

  • Get the Grails demo project MsSqlConstraints (Select File, Download original to get hold of the .zip on your PC).
  • Running it using grails test-app integration:spock will perform Spock test MsSqlConstraintsSpec.groovy and produce output similar to this.

Have fun,
Reiner

2 comments.

How to Deploy a Grails Application to JBoss 5

Posted on March 6th, 2010 by Reiner.
Categories: Hibernate, Grails, English.

As outlined at Grails - How to Use Native Server Logging Configuration (e.g. Tomcat GlassFish JBoss), it may be advisable to remove any conflicting logging implementation when deploying a Grails application into a production environment. I’ll give a full source example on how to achieve this and how to work-around an issue which prevents a Grails 1.2.0 and 1.2.1 applications from starting.

Unfortunately, each server provides a distinct set of logging frameworks. Still worse, servers can be reconfigured to use alternate logging frameworks (e.g. JBoss can be configured to use Java Logging instead of its default Log4J implementation and Tomcat 6 can be configured to use Log4J instead of its default Java Logging implementation).

Thus you either have to prepare a war which has been set-up for a specific target environment or completely remove any logging implementation from your Grails war and add missing jars to the lib directory of your target server instead. Which one to choose may depend on whether access to the server configuration is permitted (e.g. its lib directory), how many different servers your application is to be deployed to and how hard it is for your build process to reliably produce variants specifically tailored for a particular deployment site.

Before presenting the code for a deployment to JBoss 5.1.0GA, let’s have a look at Grails’ logging architecture. In its infancy Grails just used Apache Commons Logging (JCL) to provide an abstraction level and Log4J to implement the actual logging. Although even now, all log instances injected into Grails applications still implement org.appache.commons.logging.Log, Grails no longer uses JCL, but meanwhile has moved on to SLF4J:

Grails logging architecture

Starting with Grails 1.2, an additional bridge jul-to-slf4j which delegates Java Logging (Jul) to Slf4j is enabled by setting grails.logging.jul.usebridge = true. Be warned though, that for performance reasons this bridge should not be used when expecting heavy logging.

Now for the good news: With JBoss 5, all of the above (but excluding jul-to-slf4j) has already been integrated into the server configuration and libraries. In order to deploy a Grails application to JBoss 5, we’ll just need to get rid of the jars within your war and prevent Grails from trying to configure the logging. How to achieve this?

Within grails-app/conf/BuildConfig.groovy we’ll remove offending jars when building the war:

// TODO JBOSS - Remove own log4j and use the one supplied by JBoss instead
grails.war.resources = {stagingDir ->
    def toRemove = [
          "$stagingDir/WEB-INF/lib/log4j-1.2.14.jar", // log4j supplied by JBoss
          "$stagingDir/WEB-INF/lib/log4j-1.2.15.jar", // log4j supplied by JBoss
          "$stagingDir/WEB-INF/classes/log4j.properties", // logging conf done in JBoss only
          "$stagingDir/WEB-INF/lib/slf4j-api-1.5.6.jar", // slf4j supplied by JBoss 5+
          "$stagingDir/WEB-INF/lib/slf4j-api-1.5.8.jar", // slf4j supplied by JBoss 5+
          "$stagingDir/WEB-INF/lib/slf4j-log4j12-1.5.6.jar", // slf4j supplied by JBoss 5+
          "$stagingDir/WEB-INF/lib/slf4j-log4j12-1.5.8.jar", // slf4j supplied by JBoss 5+
          "$stagingDir/WEB-INF/lib/jcl-over-slf4j-1.5.6.jar", // jcl supplied by JBoss as well
          "$stagingDir/WEB-INF/lib/jcl-over-slf4j-1.5.8.jar", // jcl supplied by JBoss as well
        // see also Config.grails.logging.jul.usebridge - shouldn't be used
        //     http://www.slf4j.org/legacy.html#jul-to-slf4j
        //          "$stagingDir/WEB-INF/lib/jul-to-slf4j-1.5.6.jar",
        //          "$stagingDir/WEB-INF/lib/jul-to-slf4j-1.5.8.jar",
        // you might want to remove JDBC drivers when using server supplied JNDI...
        //          "$stagingDir/WEB-INF/lib/hsqldb-1.8.0.5.jar",
    ].each {
        delete(file: it)
    }
}

Within scripts/_Events.groovy we’ll disable Grails logging configuration components:

import groovy.xml.StreamingMarkupBuilder

/**
 * TODO JBOSS - Remove log4j configuration stuff (when running with JBoss or GlassFish a.s.o)
 */
eventWebXmlEnd = {String tmpfile ->

    def root = new XmlSlurper().parse(webXmlFile)

    // When running with JBoss (or GlassFish a.s.o) remove log4j configuration stuff
    def log4j = root.listener.findAll {node ->
        node.'listener-class'.text() == 'org.codehaus.groovy.grails.web.util.Log4jConfigListener'
    }
    log4j.replaceNode {}

    def log4jFile = root.'context-param'.findAll {node ->
        node.'param-name'.text() == 'log4jConfigLocation'
    }
    log4jFile.replaceNode {}

    webXmlFile.text = new StreamingMarkupBuilder().bind {
        mkp.declareNamespace("": "http://java.sun.com/xml/ns/j2ee")
        mkp.yield(root)
    }
}

You are now set to control logging within JBoss jboss-log4j.xml.

If you’re using Grails 1.2.0 or 1.2.1, JBoss still refuses to deploy your war. This is caused by version conflicts within Hibernate jars (used to be work-arounded by deleting all Hibernate jars from JBoss lib directory) and they have been fixed by GRAILS-5606 for (not yet released) Grails 1.2.2.

Graeme Rocher as well supplies a work-around for 1.2.x:

Fixed in latest hibernate plugin 1.3.0.BUILD-SNAPSHOT

It seems in order to use the latest version of Hibernate on JBoss you must include Hibernate validator otherwise you run into this classloading error. This means the hibernate plugin is forced to include hibernate-validator as a dependency even if we don’t use it which is a pretty annoying.

Anyway you can fix this yourselves in 1.2.x by adding the following dependency to BuildConfig.groovy:

runtime('org.hibernate:hibernate-validator:3.1.0.GA') {
    excludes 'sl4j-api', 'hibernate.core',
             'hibernate-commons-annotations', 'hibernate-entitymanager'
    }

Be sure to uncomment the jboss maven repository so the dependency resolvers

This work-around is included within sample sources. Just search for TODO tags.

Links:

10 comments.

How to persuade Hibernate to accept Nullable Timestamp Columns for Optimistic Locking

Posted on January 13th, 2010 by Reiner.
Categories: Hibernate, Java, Grails, English.

Working on a Grails project that needs to access a legacy database, it proved quite time-consuming to implement optimistic locking in a way that can be handled by Hibernate. Optimistic locking used to be implemented within (legacy) application code and should now be handled by Hibernate completely on its own. For each entity, the legacy schema uses two nullable columns to both trace creation and last updated times as well as to provide optimistic locking capabilities. TS_INSERT holds the Timestamp the entity has been created and TS_UPDATE (initially null) records subsequent mutations to an entity. Just disabling optimistic locking (as well being advertised here) keeps away run time errors but results in an application that cannot meet production quality demands without additional (awkward) application code.

Annotating the TS_UPDATE column with @Version almost did the trick, except it was unable to successfully update entities that had not yet been updated (TS_UPDATE is null). The reason for this behavior is burried within method toStatementString Hibernate Update- and Delete-Code that use TS_UPDATE=? as part of the sql (prepared) statements in order to check for matching old version values. Of course, this condition will always yield false for null values (remember: null = anything including null = null always yields false).

How to cause TS_UPDATE=? to yield true?

Short of changing Hibernate source code I designed a work-around, that effectively changes the TS_UPDATE=? fragment so that it yields true, even for null values. The work-around replaces the TS_UPDATE=? with COALESCE(TS_UPDATE,{ts ‘1900-01-01 00:00:00′})=COALESCE(?,{ts ‘1900-01-01 00:00:00′}). I use the COALESCE function, because it is specified by Ansi SQL 92 Standard and thus should be implemented by most database vendors.

How to alter Hibernate sql statements?

Hibernate provides a rather sophisticated set of interceptor methods, that will be called (back) at certain life cycle stages. Using the onPrepareStatement event, arbitrary changes can be applied to the sql statements that are then sent to JDBC for execution. This is what my interceptor looks like (note that it looks for and… as the version condition will always be appended to the primary key condition - todo: this still needs to be verified for persisting collections):

package com.company.fw.hibernate.util.centura;

import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.hibernate.EmptyInterceptor;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * Fixes a shortcoming within Hibernate: Hibernate cannot handle NULL values
 * within optimistic version columns. The where clause will always include
 * e.g. TS_UPDATE=?. We'll try to fix this by changing the sql to e.g.
 * COALESCE(TS_UPDATE,{ts '1900-01-01 00:00:00'})=COALESCE(?,{ts '1900-01-01 00:00:00'}),
 * so that NULL values match.
 */
public class DBcenturaInterceptor extends EmptyInterceptor {

    private static final String PS = "(and) (ts_update)=?";
    private static final String RS = "$1 COALESCE($2,{ts '1900-01-01 00:00:00'})=COALESCE(?,{ts '1900-01-01 00:00:00'})";
    private static final Pattern P = Pattern.compile(PS, Pattern.CASE_INSENSITIVE);

    private final static Logger LOG = LoggerFactory.getLogger(DBcenturaInterceptor.class);

    public DBcenturaInterceptor() {
        super();
        LOG.info("new DBcenturaInterceptor()");
    }

    /**
     * @param sql the sql to be prepared
     * @return and TS_UPDATE=? replaced by
     *         and COALESCE(TS_UPDATE,{ts '1900-01-01 00:00:00'})=COALESCE(?,{ts '1900-01-01 00:00:00'})
     */
    @Override
    public String onPrepareStatement(final String sql) {
        final Matcher m = P.matcher(sql);
        final String result = m.replaceAll(RS);
        if (LOG.isTraceEnabled() && !result.equals(sql)) {
            LOG.trace("onPrepareStatement(" + sql + ") = " + result);
        }
        return result;
    }

}

How to activate the interceptor?

When creating a session, an interceptor can by passed to the Hibernate SessionFactory.openSession(…). As my application is a Grails application, I choose to provide my Interceptor by passing it to the Grails Hibernate session factory, so my interceptor will be used for all and every Hibernate session created within my Grails application. Starting from Grails 1.2, just a single statement is needed within grails-app/conf/spring/resources.groovy:

import com.company.fw.hibernate.util.centura.DBcenturaInterceptor

// Place your Spring DSL code here
beans = {
  entityInterceptor(com.company.fw.hibernate.util.centura.DBcenturaInterceptor)
}

For native Spring applications, have a look at Using a Hibernate Interceptor To Set Audit Trail Properties.

That’s all, take care and have fun :-)

0 comments.

Hibernate Reverse Engineering Tool does not create @TYPE Annotation for User Types within POJOs

Posted on January 9th, 2010 by Reiner.
Categories: Hibernate, Java, English.

Working on a Grails project that needs to access a legacy database, I’ve used the Hibernate Tools for Eclipse and Ant to create annotated EJB3 entity classes.

As the database model uses some peculiar concepts (e.g. char(1) for booleans), I ‘ve created Hibernate custom types that properly map database types to Java types. The Hibernate Reveng Tool can be configured to map certain database columns to custom types, but it fails to create the @Type annotation that is required for custom user types. And as numerous reverse engineering cycle are about to be executed, I didn’t want to fix the generated sources by hand.

There already exists a bug for this problem, but there has been no attempt to fix it during the past two years :-( Thus I’ve designed a work-around and documented it at Issue HBX-849 - Tools does not insert @Type in POJOs for user types defined in reveng.xml :-)

0 comments.