AR changes to support creating ordered (asc, desc) indexes

11 files changed, 111 insertions, 20 deletions

diff --git a/activerecord/CHANGELOG.md b/activerecord/CHANGELOG.md
index 2b46a6a869..426f6b48b3 100644
--- a/activerecord/CHANGELOG.md
+++ b/activerecord/CHANGELOG.md
@@ -1,5 +1,7 @@
 ## Rails 3.2.0 (unreleased) ##
 
+*   Support index sort order in sqlite, mysql and postgres adapters. *Vlad Jebelev*
+
 *   Allow the :class_name option for associations to take a symbol (:Client) in addition to
     a string ('Client').
 
diff --git a/activerecord/lib/active_record/connection_adapters/abstract/schema_definitions.rb b/activerecord/lib/active_record/connection_adapters/abstract/schema_definitions.rb
index 989a4fcbca..3f69f75565 100644
--- a/activerecord/lib/active_record/connection_adapters/abstract/schema_definitions.rb
+++ b/activerecord/lib/active_record/connection_adapters/abstract/schema_definitions.rb
@@ -6,7 +6,7 @@ require 'bigdecimal/util'
 
 module ActiveRecord
   module ConnectionAdapters #:nodoc:
-    class IndexDefinition < Struct.new(:table, :name, :unique, :columns, :lengths) #:nodoc:
+    class IndexDefinition < Struct.new(:table, :name, :unique, :columns, :lengths, :orders) #:nodoc:
     end
 
     # Abstract representation of a column definition. Instances of this type
diff --git a/activerecord/lib/active_record/connection_adapters/abstract/schema_statements.rb b/activerecord/lib/active_record/connection_adapters/abstract/schema_statements.rb
index 7226069ebf..0e5e33fa02 100644
--- a/activerecord/lib/active_record/connection_adapters/abstract/schema_statements.rb
+++ b/activerecord/lib/active_record/connection_adapters/abstract/schema_statements.rb
@@ -339,6 +339,14 @@ module ActiveRecord
       #  CREATE INDEX by_name_surname ON accounts(name(10), surname(15))
       #
       # Note: SQLite doesn't support index length
+      #
+      # ====== Creating an index with a sort order (desc or asc, asc is the default)
+      #  add_index(:accounts, [:branch_id, :party_id, :surname], :order => {:branch_id => :desc, :part_id => :asc})
+      # generates
+      #  CREATE INDEX by_branch_desc_party ON accounts(branch_id DESC, party_id ASC, surname)
+      #
+      # Note: mysql doesn't yet support index order (it accepts the syntax but ignores it)
+      #
       def add_index(table_name, column_name, options = {})
         index_name, index_type, index_columns = add_index_options(table_name, column_name, options)
         execute "CREATE #{index_type} INDEX #{quote_column_name(index_name)} ON #{quote_table_name(table_name)} (#{index_columns})"
@@ -520,9 +528,29 @@ module ActiveRecord
       end
 
       protected
+        def add_index_sort_order(option_strings, column_names, options = {})
+          if options.is_a?(Hash) && order = options[:order]
+            case order
+            when Hash
+              column_names.each {|name| option_strings[name] += " #{order[name].to_s.upcase}" if order.has_key?(name)}
+            when String
+              column_names.each {|name| option_strings[name] += " #{order.upcase}"}
+            end
+          end
+
+          return option_strings
+        end
+
         # Overridden by the mysql adapter for supporting index lengths
         def quoted_columns_for_index(column_names, options = {})
-          column_names.map {|name| quote_column_name(name) }
+          option_strings = Hash[column_names.map {|name| [name, '']}]
+
+          # add index sort order if supported
+          if supports_index_sort_order?
+            option_strings = add_index_sort_order(option_strings, column_names, options)
+          end
+
+          column_names.map {|name| quote_column_name(name) + option_strings[name]}
         end
 
         def options_include_default?(options)
diff --git a/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb b/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb
index 4c3a8f7233..c47bcfc406 100644
--- a/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb
+++ b/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb
@@ -130,6 +130,11 @@ module ActiveRecord
         false
       end
 
+      # Does this adapter support index sort order?
+      def supports_index_sort_order?
+        false
+      end
+
       # QUOTING ==================================================
 
       # Override to return the quoted table name. Defaults to column quoting.
diff --git a/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb b/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb
index dd573ba569..35c2118190 100644
--- a/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb
+++ b/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb
@@ -155,6 +155,12 @@ module ActiveRecord
         true
       end
 
+      # Technically MySQL allows to create indexes with the sort order syntax 
+      # but at the moment (5.5) it doesn't yet implement them
+      def supports_index_sort_order?
+        true
+      end
+
       def native_database_types
         NATIVE_DATABASE_TYPES
       end
@@ -526,17 +532,29 @@ module ActiveRecord
 
       protected
 
+      def add_index_length(option_strings, column_names, options = {})
+        if options.is_a?(Hash) && length = options[:length]
+          case length
+          when Hash
+            column_names.each {|name| option_strings[name] += "(#{length[name]})" if length.has_key?(name)}
+          when Fixnum
+            column_names.each {|name| option_strings[name] += "(#{length})"}
+          end
+        end
+
+        return option_strings
+      end
+
       def quoted_columns_for_index(column_names, options = {})
-        length = options[:length] if options.is_a?(Hash)
+        option_strings = Hash[column_names.map {|name| [name, '']}]
 
-        case length
-        when Hash
-          column_names.map {|name| length[name] ? "#{quote_column_name(name)}(#{length[name]})" : quote_column_name(name) }
-        when Fixnum
-          column_names.map {|name| "#{quote_column_name(name)}(#{length})"}
-        else
-          column_names.map {|name| quote_column_name(name) }
-        end
+        # add index length
+        option_strings = add_index_length(option_strings, column_names, options)
+
+        # add index sort order
+        option_strings = add_index_sort_order(option_strings, column_names, options)
+
+        column_names.map {|name| quote_column_name(name) + option_strings[name]}
       end
 
       def translate_exception(exception, message)
diff --git a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb
index e8a43e7bce..15e329a1c8 100644
--- a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb
+++ b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb
@@ -247,6 +247,10 @@ module ActiveRecord
         true
       end
 
+      def supports_index_sort_order?
+        true
+      end
+
       class StatementPool < ConnectionAdapters::StatementPool
         def initialize(connection, max)
           super
@@ -756,7 +760,7 @@ module ActiveRecord
       def indexes(table_name, name = nil)
          schemas = schema_search_path.split(/,/).map { |p| quote(p) }.join(',')
          result = query(<<-SQL, name)
-           SELECT distinct i.relname, d.indisunique, d.indkey, t.oid
+           SELECT distinct i.relname, d.indisunique, d.indkey, pg_get_indexdef(d.indexrelid), t.oid
            FROM pg_class t
            INNER JOIN pg_index d ON t.oid = d.indrelid
            INNER JOIN pg_class i ON d.indexrelid = i.oid
@@ -772,7 +776,8 @@ module ActiveRecord
           index_name = row[0]
           unique = row[1] == 't'
           indkey = row[2].split(" ")
-          oid = row[3]
+          inddef = row[3]
+          oid = row[4]
 
           columns = Hash[query(<<-SQL, "Columns for index #{row[0]} on #{table_name}")]
           SELECT a.attnum, a.attname
@@ -782,7 +787,12 @@ module ActiveRecord
           SQL
 
           column_names = columns.values_at(*indkey).compact
-          column_names.empty? ? nil : IndexDefinition.new(table_name, index_name, unique, column_names)
+
+          # add info on sort order for columns (only desc order is explicitly specified, asc is the default)
+          desc_order_columns = inddef.scan(/(\w+) DESC/).flatten
+          orders = desc_order_columns.any? ? Hash[desc_order_columns.map {|order_column| [order_column, :desc]}] : {}
+      
+          column_names.empty? ? nil : IndexDefinition.new(table_name, index_name, unique, column_names, [], orders)
         end.compact
       end
 
diff --git a/activerecord/lib/active_record/connection_adapters/sqlite_adapter.rb b/activerecord/lib/active_record/connection_adapters/sqlite_adapter.rb
index f74f3e6ec8..caecbc9b3a 100644
--- a/activerecord/lib/active_record/connection_adapters/sqlite_adapter.rb
+++ b/activerecord/lib/active_record/connection_adapters/sqlite_adapter.rb
@@ -157,6 +157,10 @@ module ActiveRecord
         sqlite_version >= '3.1.0'
       end
 
