A few weeks ago Rob Conery foolishly tapped me to help get migrations in SubSonic up to snuff and I've been working on them ever since trying to sneak them into the latest SubSonic beta.  I've changed the way they're implemented slightly from when Rob first talked about them so here's a quick re-introduction to migrations.

Migrations are a way to create and version your database schema using code rather than having to rely on SQL scripts or compare and sync tools.  They allow you to quickly rollback schema changes as well migrate schema changes from your development database to staging and then on to production.  In a nutshell they rock when it comes to database maintaince, versioning and deployment.

Migration Breakdown

A migration is a class that descends from SubSonic.Migration and overrides both the Up() and Down() methods.  Up() is used when going up a version and Down() is used to restore the database schema to the pre-Up() state.  Anything you do in the Up() should be undone in your Down(). 

By convention they are put in a Migrations folder off the root of your project folder.  While the actual name of the class isn't important the name of the file is critical because this is how SubSonic determines which version the migration represents.  The naming convention is 000_MigrationName.cs (or .vb) with the version number represented by leading three numerics, starting at '001' and working your way up.  Currently it's pretty particular about that naming convention so make sure it's exactly three numerics, padded with zeros if needed.  It's convention to name your migration file something descriptive and to also not repeat names, such as:

001_AddExerciseTable.cs

An Example

Let's start with a simple example and break it down:

using System;
using System.Collections.Generic;
using System.Text;
using SubSonic;

namespace SubSonic {
  public class Migration001:Migration {

    public override void Up() {
      TableSchema.Table t = CreateTable("Flights");
      t.AddColumn("Name", System.Data.DbType.String);
      t.AddColumn("FlightNumber", System.Data.DbType.String, 100);
      t.AddColumn("DateTraveling", System.Data.DbType.DateTime, 0, false, "getdate()"); 
      AddSubSonicStateColumns(t);
    }
  
    public override void Down() {
      DropTable("Flights");
    }
  }
}

In the example the 'Flights' table is created, then three columns are added to it, followed by the standard SubSonic state columns.  If you don't specify a primary key one will be created for you with the pattern of 'TableNameID', so that's one less thing to worry about.  The Down() method undoes everything we did in the Up() by dropping the 'Flights' table.

Available Methods

Currently the methods available from inside your migration are:

• CreateTable(string tableName) - This creates and returns a table schema to which you can add your columns, as seen in the example.
• DropTable(string tableName) - Does exactly what it says.  If your Up() has a CreateTable() you'll need a corresponding DropTable().
• AddColumn(string tableName, string columnName, ...) - Used to add a new column to an existing table.  It has all the same overloads as TableSchema.Table.AddColumn() except the first parameter is the name of the table you'll be adding columns to.
• RemoveColumn(string tableName, string columnName) - You only get one guess that this does :)
• AlterColumn(string tableName, string columnName, ...) - Used to alter an existing column, again, the same overloads as AddColumn.
• AddSubSonicStateColumns(TableSchema.Table table) - Adds the conventional SubSonic state columns to your table.  I'll be adding another overload that just takes a tableName if you want to add those columns to an existing table.

Running your Migrations

To run your migrations you'll use SubCommander, the same tool used to generate your models but with the 'migrate' command.  The simplest usage is:

sonic migrate

That's it.  It'll use your default provider, look for your migrations in <project>\Migrations and run every migration Up() starting at your database's current migration up the last one found in the Migrations folder.  You can also specify the provider, migration directory and version at the command line like this:

sonic migrate /provider "Northwind" /migrationDirectory "D:\Testing\Migrations" /version 4

A few things to remember:

• Migrations by convention are looked for in a \Migrations folder off the root of your project, though this can be changed via the command line. (/migrationDirectory "D:\Migrations")
• You run a migration against a single provider at a time, there is no support for specifying the provider inside the migration.  The main reason is portability, often you'll be running this migrations against different databases and hardcoding the provider name in the migration destroys their usefulness.
• Migrations will run against the default provider unless otherwise specified via the command line (/provider "Northwind")
• By default migrations will try to go up to the latest version found in the migrations folder.   To go up or down to a specific version use /version X to indicate which version.
• To enable migration support a new table 'SubSonicSchemaInfo' will be created in your database, so don't delete it and tell your DBA that it's OK :)

TODO

These are things you *should* see before the next beta drop, but don't hold me to it :)

• Ability to generate your migration code skeleton using sonic.exe.
• Add RenameTable()
• Add RenameColumn()
• Add ability to execute ad-hoc sql, for creating stored procs, views, creating roles, users, etc.
• Add constraints
• Add foreign keys

I just had a friend at work ask that most innocuous of questions, "So, what should I learn if I want to be web developer?" which led us into a pretty good discussion about all things web related and to give him (Hi Nat!) a place to reference my ramblings I thought I'd jot down what I suggested.

Define "Web Developer"

Web developer can pretty much mean anything these days so I find it best to ask yourself what it is you really want to do.  I've been in shops where web developer meant just HTML/CSS while in others the dev does it all, from comps to HTML to database interaction.  In my friend's case it meant creating a dynamic database-driven website from nothing more than a designer's comps and the napkin the business leaders scribbled on in between rounds of golf.  Which was good because that's how I define it.

Stage 1 - Learn HTML/CSS

You've gotta learn the basic currency of the web before you can get fancy pants on it so getting a solid understanding of good, standards-based semantic HTML and CSS (vs. table tag soup) is key.  Regardless of which whiz-bang rocket framework you use in the end it all comes down to pushing HTML so you need to know how to craft good basic pages.

