Liquibase scripts with Hsqldb

Liquibase scripts with Hsqldb

Liquibase is an open source library for tracking, managing and applying database changes that can be used for any database with a JDBC driver.
In some of our projects, we have used Liquibase to create tables as well as load pre-required data for the software to function smoothly.

Example

<changeSet author="liquibase-docs" id="1">
    <createTable 
            schemaName="public"
            tableName="person"
            remarks="person">
            <column name="id" type="INTEGER" autoIncrement="true">
                <constraints primaryKey="true" primaryKeyName="person_pk"                         nullable="false"/>
            </column>
            <column name="address" type="varchar(255)"/>
    </createTable>
</changeSet>

autoIncrement="true"

This helps in managing Ids at the time of data insertion.

But recently, we encountered a situation wherein we found that Hsql database, by default, starts with the Id=0 .
If we use an enumeration with ordinal values as well as datatable for a particular entity, there will be a mismatch in fetching data with the help of enum ordinals.

To solve this problem as a Liquibase script writer, we should specify the following properties in the script:

<changeSet author="liquibase-docs" id="1">
    <createTable 
            schemaName="public"
            tableName="person"
            remarks="person">
            <column name="id" type="INTEGER" autoIncrement="true" startWith="1" incrementBy="1">
                    <constraints primaryKey="true" primaryKeyName="person_pk"                         nullable="false"/>
            </column>
            <column name="address" type="varchar(255)"/>
    </createTable>
</changeSet>

startwith="1" and incrementBy="1"

These attributes will make life easy for accessing table data with the help of enum ordinals.