Development Blog

My source control contains many tables, many of which have dependencies on one another via foreign keys. When the table structures are being changed fairly regularly, it can be a pain to have to keep updating the database manually.

Ant has a built in SQL task which will pick up a file, then execute the contained statements on the database of your choice.

1. Firstly, create the required scripts to be executed.  For table creation scripts, I always add ‘DROP TABLE IF EXISTS’ (or a variant depending on SQL dialect) to make it easier to run a script on its own if it has no external dependencies.
2. Create extra scripts for dropping the tables (with the commands in the correct order to respect foreign keys).
3. Create an Ant build file (build.xml). Each script to be executed is placed in the file in order of execution (to again respect foreign keys).
4. Execute Ant from the directory containing build.xml

Example for step one

DROP TABLE IF EXISTS reference_generator;

CREATE TABLE reference_generator (
  ref_gen_type VARCHAR(20) NOT NULL,
  reference    BIGINT      NOT NULL,
  timestamp    DATETIME    NOT NULL,
  PRIMARY KEY (ref_gen_type)
)
ENGINE=INNODB;

Example for step two (filename – drop_party.sql)

DROP TABLE IF EXISTS party_attribute;
DROP TABLE IF EXISTS party_ext_ref;
DROP TABLE IF EXISTS party_flag;
DROP TABLE IF EXISTS party;

Example for step three (build.xml)

<project name="DatabaseSetup" basedir="." default="createAndPopulate">

  <property name="sql.driver" value="com.mysql.jdbc.Driver" />
  <property name="sql.url" value="jdbc:mysql://server/dbname" />
  <property name="sql.username" value="myusername" />
  <property name="sql.password" value="mypassword" />

  <target name="createAndPopulate">
    <sql driver="${sql.driver}" url="${sql.url}" userid="${sql.username}" password="${sql.password}" >
      <transaction src="./table/drop_party.sql" />
      <transaction src="./table/party.sql" />
      <transaction src="./table/party_attribute.sql" />
      <transaction src="./table/party_ext_ref.sql" />
      <transaction src="./table/party_flag.sql" />

      <transaction src="./table/reference_generator.sql" />
      <transaction src="./data/reference_generator.sql" />
    </sql>
  </target>

</project>

It is of course important that the JDBC driver referenced (in the example com.mysql.jdbc.Driver) is available to Ant. A simple way of doing this is to add the relevant jar to Ant’s lib directory.

MySQL Stored Procedures
The above technique works for MySQL stored procedures, with one minor change. When writing a MySQL stored procedure, the delimiter to use must be explicitly set (as ; is used within the stored procedures statements).

To set this delimiter, the ‘delimiter’ attribute within the sql tag must be provided.

For example:

<!-- Usage: ant createsp -Dfile=./dir/file.sql-->
  <target name="createsp">
    <sql driver="${sql.driver}" url="${sql.url}" userid="${sql.username}" password="${sql.password}" delimiter="//" >
      <transaction src="${file}" />
    </sql>
  </target>

When creating an enum dynamically, there are two levels of difficulty.

The first case is where the type of the enum is known at compile-time, but the value is not.  In this case, we can use the valueOf method of the enum in question:

  MyEnum.valueOf(stringVar);

The second, more complex case is where neither the type nor the value is known. The Enum base class (from whence all enums derive) contains a static method for this scenario:

  Enum.valueOf(classVar, stringVar);

This can result in Unchecked Invocation warnings so either proper checking will be required, or a suppression of the warning if absolutely sure.

In cases involving generics, it will be required to cast this call to prevent the “Bound mismatch” exception (essentially the compiler forcing the generic to be an implementation of Enum, which of course it must be since all enumerations derive from Enum).

  Enum.valueOf((Class<? extends Enum>)classVar, stringVar);