Something I haven't tackled yet is adding SQL executions to the mix for our automated builds. There are two potential use cases for this:

1) Setup/teardown of test databases during unit testing
2) Publishing SQL changes when we publish our new CF/Flex/Java code

Since there is a SQL plugin for Maven, I decided to give it a go with my local MySQL instance. I logged in as my MySQL root user and created a database to play with and gave myself permissions for it.

CREATE DATABASE maven;
GRANT ALL ON maven.* TO 'porgesm'@'localhost';

Next, I created a Maven build to execute some SQL. I wanted to use the mysql-connectorj library, so I went to the public Maven repository at http://repo1.maven.org and found the details on the available MySQL depedencies under http://repo1.maven.org/maven2/mysql/mysql-connector-java.

<?xml version="1.0" encoding="UTF-8"?><metadata>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>5.0.5</version>
  <versioning />
</metadata>

Now I needed a Maven build that would make use of this library. I found the details on the SQL plugin for Maven and created a modified version of the sample build files they showed on their web site.

<?xml version="1.0" encoding="UTF-8"?>
<project>
  <modelVersion>4.0.0</modelVersion>
  <groupId>MavenSQL</groupId>
  <artifactId>MavenSQL</artifactId>
  <version>0.0.1</version>
  <build>
    <plugins>
      <plugin>
        <groupId>org.codehaus.mojo</groupId>
        <artifactId>sql-maven-plugin</artifactId>

        <!-- JDBC Driver -->
        <dependencies>
           <dependency>
              <groupId>mysql</groupId>
              <artifactId>mysql-connector-java</artifactId>
              <version>5.0.5</version>
           </dependency>
        </dependencies>

        <configuration>
           <driver>com.mysql.jdbc.Driver</driver>
           <url>jdbc:mysql://localhost:3306/maven</url>
           <username>porgesm</username>
           <password>password</password>
           <autocommit>true</autocommit>
           <onError>continue</onError>
           <srcFiles>
              <srcFile>src/../sql/${project.version}_drop.sql</srcFile>
              <srcFile>src/../sql/${project.version}_create.sql</srcFile>
           </srcFiles>
        </configuration>

      </plugin>
    </plugins>
  </build>
</project>

Usually when developing, I find it easiest to create two scripts: one that will reverse any changes I intend to make (a "drop" script), and then one to stage my new changes (a "create" script). To do this, I created two files: ${project.version}_drop.sql and ${project.version}_create.sql. I use the token ${project.version} so that the plugin will automatically reference a SQL script based upon the version of the project I am on. This will give me something akin to a poor man's version of migrations in Rails, where only the SQL scripts for the current version will execute when the build file is run.

Of course, the first time you run the drop script, it will fail since the changes don't exist yet. To make Maven ignore errors due to the drop, I threw in the <onError>continue</onError> declaration.

I then created two files that held simple SQL statements to be executed in the build.

0.0.1_drop.sql

DROP TABLE maven_table;

0.0.1_create.sql

CREATE TABLE maven_table (stuff NUMERIC);

You will notice that there is a username/password entry in the build file. I can move this to my global settings.xml file inside my Maven home directory, where I keep all my other passwords. If I were to do this, the settings entry would look like this.

<?xml version="1.0"?>
<settings>
  <servers>
    <server>
      <id>settingsKey</id>
      <username>porgesm</username>
      <password>password</password>
    </server>
  </servers>
</settings>

I would then reconfigure the configuration in the plugin as follows.

...

<configuration>
  ...
  <settingsKeys>settingsKey</settingsKeys>
  <driver>com.mysql.jdbc.Driver</driver>
  <url>jdbc:mysql://localhost:3306/maven</url>
  <autocommit>true</autocommit>
  ...
</configuration>

...

I ran the build, and it executed flawlessly. It gives you output regarding the SQL scripts that you run.

mac:~/Documents/code/javaprojects/MavenSQL porgesm$ mvn sql:execute
[INFO] Scanning for projects...
[INFO] Searching repository for plugin with prefix: 'sql'.
[INFO] -------------------------------------------------
[INFO] Building Unnamed - MavenSQL:MavenSQL:jar:0.0.1
[INFO]    task-segment: [sql:execute]
[INFO] -------------------------------------------------
[INFO] [sql:execute]
[INFO] Executing file: /Users/.../sql/0.0.1_drop.sql
[INFO] Executing file: /Users/.../sql/0.0.1_create.sql
[INFO] 2 of 2 SQL statements executed successfully
[INFO] -------------------------------------------------
[INFO] BUILD SUCCESSFUL
[INFO] -------------------------------------------------
[INFO] Total time: < 1 second
[INFO] Finished at: Sat Sep 15 15:20:27 EDT 2007
[INFO] Final Memory: 2M/3M
[INFO] -------------------------------------------------

One case that is not handled in this build file is if there are no SQL scripts to be run. If no SQL scripts for the current version being deployed exist, then the plugin fails and the build dies. There are two possible solutions. One would be to require a set of SQL scripts for every build, and just have them be blank if there is no SQL to execute (a little kludgey). Another would be to set execution logic in the build so that we only execute this plugin if SQL scripts exist, or by using a command line parameter.

There is a <skip/> option in the SQL plugin to allow you to conditionally set execution, so this wouldn't be too hard to do. Here is an example of skipping builds if Maven is being run during a test execution.

<configuration>
  <username>postgres</username>
  <password>password</password>
  <settingsKeys>key</settingsKeys>
  <url>jdbc:postgressql://localhost:5432:yourdb</url>
  <skip>${maven.test.skip}</skip>
</configuration>

So, I'm still finding some simple and useful additions to the build process with Maven, and they're still working fine. I saw something on a web site saying that Maven's goal was to handle 95% of Java development use cases out of the box, and that rule still seems to apply in my experience so far.