I also suggest forgetting about Dreamweaver, FrontPage or any other HTML editor, for now.  In fact I'd suggest using just a really solid text editor like TextPad, InType or E.  My current favorite is InType because it doesn't require the cygwin install like E yet it has better syntax highlighting and snippet expansion than TextPad.  When learning HTML you want to be as close to the metal as possible.

There are a ton of great books and websites out there teaching this stuff.  Two of my favorites are "Bulletproof Web Design" and "Web Standards Solutions", both by Dan Cederholm, who presents web design (as in the HTML, not Photoshop comp work) in a real-world, useful manner.  Good stuff.

Stage 2 - Learn JavaScript

I'm still iffy on this one since you could argue that JavaScript could come later but I feel it's best to at least get a rudimentary understanding of JavaScript to learn the basics like client-side validation, confirmation boxes and how to use one of the various JavaScript libraries out there like Prototype, jQuery, ExtJS.  Most server-side frameworks try to color the basic way you use JavaScript which I feel can dilute a person's understanding of just what client-side coding is all about.

Stage 3 - Pick Your Poison (Server-Side Framework)

Ahh, the golden ring, the big prize, what it's all about, at least for me, the server-side framework that makes all the magic happen when it comes to dynamic page generation.  This was probably the hardest thing to guide him on because I've worked with most of the major frameworks and they all have pros and cons.  The big three to me are ASP.NET, Rails and PHP.  They all have great support, vibrant communities and very active development.  There is also Java, but I was badly scarred when I learned Java's AWT and Swing frameworks so I'm going to completely ignore it since even thinking about it again makes me whimper :)

ASP.NET - I'm a little biased towards .NET because it offers a great springboard in terms further types of development.  Want to do create a desktop application?  No problem, you already know the IDE and C# (OK, or Visual Basic).  Feel like being more creative and want to do something Flashy?  .NET has you covered with Silverlight.  It's like a gateway drug of development, especially once you start factoring in things like IronPython, IronRuby and MVC.  And despite what some people say ASP.NET isn't just for corporate drones, there's no Web 2.0 site out there that can't be coded in .NET.  Downsides are that it's a little more complex to get started and that it'll warp your fragile little mind when it comes to the bastard child that is WebForms.

Rails - I think learning Ruby (the language of Rails) is a great addition to any developer's knowledge, regardless of what they code in by day.  If someone is interested strictly in single focus, highly dynamic web sites and really has an aversion to Microsoft this is where I'd steer them.  It's a much better abstraction of the web than .NET's WebForms and you can really get rocking quickly without having to understand much with Rails.  The downside is finding jobs in your area looking for entry-level Rails devs.

PHP - This is sort of the monkey in the middle.  There are a ton of great PHP jobs but it's lost a little of it's hipster sizzle, which isn't exactly a bad thing.  PHP is a great choice for the newbie consultant looking to work with small to medium-sized companies because there are a lot of CMS packages in PHP and you can get solid PHP hosting for a song.

In the end I suggested that he go through job descriptions he was interested in (shhh, don't tell his boss) and see what they were asking for in terms of knowledge.  This brings up another good point, why do you want to be a web developer?  If it's just for a better job then I stand by my suggestion.  On the other hand if it's because you want to do Website X or Project Y then that completely changes the game and makes it easier.  You pick the technology and learning curve that will get you quickest to your goal.  In the end users could care less if your site is in Rails, .NET or PHP.

The cool kids over on the other side of the fence always make these really cool "cheat sheets" for whatever bit of tech kit they're using and I fully expect someone with some good design skills to produce one for ASP.NET MVC.

You hear that Rob Conery?  Scott Hanselman?  How about you Phil Haack?  I fully expect Scott Guthrie, who is a Word among Bytes, to conscript some stylish hipster graphic designer to produce a masterful, stylish and yes, useful cheat sheet for the MVC masses at Mix '08, an event I sadly won't be attending because my company considers computers and those that make them work to be second class citizens.  I'm lucky if I get  to upgrade my IDE before the next one comes out much less attend an actual conference.  I've heard of conference swag but I've never actually received any of this mythical bounty.

Still, I desire, want and dare I say expect said cool cheat sheet.

Here are some examples for those that need some prompting and design ideas and to figure out just what in the hell I'm talking about:

• REST Cheat Sheet
• Prototype.js Cheat Sheet
• Rails Cheat Sheet
• What Goes Where (Ruby on Rails cheat sheet by Amy Hoy)
• Form Helpers (Ruby on Rails cheat sheet by Amy Hoy)

I'm starting to see more ASP.NET MVC samples and questions come out and I'm realizing that a large portion of the ASP.NET crowd doesn't even realize that a huge reason for the MVC movement is because of the Ruby on Rails framework.  A lot of new .NET MVC developers are struggling with architectural questions that have already been debated and answered in the Rails community, which makes Rails a great resource for when you're first starting out or you're curious how to handle certain situations, like nested resources or how to structure your controllers.

Speaking of controllers one great thing from Rails that I hope more MVC developers embrace is REST.  Instead of repeating everything just watch David Heinemeier Hansson's keynote speech from RailsConf back in 2006.  Sure, it's almost two years but for ASP.NET developers it may as well be yesterday.  I'd suggest starting from the second part since the first segment is just normal conference ra-ra-ra.

Check it out here (don't forget to download the slides that he refers to here).

Note

He talks about using a semi-colon in the URL to denote an aspect/action of a controller, like this:

/people/1;edit

Well, you can ignore that and just assume he *really* meant to say:

/people/1/edit

They dropped that semi-colon silliness in Rails 2.0 and it feels much cleaner.