I wanted to have a way of controlling database updates for the project I am working on. I was aiming for the following:
1. A series of database deltas
2. A Database on each developer machine, and on each of the CI environments
3. The deltas to be applied in order and only once
4. Each database to have a list of applied deltas
5. I wanted the database versioning to be part of CI
These goals were fairly straightforward, and I did not see the need to write code to achieve this. At the suggestion of a
colleague, I looked at
DBDeploy.
This seemed to provide the features I wanted and is pretty trivial to set up, as described
here. However there were a few complications, primarily due to Maven and
MySQL.
1. DBDeploy has an Ant target, but no maven plugin. I therefore used the
Maven Antrun plugin to call the DBDeploy Ant target
2. MySql has no differentiation between end of statement and end of block. This can be a problem when scripting procedures. The work-around is to use a different delimiter. In my delta scripts I use #.
Unfortunately (for me) however DBDeploy works by concatenating all deltas into one script and adding it's own versioning statements at either end. These statements are hard coded to use a semi-colon delimiter.
In order to be able to use a different delimiter, it was therefore necessary to do some post processing on the file. Since I was already using the Antrun plugin and ant has some good targets for this kind of thing, I used the Ant
replace and
replaceregex Ant targets to do this.
3. In order to run the script as part of CI, I used the
sql-maven-plugin and changed the delimiter to #
The plugin configuration was as follows:
...
<plugins>
<plugin>
<artifactId>maven-antrun-plugin</artifactId>
<executions>
<execution>
<phase>compile</phase>
<configuration>
<tasks>
<taskdef name="dbdeploy" classname="net.sf.dbdeploy.AntTarget"/>
<taskdef name="ReplaceRegExp" classname="org.apache.tools.ant.taskdefs.optional.ReplaceRegExp"/>
<mkdir dir="${basedir}/target/db"/>
<dbdeploy
driver="com.mysql.jdbc.Driver"
url="${db.url}"
userid="${db.user}"
password="${db.password}"
dir="${basedir}/src/main/resources/db/deltas"
outputfile="${basedir}/target/db/all-deltas.sql"
dbms="mysql"
undoOutputfile="${basedir}/target/db/undo-all-deltas.sql"
/>
<replace dir="${basedir}/target/" token="COMMIT;" value="COMMIT#">
<include name="**/*.sql"/>
</replace>
<replaceregexp byline="true">
<regexp pattern=" changelog(.*);"/>
<substitution expression=" changelog\1#"/>
<fileset dir="${basedir}/target/">
<include name="**/*.sql"/>
</fileset>
</replaceregexp>
<replace dir="${basedir}/target/" token="@DBNAME@" value="${db.name}">
<include name="**/*.sql"/>
</replace>
</tasks>
</configuration>
<goals>
<goal>run</goal>
</goals>
</execution>
</executions>
<dependencies>
<dependency>
<groupId>dbdeploy</groupId>
<artifactId>dbdeploy</artifactId>
<version>2.1.1</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.6</version>
</dependency>
<dependency>
<groupId>org.apache.ant</groupId>
<artifactId>ant-nodeps</artifactId>
<version>1.7.1</version>
</dependency>
</dependencies>
</plugin>
<plugin>
<groupId>org.codehaus.mojo</groupId>
<artifactId>sql-maven-plugin</artifactId>
<version>1.1</version>
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.6</version>
</dependency>
</dependencies>
<configuration>
<driver>com.mysql.jdbc.Driver</driver>
<autocommit>true</autocommit>
<delimiter>#</delimiter>
</configuration>
<executions>
<execution>
<id>update-schema-db</id>
<phase>generate-test-resources</phase>
<goals>
<goal>execute</goal>
</goals>
<configuration>
<url>${db.url}</url>
<username>${db.user}</username>
<password>${db.password}</password>
<fileset>
<basedir>${basedir}/target/db</basedir>
<includes>
<include>all-deltas.sql</include>
</includes>
</fileset>
</configuration>
</execution>
</executions>
</plugin>
</plugins>
...
