aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorRyuta Kamizono <kamipo@gmail.com>2018-10-25 07:29:51 +0900
committerRyuta Kamizono <kamipo@gmail.com>2018-10-25 18:09:02 +0900
commitd496055b5b75387136bcdd1cc93aeab14dbda606 (patch)
tree232e12f4d7b3800386f51fb09c5387699b84f8b1
parentd76d66fb554fc3267de93d780344815d05ae6533 (diff)
downloadrails-d496055b5b75387136bcdd1cc93aeab14dbda606.tar.gz
rails-d496055b5b75387136bcdd1cc93aeab14dbda606.tar.bz2
rails-d496055b5b75387136bcdd1cc93aeab14dbda606.zip
Support expression indexes for MySQL
MySQL 8.0.13 and higher supports functional key parts that index expression values rather than column or column prefix values. https://dev.mysql.com/doc/refman/8.0/en/create-index.html
-rw-r--r--activerecord/CHANGELOG.md9
-rw-r--r--activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb4
-rw-r--r--activerecord/lib/active_record/connection_adapters/mysql/schema_statements.rb34
-rw-r--r--activerecord/test/cases/schema_dumper_test.rb7
-rw-r--r--activerecord/test/schema/schema.rb2
5 files changed, 49 insertions, 7 deletions
diff --git a/activerecord/CHANGELOG.md b/activerecord/CHANGELOG.md
index 97b7ad93d1..94061a076d 100644
--- a/activerecord/CHANGELOG.md
+++ b/activerecord/CHANGELOG.md
@@ -1,3 +1,12 @@
+* Support expression indexes for MySQL.
+
+ MySQL 8.0.13 and higher supports functional key parts that index
+ expression values rather than column or column prefix values.
+
+ https://dev.mysql.com/doc/refman/8.0/en/create-index.html
+
+ *Ryuta Kamizono*
+
* Fix collection cache key with limit and custom select to avoid ambiguous timestamp column error.
Fixes #33056.
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 0f73641369..13c799b64a 100644
--- a/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb
+++ b/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb
@@ -72,6 +72,10 @@ module ActiveRecord
!mariadb? && version >= "8.0.1"
end
+ def supports_expression_index?
+ !mariadb? && version >= "8.0.13"
+ end
+
def supports_transaction_isolation?
true
end
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 e167c01802..65e9a1fda5 100644
--- a/activerecord/lib/active_record/connection_adapters/mysql/schema_statements.rb
+++ b/activerecord/lib/active_record/connection_adapters/mysql/schema_statements.rb
@@ -35,13 +35,39 @@ module ActiveRecord
]
end
- indexes.last[-2] << row[:Column_name]
- indexes.last[-1][:lengths][row[:Column_name]] = row[:Sub_part].to_i if row[:Sub_part]
- indexes.last[-1][:orders].merge!(row[:Column_name] => :desc) if row[:Collation] == "D"
+ if row[:Expression]
+ expression = row[:Expression]
+ expression = +"(#{expression})" unless expression.start_with?("(")
+ indexes.last[-2] << expression
+ indexes.last[-1][:expressions] ||= {}
+ indexes.last[-1][:expressions][expression] = expression
+ indexes.last[-1][:orders][expression] = :desc if row[:Collation] == "D"
+ else
+ indexes.last[-2] << row[:Column_name]
+ indexes.last[-1][:lengths][row[:Column_name]] = row[:Sub_part].to_i if row[:Sub_part]
+ indexes.last[-1][:orders][row[:Column_name]] = :desc if row[:Collation] == "D"
+ end
end
end
- indexes.map { |index| IndexDefinition.new(*index) }
+ indexes.map do |index|
+ options = index.last
+
+ if expressions = options.delete(:expressions)
+ orders = options.delete(:orders)
+ lengths = options.delete(:lengths)
+
+ columns = index[-2].map { |name|
+ [ name.to_sym, expressions[name] || +quote_column_name(name) ]
+ }.to_h
+
+ index[-2] = add_options_for_index_columns(
+ columns, order: orders, length: lengths
+ ).values.join(", ")
+ end
+
+ IndexDefinition.new(*index)
+ end
end
def remove_column(table_name, column_name, type = nil, options = {})
diff --git a/activerecord/test/cases/schema_dumper_test.rb b/activerecord/test/cases/schema_dumper_test.rb
index db13f20a39..dda3efa47c 100644
--- a/activerecord/test/cases/schema_dumper_test.rb
+++ b/activerecord/test/cases/schema_dumper_test.rb
@@ -229,11 +229,14 @@ class SchemaDumperTest < ActiveRecord::TestCase
if ActiveRecord::Base.connection.supports_expression_index?
def test_schema_dump_expression_indices
index_definition = dump_table_schema("companies").split(/\n/).grep(/t\.index.*company_expression_index/).first.strip
+ index_definition.sub!(/, name: "company_expression_index"\z/, "")
if current_adapter?(:PostgreSQLAdapter)
- assert_match %r{CASE.+lower\(\(name\)::text\)}i, index_definition
+ assert_match %r{CASE.+lower\(\(name\)::text\).+END\) DESC"\z}i, index_definition
+ elsif current_adapter?(:Mysql2Adapter)
+ assert_match %r{CASE.+lower\(`name`\).+END\) DESC"\z}i, index_definition
elsif current_adapter?(:SQLite3Adapter)
- assert_match %r{CASE.+lower\(name\)}i, index_definition
+ assert_match %r{CASE.+lower\(name\).+END\) DESC"\z}i, index_definition
else
assert false
end
diff --git a/activerecord/test/schema/schema.rb b/activerecord/test/schema/schema.rb
index 4ef463fdad..56ff81af29 100644
--- a/activerecord/test/schema/schema.rb
+++ b/activerecord/test/schema/schema.rb
@@ -216,7 +216,7 @@ ActiveRecord::Schema.define do
t.index [:firm_id, :type, :rating], name: "company_index", length: { type: 10 }, order: { rating: :desc }
t.index [:firm_id, :type], name: "company_partial_index", where: "(rating > 10)"
t.index :name, name: "company_name_index", using: :btree
- t.index "(CASE WHEN rating > 0 THEN lower(name) END)", name: "company_expression_index" if supports_expression_index?
+ t.index "(CASE WHEN rating > 0 THEN lower(name) END) DESC", name: "company_expression_index" if supports_expression_index?
end
create_table :content, force: true do |t|