+      def supports_index_sort_order?
+        sqlite_version >= '3.3.0'
+      end
+
       def native_database_types #:nodoc:
         {
           :primary_key => default_primary_key_type,
diff --git a/activerecord/lib/active_record/migration.rb b/activerecord/lib/active_record/migration.rb
index 7166f1b82a..2fb1b8f7a3 100644
--- a/activerecord/lib/active_record/migration.rb
+++ b/activerecord/lib/active_record/migration.rb
@@ -116,8 +116,9 @@ module ActiveRecord
   #   +column_name+ from the table called +table_name+.
   # * <tt>add_index(table_name, column_names, options)</tt>: Adds a new index
   #   with the name of the column. Other options include
-  #   <tt>:name</tt> and <tt>:unique</tt> (e.g.
-  #   <tt>{ :name => "users_name_index", :unique => true }</tt>).
+  #   <tt>:name</tt>, <tt>:unique</tt> (e.g.
+  #   <tt>{ :name => "users_name_index", :unique => true }</tt>) and <tt>:order</tt>
+  #   (e.g. { :order => {:name => :desc} }</tt>).
   # * <tt>remove_index(table_name, :column => column_name)</tt>: Removes the index
   #   specified by +column_name+.
   # * <tt>remove_index(table_name, :name => index_name)</tt>: Removes the index
diff --git a/activerecord/lib/active_record/schema_dumper.rb b/activerecord/lib/active_record/schema_dumper.rb
index 6fe305f843..cdde5cf3b9 100644
--- a/activerecord/lib/active_record/schema_dumper.rb
+++ b/activerecord/lib/active_record/schema_dumper.rb
@@ -190,6 +190,9 @@ HEADER
             index_lengths = (index.lengths || []).compact
             statement_parts << (':length => ' + Hash[index.columns.zip(index.lengths)].inspect) unless index_lengths.empty?
 
+            index_orders = (index.orders || {})
+            statement_parts << (':order => ' + index.orders.inspect) unless index_orders.empty?
+
             '  ' + statement_parts.join(', ')
           end
 
diff --git a/activerecord/test/cases/adapters/postgresql/schema_test.rb b/activerecord/test/cases/adapters/postgresql/schema_test.rb
index c8f8714f66..657527137a 100644
--- a/activerecord/test/cases/adapters/postgresql/schema_test.rb
+++ b/activerecord/test/cases/adapters/postgresql/schema_test.rb
@@ -10,14 +10,17 @@ class SchemaTest < ActiveRecord::TestCase
   INDEX_A_NAME = 'a_index_things_on_name'
   INDEX_B_NAME = 'b_index_things_on_different_columns_in_each_schema'
   INDEX_C_NAME = 'c_index_full_text_search'
+  INDEX_D_NAME = 'd_index_things_on_description_desc'
   INDEX_A_COLUMN = 'name'
   INDEX_B_COLUMN_S1 = 'email'
   INDEX_B_COLUMN_S2 = 'moment'
   INDEX_C_COLUMN = %q{(to_tsvector('english', coalesce(things.name, '')))}
+  INDEX_D_COLUMN = 'description'
   COLUMNS = [
     'id integer',
     'name character varying(50)',
     'email character varying(50)',
+    'description character varying(100)',
     'moment timestamp without time zone default now()'
   ]
   PK_TABLE_NAME = 'table_with_pk'
@@ -54,6 +57,8 @@ class SchemaTest < ActiveRecord::TestCase
     @connection.execute "CREATE INDEX #{INDEX_B_NAME} ON #{SCHEMA2_NAME}.#{TABLE_NAME}  USING btree (#{INDEX_B_COLUMN_S2});"
     @connection.execute "CREATE INDEX #{INDEX_C_NAME} ON #{SCHEMA_NAME}.#{TABLE_NAME}  USING gin (#{INDEX_C_COLUMN});"
     @connection.execute "CREATE INDEX #{INDEX_C_NAME} ON #{SCHEMA2_NAME}.#{TABLE_NAME}  USING gin (#{INDEX_C_COLUMN});"
+    @connection.execute "CREATE INDEX #{INDEX_D_NAME} ON #{SCHEMA_NAME}.#{TABLE_NAME}  USING btree (#{INDEX_D_COLUMN} DESC);"
+    @connection.execute "CREATE INDEX #{INDEX_D_NAME} ON #{SCHEMA2_NAME}.#{TABLE_NAME}  USING btree (#{INDEX_D_COLUMN} DESC);"
     @connection.execute "CREATE TABLE #{SCHEMA_NAME}.#{PK_TABLE_NAME} (id serial primary key)"
   end
 
@@ -184,11 +189,11 @@ class SchemaTest < ActiveRecord::TestCase
   end
 
   def test_dump_indexes_for_schema_one
-    do_dump_index_tests_for_schema(SCHEMA_NAME, INDEX_A_COLUMN, INDEX_B_COLUMN_S1)
+    do_dump_index_tests_for_schema(SCHEMA_NAME, INDEX_A_COLUMN, INDEX_B_COLUMN_S1, INDEX_D_COLUMN)
   end
 
   def test_dump_indexes_for_schema_two
-    do_dump_index_tests_for_schema(SCHEMA2_NAME, INDEX_A_COLUMN, INDEX_B_COLUMN_S2)
+    do_dump_index_tests_for_schema(SCHEMA2_NAME, INDEX_A_COLUMN, INDEX_B_COLUMN_S2, INDEX_D_COLUMN)
   end
 
   def test_with_uppercase_index_name
@@ -288,13 +293,16 @@ class SchemaTest < ActiveRecord::TestCase
       @connection.schema_search_path = "'$user', public"
     end
 
-    def do_dump_index_tests_for_schema(this_schema_name, first_index_column_name, second_index_column_name)
+    def do_dump_index_tests_for_schema(this_schema_name, first_index_column_name, second_index_column_name, third_index_column_name)
       with_schema_search_path(this_schema_name) do
         indexes = @connection.indexes(TABLE_NAME).sort_by {|i| i.name}
-        assert_equal 2,indexes.size
+        assert_equal 3,indexes.size
 
         do_dump_index_assertions_for_one_index(indexes[0], INDEX_A_NAME, first_index_column_name)
         do_dump_index_assertions_for_one_index(indexes[1], INDEX_B_NAME, second_index_column_name)
+        do_dump_index_assertions_for_one_index(indexes[2], INDEX_D_NAME, third_index_column_name)
+
+        assert_equal :desc, indexes.select{|i| i.name == INDEX_D_NAME}[0].orders[INDEX_D_COLUMN]
       end
     end
 
diff --git a/activerecord/test/cases/migration_test.rb b/activerecord/test/cases/migration_test.rb
index 5c47a8ad33..e8ad37d437 100644
--- a/activerecord/test/cases/migration_test.rb
+++ b/activerecord/test/cases/migration_test.rb
@@ -121,6 +121,18 @@ if ActiveRecord::Base.connection.supports_migrations?
         assert_nothing_raised { Person.connection.add_index("people", %w(last_name first_name administrator), :name => "named_admin") }
         assert_nothing_raised { Person.connection.remove_index("people", :name => "named_admin") }
       end
+
+      # Selected adapters support index sort order
+      if current_adapter?(:SQLite3Adapter, :MysqlAdapter, :Mysql2Adapter, :PostgreSQLAdapter)
+        assert_nothing_raised { Person.connection.add_index("people", ["last_name"], :order => {:last_name => :desc}) }
+        assert_nothing_raised { Person.connection.remove_index("people", ["last_name"]) }
+        assert_nothing_raised { Person.connection.add_index("people", ["last_name", "first_name"], :order => {:last_name => :desc}) }
+        assert_nothing_raised { Person.connection.remove_index("people", ["last_name", "first_name"]) }
+        assert_nothing_raised { Person.connection.add_index("people", ["last_name", "first_name"], :order => {:last_name => :desc, :first_name => :asc}) }
+        assert_nothing_raised { Person.connection.remove_index("people", ["last_name", "first_name"]) }
+        assert_nothing_raised { Person.connection.add_index("people", ["last_name", "first_name"], :order => :desc) }
+        assert_nothing_raised { Person.connection.remove_index("people", ["last_name", "first_name"]) }
+      end
     end
 
     def test_index_symbol_names