Add Expression Indexes and Operator Classes support for PostgreSQL

Example:

    create_table :users do |t|
      t.string :name
      t.index 'lower(name) varchar_pattern_ops'
    end

Fixes #19090.
Fixes #21765.
Fixes #21819.
Fixes #24359.

Signed-off-by: Jeremy Daer <jeremydaer@gmail.com>

5 files changed, 53 insertions, 20 deletions

diff --git a/activerecord/test/cases/adapters/postgresql/active_schema_test.rb b/activerecord/test/cases/adapters/postgresql/active_schema_test.rb
index edb391b525..439e2ce6f7 100644
--- a/activerecord/test/cases/adapters/postgresql/active_schema_test.rb
+++ b/activerecord/test/cases/adapters/postgresql/active_schema_test.rb
@@ -28,7 +28,13 @@ class PostgresqlActiveSchemaTest < ActiveRecord::PostgreSQLTestCase
     ActiveRecord::ConnectionAdapters::PostgreSQLAdapter.send(:define_method, :index_name_exists?) { |*| false }
 
     expected = %(CREATE UNIQUE INDEX  "index_people_on_last_name" ON "people"  ("last_name") WHERE state = 'active')
-    assert_equal expected, add_index(:people, :last_name, :unique => true, :where => "state = 'active'")
+    assert_equal expected, add_index(:people, :last_name, unique: true, where: "state = 'active'")
+
+    expected = %(CREATE UNIQUE INDEX  "index_people_on_lower_last_name" ON "people"  (lower(last_name)))
+    assert_equal expected, add_index(:people, 'lower(last_name)', unique: true)
+
+    expected = %(CREATE UNIQUE INDEX  "index_people_on_last_name_varchar_pattern_ops" ON "people"  (last_name varchar_pattern_ops))
+    assert_equal expected, add_index(:people, 'last_name varchar_pattern_ops', unique: true)
 
     expected = %(CREATE  INDEX CONCURRENTLY "index_people_on_last_name" ON "people"  ("last_name"))
     assert_equal expected, add_index(:people, :last_name, algorithm: :concurrently)
