Ruby Buzz Forum - Adding Support for Functional Indexes in Rails

Rails migrations are a great tool, and one of the things I really love about rails. It made database changes phobia a thing of the past :-) But the migration support a pretty low common denominator as to what can be done without resorting to sending direct SQL commands.

One of the things I use all the time and really miss in migrations, are functional indexes. Postgres supports those, and it's a shame not to use it. For those not aware of what a functional index is, it's an index that is built by calling a function for the row values to index, insted of using the actual value itself. The simplest use I have for it is when I want to make names case-insensitive when searching. So that a:

The change works in such a way that if the database doesn't support functional indexes (as defined in the adapter in rails) it will fall back to generate the regular index. So the following statement in a migration:

add_index :users, :name, :functional => 'lower(name)'

Will create a regular index in MySQL, but will create a functional index in Postgres.

Here is the diff agains rails 1.2.1. The changes can also be transfered to other rails versions as they are pretty simple:

Index: activerecord/lib/active_record/connection_adapters/abstract/schema_statements.rb
===================================================================
--- activerecord/lib/active_record/connection_adapters/abstract/schema_statements.rb    (revision 28)
+++ activerecord/lib/active_record/connection_adapters/abstract/schema_statements.rb    (working copy)
@@ -186,14 +186,16 @@
       def add_index(table_name, column_name, options = {})
         column_names = Array(column_name)
         index_name   = index_name(table_name, :column => column_names)
+        functional   = nil

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

Index: activerecord/lib/active_record/connection_adapters/abstract_adapter.rb
===================================================================
--- activerecord/lib/active_record/connection_adapters/abstract_adapter.rb      (revision 28)
+++ activerecord/lib/active_record/connection_adapters/abstract_adapter.rb      (working copy)
@@ -42,6 +42,12 @@
         false
       end

+      # Does this adapter support functional indexes? Backend specific, as the
+      # abstract adapter always returns +false+.
+      def supports_functional_indexes?
+        false
+      end
+
       # Does this adapter support using DISTINCT within COUNT?  This is +true+
       # for all adapters except sqlite.
       def supports_count_distinct?
Index: activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb
===================================================================
--- activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb    (revision 28)
+++ activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb    (working copy)
@@ -111,6 +111,10 @@
         63
       end

+      def supports_functional_indexes?
+        true
+      end
+
       # QUOTING ==================================================

       def quote(value, column = nil)


	Web Artima.com