Liz Douglass

Posts Tagged ‘HSQLDB

LiquiBase: Inserting data from a CSV file

with one comment

BA couple of weeks ago Nick pointed out to me that it was possible to insert data into a LiquiBase change set from a CSV file. This Load Data feature was added in version 1.7.0 of LiquiBase and is particularly useful for two reasons:

  1. It keeps the length of the changelog file down
  2. It’s easier to edit a CSV file than it is an XML file, especially when you’re adding a large amount of data to a table

While this is a great option there are a couple of things worth keeping an eye out for if you go down this path…

First, dates:
As I’ve written about before, we are using HSQLDB for development but need to support Oracle. We’ve had relatively few problems in working with both, except for the date compatibility issue.

When inserting XML date data into table entries we were using SYSDATE as the value. This works fine in XML, but does not work when loading data from a CSV file. In the CSV case LiquiBase attempts to create a formatted timestamp from the ‘SYSDATE’ string. We tried a number of ways to enter dates that were compatible in both HSQLDB and Oracle, including adding extra columns in the CSV to cater for the different databases (see below). We also tried updating the table date columns after inserting the entries using the CSV file, but this was messy because those columns have not-null constraints.

The solution to the date compatibility problem turns out is very simple (drumroll) – just define the date in one of the date formats in the Liquibase.util.ISODateFormat class (below) and LiquiBase will generate a database specific date for you!

yyyy-MM-dd HH:mm:ss

Second, columns:

The documentation for the CSV load data feature gives the impression that you need to map each column in the CSV to one in the table. In fact LiquiBase will map the CSV file column headings to table column headings automatically. You only need to map the columns that have a different heading in the CSV to that in the table. Note however that we found it was necessary to map the columns containing date data, even if the headings were the same:

    <loadData tableName="My_Table" file="My_File.csv">
        <column name="CREATED_DATE" header="CREATED_DATE" type="DATE"/>

Unfortunately the automatic mapping also means that you cannot have extra columns in the CSV.


Written by lizdouglass

February 1, 2009 at 11:06 am

Posted in Java

Tagged with , , ,

Things I like about Buildr

with 3 comments

A few months ago the idea of myself writing such words (“Things I like about Buildr“) seemed very unlikely and I dare say that my project buddies may be surprised at the statement. Buildr, for those unfamiliar, is a build tool for Java applications that is written in Ruby. It’s key benefit is its concise and extensible nature. A bit of the wow factor of Buildr is a bit lost on me because I’ve not yet used Maven and I’ve only used Ant in some pretty straightforward ways… and I’d not used Rake prior to using Buildr. (So a strong starting point I’m sure you’d agree. Hhhmm, anyway…)

Our project buildfile started off looking very much like the example in the Buildr documentation, but has since grown significantly in functionality but not proportionally in size. We have complimented the Buildr functionality with handfuls of Rake tasks that perform other bits and pieces associated with our build, such as:

  • Deploying a WAR package to a Tomcat server
  • Starting and stopping database and Selenium servers
  • Updating and rolling back our HSQLDB and Oracle databases using LiquiBase
  • Checking the contents of the build artifacts produced using the Buildr check functionality

The time we’ve spent trawling through the documentation and getting ankle deep in the source has paid off. I want to capture how we’ve set up some of these build activities. So without further introduction and in no particular order, here are some of my favourites (drumroll):

1. Pretty packaging

