Sunday, September 7, 2008

Rails against a legacy sql server database

The main problem with legacy database is that you can't do what you want. And there is 99% of chances that your legacy database does not follow the rails conventions.

Here are a few tricks to simplify your life :

1. First of all, SQL Server views are updatable so use them !

ex. create view
as
select mylegacyid as id,
originalcolumn1,
originalcolumn2,
originalcolumn3,
originalcolumn4,
originalcolumn...,
record_creation_date as created_at,
record_modification_date as updated_at

This way your legacy system continues to work correctly and you have a Rails layer built on top using specific views.

Then a trick, as rails builds its indentation on the last record in the database, you must have at least one record in each table otherwise rails won't be able to insert a new record with this method. I've found that this method works well with tables with few fields (25 fields) and saves time.

The other method is to modify the conventions and specify everything (key, table name etc..) manually. If you can't avoid it, here are a few tips :

HowToUseLegacySchemas

Legacy databases

Masking the Database

Rails and Legacy databases

My advice here is to start generating your empty scaffold with the rails convention for your model : ruby script/generate scaffold Car

Then you need to edit the model and indicate the primary key and the table name:

class Car < ActiveRecord::Base
set_table_name "TABLE_VintageCars"
set_primary_key "MyPrimaryKey_Identifier"
end

Then finally update your views. Be carefull with the column names as you know that rails is case sensitive (not like most of your SQL Server or ODBC setup).

This method functions as well and although the creation of new views seems to conform more to the rails philosophy, it might be usefull not to add a ton of new object in your db and to live with existing tables.

No comments: