FluentMigrator – Setting the collation on a column

I got a question via a Twitter DM about how to set the collation for a column in a FluentMigrator migration. I gave a quick answer there but here is a longer answer for anyone stuck on this.

Using AsCustom

There is no explicit support for setting collation per column in FluentMigrator but it can be done per database. When creating a column using the WithColumn attributes you can use the AsCustom to set the type with any valid sql. This will be specific for one database and will not be translated to the correct form for other databases.

Here is an example for MS Sql Server where the “test” column’s collation is set to Latin1_General_CI_AS using the COLLATE clause:

public override void Up()
        .WithColumn("test").AsCustom("varchar(10)COLLATE Latin1_General_CI_AS");

Supporting non-standard SQL for multiple databases

FluentMigrator supports a lot of databases and has fluent expressions that support most of the common scenarios for all of them. However, these databases have loads of different features like collation that are not standard ANSI SQL. FluentMigrator will never be able to support all of these. Occasionally you will need to handle edge cases like these for multiple databases.

The way to do this is using FluentMigrator’s IfDatabase. This is quite simple. Just add IfDatabase before the create expression:

    .WithColumn("test").AsCustom("varchar(10)COLLATE Latin1_General_CI_AS");

    .WithColumn("test").AsCustom("text collate \"en_US\"");

And now you can write migrations with non-standard SQL for multiple databases in the same migration.

Getting around Nuget’s External Package dependency problem

I’ve already blogged about how to create simple Nuget packages using Rake and Albacore, now to the advanced part.

I used FluentMigrator as the example in my previous post and it was while creating the second package for FluentMigrator that I ran into a ‘feature’ of Nuget.

We had decided to divide the FluentMigrator package into two packages, the main core package and a new FluentMigrator.Tools package. FluentMigrator.Tools contains all the extra dlls that we didn’t want to put in the core package. Basically, there are four versions of the tools directory, one for each supported .NET version and per platform (.NET 3.5 and 4.0, x86 and x64). The idea is that if you need the x86 3.5 version of the migration runners then the core package is sufficient otherwise you need to fetch the FluentMigrator.Tools Nuget package.

The core package has a lib directory which contains the FluentMigrator dll. So I created a dependency on the core package in the Tools package so that when you download the Tools package from Nuget it also downloads the core package. This meant that the Tools package only contained a tools directory at the root, no lib and no content directory.

Side Note on how to test your Nuget package

To test your newly created Nuget package (always a good idea!) point your Nuget source in Visual Studio to your nupkg file. In Visual Studio 2010 go to Tools->Library Package Manager->Package Manager Settings then choose Package Sources in the left hand menu and add the directory which contains the nupkg file to be tested. Then when adding a package from the Package Manager Console, in the Package Source dropdown menu to the upper left you should have two choices now; Nuget official package source and your new test source. So choose your test source and install-package NameOfYourPackage and test it out.

To the heart of the matter

While testing the Tools package in the Package Manager Console I got the following incomprehensible error:

External packages cannot depend on packages that target projects

Steve Sanderson ran into this problem first and filed an issue on Codeplex in which he also mentions a workaround (Thank you Steve!). The reason for this error is that my new FluentMigrator.Tools package only contains a tools directory and no content or lib directory and Nuget has decided that it therefore may not be dependent on a package that does contain a lib or content directory.

The solution is a big, fat hack. Add a content directory with a dummy text file and then use a powershell script to remove it from the target project after you have installed the Nuget package.

Diving into PowerShell

At this stage you will want to have the Nuget documentation open so that you check out the different conventions around powershell and Nuget. I created a text file named InstallationDummyFile.txt and placed it in the content directory and a powershell file named install.ps1 and placed it in the root of the tools directory. install.ps1 looks like this:

param($installPath, $toolsPath, $package, $project)

$project.ProjectItems | ForEach { if ($_.Name -eq "InstallationDummyFile.txt") { $_.Remove() } }
$projectPath = Split-Path $project.FullName -Parent
Join-Path $projectPath "InstallationDummyFile.txt" | Remove-Item

