July 1, 2008

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:

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:

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.