Configuring Grails to Ignore DDL on Specific Domains

Often building a Grails application that uses all of the cool bits of GORM with an existing database schema (such as that provided by a 3rd party product) can be pretty easy, but depending on the schema complexity it can also get ugly very fast. Recently, I finished an application that needed to both (a) map custom application objects to new tables, and (b) mash up existing data from vendor tables without modifying or compromising their schema in anyway.

The vendor schema in question is part of an ERP with 1,000s of tables, and an equally large number of indexes, constraints, triggers, and on and on. If you are an experienced Grails developer, you may think “just map objects to the existing tables and be done with it”. Okay, but what happens if like me you accidentally leave the dbCreate property on your data source set to “create-drop”, and during testing Grails drops a critical table with millions of records that cannot be lost? Thankfully, that happened in a development environment where its okay for those accidents to happen, but in a production environment those accidents are not acceptable. This event got me thinking: what is a responsible way to use Grails with existing tables, and guarantee that it will not try to change those schema objects? If only Grails mapping had a simple way to turn off DDL on specific domains, but still retain the awesomeness of DRY methods like list(…), named queries, and even inserting/updating records through save(…).

I present to you what I think is a reasonably elegant solution for this problem pulled together from reading through many forum posts, and finding tiny useful bits of information on other blogs. The trick is to configure your data source with a new customized Hibernate configuration class that extends the default Grails behaviors, but then overrides the DDL statement creation with our own actions to ignore specific tables.

This particular solution takes advantage of the fact that the baseline Grails edition of the Hibernate configuration class is instantiated with access to the Grails application instance. Therefore we can tie into Config.groovy, and not have to hard code table names.

package edu.fhda.grails.hibernate

import groovy.util.logging.Log4j
import org.codehaus.groovy.grails.commons.GrailsApplication
import org.codehaus.groovy.grails.orm.hibernate.cfg.GrailsAnnotationConfiguration
import org.hibernate.HibernateException
import org.hibernate.dialect.Dialect
import org.hibernate.tool.hbm2ddl.DatabaseMetadata

/**
 * Customized Hibernate configurator that extends the default GrailsAnnotationConfiguration to provide
 * a method for specifying a configurable list of tables to ignore during Grails startup. Very helpful
 * if you want to have domain objects that should be GORM managed alongside domain objects that
 * map to existing Banner database tables. This will prevent Grails from trying to create, update, or drop
 * Banner baseline schema objects.
 * @author Matt Rapczynski, rapczynskimatthew@fhda.edu
 */
@Log4j
class BannerHibernateConfiguration extends GrailsAnnotationConfiguration {

    private GrailsApplication grailsApplication

    @Override
    String[] generateDropSchemaScript(Dialect dialect) throws HibernateException {
        return checkAndRemoveIgnoredTables(super.generateDropSchemaScript(dialect));
    }

    @Override
    String[] generateSchemaCreationScript(Dialect dialect) throws HibernateException {
        return checkAndRemoveIgnoredTables(super.generateSchemaCreationScript(dialect));
    }

    @Override
    String[] generateSchemaUpdateScript(Dialect dialect, DatabaseMetadata databaseMetadata) throws HibernateException {
        return checkAndRemoveIgnoredTables(super.generateSchemaUpdateScript(dialect, databaseMetadata));
    }

    private String[] checkAndRemoveIgnoredTables(String[] sqlStatements) {
        // Create a collection of SQL statements for non-ignored tables
        ArrayList<String> validSqlStatements = new ArrayList<>()

        // Get the list of table names to ignored from the application config
        def ignoredTables = grailsApplication.config.edu.fhda.grails.hibernate.banner.ignoreTables as List<String>
        if(ignoredTables?.size() > 0) {
            log.debug "Using ignored tables configuration: ${ignoredTables}"

            // Iterate each SQL DML statement generated by GORM
            sqlStatements?.each { String sqlStatement ->
                boolean safeToAdd = true

                // Check to see if the statement references any ignored tables
                ignoredTables?.each { String tableName ->
                    if(sqlStatement.toLowerCase().contains("table ${tableName.toLowerCase()}")) {
                        /* NOTE: Log reference using this keyword prevents NullPointerExceptions */
                        this.log.debug "Bypassing ${tableName} for Grails domain mapping"
                        safeToAdd = false
                    }
                }

                // Did the statement pass the test?
                if(safeToAdd) {
                    validSqlStatements.add(sqlStatement)
                }
            }

            // Return a String array of validated statements
            return validSqlStatements.toArray() as String[]
        }
        return sqlStatements
    }