@@ -39,16 +45,17 @@ class PostgresqlActiveSchemaTest < ActiveRecord::PostgreSQLTestCase
 
       expected = %(CREATE  INDEX CONCURRENTLY "index_people_on_last_name" ON "people" USING #{type} ("last_name"))
       assert_equal expected, add_index(:people, :last_name, using: type, algorithm: :concurrently)
+
+      expected = %(CREATE UNIQUE INDEX  "index_people_on_last_name" ON "people" USING #{type} ("last_name") WHERE state = 'active')
+      assert_equal expected, add_index(:people, :last_name, using: type, unique: true, where: "state = 'active'")
+
+      expected = %(CREATE UNIQUE INDEX  "index_people_on_lower_last_name" ON "people" USING #{type} (lower(last_name)))
+      assert_equal expected, add_index(:people, 'lower(last_name)', using: type, unique: true)
     end
 
     assert_raise ArgumentError do
       add_index(:people, :last_name, algorithm: :copy)
     end
-    expected = %(CREATE UNIQUE INDEX  "index_people_on_last_name" ON "people" USING gist ("last_name"))
-    assert_equal expected, add_index(:people, :last_name, :unique => true, :using => :gist)
-
-    expected = %(CREATE UNIQUE INDEX  "index_people_on_last_name" ON "people" USING gist ("last_name") WHERE state = 'active')
-    assert_equal expected, add_index(:people, :last_name, :unique => true, :where => "state = 'active'", :using => :gist)
 
     ActiveRecord::ConnectionAdapters::PostgreSQLAdapter.send :remove_method, :index_name_exists?
   end
diff --git a/activerecord/test/cases/adapters/postgresql/postgresql_adapter_test.rb b/activerecord/test/cases/adapters/postgresql/postgresql_adapter_test.rb
index 8b08ebc3c4..9832df7839 100644
--- a/activerecord/test/cases/adapters/postgresql/postgresql_adapter_test.rb
+++ b/activerecord/test/cases/adapters/postgresql/postgresql_adapter_test.rb
@@ -259,6 +259,22 @@ module ActiveRecord
         end
       end
 
+      def test_expression_index
+        with_example_table do
+          @connection.add_index 'ex', 'mod(id, 10), abs(number)', name: 'expression'
+          index = @connection.indexes('ex').find { |idx| idx.name == 'expression' }
+          assert_equal 'mod(id, 10), abs(number)', index.columns
+        end
+      end
+
+      def test_index_with_opclass
+        with_example_table do
+          @connection.add_index 'ex', 'data varchar_pattern_ops', name: 'with_opclass'
+          index = @connection.indexes('ex').find { |idx| idx.name == 'with_opclass' }
+          assert_equal 'data varchar_pattern_ops', index.columns
+        end
+      end
+
       def test_columns_for_distinct_zero_orders
         assert_equal "posts.id",
           @connection.columns_for_distinct("posts.id", [])
diff --git a/activerecord/test/cases/adapters/postgresql/schema_test.rb b/activerecord/test/cases/adapters/postgresql/schema_test.rb
index 00ebabc9c5..52ef07f654 100644
--- a/activerecord/test/cases/adapters/postgresql/schema_test.rb
+++ b/activerecord/test/cases/adapters/postgresql/schema_test.rb
@@ -325,7 +325,7 @@ class SchemaTest < ActiveRecord::PostgreSQLTestCase
 
   def test_dump_indexes_for_table_with_scheme_specified_in_name
     indexes = @connection.indexes("#{SCHEMA_NAME}.#{TABLE_NAME}")
-    assert_equal 4, indexes.size
+    assert_equal 5, indexes.size
   end
 
   def test_with_uppercase_index_name
@@ -449,18 +449,22 @@ class SchemaTest < ActiveRecord::PostgreSQLTestCase
     def do_dump_index_tests_for_schema(this_schema_name, first_index_column_name, second_index_column_name, third_index_column_name, fourth_index_column_name)
       with_schema_search_path(this_schema_name) do
         indexes = @connection.indexes(TABLE_NAME).sort_by(&:name)
-        assert_equal 4,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)
-        do_dump_index_assertions_for_one_index(indexes[3], INDEX_E_NAME, fourth_index_column_name)
-
-        indexes.select{|i| i.name != INDEX_E_NAME}.each do |index|
-           assert_equal :btree, index.using
-        end
-        assert_equal :gin, indexes.select{|i| i.name == INDEX_E_NAME}[0].using
-        assert_equal :desc, indexes.select{|i| i.name == INDEX_D_NAME}[0].orders[INDEX_D_COLUMN]
+        assert_equal 5, indexes.size
+
+        index_a, index_b, index_c, index_d, index_e = indexes
+
+        do_dump_index_assertions_for_one_index(index_a, INDEX_A_NAME, first_index_column_name)
+        do_dump_index_assertions_for_one_index(index_b, INDEX_B_NAME, second_index_column_name)
+        do_dump_index_assertions_for_one_index(index_d, INDEX_D_NAME, third_index_column_name)
+        do_dump_index_assertions_for_one_index(index_e, INDEX_E_NAME, fourth_index_column_name)
+
+        assert_equal :btree, index_a.using
+        assert_equal :btree, index_b.using
+        assert_equal :gin,   index_c.using
+        assert_equal :btree, index_d.using
+        assert_equal :gin,   index_e.using
+
+        assert_equal :desc,  index_d.orders[INDEX_D_COLUMN]
       end
     end
 
diff --git a/activerecord/test/cases/schema_dumper_test.rb b/activerecord/test/cases/schema_dumper_test.rb
index 7a304eab7f..f1927f561e 100644
--- a/activerecord/test/cases/schema_dumper_test.rb
+++ b/activerecord/test/cases/schema_dumper_test.rb
@@ -92,7 +92,7 @@ class SchemaDumperTest < ActiveRecord::TestCase
       next if column_set.empty?
 
       lengths = column_set.map do |column|
-        if match = column.match(/\bt\.\w+\s+"/)
+        if match = column.match(/\bt\.\w+\s+(?="\w+?")/)
           match[0].length
         end
       end.compact
@@ -279,6 +279,11 @@ class SchemaDumperTest < ActiveRecord::TestCase
       assert_match %r{t\.decimal\s+"decimal_array_default",\s+default: \["1.23", "3.45"\],\s+array: true}, output
     end
 
+    def test_schema_dump_expression_indices
+      index_definition = standard_dump.split(/\n/).grep(/t\.index.*company_expression_index/).first.strip
+      assert_equal 't.index "lower((name)::text)", name: "company_expression_index", using: :btree', index_definition
+    end
+
     if ActiveRecord::Base.connection.supports_extensions?
       def test_schema_dump_includes_extensions
         connection = ActiveRecord::Base.connection
diff --git a/activerecord/test/schema/schema.rb b/activerecord/test/schema/schema.rb
index 1027bcb365..628a59c2e3 100644
--- a/activerecord/test/schema/schema.rb
+++ b/activerecord/test/schema/schema.rb
@@ -199,6 +199,7 @@ ActiveRecord::Schema.define do
     t.index [:firm_id, :type, :rating], name: "company_index"
     t.index [:firm_id, :type], name: "company_partial_index", where: "rating > 10"
     t.index :name, name: 'company_name_index', using: :btree
+    t.index 'lower(name)', name: "company_expression_index" if supports_expression_index?
   end
 
   create_table :content, force: true do |t|