Today I tried to find a good way to include the SQL schema in my code and thus in source control. Having bookmarked a couple of frameworks in the past I finally got the change to look closer at some.
This seems to be the fairly well maintained, it has commits in the past month and lots of patches in the discussion group, which is good. A few typos in the documentation wiki that were pointed out months ago without being fixed is not so good. Also, the documentation assumed some features that were only present in the source tree, not in the release package.
The API interface leaves me somewhat lacking
1: Database.AddTable("Event", 2: new Column("Id", DbType.Int32, ColumnProperty.PrimaryKeyWithIdentity), 3: new Column("Name", DbType.String, 4: ColumnProperty.Indexed | ColumnProperty.NotNull),
5: new Column("StartTime", DbType.DateTime, 6: ColumnProperty.Indexed | ColumnProperty.NotNull),
7: new Column("EndTime", DbType.DateTime, ColumnProperty.Null) 8: );
I would have wished for a more fluent interface, but it works.
Each migration is a class, inheriting from Migration and implementing the Up() and Down() methods. It uses class attributes to figure out the order of the migrations and if they have been run.
1: [Migration(20090211235701)]
2: public class AddEventTable : Migration { 3: public override void Up() { 4:
5: Database.AddTable("Event", 6: new Column("Id", DbType.Int32, ColumnProperty.PrimaryKeyWithIdentity), 7: new Column("Name", DbType.String, 8: ColumnProperty.Indexed | ColumnProperty.NotNull),
9: new Column("StartTime", DbType.DateTime, 10: ColumnProperty.Indexed | ColumnProperty.NotNull),
11: new Column("EndTime", DbType.DateTime, ColumnProperty.Null) 12: );
13:
14: }
15:
16: public override void Down() { 17: Database.RemoveTable("Event"); 18: }
19: }
Information about which migration have been run against a database is stored in a SchemaInfo table.
The database must be created in advance since the migration tool connects to the database before running any migrations scripts.
Migrations.NET support several databases, including MS SQL, Oracle, MySQL and SQLite.
One problem I had was that ColumProperty.Indexed does not work and there is no way to add an index to a table without adding custom SQL, thus negating some of the db-independence.
In the end, I’ve come to the conclusion that it’s nice, but missing some features and that I don’t really like the API. Putting it aside for now.
Rikmigrations uses the same Up()/Down() pattern but with a slightly different API interface that I liked better. Here, the attributes are set on the assembly level instead of the class level. I liked the class attributes from Migrator.NET better, but this works too.
1: using RikMigrations;
2: [assembly: Migration(typeof(BlogMigration1), 1)]
3: [assembly: Migration(typeof(BlogMigration2), 2)]
4: namespace Blog.Migrations
5: { 6: public class BlogMigration1 : IMigration
7: { 8: public void Up(DbProvider db)
9: { 10: Table t = db.AddTable("Blog"); 11: t.AddColumn("ID", typeof(int)).PrimaryKey().AutoGenerate(); 12: t.AddColumn("Name", typeof(string), 64); 13: t.Save();
14: }
15: public void Down(DbProvider db)
16: { 17: db.DropTable("Blog"); 18: }
19:
20: }
21: public class BlogMigration2 : IMigration
22: { 23: public void Up(DbProvider db)
24: { 25: Table t = db.AlterTable("Blog"); 26: t.AddColumn("Description", typeof(string), int.MaxValue); 27: t.Save();
28: }
29: public void Down(DbProvider db)
30: { 31: Table t = db.AlterTable("Blog"); 32: t.DropColumn("Description"); 33: t.Save();
34: }
35: }
36: }
It has some cool features like using generics to specify the data types:
1: Table t = db.AddTable("Blog"); 2: t.AddColumn<int>("ID"); 3: t.AddColumn<string>("Name", 64); 4: t.Save();
and inserting default data into a table:
1: db.InsertDataInto("Table1", new { ID = 1, Name = "Hello World" }, true);
It looks like it’s also missing the feature to add an index, but from what I’ve glanced at the code, it shouldn’t be that hard to implement (this might just come back to haunt me…)
The documentation isn’t much and what little there is seems to be pretty old, but the code doesn’t look that hard to just figure out so I don’t think it will be much of a problem.
I didn’t get a chance to try this today but tomorrow I’m going to take it for a spin. There’s a couple of others I’d like to try as well. Watch this space for more.