Liquibase is a tool that helps you manage database changes. It can can be integrated into your build process to automatically update your database, and it also has a command line interface that operational staff can use to generate SQL scripts for production deployment. One of its nice features is database abstraction. You define your changesets in XML, and Liquibase generates compatible SQL for you. As with any tool that tries to provide this level of abstraction over a large number of vendors, it doesn’t work 100% with all DBMS. Below is a simple changeset.
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<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 author="marc" id="1">
<createTable tableName="test">
<column name="id" type="INT" />
</createTable>
</changeSet>
<changeSet author="marc" id="2">
<createIndex indexName="idx1" tableName="test">
<column name="id" />
</createIndex>
</changeSet>
</databaseChangeLog>Writing these by hand isn’t that bad, especially if you’re using an XML editor that does autocomplete based on the document’s XSD, such as Eclipse WTP. Some of the database compatibility issues you may run into include things like unsupported constraint options, lack of support for DDL in transactions, metadata issues, statement formatting and identifier delimitation. These are not issues with Liquibase, just things that can be incompatible between different DBMS.
If you find yourself faced with one of these incompatibilities, you have a few options. You can open a bug report and wait until it’s fixed. Better yet, you can fix it yourself and contribute back to the project. But if neither of those options will work for you, the modifySql tag may get the job done. This tag lets you modify the SQL that’s generated, so you can do anything from a simple append or character replacement to regular expressions. The example I’m going to cover is how to do a regular expression replacement. Regular expressions in Java are bad enough, but they can be a little confusing in Liquibase because you have to format them for XML. Below is a Java example of what we’re trying to accomplish.
@Test
public void pattern_replaces_quotes_with_square_brackets() {
String expression = "\"(\\w+)\"";
Pattern p = Pattern.compile(expression);
Matcher m = p.matcher("create index \"idx1\" on \"test\" (\"id\")");
assertThat(
m.replaceAll("[$1]"), is("create index [idx1] on [test] ([id])"));
System.out.println(expression.replaceAll("\"", """));
}This pattern will find all instances of one or more word characters
([a-zA-Z_0-9]) within quotes and replace those quotes with square
brackets. The $1 contains whatever the first thing in parentheses
matched, \w+ in this case. We can’t just do a simple character
replacement of double quotes because we have to account for left and
right brackets. Note that if you have dashes (-) in your object names,
the \w pattern won’t match them. Check out the Javadoc for Pattern
if you need to brush up on your regular expression syntax. The println
at the end of the test will print out the expression formatted for
inclusion in XML, so you can just copy and paste the output into your
changelog file. If you follow these steps, you’ll end up with a
modifySql tag like the one below.
<changeSet author="marc" id="2">
<createIndex indexName="idx1" tableName="test">
<column name="id" />
</createIndex>
<modifySql>
<regExpReplace replace=""(\w+)"" with="[$1]"/>
</modifySql>
</changeSet>I would highly recommend creating a unit test like I did to ensure that you have the correct regular expression. You’ll save yourself a lot of time, as doing your verification through Liquibase would require repeatedly executing the change, checking it, then rolling back if necessary to try again. If you’re not interested in adding more XML to your build process, but you’d like a capable tool to manage your database changes, Liquibase is supposed to support SQL changelogs in its 2.0 release.