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

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.zip.
  • 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?

Have fun,
Reiner

About Reiner

Born 1954 in Ratisbon (Bavaria, Germany), in 1976 punched cards at Berlin Technical University, caught hacking one of its mainframes by Horst Zuse (son of Konrad Zuse), started studying computer science and soon was offered a job whithin their computer department doing systems programming for IBM VM/370. While studying, jobbed around Germany at various places doing all sorts of things, then returned to Berlin to work at SRZ (computer aided typesetting). Never finished my master degree, but chose to take up self-employed work (which didn't turn me rich nor famous). Now working for a mid-sized software company within a very promising department as head of server software development.
This entry was posted in English, Grails, Hibernate. Bookmark the permalink.

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

  1. Evon Chilo says:

    I have to tell you that it’s hard to find your posts in google, i
    found this one on 13 spot, you should build some quality backlinks in order
    to rank your webiste, i know how to help you, just type in google
    – k2 seo tips

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s