Adding support for Oracle’s bitmap indexes to Rails migrations

My coworker Greg Donald helped me find a way to override the way Rails migrations create Oracle sequences by putting code in the environment.rb file. I mentioned at the bottom of that post that I would like to find a way to make it also support Oracle’s bitmap indexes. This turned out to be trivial, simply a matter of adding another section to the override code I already had. The method which creates the indexes is ActiveRecord::ConnectionAdapters::SchemaStatements.add_index. It surprises me that there isn’t an Oracle-specific override in the OracleAdapter. Anyway, my code from the last blog post already had the ActiveRecord::ConnectionAdapters in scope, so I added another section as below:

module SchemaStatements
  def add_index(table_name, column_name, options = })
    column_names = Array(column_name)
    index_name   = index_name(table_name, :column => column_names)

    if Hash === options # legacy support, since this param was a string
      index_type = options[:unique] ? "UNIQUE" : options[:bitmap] ? "BITMAP" : ""
      index_name = options[:name] || index_name
    else
      index_type = options
    end
    quoted_column_names = column_names.map { |e| quote_column_name(e) }.join(", ")
    execute "CREATE #{index_type} INDEX #{quote_column_name(index_name)} ON #{quote_table_name(table_name)} (#{quoted_column_names})"
  end
end

Only one change was made to the original:

index_type = options[:unique] ? "UNIQUE" : ""

became

index_type = options[:unique] ? "UNIQUE" : options[:bitmap] ? "BITMAP" : ""

This now allows the specification of a bitmap option to add_index in the migration file:

add_index "mytable", ["mycolumn"], :name => "mytable_bitmap_idx", :bitmap => true

I’m pretty happy with the ability Ruby on Rails gives you to customize things like this. Being able to override class methods in this way is really pretty special.

Altering the way Rails migrations create Oracle sequences

Lately I’ve been using Ruby on Rails for a project at work, and am really enjoying it. Several things bug me about managing an Oracle database with migrations, though. The main one that just struck me as bizarre is how each table’s sequence is created with a starting value of 10,000. Why? Where did that number come from, who came up with it, and why is there not a way to override it within the migration, short of using execute to drop the sequence after the fact and recreate it (since you can’t change an Oracle sequence’s starting value with an alter statement).

I dug into the code for the OracleAdapter, and sure enough, there it is, right there in the create_table method:

def create_table(name, options = {}) #:nodoc:
  super(name, options)
  seq_name = options[:sequence_name] || "#{name}_seq"
  execute "CREATE SEQUENCE #{seq_name} START WITH 10000" unless options[:id] == false
end

I was all set to just modify the CREATE SEQUENCE code above when my coworker Greg Donald, who knows much more about Ruby and RoR than I do, knocked me out with yet another in the long line of things about Ruby on Rails that make me yell “Sweet!” Thanks, Greg!

Within your Rails app’s environment.rb file you can override code. Simply by putting this snippet at the bottom of the file, I was able to change the way migrate creates my Oracle sequences:

module ActiveRecord
  module ConnectionAdapters
    class OracleAdapter
      def create_table(name, options = {}) #:nodoc:
        super(name, options)
        seq_name = options[:sequence_name] || "#{name}_seq"
        execute "CREATE SEQUENCE #{seq_name} START WITH 1 NOCACHE" unless options[:id] == false
      end
    end
  end
end

Notice that I also added in NOCACHE while I was at it, to prevent Oracle from grabbing 20 sequence values at a time.

This is just cool. Without having to keep track of custom changes made to the adapter, which could be lost with future upgrades, and without having to resort to a bunch of execute statements within the migration file to drop and recreate the sequences, I’m able to get the custom behavior that I need. Honestly, of course, I think that the starting value should default to 1, not 10000, and I wish that something like NOCACHE was an option that could be passed. But this gets me what I need, and I can see this sort of override capability coming in very handy for other Rails customizations.

Now on to adding something to support bitmap indexes!