Debugging Cross-Database Issues in Rails Migrations

Migrations are wonderful. They are, quite frankly, one of the nicest features to come out of Rails. Migrations were provided by the Rails framework to automate database builds (with corresponding functionality for undoing database builds). In practice, migrations make it easy to create, tear down and re-create database schemas as needed in order to support a project.

All of that automation is extremely convenient. It helps that migrations were designed to be, as much as possible, database-agnostic. However, when you start using migrations to build schemas for multiple types of databases, you will run into a few issues. They're not show-stoppers by any means, but they are issues that you'll need to resolve in order to use migrations effectively.

So, here's a scenario that I ran into on a real-life project. The developers for the project, including myself, were doing their development with MySQL, where the following migration worked perfectly. However, the target database for production was Oracle, where the migration didn't work quite so well.

Listing 1 - A Sample Migration

   class CreateInstallItemExceptions < ActiveRecord::Migration
      def self.up
         create_table :xyz_install_item_exceptions, :id => false do |t|
                  t.column :install_item_id, :integer, :null=>false
                  t.column :exception_type_id, :integer, :null=>false
         end

         add_index :xyz_install_item_exceptions, 
           [:install_item_id, :exception_type_id]
      end

      def self.down
         remove_index :xyz_install_item_exceptions
         drop_table :xyz_install_item_exceptions
      end

   end

The migration logic attempts to do the proper thing in whatever database you've configured your system to use. This is clearly an associational table; the migration is supposed to add an index on the INSTALL_ITEM_ID and EXCEPTION_TYPE_ID columns of the XYZ_INSTALL_ITEM_EXCEPTIONS table. The default logic creates an index called:

      index_xyz_install_item_exceptions_on_install_item_id

There's nothing inherently wrong with this. This name works fine in databases like MySQL, where there's a relatively high limit on the length of the names of database objects. In Oracle, however, names were limited to 30 characters. Therefore, the migration aborted when it tried to create the index.

The solution, of course, is to explicitly define the name of the index:

      add_index :btr_install_item_exceptions, [:install_item_id, :exception_type_id],
         :name => 'btr_installitemex'

However, since the name of the index has been changed from the default, similar code will have to be added to the down method of the migration, or the index will not be properly removed when the table is dropped. The code below handles the desired action:

      remove_index :btr_install_item_exceptions, :name => 'btr_installitemex'

The next wrinkle involves another difference between Oracle and MySQL. With the MySQL database, the index is automatically removed if the table is dropped. In Oracle, it's necessary to explicitly drop the index. My article, Conditional Logic in Rails Migrations, provides a solution for this problem, where different logic can be executed in the migration based on the type of database that is configured.

In this example problem, indexes were impacted by the differences between databases. The same type of problem can occur with Oracle sequences.

For example, most Rails-oriented tables contain an ID column. In MySQL, that column is typically defined as an IDENTITY column, i.e. - a table in which unique numeric values are generated whenever a row is inserted into the table. Oracle has a different database construct known as a sequence, which is subject to the same name-length limitations as indexes and other database objects.

The default name for a sequence, which is automatically generated by Rails, is:

      <table-name>_SEQ

As with indexes, sequences can be explicitly named.

In the scheme of things, these are relatively minor issues. For developers who work with a variety of different databases, solving these issues within migrations enables developers to achieve the benefits of migrations despite differences between database products.



Comments

No comments yet. Be the first.



Leave a Comment

Comments are moderated and will not appear on the site until reviewed.

(not displayed)