Using Liquibase with Ingres

The following is known to apply to Ingres 10.1.0, and to Liquibase 2.0.6-SNAPSHOT as of 24-Aug-2012. Other versions may differ.

Background

Liquibase doesn’t officially contain support for Ingres, but it has limited support for “unsupported” DBMSes (Using Unsupported Databases). Specifically, there’s a generic, limited-functionality driver, liquibase.database.core.UnsupportedDatabase, which is used as a fallback for unrecognized DBMSes. (Just how limited the support is depends on the DBMS in question — basically, on how much it differs from what the fallback driver expects.)

For Ingres, there are a number of issues, workarounds for which are given below. (Adding Ingres support to Liquibase was beyond the scope of our current needs.)

Known Issues

  1. There is a showstopper bug in Liquibase’s unsupported-DBMS code, which needs to be patched before you can make any progress at all.
  2. Ingres lowercases all table names: CREATE TABLE FooBar results in a table called foobar. (Presumably this is true for other objects too, but I haven’t investigated this.) But Liquibase, for unsupported databases, expects at least case-preserving behaviour, if not full case-sensitivity.
  3. Ingres’s date-time type is called TIMESTAMP, but Liquibase’s default spelling is DATETIME
  4. In Ingres, the way you set a column to the current date and/or time is by assigning to it the magic string now:
    update myTable set myDateTimeColumn='now'

Workarounds

Patch Liquibase

There’s a bug in Liquibase’s unsupported-DBMS code, which results in NullPointerExceptions being thrown. Specifically, this applies to any DBMS that’s implemented by the fallback UnsupportedDatabase driver. The bug is present in Liquibase 2.0.5, and in 2.0.6-SNAPSHOT as of 24-Aug-2012, but is fixed in 3.0.0-SNAPSHOT as of the same date.

Here’s the bug report; it has a patch attached. Until such time as the patch has been applied upstream, you have to apply it yourself. Within Intelliware, a patched version of Liquibase is available.

Tell Liquibase to use all-lower-case names for its tables

Liquibase needs two tables in the database in order to keep track of its own work:

  • DatabaseChangeLog contains a row for each refactoring that has been applied to the database
  • DatabaseChangeLogLock is used for mutual-exclusion locking among multiple Liquibase instances

But those are only the default names; one can use differently named tables, by providing the desired names as system properties to Liquibase.

The workaround for Ingres’s case handling is to tell Liquibase explicitly to use all-lower-case table names. To do this, set the following two JVM properties:

liquibase.databaseChangeLogTableName=databasechangelog
liquibase.databaseChangeLogLockTableName=databasechangeloglock

 

Manually create the Liquibase structure

If Liquibase’s tables don’t already exist in a given database, Liquibase attempts to create them. But for an unsupported database, it attempts to create some DATETIME columns, which Ingres doesn’t understand.

The workaround is to create the tables manually, before attempting to use Liquibase on the database. Here’s the SQL to do it for Ingres:

CREATE TABLE databasechangeloglock (
    ID INT NOT NULL,
    LOCKED BOOLEAN NOT NULL,
    LOCKGRANTED TIMESTAMP,
    LOCKEDBY VARCHAR(255),
    CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID)
);

INSERT INTO databasechangeloglock (ID, LOCKED) VALUES (1, FALSE);

CREATE TABLE databasechangelog (
    ID VARCHAR(63) NOT NULL,
    AUTHOR VARCHAR(63) NOT NULL,
    FILENAME VARCHAR(200) NOT NULL,
    DATEEXECUTED TIMESTAMP NOT NULL,
    ORDEREXECUTED INT NOT NULL,
    EXECTYPE VARCHAR(10) NOT NULL,
    MD5SUM VARCHAR(35),
    DESCRIPTION VARCHAR(255),
    COMMENTS VARCHAR(255),
    TAG VARCHAR(255),
    LIQUIBASE VARCHAR(20),
    CONSTRAINT PK_DATABASECHANGELOG PRIMARY KEY (ID, AUTHOR, FILENAME)
);

 

Other limitations

Liquibase lets you write refactorings either in raw SQL or in its own XML format. The latter is intended to be DBMS-independent, but requires driver support to translate the XML into SQL appropriate for a particular DBMS. The fallback UnsupportedDatabase driver does this translation, but we haven’t tested whether the resulting SQL is appropriate for Ingres.

Thus, only raw-SQL refactorings (<sql> and <sqlFile>) are known to work. If you want to use the XML-based ones, test them first! (Each individual refactoring needs to be tested before you can depend on it; that refactoring A works says nothing about refactoring B.)

Even if a given XML refactoring works, there will be TIMESTAMP-vs-DATETIME issues. Liquibase’s modified-SQL feature might help here, but again, this has not been tested.

Summary

To use Liquibase on an Ingres database:

  1. Make sure you’re using a version in which https://liquibase.jira.com/browse/CORE-1200 has been fixed: either 3.0.0-SNAPSHOT or a patched (or sufficiently recent) version of 2.0.6-SNAPSHOT
  2. Create Liquibase’s tables manually, using the SQL shown above
  3. Invoke Liquibase as follows:

    From a UNIX shell:

    java -D liquibase.databaseChangeLogTableName=databasechangelog \
         -D liquibase.databaseChangeLogLockTableName=databasechangeloglock \
         liquibase.integration.commandline.Main \
         --driver=com.ingres.jdbc.IngresDriver \
         "--currentDateTimeFunction='now'" \
         ...

    From Maven:

    <configuration>
        <systemProperties>
            <liquibase.databaseChangeLogTableName>databasechangelog</liquibase.databaseChangeLogTableName>
            <liquibase.databaseChangeLogLockTableName>databasechangeloglock</liquibase.databaseChangeLogLockTableName>
        </systemProperties>
        <driver>com.ingres.jdbc.IngresDriver</driver>
        <currentDateTimeFunction>'now'</currentDateTimeFunction>
        ...
    </configuration>

    Note that, however Liquibase is invoked, the value passed for currentDateTimeFunction must be the literal string 'now', including the quotes.

It's only fair to share...
Share on FacebookGoogle+Tweet about this on TwitterShare on LinkedIn

Leave a Reply