Continuous Integration and the Automated Database Update Process

[article]

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

                             <echo message="Running Script ${sqlscriptname}" />

<echo message="sqlcmd -S ${sql.server} -d ${sql.database} -U ${sql.user} -P ${sql.password} -i ${sqlscriptname} -I" />   

<exec failonerror="true" program="${sqlcmd.exe}" commandline='-b -S ${sql.server} -d ${sql.database} -U ${sql.user} -P ${sql.password} -i "${sqlscriptname}" -I' />

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

                      </if> 

              </do>        

       </foreach>

</target>

Update version
This target is executed at the end of the update process to add the version number to the _dbversion table. This version is essentially the SVN revision of the database source and provides some history of when a database was updated with a cross reference into the SVN repository—what the updates were.

<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>

Final Word
In a truly integrated CI environment like the INT environment we use today at Onlife Health, not only are the website applications automatically built in a CI fashion and then subsequently deployed as live websites, but also the live connected databases are kept up to date in an automated fashion. This allows development teams to get the ultimate feedback by seeing their changes in live, updated application websites and databases. This database update approach can also be extended to include build-to-build migration scripts for deploying applications and their related database updates to any environment. I discuss this concept and approach in an upcoming article titled “Database Versioning and Update Automation.”When using both in tandem, the earlier CI database update also provides feedback on how the set of database changes will integrate, before a full release build and subsequent deploy have taken place.

AgileConnection is one of the growing communities of the TechWell network.

Featuring fresh, insightful stories, TechWell.com is the place to go for what is happening in software development and delivery.  Join the conversation now!