Merge pull request #28773 from kamipo/support_descending_indexes

Support Descending Indexes for MySQL

8 files changed, 128 insertions, 71 deletions

diff --git a/activerecord/CHANGELOG.md b/activerecord/CHANGELOG.md
index ffe588b3c5..f74425b281 100644
--- a/activerecord/CHANGELOG.md
+++ b/activerecord/CHANGELOG.md
@@ -1,3 +1,10 @@
+*   Support Descending Indexes for MySQL.
+
+    MySQL 8.0.1 and higher supports descending indexes: `DESC` in an index definition is no longer ignored.
+    See https://dev.mysql.com/doc/refman/8.0/en/descending-indexes.html.
+
+    *Ryuta Kamizono*
+
 *   Fix inconsistency with changed attributes when overriding AR attribute reader.
 
     *bogdanvlviv*
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 4682afc188..7cafbb7e12 100644
--- a/activerecord/lib/active_record/connection_adapters/abstract/schema_definitions.rb
+++ b/activerecord/lib/active_record/connection_adapters/abstract/schema_definitions.rb
@@ -3,7 +3,32 @@ module ActiveRecord
     # Abstract representation of an index definition on a table. Instances of
     # this type are typically created and returned by methods in database
     # adapters. e.g. ActiveRecord::ConnectionAdapters::AbstractMysqlAdapter#indexes
-    IndexDefinition = Struct.new(:table, :name, :unique, :columns, :lengths, :orders, :where, :type, :using, :comment) #:nodoc:
+    class IndexDefinition # :nodoc:
+      attr_reader :table, :name, :unique, :columns, :lengths, :orders, :where, :type, :using, :comment
+
+      def initialize(
+        table, name,
+        unique = false,
+        columns = [],
+        lengths: {},
+        orders: {},
+        where: nil,
+        type: nil,
+        using: nil,
+        comment: nil
+      )
+        @table = table
+        @name = name
+        @unique = unique
+        @columns = columns
+        @lengths = lengths
+        @orders = orders
+        @where = where
+        @type = type
+        @using = using
+        @comment = comment
+      end
+    end
 
     # Abstract representation of a column definition. Instances of this type
     # are typically created by methods in TableDefinition, and added to the
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 f118e086bb..71fa7b929c 100644
--- a/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb
+++ b/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb
@@ -48,9 +48,6 @@ module ActiveRecord
         json:        { name: "json" },
       }
 
-      INDEX_TYPES  = [:fulltext, :spatial]
-      INDEX_USINGS = [:btree, :hash]
-
       class StatementPool < ConnectionAdapters::StatementPool
         private def dealloc(stmt)
           stmt[:stmt].close
@@ -93,10 +90,8 @@ 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
+        !mariadb? && version >= "8.0.1"
       end
 
       def supports_transaction_isolation?
@@ -304,36 +299,6 @@ module ActiveRecord
         execute "TRUNCATE TABLE #{quote_table_name(table_name)}", name
       end
 
-      # Returns an array of indexes for the given table.
-      def indexes(table_name, name = nil) #:nodoc:
-        if name
-          ActiveSupport::Deprecation.warn(<<-MSG.squish)
-            Passing name to #indexes is deprecated without replacement.
-          MSG
-        end
-
-        indexes = []
-        current_index = nil
-        execute_and_free("SHOW KEYS FROM #{quote_table_name(table_name)}", "SCHEMA") do |result|
-          each_hash(result) do |row|
-            if current_index != row[:Key_name]
-              next if row[:Key_name] == "PRIMARY" # skip the primary key
-              current_index = row[:Key_name]
-
-              mysql_index_type = row[:Index_type].downcase.to_sym
-              index_type  = INDEX_TYPES.include?(mysql_index_type)  ? mysql_index_type : nil
-              index_using = INDEX_USINGS.include?(mysql_index_type) ? mysql_index_type : nil
-              indexes << IndexDefinition.new(row[:Table], row[:Key_name], row[:Non_unique].to_i == 0, [], {}, nil, nil, index_type, index_using, row[:Index_comment].presence)
-            end
-
-            indexes.last.columns << row[:Column_name]
-            indexes.last.lengths.merge!(row[:Column_name] => row[:Sub_part].to_i) if row[:Sub_part]
-          end
-        end
-
-        indexes
-      end
-
       def table_comment(table_name) # :nodoc:
         scope = quoted_scope(table_name)
 
diff --git a/activerecord/lib/active_record/connection_adapters/mysql/schema_statements.rb b/activerecord/lib/active_record/connection_adapters/mysql/schema_statements.rb
index 9e2d0fb5e7..571edffec7 100644
--- a/activerecord/lib/active_record/connection_adapters/mysql/schema_statements.rb
+++ b/activerecord/lib/active_record/connection_adapters/mysql/schema_statements.rb
@@ -2,6 +2,49 @@ module ActiveRecord
   module ConnectionAdapters
     module MySQL
       module SchemaStatements # :nodoc:
+        # Returns an array of indexes for the given table.
+        def indexes(table_name, name = nil)
+          if name
+            ActiveSupport::Deprecation.warn(<<-MSG.squish)
+              Passing name to #indexes is deprecated without replacement.
+            MSG
+          end
+
+          indexes = []
+          current_index = nil
+          execute_and_free("SHOW KEYS FROM #{quote_table_name(table_name)}", "SCHEMA") do |result|
+            each_hash(result) do |row|
+              if current_index != row[:Key_name]
+                next if row[:Key_name] == "PRIMARY" # skip the primary key
+                current_index = row[:Key_name]
+
+                mysql_index_type = row[:Index_type].downcase.to_sym
+                case mysql_index_type
+                when :fulltext, :spatial
+                  index_type = mysql_index_type
+                when :btree, :hash
+                  index_using = mysql_index_type
+                end
+
+                indexes << IndexDefinition.new(
+                  row[:Table],
+                  row[:Key_name],
+                  row[:Non_unique].to_i == 0,
+                  type: index_type,
+                  using: index_using,
+                  comment: row[:Index_comment].presence
+                )
+              end
+
+              indexes.last.columns << row[:Column_name]
+              indexes.last.lengths.merge!(row[:Column_name] => row[:Sub_part].to_i) if row[:Sub_part]
+              indexes.last.orders.merge!(row[:Column_name] => :desc) if row[:Collation] == "D"
+            end
+          end
+
+          indexes
+        end
+
         private
           def schema_creation
             MySQL::SchemaCreation.new(self)
diff --git a/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb b/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb
index 02a6da2f71..5b483ad4ab 100644
--- a/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb
+++ b/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb
@@ -140,8 +140,17 @@ module ActiveRecord
               ]
             end
 
-            IndexDefinition.new(table_name, index_name, unique, columns, [], orders, where, nil, using.to_sym, comment.presence)
-          end.compact
+            IndexDefinition.new(
+              table_name,
+              index_name,
+              unique,
+              columns,
+              orders: orders,
+              where: where,
+              using: using.to_sym,
+              comment: comment.presence
+            )
+          end
         end
 
         def table_options(table_name) # :nodoc:
diff --git a/activerecord/lib/active_record/connection_adapters/sqlite3/schema_statements.rb b/activerecord/lib/active_record/connection_adapters/sqlite3/schema_statements.rb
index 8066a05c5e..e02491edb6 100644
--- a/activerecord/lib/active_record/connection_adapters/sqlite3/schema_statements.rb
+++ b/activerecord/lib/active_record/connection_adapters/sqlite3/schema_statements.rb
@@ -2,6 +2,41 @@ module ActiveRecord
   module ConnectionAdapters
     module SQLite3
       module SchemaStatements # :nodoc:
