NMigrate

• NMigrate codeplex site

Content

• Problem
• Solution
• FileHandlers
  • BatchFileHandler
  • SqlFileHandler
  • ConfigFileHandler
  • CsFileHandler
• How to run the migration and get results
• Conclusion

Problem

Have you already shipped a new release and you had to repeat manually the same error prone procedures on lots of machines ?

If you say no, it means your deployment is automated -continuous deployment- and it's great.

Or maybe your software is not yet in production so you don't have to worry yet about migration.

Or maybe, someone else -an intern- is doing your deployment... but who will waste time when he will break things ?

Sometimes, you deployed changes by hand, sometimes you created your own migration tool, sometimes you used someting like DBMaintain to migrate your data.

DBMaintain is a great tool to help you to migrate your database. However, as a developer, you need to deploy at the same time code and database. You can't dissociate both.

Solution

NMigrate is a framework to help you to create migration project, so you can easily migrate both, code and database, with the same tool.

You will be able to execute your SQL scripts, C# scripts, batch scripts, config file replacement scripts, one after the other, in parallel or not, to your next version.

This tool is in its first release and already in production (for my own company's programs), so you can be sure that I'll do everything to make your (read "my") life the easier I can.

So first of all I want to thanks Terence Parr (creator of Antlr and StringTemplate)for doing such a great and simple template engine.

This guy wrote some books on parsers and DSL. If you want to learn more about this black art, I highly recommend it to you !

NMigrate is using StringTemplate for it's simplicity against other template engine like NVelocity (after trying to use NVelocity, I was not sure if it really was a template engine). I don't plan yet to extend NMigrate to use other template engine. (Simplicity first, time will tell us if we need it)

How to get started ?

A starter project template is provided, just copy the latest template zip file to "My documents\Visual Studio 2010\Templates\ProjectTemplates\Visual C#\NMigrate".

Restart visual studio, and now a new project type is showing

How does it work

So what did it create for us ?

We have

• NMigrate and StringTemplate binaries
• Some scripts samples
• Environment.conf is the configuration of the environment to deploy. (I'll come back later on it)
• Environment.cs is the format of the configuration file as a WCF DataContract.
• Program.cs, well I think you know what it is.

Scripts are prefixed with a version number and executed in order. If two scripts have the same version number, then they are executed in parallel.

That's it, now let's take a look at the Program.cs file.

class Program
{
	static void Main(string[] args)
	{
		JsonConfiguration<Environment> configuration = new JsonConfiguration<Environment>("../../Environment.conf");
		Migration migration = new Migration();
		migration.Configuration = configuration;
		migration.FileHandlers.Find<ConfigFileHandler>().DefaultAppExePath = "NMigrate1.exe";
		migration.FileHandlers.Find<SqlFileHandler>().DefaultConnectionString = configuration.GetValue().Database.FullConnectionString;
		var result = migration.Migrate("../../Scripts", GetCurrentVersion());
		SetCurrentVersion(result.CurrentVersion);
		result.ThrowIfError();
	}

	private static void SetCurrentVersion(int versionNumber)
	{
		File.WriteAllText("Version.txt", versionNumber.ToString());
	}

	private static int GetCurrentVersion()
	{
		if(!File.Exists("Version.txt"))
			return 0;
		return int.Parse(File.ReadAllText("Version.txt"));
	}
}

The code just take the configuration of the environment through the JsonConfiguration class, Environment is the type of the format of this config file.

Let's see Environment.cs, nothing complicated just a classic WCF DataContract with some helper properties :

[DataContract]
public class Environment
{
	[DataContract]
	public class DatabaseEnvironment
	{
		[DataMember]
		public String FullConnectionString
		{
			get;
			set;
		}
		public string ServerConnectionString
		{
			get
			{
				var builder = new SqlConnectionStringBuilder(FullConnectionString);
				builder.InitialCatalog = "";
				return builder.ToString();
			}
		}
		public string DatabaseName
		{
			get
			{
				return new SqlConnectionStringBuilder(FullConnectionString).InitialCatalog;
			}
		}
	}

	[DataMember]
	public DatabaseEnvironment Database
	{
		get;
		set;
	}
}

As you can guess, here is the config file

{
	"Database" :
	{
		"FullConnectionString" : "Initial Catalog=Easydhome;Data Source=.\\SQLEXPRESS;Integrated Security=True;"
	}
}

This config file is supposed to be different when deployment environment are different.

You can extend NMigrate if you want a XML file, it's not really complicated, but do you really need it ?

FileHandlers

The next lines need some explanation

migration.Configuration = configuration;
migration.FileHandlers.Find<ConfigFileHandler>().DefaultAppExePath = "NMigrate1.exe";
migration.FileHandlers.Find<SqlFileHandler>().DefaultConnectionString = configuration.GetValue().Database.FullConnectionString;

I set the configuration to my Migration object then configure FileHandlers, these classes will process your scripts.

There are 4 file handlers out of the box for .bat files, .sql files, cs files, and .config files.

Some file handlers can be templated, it means that they pass through the template engine before being executed. BatchFileHandler return True on IsTemplated(FileInfo) method.

BatchFileHandler

Let's take a look at the 4.CreateMembershipUserTable.bat file in the sample.

"C:\Windows\Microsoft.NET\Framework\v4.0.30319\aspnet_regsql.exe" -d "$o.Database.DatabaseName$" -C "$o.Database.ServerConnectionString$" -A m

As you can see, $placeholders$ are exposed, they have direct access to the environment configuration's file properties.

This file is handled by the BatchFileHandler

By default, Out and Error TextWriter write on the console output (in red for errors).

SqlFileHandler

Let's take an SQL script sample

IF EXISTS(SELECT name FROM sys.databases
WHERE name = '$o.Database.DatabaseName$')
	DROP DATABASE [$o.Database.DatabaseName$]
GO

As you can see, SQL files are also templated.

With the handler type, you can speicify how to choose the connection string given the script file.

DefaultConnectionString is used in Program.cs

migration.FileHandlers.Find<SqlFileHandler>().DefaultConnectionString = configuration.GetValue().Database.FullConnectionString;

If you have multiple database, you can provide a custom handler to select the right connection string based on the script name with the SelectConnectionString property.

migration.FileHandlers.Find<SqlFileHandler>().SelectConnectionString = (file) => file.Name.Contains("SecondaryDB") ? env.SecondConnectionString : env.PrimaryConnectionString;

Internally, this handler use SqlCmd.exe tool, so you have to configure the path to it with the SqlCmdPath, if it is different from the default one.

ConfigFileHandler

Here an exemple of a templatized config file (1.App.config)

<configuration>
	<connectionStrings>
		<add name="myConnection" connectionString="$o.Database.FullConnectionString$"/>
	</connectionStrings>

ConfigFileHandler need to know where to copy and how to rename this config file after placeholder's replacement. For exemple in the Program.cs file we see :

migration.FileHandlers.Find<ConfigFileHandler>().DefaultAppExePath = "NMigrate1.exe";

It means that App.config is for the application NMigrate1.exe, so after being templatized, the config file will be moved to "NMigrate1.exe's" directory and renamed NMigrate1.exe.config.

ConfigFileHandler is not limited to executable config file.

• *.Web.config script are templated and copied to DefaultWebSitePath.
• *.App.config script are templated and copied to DefaultAppPath.
• *.*.exe.config files are copied to there corresponding folder of ApplicationPaths.
• *.*.config files are copied to there corresponding website folder in WebsitePaths.

CsFileHandler

C# scripts are compiled on the fly during execution with CodeDom and reference already loaded assemblies of your current AppDomain.

Sometimes, you will have compilation error because of not referenced assemblies, it means that you AppDomain has not yet loaded this assemblies when compiling the script.

To load it in the AppDomain, just declare a null variable of a type contained in the assembly before starting migration, and the CLR will load it.

Let's take a look at the script 5.CreateUsers.Mock.cs

public class CreateUsers : IExecutable
{
	#region IExecutable Members

	public void Execute(Configuration args)
	{
		SqlMembershipProviderFactory factory = new SqlMembershipProviderFactory();
		factory.SetLowSecurity();
		factory.ConnectionString = args.GetValue<Environment>().Database.FullConnectionString;

		var success = factory.CreateMembershipProvider().CreateUser("TestUser", "password");
		if(success != MembershipCreateStatus.Success)
			throw new Exception("Unexpected result when creating test user " + success.ToString());
	}

	#endregion
}

As you can see, you have full access to the environment's configuration

SqlMembershipProviderFactory, is a helper type of NMigrate to create SqlMembershipProviders without using the config file.

Error are handled simply by throwing an exception that you will be able to see with MigrationResult.Error or by calling MigrationResult.ThrowIfError().

How to run the migration and get results

To run the migration from the current version use Migratoin.Migrate(string path, int currentVersion) method, this method is not expected to throw.

var result = migration.Migrate("../../Scripts", GetCurrentVersion());
SetCurrentVersion(result.CurrentVersion);
result.ThrowIfError();

Results are retrieved by version, and by script file

Conclusion

That's it for now, this tool will evolve depending on your needs, my priority is keep common things simple, I'll do my best to not over design it.