Continuous Integration and the Automated Database Update Process

[article]

Complete NAnt Syncdb.build Script

<?xml version="1.0"?>

<!-- Generated by NAntBuilder v2.0-->

<!-- =================================

       Project: syncdb.build

       Author : Lance Lyons

       Onlife Health Solutions

This script will create the blank model database on the blank model database server as well as create the migration script from the prior build to the current build.

       Required inputs:

       sql.database         

       sql.user

       sql.password

       sql.scriptsourcepath

       sql.scriptfilepathname             

       sql.reportfilepathname

================================= -->

<project name="syncdb" default="donothing">     

       <target name="donothing">

       </target>

       <!-- ================================= -->

       <!-- Add your includes in this area    -->  

       <include buildfile="publishing.include.build" />     

       <!-- ================================= -->

       <loadtasks assembly="C:\nantcontrib-0.85\bin\NAnt.Contrib.Tasks.dll" />

       <property name="sqlcmd.exe" value="C:\Program Files\Microsoft SQL Server\100\Tools\Binn\sqlcmd.exe" />

       <property name="sql.database" value="" readonly="false" />

       <property name="sql.user" value="app_AutomatedBuild" readonly="false" />

       <property name="sql.password" value="" readonly="false" />

       <property name="sql.server" value="int-sql01" readonly="false" />

       <if test="${deploy.environment == 'DEV'}" >

              <property name="sql.server" value="dev-sql02,54552" readonly="false" />

       </if> 

       <if test="${deploy.environment == 'DEV2'}" >

              <property name="sql.server" value="dev-sql02,54552" readonly="false" />

       </if>

       <property name="HasSQLScriptRun" value="false" />

       <property name="sqlcompare.path" value="C:\Program Files\Red Gate\SQL Compare 8" readonly="false" />

       <property name="sql.scriptsourcepath" value="" readonly="false" />

       <property name="sql.scriptfilepath" value="" readonly="false" />

       <property name="sql.reportfilepath" value="" readonly="false" />

       <property name="build.version" value="" readonly="false" />

       <property name="sql.scriptsourcepath.postdeploy" value="${sql.scriptsourcepath}\Post-Deploy" readonly="false" />

       <property name="sql.scriptsourcepath.predeploy" value="${sql.scriptsourcepath}\Pre-Deploy" readonly="false" />

       <property name="sql.syncfilename" value="${sql.scriptfilepath}\${sql.database}\sync${sql.database}${build.version}.sql" />

       <property name="sql.reportfilename" value="${sql.scriptfilepath}\${sql.database}\report${sql.database}${build.version}.html" />

       <property name="sql.tempdir.postdeploy" value="e:\tempdir\${sql.database}\Post-Deploy" readonly="false" />

       <property name="sql.tempdir.predeploy" value="e:\tempdir\${sql.database}\Pre-Deploy" readonly="false" />

       <target name="updateversion" >

              <if test="${runsql=='true' and HasSQLScriptRun=='true' }" >

                      <sql

                             connstring="Provider=SQLOLEDB;Data Source=${sql.server}; Initial Catalog=${sql.database};User Id=${sql.user}; Password=${sql.password};"

                             transaction="/true"

                             delimiter=";"

                             delimstyle="Normal"

                             print="true"

                             output="${sql.scriptfilepath}\results.txt"

                             >

                             insert into _dbversion values ('${build.version}',getdate(),'CIUPDATE','${sql.database}');

                      </sql>

              </if>

       </target>

       <target name="movePrePostDeployFoldersOut">

              <echo message="deleting temp directory : ${sql.tempdir.postdeploy}" />

              <echo message="deleting temp directory : ${sql.tempdir.predeploy}" />

              <delete>

                      <fileset basedir="${sql.tempdir.postdeploy}">

                             <include name="**"/>

AgileConnection is a TechWell community.

Through conferences, training, consulting, and online resources, TechWell helps you develop and deliver great software every day.