Devlin's Weblog

« November 2005 | Main | August 2007 »

January 20, 2006

Rubifying Legacy Databases

ActiveRecord

There are several conventions, which when followed in RoR lead to getting a tremendous amount of functionality for free. Some of the more well known conventions is that of model names to table names. Model names should be in the singular form, with mixed case (i.e. Person, LineItem, Post) and the table names by convention are pluralized written in the underscore style (i.e. people, line_items, posts). Each table should have a surrogate primary key, named 'id', an auto increment integer. For new applications the conventions are so easily followed that usually there's no good reason to go against the grain. This is not true however, when you've inherited a legacy database.

The above described convention is just the convention, not the "must-be-done-this-way" edict. All of the properties above can be overridden, but if you don't follow the convention you must inform rails explicitly. No config files are used here, just good old ruby. The methods to look up here are

set_table_name 'table_name'
set_primary_key 'StrangeConcoction'

These methods work great - I have successfully connected ActiveRecord to a databse originally created in Access, then exported to MySQL. It wasn't created by an DBA, so several tables use things like "CompanyName" as the primary key. ActiveRecord handles these without a hitch. I did find a problem however with columns whose names ended with a question mark. ActiveRecord has no problem getting the value from the database, it runs into trouble when trying to set a new value to that field.

sample = Sample.find :first
sample.ReportSent?
=> false  # ok
sample.ReportSent?= true
=> syntax error  #not ok

Meta Programming to the Rescue

I'll just extend ActiveRecord::Base to include a method, which when called, will query the database to discover which columns end in '?''s and then adds methods to the class, implementing methods to give us access to these columns.

To add this functionality to ActiveRecord::Base, we can just open it up and define some more methods. I put this in a file called column_renamer.rb in my lib/ directory.

module ActiveRecord
   class Base
      def self.add_question_mark_writers
         columns.find_all{|c| c.name.last == '?'}.map{|c| c.name.chop} do |method|
            define_method(method + '=') { |val|
               write_attribute method, val
            end
         end
      end
   end
end

Now, in the class:

class Sample < ActiveRecord::Base
   set_table_name 'tblSamples'
   set_primary_key 'labTrackingNumber'
   add_question_mark_writers
end

Then...

sample = Sample.find :first
sample.ReportSent?  
=> false  # ok
sample.ReportSent = true  
=> true   # ok too!

It works, now let's "rubify"

So after I got the above working, I had access to each field of the database tables and life was good. It did not take long however for something apparent above to start bugging me. The Ruby way of naming attributes is in lower case with underscores separating words, not CamelCase or whatever other convention is used in the legacy database. It just sticks out like a sore thumb in beautiful ruby code. Then I realized, I'll just use my trick to make the databse look more like ruby, or I call it "rubifying" the database.

Now my column_renamer.rb looks like this

module ActiveRecord
   class Base
      def self.add_question_mark_writers
         columns.find_all{|c| c.name.last == '?'}.map{|c| c.name.chop} do |method|
            # just call underscore to change method name to ruby style instead
            define_method(method.underscore + '=') { |val|
               write_attribute method, val
            end

            define_method(method.underscore + '?') {
               read_attribute(method + '?')
            end
         end
      end

      def rubify_methods
         add_question_mark_writers
         # we've already handled the question mark special case
         # just rubify the rest
         self.columns.find_all{|c| c.name.last != '?'}.map{|c| c.name} do |column|
            define_method(column.underscore) do
              read_attribute column
            end
          
            define_method(column.underscore + '=') do |val|
               write_attribute column, val
            end
         end
      end
   end
end

Now, I get code that looks like:

sample = Sample.find :first
sample.report_sent?
=> false
sample.report_sent = true
=> true

Ahhhh, much better.

Posted by Devlin at 04:42 PM | Comments (0)