null is not an object

Working on a project with multiple people, multiple databases and many tests for each of those people and databases makes managing schemas a pretty big nightmare as many of you can probably atest to even more than I. If you are all working on a single rails app rails migrations make things much better but even then there are still cases that make it painful like when schema changes break all your tests. Since half of the application that I am currently working on is not even a rails app it means that we need some other way of managing that other database schema. To make things worse our test cases are rather specific and it may end up that a lof the tests have very different test data sets so using one set of rails fixtures also doesn’t cut it.

I looked around a bit for other migration tools and did find Migrate4j which looked OK but the idea of learning another abstraction ontop of SQL did not really sit well with me. I like writing migrations in SQL since you know exactly what you are getting and there are no problems with things like setting a field as MEDIUMTEXT type that doesn’t exist in your abstraction language.

Anyhow, I cobbled something together in the form of an ANT script in a few hours that resembles a generic migration system that will take a SQL file containing your migration (add_column_to_table.up.sql and corresponding down file) and then apply it to schema SQL file as well as data SQL files that are used for any tests.


<target name="db.dump.schema">
<exec executable="cmd" os="Windows XP,Windows Vista>
<arg value="/c" />
<arg value="mysqldump -u ${db.user} --password="${db.password}"
--databases ${db.name} --add-drop-database -R --no-data
--skip-set-charset --default-character-set=utf8 > ${sql.name}" />
</exec>
</target>


Tags: ant, migrations, programming, workflow.