-
Notifications
You must be signed in to change notification settings - Fork 1.1k
/
postgres_spec.rb
6032 lines (5274 loc) · 247 KB
/
postgres_spec.rb
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
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
SEQUEL_ADAPTER_TEST = :postgres
require_relative 'spec_helper'
uses_pg = Sequel::Postgres::USES_PG if DB.adapter_scheme == :postgres
uses_pg_or_jdbc = uses_pg || DB.adapter_scheme == :jdbc
Sequel.extension :pg_extended_date_support
DB.extension :pg_array, :pg_range, :pg_row, :pg_inet, :pg_json, :pg_enum
begin
DB.extension :pg_interval
rescue LoadError
end
DB.extension :pg_hstore if DB.type_supported?('hstore')
DB.extension :pg_multirange if DB.server_version >= 140000
if uses_pg && ENV['SEQUEL_PG_AUTO_PARAMETERIZE']
if ENV['SEQUEL_PG_AUTO_PARAMETERIZE'] = 'in_array_string'
DB.extension :pg_auto_parameterize_in_array
DB.opts[:treat_string_list_as_text_array] = 't'
elsif ENV['SEQUEL_PG_AUTO_PARAMETERIZE'] = 'in_array'
DB.extension :pg_auto_parameterize_in_array
else
DB.extension :pg_auto_parameterize
end
end
describe 'PostgreSQL adapter' do
before do
@db = DB
@db.disconnect
end
after do
@db.disconnect
end
it "should handle case where status raises PGError" do
proc do
@db.synchronize do |c|
def c.status; raise Sequel::Postgres::PGError end
def c.execute_query(*); raise Sequel::Postgres::PGError end
c.execute('SELECT 1')
end
end.must_raise Sequel::DatabaseDisconnectError
end
it "should handle case where execute_query returns nil" do
@db.synchronize do |c|
def c.execute_query(*); super; nil end
c.execute('SELECT 1 AS v'){|v| v.must_be_nil}
end
end
it "should handle prepared statement case where exec_prepared returns nil" do
@db.synchronize do |c|
def c.exec_prepared(*); super; nil end
@db['SELECT 1 AS v'].prepare(:all, :test_prepared)
@db.execute(:test_prepared){|v| v.must_be_nil}
end
end
it "should handle prepared statement case where same variable is used more than once" do
@db['SELECT (?::integer + ?::integer) AS v', :$v, :$v].prepare(:single_value, :test_prepared).call(:v=>2).must_equal 4
end
end if uses_pg
describe 'A PostgreSQL database' do
before do
@db = DB
end
after do
@db.drop_table?(:test)
@db.timezone = nil
Sequel.datetime_class = Time
end
it "should return true for table_exists? if table exists but is locked" do
begin
@db.create_table(:test){Integer :id}
t = []
@db[:test].lock('ACCESS EXCLUSIVE') do
Thread.new do
@db.run "SET lock_timeout = 1"
t << @db.table_exists?(:test)
end.join
Thread.new do
@db.run "SET statement_timeout = 1"
t << @db.table_exists?(:test)
end.join
end
t.must_equal [true, true]
ensure
# Must disconnect to ensure connections with lock/statement timeout are not left in the pool
@db.disconnect
end
end if DB.server_version >= 90300
it "should be able to handle various types of IN/NOT IN queries" do
ds = @db.select(1)
ds.where(2=>[2, 3]).wont_be_empty
ds.where(4=>[2, 3]).must_be_empty
ds.exclude(4=>[2, 3]).wont_be_empty
ds.exclude(2=>[2, 4]).must_be_empty
ds.where('2'=>%w[2 3]).wont_be_empty
ds.where('4'=>%w[2 3]).must_be_empty
ds.exclude('4'=>%w[2 3]).wont_be_empty
ds.exclude('2'=>%w[2 3]).must_be_empty
ds.where(2=>[2, 3].map{|i| BigDecimal(i)}).wont_be_empty
ds.where(4=>[2, 3].map{|i| BigDecimal(i)}).must_be_empty
ds.exclude(4=>[2, 3].map{|i| BigDecimal(i)}).wont_be_empty
ds.exclude(2=>[2, 3].map{|i| BigDecimal(i)}).must_be_empty
ds.where(Date.new(2021, 2)=>[2, 3].map{|i| Date.new(2021, i)}).wont_be_empty
ds.where(Date.new(2021, 4)=>[2, 3].map{|i| Date.new(2021, i)}).must_be_empty
ds.exclude(Date.new(2021, 4)=>[2, 3].map{|i| Date.new(2021, i)}).wont_be_empty
ds.exclude(Date.new(2021, 2)=>[2, 3].map{|i| Date.new(2021, i)}).must_be_empty
ds.where(DateTime.new(2021, 2)=>[2, 3].map{|i| DateTime.new(2021, i)}).wont_be_empty
ds.where(DateTime.new(2021, 4)=>[2, 3].map{|i| DateTime.new(2021, i)}).must_be_empty
ds.exclude(DateTime.new(2021, 4)=>[2, 3].map{|i| DateTime.new(2021, i)}).wont_be_empty
ds.exclude(DateTime.new(2021, 2)=>[2, 3].map{|i| DateTime.new(2021, i)}).must_be_empty
ds.where(Time.local(2021, 2)=>[2, 3].map{|i| Time.local(2021, i)}).wont_be_empty
ds.where(Time.local(2021, 4)=>[2, 3].map{|i| Time.local(2021, i)}).must_be_empty
ds.exclude(Time.local(2021, 4)=>[2, 3].map{|i| Time.local(2021, i)}).wont_be_empty
ds.exclude(Time.local(2021, 2)=>[2, 3].map{|i| Time.local(2021, i)}).must_be_empty
ds.where(Sequel::SQLTime.create(2, 0, 0)=>[2, 3].map{|i| Sequel::SQLTime.create(i, 0, 0)}).wont_be_empty
ds.where(Sequel::SQLTime.create(4, 0, 0)=>[2, 3].map{|i| Sequel::SQLTime.create(i, 0, 0)}).must_be_empty
ds.exclude(Sequel::SQLTime.create(4, 0, 0)=>[2, 3].map{|i| Sequel::SQLTime.create(i, 0, 0)}).wont_be_empty
ds.exclude(Sequel::SQLTime.create(2, 0, 0)=>[2, 3].map{|i| Sequel::SQLTime.create(i, 0, 0)}).must_be_empty
ds.where(2=>[2, 3].map{|i| Float(i)}).wont_be_empty
ds.where(4=>[2, 3].map{|i| Float(i)}).must_be_empty
ds.exclude(4=>[2, 3].map{|i| Float(i)}).wont_be_empty
ds.exclude(2=>[2, 3].map{|i| Float(i)}).must_be_empty
if @db.server_version >= 140000
ds.where(2=>[2.0, 3.0, 1.0/0.0, -1.0/0.0, 0.0/0.0]).wont_be_empty
ds.where(4=>[2.0, 3.0, 1.0/0.0, -1.0/0.0, 0.0/0.0]).must_be_empty
ds.exclude(4=>[2.0, 3.0, 1.0/0.0, -1.0/0.0, 0.0/0.0]).wont_be_empty
ds.exclude(2=>[2.0, 3.0, 1.0/0.0, -1.0/0.0, 0.0/0.0]).must_be_empty
end
ds.where(Sequel.blob('2')=>%w[2 3].map{|i| Sequel.blob(i)}).wont_be_empty
ds.where(Sequel.blob('4')=>%w[2 3].map{|i| Sequel.blob(i)}).must_be_empty
ds.exclude(Sequel.blob('4')=>%w[2 3].map{|i| Sequel.blob(i)}).wont_be_empty
ds.exclude(Sequel.blob('2')=>%w[2 3].map{|i| Sequel.blob(i)}).must_be_empty
ds.where(2=>[2, 3.0]).wont_be_empty
ds.where(4=>[2, 3.0]).must_be_empty
ds.exclude(4=>[2, 3.0]).wont_be_empty
ds.exclude(2=>[2, 4.0]).must_be_empty
ds.where(2=>[2]).wont_be_empty
ds.where(4=>[2]).must_be_empty
ds.exclude(4=>[2]).wont_be_empty
ds.exclude(2=>[2]).must_be_empty
ds.where(2=>[2, 3, nil]).wont_be_empty
ds.where(4=>[2, 3, nil]).must_be_empty
ds.exclude(4=>[2, 3, nil]).must_be_empty # NOT IN (..., NULL) predicate always false
ds.exclude(2=>[2, 4, nil]).must_be_empty
end
it "should provide a list of existing ordinary tables" do
@db.create_table(:test){Integer :id}
@db.tables.must_include :test
end
it "should handle providing a block to tables" do
@db.create_table(:test){Integer :id}
@db.tables{|ds| ds.where(:relkind=>'r').map{|r| r[:relname]}}.must_include 'test'
@db.tables{|ds| ds.where(:relkind=>'p').map{|r| r[:relname]}}.wont_include 'test'
end
it "should handle blobs" do
@db.create_table(:test){File :blob}
blob = Sequel.blob("\0\1\254\255").force_encoding('BINARY')
@db[:test].insert(blob)
@db[:test].get(:blob).force_encoding('BINARY').must_equal blob
end
it "should not include :min_value or :max_value for enums" do
begin
@db.drop_enum(:intro_type) rescue nil
@db.create_enum(:intro_type, %w(foo bar))
@db.create_table(:test){intro_type :baz; column :bazs, 'intro_type[]'}
sch = @db.schema(:test).first.last
sch[:max_value].must_be_nil
sch[:min_value].must_be_nil
sch = @db.schema(:test).last.last
sch[:max_value].must_be_nil
sch[:min_value].must_be_nil
ensure
@db.drop_enum(:intro_type) rescue nil
end
end
it "should not include :min_value or :max_value for arrays" do
@db.create_table(:test){column :baz, 'integer[]'}
sch = @db.schema(:test).first.last
sch[:max_value].must_be_nil
sch[:min_value].must_be_nil
end
{
Date=>Date,
'timestamp'=>Time,
'timestamptz'=>Time,
}.each do |type, klass|
if DB.server_version >= 90600
it "should correctly parse maximum and minimum values for #{type} columns" do
@db.create_table!(:test){column :a, type}
@db.timezone = :utc
sch = @db.schema(:test).first.last
max = sch[:max_value]
min = sch[:min_value]
max.must_be_kind_of klass
min.must_be_kind_of klass
insert_max = max
insert_min = min
if ENV['SEQUEL_PG_AUTO_PARAMETERIZE']
@db.extension :pg_extended_date_support
ds = @db[:test]
if type == 'timestamptz'
insert_max = Sequel.cast(max, 'timestamptz')
insert_min = Sequel.cast(min, 'timestamptz')
end
else
ds = @db[:test].with_extend(Sequel::Postgres::ExtendedDateSupport::DatasetMethods)
if type == 'timestamptz'
ds = ds.extension(:pg_timestamptz)
end
end
proc{ds.insert(max+1)}.must_raise(Sequel::DatabaseError)
proc{ds.insert(min-1)}.must_raise(Sequel::DatabaseError)
ds.insert(insert_max)
ds.insert(insert_min)
min, max_value = ds.select_order_map(:a)
min.must_be_kind_of klass
max_value.must_equal max
end
else
it "should not provide maximum and minimum values for #{type} columns" do
@db.create_table!(:test){column :a, type}
sch = @db.schema(:test).first.last
sch[:max_value].must_be_nil
sch[:min_value].must_be_nil
end
end
if klass == Time
it "should not provide maximum and minimum values for #{type} columns when Sequel.datetime_class is DateTime" do
Sequel.datetime_class = DateTime
@db.create_table!(:test){column :a, type}
sch = @db.schema(:test).first.last
sch[:max_value].must_be_nil
sch[:min_value].must_be_nil
end
end
end
it "should provide a list of existing partitioned tables" do
@db.create_table(:test, :partition_by => :id, :partition_type => :range){Integer :id}
@db.tables.must_include :test
end if DB.server_version >= 100000
it "should provide a list of existing ordinary and partitioned tables" do
@db.create_table(:test, :partition_by => :id, :partition_type => :range){Integer :id}
@db.create_table(:test_1, :partition_of => :test){from 1; to 3}
@db.create_table(:test_2, :partition_of => :test){from 3; to 4}
@db.tables.must_include :test
@db.tables.must_include :test_1
@db.tables.must_include :test_2
end if DB.server_version >= 100000
it "should provide a list of indexes on partitioned tables" do
@db.create_table(:test, :partition_by => :id, :partition_type => :range){Integer :id, :index=>true}
@db.create_table(:test_1, :partition_of => :test){from 1; to 3}
[:test, :test_1].each do |t|
h = @db.indexes(t)
h.size.must_equal 1
h.shift[1].must_equal(:columns=>[:id], :unique=>false, :deferrable=>nil)
end
end if DB.server_version >= 110000
it "should provide a list of foreign keys on partitioned tables" do
begin
@db.create_table(:test){primary_key :i}
@db.create_table(:test2, :partition_by => :id, :partition_type => :range){foreign_key :id, :test}
fks = @db.foreign_key_list(:test2)
fks.length.must_equal 1
fks[0][:table].must_equal :test
fks[0][:columns].must_equal [:id]
fks[0][:key].must_equal [:i]
ensure
@db.drop_table?(:test2)
end
end if DB.server_version >= 110000
end
describe "PostgreSQL", '#create_table' do
before do
@db = DB
end
after do
@db.drop_table?(:tmp_dolls, :unlogged_dolls)
@db.default_string_column_size = nil
end
it "should support multiple types of string columns" do
@db.default_string_column_size = 50
@db.create_table(:tmp_dolls) do
String :a1
String :a2, :size=>10
String :a3, :fixed=>true
String :a4, :fixed=>true, :size=>10
String :a5, :text=>false
String :a6, :text=>false, :size=>10
String :a7, :text=>true
end
@db.schema(:tmp_dolls).map{|k, v| v[:max_length]}.must_equal [nil, 10, 50, 10, 50, 10, nil]
end
it "should support range partitioned tables for single columns with :partition_* options" do
@db.create_table(:tmp_dolls, :partition_by => :id, :partition_type=>:range){Integer :id}
@db.create_table(:tmp_dolls_1, :partition_of => :tmp_dolls){from 1; to 3}
@db.create_table(:tmp_dolls_2, :partition_of => :tmp_dolls){from 3; to 4}
@db[:tmp_dolls].insert(1)
@db[:tmp_dolls].insert(2)
@db[:tmp_dolls].insert(3)
proc{@db[:tmp_dolls].insert(0)}.must_raise Sequel::DatabaseError
proc{@db[:tmp_dolls].insert(5)}.must_raise Sequel::DatabaseError
@db.create_table(:tmp_dolls_0, :partition_of => :tmp_dolls){from minvalue; to 1}
@db.create_table(:tmp_dolls_3, :partition_of => :tmp_dolls){from 4; to maxvalue}
@db[:tmp_dolls].insert(0)
@db[:tmp_dolls].insert(5)
@db[:tmp_dolls].order(:id).select_order_map(:id).must_equal [0,1,2,3,5]
@db[:tmp_dolls_0].order(:id).select_order_map(:id).must_equal [0]
@db[:tmp_dolls_1].order(:id).select_order_map(:id).must_equal [1,2]
@db[:tmp_dolls_2].order(:id).select_order_map(:id).must_equal [3]
@db[:tmp_dolls_3].order(:id).select_order_map(:id).must_equal [5]
end if DB.server_version >= 100000
it "should support range partitioned tables for multiple columns with :partition_* options" do
@db.create_table(:tmp_dolls, :partition_by => [:id, :id2], :partition_type=>:range){Integer :id; Integer :id2}
@db.create_table(:tmp_dolls_1, :partition_of => :tmp_dolls){from 1, 1; to 3, 3}
@db.create_table(:tmp_dolls_2, :partition_of => :tmp_dolls){from 3, 3; to 4, 4}
@db[:tmp_dolls].insert(1, 1)
@db[:tmp_dolls].insert(2, 2)
@db[:tmp_dolls].insert(3, 3)
proc{@db[:tmp_dolls].insert(0, 0)}.must_raise Sequel::DatabaseError
proc{@db[:tmp_dolls].insert(5, 5)}.must_raise Sequel::DatabaseError
@db.create_table(:tmp_dolls_0, :partition_of => :tmp_dolls){from minvalue, minvalue; to 1, 1}
@db.create_table(:tmp_dolls_3, :partition_of => :tmp_dolls){from 4, 4; to maxvalue, maxvalue}
@db[:tmp_dolls].insert(0, 0)
@db[:tmp_dolls].insert(5, 5)
@db[:tmp_dolls].order(:id).select_order_map([:id, :id2]).must_equal [0,1,2,3,5].map{|x| [x,x]}
@db[:tmp_dolls_0].order(:id).select_order_map([:id, :id2]).must_equal [0].map{|x| [x,x]}
@db[:tmp_dolls_1].order(:id).select_order_map([:id, :id2]).must_equal [1,2].map{|x| [x,x]}
@db[:tmp_dolls_2].order(:id).select_order_map([:id, :id2]).must_equal [3].map{|x| [x,x]}
@db[:tmp_dolls_3].order(:id).select_order_map([:id, :id2]).must_equal [5].map{|x| [x,x]}
end if DB.server_version >= 100000
it "should support list partitioned tables for single column with :partition_* options" do
@db.create_table(:tmp_dolls, :partition_by => :id, :partition_type=>:list){Integer :id}
@db.create_table(:tmp_dolls_1, :partition_of => :tmp_dolls){values_in 1, 2}
@db.create_table(:tmp_dolls_2, :partition_of => :tmp_dolls){values_in 3}
@db[:tmp_dolls].insert(1)
@db[:tmp_dolls].insert(2)
@db[:tmp_dolls].insert(3)
proc{@db[:tmp_dolls].insert(0)}.must_raise Sequel::DatabaseError
proc{@db[:tmp_dolls].insert(5)}.must_raise Sequel::DatabaseError
@db.create_table(:tmp_dolls_0, :partition_of => :tmp_dolls){values_in 0}
@db.create_table(:tmp_dolls_3, :partition_of => :tmp_dolls){default}
@db[:tmp_dolls].insert(0)
@db[:tmp_dolls].insert(5)
@db[:tmp_dolls].order(:id).select_order_map(:id).must_equal [0,1,2,3,5]
@db[:tmp_dolls_0].order(:id).select_order_map(:id).must_equal [0]
@db[:tmp_dolls_1].order(:id).select_order_map(:id).must_equal [1,2]
@db[:tmp_dolls_2].order(:id).select_order_map(:id).must_equal [3]
@db[:tmp_dolls_3].order(:id).select_order_map(:id).must_equal [5]
end if DB.server_version >= 110000
it "should support hash partitioned tables for single column with :partition_* options" do
@db.create_table(:tmp_dolls, :partition_by => :id, :partition_type=>:hash){Integer :id}
@db.create_table(:tmp_dolls_0, :partition_of => :tmp_dolls){modulus 4; remainder 0}
@db.create_table(:tmp_dolls_1, :partition_of => :tmp_dolls){modulus 4; remainder 1}
@db.create_table(:tmp_dolls_2, :partition_of => :tmp_dolls){modulus 4; remainder 2}
@db.create_table(:tmp_dolls_3, :partition_of => :tmp_dolls){modulus 4; remainder 3}
@db[:tmp_dolls].insert(1)
@db[:tmp_dolls].insert(2)
@db[:tmp_dolls].insert(3)
@db[:tmp_dolls].insert(4)
@db[:tmp_dolls].order(:id).select_order_map(:id).must_equal [1,2,3,4]
[0,1,2,3].flat_map{|i| @db[:"tmp_dolls_#{i}"].select_order_map(:id)}.sort.must_equal [1,2,3,4]
end if DB.server_version >= 110000
it "should support partitioned tables with create_table?" do
@db.create_table(:tmp_dolls, :partition_by => :id, :partition_type=>:range){Integer :id}
@db.create_table?(:tmp_dolls_1, :partition_of => :tmp_dolls){from 1; to 3}
@db.create_table?(:tmp_dolls_2, :partition_of => :tmp_dolls){from 3; to 4}
@db[:tmp_dolls].insert(1)
@db[:tmp_dolls].insert(2)
@db[:tmp_dolls].insert(3)
proc{@db[:tmp_dolls].insert(0)}.must_raise Sequel::DatabaseError
proc{@db[:tmp_dolls].insert(5)}.must_raise Sequel::DatabaseError
@db.create_table?(:tmp_dolls_0, :partition_of => :tmp_dolls){from minvalue; to 1}
@db.create_table?(:tmp_dolls_3, :partition_of => :tmp_dolls){from 4; to maxvalue}
@db[:tmp_dolls].insert(0)
@db[:tmp_dolls].insert(5)
@db[:tmp_dolls].order(:id).select_order_map(:id).must_equal [0,1,2,3,5]
@db[:tmp_dolls_0].order(:id).select_order_map(:id).must_equal [0]
@db[:tmp_dolls_1].order(:id).select_order_map(:id).must_equal [1,2]
@db[:tmp_dolls_2].order(:id).select_order_map(:id).must_equal [3]
@db[:tmp_dolls_3].order(:id).select_order_map(:id).must_equal [5]
end if DB.server_version >= 100000
it "should support partitioned tables with create_table!" do
@db.create_table(:tmp_dolls, :partition_by => :id, :partition_type=>:range){Integer :id}
@db.create_table!(:tmp_dolls_1, :partition_of => :tmp_dolls){from 1; to 3}
@db.create_table!(:tmp_dolls_1, :partition_of => :tmp_dolls){from 1; to 3}
@db.create_table!(:tmp_dolls_2, :partition_of => :tmp_dolls){from 3; to 4}
@db.create_table!(:tmp_dolls_2, :partition_of => :tmp_dolls){from 3; to 4}
@db[:tmp_dolls].insert(1)
@db[:tmp_dolls].insert(2)
@db[:tmp_dolls].insert(3)
proc{@db[:tmp_dolls].insert(0)}.must_raise Sequel::DatabaseError
proc{@db[:tmp_dolls].insert(5)}.must_raise Sequel::DatabaseError
@db.create_table!(:tmp_dolls_0, :partition_of => :tmp_dolls){from minvalue; to 1}
@db.create_table!(:tmp_dolls_0, :partition_of => :tmp_dolls){from minvalue; to 1}
@db.create_table!(:tmp_dolls_3, :partition_of => :tmp_dolls){from 4; to maxvalue}
@db.create_table!(:tmp_dolls_3, :partition_of => :tmp_dolls){from 4; to maxvalue}
@db[:tmp_dolls].insert(0)
@db[:tmp_dolls].insert(5)
@db[:tmp_dolls].order(:id).select_order_map(:id).must_equal [0,1,2,3,5]
@db[:tmp_dolls_0].order(:id).select_order_map(:id).must_equal [0]
@db[:tmp_dolls_1].order(:id).select_order_map(:id).must_equal [1,2]
@db[:tmp_dolls_2].order(:id).select_order_map(:id).must_equal [3]
@db[:tmp_dolls_3].order(:id).select_order_map(:id).must_equal [5]
end if DB.server_version >= 100000
it "should raise for unsupported partition types" do
@db.create_table(:tmp_dolls, :partition_by => [:id, :id2], :partition_type=>:range){Integer :id; Integer :id2}
proc{@db.create_table(:tmp_dolls_1, :partition_of => :tmp_dolls){from 1, 1; to 3, 3; modulus 10}}.must_raise Sequel::Error
proc{@db.create_table(:tmp_dolls_1, :partition_of => :tmp_dolls){from 1, 1}}.must_raise Sequel::Error
proc{@db.create_table(:tmp_dolls_1, :partition_of => :tmp_dolls){modulus 10}}.must_raise Sequel::Error
proc{@db.create_table(:tmp_dolls_1, :partition_of => :tmp_dolls){}}.must_raise Sequel::Error
end if DB.server_version >= 100000
it "should not use a size for text columns" do
@db.create_table(:tmp_dolls){String :description, text: true, size: :long}
@db.tables.must_include :tmp_dolls
end
it "should create an unlogged table" do
@db.create_table(:unlogged_dolls, :unlogged => true){text :name}
end if DB.server_version >= 90100
it "should create a table inheriting from another table" do
@db.create_table(:unlogged_dolls){text :name}
@db.create_table(:tmp_dolls, :inherits=>:unlogged_dolls){}
@db[:tmp_dolls].insert('a')
@db[:unlogged_dolls].all.must_equal [{:name=>'a'}]
end
it "should create a table inheriting from multiple tables" do
begin
@db.create_table(:unlogged_dolls){text :name}
@db.create_table(:tmp_dolls){text :bar}
@db.create_table!(:items, :inherits=>[:unlogged_dolls, :tmp_dolls]){text :foo}
@db[:items].insert(:name=>'a', :bar=>'b', :foo=>'c')
@db[:unlogged_dolls].all.must_equal [{:name=>'a'}]
@db[:tmp_dolls].all.must_equal [{:bar=>'b'}]
@db[:items].all.must_equal [{:name=>'a', :bar=>'b', :foo=>'c'}]
ensure
@db.drop_table?(:items)
end
end
it "should have #immediate_constraints and #defer_constraints for deferring/checking deferrable constraints" do
@db.create_table(:tmp_dolls) do
primary_key :id
foreign_key(:x, :tmp_dolls, :foreign_key_constraint_name=>:x_fk, :deferrable=>true)
foreign_key(:y, :tmp_dolls, :foreign_key_constraint_name=>:y_fk, :deferrable=>true)
end
ds = @db[:tmp_dolls]
@db.transaction do
@db.immediate_constraints
ds.insert(:id=>1)
@db.defer_constraints
ds.insert(:id=>2, :x=>1, :y=>3)
proc{@db.immediate_constraints}.must_raise Sequel::ForeignKeyConstraintViolation
end
@db[:tmp_dolls].must_be_empty
@db.transaction do
@db.immediate_constraints
@db.defer_constraints(:constraints=>:y_fk)
ds.insert(:id=>1, :x=>1, :y=>2)
proc{@db.immediate_constraints}.must_raise Sequel::ForeignKeyConstraintViolation
end
@db[:tmp_dolls].must_be_empty
@db.transaction do
@db.defer_constraints
ds.insert(:id=>1, :x=>1, :y=>2)
proc{@db.immediate_constraints(:constraints=>:y_fk)}.must_raise Sequel::ForeignKeyConstraintViolation
end
@db[:tmp_dolls].must_be_empty
@db.transaction do
@db.immediate_constraints
@db.defer_constraints(:constraints=>[:x_fk, :y_fk])
ds.insert(:id=>1, :x=>3, :y=>2)
ds.update(:id=>1, :x=>1, :y=>1)
end
@db[:tmp_dolls].count.must_equal 1
end
it "should have #check_constraints method for getting check constraints" do
@db.create_table(:tmp_dolls) do
Integer :i
Integer :j
constraint(:ic, Sequel[:i] > 2)
constraint(:jc, Sequel[:j] > 2)
constraint(:ijc, Sequel[:i] - Sequel[:j] > 2)
end
@db.check_constraints(:tmp_dolls).must_equal(:ic=>{:definition=>"CHECK ((i > 2))", :columns=>[:i]}, :jc=>{:definition=>"CHECK ((j > 2))", :columns=>[:j]}, :ijc=>{:definition=>"CHECK (((i - j) > 2))", :columns=>[:i, :j]})
end
it "should have #check_constraints return check constraints where columns are unknown" do
begin
@db.create_table(:tmp_dolls) do
Integer :i
Integer :j
end
@db.run "CREATE OR REPLACE FUNCTION valid_tmp_dolls(t1 tmp_dolls) RETURNS boolean AS 'SELECT false' LANGUAGE SQL;"
@db.alter_table(:tmp_dolls) do
add_constraint(:valid_tmp_dolls, Sequel.function(:valid_tmp_dolls, :tmp_dolls))
end
@db.check_constraints(:tmp_dolls).must_equal(:valid_tmp_dolls=>{:definition=>"CHECK (valid_tmp_dolls(tmp_dolls.*))", :columns=>[]})
ensure
@db.run "ALTER TABLE tmp_dolls DROP CONSTRAINT IF EXISTS valid_tmp_dolls"
@db.run "DROP FUNCTION IF EXISTS valid_tmp_dolls(tmp_dolls)"
end
end if DB.server_version >= 90000
it "should support :if_exists option to drop_column" do
@db.create_table(:tmp_dolls){Integer :a; Integer :b}
2.times do
@db.drop_column :tmp_dolls, :b, :if_exists=>true
@db[:tmp_dolls].columns.must_equal [:a]
end
end if DB.server_version >= 90000
it "should support primary_key with :type=>:smallserial, :type=>:serial or :type=>:bigserial" do
[:smallserial, :serial, :bigserial, 'smallserial', 'serial', 'bigserial'].each do |type|
@db.create_table!(:tmp_dolls){primary_key :id, :type=>type}
@db[:tmp_dolls].insert
@db[:tmp_dolls].get(:id).must_equal 1
end
end if DB.server_version >= 100002
it "should support primary_key with :serial=>true" do
@db.create_table!(:tmp_dolls){primary_key :id, :serial=>true}
@db[:tmp_dolls].insert
@db[:tmp_dolls].get(:id).must_equal 1
end if DB.server_version >= 100002
it "should support primary_key with :Bignum type and serial=>true" do
@db.create_table!(:tmp_dolls){primary_key :id, :type=>:Bignum, :serial=>true}
@db[:tmp_dolls].insert(2**48)
@db[:tmp_dolls].get(:id).must_equal(2**48)
end
it "should support Bignum column with serial=>true" do
@db.create_table!(:tmp_dolls){Bignum :id, :serial=>true, :primary_key=>true}
@db[:tmp_dolls].insert(2**48)
@db[:tmp_dolls].get(:id).must_equal(2**48)
end
it "should support creating identity columns on non-primary key tables" do
@db.create_table(:tmp_dolls){Integer :a, :identity=>true}
2.times do
@db[:tmp_dolls].insert
end
@db[:tmp_dolls].select_order_map(:a).must_equal [1, 2]
@db[:tmp_dolls].insert(:a=>2)
@db[:tmp_dolls].select_order_map(:a).must_equal [1, 2, 2]
@db[:tmp_dolls].insert(:a=>4)
@db[:tmp_dolls].select_order_map(:a).must_equal [1, 2, 2, 4]
@db[:tmp_dolls].overriding_user_value.insert(:a=>5)
@db[:tmp_dolls].select_order_map(:a).must_equal [1, 2, 2, 3, 4]
end if DB.server_version >= 100002
it "should support creating identity columns generated always" do
@db.create_table(:tmp_dolls){primary_key :id, :identity=>:always}
2.times do
@db[:tmp_dolls].insert
end
@db[:tmp_dolls].select_order_map(:id).must_equal [1, 2]
proc{@db[:tmp_dolls].insert(:id=>2)}.must_raise Sequel::DatabaseError
@db[:tmp_dolls].overriding_system_value.insert(:id=>4)
@db[:tmp_dolls].select_order_map(:id).must_equal [1, 2, 4]
@db[:tmp_dolls].insert
@db[:tmp_dolls].select_order_map(:id).must_equal [1, 2, 3, 4]
end if DB.server_version >= 100002
it "should handle generated column overrides using override value at time of merge_insert call" do
@db.create_table(:tmp_dolls){primary_key :id, :identity=>:always}
@db.create_table(:unlogged_dolls){Integer :i}
@db[:unlogged_dolls].insert(10)
@db[:unlogged_dolls].insert(20)
@db[:tmp_dolls].
merge_using(:unlogged_dolls, :id=>:i).
overriding_system_value.
merge_insert(:i){i > 15}.
overriding_user_value.
merge_insert(:i).
merge
@db[:tmp_dolls].select_order_map(:id).must_equal [1, 20]
end if DB.server_version >= 150000
it "should support converting serial columns to identity columns" do
@db.create_table(:tmp_dolls){primary_key :id, :identity=>false, :serial=>true}
sch = @db.schema(:tmp_dolls)[0][1]
sch[:default].must_match(/nextval/)
sch[:auto_increment].must_equal true
2.times do
@db[:tmp_dolls].insert
end
@db.convert_serial_to_identity(:tmp_dolls)
sch = @db.schema(:tmp_dolls)[0][1]
sch[:default].must_be_nil
sch[:auto_increment].must_equal true
@db[:tmp_dolls].insert
@db[:tmp_dolls].insert(5)
@db[:tmp_dolls].select_order_map(:id).must_equal [1, 2, 3, 5]
# Make sure it doesn't break if already converted
@db.convert_serial_to_identity(:tmp_dolls)
end if DB.server_version >= 100002 && DB.get{current_setting('is_superuser')} == 'on'
it "should support converting serial columns to identity columns when using the :column option" do
@db.create_table(:tmp_dolls){Integer :i, :primary_key=>true; serial :id}
sch = @db.schema(:tmp_dolls)[1][1]
sch[:default].must_match(/nextval/)
2.times do |i|
@db[:tmp_dolls].insert(:i=>-i)
end
# Automatic conversion should not work
proc{@db.convert_serial_to_identity(:tmp_dolls)}.must_raise Sequel::Error
# Conversion of type without related sequence should not work
proc{@db.convert_serial_to_identity(:tmp_dolls, :column=>:i)}.must_raise Sequel::Error
@db.convert_serial_to_identity(:tmp_dolls, :column=>:id)
sch = @db.schema(:tmp_dolls)[1][1]
sch[:default].must_be_nil
@db[:tmp_dolls].insert(:i=>200)
@db[:tmp_dolls].insert(:i=>300, :id=>5)
@db[:tmp_dolls].select_order_map(:id).must_equal [1, 2, 3, 5]
# Make sure it doesn't break if already converted
@db.convert_serial_to_identity(:tmp_dolls, :column=>:id)
end if DB.server_version >= 100002 && DB.get{current_setting('is_superuser')} == 'on'
it "should support creating generated columns" do
@db.create_table(:tmp_dolls){Integer :a; Integer :b; Integer :c, :generated_always_as=>Sequel[:a] * 2 + :b + 1}
@db[:tmp_dolls].insert(:a=>100, :b=>10)
@db[:tmp_dolls].select_order_map([:a, :b, :c]).must_equal [[100, 10, 211]]
end if DB.server_version >= 120000
it "should include :generated entry in schema for whether the column is generated" do
@db.create_table(:tmp_dolls){Integer :a; Integer :b; Integer :c, :generated_always_as=>Sequel[:a] * 2 + :b + 1}
@db.schema(:tmp_dolls).map{|_,v| v[:generated]}.must_equal [false, false, true]
end if DB.server_version >= 120000
it "should support deferred primary key and unique constraints on columns" do
@db.create_table(:tmp_dolls){primary_key :id, :primary_key_deferrable=>true; Integer :i, :unique=>true, :unique_deferrable=>true}
@db[:tmp_dolls].insert(:i=>10)
DB.transaction do
@db[:tmp_dolls].insert(:id=>1, :i=>1)
@db[:tmp_dolls].insert(:id=>10, :i=>10)
@db[:tmp_dolls].where(:i=>1).update(:id=>2)
@db[:tmp_dolls].where(:id=>10).update(:i=>2)
end
@db[:tmp_dolls].select_order_map([:id, :i]).must_equal [[1, 10], [2, 1], [10, 2]]
end if DB.server_version >= 90000
it "should support pg_loose_count extension" do
@db.extension :pg_loose_count
@db.create_table(:tmp_dolls){text :name}
@db.loose_count(:tmp_dolls).must_be_kind_of(Integer)
[0, -1].must_include @db.loose_count(:tmp_dolls)
[0, -1].must_include @db.loose_count(Sequel[:public][:tmp_dolls])
@db[:tmp_dolls].insert('a')
@db << 'VACUUM ANALYZE tmp_dolls'
@db.loose_count(:tmp_dolls).must_equal 1
@db.loose_count(Sequel[:public][:tmp_dolls]).must_equal 1
end
end
describe "PostgreSQL temporary table/view support" do
before(:all) do
@db = DB
@db.disconnect
end
after do
@db.drop_view(:tmp_dolls_view, :if_exists=>true, :cascade=>true) rescue nil
@db.drop_table?(:tmp_dolls)
end
it "should create a temporary table" do
@db.create_table(:tmp_dolls, :temp => true){text :name}
@db.table_exists?(:tmp_dolls).must_equal true
@db.disconnect
@db.table_exists?(:tmp_dolls).must_equal false
end
it "temporary table should support :on_commit option" do
@db.drop_table?(:some_table)
@db.transaction do
@db.create_table(:some_table, :temp => true, :on_commit => :drop){text :name}
end
@db.table_exists?(:some_table).must_equal false
@db.transaction do
@db.create_table(:some_table, :temp => true, :on_commit => :delete_rows){text :name}
@db[:some_table].insert('a')
end
@db.table_exists?(:some_table).must_equal true
@db[:some_table].empty?.must_equal true
@db.drop_table(:some_table)
@db.transaction do
@db.create_table(:some_table, :temp => true, :on_commit => :preserve_rows){text :name}
@db[:some_table].insert('a')
end
@db.table_exists?(:some_table).must_equal true
@db[:some_table].count.must_equal 1
@db.drop_table(:some_table)
end
it "temporary table should accept :on_commit with :as option" do
@db.drop_table?(:some_table)
@db.transaction do
@db.create_table(:some_table, :temp => true, :on_commit => :drop, :as => 'select 1')
end
@db.table_exists?(:some_table).must_equal false
end
it ":on_commit should raise error if not used on a temporary table" do
proc{@db.create_table(:some_table, :on_commit => :drop)}.must_raise(Sequel::Error)
end
it ":on_commit should raise error if given unsupported value" do
proc{@db.create_table(:some_table, :temp => true, :on_commit => :unsupported){text :name}}.must_raise(Sequel::Error)
end
it "should not allow to pass both :temp and :unlogged" do
proc do
@db.create_table(:temp_unlogged_dolls, :temp => true, :unlogged => true){text :name}
end.must_raise(Sequel::Error, "can't provide both :temp and :unlogged to create_table")
end
it "should support temporary views" do
@db.create_table(:tmp_dolls, :temp => true){Integer :number}
@db[:tmp_dolls].insert(10)
@db[:tmp_dolls].insert(20)
@db.create_view(:tmp_dolls_view, @db[:tmp_dolls].where(:number=>10), :temp=>true)
@db[:tmp_dolls_view].map(:number).must_equal [10]
@db.create_or_replace_view(:tmp_dolls_view, @db[:tmp_dolls].where(:number=>20), :temp=>true)
@db[:tmp_dolls_view].map(:number).must_equal [20]
end
it "should support security_invoker view option" do
@db.create_table(:tmp_dolls, :temp => true){Integer :number}
@db[:tmp_dolls].insert(10)
@db.create_view(:tmp_dolls_view, @db[:tmp_dolls].where(:number=>10), :temp=>true, :security_invoker=>true)
@db[:tmp_dolls_view].count.must_equal 1
end if DB.server_version >= 150000
end
describe "PostgreSQL views" do
before do
@db = DB
@db.drop_table?(:items, :cascade=>true)
@db.create_table(:items){Integer :number}
@db[:items].insert(10)
@db[:items].insert(20)
end
after do
@opts ||={}
@db.drop_view(:items_view, @opts.merge(:if_exists=>true, :cascade=>true)) rescue nil
@db.drop_table?(:items)
end
it "should support recursive views" do
@db.create_view(:items_view, @db[:items].where(:number=>10).union(@db[:items, :items_view].where(Sequel.-(:number, 5)=>:n).select(:number), :all=>true, :from_self=>false), :recursive=>[:n])
@db[:items_view].select_order_map(:n).must_equal [10]
@db[:items].insert(15)
@db[:items_view].select_order_map(:n).must_equal [10, 15, 20]
end if DB.server_version >= 90300
it "should support materialized views" do
@opts = {:materialized=>true}
@db.create_view(:items_view, @db[:items].where{number >= 10}, @opts)
@db[:items_view].select_order_map(:number).must_equal [10, 20]
@db[:items].insert(15)
@db[:items_view].select_order_map(:number).must_equal [10, 20]
@db.refresh_view(:items_view)
@db[:items_view].select_order_map(:number).must_equal [10, 15, 20]
@db.views.wont_include :items_view
@db.views(@opts).must_include :items_view
end if DB.server_version >= 90300
it "should support replacing materialized views" do
@opts = {:materialized=>true}
@db[:items].insert(15)
@db.create_or_replace_view(:items_view, @db[:items].where{number > 10}, @opts)
@db[:items_view].select_order_map(:number).must_equal [15, 20]
@db.create_or_replace_view(:items_view, @db[:items].where{number > 15}, @opts)
@db[:items_view].select_order_map(:number).must_equal [20]
end if DB.server_version >= 90300
it "should support refreshing materialized views concurrently" do
@opts = {:materialized=>true}
@db.create_view(:items_view, @db[:items].where{number >= 10}, @opts)
@db.refresh_view(:items_view)
proc{@db.refresh_view(:items_view, :concurrently=>true)}.must_raise(Sequel::DatabaseError)
@db.add_index :items_view, :number, :unique=>true
@db.refresh_view(:items_view, :concurrently=>true)
end if DB.server_version >= 90400
it "should support specifying tablespaces for materialized views" do
@opts = {:materialized=>true}
@db.create_view(:items_view, @db[:items].where{number >= 10}, :materialized=>true, :tablespace=>:pg_default)
end if DB.server_version >= 90300
it "should support :if_exists=>true for not raising an error if the view does not exist" do
@db.drop_view(:items_view, :if_exists=>true)
end
end
describe "PostgreSQL", 'INSERT ON CONFLICT' do
before(:all) do
@db = DB
@db.create_table!(:ic_test){Integer :a; Integer :b; Integer :c; TrueClass :c_is_unique, :default=>false; unique :a, :name=>:ic_test_a_uidx; unique [:b, :c], :name=>:ic_test_b_c_uidx; index [:c], :where=>:c_is_unique, :unique=>true}
@ds = @db[:ic_test]
end
before do
@ds.delete
end
after(:all) do
@db.drop_table?(:ic_test)
end
it "Dataset#supports_insert_conflict? should be true" do
@ds.supports_insert_conflict?.must_equal true
end
it "Dataset#insert_ignore and insert_conflict should ignore uniqueness violations" do
@ds.insert(1, 2, 3)
@ds.insert(10, 11, 3, true)
proc{@ds.insert(1, 3, 4)}.must_raise Sequel::UniqueConstraintViolation
proc{@ds.insert(11, 12, 3, true)}.must_raise Sequel::UniqueConstraintViolation
@ds.insert_ignore.insert(1, 3, 4).must_be_nil
@ds.insert_conflict.insert(1, 3, 4).must_be_nil
@ds.insert_conflict.insert(11, 12, 3, true).must_be_nil
@ds.insert_conflict(:target=>:a).insert(1, 3, 4).must_be_nil
@ds.insert_conflict(:target=>:c, :conflict_where=>:c_is_unique).insert(11, 12, 3, true).must_be_nil
@ds.insert_conflict(:constraint=>:ic_test_a_uidx).insert(1, 3, 4).must_be_nil
@ds.all.must_equal [{:a=>1, :b=>2, :c=>3, :c_is_unique=>false}, {:a=>10, :b=>11, :c=>3, :c_is_unique=>true}]
end
it "Dataset#insert_ignore and insert_conflict should work with multi_insert/import" do
@ds.insert(1, 2, 3)
@ds.insert_ignore.multi_insert([{:a=>1, :b=>3, :c=>4}])
@ds.insert_ignore.import([:a, :b, :c], [[1, 3, 4]])
@ds.all.must_equal [{:a=>1, :b=>2, :c=>3, :c_is_unique=>false}]
@ds.insert_conflict(:target=>:a, :update=>{:b=>3}).import([:a, :b, :c], [[1, 3, 4]])
@ds.all.must_equal [{:a=>1, :b=>3, :c=>3, :c_is_unique=>false}]
@ds.insert_conflict(:target=>:a, :update=>{:b=>4}).multi_insert([{:a=>1, :b=>5, :c=>6}])
@ds.all.must_equal [{:a=>1, :b=>4, :c=>3, :c_is_unique=>false}]
end
it "Dataset#insert_conflict should handle upserts" do
@ds.insert(1, 2, 3)
@ds.insert_conflict(:target=>:a, :update=>{:b=>3}).insert(1, 3, 4).must_be_nil
@ds.all.must_equal [{:a=>1, :b=>3, :c=>3, :c_is_unique=>false}]
@ds.insert_conflict(:target=>[:b, :c], :update=>{:c=>5}).insert(5, 3, 3).must_be_nil
@ds.all.must_equal [{:a=>1, :b=>3, :c=>5, :c_is_unique=>false}]
@ds.insert_conflict(:constraint=>:ic_test_a_uidx, :update=>{:b=>4}).insert(1, 3).must_be_nil
@ds.all.must_equal [{:a=>1, :b=>4, :c=>5, :c_is_unique=>false}]
@ds.insert_conflict(:constraint=>:ic_test_a_uidx, :update=>{:b=>5}, :update_where=>{Sequel[:ic_test][:b]=>4}).insert(1, 3, 4).must_be_nil
@ds.all.must_equal [{:a=>1, :b=>5, :c=>5, :c_is_unique=>false}]
@ds.insert_conflict(:constraint=>:ic_test_a_uidx, :update=>{:b=>6}, :update_where=>{Sequel[:ic_test][:b]=>4}).insert(1, 3, 4).must_be_nil
@ds.all.must_equal [{:a=>1, :b=>5, :c=>5, :c_is_unique=>false}]
end
it "Dataset#insert_conflict should support table aliases" do
@ds = @db[Sequel[:ic_test].as(:foo)]
@ds.insert(1, 2, 5)
proc{@ds.insert(1, 3, 4)}.must_raise Sequel::UniqueConstraintViolation
@ds.insert_conflict(:target=>:a, :update=>{:b=>Sequel[:foo][:c] + Sequel[:excluded][:c]}).insert(1, 7, 10)
@ds.all.must_equal [{:a=>1, :b=>15, :c=>5, :c_is_unique=>false}]
end
end if DB.server_version >= 90500
describe "A PostgreSQL database" do
before(:all) do
@db = DB
@db.create_table!(Sequel[:public][:testfk]){primary_key :id; foreign_key :i, Sequel[:public][:testfk]}
end
after(:all) do
@db.drop_table?(Sequel[:public][:testfk])
end
it "should provide the server version" do
@db.server_version.must_be :>, 70000
end
it "should create a dataset using the VALUES clause via #values" do
@db.values([[1, 2], [3, 4]]).map([:column1, :column2]).must_equal [[1, 2], [3, 4]]
end
it "should support compounds with VALUES" do
@db.values([[1, 2]]).union(@db.values([[3, 4]])).map([:column1, :column2]).must_equal [[1, 2], [3, 4]]
end
it "#values should error if given an empty array" do
proc{@db.values([])}.must_raise(Sequel::Error)
end
it "#empty? should return false for datasets using VALUES" do
@db.values([[nil]]).empty?.must_equal false
end
it "#count should return correct number of rows for datasets using VALUES when called without arguments" do
@db.values([[100]]).count.must_equal 1
@db.values([[100, 1]]).count.must_equal 1
@db.values([[100], [200]]).count.must_equal 2
@db.values([[100, 10], [200, 10]]).count.must_equal 2
end
it "#count should return correct number of rows for datasets using VALUES when called with arguments" do
@db.values([[100]]).count(:column1).must_equal 1
@db.values([[nil]]).count{:column1}.must_equal 0
end
it "should correctly handle various numbers of columns" do
[1, 2, 15, 16, 17, 63, 64, 65, 255, 256, 257, 1663, 1664].each do |i|
DB.get((1..i).map{|j| Sequel.as(j, "c#{j}")}).must_equal((1..i).to_a)
end
proc{DB.get((1..1665).map{|j| Sequel.as(j, "c#{j}")})}.must_raise Sequel::DatabaseError
end
it "should support ordering in aggregate functions" do
@db.from(@db.values([['1'], ['2']]).as(:t, [:a])).get{string_agg(:a, '-').order(Sequel.desc(:a)).as(:c)}.must_equal '2-1'
end if DB.server_version >= 90000
it "should support ordering and limiting with #values" do
@db.values([[1, 2], [3, 4]]).reverse(:column2, :column1).limit(1).map([:column1, :column2]).must_equal [[3, 4]]
@db.values([[1, 2], [3, 4]]).reverse(:column2, :column1).offset(1).map([:column1, :column2]).must_equal [[1, 2]]
end