database migration automation example

classpath.xml

<!-- jtds.jar, used to connect to SQL Server, is in 'lib' -->
<path id="project.classpath">
	<fileset dir="lib">
		<include name="*.jar" />
	</fileset>
</path>

copy_and_modify.xml

<copy todir="${sql.scriptcopydir}">
	<fileset dir="${sql.scripts}/production/ddl/" includes="${sql.scriptsToRun}"/>
	<fileset dir="${sql.scripts}/production/dml/" includes="${sql.scriptsToRun}"/>
</copy>
<replace dir="${sql.scriptcopydir}" token="migrated" value="unmigrated"/>

find_unrun_files.xml

<!-- get the list of files which have been run on local but not on unmigrated database -->
<sql
	driver="${sql.driver}" url="${sql.url}" userid="${local.sqlusername}" password="${local.sqlpassword}" 
	classpathref="project.classpath"
	output="${sql.outputdir}/scripts_to_run.txt"
	print="true" showheaders="false" showtrailers="false"
>
	declare @fileList varchar(MAX)
	select @fileList = coalesce(@fileList + ',', '') +
	 n3.scriptName from migrated..ExecutedSqlScripts n3
	left outer join unmigrated..ExecutedSqlScripts n3Prod on n3.scriptName = n3Prod.scriptName and n3.description = n3Prod.description
	where n3Prod.scriptName is null
	order by n3.scriptName

	select @fileList as TheFiles;
</sql>
<loadfile property="sql.scriptsToRun" srcfile="${sql.outputdir}/scripts_to_run.txt"/>

migrate.xml

<!-- run those files against unmigrated -->
<sql
	driver="${sql.driver}" url="${sql.url}/unmigrated" userid="${local.sqlusername}" password="${local.sqlpassword}" 
	classpathref="project.classpath"
	output="${sql.outputdir}/scripts_run_results.txt"
	print="true" showheaders="false" showtrailers="false" delimiter="GO"
>
	<path>
		<fileset dir="${sql.scriptcopydir}" includes="*.sql"/>
	</path>	
</sql>

output.xml

 [copy] Copying 4 files to C:\dev\projects\himom\migration\build\buildoutput\scripts
  [sql] Executing resource: C:\dev\projects\himom\migration\build\buildoutput\scripts\20121114_01_alterAThing.sql
  [sql] Executing resource: C:\dev\projects\himom\migration\build\buildoutput\scripts\20121115_01_createSomeOtherThing.sql
  [sql] Executing resource: C:\dev\projects\himom\migration\build\buildoutput\scripts\20121115_02_alterSomethingElse.sql
  [sql] Executing resource: C:\dev\projects\himom\migration\build\buildoutput\scripts\20121120_01_createYetMoreStuff.sql
  [sql] 41 of 41 SQL statements executed successfully