    @Override
    void setGrailsApplication(GrailsApplication application) {
        // Run superclass method
        super.setGrailsApplication(application)

        // Inject Grails application reference
        this.grailsApplication = application
    }

}

What’s going on?

• Hibernate generates DDL statements based on how objects are described to, and it will compile those statements into an array of Strings. We can override those generation method, have the superclass run its usual routine, but then modify the statements before returning them to be executed in the database.
• A private method checkAndRemoveIgnoredTables provides logic to inspect the SQL statements in the String array. Using a namespaced list of tables in Config.groovy, we can look for patterns where we find the text “table table_name” in a DDL statement, and then flag it to be ignored. This works great for finding statements such create table, drop table, alter table create index on table, etc. and so on.
• Once we have identified a set of SQL statements that are okay to run such as for domain objects that we do want Grails to manage, then that list is returned as a String[] and Hibernate continues its work.
• Tested with Grails 2.2.1
• Tested on an Oracle 11gR2 RDBMS

What does the Config.groovy file look like when you want to specify specific tables?

// Hibernate Configuration for Banner (one or more tables Grails should never try to modify)
edu.fhda.grails.hibernate.banner.ignoreTables = [
    "STVTERM"
]

And the DataSource.grooy configuration; all you need to do is add (or adjust) one line by specifying new class name for the Hibernate configuration:

// Example data source configuration
dataSource {
    pooled = true
    driverClassName = "oracle.jdbc.OracleDriver"
    configClass = edu.fhda.grails.hibernate.BannerHibernateConfiguration
}

And that is all! Go through the process of adding this to an application that needs it for the first time, and you should find that this is a reasonably easy solution to work with. I have used it for an application in production today with great success. The only way this gets easier is if the Grails team adds this functionality to a future Grails release where we can turn DDL on or off with a true or false in the table mapping.

In the intro, I focused on the challenge of mixing custom tables with mapping existing tables. A scenario I skipped over is if you wanted to map a domain to an SQL view as opposed to directly to the table. This solution works really well for views too – and I should know because that is part of the reason for why I explored this challenge in the first place. Why should Grails need to do DDL on an existing view? It doesn’t need to, and you can use this approach to skip over views in your database.

During my initial research, I read some commentary on Stack Overflow from another developer who felt mapping Grails domains to views was “overkill”, but I think that needs to depend on what you are trying to do with a view. In my specific case, I am working on building applications for an ERP system where it is not uncommon to write queries with 6, 10, and even 12 table joins. Turning those joins into individual domains, and then navigating the rocky waters of composite foreign keys is tricky business (maybe even impossible!). It is simply the name of the game when you are working with a gigantic system developed over a period longer than 15 years.

I use views to mask that complexity in a manageable way, and still retain the benefits of GORM for clean business logic. With the solution above, I can ensure my views are untouched and that Grails does not try to create indexes or constraints on things that do not need them. Bonus Suggestion: I have not frequently worked with relational databases other than Oracle, but in the Oracle ecosystem I use “INSTEAD OF” triggers for retaining the ability to do inserts and updates through views from Grails domains.

Anyways, that’s that. I have posted the code snippets above as a GitHub Gist. If you have some nice mods to make, please fork the gist so the community can benefit from any improvements. Enjoy, and I hope it serves you well as it has for me.

See this example as a GitHub Gist