Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitedc2b77

Browse files
kamipojeremy
authored andcommitted
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' endFixes#19090.Fixes#21765.Fixes#21819.Fixes#24359.Signed-off-by: Jeremy Daer <jeremydaer@gmail.com>
1 parentc41ef01 commitedc2b77

File tree

10 files changed

+112
-38
lines changed

10 files changed

+112
-38
lines changed

‎activerecord/CHANGELOG.md‎

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1,3 +1,16 @@
1+
* PostgreSQL: Support Expression Indexes and Operator Classes.
2+
3+
Example:
4+
5+
create_table :users do |t|
6+
t.string :name
7+
t.index 'lower(name) varchar_pattern_ops'
8+
end
9+
10+
Fixes#19090,#21765,#21819,#24359.
11+
12+
*Ryuta Kamizono*
13+
114
* MySQL: Prepared statements support.
215

316
To enable, set`prepared_statements: true` in config/database.yml.

‎activerecord/lib/active_record/connection_adapters/abstract/schema_statements.rb‎

Lines changed: 17 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1110,15 +1110,19 @@ def update_table_definition(table_name, base) #:nodoc:
11101110
Table.new(table_name,base)
11111111
end
11121112

1113-
defadd_index_options(table_name,column_name,comment:nil, **options)#:nodoc:
1114-
column_names=Array(column_name)
1113+
defadd_index_options(table_name,column_name,comment:nil, **options)# :nodoc:
1114+
ifcolumn_name.is_a?(String) &&/\W/ ===column_name
1115+
column_names=column_name
1116+
else
1117+
column_names=Array(column_name)
1118+
end
11151119

11161120
options.assert_valid_keys(:unique,:order,:name,:where,:length,:internal,:using,:algorithm,:type)
11171121

11181122
index_type=options[:type].to_sifoptions.key?(:type)
11191123
index_type ||=options[:unique] ?"UNIQUE" :""
11201124
index_name=options[:name].to_sifoptions.key?(:name)
1121-
index_name ||=index_name(table_name,column:column_names)
1125+
index_name ||=index_name(table_name,index_name_options(column_names))
11221126
max_index_length=options.fetch(:internal,false) ?index_name_length :allowed_index_name_length
11231127

11241128
ifoptions.key?(:algorithm)
@@ -1174,6 +1178,8 @@ def add_index_sort_order(option_strings, column_names, options = {})
11741178

11751179
# Overridden by the MySQL adapter for supporting index lengths
11761180
defquoted_columns_for_index(column_names,options={})
1181+
return[column_names]ifcolumn_names.is_a?(String)
1182+
11771183
option_strings=Hash[column_names.map{|name|[name,'']}]
11781184

11791185
# add index sort order if supported
@@ -1249,6 +1255,14 @@ def create_alter_table(name)
12491255
AlterTable.newcreate_table_definition(name)
12501256
end
12511257

1258+
defindex_name_options(column_names)# :nodoc:
1259+
ifcolumn_names.is_a?(String)
1260+
column_names=column_names.scan(/\w+/).join('_')
1261+
end
1262+
1263+
{column:column_names}
1264+
end
1265+
12521266
defforeign_key_name(table_name,options)# :nodoc:
12531267
identifier="#{table_name}_#{options.fetch(:column)}_fk"
12541268
hashed_identifier=Digest::SHA256.hexdigest(identifier).first(10)

‎activerecord/lib/active_record/connection_adapters/abstract_adapter.rb‎

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -248,6 +248,11 @@ def supports_partial_index?
248248
false
249249
end
250250

251+
# Does this adapter support expression indices?
252+
defsupports_expression_index?
253+
false
254+
end
255+
251256
# Does this adapter support explain?
252257
defsupports_explain?
253258
false

‎activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb‎

Lines changed: 20 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -175,7 +175,10 @@ def indexes(table_name, name = nil)
175175

176176
result=query(<<-SQL,'SCHEMA')
177177
SELECT distinct i.relname, d.indisunique, d.indkey, pg_get_indexdef(d.indexrelid), t.oid,
178-
pg_catalog.obj_description(i.oid, 'pg_class') AS comment
178+
pg_catalog.obj_description(i.oid, 'pg_class') AS comment,
179+
(SELECT COUNT(*) FROM pg_opclass o
180+
JOIN (SELECT unnest(string_to_array(d.indclass::text, ' '))::int oid) c
181+
ON o.oid = c.oid WHERE o.opcdefault = 'f')
179182
FROM pg_class t
180183
INNER JOIN pg_index d ON t.oid = d.indrelid
181184
INNER JOIN pg_class i ON d.indexrelid = i.oid
@@ -194,25 +197,27 @@ def indexes(table_name, name = nil)
194197
inddef=row[3]
195198
oid=row[4]
196199
comment=row[5]
200+
opclass=row[6]
197201

198-
columns=Hash[query(<<-SQL,"SCHEMA")]
199-
SELECT a.attnum, a.attname
200-
FROM pg_attribute a
201-
WHERE a.attrelid =#{oid}
202-
AND a.attnum IN (#{indkey.join(",")})
203-
SQL
202+
using,expressions,where=inddef.scan(/ USING (\w+?)\((.+?)\)(?: WHERE (.+))?\z/).flatten
204203

205-
column_names=columns.values_at(*indkey).compact
204+
ifindkey.include?(0) ||opclass >0
205+
columns=expressions
206+
else
207+
columns=Hash[query(<<-SQL.strip_heredoc,"SCHEMA")].values_at(*indkey).compact
208+
SELECT a.attnum, a.attname
209+
FROM pg_attribute a
210+
WHERE a.attrelid =#{oid}
211+
AND a.attnum IN (#{indkey.join(",")})
212+
SQL
206213

207-
unlesscolumn_names.empty?
208214
# add info on sort order for columns (only desc order is explicitly specified, asc is the default)
209-
desc_order_columns=inddef.scan(/(\w+) DESC/).flatten
210-
orders=desc_order_columns.any? ?Hash[desc_order_columns.map{|order_column|[order_column,:desc]}] :{}
211-
where=inddef.scan(/WHERE (.+)$/).flatten[0]
212-
using=inddef.scan(/USING (.+?) /).flatten[0].to_sym
213-
214-
IndexDefinition.new(table_name,index_name,unique,column_names,[],orders,where,nil,using,comment)
215+
orders=Hash[
216+
expressions.scan(/(\w+) DESC/).flatten.map{ |order_column|[order_column,:desc]}
217+
]
215218
end
219+
220+
IndexDefinition.new(table_name,index_name,unique,columns,[],orders,where,nil,using.to_sym,comment)
216221
end.compact
217222
end
218223

‎activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -140,6 +140,10 @@ def supports_partial_index?
140140
true
141141
end
142142

143+
defsupports_expression_index?
144+
true
145+
end
146+
143147
defsupports_transaction_isolation?
144148
true
145149
end

‎activerecord/test/cases/adapters/postgresql/active_schema_test.rb‎

Lines changed: 13 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -28,7 +28,13 @@ def test_add_index
2828
ActiveRecord::ConnectionAdapters::PostgreSQLAdapter.send(:define_method,:index_name_exists?){ |*|false}
2929

3030
expected=%(CREATE UNIQUE INDEX "index_people_on_last_name" ON "people" ("last_name") WHERE state = 'active')
31-
assert_equalexpected,add_index(:people,:last_name,:unique=>true,:where=>"state = 'active'")
31+
assert_equalexpected,add_index(:people,:last_name,unique:true,where:"state = 'active'")
32+
33+
expected=%(CREATE UNIQUE INDEX "index_people_on_lower_last_name" ON "people" (lower(last_name)))
34+
assert_equalexpected,add_index(:people,'lower(last_name)',unique:true)
35+
36+
expected=%(CREATE UNIQUE INDEX "index_people_on_last_name_varchar_pattern_ops" ON "people" (last_name varchar_pattern_ops))
37+
assert_equalexpected,add_index(:people,'last_name varchar_pattern_ops',unique:true)
3238

3339
expected=%(CREATE INDEX CONCURRENTLY "index_people_on_last_name" ON "people" ("last_name"))
3440
assert_equalexpected,add_index(:people,:last_name,algorithm::concurrently)
@@ -39,16 +45,17 @@ def test_add_index
3945

4046
expected=%(CREATE INDEX CONCURRENTLY "index_people_on_last_name" ON "people" USING#{type} ("last_name"))
4147
assert_equalexpected,add_index(:people,:last_name,using:type,algorithm::concurrently)
48+
49+
expected=%(CREATE UNIQUE INDEX "index_people_on_last_name" ON "people" USING#{type} ("last_name") WHERE state = 'active')
50+
assert_equalexpected,add_index(:people,:last_name,using:type,unique:true,where:"state = 'active'")
51+
52+
expected=%(CREATE UNIQUE INDEX "index_people_on_lower_last_name" ON "people" USING#{type} (lower(last_name)))
53+
assert_equalexpected,add_index(:people,'lower(last_name)',using:type,unique:true)
4254
end
4355

4456
assert_raiseArgumentErrordo
4557
add_index(:people,:last_name,algorithm::copy)
4658
end
47-
expected=%(CREATE UNIQUE INDEX "index_people_on_last_name" ON "people" USING gist ("last_name"))
48-
assert_equalexpected,add_index(:people,:last_name,:unique=>true,:using=>:gist)
49-
50-
expected=%(CREATE UNIQUE INDEX "index_people_on_last_name" ON "people" USING gist ("last_name") WHERE state = 'active')
51-
assert_equalexpected,add_index(:people,:last_name,:unique=>true,:where=>"state = 'active'",:using=>:gist)
5259

5360
ActiveRecord::ConnectionAdapters::PostgreSQLAdapter.send:remove_method,:index_name_exists?
5461
end

‎activerecord/test/cases/adapters/postgresql/postgresql_adapter_test.rb‎

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -259,6 +259,22 @@ def test_partial_index
259259
end
260260
end
261261

262+
deftest_expression_index
263+
with_example_tabledo
264+
@connection.add_index'ex','mod(id, 10), abs(number)',name:'expression'
265+
index=@connection.indexes('ex').find{ |idx|idx.name =='expression'}
266+
assert_equal'mod(id, 10), abs(number)',index.columns
267+
end
268+
end
269+
270+
deftest_index_with_opclass
271+
with_example_tabledo
272+
@connection.add_index'ex','data varchar_pattern_ops',name:'with_opclass'
273+
index=@connection.indexes('ex').find{ |idx|idx.name =='with_opclass'}
274+
assert_equal'data varchar_pattern_ops',index.columns
275+
end
276+
end
277+
262278
deftest_columns_for_distinct_zero_orders
263279
assert_equal"posts.id",
264280
@connection.columns_for_distinct("posts.id",[])

‎activerecord/test/cases/adapters/postgresql/schema_test.rb‎

Lines changed: 17 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -325,7 +325,7 @@ def test_dump_indexes_for_schema_multiple_schemas_in_search_path
325325

326326
deftest_dump_indexes_for_table_with_scheme_specified_in_name
327327
indexes=@connection.indexes("#{SCHEMA_NAME}.#{TABLE_NAME}")
328-
assert_equal4,indexes.size
328+
assert_equal5,indexes.size
329329
end
330330

331331
deftest_with_uppercase_index_name
@@ -449,18 +449,22 @@ def columns(table_name)
449449
defdo_dump_index_tests_for_schema(this_schema_name,first_index_column_name,second_index_column_name,third_index_column_name,fourth_index_column_name)
450450
with_schema_search_path(this_schema_name)do
451451
indexes=@connection.indexes(TABLE_NAME).sort_by(&:name)
452-
assert_equal4,indexes.size
453-
454-
do_dump_index_assertions_for_one_index(indexes[0],INDEX_A_NAME,first_index_column_name)
455-
do_dump_index_assertions_for_one_index(indexes[1],INDEX_B_NAME,second_index_column_name)
456-
do_dump_index_assertions_for_one_index(indexes[2],INDEX_D_NAME,third_index_column_name)
457-
do_dump_index_assertions_for_one_index(indexes[3],INDEX_E_NAME,fourth_index_column_name)
458-
459-
indexes.select{|i|i.name !=INDEX_E_NAME}.eachdo |index|
460-
assert_equal:btree,index.using
461-
end
462-
assert_equal:gin,indexes.select{|i|i.name ==INDEX_E_NAME}[0].using
463-
assert_equal:desc,indexes.select{|i|i.name ==INDEX_D_NAME}[0].orders[INDEX_D_COLUMN]
452+
assert_equal5,indexes.size
453+
454+
index_a,index_b,index_c,index_d,index_e=indexes
455+
456+
do_dump_index_assertions_for_one_index(index_a,INDEX_A_NAME,first_index_column_name)
457+
do_dump_index_assertions_for_one_index(index_b,INDEX_B_NAME,second_index_column_name)
458+
do_dump_index_assertions_for_one_index(index_d,INDEX_D_NAME,third_index_column_name)
459+
do_dump_index_assertions_for_one_index(index_e,INDEX_E_NAME,fourth_index_column_name)
460+
461+
assert_equal:btree,index_a.using
462+
assert_equal:btree,index_b.using
463+
assert_equal:gin,index_c.using
464+
assert_equal:btree,index_d.using
465+
assert_equal:gin,index_e.using
466+
467+
assert_equal:desc,index_d.orders[INDEX_D_COLUMN]
464468
end
465469
end
466470

‎activerecord/test/cases/schema_dumper_test.rb‎

Lines changed: 6 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -92,7 +92,7 @@ def test_types_line_up
9292
nextifcolumn_set.empty?
9393

9494
lengths=column_set.mapdo |column|
95-
ifmatch=column.match(/\bt\.\w+\s+"/)
95+
ifmatch=column.match(/\bt\.\w+\s+(?="\w+?")/)
9696
match[0].length
9797
end
9898
end.compact
@@ -279,6 +279,11 @@ def test_schema_dump_allows_array_of_decimal_defaults
279279
assert_match%r{t\.decimal\s+"decimal_array_default",\s+default:\["1.23", "3.45"\],\s+array: true},output
280280
end
281281

282+
deftest_schema_dump_expression_indices
283+
index_definition=standard_dump.split(/\n/).grep(/t\.index.*company_expression_index/).first.strip
284+
assert_equal't.index "lower((name)::text)", name: "company_expression_index", using: :btree',index_definition
285+
end
286+
282287
ifActiveRecord::Base.connection.supports_extensions?
283288
deftest_schema_dump_includes_extensions
284289
connection=ActiveRecord::Base.connection

‎activerecord/test/schema/schema.rb‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -199,6 +199,7 @@
199199
t.index[:firm_id,:type,:rating],name:"company_index"
200200
t.index[:firm_id,:type],name:"company_partial_index",where:"rating > 10"
201201
t.index:name,name:'company_name_index',using::btree
202+
t.index'lower(name)',name:"company_expression_index"ifsupports_expression_index?
202203
end
203204

204205
create_table:content,force:truedo |t|

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp