Thursday, December 9, 2010

Using Liquibase for managing Database Migrations in grails


Here are some notes I recently sent to a developer about our usage LiquiBase for database migrations. I was just about to send it to a second person and decided it was time to publish the information so more people could get some use out of it as well. We use Liquibase to manage database migrations for Grails applications using two plugins for Grails: Liquibase and LiquibaseRunner. Note however that LiquiBase is NOT specific to Grails – you can use it on any java project that needs to manage DB changes.

Changelog structure:
We typically set up a root changelog that then references other changelog files. We create an “initial” changelog file plus one per release. It works well for keeping the changelog files relatively small and also keeps things into nice manageable chunks. It looks something like this (where all the files live in the grails-app/migrations directory):

changelog.xml:
<?xml version="1.0" encoding="UTF-8"?>


<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog/1.9"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog/1.9
         http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-1.9.xsd">


    <include file="000-initial-changelog.xml" relativeToChangelogFile="true"/>
    <include file="001-changelog-r1.xml" relativeToChangelogFile="true"/>
    <include file="002-changelog-r2.xml" relativeToChangelogFile="true"/>


</databaseChangeLog>

The 000, 001, 002 naming convention isn’t required, it’s a leftover artifact from using autobase but we also like it in that it keeps the files in order if you’re looking at them in a file system. The “relativeToChangelogFile” works for us because we use the liquibase-runner plugin which copies all the migrations to the classes/migrations dir in _Events.groovy (at package end).
Typically we develop new applications using the HSQL database up until the point at which we deploy the app as a WAR file on a tomcat instance. Once we get there, we start an initial changelog. The sooner you start one the better, but we have also had success retro-fitting a changelog on to apps that didn’t start out with one. It requires a couple manual steps, but as you know the Liquibase plugin has some handy scripts for helping out with that (generate-changelog, I’m looking at you).

Types – use generic types 
When generating the changelog, Liquibase uses whatever types it finds in the DB – that is, it doesn’t convert things to generic types. So usually I go through a generated changelog and switch types to the generic ones. For example, when using MySql, I switch “BIT” to “BOOLEAN” and “TINYCLOB” to “CLOB”, etc. The list of generic types Liquibase supports can be found on the “Column” page in the manual: http://www.liquibase.org/manual/column This is only required if you want your changeset to be able to work against multiple different database engines.

MySql Innodb
The generated changelog doesn’t appear to capture the type of table/database in MySql. So even if you generate a changelog from a DB that was created with a dialect of org.hibernate.dialect.MySQLInnoDBDialect it doesn’t reflect that. We learned this the hard way when we deployed an app to a MySql database that was configured as MyISAM by default and all our tables turned out to be MyISAM instead of InnoDB. Now, we always add a “modifySql” tag to the end of every “createTable”, like this:

<changeSet author="mjhugo (generated)" id="1288615205809-4">
    <createTable tableName="security_role">
        <column autoIncrement="true" name="id" type="BIGINT">
            <constraints nullable="false" primaryKey="true"/>
        </column>
        <column name="version" type="BIGINT">
            <constraints nullable="false"/>
        </column>
        <column name="authority" type="VARCHAR(255)">
            <constraints nullable="false"/>
        </column>
    </createTable>
    <modifySql dbms="mysql">
        <append value=" engine innodb"/>
    </modifySql>
</changeSet>

Running migrations automatically when the application starts
We use the liquibase-runner plugin on most of our projects to automatically run all the migrations when the WAR file starts up. It’s also useful in development because once a changeset is checked in, every developer database is automatically up to date with the domain classes. An interesting side effect of this is that once a change set has been checked in, we consider it to be written in stone – we don’t necessarily know where that changeset has already been run. So if we make a mistake in a changeset, we create a new changeset to fix the mistake, rather than editing the changeset that had a mistake in the first place.

Tuesday, June 22, 2010

Grails pageScope variable in GSPs and TagLibraries


Grails pageScope variable is available to you in Tag Libraries and GSPs and contains a wealth of state information. For instance, you can set an attribute in the model in a controller and then reference it in a TagLibrary through the pageScope variable without having to pass it through the tag attributes

Controller:

def myAction = {
    //...
    boolean readOnly = true
    render(view: 'edit', model: [bookInstance:book, readOnly:readOnly])
}

GSP:

    <g:myInputFieldTag name="title" value="${bookInstance.title}"/>

TagLibrary

