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()
{
    Create.Table("Order")
        .WithColumn("Id").AsInt32().NotNullable().Identity().PrimaryKey()
        .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:

IfDatabase("sqlserver").Create.Table("Order")
    .WithColumn("Id").AsInt32().NotNullable().Identity().PrimaryKey()
    .WithColumn("test").AsCustom("varchar(10)COLLATE Latin1_General_CI_AS");

IfDatabase("postgres").Create.Table("Order")
    .WithColumn("Id").AsInt32().NotNullable().Identity().PrimaryKey()
    .WithColumn("test").AsCustom("text collate \"en_US\"");

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

Build Your Open Source .NET Project On Travis CI

Travis CI is a continuous integration service that lives in the cloud and is free for public Github repositories. When you push a change to your Github repo, Travis CI will automatically detect it and run a build script. It works for all branches and pull requests as well and has some really nice integration with Github. In the screenshots of the Github Pull Requests below you can see the first Pull Request has a Good to merge label and the second one failed.

SafeToMergePRTravis

FailedPRTravis

Travis CI supports loads of languages but not C# and the reason is that Travis CI only supports Linux servers. Although Windows support seems to be on the way.

Being a core committer for FluentMigrator, an OSS .NET project, this is actually just what I was looking for. We have a Teamcity server (thank you Jetbrains and Codebetter) but we don’t have any testing for Mono and Linux. I had seen that the git-tfs project (also .NET and C#) was using Travis CI and thought I’d try and copy their build script. But it was not as simple as that! Here is my guide to getting a .NET project to build on Travis CI.

Sign up for Travis CI

The first step is to sign up on the Travis CI website. You can only sign up for Travis CI via your Github login which makes sense as the service focuses on CI for Github projects only. After signing in you should see your name in the top right corner so click on that to open your profile.

TravisEnableRepo

Enable the Github hook for Travis CI by selecting the appropriate repository (daniellee/FluentMigrator in my case). And that is all you need to do. If the repository contains a file called .travis.yml then it will try and build it. This is triggered after every push to the repo.

XBuild

The second step is to create an MSBuild Xml file that can be run with XBuild. XBuild is the Mono version of MSBuild and uses the same file format. The simplest build script describes which .NET version to build the project in, the platform (x86 or AnyCPU) and the name of the solution file. Here is the MSBuild file for FluentMigrator:

<?xml version="1.0"?>
<Project ToolsVersion="4.0" DefaultTargets="CI" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">

  <Target Name="CI" DependsOnTargets="Build" />

  <Target Name="Build">
    <MSBuild Projects="FluentMigrator (2010).sln" Properties="Configuration=Debug;Platform=x86" />
  </Target>

</Project>

Create A Travis YAML File

The next step is to create a .travis.yml file. A file with the extension .yml is a YAML file, a file format sort of like JSON but without all the curly braces. Check out the documentation for all the options that can be defined in the YAML file. Here is the YAML file for FluentMigrator:

language: c

install:
  - sudo apt-get install mono-devel mono-gmcs nunit-console

script:
  - xbuild CI.proj
  - nunit-console ./src/FluentMigrator.Tests/bin/Debug/FluentMigrator.Tests.dll -exclude Integration,NotWorkingOnMono

This needs a bit of explanation. Travis CI runs on Ubuntu Linux so before running this script .NET/Mono is not installed so we have to install everything before trying to build and test the project.

As C# and .NET are not supported by Travis we set the language to C.

The install part of the script uses Ubuntu’s package manager, apt-get (Chocolatey is the Windows version) to fetch Mono, the Mono C# Compiler (MCS) and the NUnit console runner. We need MCS to be able to compile our C# code. We need the Mono Development Tools (mono-devel) as it contains Mono 4.0, a CLR implementation that works on Linux. And the NUnit console runner is needed to run tests from the command line.

The first task in the script step is to use xbuild to build the MSBuild file. For FluentMigrator this builds the main solution file that contains all the projects.

The second task is to run all the NUnit tests from one of the test projects. I did try and run NUnit via the MSBuild script but gave up as it was much easier to do this way. There is one very important gotcha to note here. All the examples of using the NUnit console runner use a forward slash (/) for the switches even the man pages (Linux help pages) for nunit-console. It took me a while but I eventually noticed that in the NUnit documentation that it mentions that on Linux you should use a hyphen (-) instead and then either a space or colon after the switch. E.g.  I use the exclude switch to exclude some categories of tests like this: –exclude Integration,NotWorkingOnMono

Deploy It!

Now it is time to cross your fingers and do a git push to your repo on Github. I’m sorry but there is no way it is going to work first time unless you have already spent some time building it on Linux. It took me seven attempts to get it to build successfully and another five attempts before I got all the tests to pass.

Potential Pitfalls

Here is a list of all the problems I ran into. Though I’m sure that there are loads of other problems that could occur in your project.

Linux is case sensitive

There is a class named SqliteQuoter in FluentMigrator (with exactly that casing) and I got the following slightly baffling error on my first run:

error CS2001: Source file `Generators/SQLite/SqliteQuoter.cs’ could not be found

When I looked at the actual filename (it looked perfectly correct in Visual Studio) I saw that it had this casing: SQLiteQuoter. As just changing the casing of the filename is not a change in Git you have to explicitly rename the file.

git mv -f SQLiteQuoter.cs SqliteQuoter.cs

The next casing problem was just as baffling. It turned out that in the csproj file a reference to System.Data.SQLite.dll looked like this: System.Data.SQLite.DLL. And that meant that Linux could not match the filenames. I fixed that by manually editing the csproj file and manually changing the case.

I got stuck on another issue for a while but it turned out that it was nothing to do with Travis CI or Linux. After that was fixed I got a green build but the test runner did not run. The problem was the one I talked about before with the NUnit console runner and using a hyphen instead of a forward slash.

NewLine

Travis CI now ran all of FluentMigrators’ tests (except for the integration tests) and had 21 failing tests out of 1254. Most of the failing tests were due to the new line problem. FluentMigrator is (was) littered with \r\n which is the Windows new line character (Carriage Return and Line Feed). In Linux a new line is \n (Line Feed) only. The rather tedious solution to this was to go through the code and replace all of instances of \r\n with Environment.NewLine.

File Paths

A much harder to solve issue is file paths. They are fundamentally different on Windows and Linux. For some tests I could use Path.Combine and Path.DirectorySeparatorChar to get them to pass but others had to be ignored (for now).

And at attempt number twelve I had FluentMigrator being built and all the tests run successfully on Mono and Ubuntu Linux. And there are not a lot of open source .NET projects that can say that.

Next Step

Travis CI has support for MySql, Postgres and Sqlite and it would be fantastic if I could get all the integration tests running against them. Next week maybe.

Hope this helps out some of the other OSS .NET projects.

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"
  end

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}"
    cmd.execute
  end

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"
end

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=127.0.0.1;Port=5432;Database=FluentMigrator;User Id=test;Password=test;'
end

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=127.0.0.1;Port=5432;Database=FluentMigrator;User Id=test;Password=test;'
end

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=127.0.0.1;Port=5432;Database=FluentMigrator;User Id=test;Password=test;'
end

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=127.0.0.1;Port=5432;Database=FluentMigrator;User Id=test;Password=test;'
	migrator.task = 'rollback'
	migrator.steps = args[:steps]
end

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!