Jay Harris is Cpt. LoadTest

With our new nightly database restore we now have the desire to automatically run all of the change scripts associated with a project. We’ve found a way; I created a NAnt script that will parse the Visual Studio Database Project (or "DBP") and execute all of the change scripts in it. Here’s how we got there.

Problem 1: Visual Studio Command Files are worthless

Our first idea was to have everyone update a command file in the DBP, and have NAnt run it every night. Visual Studio command files are great and all, but we have discovered a problem with them: they do not keep the files in order. We have named all of our folders (01 DDL, 02 DML, etc) and our change scripts (0001 Create MyTable.sql, 0002 AddInfoColumn to MyTable.sql) accordingly so that they should run in order. We have found that the command file feature of VS.Net 2003 does not keep them in order but rather seems to sort them first by extension, then by order, or some similar oddness. Obviously, if I try to at InfoColumn to MyTable before MyTable exists, I’m going to have a problem. So, the command file idea was axed.

Problem 2: Visual SourceSafe contents can’t be trusted

Our second idea was to VSSGET the DBP directory in VSS and execute every script in it. However, the VSS store cannot be trusted. If a developer creates a script in VS.Net called ‘0001 Crate MyTable.sql’ and checks it in to the project, then proceeds to correct the spelling error in VS.Net to ‘0001 Create MyTable.sql’, VS does not rename the old file in VSS. Instead, it removes the old file from the project, renames it locally, then adds the new name to the project and to VSS. It also never deletes the old file name from the VSS store. Now, both files (’0001 Crate MyTable.sql’ and ‘0001 Create MyTable.sql’) exist in VSS. Performing a VSSGET and executing all scripts will run both scripts, which could lead to more troubles.

So, we can’t use a command file, because it won’t maintain the order. We can’t trust VSS, since it can have obsolete files. We can only trust the project, but how do we get a list of files, ourselves?

Fortunately, DBP files are just text in a weird XML-wannabe format. The NAnt script will open the file and run through it looking for every ‘SCRIPT’ entry in the file. If it finds a ‘BEGIN something’ entry, it assumes that ’something’ is a folder name, and appends it to the working path until it finds ‘END’, at which time it returns to the parent directory.

It’s not perfect. It still runs in to some problems, but here it is in v0.1 form.

<project name="RunDBPScripts" default="RunScripts">
<!–-
Execute all scripts in a VS.Net DBP
Author: Jay Harris, http://www.cptloadtest.com, (c) 2005 Jason Harris
License: This work is licensed under a  
   Creative Commons Attribution 3.0 United States License.  
   http://creativecommons.org/licenses/by/3.0/us/ 

This script is offered as-is.
I am not responsible for any misfortunes that may arise from its use.
Use at your own risk.
-–>
<!-– Project: The path of the DBP file –->
<property name="project" value="Scripts.dbp" overwrite="false" />
<!-– Server: The machine name of the Database Server –->
<property name="server" value="localhost" overwrite="false" />
<!-– Database: The database that the scripts will be run against –->
<property name="database" value="Northwind" overwrite="false" />
<target name="RunScripts">
        <property name="currentpath"
            value="${directory::get-parent-directory(project)}" />
        <foreach item="Line" property="ProjectLineItem" in="${project}">
            <if test="${string::contains(ProjectLineItem, 'Begin Folder = ')}">
                <regex pattern="Folder = &quot;(?’ProjectFolder’.*)&quot;$"
                    input="${string::trim(ProjectLineItem)}" />
                <property name="currentpath"
                    value="${path::combine(currentpath, ProjectFolder)}" />
            </if>
            <if test="${string::contains(ProjectLineItem, 'Script = ')}">
                <regex pattern="Script = &quot;(?’ScriptName’.*)&quot;$"
                    input="${string::trim(ProjectLineItem)}" />
                <echo message="Executing Change Script (${server+"\"+database}): ${path::combine(currentpath, ScriptName)}" />
                <exec workingdir="${currentpath}" program="osql"
                    basedir="C:\Program Files\Microsoft SQL Server\80\Tools\Binn"
                    commandline=’-S ${server} -d ${database} -i “${ScriptName}" -n -E -b’ />
            </if>
            <if test="${string::trim(ProjectLineItem) == 'End’}">
                <property name="currentpath"
                    value="${directory::get-parent-directory(currentpath)}" />
            </if>
        </foreach>
    </target>
</project>

I used an <EXEC> NAnt task rather than <SQL>. I found that a lot of the scripts would not execute in the SQL task because of their design. VS Command Files use OSQL, so that’s what I used. I guess those command files were worth something after all.

If you know of a better way, or have any suggestions or comments, please let me know.