Database migrations with Ant

build.xml

<?xml version="1.0" encoding="UTF-8"?>
<project name="db-worklflow" default="update">
	<property name="db.server">remote_db_server</property>
	<property name="db.database"></property>
	<property name="db.username">root</property>
	<property name="db.password">root</property>
	<taskdef resource="net/sf/antcontrib/antlib.xml" />

	<target name="sql_execute_command" description="Executes exactly one SQL command and returns it output as 'latest_sql_result'">
		<if>
			<not>
				<equals arg1="${no-database-argument}" arg2="true" />
			</not>
			<then>
				<property name="arg_database" value="--database=${db.database}" />
			</then>
			<else>
				<property name="arg_database" value="" />
			</else>
		</if>

		<echo message='Executing SQL command "${command}"' />
		<exec executable="cmd" outputproperty="latest_sql_result">
			<arg value="/c" />
			<!--TODO: duplicate-->
			<arg value='mysql -u ${db.username} --password=${db.password} ${arg_database} -N -e "${command}"' />
		</exec>
	</target>

	<target name="sql_execute_file" description="Executes an SQL file and compiles - if wished - the SQL file so that Ant properties are available inside it">
		<if>
			<equals arg1="${compile}" arg2="true" />
			<then>
				<tempfile property="target_file" suffix="_compiled.sql" deleteonexit="" />
				<echo message="bla" />
				<copy tofile="${target_file}">
					<filterchain>
						<expandproperties />
					</filterchain>
					<fileset file="${file}" />
				</copy>
			</then>
			<else>
				<property name="target_file" value="${file}" />
			</else>
		</if>

		<if>
			<not>
				<equals arg1="${no-database-argument}" arg2="true" />
			</not>
			<then>
				<property name="arg_database" value="--database=${db.database}" />
			</then>
			<else>
				<property name="arg_database" value="" />
			</else>
		</if>

		<echo message="executing SQL script ${file}" />
		<exec executable="cmd">
			<arg value="/c" />
			<!--TODO: duplicate-->
			<arg value="mysql -u ${db.username} --password=${db.password} ${arg_database} &lt; ${target_file}" />
		</exec>
	</target>

	<target name="sql_execute_dir" description="Executes every file in an directory -not subdirectory- in a sorted order">
		<for param="file">
			<sort>
				<fileset dir="${dir}">
					<include name="**/*.sql" />
				</fileset>
			</sort>
			<sequential>
				<antcall target="sql_execute_file">
					<param name="file" value="@{file}" />
				</antcall>
			</sequential>
		</for>
	</target>

	<target name="sql_execute_file_latest" description="Executes only the latest file in a directory">
		<for param="file">
			<last>
				<sort>
					<fileset dir="${dir}">
						<include name="**/*.sql" />
					</fileset>
				</sort>
			</last>
			<sequential>
				<antcall target="sql_execute_file">
					<param name="file" value="@{file}" />
				</antcall>
			</sequential>
		</for>
	</target>

	<target name="retrieve_latest_revision" description="Retrieves the latest installed database version">
		<antcallback target="sql_execute_command" return="latest_sql_result">
			<param name="command" value="SELECT version AS a FROM schema_migration ORDER BY version DESC LIMIT 1" />
		</antcallback>
		<property name="latest_revision_installed" value="${latest_sql_result}" />
	</target>

	<target name="update" depends="retrieve_latest_revision" description="executes every migration script which is greater than the database version">
		<echo message="Latest installed revision: ${latest_revision_installed}" />

		<for param="file">
			<sort>
				<fileset dir="db/tables_views">
					<include name="**/*.sql" />
				</fileset>
			</sort>
			<sequential>
				<propertyregex property="file_revision" input="@{file}" regexp="(.*)\\(\d+)_(\d+)(.*)" select="\2\3" />
				<if>
					<!-- Scary -->
					<scriptcondition language="javascript" value="true">
							self.setValue(true);
							
							fileRev = project.getProperty("file_revision");
							latest = project.getProperty("latest_revision_installed");
							// self.log("filerev: " + fileRev + "latest: " + latest + " - comp: " + (fileRev > latest));
							self.setValue((fileRev > latest));
						</scriptcondition>
					<then>
						<echo message="file_rev: ${file_revision} is older and uninstalled, applying" />
						<antcall target="sql_execute_file">
							<param name="file" value="@{file}" />
						</antcall>
					</then>
				</if>
			</sequential>
		</for>
	</target>
       <!-- MISSING: Write applied version to schema_migration table -->

	<target name="development" description="template for development environment">
		<!-- drop current database -->
		<antcall target="sql_execute_file">
			<param name="file" value="db/handlers/drop_database_template.sql" />
			<param name="compile" value="true" />
			<param name="no-database-argument" value="true" />
		</antcall>
		<!-- create new database -->
		<antcall target="sql_execute_file">
			<param name="file" value="db/handlers/create_database_template.sql" />
			<param name="compile" value="true" />
			<param name="no-database-argument" value="true" />
		</antcall>
		<!-- execute every file inside directory, ordered by filename -->
		<antcall target="sql_execute_dir">
			<param name="dir" value="db/migrations" />
			<param name="compile" value="true" />
		</antcall>
		<!-- create stored procedures or functions -->
		<antcall target="sql_execute_dir">
			<param name="dir" value="db/routines" />
			<param name="compile" value="true" />
		</antcall>
		<!-- insert only the latest core data into the database -->
		<antcall target="sql_execute_file_latest">
			<param name="dir" value="db/core_data" />
		</antcall>
		<!-- insert only the latest fixture -->
		<antcall target="sql_execute_file_latest">
			<param name="dir" value="db/fixtures/${fixture_dir}" />
		</antcall>
	</target>

	<target name="unittest_local" description="template for developer database unittests">
		<property name="db.database" value="${user.name}_unittest" />
		<antcall target="development">
			<param name="fixture_dir" value="unittest" />
		</antcall>
	</target>

	<target name="integration_local" description="template for developer database integration tests">
		<property name="db.database" value="${user.name}_integration" />
		<antcall target="development">
			<param name="fixture_dir" value="integration" />
		</antcall>
	</target>
</project>