Tag Archives: liquibase

SQL Database Version Control and RENAMEs

You’ve just watched Josh Berkus’ presentation “Ten Ways to Wreck Your Database” and bearing in mind point #2 “ranDom_naming(s),” your team has decided to get your act together and adopt a naming convention (although this may not be advantageous to your job security, per josh :-)).

PostgreSQL makes it fairly easy to rename schemas, tables and columns via an appropriate ALTER object RENAME statement. Aside: Other SQL implementations provide similar capabilities to various degrees of completeness. Some SQL version control tools support such changes. For example, Liquibase supports renaming tables, columns and views.

On the other hand, an ALTER statement –like a biological mutation– destroys information, which creates a problem for difference engines such as apgdiff, Andromeda and Pyrseas. It may be obvious to a human that the film.rating column and category table in the production database correspond to the renamed film.mpaa_rating column and categories table, respectively, in the development database. However, there is nothing in the latter’s system catalogs that tie the new names to the previous ones. As a result, difference engines will usually DROP the former objects and CREATE others with the new names.

The Pyrseas solution to this problem is to add an oldname field to the YAML spec. For example, if you have the following abbreviated spec (generated by dbtoyaml from the development database):

schema public:
  table film:
    check_constraints:
      ...
    columns:
    ...
    - mpaa_rating:
        type: character(5)
    ...
    primary_key:
    ...

By editing the spec and making the following change to the mpaa_rating column:

    - mpaa_rating:
        oldname: rating
        type: character(5)

Then yamltodb will, when run against a database that still uses the previous colum name, generate the following SQL script:

ALTER TABLE film RENAME COLUMN rating TO mpaa_rating;

Similarly, you can use oldname on a table to generate ALTER TABLE name RENAME TO new_name and on a schema to generate ALTER SCHEMA name RENAME TO new_name.

This is not a foolproof solution because the rename is a one-time action. You could commit the changed spec to your VCS, but if you ran yamltodb against a database after the RENAME has been applied, it will give an error because it cannot find the oldname object. Aside: I’m considering changing that to simply a warning. Another option would be to allow yamltodb to read more than one YAML spec or one that lists some objects more than once, so the extra information could be added when needed. For these types of changes, a complementary tool such as depesz Versioning can be used to introduce the RENAMEs into production.

Pyrseas 0.1.0 includes the RENAME capability for schemas and tables and I’ve recently committed the changes needed for RENAMEing columns.

In other project news, I’ve also added support for FOREIGN KEY ON UPDATE and ON DELETE actions, support for COMMENT statements on schemas, tables and columns, and corrected a problem with indexes being created in the wrong schema. With these changes, the autodoc regression database schemas, tables, columns, primary keys, foreign keys, indexes and comments on these objects can all be properly recreated using dbtoyaml and yamltodb, with the exception of the inheritance tables.

SQL Database Version Control – Liquibase

Scott Ambler is a prolific author in the area of agile software and agile database development. He and Pramod Sadalage wrote Refactoring Databases: Evolutionary Database Design, a book that expounds the process and techniques for making changes to databases. Caveat for followers of C.J. Date: Chris does not hold Mr. Ambler’s writings in high regard (see this, for example). Sadalage, a ThoughtWorks consultant, has a web site that lists and provides examples of the various refactorings discussed in the book.

The book inspired at least two products for database version control. One, dbdeploy, stemmed from the work of some ThoughtWorks UK staff members. Liquibase, the other product/project, was started by Nathan Voxland after reading the book and noticing a lack of tools to address the problem.

Both dbdeploy and Liquibase are open source and both use XML for their change specification files. Liquibase uses Java, whereas dbdeploy has been ported to Java, .Net and PHP (Liquibase is working on a .Net port). According to its documentation, dbdeploy supports Oracle, SQL Server, Sybase, Hypersonic SQL and MySQL, but the release notes for 3.0M2 indicate support for PostgreSQL and DB2 has been added. Liquibase supports all those and more.

Today we’ll once again follow our favorite DBAs, Carol and Dave, as they use Liquibase to implement the changes described in Version Control, Part 2: SQL Databases.

Version 0.1

For the first release, Carol creates the changelog file film.xml, as follows:

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

<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
         http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-2.0.xsd">
    <changeSet id="1" author="carol">
        <createTable tableName="film">
            <column name="id" type="int">
                <constraints primaryKey="true" nullable="false"/>
            </column>
            <column name="title" type="varchar(32)">
                <constraints nullable="false"/>
            </column>
            <column name="release_year" type="int">
            </column>
        </createTable>
    </changeSet>
</databaseChangeLog>

Obviously, there’s very little that could be identified as SQL in the changelog. If you’re paying attention, you’ll notice the CHECK constraint on release_year is missing. Although Sadalage lists “Introduce Column Constraint” as one of the refactorings, Liquibase 2.0 doesn’t support that.

After creating film.xml, Carol can validate it, generate SQL from it, or apply it to her database. For example, to actually create the table she issues a command such as the following:

