Migrations
Migrations
Summary
This is for SubSonic 2.x Only Migrations help you design your database using code that you write. It's a Rails thing and a lot of people love it.
Give Shawn Oster Some Love== I laid the groundwork for Migrations a while ago, but didn't have the bandwidth to give it the love it deserved. Frankly I don't know if I was smart enough. I asked Shawn Oster to help out and he took his massive swingin geek smarts and put together a pretty cool set of functionality. So if you see Shawn at the local Denver Gamer Shop, setting up for the Monday Night D&D Showdown (LARP-fest), give him a wizardly high-five! ==Video== subsonic_migrations.flv ==The Code
The Migration system works by basically "sucking" out the Migration classes from your project, reading in the code, and then executing it in a virtual compiler. This may seem complex, but it's actually pretty simple and at it's core is what we do to generate the code files anyway. To create a Migration, you have to follow our Conventional system and create a class file, something like 001Initial.cs ... and place that class file into a folder called "Migrations" off the root of your project (you can override this directory convention - see below). This class file must have one class (call it whatever you like), and it has to inherit from SubSonic.Migration:using System; using System.Collections.Generic; using System.Text; using SubSonic; namespace MigrationSample.Migrations { public class Migration001:Migration { } } Each "Migration" consists of changing from one version to another, either up or down. The code, therefore, follows this logic and offers two methods for you to tell the DB what to look like. For the first example (001Init), this is version one. The Up() method therefore is transitioning the DB from version 0 to version 1, so we need to put in some code to tell it what we want it to do: public override void Up() { //Create the Records Table TableSchema.Table records = CreateTableWithKey("Records"); records.AddColumn("RecordName"); records.AddColumn("GroupID", System.Data.DbType.Int32); records.AddColumn("LabelID", System.Data.DbType.Int32); AddSubSonicStateColumns(records); //Create the Groups Table TableSchema.Table groups = CreateTableWithKey("Groups"); groups.AddColumn("GroupName"); AddSubSonicStateColumns(groups); //Link them CreateForeignKey(groups.GetColumn("id"), records.GetColumn("groupID")); } For the Down() method, we need to reverse, exactly, everything we did with Up(): public override void Down() { TableSchema.Table records = GetTable("records"); TableSchema.Table groups = GetTable("groups"); //drop the FK DropForeignKey(groups.GetColumn("id"), records.GetColumn("groupID")); DropTable("Records"); DropTable("Groups"); } *Note: I'm working on the syntax to drop the FK constraint. I know it's heavy. If you needed to alter a column, you use: AlterColumn("records", "RecordName", System.Data.DbType.String, 800); You can change the column's length, type, name, and nullability if you need to. You can also remove a column from a table: RemoveColumn("records", "groupid");
Iterations
If your client says to you "hey great, you made a Records table - but you forgot Labels! You have labelID, where's labels!" - it's time to write another Migration, and migrate from version 1 to version 2: Following our convention, add another class file to the Migrations folder: 002_AddLabels Next up, add the code to Up/Down our migration:
namespace MigrationSample.Migrations { public class Migration002:Migration { public override void Up() { //add the labels table TableSchema.Table labels = CreateTableWithKey("Labels", "labelID"); labels.AddColumn("LabelName"); AddSubSonicStateColumns(labels); Execute("INSERT INTO Labels(labelname) VALUES('Capitol')"); Execute("INSERT INTO Labels(labelname) VALUES('Arista')"); Execute("INSERT INTO Labels(labelname) VALUES('Virgin')"); TableSchema.Table records = GetTable("records"); CreateForeignKey(labels.GetColumn("labelID"), records.GetColumn("id")); } public override void Down() { TableSchema.Table records = GetTable("records"); TableSchema.Table labels = GetTable("labels"); //drop the FK DropForeignKey(labels.GetColumn("labelID"), records.GetColumn("id")); DropTable("labels"); } } } Yes! Migrations also allow you to add data! I know that inline script is probably not what you had in mind ;) - I'll have this worked out by the time we go final with 2.1 (allowing you to use our query tool) but for now - the ability is there. Now, to execute... sonic.exe /migrate You can set this up like you do with other SubCommander commands - please see the video for how to make this happen!
Comparisons== There are other things out there (like DB projects with VS) and I go into some of this in the video (nudge nudge). This isn't a versioning tool per se - it's a development tool. If you like scripts, more power to ya. Migrations take a little getting used to, that's for sure. ==Caveats
This stuff may still be a tad rough. We've tested it a lot, but whenever you talk about tweaking DB schema and code... well there's a reason there's not many Migration solutions in .NET land right now :). Please be patient and help us to get this up to par.