One of the challenge we face while developing software is database change management. We, at Headspring, have developed NAnt task to facilitate this process and is part of tarantino library, an open source project owned and managed by Kevin Hurwitz.
I do not intent to repeat same documentation as already been posted by project team. If you want to know about it’s nut and bolt then go here.
Recently we faced little different problem related to database change management. In an attempt to preserve initial set of data that we and product owner uses to test user stories, we wanted to switch from dropping database every time and regenerating it using nhibernate export schema to update only mode.
Technically what that mean is we need to change our build process to remove “dropDatabase” and “CreateDatabase” task and instead use”Update” database. However challenge was to use the same script for managing test instance of database as well. This is the db against which all our integration tests run. We wanted all schema changes to apply on this db but no data. I.e. only create/alter/drop script and no insert or update scripts.
In an attempt to solve the issue in” simplest possible things that work” manner, I decided to use convention over configuration option and added one more attribute to the “manageSqlDatabase” task in tarantino. Originally it looked like this:
<target name="manageSqlDatabase">
<tstamp></tstamp>
<manageSqlDatabase
action="${action}"
scriptDirectory="${database.script.directory}"
database="${database.catalog}"
server="${database.server}"
integratedAuthentication="${database.integrated}"
username="${database.username}"
password="${database.password}"
/>
<if test="${action != 'Drop'}">
<echo message="Current Database Version: ${usdDatabaseVersion}" />
</if>
</target>
The newer version looks like this:
<target name="manageSqlDatabase">
<tstamp></tstamp>
<manageSqlDatabase
action="${action}"
scriptDirectory="${database.script.directory}"
database="${database.catalog}"
server="${database.server}"
integratedAuthentication="${database.integrated}"
username="${database.username}"
password="${database.password}"
skipFileNameContaining="${database.skipFileNameContaining}"
/>
<if test="${action != 'Drop'}">
<echo message="Current Database Version: ${usdDatabaseVersion}" />
</if>
</target>
As you can see, the difference is skipFileNameContaining attribute. By specifying value to this attribute, you can effectively skip execution of those sql file under Update/ExistingSchema folder for which file name contains the specified word (in case sensitive manner) .
In our case we adopted convention that
- We will never put schema change and data script in same sql file
- We will include work _Data_ as part of file name when doing data insert or update
This change is available in tarantino from revision
142 onwards from trunk
Develop smartly :)