Jakob Külzer Ninja Coding Monkey goes Canada

12Dec/09Off

Liquibase Incremental DB Diff

After discussing this on the Groovy and Grails Exchange, I finally put together a new version of the db diff script that I've mentioned in an earlier post. I couldn't find the original version that I've written a couple of months ago, so I hacked together a new one. It's far from perfect, efficient or beautiful, but it does what it is supposed to.

So, what's the idea behind this script? Everybody that uses Liquibase (if you are not, check out my blog post about what Liquibase is and how to use it) knows how painful it is to update your changelog.xml. Liquibase already gives you a great tool to start with, the db-diff command. However, it is hardcoded to diff the current environment's database against the test database. This is annoying as you'll have to modify your datasources and keep the database schema updated manually. But fear not, here is my (surprisingly) easy solution.

I've created a simple script (or rather, I took the db-diff script and hacked it), which I've called incremental DB diff (all the other cool names are already taken...) and what it does is the following:

  1. update the schema in the db-diff (or whatever you call it) database using the changelog.xml
  2. diff the database of the current environment against this database and output the diff as Liquibase XML

So, how is this a good thing? Easy: this little script will automatically create a target database to diff against using the changelog.xml which is good, as the changelog.xml represents your last migration status. And as it uses a separate database it doesn't influence the test database.

After running the script just put the output in the changelog.xml and you're done.

All you have to do to use this is to drop the file into your /scripts/ directory, create a new database and an according datasource entry for the environment "dbdiff" like this:

	dbdiff {
		dataSource {
			driverClassName = "com.mysql.jdbc.Driver"
			dbCreate = "create-drop"
			url = "jdbc:mysql://localhost/foo_dbdiff"
			username = "foo"
			password = "bar"
		}
	}

Download DbDiffIncremental.groovy. Drop me a mail or a comment if you have questions.

25Sep/08Off

LiquiBase, addForeignKeyConstraint and Column Types do not Match

Today I encountered some weird errors while migrating my database using LiquiBase. It took me quite some time to figure out what was wrong as no one has encountered this error before. So, here's the problem and the solution. ;-)

Ugly but simple example: I have two domain classes, say Book and Author and a Book has an Author. How Author looks like is not important. Book looks like this

class Book {
  Author author
}

Due to changes in the requirements the author needs to be optional thus nullable. With updated constraints the Book class looks like this:

class Book {
  Author author
  static constraints = { author(nullable:true) }
}

So upgrading the database with LiquiBase should be as easy as adding a couple of lines to the changelog file:

<changeSet author="jakob" id="foo-1">
  <dropForeignKeyConstraint baseTableName="BOOK" constraintName="FK2E3AE9CD85EDFA"/>
  <dropNotNullConstraint tableName="BOOK" columnName="AUTHOR_ID"/>
  <addForeignKeyConstraint baseColumnNames="AUTHOR_ID"
    baseTableName="BOOK" constraintName="FK2E3AE9CD85EDFA"
    deferrable="false" initiallyDeferred="false"
    referencedColumnNames="ID" referencedTableName="AUTHOR"/>
</changeSet>

Basically this changeset drops the foreign key constraint on AUTHOR_ID, drops the not-null-constraints and re-adds the foreign key constraint which is required to handle the association between the Book and the Author class.

Unfortunately the addForeignKeyConstraint will cause an error, something like this:

Caused by: java.sql.SQLException: Column types do not match in statement [ALTER TABLE...

The reason why this happens is that: when dropping the not-null constraint the type of the AUTHOR_ID column is re-set to something unusable. Inspecting the database with the HSQLDB Databasemanager showed that the type of the is set to NULL which is weird. The solution to this is easy however. All you need to do is to provide LiquiBase the type with the columnDataType attribute of the column in the dropNotNullConstraint element:

<changeSet author="jakob" id="foo-1">
  <dropForeignKeyConstraint baseTableName="BOOK" constraintName="FK2E3AE9CD85EDFA"/>
  <dropNotNullConstraint tableName="BOOK" columnName="AUTHOR_ID"/>
  <addForeignKeyConstraint baseColumnNames="AUTHOR_ID" columnDataType="BIGINT" 
    baseTableName="BOOK" constraintName="FK2E3AE9CD85EDFA"
    deferrable="false" initiallyDeferred="false"
    referencedColumnNames="ID" referencedTableName="AUTHOR"/>
</changeSet>

With this in place it will work like a charm.

23Sep/08Off

Updated Post about Grails and LiquiBase

If you have read my post about Grails and LiquiBase please note that I updated it today. I added new contents and corrected the section about db-diff. Make sure you have a look at it ;-) .

19Sep/08Off

Grails and LiquiBase – How to use

In my current project I'm now at a point where the application has come pretty far but is still far from finished. The domain model is not perfect, but very stable at this point -- changes however, are still possible.

To get going with the application we are planning to perform tests with some real data. The problem at this point is that entering real data into a database that might be wiped due to changes in the domain model is a very tedious task, at least for the person responsible for entering the data. And the reason for running such a test is to find problems with the domain model which lead to the dreaded changes in the domain model. This cat bites her own tail...

As wiping out the database and re-entering the data is not an option at this point, I decided to look into LiquiBase which as been sitting on my delicious list for quite a while.

This post is an attempt to write down my experiences and learned lessons with LiquiBase. The documentation of LiquiBase is technically complete and extensive but lacks more information about how to use it in a real project. This is an attempt to create a guide on how to leverage LiquiBase in a real world project.