It does two things:

  • Removes the reference to InstallationDummyFile.txt from the project by looping through ProjectItems until it finds the text file.
  • Deletes the InstallationDummyFile.txt file. First I get the path to the project file and store it in $projectPath and create a new path by joining it with the file name and then remove it from the project directory.

I am a total beginner when it comes to powershell so this was a bit of a struggle (and maybe will be for others?). The above calls are pipelines and start on the left with a bar (|) separating the different stages. E.g. The Join-Path function takes in two parameters and returns a new path to the text file, this is then passed on the next stage in the pipeline as a parameter. Remove-Item takes in the newly generated path as a parameter and deletes the file. Read the Nuget documentation for more info on the parameters passed in on the first line.

The Result

Now when you install the Tools package it first downloads its dependency, the core package, and adds a reference to the FluentMigrator dll in the target project. As the Tools package now has a content file, Nuget no longer classes it as an “external package”. Next Nuget installs the Tools package in the packages directory alongside the core package and calls install.ps1. This removes the dummy file from the project (both as a reference and by physically deleting the file from the project folder) and now there is no incriminating evidence left of us fooling Nuget. When creating my Rake script to run migrations, I can now refer to the console runner in the FluentMigrator.Tools directory in the Nuget package directory. Mission accomplished.

Creating Nuget packages with Rake and Albacore

Creating a Nuget package is quite simple in theory and creating one manually can be done in a few minutes. But simple, repetitive tasks are easy to mess up and a tested build script will never include the wrong config file because it was thinking about coffee and cinnamon buns. Besides who wants to do this by hand every release, it’s a perfect task to be automated.

I helped to change FluentMigrator’s packaging script to create a Nuget package a while ago, so I’ll use that to illustrate how to create a Nuget package with Rake and Albacore.

All Nuget packages need a nuspec file and there are two ways to script this. The first way is to just create a nuspec file manually and then update the version with the build script. Nancy uses this approach, see here. They parse the nuspec files with an XML parser to find the right nodes, such as version, to update. Nancy has a lot of packages so they search after nuspec files and create a package when they find one. With FluentMigrator we only have two packages so I used Albacore and the Nuspec task to create the nuspec file:

desc "create the FluentMigrator nuspec file"
  nuspec :create_spec do |nuspec|
     version = "#{ENV['version']}"

     nuspec.id = "FluentMigrator"
     nuspec.version = version.length == 7 ? version : FLUENTMIGRATOR_VERSION
     nuspec.authors = "Josh Coffman"
     nuspec.owners = "Sean Chambers"
     nuspec.description = "FluentMigrator description which is really long."
     nuspec.title = "Fluent Migrator"
     nuspec.language = "en-US"
     nuspec.projectUrl = "https://github.com/schambers/fluentmigrator/wiki/"
     nuspec.working_directory = "packages/FluentMigrator"
     nuspec.output_file = "FluentMigrator.nuspec"

The only bit of logic here is for setting the version. There is a default version defined as constant but this can be overwritten by passing in a command line parameter.

FluentMigrator has a working directory for packaging where I copied in the files to be included in the Nuget package. Nuget uses conventions for the directory names in the working directory. The three conventions are:

  1. libfor the assembly references to be installed into the target project,
  2. toolsfor the command line tools and powershell scripts and
  3. content for files that are copied into the root of the target project.

The main FluentMigrator package only uses lib and tools. Lib contains two subdirectories one for .NET 3.5 and one for 4.0. So the FluentMigrator dlls are copied into the appropriate lib subdirectories, the .NET 3.5 dll into lib\35 and the 4.0 dll into lib\40. Into the tools directory go all the different runners: the command line runner, the MSBuild runner and the Nant runner as well as all the dlls for the supported sql providers.

Then you just need to run the Nuget command line and call the pack command to create a nupkg file:

def nuget_pack(base_folder, nuspec_path)
    cmd = Exec.new
    output = 'nuget/'
    cmd.command = 'tools/NuGet.exe'
    cmd.parameters = "pack #{nuspec_path} -basepath #{base_folder} -outputdirectory #{output}"

