Some days ago Joel Moss released a new version of Cake DB Migrations. As this version also supports advanced installations, I tested it a bit. After fixing some small glitches it worked fine. But, well, it seems that it doesn’t fit to my working style. As I do a lot of tiny steps to build a database, the overhead when using Migrations is rather high: for each change there is a migration file. The format of those migration files is rather “talkative”, and I miss a bit of magic, i.e. if I use create_table in the UP section, I would expect that automagically a drop_table is done in the DOWN section. Last, but not least, the migration tool works similar to the bake script, which means you have to select several options each time you want to apply a migration. And that is a pain! But test it yourself to see whether it fits to your working style.

And so I am back to Apache Ant and the good old SQL scripts ;-) My approach follows a simple pattern after a modification of the SQL scripts: drop, create and insert. Here is the Ant script I use:

<?xml version="1.0"?>
<project name="myproject" default="default">
  <property name="driver" value="com.mysql.jdbc.Driver"></property>
  <property name="url" value="jdbc:mysql://localhost/myproject?characterEncoding=UTF-8"></property>
  <property name="user" value="root"></property>
  <property name="password" value=""></property>

  <target name="default" >
    <sql driver="${driver}" password="${password}" url="${url}" userid="${user}" src="drop.sql" encoding="UTF-8"></sql>
    <sql driver="${driver}" password="${password}" url="${url}" userid="${user}" src="create.sql" encoding="UTF-8"></sql>
    <sql driver="${driver}" password="${password}" url="${url}" userid="${user}" src="insert.sql" encoding="UTF-8"></sql>
  </target>
</project>

This script I can then execute with one mouse click directly from my IDE. Simple and fast.