Almost every software project comes with a database. Sometimes it will be developed from scratch and in parallel with the actual domain model for a new (aka. 'greenfield') project, sometimes it will be a pre-existent datastore provided by the customer or coming from an already running software that has to be extended (then we can call it a 'brownfield' project). In either case, you will likely end up with a bunch of database scripts that need to be executed as part of your installation process.
I often came across this scenario during my professional life, and more often than not that DB script stuff is treated somewhat carelessly. After all, it isn't a 'real' software project and managing it is not immediately familiar to most software developers. For some of us, the database is just something that has to be there, all other questions are left to the DB admins. But on the other hand, "getting the database right" and keeping it in parallel with the rest of the software is crucial for a software project - and something that often causes a significant amount of problems. We definitely should give it some more love.
That's why I took me some time and came up with a solution to automate script execution for a MS SQL Server database. The sample project can be downloaded from here. It uses the AdventureWorksLT sample database from Microsoft (which you could get here, but note that downloading it is not necessary - the sample solution will build it from scratch). For code execution, I will use the Gallio/MbUnit testing framework - this (or any other xUnit framework) is a well-suited tool not only for unit testing but for any kind of code automation...
Ok now, the first things that we need are SQL-scripts that can be executed by the SqlCmd.exe command line tool to create our database from scratch. In most cases the scripts can be easily extracted from the DB instance with SQL Server Management Studio ("script table as... | CREATE to... | file..." or something similar). However, this is not the case for the CREATE DATABASE script, because we want to use a variable for the name of the new database and MS SQL Server does not allow the usage of variables for db objects. So to be able to dynamically provide the database name to the CREATE DATABASE script at runtime, we have to do a little string replacement magic. This is how the final script looks like:
USE [master]
GO
-- **************************************************************************************
-- This somewhat strange template replacement mechanism is required because MS SQL Server
-- doesn't allow using variables in a create table statement for the new db.
-- **************************************************************************************
DECLARE @template nvarchar(max);
SET @template = '
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = ''__dbname__'')
begin
DROP DATABASE __dbname__
PRINT(''Database [__dbname__] dropped for recreation...'')
end
CREATE DATABASE [__dbname__]
IF (1 = FULLTEXTSERVICEPROPERTY(''IsFullTextInstalled''))
begin
EXEC [__dbname__].[dbo].[sp_fulltext_database] @action = ''enable''
end
ALTER DATABASE [__dbname__] SET ANSI_NULL_DEFAULT OFF
ALTER DATABASE [__dbname__] SET ANSI_NULLS OFF
ALTER DATABASE [__dbname__] SET ANSI_PADDING OFF
ALTER DATABASE [__dbname__] SET ANSI_WARNINGS OFF
ALTER DATABASE [__dbname__] SET ARITHABORT OFF
ALTER DATABASE [__dbname__] SET AUTO_CLOSE OFF
ALTER DATABASE [__dbname__] SET AUTO_CREATE_STATISTICS ON
ALTER DATABASE [__dbname__] SET AUTO_SHRINK OFF
ALTER DATABASE [__dbname__] SET AUTO_UPDATE_STATISTICS ON
ALTER DATABASE [__dbname__] SET CURSOR_CLOSE_ON_COMMIT OFF
ALTER DATABASE [__dbname__] SET CURSOR_DEFAULT GLOBAL
ALTER DATABASE [__dbname__] SET CONCAT_NULL_YIELDS_NULL OFF
ALTER DATABASE [__dbname__] SET NUMERIC_ROUNDABORT OFF
ALTER DATABASE [__dbname__] SET QUOTED_IDENTIFIER OFF
ALTER DATABASE [__dbname__] SET RECURSIVE_TRIGGERS OFF
ALTER DATABASE [__dbname__] SET DISABLE_BROKER
ALTER DATABASE [__dbname__] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
ALTER DATABASE [__dbname__] SET DATE_CORRELATION_OPTIMIZATION OFF
ALTER DATABASE [__dbname__] SET TRUSTWORTHY OFF
ALTER DATABASE [__dbname__] SET ALLOW_SNAPSHOT_ISOLATION OFF
ALTER DATABASE [__dbname__] SET PARAMETERIZATION SIMPLE
ALTER DATABASE [__dbname__] SET READ_COMMITTED_SNAPSHOT OFF
ALTER DATABASE [__dbname__] SET READ_WRITE
ALTER DATABASE [__dbname__] SET RECOVERY FULL
ALTER DATABASE [__dbname__] SET MULTI_USER
ALTER DATABASE [__dbname__] SET PAGE_VERIFY CHECKSUM
ALTER DATABASE [__dbname__] SET DB_CHAINING OFF
PRINT(''Database [__dbname__] created...'')';
DECLARE @sqlscript nvarchar(max);
SET @sqlscript = REPLACE(@template, '__dbname__', '$(dbname)');
exec(@sqlscript);
GO
With this script, we can execute the following command line (which is what we need):
sqlcmd -U <usr> -P <pwd> -S <srv> -i "<script>" -v dbname=<db>
All other scripts for the various database objects can be left unchanged like they are generated by Manage-ment Studio. They can be executed via SqlCmd.exe with the below command line:
sqlcmd -U <usr> -P <pwd> -S <srv> -d <db> -i "<script>"
We place all these scripts in a well-known folder and add it to a VS solution (this is btw. the only method to put a database under Source Control). Then we add a class library project to the solution, that will contain one single class: The 'test fixture' that acts as a kind of 'script execution engine' (I named it CreateDatabaseFixture). The final solution will look something like this in VS' Solution Explorer:
Now that we have everything in place, we can finally write some code for our CreateDatabaseFixture class. First we define some constants for paths, filenames and database credentials, along with some convenience properties for data access:
namespace ScriptAutomation
{
/// <summary>
/// Gallio/MbUnit fixture to automate db script execution via SqlCmd.
/// </summary>
[TestFixture]
public class CreateDatabaseFixture
{
private const string SqlCmdExe = @"""C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SqlCmd.exe""";
private const string ScriptFolder = @"..\..\..\Database\Scripts";
private const string CreateDatabaseScript = "_dbCreate.sql";
private const string CreateSchemaScript = "Create-Schema-SalesLT.sql";
private const string CreateTypesScript = "Create-Types.sql";
private const string User = "test";
private const string Password = "test";
private const string Server = "DB";
private const string Database = "AdventureWorksLT_DEMO";
private const string Schema = "SalesLT";
/// <summary>
/// Builds a MS sql server connection string from the constants.
/// </summary>
/// <value>The MS sql server connection string.</value>
private static string ConnectionString
{
get
{
return string.Format("Data Source={0};Initial Catalog={1};User ID={2};Password={3}",
Server, Database, User, Password);
}
}
/// <summary>
/// Gets a db connection.
/// </summary>
/// <value>The db connection.</value>
private static IDbConnection DbConnection
{
get { return new SqlConnection(ConnectionString); }
}
...
} // class CreateDatabaseFixture
} // namespace ScriptAutomation
The "heart" of our 'test fixture' then is the method that executes a given SQL-script - it uses an instance of the System.Process class to run the SqlCmd.exe tool and to provide it with the above shown command line. Additionally, the method asserts that the execution was actually successful and writes any console output to the test log:
/// <summary>
/// Executes the <c>SqlCmd</c> script and verifies the result.
/// </summary>
/// <remarks>
/// Writes the process output to the <see cref="TestLog"/>. Asserts that
/// <c>SqlCmd</c>'s exit code is 0 (zero) and that there is no error output.
/// </remarks>
/// <param name="script">The script.</param>
private static void ExecuteSqlCmdScript(string script)
{
script = Path.Combine(ScriptFolder, script);
using (Process proc = new Process())
{
string arguments = string.Format(@"-U ""{0}"" -P ""{1}"" -S ""{2}"" -d ""{3}"" -i ""{4}""",
User, Password, Server, Database, script);
proc.StartInfo.FileName = SqlCmdExe;
proc.StartInfo.Arguments = arguments;
proc.StartInfo.UseShellExecute = false;
proc.StartInfo.RedirectStandardOutput = true;
proc.StartInfo.RedirectStandardError = true;
proc.StartInfo.CreateNoWindow = true;
proc.Start();
proc.WaitForExit();
string output = proc.StandardOutput.ReadToEnd();
string error = proc.StandardError.ReadToEnd();
if (!string.IsNullOrEmpty(output))
{
TestLog.WriteLine(output);
}
if (!string.IsNullOrEmpty(error))
{
TestLog.WriteLine();
TestLog.WriteLine("Error output:");
TestLog.WriteLine("************");
TestLog.WriteLine(error);
}
Assert.AreEqual(0, proc.ExitCode,
"Process exitcode (SqlCmd.exe) is not zero.");
Assert.IsTrue(string.IsNullOrEmpty(error),
"Execution of script '{0}' produced some error output (see test log).",
script);
}
}
(Note: This method is not suitable for the CREATE DATABASE script, since we need a somewhat different command line for that. The CreateDatabaseFixture class contains an extra method for this purpose, which is almost identical to the above one. Also there are some similar methods for database objects other than tables. They are not shown here, because they don't provide any additional information.)
With the ExecuteSqlCmdScript() 'workhorse' and another helper method, called VerifyTableCreation(), our final 'test' to create all tables, looks as simple as this:
[Test, DependsOn("CreateBlankDatabase"), DependsOn("CreateSchema"), DependsOn("CreateTypes")]
[Factory("TablesAndScripts")]
public void CreateTables(string tableName, string scriptName)
{
ExecuteSqlCmdScript(scriptName);
VerifyTableCreation(tableName);
}
To run this data-driven 'test', we also need a factory method (TablesAndScripts()) that lists all our tables and related scripts. Here it is:
/// <summary>Datasource for the <see cref="CreateTables"/> method.</summary>
/// <remarks>This is executed in strict top-to-bottom order (dependencies!).</remarks>
/// <returns>Tables and their related Create-Scripts</returns>
public IEnumerable TablesAndScripts()
{
yield return new object[] { "ProductCategory", "Create-Table-ProductCategory.sql" };
yield return new object[] { "ProductModel", "Create-Table-ProductModel.sql" };
yield return new object[] { "Product", "Create-Table-Product.sql" };
yield return new object[] { "ProductDescription", "Create-Table-ProductDescription.sql" };
yield return new object[] { "ProductModelProductDescription", "Create-Table-ProductModelProductDescription.sql" };
yield return new object[] { "Address", "Create-Table-Address.sql" };
yield return new object[] { "Customer", "Create-Table-Customer.sql" };
yield return new object[] { "CustomerAddress", "Create-Table-CustomerAddress.sql" };
yield return new object[] { "SalesOrderHeader", "Create-Table-SalesOrderHeader.sql" };
yield return new object[] { "SalesOrderDetail", "Create-Table-SalesOrderDetail.sql" };
}
The CreateTables() method will execute once for each item that TablesAndScripts() returns. Here's the resulting test report for an execution of the entire CreateDatabaseFixture class:
As you can see, the execution order is exactly like we wanted it to be. The code imposed a strict sequence on the various test methods by using MbUnit's DependsOn attribute on method level together with the declaration order inside the TablesAndScripts() factory method. This may sound somewhat trivial, but it's not. Gallio is a unit testing framework, and good unit tests are supposed to not depend on each other. Consequently, the framework normally does not guarantee for any execution order. However, for this particular use case, controlling this order is of course absolutely crucial.
The here outlined methodology is also a good way to keep up with historical schema changes, like they often occur during the lifecycle of a software project and its accompanying database: If you alter something on your database schema, SQL Server Management Studio can generate a change-script for you, which you then can manage and execute just like all the other SQL-scripts. Just add another line to the end of the TablesAndScripts() factory method for each new change-script, it will be executed in the desired order.
I provided a sample solution (VS 2008) with the above described 'test fixture' and the SQL-scripts. To run it, you will need access to an SQL server instance and you must have the Gallio framework installed, which you can download from here (it's free).