Spark, FluentMigrator, and FluentNHiberate

Spark looks like an excellent alternative view engine. It’s appeal is that the HTML dominates the view instead of <asp:controls>. Maybe its because I like rails, but it just feels more natural. Plus you still get the power & familiarity of .Net – in any form! It supports IronRuby & IronPython even!

So what happens when you take Spark, ASP.Net MVC, FluentMigrator, and FluentNHibernate.. will it blend? Sure, and here’s how.

1. create and ASP.Net MVC web application.
2. follow this guide to replace the default view engine
3. use the same simplistic example migration and setup from my InstantApp
4. Then just add a view and the code to list the users

You can grab the code from http://github.com/jcoffman/csinctools, where you’ll also find the code for InstantApp.

Controller method:

View: Result:

By the way, the top image is from the book “The People of Sparks” which is the second in the City of Ember series.  The first is better; never read the third.

Mono and FluentMigrator ..and Android

I’ve got a little itch to see about making FluentMigrator run on Mono. I ran the analysis tool, which say the FM assemblies look ok. However, System.Data.Sqlite.dll doesn’t seem to be good. I read that it should work on mono, and even if not there is Mono.Data.Sqlite.

I think it would just be cool to do this, but it would also let FM run on Linux and Mac with Mono. That would rock! People could write C# on Mac or Linux, and use FM. Now we’d probably need to add support for more databases, but I’d like to see that happen anyway.

So a secondary part of wanting this, is knowing that Mono can run on Android. I think Android is going to be big, and that its getting close to a huge growth phase. [I missed James Britt’s Android and JRuby talk last night.] While I’m happy to learn other languages, being able to use C# in new ways on a new device would be a whole lotta fun.

If anyone out there would like to help or offer guidance, you can reach me through the contact page. Or post to the FM google group.

New .Net App – Just add water

I’ve been meaning to post this for a while, only I was planning on covering it with more depth. Life has been a bit busy, so I’m going to post it in it’s current form.

Using FluentMigrator, Fluent-NHibernate, and NHibernate, one can whip up an app fairly quickly. Let’s walk through it in brief.

First, grab the source for FluentMigrator and Fluent-NHibernate from github. Second, build each and put the binaries somewhere practical for your application. Use the NHib binaries from Fluent-NHiberate; otherwise dll versions can be a problem. Now open Visual Studio and create an application. To keep things easy, I’ll make it a WinForm application. Source will be in our OSS project, title InstantApp.

Now add the references to FluentNH, FluentMigrator, and NH. For the count, I added FluentMigrator.dll, FluentMigrator.Runner.dll, Castle.Core.dll, Castle.DynamicProxy2.dll, log4net.dll, and System.Data.SQLite.dll to use sqlite as the database. Don’t worry, the sqlite dll is in the sample code repository. (Update: please refer to the sample for the added dlls. I added others such as NHibernate.linq.)

Now, we need some schema. I recommend keeping schema codefiles in a seperate folder to keep things easy to maintain; so I added a “Migrations” folder in the project. Then added a “first.cs” schema class. Because I am a bit particular, I’m also adding a folder called “Models” to contain the object model classes. In order to wire-up NHib with Fluent-NH, each model will need a mapping class. I’ve added those to the folder “Mappings” under the “Models” folder. Then I’m adding “Tests.cs” because I want to test a couple things.

So far, so good… now we need some code, and because this is fast & dirty, I’m going to whip out the migration code. Let’s keep it to just one table. And add some data.

   1:  public override void Up()

   2:  {

   3:    //users table

   4:    Create.Table("Users")

   5:    .WithColumn("Id").AsInt32().NotNullable().PrimaryKey()

   6:    .WithColumn("Email").AsString(50).NotNullable()

   7:    .WithColumn("Name").AsString(50).NotNullable()

   8:    .WithColumn("Password").AsString(100).NotNullable();

   9:   

  10:    for(int loop = 0; loop < 10; loop++)

  11:    {

  12:      Insert.IntoTable("Users").Row(new { Id = loop, Email = string.Format("email{0}@mail.com", loop),

  13:                                                  Name = string.Format("test name{0}", loop),

  14:                                                  Password = string.Format("password{0}", loop)

  15:      });

  16:    }

  17:  }

  18:   

  19:  public override void Down()

  20:  {

  21:    Delete.Table("Users");

  22:  }

Let’s run this on startup so the table gets created right away. So I added this to Program.cs and called it in Main():

   1:  static void MigrateDatabase()

   2:  {

   3:   //make sure the sqlite db file exists

   4:   if (!File.Exists(DbFile))

   5:   System.Data.SQLite.SQLiteConnection.CreateFile(DbFile);

   6:   

   7:   //run migration

   8:   using (SQLiteConnection connection = new SQLiteConnection(ConnectionString))

   9:   {

  10:    var conventions = new MigrationConventions();

  11:    connection.Open();

  12:    var processor = new FluentMigrator.Runner.Processors.Sqlite.SqliteProcessor(connection, new SqliteGenerator());

  13:    var runner = new MigrationVersionRunner(conventions, processor, new MigrationLoader(conventions));

  14:   

  15:   //upgrade to latest

  16:   runner.UpgradeToLatest(false);

  17:   }

  18:  }

Aaaanndd now.. {drum roll} ..let’s get a list of all the users as take from one of the tests:

   1:  var session = CreateSessionFactory().OpenSession();

   2:  var list = from c in session.Linq<User>()

   3:    select c;

   4:  Assert.True(list.ToList().Count == 10);

Yeah! It passes. So now, just add a data grid view to the WinForm, and the right code.  Wha-bam! You get data in your grid. Pretty nice for less than an hour’s work.

   1:  var session = CreateSessionFactory().OpenSession();

   2:  var list = from c in session.Linq<User>()

   3:   select c;

   4:   

   5:  dataGridView1.AutoGenerateColumns = true;

   6:  dataGridView1.DataSource = list.ToList();

That’s the short intro to building an app with NH, FNH, FluentMigr, and a dash of Linq. See the OSS code for better details.

Git Tips For Twits Like Me

  If you’re a windows dev and use github for source control, you should set the autocrlf setting in git like described in this github article. I’m also setting the safecrlf setting, and hopefully those funky end of line characters I got going on will wiggle there way out. I wasn’t aware of this but probably should have thought of it, but git will add unix end of line characters to your files when you check them into a git repository. Just make sure everyone on the project has the autcrlf set to true, and everything will be fine. Use git bash and run these commands:

git config --global core.autocrlf false

git config --global core.safecrlf false

  It’s easy to miss. I ended up adding those unix eof’s into the solution file and others for FluentMigrator. Justin Etheredge pointed out the problem and the fix. Hopefully it will all be cleared up soon. It doesn’t seem to prevent VS from opening and compiling, and even the build server continues to run.

UPDATE: I realized I was giving bad advice. You should set autocrlf to false not true.

-j

FluentMigrator: A Debate Raging In My Head

  Here’s a little secret. Slow tests really annoy me. You know, when you run a single test that takes 10+ seconds or a small set of tests takes 3 minutes. One of the many reasons I really like unit testing is getting a measure on my progress quickly. That enables continuing to make quick progress or quickly fixing the problem. That falls apart when tests run slow. For example, my tests on one particular project where one test takes 10 seconds. Granted its an integration test and all subsequent tests run much faster. Still I would like to improve that.

  I know exactly what the problem is; I created the problem. On purpose. It’s resetting my database schema back to the version I want using FluentMigrator by stepping down to version zero and then migrating back up. I want a clean database when I run integration tests. The thing is there is almost a solution for the problem: FluentMigrator.

  My database is sql server; but if I was running sqlite in memory, the tests should be really fast. This leads to the debate inside my head. You see, sqlite doesn’t support foreign keys which my sql schema uses. On top of that, It’s throwing an error in the sqlite generator when you try to create a foreign key. But should it? On one hand, it’s not supported in sqlite so it kind of makes sense. It would be nice to gracefully handle it though for situations where you might want some flexibility with your back end. Like my integration testing scenario. Before I ask, I know it’s thin ice to perform integration testing against a different db than your target product db. Now, what do you think? I’m thinking towards allowing both but defaulting to throwing an error with FK’s in sqlite.

Maybe something like this to avoid the FK error with sqlite?

   1: Create.ForeignKey("FK_Approvals_UserId").FromTable("Approvals").ForeignColumn("UserId").ToTable("Users").PrimaryColumn("Id").SoftFail();

-j

FluentMigrator: More Schema Discord

Well alright, I’m finding more ways in which SQL Server schema differs from SQLite. I’m not really surprised; just learning how much. For instance, SQLite doesn’t support Foreign Keys, which is why the SQliteGenerator in FM throws a NotImplementedException when it tries to generate sql for Foreign Keys. Also, you can’t do a simple rename of a column in SQLite. You have to rename the table, create a new table with the new column name, copy the data from the old into the new, and then drop the old table.

So a quick web search found a couple articles on differences between sql server and sqlite: uno, dos. Chances are there will be more issues to address.

I’m not going to worry about it to much right now since I don’t need it. I just need to change the sample migration so the tests pass withthe SQLite generator. If we ever work on supporting mysql, there will be similar problems. Ultimately, you have to know at least a little about the database you are creating schema for.

-j

Schema Syntax Entropy

As I previously mentioned, I’m starting to work on FluentMigrator which is a database schema generator and versioning library. I’m doing this for my own need, but am trying to continue to respect the potential for others. I’ve found some syntax errors in generated sql statements, and one in particular causing a test to fail.  It’s a t-sql versus SqLite problem.  Ideally, one could define a migration that would be valid for both SqlServer and SqLite as long as the generator builds the proper sql statements. It’s not as easy as you (or at least me) would think at first. Consider this migration code:

   1:  public override void Up()

   2:  {

   3:    Create.Table("Users")

   4:      .WithColumn("UserId").AsInt32().Identity().PrimaryKey()

   5:      .WithColumn("UserName").AsString(32).NotNullable()

   6:      .WithColumn("Password").AsString(32).NotNullable();

   7:  }

   8:   

   9:  public override void Down()

  10:  {

  11:    Delete.Table("Users");

  12:  }

It’s a really simple example to be sure, but look at the difference in the ideal sql statements:

[SQL Server]

   1:  CREATE TABLE Users

   2:  (

   3:    UserId int IDENTITY(1,1) PRIMARY KEY,

   4:    UserName nvarchar(32) NOT NULL,

   5:    Password nvarchar(32) NOT NULL

   6:  )

[SQLite]

   1:  CREATE TABLE Users

   2:  (

   3:    UserId integer PRIMARY KEY AUTOINCREMENT, 

   4:    UserName varchar(32) NOT NULL,

   5:    Password varchar(32) NOT NULL

   6:  )

But those aren’t the sql statements it currently generates.

Problem:

For SQLite to have the same behavior as SQL Server, you need to define the column as “PRIMARY KEY AUTOINCREMENT” instead of “IDENTITY(1,1) PRIMARY KEY”. But the migration generator is just trying to translate the syntax .Identity() into the database specific keyword(s). Both SQLite and SQL Server have an IDENTITY keyword but it means something different in SQLite. It only means that the column is the identity for the row but doesn’t auto-populate it as it would in SQL Server.

Solution:

I’m working on it. Obviously, we want this to be handled. I just don’t want to spend a lot of time making the SQLite generator too smart. Especially since it’s not something I need. I’m sure the answer is out there. If you happen to know, please contact me.

-j

FluentMigrator Rising From The Ashes

  Well more like it was peacefully sleeping until I came along and started pounding it on the chest. Now it’s hunched over and coughing while muttering something about me under it’s breath. But anyway..

  I wanted to build schema management into one of the projects I’m working on, and found FluentMigrator partly by reference and partly by accident.  The point is to build up and change database schema in code with a fluent syntax. Perfect, only I wanted my app to automatically update if it has a newer version than the database. So I went about adding that to the code, and all of a sudden this fork appeared for me in github while angelic voices sung magically in the background. Right before I pulled the sword from the stone! Seriously though, I’m working on adding functionality to an excellent project started by Justin Etheredge and Nate Kohari. You can find my github fork here.

  My focus will be on my own needs, but feel free to make requests along the way. Even better, feel free to help! Hopefully, Justin will accept my changes into the master tree and maybe we can put together a release.

To give you a bit of a preview, here is a sample migration to create a couple tables and foreign keys:

   1:      [Migration(1)]

   2:      public class TestMigration : Migration

   3:      {

   4:          public override void Up()

   5:          {

   6:              Create.Table("Users")

   7:                  .WithColumn("UserId").AsInt32().Identity().PrimaryKey()

   8:                  .WithColumn("GroupId").AsInt32().NotNullable()

   9:                  .WithColumn("UserName").AsString(32).NotNullable()

  10:                  .WithColumn("Password").AsString(32).NotNullable();

  11:   

  12:              Create.Table("Groups")

  13:                  .WithColumn("GroupId").AsInt32().Identity().PrimaryKey()

  14:                  .WithColumn("Name").AsString(32).NotNullable();

  15:   

  16:              Create.Column("Foo").OnTable("Users").AsInt16().Indexed();

  17:   

  18:              Create.ForeignKey("FK_Foo").FromTable("Users").ForeignColumn("GroupId").ToTable("Groups").PrimaryColumn("GroupId");

  19:              

  20:              Create.Table("Foo")

  21:                  .WithColumn("Fizz").AsString(32);

  22:   

  23:              Rename.Table("Foo").To("Bar");

  24:              Rename.Column("Fizz").OnTable("Bar").To("Buzz");

  25:   

  26:              Insert.IntoTable("Users").Row(new { UserName = "Data1", Password = "Data2" });

  27:          }

  28:   

  29:          public override void Down()

  30:          {

  31:              Delete.ForeignKey("FK_Foo").OnTable("Users");

  32:              

  33:              Delete.Table("Bar");

  34:              Delete.Column("Foo").FromTable("Users");

  35:              Delete.Table("Users");

  36:              Delete.Table("Groups");

  37:          }

  38:      }

I’m actually not so much of a fan of the class attribute to specify the version. I’d prefer to have a convention using the class name, and a convention to specify where the migration classes live within an assembly. We’ll work with this for now and see what can be done later. So go check it out.

-j