diff options
10 files changed, 112 insertions, 38 deletions
diff --git a/activerecord/CHANGELOG.md b/activerecord/CHANGELOG.md index 2de1fc9253..e76b0973ca 100644 --- a/activerecord/CHANGELOG.md +++ b/activerecord/CHANGELOG.md @@ -1,3 +1,16 @@ +* PostgreSQL: Support Expression Indexes and Operator Classes. + + Example: + + create_table :users do |t| + t.string :name + t.index 'lower(name) varchar_pattern_ops' + end + + Fixes #19090, #21765, #21819, #24359. + + *Ryuta Kamizono* + * MySQL: Prepared statements support. To enable, set `prepared_statements: true` in config/database.yml. 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 f65dc4ed5a..99a3e99bdc 100644 --- a/activerecord/lib/active_record/connection_adapters/abstract/schema_statements.rb +++ b/activerecord/lib/active_record/connection_adapters/abstract/schema_statements.rb @@ -1110,15 +1110,19 @@ module ActiveRecord Table.new(table_name, base) end - def add_index_options(table_name, column_name, comment: nil, **options) #:nodoc: - column_names = Array(column_name) + def add_index_options(table_name, column_name, comment: nil, **options) # :nodoc: + if column_name.is_a?(String) && /\W/ === column_name + column_names = column_name + else + column_names = Array(column_name) + end options.assert_valid_keys(:unique, :order, :name, :where, :length, :internal, :using, :algorithm, :type) index_type = options[:type].to_s if options.key?(:type) index_type ||= options[:unique] ? "UNIQUE" : "" index_name = options[:name].to_s if options.key?(:name) - index_name ||= index_name(table_name, column: column_names) + index_name ||= index_name(table_name, index_name_options(column_names)) max_index_length = options.fetch(:internal, false) ? index_name_length : allowed_index_name_length if options.key?(:algorithm) @@ -1174,6 +1178,8 @@ module ActiveRecord # Overridden by the MySQL adapter for supporting index lengths def quoted_columns_for_index(column_names, options = {}) + return [column_names] if column_names.is_a?(String) + option_strings = Hash[column_names.map {|name| [name, '']}] # add index sort order if supported @@ -1249,6 +1255,14 @@ module ActiveRecord AlterTable.new create_table_definition(name) end + def index_name_options(column_names) # :nodoc: + if column_names.is_a?(String) + column_names = column_names.scan(/\w+/).join('_') + end + + { column: column_names } + end + def foreign_key_name(table_name, options) # :nodoc: identifier = "#{table_name}_#{options.fetch(:column)}_fk" hashed_identifier = Digest::SHA256.hexdigest(identifier).first(10) diff --git a/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb b/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb index 130c03dd1d..86de8f1ca0 100644 --- a/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb +++ b/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb @@ -248,6 +248,11 @@ module ActiveRecord false end + # Does this adapter support expression indices? + def supports_expression_index? + false + end + # Does this adapter support explain? def supports_explain? false 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 272e6293ab..074ebb90a5 100644 --- a/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb +++ b/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb @@ -175,7 +175,10 @@ module ActiveRecord result = query(<<-SQL, 'SCHEMA') SELECT distinct i.relname, d.indisunique, d.indkey, pg_get_indexdef(d.indexrelid), t.oid, - pg_catalog.obj_description(i.oid, 'pg_class') AS comment + pg_catalog.obj_description(i.oid, 'pg_class') AS comment, + (SELECT COUNT(*) FROM pg_opclass o + JOIN (SELECT unnest(string_to_array(d.indclass::text, ' '))::int oid) c + ON o.oid = c.oid WHERE o.opcdefault = 'f') FROM pg_class t INNER JOIN pg_index d ON t.oid = d.indrelid INNER JOIN pg_class i ON d.indexrelid = i.oid @@ -194,25 +197,27 @@ module ActiveRecord inddef = row[3] oid = row[4] comment = row[5] + opclass = row[6] - columns = Hash[query(<<-SQL, "SCHEMA")] - SELECT a.attnum, a.attname - FROM pg_attribute a - WHERE a.attrelid = #{oid} - AND a.attnum IN (#{indkey.join(",")}) - SQL + using, expressions, where = inddef.scan(/ USING (\w+?) \((.+?)\)(?: WHERE (.+))?\z/).flatten - column_names = columns.values_at(*indkey).compact + if indkey.include?(0) || opclass > 0 + columns = expressions + else + columns = Hash[query(<<-SQL.strip_heredoc, "SCHEMA")].values_at(*indkey).compact + SELECT a.attnum, a.attname + FROM pg_attribute a + WHERE a.attrelid = #{oid} + AND a.attnum IN (#{indkey.join(",")}) + SQL - unless column_names.empty? # 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]}] : {} - where = inddef.scan(/WHERE (.+)$/).flatten[0] - using = inddef.scan(/USING (.+?) /).flatten[0].to_sym - - IndexDefinition.new(table_name, index_name, unique, column_names, [], orders, where, nil, using, comment) + orders = Hash[ + expressions.scan(/(\w+) DESC/).flatten.map { |order_column| [order_column, :desc] } + ] end + + IndexDefinition.new(table_name, index_name, unique, columns, [], orders, where, nil, using.to_sym, comment) end.compact end diff --git a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb index e397a28cac..39a2cbbda7 100644 --- a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb +++ b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb @@ -140,6 +140,10 @@ module ActiveRecord true end + def supports_expression_index? + true + end + def supports_transaction_isolation? true end 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| |