In my earlier post I described how we used One-JAR to create a single JAR containing all the bits required to update/rollback a database using LiquiBase. Below is the sub-project task that is used to package the lot. The nicest things about this sub-project are how easily we’ve packaged the included JARs, and how easily we’ve added entries into the manifest file:

        define "assembly" do
            package(:jar).path("lib").tap do | p |
                p.include artifact(LIQUIBASE_JAR).to_s
                p.include artifact(HSQLDB_CLIENT_JAR).to_s
            package(:jar).path("main").tap do | p |
                p.include project('database-migration').package(:jar)
            package(:jar).include file('../changelog.xml')
            package(:jar).with :manifest=>manifest.merge(

2. Delightful dependencies

Some of our rake tasks require us to depend on the existence of a WAR or JAR that has been packaged in one of our buildfile sub-projects. For example, the task that deploys to a Tomcat server depends on a WAR file being generated from one of our sub-projects. Thanks to Buildr, you can do something like this:

task :foo => ['rake:MyProject:MySubProject:package'] do

Note that it is also possible and extemely easy to refer to an artifact that one of your projects has created by using syntax like this:


3. Fabulous filtering

We are using HSQLDB for development because it’s really light. Our application also needs to support Oracle though, and hence we wanted to run some of our acceptance tests using both databases. To achieve this we created another Buildr sub-project that runs some of the tests against Oracle (we already had the one that runs all the acceptance tests with HSQLDB). There are two things about this sub-project (below) that impress me. The first is that we reuse code using Buildr’s filter task. The second is how easily we can run only a subset of the tests using ‘test.include’:

            define 'my-sub-project' do
                test.compile.with _('target/classes')
                test.using :properties => { :my_property => property_value }
                test.include '*only_these_tests*'

4. Cheeky checking

Buildr callbacks can be used to extend projects. We’ve combined the after-define callback with the handy project check task to test that we’ve built what we expect to and created a number of modules like this one:

module CheckJarExists
    include Extension

    after_define do |project|
        project.enhance do
            project.check_jar_exists project

    def check_jar_exists project
        project.check project.package(:jar), 'should exist' do
            puts 'checking jar package exists....'
            it.should exist

And just in-case you’re like me and unsure of how to include this module in a project task, here’s how we did it:

define "subproject" do
      extend CheckJarExists

I’m sure there’s lots more favourites out there in the deep blue Buildr sea… happy fishing!

Written by lizdouglass

December 20, 2008 at 11:34 pm

Posted in Building, JARs, Java, Ruby

Tagged with , , , , , ,


with 3 comments

Last week I was working on packaging some JARs and a LiquiBase changelog file into a single JAR, so that we can easily populate a HSQLDB database with little hassle . We wanted someone to pick up the bundled JAR and be able to do something like this:

java –jar migration.jar

This should achieve the same result as had that person entered this on the command line:

java -jar \
$LIQUIBASE_HOME/liquibase-core/1.8.1/liquibase-core-1.8.1.jar \
--changeLogFile=$LIQUIBASE_HOME/changelog.xml \
--username=myUser \
--url=jdbc:hsqldb:hsql://localhost/xdb \
--classpath=$HSQLDB_HOME/ \
--driver=org.hsqldb.jdbcDriver \
--logFile=liquibase.log \
--logLevel="finest" \

(Note that this assumes that the HSQLDB server is already running.)

Initially we thought that this task was as simple as creating a single JAR (which I’ll refer to as the Migration JAR) that contained the LiquiBase and HSQLDB JARs (in the resources directory), the LiquiBase changelog file, as well as a lone class with main method… like this one:

package my.package;

import liquibase.commandline.Main;
import liquibase.exception.CommandLineParsingException;


public class DatabaseMigration {

    private static final String CHANGELOG_FILE = "changelog.xml";

    public static void main(String[] args) {

        String[] liquibaseArgs = {
                "--changeLogFile=" + CHANGELOG_FILE,
        try {
        } catch (CommandLineParsingException e) {
        } catch (IOException e) {

We tried adding the LiquiBase and HSQLDB JARs into the classpath of the Migration JAR by adding a Class-Path attribute in the META-INF/MANIFEST of the Migration JAR. As the Java tutorial points out, this is not possible:

The Class-Path header points to classes or JAR files on the local network, not JAR files within the JAR file… To load classes in JAR files within a JAR file into the class path, you must write custom code to load those classes.

After some googling we discovered Uberjar and One-JAR. We tried out Uberjar but found the configuration to be confusing and heavy. In comparison One-JAR was relatively simple to get up and running. Following the instructions (or so we thought) on the One-JAR website, we edited our Migration JAR manifest file to look include these statements:


When we ran it, we got this…

Exception in thread "main" java.lang.NoClassDefFoundError: liquibase/exception/CommandLineParsingException

After some head scratching I came across this article by Simon Tuffs about how he created One-JAR. The article compliments the One-JAR website very well, and it provided the solution to our problem in this statement:

In brief, the delegation mode for classloaders required that I put the main class com.main.Main into its own JAR file so that it would be able to locate the library classes (on which it depends).

We had mistakenly thought that we could add the One-JAR content and configuration into our Migration JAR, when in actual fact we needed to another JAR that wraps the entire bundle (the Migration JAR and its dependencies). The resulting (working) structure looks like this (drumroll):

| | | Main-Class: com.simontuffs.onejar.Boot
| | | One-Jar-Main-Class: my.package.DatabaseMigration
| /main
| | Migration.jar
| | | my.package.DatabaseMigration.class
| /lib
| | hsqldb-
| | liquibase-core-1.8.1.jar
| /com.simontuffs.onejar
| | Boot.class
| | etc.

While One-JAR has provided us with much a appreciated solution, it is more complex than we envisaged. Effectively having two bundling JARs does seem like overkill but I can appreciate that in most One-JAR use cases the Migration JAR would be more meaty.

Written by lizdouglass

October 28, 2008 at 10:16 pm

Posted in JARs, Java

Tagged with , , ,