From 1dca75c2c8f930b58d86cd2216af5e14307b3e53 Mon Sep 17 00:00:00 2001 From: Greg Navis Date: Sat, 13 May 2017 03:09:58 +0200 Subject: Add support for PostgreSQL operator classes to add_index Add support for specifying non-default operator classes in PostgreSQL indexes. An example CREATE INDEX query that becomes possible is: CREATE INDEX users_name ON users USING gist (name gist_trgm_ops); Previously it was possible to specify the `gist` index but not the custom operator class. The `add_index` call for the above query is: add_index :users, :name, using: :gist, opclasses: {name: :gist_trgm_ops} --- .../abstract/schema_definitions.rb | 4 ++- .../abstract/schema_statements.rb | 28 +++++++++++++++++-- .../postgresql/schema_statements.rb | 32 ++++++++++++++-------- .../connection_adapters/postgresql_adapter.rb | 17 ++++++++++++ activerecord/lib/active_record/schema_dumper.rb | 1 + 5 files changed, 67 insertions(+), 15 deletions(-) (limited to 'activerecord/lib') 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 be2f625d74..b66009bdc6 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 @@ module ActiveRecord # this type are typically created and returned by methods in database # adapters. e.g. ActiveRecord::ConnectionAdapters::MySQL::SchemaStatements#indexes class IndexDefinition # :nodoc: - attr_reader :table, :name, :unique, :columns, :lengths, :orders, :where, :type, :using, :comment + attr_reader :table, :name, :unique, :columns, :lengths, :orders, :where, :type, :using, :opclass, :comment def initialize( table, name, @@ -17,6 +17,7 @@ module ActiveRecord where: nil, type: nil, using: nil, + opclass: {}, comment: nil ) @table = table @@ -28,6 +29,7 @@ module ActiveRecord @where = where @type = type @using = using + @opclass = opclass @comment = comment end end 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 9b7345f7c3..2a335e8f2b 100644 --- a/activerecord/lib/active_record/connection_adapters/abstract/schema_statements.rb +++ b/activerecord/lib/active_record/connection_adapters/abstract/schema_statements.rb @@ -738,6 +738,29 @@ module ActiveRecord # # Note: only supported by PostgreSQL and MySQL # + # ====== Creating an index with a specific operator class + # + # add_index(:developers, :name, using: 'gist', opclass: :gist_trgm_ops) + # + # generates: + # + # CREATE INDEX developers_on_name ON developers USING gist (name gist_trgm_ops) -- PostgreSQL + # + # add_index(:developers, [:name, :city], using: 'gist', opclass: { city: :gist_trgm_ops }) + # + # generates: + # + # CREATE INDEX developers_on_name_and_city ON developers USING gist (name, city gist_trgm_ops) -- PostgreSQL + # + # add_index(:developers, [:name, :city], using: 'gist', opclass: :gist_trgm_ops }) + # + # generates: + # + # CREATE INDEX developers_on_name_and_city ON developers USING gist (name gist_trgm_ops, city gist_trgm_ops) -- PostgreSQL + # + # + # Note: only supported by PostgreSQL + # # ====== Creating an index with a specific type # # add_index(:developers, :name, type: :fulltext) @@ -1120,7 +1143,7 @@ module ActiveRecord def add_index_options(table_name, column_name, comment: nil, **options) # :nodoc: column_names = index_column_names(column_name) - options.assert_valid_keys(:unique, :order, :name, :where, :length, :internal, :using, :algorithm, :type) + options.assert_valid_keys(:unique, :order, :name, :where, :length, :internal, :using, :algorithm, :type, :opclass) index_type = options[:type].to_s if options.key?(:type) index_type ||= options[:unique] ? "UNIQUE" : "" @@ -1186,7 +1209,8 @@ module ActiveRecord quoted_columns end - # Overridden by the MySQL adapter for supporting index lengths + # Overridden by the MySQL adapter for supporting index lengths and by + # the PostgreSQL adapter for supporting operator classes. def add_options_for_index_columns(quoted_columns, **options) if supports_index_sort_order? quoted_columns = add_index_sort_order(quoted_columns, options) 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 846e721983..15a1dfd102 100644 --- a/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb +++ b/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb @@ -87,10 +87,7 @@ 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, - (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') + pg_catalog.obj_description(i.oid, 'pg_class') AS comment FROM pg_class t INNER JOIN pg_index d ON t.oid = d.indrelid INNER JOIN pg_class i ON d.indexrelid = i.oid @@ -109,11 +106,10 @@ module ActiveRecord inddef = row[3] oid = row[4] comment = row[5] - opclass = row[6] using, expressions, where = inddef.scan(/ USING (\w+?) \((.+?)\)(?: WHERE (.+))?\z/).flatten - if indkey.include?(0) || opclass > 0 + if indkey.include?(0) columns = expressions else columns = Hash[query(<<-SQL.strip_heredoc, "SCHEMA")].values_at(*indkey).compact @@ -123,10 +119,21 @@ module ActiveRecord AND a.attnum IN (#{indkey.join(",")}) SQL - # add info on sort order for columns (only desc order is explicitly specified, asc is the default) - orders = Hash[ - expressions.scan(/(\w+) DESC/).flatten.map { |order_column| [order_column, :desc] } - ] + orders = {} + opclasses = {} + + # add info on sort order (only desc order is explicitly specified, asc is the default) + # and non-default opclasses + expressions.scan(/(\w+)(?: (?!DESC)(\w+))?(?: (DESC))?/).each do |column, opclass, desc| + opclasses[column] = opclass if opclass + orders[column] = :desc if desc + end + + # Use a string for the opclass description (instead of a hash) when all columns + # have the same opclass specified. + if columns.count == opclasses.count && opclasses.values.uniq.count == 1 + opclasses = opclasses.values.first + end end IndexDefinition.new( @@ -137,6 +144,7 @@ module ActiveRecord orders: orders, where: where, using: using.to_sym, + opclass: opclasses, comment: comment.presence ) end @@ -458,8 +466,8 @@ module ActiveRecord end def add_index(table_name, column_name, options = {}) #:nodoc: - index_name, index_type, index_columns, index_options, index_algorithm, index_using, comment = add_index_options(table_name, column_name, options) - execute("CREATE #{index_type} INDEX #{index_algorithm} #{quote_column_name(index_name)} ON #{quote_table_name(table_name)} #{index_using} (#{index_columns})#{index_options}").tap do + index_name, index_type, index_columns_and_opclasses, index_options, index_algorithm, index_using, comment = add_index_options(table_name, column_name, options) + execute("CREATE #{index_type} INDEX #{index_algorithm} #{quote_column_name(index_name)} ON #{quote_table_name(table_name)} #{index_using} (#{index_columns_and_opclasses})#{index_options}").tap do execute "COMMENT ON INDEX #{quote_column_name(index_name)} IS #{quote(comment)}" if comment end end diff --git a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb index 7b27f6b7a0..eee61780b7 100644 --- a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb +++ b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb @@ -388,6 +388,23 @@ module ActiveRecord private + def add_index_opclass(column_names, options = {}) + opclass = + if options[:opclass].is_a?(Hash) + options[:opclass].symbolize_keys + else + Hash.new { |hash, column| hash[column] = options[:opclass].to_s } + end + column_names.each do |name, column| + column << " #{opclass[name]}" if opclass[name].present? + end + end + + def add_options_for_index_columns(quoted_columns, **options) + quoted_columns = add_index_opclass(quoted_columns, options) + super + end + # See https://www.postgresql.org/docs/current/static/errcodes-appendix.html VALUE_LIMIT_VIOLATION = "22001" NUMERIC_VALUE_OUT_OF_RANGE = "22003" diff --git a/activerecord/lib/active_record/schema_dumper.rb b/activerecord/lib/active_record/schema_dumper.rb index 66f7d29886..8cb2851557 100644 --- a/activerecord/lib/active_record/schema_dumper.rb +++ b/activerecord/lib/active_record/schema_dumper.rb @@ -189,6 +189,7 @@ HEADER index_parts << "where: #{index.where.inspect}" if index.where index_parts << "using: #{index.using.inspect}" if !@connection.default_index_type?(index) index_parts << "type: #{index.type.inspect}" if index.type + index_parts << "opclass: #{index.opclass.inspect}" if index.opclass.present? index_parts << "comment: #{index.comment.inspect}" if index.comment index_parts end -- cgit v1.2.3