+        # Returns an array of indexes for the given table.
+        def indexes(table_name, name = nil)
+          if name
+            ActiveSupport::Deprecation.warn(<<-MSG.squish)
+              Passing name to #indexes is deprecated without replacement.
+            MSG
+          end
+
+          exec_query("PRAGMA index_list(#{quote_table_name(table_name)})", "SCHEMA").map do |row|
+            index_sql = select_value(<<-SQL, "SCHEMA")
+              SELECT sql
+              FROM sqlite_master
+              WHERE name = #{quote(row['name'])} AND type = 'index'
+              UNION ALL
+              SELECT sql
+              FROM sqlite_temp_master
+              WHERE name = #{quote(row['name'])} AND type = 'index'
+            SQL
+
+            /\sWHERE\s+(?<where>.+)$/i =~ index_sql
+
+            columns = exec_query("PRAGMA index_info(#{quote(row['name'])})", "SCHEMA").map do |col|
+              col["name"]
+            end
+
+            IndexDefinition.new(
+              table_name,
+              row["name"],
+              row["unique"] != 0,
+              columns,
+              where: where
+            )
+          end
+        end
+
         private
           def schema_creation
             SQLite3::SchemaCreation.new(self)
diff --git a/activerecord/lib/active_record/connection_adapters/sqlite3_adapter.rb b/activerecord/lib/active_record/connection_adapters/sqlite3_adapter.rb
index c54b88f7d1..e2c05ccc4e 100644
--- a/activerecord/lib/active_record/connection_adapters/sqlite3_adapter.rb
+++ b/activerecord/lib/active_record/connection_adapters/sqlite3_adapter.rb
@@ -259,37 +259,6 @@ module ActiveRecord
 
       # SCHEMA STATEMENTS ========================================
 
-      # Returns an array of indexes for the given table.
-      def indexes(table_name, name = nil) #:nodoc:
-        if name
-          ActiveSupport::Deprecation.warn(<<-MSG.squish)
-            Passing name to #indexes is deprecated without replacement.
-          MSG
-        end
-
-        exec_query("PRAGMA index_list(#{quote_table_name(table_name)})", "SCHEMA").map do |row|
-          sql = <<-SQL
-            SELECT sql
-            FROM sqlite_master
-            WHERE name=#{quote(row['name'])} AND type='index'
-            UNION ALL
-            SELECT sql
-            FROM sqlite_temp_master
-            WHERE name=#{quote(row['name'])} AND type='index'
-          SQL
-          index_sql = exec_query(sql).first["sql"]
-          match = /\sWHERE\s+(.+)$/i.match(index_sql)
-          where = match[1] if match
-          IndexDefinition.new(
-            table_name,
-            row["name"],
-            row["unique"] != 0,
-            exec_query("PRAGMA index_info('#{row['name']}')", "SCHEMA").map { |col|
-              col["name"]
-            }, nil, nil, where)
-        end
-      end
-
       def primary_keys(table_name) # :nodoc:
         pks = table_structure(table_name).select { |f| f["pk"] > 0 }
         pks.sort_by { |f| f["pk"] }.map { |f| f["name"] }
diff --git a/activerecord/test/cases/schema_dumper_test.rb b/activerecord/test/cases/schema_dumper_test.rb
index fccba4738f..cb8d449ba9 100644
--- a/activerecord/test/cases/schema_dumper_test.rb
+++ b/activerecord/test/cases/schema_dumper_test.rb
@@ -182,7 +182,11 @@ class SchemaDumperTest < ActiveRecord::TestCase
     if current_adapter?(:PostgreSQLAdapter)
       assert_equal 't.index ["firm_id", "type", "rating"], name: "company_index", order: { rating: :desc }', index_definition
     elsif current_adapter?(:Mysql2Adapter)
-      assert_equal 't.index ["firm_id", "type", "rating"], name: "company_index", length: { type: 10 }', index_definition
+      if ActiveRecord::Base.connection.supports_index_sort_order?
+        assert_equal 't.index ["firm_id", "type", "rating"], name: "company_index", length: { type: 10 }, order: { rating: :desc }', index_definition
+      else
+        assert_equal 't.index ["firm_id", "type", "rating"], name: "company_index", length: { type: 10 }', index_definition
+      end
     else
       assert_equal 't.index ["firm_id", "type", "rating"], name: "company_index"', index_definition
     end