def myInputFieldTag = { attrs->
    if (pageScope.readOnly){
        out << attrs.value
    } else {
        out << //...
    }
}

You can also use it within a GSP or to get access to variables you have set in a GSP or to store variables in page scope in a tag library.
Docs:
http://grails.org/doc/latest/ref/Tag%20Libraries/pageScope.html
and
http://grails.org/1.0-RC1+Release+Notes#Improved%20support%20for%20scopes

Thursday, June 17, 2010

Grails, p6spy and Sql Profiler


There are several ways to have Grails log SQL statements that Hibernate is sending to the database under the covers of GORM.  The first is the loggingSql flag in DataSource.groovy:

loggingSql=true

This will show you the prepared statement that Hibernate is generating, like this:

SELECT
        this_.id AS id6_0_,
        this_.version AS version6_0_,
        this_.authority AS authority6_0_,
        this_.description AS descript4_6_0_
    FROM
        role this_
    WHERE
        this_.authority=?


This is a good first step – but what if I want to see the parameter that is being sent as well?  You could turn logging for the org.hibernate.type package to TRACE level:

//in Config.groovy
log4j = {
    trace  'org.hibernate.type'
}

YIKES! That gets REALLY noisy, really fast:

Hibernate:
    select
        this_.id as id6_0_,
        this_.version as version6_0_,
        this_.authority as authority6_0_,
        this_.description as descript4_6_0_
    from
        role this_
    where
        this_.authority=?
TRACE type.StringType  - binding 'ROLE_USER' to parameter: 1
TRACE type.LongType  - returning '1' as column: id6_0_
TRACE type.LongType  - returning '1' as column: version6_0_
TRACE type.StringType  - returning 'ROLE_USER' as column: authority6_0_
TRACE type.StringType  - returning 'Default user role' as column: descript4_6_0_

Besides, what I really want is the ability to copy and paste a query, with the parameters bound, directly into a SQL editor to execute the query and see the results myself. Enter the p6spy plugin for Grails:

grails install-plugin p6spy

Swap the driver in your DataSource.groovy file from your driver to the p6spy driver (the plugin will automatically add this, you just need to comment it in):

environments {
  development {
    dataSource {
      //driverClassName = "org.hsqldb.jdbcDriver"
      driverClassName = "com.p6spy.engine.spy.P6SpyDriver"
    }
  }
}

Fire up your Grails application – p6spy will create a spy.log file in your application directory and log a pipe delimited line – the last column has the actual SQL being executed, with the parameters!

select this_.id as id2_0_, this_.version as version2_0_, this_.authority as authority2_0_, this_.description as descript4_2_0_ from role this_ where this_.authority='ROLE_USER'

This is a great start, and most people stop here. But sometimes there are so many queries being executed that it’s difficult to wade through spy.log to find the one you’re looking for. Thanks to a tip from the excellent Grails In Action book that was recently released – SQL Profiler can help!

Download SQL Profiler (http://sourceforge.net/projects/sqlprofiler) and add these lines to grails-app/conf/spy.properties:

log4j.appender.SQLPROFILER_CLIENT=org.apache.log4j.net.SocketAppender
log4j.appender.SQLPROFILER_CLIENT.RemoteHost=localhost
log4j.appender.SQLPROFILER_CLIENT.Port=4445
log4j.appender.SQLPROFILER_CLIENT.LocationInfo=true


Add the appender to the logger definition:

log4j.logger.p6spy=INFO,STDOUT,SQLPROFILER_CLIENT

Finally, make sure the Log4j Appender is enabled:

#specifies the appender to use for logging
appender=com.p6spy.engine.logging.appender.Log4jLogger
#appender=com.p6spy.engine.logging.appender.StdoutLogger
#appender=com.p6spy.engine.logging.appender.FileLogger

Then, in the directory in which you downloaded the SQL Profiler jar, launch the GUI with this line:

java -jar sqlprofiler.jar

Now, p6spy will log SQL to the SQL Profiler GUI! You can use it to profile SQL statements, but you can also use the ‘Logger’ tab to filter the log. I click the little trash can icon in the upper left to clear the log, run a test in the application, then filter the results using the filter fields available. For instance, if I only want to see queries related to the ‘ROLE’ table, just put the word ‘role’ in the Filter message text box, and you’ll only see queries related to that table. It’s a great way to get straight at a particular SQL query when you’re drowning in tons and tons of SQL messages.