There is an Albacore task for this but I’ve gone with the Exec task instead here.  And this is called like this:

nuget_pack('packages/FluentMigrator/', 'packages/FluentMigrator/FluentMigrator.nuspec')

So to recap; the script creates a nuspec file, copies in the relevant files into the convention based working directory and then finally calls NuGet.exe with the pack command to create a Nuget package.

Pretty easy so far, it did get a bit more complicated building the second FluentMigrator package that is dependent on the first package. But I’ll leave that for another post.

Migrations with Rake, Albacore and FluentMigrator

For my previous projects I have mostly used FluentMigrator in combination with Nant for my migrations but now that there is a new FluentMigratorTask for Albacore (created by Mark Boltuc) I thought I would give Rake a try. For an introduction to FluentMigrator see Sean Chambers’ introduction or this article on FluentMigrator and MSBuild.

First up, you have to install Ruby and RubyGems. For Windows go here and for Ubuntu have a look at this guide. For any other operating systems you’re on your own!

Open up your command prompt (on Windows use the Command Prompt with Ruby that comes with Ruby for Windows), and then:

gem install albacore

Next step is to create a rake build file; to do this create a file named rakefile. The only dependency you need for a basic .NET project is Albacore. So a simple Rake file with the MSBuild Albacore task looks like this:

require 'albacore'

task :default => [:build]

desc "Build"
msbuild :build do |msb|
	msb.properties :configuration => :Release
	msb.targets :Clean, :Build
	msb.solution = "Test.sln"

Check out the Albacore wiki for more on this and other tasks.

The FluentMigrator Albacore task is a wrapper (written in Ruby) around FluentMigrator’s command line tool. So anything you can do with the command line tool you can do with the Albacore task. Here is a simple migrate to latest version rake task:

desc "MigrateDb"
fluentmigrator :migrate do |migrator|
	migrator.command = 'lib/Migrate.exe'
	migrator.provider = 'postgres'
	migrator.target = './Migrations/bin/Debug/Migrations.dll'
	migrator.connection = 'Server=;Port=5432;Database=FluentMigrator;User Id=test;Password=test;'

These four parameters are mandatory. The first parameter specifies where the command line tool is located. The other three are the same as when using the command line tool. See the wiki page for the FluentMigratorTask for more on the different options available.

The migrate task is all I need for most build files but occasionally you might need to rollback a version or rollback to the start. The rollback task below rolls back one step and this is usually good enough for me.

desc "RollbackDb"
fluentmigrator :rollback do |migrator|
	migrator.command = 'lib/Migrate.exe'
	migrator.provider = 'postgres'
	migrator.target = './Migrations/bin/Debug/Migrations.dll'
	migrator.task = 'rollback' migrator.connection = 'Server=;Port=5432;Database=FluentMigrator;User Id=test;Password=test;'

desc "RollbackDbToStart"
fluentmigrator :rollback_all do |migrator|
	migrator.command = 'lib/Migrate.exe'
	migrator.provider = 'postgres'
	migrator.target = './Migrations/bin/Debug/Migrations.dll'
	migrator.task = 'rollback:all'
	migrator.connection = 'Server=;Port=5432;Database=FluentMigrator;User Id=test;Password=test;'

But a more advanced version lets you specify the number of steps to rollback. It is pretty easy to adjust this to use version instead of steps i.e. roll back to version 2 instead of roll back 2 steps.

 desc "RollbackDbByXSteps"
fluentmigrator :rollback_with_steps, :steps do |migrator, args|
	args.with_defaults(:steps => 1)
	migrator.command = 'lib/Migrate.exe'
	migrator.provider = 'postgres'
	migrator.target = './Migrations/bin/Debug/Migrations.dll'
	migrator.connection = 'Server=;Port=5432;Database=FluentMigrator;User Id=test;Password=test;'
	migrator.task = 'rollback'
	migrator.steps = args[:steps]

This is then called like this:

 rake rollback_with_steps[2] 

And that should be enough to get you started with Rake, Albacore and FluentMigratorTask!