Let ActiveRecord support Enterprise Databases.

I am currently investigating using ActiveRecord's Migrations to support a legacy enterprise application. Migrations could help us because we currently support multiple versions of the product, which means multiple versions of the schema. Development and QA are already spending too much time reinstalling different versions of the database.

So can ActiveRecord Migrations support my application? Nope; not right out of the box.

Can I tweak ActiveRecord to support my application? YES! It isn't too hard to extend/override ActiveRecord.

Why can't ActiveRecord support my application out of the box? The short answer is ActiveRecord was written to only support a database neutral schema. Supporting multiple databases means only supporting the lowest common denominator. So db specific types in my schema like tinyint and smallint get simplified to integer. ActiveRecord doesn't support all the different constraints that are available: foreign keys, composite keys, triggers. Also, I noticed ActiveRecord did not capture any of my Views (SqlServer).

In addition, I have a deployment problem. I have to ship and deploy my application to customers. Right now, I can't ship and install Ruby on those machines. So I'd rather just have ActiveRecord Migrations produce a SQL file that I can deploy.

After a little experimentation, I discovered it is possible to easily overcome these issues. The first is can I run Migrations without Rails? This is easily accomplished as described on PragDave's blog.

Next, I wanted to know how I could modify ActiveRecord to serve my needs. Thankfully Ruby is such a flexible language that it allows modification of existing classes. So I can override ActiveRecord methods as needed. For example:

require 'rubygems'
require 'active_record'

module ActiveRecord
  module ConnectionAdapters # :nodoc:
    class SQLServerAdapter
      def execute( sql, name = nil )
        puts sql
        puts "GO"
        puts 
      end
    end
  end
end

In the example above, I first load ActiveRecord, then modify the SQLServerAdapter's execute method (my database is SqlServer). It turns out that this method is called by Migration to send SQL to the database. So in this example, I am simply sending the SQL to STDOUT. Notice that I can also modify the output. In this example I add the “GO” command between SQL statements. I could have just as easily added transactions or error checking. With a few more tweaks, I could have sent this to a file to generate a SQL script from the Migration.

ActiveRecord contains code to simplify data types for database neutrality. Since the legacy database uses many other types, this simplification is an issue. So after examining the source, I found a couple of places where the simplification is applied. By overriding these methods it is easy to get all ActiveRecord to support the data types required. For example, the code below adds several types.

module ActiveRecord
  module ConnectionAdapters # :nodoc:
    class SQLServerAdapter
      def native_database_types
        SQLServerAdapter.native_database_types
      end
      
      def self.native_database_types
        {
          :binary => { :name => "binary", :haslimit => true },
          :bit     => { :name => "bit"},
          :char => { :name => "char", :haslimit => true },
          :decimal => { :name => "decimal" },
          :int     => { :name => "int" },
          :nvarchar => { :name => "nvarchar" },
          :real => { :name => "real" },
          :smallint => { :name => "smallint" },
          :tinyint => { :name => "tinyint" },
          :timestamp => { :name => "timestamp" },
          :uniqueidentifier => { :name => "uniqueidentifier" },
          :varchar      => { :name => "varchar", :limit => 255, :haslimit => true  },
          :primary_key => "int NOT NULL IDENTITY(1, 1) PRIMARY KEY",
          :text        => { :name => "text" },
          :float       => { :name => "float", :limit => 8 },
          :datetime    => { :name => "datetime" },
          :image      => { :name => "image"},
        }
      end
    end

    class ColumnWithIdentity
      def initialize(name, default, sql_type = nil, is_identity = false, null = true, scale_value = 0)
        super(name, default, sql_type, null)
        @identity = is_identity
        @is_special = sql_type =~ /text|ntext|image/i ? true : false
        @scale = scale_value
        # SQL Server only supports limits on a few types
        @limit = nil unless SQLServerAdapter.native_database_types[@type][:haslimit] == true
      end

      
      #DO NOT SIMPLIFY, Just use the native type name; trim off size
      def simplified_type(field_type)
        field_type.slice( /[^\(]*/).to_sym 
      end
    end
  end
end

This example shows how easy it is to change ActiveRecord's behavior as needed.

Now the code above isn't a complete solution, but it will get SchemaDump to output the correct types that my legacy system is using. If you look at the original methods you can see the changes were pretty simple to make. One refactoring I did was to make SQLServerAdapter's instance method native_database_types into a class method. Then I could access it from ColumnWithIdentity, where it was copied code before. Also, I added the :haslimit key to make the code in ColumnWithIdentity more data driven off the native_database_types hash. Before, it had the types with limits hard coded into the ColumnWithIdentity initialize method.

What about adding new features? This is probably the easiest of all. A frequent request is to support composite keys. Just as an example of extending the DSL, the following will create a primary key constraint and support multiple columns in the key.

module ActiveRecord
  module ConnectionAdapters # :nodoc:
    module SchemaStatements
      def add_pk(table_name, column_names)
        quoted_column_names = column_names.map { |e| quote_column_name(e) }.join(", ")
        sql = "ALTER TABLE #{table_name} ADD CONSTRAINT PK_#{table_name} PRIMARY KEY CLUSTERED ( #{quoted_column_names} ) ON [PRIMARY]"
        execute(sql)
      end
    end
  end
end

This is simple DSL programming in Ruby. Another approach is to allow the :primarykey option for the create_table method to take an array of column names. I'll leave that exercise for later.

These examples show what is possible. I know there is a raging debate about supporting db neutrality verses db specifics. DHH has expressed his intention to keep Rails db neutral, because that is best for the world he lives within. For those of us unfortunate enough to live with legacy databases, we need an ActiveRecord that fully supports our database. Why not support both? Why not setup a method for the enterprise folks to add the database specifics to ActiveRecord? Maybe some form of plug-in or stable API that we could extend (my changes above risk being broken by future versions of ActiveRecord). To satisfy the db neutral camp, just generate warnings or errors if db specific extensions are used when ActiveRecord is configured to be db neutral.

Happy Migrating!