Monday, March 02, 2009

Working with Legacy Databases in Rails

While working on a presentation for the upcoming Twin Cities Code Camp. I ran into an interesting situation. I am using the Northwind database provided from Microsoft as the basis of my presentation and demo. I have found a unique situation where they are using a 5 character string as the primary key. While this is not an ideal situation I'm sure it happens with older databases. Since the goal of this presentation is to develop a rails site around an existing database I took on this challenge and after consulting with Brian Hogan we came up with a solution to this problem.

First you want to set up your model to reflect the database structure


class Customer < ActiveRecord::Base
set_table_name "Customers"
set_primary_key "CustomerID"
validates_uniqueness_of :CompanyName ##This is to make sure we don't have duplicate keys
end


Next to create the primary key you have to do some hacking. Rails likes to use database incrementing primary keys and does not let you set the primary key id so you have to create a "before_create" method as seen below.


def before_create
## CustomerID needs to be a 5 char all upcase string, to match current DB layout
self.CustomerID = (self.CompanyName.gsub(" ", "")[0,5]).upcase
end


In this method I decided to use a pattern that I noticed the primary keys following. My observation was that they were using the first 5 alpha characters of the company name and uppercasing them. So this method reflects setting the primary key "CustomerID" to the company name minus out the whitespace and taking the first 5 characters.

The reason for using a before create is you should not have to update the primary key ever as many records may be linked to that, so using a "before_create" keeps our code safe and dry.

Below is a test case for the customer table of the Northwind database using the before create method.


def test_should_be_able_to_create_customer
@customer = Customer.new(
:CompanyName => "Inacom",
:ContactName => "Chris Johnson",
:ContactTitle => "Solutions Developer",
:Address => "123 My Street",
:City => "Madison",
:Country => "USA",
:Phone => "608.555.5555",
:Fax => "608.555.5555")
assert @customer.save

assert_equal(@customer.CustomerID, Customer.find_by_CompanyName("Inacom").CustomerID)
assert_equal("INACO", Customer.find_by_CompanyName("Inacom").CustomerID)
end

Friday, February 20, 2009

Rails and Legacy Databases

I finally started to work on the actual content for the upcoming Twin Cities Code Camp. I am really struggling with a good "bad" database to use. I am looking for real world examples where you would have to work with a bad database and could not re-write it or migrate to something better.

Unfortunately for me I have one really good example (the reason for this talk) however I can not divulge the customers name and the data is sensitive make this not the ideal situation. So if anyone has any good ideas shoot them my way by contacting me at [blog] at [johnsonch] dot [com].

Thanks for the help,
-CH