1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
|
# encoding: utf-8
require "cases/helper"
module ActiveRecord
module ConnectionAdapters
class PostgreSQLAdapterTest < ActiveRecord::TestCase
def setup
@connection = ActiveRecord::Base.connection
@connection.exec_query('drop table if exists ex')
@connection.exec_query('create table ex(id serial primary key, number integer, data character varying(255))')
end
def test_valid_column
column = @connection.columns('ex').find { |col| col.name == 'id' }
assert @connection.valid_type?(column.type)
end
def test_invalid_column
assert_not @connection.valid_type?(:foobar)
end
def test_primary_key
assert_equal 'id', @connection.primary_key('ex')
end
def test_primary_key_works_tables_containing_capital_letters
assert_equal 'id', @connection.primary_key('CamelCase')
end
def test_non_standard_primary_key
@connection.exec_query('drop table if exists ex')
@connection.exec_query('create table ex(data character varying(255) primary key)')
assert_equal 'data', @connection.primary_key('ex')
end
def test_primary_key_returns_nil_for_no_pk
@connection.exec_query('drop table if exists ex')
@connection.exec_query('create table ex(id integer)')
assert_nil @connection.primary_key('ex')
end
def test_primary_key_raises_error_if_table_not_found
assert_raises(ActiveRecord::StatementInvalid) do
@connection.primary_key('unobtainium')
end
end
def test_insert_sql_with_proprietary_returning_clause
id = @connection.insert_sql("insert into ex (number) values(5150)", nil, "number")
assert_equal "5150", id
end
def test_insert_sql_with_quoted_schema_and_table_name
id = @connection.insert_sql('insert into "public"."ex" (number) values(5150)')
expect = @connection.query('select max(id) from ex').first.first
assert_equal expect, id
end
def test_insert_sql_with_no_space_after_table_name
id = @connection.insert_sql("insert into ex(number) values(5150)")
expect = @connection.query('select max(id) from ex').first.first
assert_equal expect, id
end
def test_insert_sql_with_returning_disabled
connection = connection_without_insert_returning
id = connection.insert_sql("insert into postgresql_partitioned_table_parent (number) VALUES (1)")
expect = connection.query('select max(id) from postgresql_partitioned_table_parent').first.first
assert_equal expect, id
end
def test_exec_insert_with_returning_disabled
connection = connection_without_insert_returning
result = connection.exec_insert("insert into postgresql_partitioned_table_parent (number) VALUES (1)", nil, [], 'id', 'postgresql_partitioned_table_parent_id_seq')
expect = connection.query('select max(id) from postgresql_partitioned_table_parent').first.first
assert_equal expect, result.rows.first.first
end
def test_exec_insert_with_returning_disabled_and_no_sequence_name_given
connection = connection_without_insert_returning
result = connection.exec_insert("insert into postgresql_partitioned_table_parent (number) VALUES (1)", nil, [], 'id')
expect = connection.query('select max(id) from postgresql_partitioned_table_parent').first.first
assert_equal expect, result.rows.first.first
end
def test_sql_for_insert_with_returning_disabled
connection = connection_without_insert_returning
result = connection.sql_for_insert('sql', nil, nil, nil, 'binds')
assert_equal ['sql', 'binds'], result
end
def test_serial_sequence
assert_equal 'public.accounts_id_seq',
@connection.serial_sequence('accounts', 'id')
assert_raises(ActiveRecord::StatementInvalid) do
@connection.serial_sequence('zomg', 'id')
end
end
def test_default_sequence_name
assert_equal 'accounts_id_seq',
@connection.default_sequence_name('accounts', 'id')
assert_equal 'accounts_id_seq',
@connection.default_sequence_name('accounts')
end
def test_default_sequence_name_bad_table
assert_equal 'zomg_id_seq',
@connection.default_sequence_name('zomg', 'id')
assert_equal 'zomg_id_seq',
@connection.default_sequence_name('zomg')
end
def test_pk_and_sequence_for
pk, seq = @connection.pk_and_sequence_for('ex')
assert_equal 'id', pk
assert_equal @connection.default_sequence_name('ex', 'id'), seq
end
def test_pk_and_sequence_for_with_non_standard_primary_key
@connection.exec_query('drop table if exists ex')
@connection.exec_query('create table ex(code serial primary key)')
pk, seq = @connection.pk_and_sequence_for('ex')
assert_equal 'code', pk
assert_equal @connection.default_sequence_name('ex', 'code'), seq
end
def test_pk_and_sequence_for_returns_nil_if_no_seq
@connection.exec_query('drop table if exists ex')
@connection.exec_query('create table ex(id integer primary key)')
assert_nil @connection.pk_and_sequence_for('ex')
end
def test_pk_and_sequence_for_returns_nil_if_no_pk
@connection.exec_query('drop table if exists ex')
@connection.exec_query('create table ex(id integer)')
assert_nil @connection.pk_and_sequence_for('ex')
end
def test_pk_and_sequence_for_returns_nil_if_table_not_found
assert_nil @connection.pk_and_sequence_for('unobtainium')
end
def test_exec_insert_number
insert(@connection, 'number' => 10)
result = @connection.exec_query('SELECT number FROM ex WHERE number = 10')
assert_equal 1, result.rows.length
assert_equal "10", result.rows.last.last
end
def test_exec_insert_string
str = 'いただきます!'
insert(@connection, 'number' => 10, 'data' => str)
result = @connection.exec_query('SELECT number, data FROM ex WHERE number = 10')
value = result.rows.last.last
assert_equal str, value
end
def test_table_alias_length
assert_nothing_raised do
@connection.table_alias_length
end
end
def test_exec_no_binds
result = @connection.exec_query('SELECT id, data FROM ex')
assert_equal 0, result.rows.length
assert_equal 2, result.columns.length
assert_equal %w{ id data }, result.columns
string = @connection.quote('foo')
@connection.exec_query("INSERT INTO ex (id, data) VALUES (1, #{string})")
result = @connection.exec_query('SELECT id, data FROM ex')
assert_equal 1, result.rows.length
assert_equal 2, result.columns.length
assert_equal [['1', 'foo']], result.rows
end
def test_exec_with_binds
string = @connection.quote('foo')
@connection.exec_query("INSERT INTO ex (id, data) VALUES (1, #{string})")
result = @connection.exec_query(
'SELECT id, data FROM ex WHERE id = $1', nil, [[nil, 1]])
assert_equal 1, result.rows.length
assert_equal 2, result.columns.length
assert_equal [['1', 'foo']], result.rows
end
def test_exec_typecasts_bind_vals
string = @connection.quote('foo')
@connection.exec_query("INSERT INTO ex (id, data) VALUES (1, #{string})")
column = @connection.columns('ex').find { |col| col.name == 'id' }
result = @connection.exec_query(
'SELECT id, data FROM ex WHERE id = $1', nil, [[column, '1-fuu']])
assert_equal 1, result.rows.length
assert_equal 2, result.columns.length
assert_equal [['1', 'foo']], result.rows
end
def test_substitute_at
bind = @connection.substitute_at(nil, 0)
assert_equal Arel.sql('$1'), bind
bind = @connection.substitute_at(nil, 1)
assert_equal Arel.sql('$2'), bind
end
def test_partial_index
@connection.add_index 'ex', %w{ id number }, :name => 'partial', :where => "number > 100"
index = @connection.indexes('ex').find { |idx| idx.name == 'partial' }
assert_equal "(number > 100)", index.where
end
def test_distinct_zero_orders
assert_equal "DISTINCT posts.id",
@connection.distinct("posts.id", [])
end
def test_columns_for_distinct_zero_orders
assert_equal "posts.id",
@connection.columns_for_distinct("posts.id", [])
end
def test_distinct_one_order
assert_equal "DISTINCT posts.id, posts.created_at AS alias_0",
@connection.distinct("posts.id", ["posts.created_at desc"])
end
def test_columns_for_distinct_one_order
assert_equal "posts.id, posts.created_at AS alias_0",
@connection.columns_for_distinct("posts.id", ["posts.created_at desc"])
end
def test_distinct_few_orders
assert_equal "DISTINCT posts.id, posts.created_at AS alias_0, posts.position AS alias_1",
@connection.distinct("posts.id", ["posts.created_at desc", "posts.position asc"])
end
def test_columns_for_distinct_few_orders
assert_equal "posts.id, posts.created_at AS alias_0, posts.position AS alias_1",
@connection.columns_for_distinct("posts.id", ["posts.created_at desc", "posts.position asc"])
end
def test_distinct_blank_not_nil_orders
assert_equal "DISTINCT posts.id, posts.created_at AS alias_0",
@connection.distinct("posts.id", ["posts.created_at desc", "", " "])
end
def test_columns_for_distinct_blank_not_nil_orders
assert_equal "posts.id, posts.created_at AS alias_0",
@connection.columns_for_distinct("posts.id", ["posts.created_at desc", "", " "])
end
def test_distinct_with_arel_order
order = Object.new
def order.to_sql
"posts.created_at desc"
end
assert_equal "DISTINCT posts.id, posts.created_at AS alias_0",
@connection.distinct("posts.id", [order])
end
def test_columns_for_distinct_with_arel_order
order = Object.new
def order.to_sql
"posts.created_at desc"
end
assert_equal "posts.id, posts.created_at AS alias_0",
@connection.columns_for_distinct("posts.id", [order])
end
def test_distinct_with_nulls
assert_equal "DISTINCT posts.title, posts.updater_id AS alias_0", @connection.distinct("posts.title", ["posts.updater_id desc nulls first"])
assert_equal "DISTINCT posts.title, posts.updater_id AS alias_0", @connection.distinct("posts.title", ["posts.updater_id desc nulls last"])
end
def test_columns_for_distinct_with_nulls
assert_equal "posts.title, posts.updater_id AS alias_0", @connection.columns_for_distinct("posts.title", ["posts.updater_id desc nulls first"])
assert_equal "posts.title, posts.updater_id AS alias_0", @connection.columns_for_distinct("posts.title", ["posts.updater_id desc nulls last"])
end
def test_raise_error_when_cannot_translate_exception
assert_raise TypeError do
@connection.send(:log, nil) { @connection.execute(nil) }
end
end
private
def insert(ctx, data)
binds = data.map { |name, value|
[ctx.columns('ex').find { |x| x.name == name }, value]
}
columns = binds.map(&:first).map(&:name)
bind_subs = columns.length.times.map { |x| "$#{x + 1}" }
sql = "INSERT INTO ex (#{columns.join(", ")})
VALUES (#{bind_subs.join(', ')})"
ctx.exec_insert(sql, 'SQL', binds)
end
def connection_without_insert_returning
ActiveRecord::Base.postgresql_connection(ActiveRecord::Base.configurations['arunit'].merge(:insert_returning => false))
end
end
end
end
|