Deprecated: Assigning the return value of new by reference is deprecated in /nfs/c01/h02/mnt/10110/domains/theusualsuspect.com/html/wp-includes/cache.php on line 36

Deprecated: Assigning the return value of new by reference is deprecated in /nfs/c01/h02/mnt/10110/domains/theusualsuspect.com/html/wp-includes/query.php on line 15

Deprecated: Assigning the return value of new by reference is deprecated in /nfs/c01/h02/mnt/10110/domains/theusualsuspect.com/html/wp-includes/theme.php on line 505
theusualsuspect.com » Membrane - Plugin Database Migration

Membrane - Plugin Database Migration

John | .Net, Membrane | Friday, September 18th, 2009

I’ve had a major case of developers block on this project. The reason for this is that I have been struggling with the best way of handling the front end template management.

After spending some time going around in circles I decided the best way to get past these blocks was to try out a few other CMS systems to see how they handled such issues. This has turned out to be a really useful exercise and has really opened up my eyes to alternatives for a number of areas.

One such alternative I have come across is from the world of Ruby ActiveRecord Migration. Thanks to this I have started to rewrite the plugin database migration handling.

Initial Idea for Plugins’ Database Work

The initial strategy was to get plugin developers to do all database work via install, uninstall and upgrade sql script files. This meant that all DB work could be source controlled. For me the downside was that the upgrade script could become large and unwieldy the more extensions got upgraded.

Enter the world of Ruby

Over the last few months I’ve been spending some time with Ruby and more specifically RadiantCMS, which is a lightweight CMS. I’ve done quite a bit of extension writing for this system and whilst somethings I am not keen on, the use of Ruby ActiveRecord Migration has been a real eye opener for me.

ActiveRecord Migration makes database work incredibly easy. It works by developers writing specific classes for database actions. For example to create a news_entries table a developer simply creates a class called CreateNewsEntry and gives it the filename 001_create_news_entry.rb. The number at the beginning of the filename is important as it tells Ruby the order in which to run the migration classes.

The CreateNewsEntry class looks as follows:

class CreateNewsEntry < ActiveRecord::Migration
  def self.up
    create_table :news_entries do |t|
      t.integer :news_type_id
      t.string :headline
      t.text :text
      t.string :image
      t.date :start
      t.date :stop
      t.boolean :featured
      t.boolean :approved
 
      t.timestamps
    end
  end
 
  def self.down
    drop_table :news
  end
end

Pretty simple hey! If we need to roll back a version of the migration this is handled by the down method.

As the News extension evolves we can change the news_entries table easily enough by creating a second migration class RemoveStopAndFeatured, which will remove a couple of fields from the database. The filename for this migration will be 002_remove_stop_and_featured.rb.

class RemoveStopAndFeatured < ActiveRecord::Migration
  def self.up
	remove_column :news_entries, :stop
	remove_column :news_entries, :featured
  end
 
  def self.down
        add_column :news_entries, :stop, :date
	add_column :news_entries, :featured, :boolean
  end
end

Again pretty simple. So with these classes we can maintain the DB schema and Ruby does the work for us.

Having this time with Ruby has certainly given me a case of Ruby Envy, and I definitely plan to spend more time using it. Tools such as Cucumber, RSpec, ZenTest and Capistrano are fantastic development aids.

Is there a .Net equivalent?

Yes, in fact there are a number of tools:

I had a read through the options and Migrator.Net, seemed to be the right tool for what I was wanting to do. That’s not to say I didn’t like the other options, just that Migrator.Net is right for Membrane.

A quick spike

I decided it was best to just get on and spike the Membrane code and test how effective Migrator.Net was.

The current situation with plugins is that they can be installed, upgraded and uninstalled via a control panel within Membrane. When a user hits the install button I want a number of things to happen:

  1. CRUDControllers and Services get registered with Windsor
  2. Any mappings get registered with AutoMapper
  3. The database gets updated as appropriate

The first two points have already been dealt successfully with in the code.

The purpose of the spike was to see if I could get Migrator.Net to run the relevant Up and Down migrations as part of the Install and Uninstall actions. Fortunately this took me a lunch break to do and although the code needs cleaning up it certainly has given me the confidence that Migrator.Net will be the tool for handling DB migrations in Membrane.

No more SQL!

As Membrane has been developed using different NHibernate tools it has meant that our dealings with SQL has been kept to a minimum. All SQL queries are done via NHibernate.Linq so we haven’t had to mess around writing stored procedures and it has also meant all the queries are covered by unit tests.

The only dealings with SQL have been for schema sql scripts. Using a migration tool now means we have finally done away with even having to write these.

No Comments »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a comment

Powered by WordPress | Theme by Roy Tanck