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