-
Notifications
You must be signed in to change notification settings - Fork 34
Manual
Mig# can be used to modify database schemas directly without any version. Have a look at the Quickstart for an example. The fluent API used to specify the altering operations is the same like the one used for migrations. So read on for more information.
Before starting to use Mig# as a migration library, make sure to understand that Mig# is mostly about portable migrations (see Feature Overview). If you are trying to leverage database platform specific functionality, Mig# is the wrong tool for you unless you want to use its custom SQL API (see below).
A migration (step) contains instructions of how to alter the state of a database from one version to another.
The important thing to note here is that one specific version denotes a well-defined state of the database. This is the crux of database migration frameworks. Given a database at a specific version, you exactly know the state of that database. This does not necessarily include the data stored in it (except maybe for some default data that you deliver a part of your application), but rather includes the schema.
The first thing you have to do when writing a migration is to create a class which will hold the migration instructions and implement the IMigration interface:
public interface IMigration
{
void Up(IDatabase db);
}As you can see, there’s only one method to implement: the Up method. As sole parameter, it gives you the database on which you can perform your changes. For instance, if you want to create a new table called “Customer” with one identity column called “Id” and one additional column called “Name”, you would do something like this:
db.CreateTable("Customers")
.WithPrimaryKeyColumn("Id", DbType.Int32).AsIdentity()
.WithNotNullableColumn("Name", DbType.String).OfSize(255);This is it.
Sometimes you also want to make your migration reversible (although this might be very hard to do for every migration). In this case, implement the IReversibleMigration interface (which inherits from the IMigration interface) which adds another method called Down. In our previous example you would do:
db.Tables["Customers"].Drop();Note the usage of the indexer on the Tables collection. This collection contains all existing tables that were created before. More specifically, it only acts as if it would know all tables. You can specify any table name as a parameter to the indexer. If that table does not exist at run-time, you will get a System.Data.Common.DbException (which rolls-back the migration step).
Mig# works with the concept of timestamps to denote specific database versions. There are two things you need to know about timestamps in Mig#:
- Each migration is associated with one specific timestamp.
- A timestamp is simply a
longnumber, the higher the number, the newer the migration.
So the question is, how does a migration specify the timestamp it belongs to?
Answer 1: The Mig# default is to derive the timestamp from the migration’s type name. In this scheme, each migration type name must be post-fixed with a long number.
Example migration names:
-
Migration1,Migration2– Would have timestamps of 1 and 2 respectively. -
Migration20110216175032,Migration20110216175412– Would have timestamps of 20110216175032 and 20110216175412
Please see the section Timestamp Approaches below for the relative merits of these two naming schemes.
Answer 2: If the default method of specifying timestamps does not suit your needs then you can customize how the timestamp is read from a migration by exporting a class which implements the IMigrationTimestampProvider interface:
public interface IMigrationTimestampProvider
{
long GetTimestamp(Type migration);
}As you can see, there’s only one method to implement: the GetTimestamp method. As a sole parameter, it gives you the type of the migration which, using reflection, allows you to examinine the migration to determine the timestamp.
The following example shows how to create a timestamp provider which retrieves a timestamp for a migration by examining it for a MigrationTimestamp attribute:
The timestamp provider implementation:
[MigrationTimestampProviderExport]
public class AttributeMigrationTimestampProvider : IMigrationTimestampProvider
{
public long GetTimestamp(Type migration)
{
if (migration == null) throw new ArgumentNullException("migration");
// Examine the migration's metadata via reflection for the MigrationTimestampAttribute
var timestampAttr = (MigrationTimestampAttribute) migration.GetCustomAttributes(typeof (MigrationTimestampAttribute), false).FirstOrDefault();
if (timestampAttr == null)
throw new ArgumentException(string.Format(CultureInfo.CurrentCulture, "Could find timestamp attribute on migration ({0}).", migration.Name));
// Retrieve the timestamp from the attribute
return timestampAttr.Timestamp;
}
}The timestamp attribute:
public sealed class MigrationTimestampAttribute : Attribute
{
public long Timestamp { get; set; }
public MigrationTimestampAttribute(long timestamp)
{
Timestamp = timestamp;
}
}The migration:
[MigrationTimestamp(201211171825)]
private class TimestampAttributeTestMigration : IMigration
{
// ...
}For more examples of timestamp provider implementations, see the class MigrationTimestampProviderTests in the MigSharp.NUnit project.
In order to support a multi-module scenario where each application module has its own database versioning all residing in the same database, Mig# allows for associating migrations with modules and upgrade (or downgrade) these independently. To support this scenario, timestamp providers can also be associated with a specific module. In order to associate a timestamp provider with a module, specify the optional ModuleName property in the [MigrationTimestampProviderExport] attribute.
Regardless of how your timestamp is being read from the migration, you have two timestamp approaches to choose from (both with their up and down-sides):
1. Use a linear naming scheme for your migrations (e.g. your migrations are called Migration1, Migration2, etc.).
2. Use time-stamp as version numbers (e.g. your migrations are called Migration20110216175032, Migration20110216175412, etc.).
The advantage of the first approach is that database versions are easier to read and interpret. The timestamp number corresponds to exactly one specific version of the database.
The advantage of the second approach is that you can always add “missing” migrations in-between existing migrations. Mig# always keeps a record of all executed migrations. When it spots a missing entry in the history of the executed steps, it executes the corresponding migration. Thus, the second approach also allows for conflict-free parallel development of migrations. If two developers add migrations at about the same time and one commits before the other, there will be no conflict. Even if the second committer’s migration might have a lower time-stamp, Mig# will pick it up and execute it. This is opposed to the first alternative in which developers need to coordinate their efforts.
The last missing step is to attribute the migration with the MigrationExportAttribute. For example:
[MigrationExport]
internal class Migration2 : IReversibleMigration
{
public void Up(IDatabase db)
{
...Mig# uses the Managed Extensibility Framework (MEF) to import all migrations.
In order to support a multi-module scenario where each application module has its own database versioning all residing in the same database, Mig# allows for associating migrations with modules and upgrade (or downgrade) these independently. In order to associate a migration to a module, specify the optional ModuleName property in the [MigrationExport] attribute.
Additionally, you can specify the optional Tag property in the [MigrationExport]. This information will be stored in Mig#‘s versioning table along with the migration’s timestamp for you to use it otherwise. Mig# will not evaluate or process this parameter anywhere.
The maximum length of the tag is 2000 characters.
Once you have implemented one or more migrations as described above, you want to execute them against a specific database. You can do so by instantiating a Migrator instance and then call the MigrateAll method. For example:
var migrator = new Migrator(ConnectionString, ProviderNames.SqlServer2008);
migrator.MigrateAll(Assembly.GetExecutingAssembly());The first argument to the constructor of Migrator is a connection-string to the database that should be upgraded (or downgraded). Mig# uses the ADO.NET System.Data.Common.DbProviderFactory type to connect to the database (see http://msdn.microsoft.com/en-us/library/system.data.common.dbproviderfactory.aspx). The second parameter is a name that uniquely refers to the “provider” that Mig# should use to execute the migrations. Use the static ProviderNames class to get a list of possible providers. Each provider corresponds to a specific database platform.
The MigrateAll method expects to get the assembly where your migrations are declared. It uses MEF to import these.
If you need to downgrade or upgrade to a specific timestamp (version), you can use the MigrateTo method (instead of MigrateAll). Whereas MigrateAll simply executes all pending migrations, MigrateTo targets a specific timestamp. If there are migrations with higher timestamps than the targeted timestamp that have already been executed before, these will be reversed by calling their Down method. This is how you can undo migrations. If there is any migration on the path to to the targeted timestamp that cannot be reversed (i.e. only implements IMigration instead of IReversibleMigration) you will get an IrreversibleMigrationException.
There are more advanced options and methods on the Migrator class that will not be treated here but the API should be self-explainable.
In some scenarios you might prefer to script the migrations into external files instead of executing them directly on a database. For this purpose you can use the MigrationOptions class. Simply call the OnlyScriptSqlTo method and specify a target directory for the scripts. If you want to execute and script files at the same time, call ExecuteAndScriptSqlTo.
It is important to note that if you are executing custom ADO.NET logic using the Execute call-back method, it is important to use the IDbCommandExecutor interface (which can be retrieved from the IRuntimeContext) to execute non-queries against the database. This ensures that these changes are correctly recorded in the scripts. Also note that not all data types can be scripted (e.g. binary, GUID, etc.).
Mig# uses a fluent API to define schema changes of a migration (see previous examples). The API against IDatabase should be self-explanatory and is well documented using xml-doc. It supports creation of tables, foreign keys, indexes, unique constraints, etc. I will not document every method in detail here. Instead, let me just mention a few noteworthy features. For very specific questions, you might also want to check-out the FAQ.
Sometimes it happens that you have to use specific logic depending on the underlying database platform. In these cases, you can query the migration context to do conditional branching in your migration. For example:
if (db.Context.ProviderMetadata.Name == ProviderNames.SqlServerCe4)
{
// do some SQL Server CE4 specific logic here...Basically, there are two ways of executing hand-rolled SQL in a migration. Both alternatives correspond to overloads of the IDatabase.Execute method:
1. Directly pass a SQL query to be executed. If you do so, make sure to use ANSI SQL that runs on all supported platforms. For example:
db.Execute(@"UPDATE ""Customers"" SET Name = 'Unknown'");2. Specify a call-back that receives the (open) connection and the transaction in which the migration is being executed. This gives you the full flexibility of performing custom ADO.NET logic inside a migration. For example:
db.Execute(context =>
{
IDbCommand command = context.Connection.CreateCommand();
command.Transaction = context.Transaction;
command.CommandText = ...When you execute a batch of migrations, Mig# first performs a validation on these migrations before doing anything physically on the database. Depending on the outcome of the validation, Mig# will then effectively perform the migrations on the database. The validation can result either in validation errors, or in validation warnings (see below).
Although in general, Mig# tries to have a broad coverage of potential cross-db issues, its validation framework cannot substitute for real integration tests of your data access layer (DAL). If you are really serious about supporting a specific database platform, you must add integration tests for this specific platform to test your DAL (which Mig# typically is only a minor portion of it).
In the case of validation errors, the whole migration is aborted with a InvalidOperationException.
Validation warnings are written to the general trace log. Once, the implication of a warning is understood, you can suppress specific warnings using the MigrationOptions type (which is passed to the Migrator).
If your application supports multiple database platforms, then you can add validation against all supported platforms. This helps you write migrations that can execute on all supported platforms. Note that this should be quite an early decision as adding support for a platform retrospectively is rather tedious when you have, for example, started using data types that are not supported by the other platform.
You can add validation for other platforms by using the MigrationOptions type. In the following example, validation for all Oracle platform starting in version 10g is added:
var options = new MigrationOptions();
options.SupportedPlatforms.AddOrReplaceMinimumRequirement(DbName.Oracle10g);Mig# also comes with a console application called Migrate which exposes the most important functionality through a command-line interface. Run Migrate.exe -help to get help on its usage.
Note that Migrate currently is not available through NuGet.
Custom Bootstrapping is a mechanism that allows an application with a legacy schema versioning system to switch to Mig#.
An application that wishes to do so needs to implement the IBootstrapper interface. When Mig# is being executed on a database for the first time, it uses this interface to ask the application about the current version of the schema. With this information, Mig# initializes its versioning table. From this point on, Mig# overtakes the versioning of that specific database. When being called on the same database again, Mig# realizes that it already has its versioning table initialized and therefore does not invoke the bootstrapper interface a second time.
In order to enable custom bootstrapping, call the UseCustomBootstrapping method of the Migrator class before executing migrations.
You can globally configure Mig#’s tracing using the Options type. There are three trace sources:
- General
Contains general trace messages. - Sql
All emitted SQL code is publish through this event source. - Performance
Contains trace messages that tell how long it took to perform certain actions.
You can set the trace levels for each of these individually. For instance, if you want to trace all SQL queries emitted to the database, call
Options.SetSqlTraceLevel(SourceLevels.All);Doing so is simple. A provider needs to implement the IProvider interface, specify which data types it supports by using attributes like this [Supports(DbType.Int32, CanBeUsedAsPrimaryKey = true)], and finally, it needs to export itself, like for example this one: [ProviderExport(ProviderNames.Oracle, "Oracle.DataAccess.Client", MaximumDbObjectNameLength = 30)] and that’s it. Well, almost. You probably want to add some integration tests. There’s ready-to-inherit-from base types for this that execute all kinds of schema changes. Please have a look at the NUnit projects that come with the source code.
If a provider doesn’t support a specific operation (for example SQL Server CE 4 does not support renaming of columns), it can simply throw a NotSupportedException for that method. Mig#’s validation framework will pick-up this information and correspondingly raise validation errors if this method is used.
Of course, you are invited to contribute new provider implementations. Please contact me if you want to do so, so I can coordinate on-going efforts and help you out where I can.