$ liquibase --driver=org.postgresql.Driver \
      --classpath=/usr/share/java/postgresql.jar \
      --url="jdbc:postgresql://localhost/caroldev"
      --changeLogFile=film.xml --username=carol --password=pswd \
      update

The liquibase command is a shell script (or Windows .bat file) that invokes java -jar liquibase.jar. The options can be placed in a file named liquibase.properties for convenience.

Liquibase has the ability to back out several types of changes automatically and createTable is one of them. For example, the following sequence creates a tag for possible future rollback, applies the film.xml changes, and rolls back the changes, i.e., DROPs the film table:

$ liquibase tag rel0
$ liquibase --changeLogFile=film.xml update
$ liquibase rollback rel0

The databasechangelog table records the Liquibase activities:

moviesdb=> select id, author, filename, orderexecuted, tag
moviesdb-> from databasechangelog;
      id       |  author   |      filename      | orderexecuted | tag
---------------+-----------+--------------------+---------------+------
 1298957853145 | liquibase | liquibase-internal |             1 | rel0
 1             | carol     | film.xml           |             2 |
(2 rows)

Version 0.2

Next, Dave creates film-length.xml and Carol creates film-rating.xml. Below are the changeSet portions of those files:

    <changeSet id="1" author="dave">
      <addColumn tableName="film">
	<column name="length" type="smallint"/>
      </addColumn>
    </changeSet>
    <changeSet id="2" author="carol">
      <addColumn tableName="film">
	<column name="rating" type="char(5)"/>
      </addColumn>
    </changeSet>

Not much more to say except that we were again unable to specify the CHECK constraint (on the length column).

Version 0.3

To implement the changes for version 0.3, Carol and Dave create film-category.xml and film-language.xml. Here are the respective changeSets:

<changeSet id="2" author="dave">
    <createTable tableName="language">
        <column name="language_id" type="int">
            <constraints primaryKey="true" nullable="false"/>
        </column>
        <column name="name" type="varchar(20)">
            <constraints nullable="false"/>
        </column>
        <column name="last_update" type="timestamp with time zone">
            <constraints nullable="false"/>
        </column>
    </createTable>
    <addColumn tableName="film">
      <column name="language_id" type="int">
        <constraints nullable="false"/>
        </column>
    </addColumn>
    <addForeignKeyConstraint constraintName="fk_film_language"
        baseTableName="film" baseColumnNames="language_id"
        referencedTableName="language" referencedColumnNames="language_id"
        />
</changeSet>
<changeSet id="3" author="carol">
    <createTable tableName="category">
        <column name="category_id" type="int" autoIncrement="true">
            <constraints primaryKey="true" nullable="false"/>
        </column>
        <column name="name" type="varchar(25)">
            <constraints nullable="false"/>
        </column>
        <column name="last_update" type="timestamp with time zone">
            <constraints nullable="false"/>
        </column>
    </createTable>
    <createTable tableName="film_category">
        <column name="film_id" type="int">
            <constraints primaryKey="true" nullable="false"/>
        </column>
        <column name="category_id" type="int">
            <constraints primaryKey="true" nullable="false"/>
        </column>
        <column name="last_update" type="timestamp with time zone">
            <constraints nullable="false"/>
        </column>
    </createTable>
        <addForeignKeyConstraint constraintName="fk_film_category"
            baseTableName="film_category" baseColumnNames="film_id"
            referencedTableName="film" referencedColumnNames="id"
            />
        <addForeignKeyConstraint constraintName="fk_category_film"
            baseTableName="film_category" baseColumnNames="category_id"
            referencedTableName="category" referencedColumnNames="category_id"
            />
</changeSet>

There may be a way to specify foreign key constraints when creating a table, or adding a column to a table, but the documentation for the “references” attribute of the column Constraints tag is not very helpful.

Review

Liquibase is installed by extracting liquibase.jar from a .zip or .tar.gz archive. It requires Java 1.5 or newer. To use it with PostgreSQL you also need to install the PostgreSQL JDBC Driver, available as the libpg-java package on Debian and Ubuntu.

In addition to the command line script, Liquibase can be invoked from the Apache Ant build tool, as well as various Java tools such as Maven and Grails. Although training videos indicate there are plugins for both Eclipse and IntelliJ IDEA, the former appears to be still under development. With the IDE plugin, instead of editing the XML changelog files, you can instead complete it by selecting from context menus and other aids.

Liquibase supports over 40 refactorings. It does not support PostgreSQL-specific features such as TYPEs, DOMAINs and AGGREGATEs. Its validate command verifies the XML syntax but delegates data type validation to the DBMS. So it would be possible to CREATE TYPE mpaa_rating AS ENUM and CREATE DOMAIN year with psql (i.e., without version control) and then use those types in a Liquibase changeSet.

One of the strengths of Liquibase is its semantic knowledge. Unlike a schema diff tool, it knows a table or column is being renamed and not dropped with a new one taking its place, because the developer has to make that explicit. On the other hand, the verbosity of XML tends to detract from its appeal, at least for those of us who prefer a concise or even terse approach.