Dealing with column conversions in Ruby ActiveRecord

Question

Dealing with a legacy database, I've come across a column in a SQL Server database where the date is stored as a decimal. E.g. 2011-04-23 is stored as 20110423.0.

Is there a general ActiveRecord mechanism for dealing with "weird" column storage conventions? Enum-like columns where they're actually stored as integers is another case that might also make use of the same mechanism, but I can't quite find what I'm looking for.

It seems like serialization gets me partly there:

class Thing < ActiveRecord::Base
  class DecimalDate
    def load(date)
      if date.is_a? Numeric
        y,m,d = /^(\d\d\d\d)(\d\d)(\d\d)/.match(date.to_s)[1..3].map(&:to_i)
        Date.civil(y,m,d)
      end
    end
    def dump(date)
      date ? date.strftime('%Y%m%d').to_i : 0
    end
  end
  serialize :weird_date, DecimalDate
end

rails c
> Thing.first.weird_date
=> Sun, 02 Jan 2011

But, the illusion is thin. The column doesn't "know" that it's a date stored as a decimal. E.g. comparisons fail:

rails c
> Thing.where('weird_date > ?', 1.week.ago)
...
ActiveRecord::StatementInvalid: ... Error converting data type varchar to numeric.:

Answer 1

If your are forced to deal with legacy data I see two possibilities to manage it.

1 From your database

You can "convert" your data by making a view of your table which convert your (date) fields on the fly. Then you make a trigger (before insert/update) on this view which convert your data back to your old format. Finally you tell ActiveRecord to use your view instead of your table.

2 From your application (Rails)

Find a way to tell ActiveRecord to do the same job. Have you already tried to manage it with AR callbacks with after_initialize and before_save? More informations here