Just recently I’ve had to do a lot of work getting rails to play with a 15 year old Oracle database. Needless to say this database doesn’t follow the conventions we’ve all grown used to with rails. The table names are all over the place, the primary keys aren’t surrogate keys but actually have business meaning, composite keys which include date columns, crazy methods of generating primary keys instead of sequences….. if there’s a rails convention this thing doesn’t break then I’ve not found it yet!

However rails does provide methods for us to get it working with these legacy systems. Of course, if we were designing an application from scratch, we’d never need these techniques because we’d do it “the rails way” from the start. So I’m going to cover a couple of techniques which I’ve had to use in case you find yourself with the misfortune of having to marry rails to a pig!

Telling rails to use a different table and primary key

If we have a model named Student then rails convention dictates this model will map to a table called students and it will have a primary key called id. Well the real world isn’t always so accommodating. No problem…

1
2
3
4

class Student < ActiveRecord::Base
  set_table_name :student
  set_primary_key :s_stno
end

Composite keys

In this instance, the composite primary keys gem is your friend. Simply install…

1

sudo gem install composite_primary_keys

... and require in your environment.rb...

1

require 'composite_primary_keys'

Now you are ready to start supplying multiple keys in your models…

1
2
3
4

class CourseFee < ActiveRecord::Base
  set_table_name "course_avail"
  set_primary_keys :course_code, :course_date
end

You may notice that one of those composite keys is a date. This can cause you problems when you try and generate a url for an instance of this class because the date won’t be output in the correct format. So we need to override the to_param method to output the date in database format.

1
2
3

  def to_param
    "#{course_code},#{course_date.to_s(:db)}"
  end

Non standard primary key generation

This one was tricky! Now it wouldn’t be too hard if your legacy system uses a sequence to generate primary keys because you can just tell rails to use this sequence if it’s not named using the rails convention (which is tablename_seq).

1
2
3

class Student < ActiveRecord::Base
  set_sequence_name 'a_non_standard_sequence_name'
end

However you could find yourself in a nasty situation where the database doesn’t use a sequence for primary keys – it uses a value stored in a table instead. No problem you’re thinking to yourself, I’ll just use a before_create hook to populate the primary key? Nope, not going to work. The oracle_enhanced adapter will still go off and try to use a sequence called tablename_seq causing the whole house of cards to come crashing down. So we need to stop oracle_enhanced from looking for this sequence and instead tell it to generate the primary key some other way.

I stole the basis of this hack from the oracle_enhanced website and added my own twist to it to fit my circumstances. I doubt your scenario will exactly match mine (and I hope for your sake it doesn’t!), but you can use this as a starting point.

So…. the primary keys in my evil system are stored in a table called counter. There is only one row in this table and a column for each table you want a primary key for. Nasty. So I started off by generating a model for this table with a method to pull out a primary key…

1
2
3
4
5
6
7
8
9
10

class Counter < ActiveRecord::Base
  set_table_name :counter

  def self.next_primary_key(col)
    current_value = Counter.first.read_attribute(col)
    new_value = current_value + 1
    Counter.connection.execute("UPDATE counter SET #{col} = #{new_value}")
    new_value
  end
end

Now assuming there is a column in the counter table called student, I can pull a primary key out by calling:

1

Counter.next_primary_key('student')

This will increment the value in the column and return it. It’s a nasty way to generate primary keys but hey, I didn’t design this!

So now we can access these primary keys in a tidy way we need to get our Student model to use it for its primary keys. And this is where the fun starts – we need to monkey patch the oracle_enhanced adapter to use this table, but only when our table doesn’t have a sequence. To do this we create an initialiser in config/initializers.

1
2
3
4
5
6
7
8
9
10
11
12
13
14

# config/initializers/oracle_enhanced.rb

ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.class_eval do
  alias_method :orig_next_sequence_value, :next_sequence_value

  def next_sequence_value(sequence_name)
    if sequence_name =~ /^counter-/
      # Strip the counter- part out of the string and pass the remainder to next_primary_key
      Counter.next_primary_key(sequence_name.match("counter-(.*$)")[1])
    else
      orig_next_sequence_value(sequence_name)
    end
  end
end

What this code does is check what the name of the model’s sequence is and if it starts with counter- it looks in the counter table for the primary key. So when we specify the following sequence name…

1
2
3
4
5

class Student < ActiveRecord::Base
  set_table_name :student
  set_primary_key :s_stno
  set_sequence_name 'counter-student'
end

... our little monkey patch will spot the counter- prefix, strip it out and make its primary key the result of a call to the following:

1

Counter.next_primary_key('student')

Perfect! It’s a little complicated but we’ve now got a flexible solution to cater for whatever crazy method of primary key generation our predecessors may have dreamt up. And the best part is it gracefully falls back to the default behaviour if the sequence name doesn’t start with counter-.

As I said before, the main guts of this hack (the monkey patching of oracle_enhanced) was written by Raimonds Simanovskis on the oracle_enhanced website. I merely souped it up a little with some regular expressions and adopted it to fit my particular use case.

Further reading

If you are wondering how to setup rails to talk to an Oracle database then you might be interested in my previous two part series on the subject which starts with this post