Skip to content

Instantly share code, notes, and snippets.

@schakko

schakko/build.xml

Created Dec 23, 2011
Embed
What would you like to do?
Database migrations with Ant
<?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>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment