Thursday, July 16, 2009

Rails with a legacy Oracle DB

It's a bit annoying...
It seems like every ROR tutorial assumes two things, firstly that you're writing your web application on Mac OSX with Textmate, and second that you're starting with a fresh database schema on MySQL. For a large percentage of people this is correct, but for the other half it's more than frustrating.

I am currently investigating using rails to replace a rather old (think 6 years+) and difficult (JSP, the kind that looks like PHP gone bad) reporting site. The site shows various metrics to our business partners by analyzing SMS traffic sent through our gateway. I need to replace it with something simpler and more maintainable, Rails seemed to fit the bill, however there are some hurdles.
  • Firstly, Ruby likes UNIX. I program on a windows box and deploy to UNIX environments, there's nothing I can do to change that. Setting up a Rails environment on windows without resorting to the horribly outdated installers is a feat in itself, I'll cover that in a later post.

  • Secondly, my company likes Oracle. For the moment. It wouldn't be my first choice on a new project, but our software (with high availability requirements, somewhere in the 99.9% up time region) has been running smoothly on Oracle databases since I was in first year university. There's nothing like a good track record to make loyal followers of upper management.

  • Thirdly the schemas of the data I need to report on are sorely outdated. Everything is capitalized, there's no standard "id" column on any table, and there's a scattering of prefixes on half the columns. This makes for a slow start with ActiveRecord...

The Oracle to Rails "Stack"
Slow it may be but start we will. My "Secondly" was pretty easy to fix. Basically we need to plug 3 gaps to get ActiveRecord to talk to an Oracle Database, that can be summarised as: Your pc needs a connection to the Oracle Database Server, Ruby needs to see this connection, and ActiveRecord needs to see Ruby's connection. To make it a bit easier (maybe) here's a diagram, drawn in glorious MS Paint =>

Oracle Client: Since I already do a lot of work on Oracle databases I didn't need a client, but if you don't already have one, go find an oracle client preferable a thin (hah!) one. Google Oracle XE if you want to have a local oracle database, it's Oracle's attempt at an express edition, however at over a gigabyte to install on windows it's not for the netbook developer types...

Ruby-OCI8: go to your command line and get the Ruby-OCI8 gem via: gem install ruby-oci8 on windows you'll get the ruby-oci8-2.0.2-x86-mswin32 version. This gem lets Ruby talk to your Oracle client.

Oracle-Enhanced: This gem is an ActiveRecord adapter. Get it by running gem install activerecord-oracle_enhanced-adapter

These different parts will get you a path from ActiveRecord in Rails all the way to the Oracle database you need to work on, but there's a few things we need to tweak in our application's config to get it all the way, see here for a lot of info that is much better than I can give.

Schema woes...
Most things in rails "Just work", so long as you followed EVERY SINGLE CONVENTION. This is a bit much to ask of the developers that designed our legacy database 6 years ago. Luckily you can override and alias enough values to shoehorn your data into a valid ActiveRecord model, and once that (admittedly long and tedious) task is done you can develop like your database schema was never an issue.

I'm going to cover a few of the more common issues by rebuilding a hypothetical (cough) table called ORGANISATION. ORGANISATION has the following columns: OR_ID, FK_RE_ID, OR_NAME, OR_CODE, FK_PMP_ID. As you can see it's kind of well structured, but definitely not "Rails Safe".

Issue 1: Rails expects database tables to be pluralised.
ActiveRecord offers a method set_table_name that takes a string. We'll add set_table_name "organisation" to our model.

Issue 2: Rails wants a column called "id" for it's primary key.
ActiveRecord offers a method set_primary_key that takes a string. We'll add set_primary_key "OR_ID" to our model.

OK we're half way there. If we were building from a scaffold command our views might work (maybe) but I can think of a few places where they'll break.

Issue 3: By ERB code is breaking, what the hell?
There's a few issues here, first is that if we use the scaffold command we'll probably get what we asked for. If we asked for upper case column names (as you'd expect) then your views will break when they try to extract the info in "organisation.FK_RE_ID". Oracle SQL is very lenient on the casing of it's table and column names, while something in Rails or our adapters hates uppercase. Use the lowercase "organisation.fk_re_id".

Issue 4: By ERB code is still breaking, what the hell?
Shortcuts... The scaffolded views will invariably at some stage try to call a method that takes the organisation and try to guess at it's. In the index.html.erb for example (we're scaffolding here) it will try to do something like: "<%= link_to 'Edit', edit_organisation_path(organisation) %>" since the organisation has no "id" column it will just send everything else. Poof, your code broke.
To fix it you need to alias your foreign key as "id" in your model, add alias_attribute :id, :or_id

Final niggling issue 5: I don't like all the unintuitive column names in my views.
Since we've aliased OR_ID to id, we might as well do the same on all our columns. Just remember to alias to LOWERCASED versions of the column names, otherwise your views will throw errors again. Below is the code for the model I've described above, it works a treat.

class Organisation < ActiveRecord::Base
set_table_name "organisation"
set_primary_key "OR_ID"

alias_attribute :id, :or_id
alias_attribute :region_id, :fk_re_id
alias_attribute :org_name, :or_name
alias_attribute :org_code, :or_code
alias_attribute :provider, :fk_pmp_id


  1. I've aliased a few columns in my legacy database to accommodate it as follows:

    class User < ActiveRecord::Base

    set_table_name 'my_legacy_user_table'
    set_primary_key 'UserId'
    alias_attribute :id, :UserId
    alias_attribute :username, :LoginId
    alias_attribute :encrypted_password, :PasswordSHA1Hash
    alias_attribute :first_name, :Name
    alias_attribute :last_name, :Surname

    devise :database_authenticatable, :authentication_keys => [:username]

    attr_accessible :username, :password, :password_confirmation

    def password_salt=(password_salt)

    def password_salt

    def password_digest(password)

    When I post to my /users/sign_in form, I get the following exception:
    Mysql2::Error: Unknown column 'my_legacy_user_table.username' in 'where clause': SELECT `kms_User`.* FROM `my_legacy_user_table` WHERE (`my_legacy_user_table`.`username` = 'mrichman') LIMIT 1
    I suppose I was under the assumption that alias_attribute would instruct ActiveRecord to use the real column name (UserId) and not the alias (username). What am I doing wrong?

  2. Hi Mark,

    A couple of things to check. This was posted when Rails 2.3 was brand new so a few things may have changed. If you're using Rails 3 you'll be starting from scratch as ActiveModel has changed a lot and AREL generates sql differently to ActiveRecord.

    The next thing to check is that Devise is using the right column, try using the legacy column name in the config line:

    devise :database_authenticatable, :authentication_keys => [: LoginId]

    Finally it's possible that the Mysql2 driver works slightly different to the oracle driver.

    Hope something here helps, I haven't looked at a legacy rails app in about 9 months so not sure what the newer issues are.

  3. THANK YOU. Just made my life a lot easier hooking up to a legacy SQL Server database.