forked from jeremyevans/sequel
/
mysql_spec.rb
941 lines (789 loc) · 34.2 KB
/
mysql_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
require File.join(File.dirname(__FILE__), 'spec_helper.rb')
unless defined?(MYSQL_USER)
MYSQL_USER = 'root'
end
unless defined?(MYSQL_DB)
MYSQL_URL = (ENV['SEQUEL_MY_SPEC_DB']||"mysql://#{MYSQL_USER}@localhost/sandbox") unless defined? MYSQL_URL
MYSQL_DB = Sequel.connect(MYSQL_URL)
end
unless defined?(MYSQL_SOCKET_FILE)
MYSQL_SOCKET_FILE = '/tmp/mysql.sock'
end
INTEGRATION_DB = MYSQL_DB unless defined?(INTEGRATION_DB)
MYSQL_URI = URI.parse(MYSQL_DB.uri)
MYSQL_DB.create_table! :test2 do
text :name
integer :value
end
def MYSQL_DB.sqls
(@sqls ||= [])
end
logger = Object.new
def logger.method_missing(m, msg)
MYSQL_DB.sqls << msg
end
MYSQL_DB.logger = logger
MYSQL_DB.drop_table(:items) rescue nil
MYSQL_DB.drop_table(:dolls) rescue nil
MYSQL_DB.drop_table(:booltest) rescue nil
SQL_BEGIN = 'BEGIN'
SQL_ROLLBACK = 'ROLLBACK'
SQL_COMMIT = 'COMMIT'
context "MySQL", '#create_table' do
before do
@db = MYSQL_DB
MYSQL_DB.sqls.clear
end
after do
@db.drop_table(:dolls) rescue nil
end
specify "should allow to specify options for MySQL" do
@db.create_table(:dolls, :engine => 'MyISAM', :charset => 'latin2'){text :name}
@db.sqls.should == ["CREATE TABLE dolls (name text) ENGINE=MyISAM DEFAULT CHARSET=latin2"]
end
specify "should create a temporary table" do
@db.create_table(:tmp_dolls, :temp => true, :engine => 'MyISAM', :charset => 'latin2'){text :name}
@db.sqls.should == ["CREATE TEMPORARY TABLE tmp_dolls (name text) ENGINE=MyISAM DEFAULT CHARSET=latin2"]
end
specify "should not use a default for a String :text=>true type" do
@db.create_table(:dolls){String :name, :text=>true, :default=>'blah'}
@db.sqls.should == ["CREATE TABLE dolls (name text)"]
end
specify "should not use a default for a File type" do
@db.create_table(:dolls){File :name, :default=>'blah'}
@db.sqls.should == ["CREATE TABLE dolls (name blob)"]
end
end
context "A MySQL database" do
specify "should provide the server version" do
MYSQL_DB.server_version.should >= 40000
end
end
if MYSQL_DB.class.adapter_scheme == :mysql
context "Sequel::MySQL.convert_tinyint_to_bool" do
before do
@db = MYSQL_DB
@db.create_table(:booltest){column :b, 'tinyint(1)'; column :i, 'tinyint(4)'}
@ds = @db[:booltest]
end
after do
Sequel::MySQL.convert_tinyint_to_bool = true
@db.drop_table(:booltest)
end
specify "should consider tinyint(1) datatypes as boolean if set, but not larger tinyints" do
@db.schema(:booltest, :reload=>true).should == [[:b, {:type=>:boolean, :allow_null=>true, :primary_key=>false, :default=>nil, :ruby_default=>nil, :db_type=>"tinyint(1)"}, ], [:i, {:type=>:integer, :allow_null=>true, :primary_key=>false, :default=>nil, :ruby_default=>nil, :db_type=>"tinyint(4)"}, ]]
Sequel::MySQL.convert_tinyint_to_bool = false
@db.schema(:booltest, :reload=>true).should == [[:b, {:type=>:integer, :allow_null=>true, :primary_key=>false, :default=>nil, :ruby_default=>nil, :db_type=>"tinyint(1)"}, ], [:i, {:type=>:integer, :allow_null=>true, :primary_key=>false, :default=>nil, :ruby_default=>nil, :db_type=>"tinyint(4)"}, ]]
end
specify "should return tinyints as bools when set" do
@ds.delete
@ds << {:b=>true, :i=>10}
@ds.all.should == [{:b=>true, :i=>true}]
@ds.delete
@ds << {:b=>false, :i=>0}
@ds.all.should == [{:b=>false, :i=>false}]
Sequel::MySQL.convert_tinyint_to_bool = false
@ds.delete
@ds << {:b=>true, :i=>10}
@ds.all.should == [{:b=>1, :i=>10}]
@ds.delete
@ds << {:b=>false, :i=>0}
@ds.all.should == [{:b=>0, :i=>0}]
@ds.delete
@ds << {:b=>1, :i=>10}
@ds.all.should == [{:b=>1, :i=>10}]
@ds.delete
@ds << {:b=>0, :i=>0}
@ds.all.should == [{:b=>0, :i=>0}]
end
end
end
context "A MySQL dataset" do
before do
MYSQL_DB.create_table(:items){String :name; Integer :value}
@d = MYSQL_DB[:items]
MYSQL_DB.sqls.clear
end
after do
MYSQL_DB.drop_table(:items)
end
specify "should quote columns and tables using back-ticks if quoting identifiers" do
@d.quote_identifiers = true
@d.select(:name).sql.should == \
'SELECT `name` FROM `items`'
@d.select('COUNT(*)'.lit).sql.should == \
'SELECT COUNT(*) FROM `items`'
@d.select(:max.sql_function(:value)).sql.should == \
'SELECT max(`value`) FROM `items`'
@d.select(:NOW.sql_function).sql.should == \
'SELECT NOW() FROM `items`'
@d.select(:max.sql_function(:items__value)).sql.should == \
'SELECT max(`items`.`value`) FROM `items`'
@d.order(:name.desc).sql.should == \
'SELECT * FROM `items` ORDER BY `name` DESC'
@d.select('items.name AS item_name'.lit).sql.should == \
'SELECT items.name AS item_name FROM `items`'
@d.select('`name`'.lit).sql.should == \
'SELECT `name` FROM `items`'
@d.select('max(items.`name`) AS `max_name`'.lit).sql.should == \
'SELECT max(items.`name`) AS `max_name` FROM `items`'
@d.select(:test.sql_function(:abc, 'hello')).sql.should == \
"SELECT test(`abc`, 'hello') FROM `items`"
@d.select(:test.sql_function(:abc__def, 'hello')).sql.should == \
"SELECT test(`abc`.`def`, 'hello') FROM `items`"
@d.select(:test.sql_function(:abc__def, 'hello').as(:x2)).sql.should == \
"SELECT test(`abc`.`def`, 'hello') AS `x2` FROM `items`"
@d.insert_sql(:value => 333).should == \
'INSERT INTO `items` (`value`) VALUES (333)'
@d.insert_sql(:x => :y).should == \
'INSERT INTO `items` (`x`) VALUES (`y`)'
end
specify "should quote fields correctly when reversing the order" do
@d.quote_identifiers = true
@d.reverse_order(:name).sql.should == \
'SELECT * FROM `items` ORDER BY `name` DESC'
@d.reverse_order(:name.desc).sql.should == \
'SELECT * FROM `items` ORDER BY `name` ASC'
@d.reverse_order(:name, :test.desc).sql.should == \
'SELECT * FROM `items` ORDER BY `name` DESC, `test` ASC'
@d.reverse_order(:name.desc, :test).sql.should == \
'SELECT * FROM `items` ORDER BY `name` ASC, `test` DESC'
end
specify "should support ORDER clause in UPDATE statements" do
@d.order(:name).update_sql(:value => 1).should == \
'UPDATE items SET value = 1 ORDER BY name'
end
specify "should support LIMIT clause in UPDATE statements" do
@d.limit(10).update_sql(:value => 1).should == \
'UPDATE items SET value = 1 LIMIT 10'
end
specify "should support regexps" do
@d << {:name => 'abc', :value => 1}
@d << {:name => 'bcd', :value => 2}
@d.filter(:name => /bc/).count.should == 2
@d.filter(:name => /^bc/).count.should == 1
end
specify "should correctly literalize strings with comment backslashes in them" do
@d.delete
proc {@d << {:name => ':\\'}}.should_not raise_error
@d.first[:name].should == ':\\'
end
end
context "MySQL datasets" do
before do
@d = MYSQL_DB[:orders]
end
specify "should correctly quote column references" do
@d.quote_identifiers = true
market = 'ICE'
ack_stamp = Time.now - 15 * 60 # 15 minutes ago
@d.select(:market, :minute.sql_function(:from_unixtime.sql_function(:ack)).as(:minute)).
where{|o|(:ack.sql_number > ack_stamp) & {:market => market}}.
group_by(:minute.sql_function(:from_unixtime.sql_function(:ack))).sql.should == \
"SELECT `market`, minute(from_unixtime(`ack`)) AS `minute` FROM `orders` WHERE ((`ack` > #{@d.literal(ack_stamp)}) AND (`market` = 'ICE')) GROUP BY minute(from_unixtime(`ack`))"
end
end
context "MySQL join expressions" do
before do
@ds = MYSQL_DB[:nodes]
@ds.db.meta_def(:server_version) {50014}
end
specify "should raise error for :full_outer join requests." do
lambda{@ds.join_table(:full_outer, :nodes)}.should raise_error(Sequel::Error)
end
specify "should support natural left joins" do
@ds.join_table(:natural_left, :nodes).sql.should == \
'SELECT * FROM nodes NATURAL LEFT JOIN nodes'
end
specify "should support natural right joins" do
@ds.join_table(:natural_right, :nodes).sql.should == \
'SELECT * FROM nodes NATURAL RIGHT JOIN nodes'
end
specify "should support natural left outer joins" do
@ds.join_table(:natural_left_outer, :nodes).sql.should == \
'SELECT * FROM nodes NATURAL LEFT OUTER JOIN nodes'
end
specify "should support natural right outer joins" do
@ds.join_table(:natural_right_outer, :nodes).sql.should == \
'SELECT * FROM nodes NATURAL RIGHT OUTER JOIN nodes'
end
specify "should support natural inner joins" do
@ds.join_table(:natural_inner, :nodes).sql.should == \
'SELECT * FROM nodes NATURAL LEFT JOIN nodes'
end
specify "should support cross joins" do
@ds.join_table(:cross, :nodes).sql.should == \
'SELECT * FROM nodes CROSS JOIN nodes'
end
specify "should support cross joins as inner joins if conditions are used" do
@ds.join_table(:cross, :nodes, :id=>:id).sql.should == \
'SELECT * FROM nodes INNER JOIN nodes ON (nodes.id = nodes.id)'
end
specify "should support straight joins (force left table to be read before right)" do
@ds.join_table(:straight, :nodes).sql.should == \
'SELECT * FROM nodes STRAIGHT_JOIN nodes'
end
specify "should support natural joins on multiple tables." do
@ds.join_table(:natural_left_outer, [:nodes, :branches]).sql.should == \
'SELECT * FROM nodes NATURAL LEFT OUTER JOIN (nodes, branches)'
end
specify "should support straight joins on multiple tables." do
@ds.join_table(:straight, [:nodes,:branches]).sql.should == \
'SELECT * FROM nodes STRAIGHT_JOIN (nodes, branches)'
end
end
context "Joined MySQL dataset" do
before do
@ds = MYSQL_DB[:nodes]
end
specify "should quote fields correctly" do
@ds.quote_identifiers = true
@ds.join(:attributes, :node_id => :id).sql.should == \
"SELECT * FROM `nodes` INNER JOIN `attributes` ON (`attributes`.`node_id` = `nodes`.`id`)"
end
specify "should allow a having clause on ungrouped datasets" do
proc {@ds.having('blah')}.should_not raise_error
@ds.having('blah').sql.should == \
"SELECT * FROM nodes HAVING (blah)"
end
specify "should put a having clause before an order by clause" do
@ds.order(:aaa).having(:bbb => :ccc).sql.should == \
"SELECT * FROM nodes HAVING (bbb = ccc) ORDER BY aaa"
end
end
context "A MySQL database" do
before do
@db = MYSQL_DB
end
specify "should support add_column operations" do
@db.add_column :test2, :xyz, :text
@db[:test2].columns.should == [:name, :value, :xyz]
@db[:test2] << {:name => 'mmm', :value => 111, :xyz => '000'}
@db[:test2].first[:xyz].should == '000'
end
specify "should support drop_column operations" do
@db[:test2].columns.should == [:name, :value, :xyz]
@db.drop_column :test2, :xyz
@db[:test2].columns.should == [:name, :value]
end
specify "should support rename_column operations" do
@db[:test2].delete
@db.add_column :test2, :xyz, :text
@db[:test2] << {:name => 'mmm', :value => 111, :xyz => 'qqqq'}
@db[:test2].columns.should == [:name, :value, :xyz]
@db.rename_column :test2, :xyz, :zyx, :type => :text
@db[:test2].columns.should == [:name, :value, :zyx]
@db[:test2].first[:zyx].should == 'qqqq'
end
specify "should support rename_column operations with types like varchar(255)" do
@db[:test2].delete
@db.add_column :test2, :tre, :text
@db[:test2] << {:name => 'mmm', :value => 111, :tre => 'qqqq'}
@db[:test2].columns.should == [:name, :value, :zyx, :tre]
@db.rename_column :test2, :tre, :ert, :type => :varchar, :size=>255
@db[:test2].columns.should == [:name, :value, :zyx, :ert]
@db[:test2].first[:ert].should == 'qqqq'
end
specify "should support set_column_type operations" do
@db.add_column :test2, :xyz, :float
@db[:test2].delete
@db[:test2] << {:name => 'mmm', :value => 111, :xyz => 56.78}
@db.set_column_type :test2, :xyz, :integer
@db[:test2].first[:xyz].should == 57
end
specify "should support add_index" do
@db.add_index :test2, :value
end
specify "should support drop_index" do
@db.drop_index :test2, :value
end
specify "should support add_foreign_key" do
@db.alter_table :test2 do
add_foreign_key :value2, :test2, :key=>:value
end
@db[:test2].columns.should == [:name, :value, :zyx, :ert, :xyz, :value2]
end
end
context "A MySQL database with table options" do
before do
@options = {:engine=>'MyISAM', :charset=>'latin1', :collate => 'latin1_swedish_ci'}
Sequel::MySQL.default_engine = 'InnoDB'
Sequel::MySQL.default_charset = 'utf8'
Sequel::MySQL.default_collate = 'utf8_general_ci'
@db = MYSQL_DB
@db.drop_table(:items) rescue nil
MYSQL_DB.sqls.clear
end
after do
@db.drop_table(:items) rescue nil
Sequel::MySQL.default_engine = nil
Sequel::MySQL.default_charset = nil
Sequel::MySQL.default_collate = nil
end
specify "should allow to pass custom options (engine, charset, collate) for table creation" do
@db.create_table(:items, @options){Integer :size; text :name}
@db.sqls.should == ["CREATE TABLE items (size integer, name text) ENGINE=MyISAM DEFAULT CHARSET=latin1 DEFAULT COLLATE=latin1_swedish_ci"]
end
specify "should use default options if specified (engine, charset, collate) for table creation" do
@db.create_table(:items){Integer :size; text :name}
@db.sqls.should == ["CREATE TABLE items (size integer, name text) ENGINE=InnoDB DEFAULT CHARSET=utf8 DEFAULT COLLATE=utf8_general_ci"]
end
specify "should not use default if option has a nil value" do
@db.create_table(:items, :engine=>nil, :charset=>nil, :collate=>nil){Integer :size; text :name}
@db.sqls.should == ["CREATE TABLE items (size integer, name text)"]
end
end
context "A MySQL database" do
before do
@db = MYSQL_DB
@db.drop_table(:items) rescue nil
MYSQL_DB.sqls.clear
end
after do
@db.drop_table(:items) rescue nil
end
specify "should support defaults for boolean columns" do
@db.create_table(:items){TrueClass :active1, :default=>true; FalseClass :active2, :default => false}
@db.sqls.should == ["CREATE TABLE items (active1 tinyint(1) DEFAULT 1, active2 tinyint(1) DEFAULT 0)"]
end
specify "should correctly format CREATE TABLE statements with foreign keys" do
@db.create_table(:items){Integer :id; foreign_key :p_id, :items, :key => :id, :null => false, :on_delete => :cascade}
@db.sqls.should == ["CREATE TABLE items (id integer, p_id integer NOT NULL, FOREIGN KEY (p_id) REFERENCES items(id) ON DELETE CASCADE)"]
end
specify "should correctly format ALTER TABLE statements with foreign keys" do
@db.create_table(:items){Integer :id}
@db.alter_table(:items){add_foreign_key :p_id, :users, :key => :id, :null => false, :on_delete => :cascade}
@db.sqls.should == ["CREATE TABLE items (id integer)", "ALTER TABLE items ADD COLUMN p_id integer NOT NULL", "ALTER TABLE items ADD FOREIGN KEY (p_id) REFERENCES users(id) ON DELETE CASCADE"]
end
specify "should have rename_column support keep existing options" do
@db.create_table(:items){String :id, :null=>false, :default=>'blah'}
@db.alter_table(:items){rename_column :id, :nid}
@db.sqls.should == ["CREATE TABLE items (id varchar(255) NOT NULL DEFAULT 'blah')", "DESCRIBE items", "ALTER TABLE items CHANGE COLUMN id nid varchar(255) NOT NULL DEFAULT 'blah'"]
@db[:items].insert
@db[:items].all.should == [{:nid=>'blah'}]
proc{@db[:items].insert(:nid=>nil)}.should raise_error(Sequel::DatabaseError)
end
specify "should have set_column_type support keep existing options" do
@db.create_table(:items){Integer :id, :null=>false, :default=>5}
@db.alter_table(:items){set_column_type :id, Bignum}
@db.sqls.should == ["CREATE TABLE items (id integer NOT NULL DEFAULT 5)", "DESCRIBE items", "ALTER TABLE items CHANGE COLUMN id id bigint NOT NULL DEFAULT 5"]
@db[:items].insert
@db[:items].all.should == [{:id=>5}]
proc{@db[:items].insert(:id=>nil)}.should raise_error(Sequel::DatabaseError)
@db[:items].delete
@db[:items].insert(2**40)
@db[:items].all.should == [{:id=>2**40}]
end
specify "should have set_column_default support keep existing options" do
@db.create_table(:items){Integer :id, :null=>false, :default=>5}
@db.alter_table(:items){set_column_default :id, 6}
@db.sqls.should == ["CREATE TABLE items (id integer NOT NULL DEFAULT 5)", "DESCRIBE items", "ALTER TABLE items CHANGE COLUMN id id int(11) NOT NULL DEFAULT 6"]
@db[:items].insert
@db[:items].all.should == [{:id=>6}]
proc{@db[:items].insert(:id=>nil)}.should raise_error(Sequel::DatabaseError)
end
specify "should have set_column_allow_null support keep existing options" do
@db.create_table(:items){Integer :id, :null=>false, :default=>5}
@db.alter_table(:items){set_column_allow_null :id, true}
@db.sqls.should == ["CREATE TABLE items (id integer NOT NULL DEFAULT 5)", "DESCRIBE items", "ALTER TABLE items CHANGE COLUMN id id int(11) NULL DEFAULT 5"]
@db[:items].insert
@db[:items].all.should == [{:id=>5}]
proc{@db[:items].insert(:id=>nil)}.should_not
end
specify "should accept repeated raw sql statements using Database#<<" do
@db.create_table(:items){String :name; Integer :value}
@db << 'DELETE FROM items'
@db[:items].count.should == 0
@db << "INSERT INTO items (name, value) VALUES ('tutu', 1234)"
@db[:items].first.should == {:name => 'tutu', :value => 1234}
@db << 'DELETE FROM items'
@db[:items].first.should == nil
end
end
# Socket tests should only be run if the MySQL server is on localhost
if %w'localhost 127.0.0.1 ::1'.include?(MYSQL_URI.host) and MYSQL_DB.class.adapter_scheme == :mysql
context "A MySQL database" do
specify "should accept a socket option" do
db = Sequel.mysql(MYSQL_DB.opts[:database], :host => 'localhost', :user => MYSQL_DB.opts[:user], :password => MYSQL_DB.opts[:password], :socket => MYSQL_SOCKET_FILE)
proc {db.test_connection}.should_not raise_error
end
specify "should accept a socket option without host option" do
db = Sequel.mysql(MYSQL_DB.opts[:database], :user => MYSQL_DB.opts[:user], :password => MYSQL_DB.opts[:password], :socket => MYSQL_SOCKET_FILE)
proc {db.test_connection}.should_not raise_error
end
specify "should fail to connect with invalid socket" do
db = Sequel.mysql(MYSQL_DB.opts[:database], :user => MYSQL_DB.opts[:user], :password => MYSQL_DB.opts[:password], :socket =>'blah')
proc {db.test_connection}.should raise_error
end
end
end
context "A grouped MySQL dataset" do
before do
MYSQL_DB[:test2].delete
MYSQL_DB[:test2] << {:name => '11', :value => 10}
MYSQL_DB[:test2] << {:name => '11', :value => 20}
MYSQL_DB[:test2] << {:name => '11', :value => 30}
MYSQL_DB[:test2] << {:name => '12', :value => 10}
MYSQL_DB[:test2] << {:name => '12', :value => 20}
MYSQL_DB[:test2] << {:name => '13', :value => 10}
end
specify "should return the correct count for raw sql query" do
ds = MYSQL_DB["select name FROM test2 WHERE name = '11' GROUP BY name"]
ds.count.should == 1
end
specify "should return the correct count for a normal dataset" do
ds = MYSQL_DB[:test2].select(:name).where(:name => '11').group(:name)
ds.count.should == 1
end
end
context "A MySQL database" do
before do
@db = MYSQL_DB
@db.drop_table(:posts) rescue nil
@db.sqls.clear
end
after do
@db.drop_table(:posts) rescue nil
end
specify "should support fulltext indexes and full_text_search" do
@db.create_table(:posts){text :title; text :body; full_text_index :title; full_text_index [:title, :body]}
@db.sqls.should == [
"CREATE TABLE posts (title text, body text)",
"CREATE FULLTEXT INDEX posts_title_index ON posts (title)",
"CREATE FULLTEXT INDEX posts_title_body_index ON posts (title, body)"
]
@db[:posts].insert(:title=>'ruby rails', :body=>'y')
@db[:posts].insert(:title=>'sequel', :body=>'ruby')
@db[:posts].insert(:title=>'ruby scooby', :body=>'x')
@db.sqls.clear
@db[:posts].full_text_search(:title, 'rails').all.should == [{:title=>'ruby rails', :body=>'y'}]
@db[:posts].full_text_search([:title, :body], ['sequel', 'ruby']).all.should == [{:title=>'sequel', :body=>'ruby'}]
@db[:posts].full_text_search(:title, '+ruby -rails', :boolean => true).all.should == [{:title=>'ruby scooby', :body=>'x'}]
@db.sqls.should == [
"SELECT * FROM posts WHERE (MATCH (title) AGAINST ('rails'))",
"SELECT * FROM posts WHERE (MATCH (title, body) AGAINST ('sequel ruby'))",
"SELECT * FROM posts WHERE (MATCH (title) AGAINST ('+ruby -rails' IN BOOLEAN MODE))"]
end
specify "should support spatial indexes" do
@db.create_table(:posts){point :geom, :null=>false; spatial_index [:geom]}
@db.sqls.should == [
"CREATE TABLE posts (geom point NOT NULL)",
"CREATE SPATIAL INDEX posts_geom_index ON posts (geom)"
]
end
specify "should support indexes with index type" do
@db.create_table(:posts){Integer :id; index :id, :type => :btree}
@db.sqls.should == [
"CREATE TABLE posts (id integer)",
"CREATE INDEX posts_id_index USING btree ON posts (id)"
]
end
specify "should support unique indexes with index type" do
@db.create_table(:posts){Integer :id; index :id, :type => :btree, :unique => true}
@db.sqls.should == [
"CREATE TABLE posts (id integer)",
"CREATE UNIQUE INDEX posts_id_index USING btree ON posts (id)"
]
end
specify "should not dump partial indexes" do
@db.create_table(:posts){text :id}
@db << "CREATE INDEX posts_id_index ON posts (id(10))"
@db.indexes(:posts).should == {}
end
end
context "MySQL::Dataset#insert and related methods" do
before do
MYSQL_DB.create_table(:items){String :name; Integer :value}
@d = MYSQL_DB[:items]
MYSQL_DB.sqls.clear
end
after do
MYSQL_DB.drop_table(:items)
end
specify "#insert should insert record with default values when no arguments given" do
@d.insert
MYSQL_DB.sqls.should == [
"INSERT INTO items () VALUES ()"
]
@d.all.should == [
{:name => nil, :value => nil}
]
end
specify "#insert should insert record with default values when empty hash given" do
@d.insert({})
MYSQL_DB.sqls.should == [
"INSERT INTO items () VALUES ()"
]
@d.all.should == [
{:name => nil, :value => nil}
]
end
specify "#insert should insert record with default values when empty array given" do
@d.insert []
MYSQL_DB.sqls.should == [
"INSERT INTO items () VALUES ()"
]
@d.all.should == [
{:name => nil, :value => nil}
]
end
specify "#on_duplicate_key_update should work with regular inserts" do
MYSQL_DB.add_index :items, :name, :unique=>true
MYSQL_DB.sqls.clear
@d.insert(:name => 'abc', :value => 1)
@d.on_duplicate_key_update(:name, :value => 6).insert(:name => 'abc', :value => 1)
@d.on_duplicate_key_update(:name, :value => 6).insert(:name => 'def', :value => 2)
MYSQL_DB.sqls.length.should == 3
MYSQL_DB.sqls[0].should =~ /\AINSERT INTO items \((name|value), (name|value)\) VALUES \(('abc'|1), (1|'abc')\)\z/
MYSQL_DB.sqls[1].should =~ /\AINSERT INTO items \((name|value), (name|value)\) VALUES \(('abc'|1), (1|'abc')\) ON DUPLICATE KEY UPDATE name=VALUES\(name\), value=6\z/
MYSQL_DB.sqls[2].should =~ /\AINSERT INTO items \((name|value), (name|value)\) VALUES \(('def'|2), (2|'def')\) ON DUPLICATE KEY UPDATE name=VALUES\(name\), value=6\z/
@d.all.should == [{:name => 'abc', :value => 6}, {:name => 'def', :value => 2}]
end
specify "#multi_insert should insert multiple records in a single statement" do
@d.multi_insert([{:name => 'abc'}, {:name => 'def'}])
MYSQL_DB.sqls.should == [
SQL_BEGIN,
"INSERT INTO items (name) VALUES ('abc'), ('def')",
SQL_COMMIT
]
@d.all.should == [
{:name => 'abc', :value => nil}, {:name => 'def', :value => nil}
]
end
specify "#multi_insert should split the list of records into batches if :commit_every option is given" do
@d.multi_insert([{:value => 1}, {:value => 2}, {:value => 3}, {:value => 4}],
:commit_every => 2)
MYSQL_DB.sqls.should == [
SQL_BEGIN,
"INSERT INTO items (value) VALUES (1), (2)",
SQL_COMMIT,
SQL_BEGIN,
"INSERT INTO items (value) VALUES (3), (4)",
SQL_COMMIT
]
@d.all.should == [
{:name => nil, :value => 1},
{:name => nil, :value => 2},
{:name => nil, :value => 3},
{:name => nil, :value => 4}
]
end
specify "#multi_insert should split the list of records into batches if :slice option is given" do
@d.multi_insert([{:value => 1}, {:value => 2}, {:value => 3}, {:value => 4}],
:slice => 2)
MYSQL_DB.sqls.should == [
SQL_BEGIN,
"INSERT INTO items (value) VALUES (1), (2)",
SQL_COMMIT,
SQL_BEGIN,
"INSERT INTO items (value) VALUES (3), (4)",
SQL_COMMIT
]
@d.all.should == [
{:name => nil, :value => 1},
{:name => nil, :value => 2},
{:name => nil, :value => 3},
{:name => nil, :value => 4}
]
end
specify "#import should support inserting using columns and values arrays" do
@d.import([:name, :value], [['abc', 1], ['def', 2]])
MYSQL_DB.sqls.should == [
SQL_BEGIN,
"INSERT INTO items (name, value) VALUES ('abc', 1), ('def', 2)",
SQL_COMMIT
]
@d.all.should == [
{:name => 'abc', :value => 1},
{:name => 'def', :value => 2}
]
end
specify "#insert_ignore should add the IGNORE keyword when inserting" do
@d.insert_ignore.multi_insert([{:name => 'abc'}, {:name => 'def'}])
MYSQL_DB.sqls.should == [
SQL_BEGIN,
"INSERT IGNORE INTO items (name) VALUES ('abc'), ('def')",
SQL_COMMIT
]
@d.all.should == [
{:name => 'abc', :value => nil}, {:name => 'def', :value => nil}
]
end
specify "#insert_ignore should add the IGNORE keyword for single inserts" do
@d.insert_ignore.insert(:name => 'ghi')
MYSQL_DB.sqls.should == ["INSERT IGNORE INTO items (name) VALUES ('ghi')"]
@d.all.should == [{:name => 'ghi', :value => nil}]
end
specify "#on_duplicate_key_update should add the ON DUPLICATE KEY UPDATE and ALL columns when no args given" do
@d.on_duplicate_key_update.import([:name,:value], [['abc', 1], ['def',2]])
MYSQL_DB.sqls.should == [
"SELECT * FROM items LIMIT 1",
SQL_BEGIN,
"INSERT INTO items (name, value) VALUES ('abc', 1), ('def', 2) ON DUPLICATE KEY UPDATE name=VALUES(name), value=VALUES(value)",
SQL_COMMIT
]
@d.all.should == [
{:name => 'abc', :value => 1}, {:name => 'def', :value => 2}
]
end
specify "#on_duplicate_key_update should add the ON DUPLICATE KEY UPDATE and columns specified when args are given" do
@d.on_duplicate_key_update(:value).import([:name,:value],
[['abc', 1], ['def',2]]
)
MYSQL_DB.sqls.should == [
SQL_BEGIN,
"INSERT INTO items (name, value) VALUES ('abc', 1), ('def', 2) ON DUPLICATE KEY UPDATE value=VALUES(value)",
SQL_COMMIT
]
@d.all.should == [
{:name => 'abc', :value => 1}, {:name => 'def', :value => 2}
]
end
end
context "MySQL::Dataset#replace" do
before do
MYSQL_DB.create_table(:items){Integer :id, :unique=>true; Integer :value}
@d = MYSQL_DB[:items]
MYSQL_DB.sqls.clear
end
after do
MYSQL_DB.drop_table(:items)
end
specify "should create a record if the condition is not met" do
@d.replace(:id => 111, :value => 333)
@d.all.should == [{:id => 111, :value => 333}]
end
specify "should update a record if the condition is met" do
@d << {:id => 111}
@d.all.should == [{:id => 111, :value => nil}]
@d.replace(:id => 111, :value => 333)
@d.all.should == [{:id => 111, :value => 333}]
end
end
context "MySQL::Dataset#complex_expression_sql" do
before do
@d = MYSQL_DB.dataset
end
specify "should handle pattern matches correctly" do
@d.literal(:x.like('a')).should == "(x LIKE BINARY 'a')"
@d.literal(~:x.like('a')).should == "(x NOT LIKE BINARY 'a')"
@d.literal(:x.ilike('a')).should == "(x LIKE 'a')"
@d.literal(~:x.ilike('a')).should == "(x NOT LIKE 'a')"
@d.literal(:x.like(/a/)).should == "(x REGEXP BINARY 'a')"
@d.literal(~:x.like(/a/)).should == "(x NOT REGEXP BINARY 'a')"
@d.literal(:x.like(/a/i)).should == "(x REGEXP 'a')"
@d.literal(~:x.like(/a/i)).should == "(x NOT REGEXP 'a')"
end
specify "should handle string concatenation with CONCAT if more than one record" do
@d.literal([:x, :y].sql_string_join).should == "CONCAT(x, y)"
@d.literal([:x, :y].sql_string_join(' ')).should == "CONCAT(x, ' ', y)"
@d.literal([:x.sql_function(:y), 1, 'z'.lit].sql_string_join(:y.sql_subscript(1))).should == "CONCAT(x(y), y[1], '1', y[1], z)"
end
specify "should handle string concatenation as simple string if just one record" do
@d.literal([:x].sql_string_join).should == "x"
@d.literal([:x].sql_string_join(' ')).should == "x"
end
end
unless MYSQL_DB.class.adapter_scheme == :do
context "MySQL Stored Procedures" do
before do
MYSQL_DB.create_table(:items){Integer :id; Integer :value}
@d = MYSQL_DB[:items]
MYSQL_DB.sqls.clear
end
after do
MYSQL_DB.drop_table(:items)
MYSQL_DB.execute('DROP PROCEDURE test_sproc')
end
specify "should be callable on the database object" do
MYSQL_DB.execute('CREATE PROCEDURE test_sproc() BEGIN DELETE FROM items; END')
MYSQL_DB[:items].delete
MYSQL_DB[:items].insert(:value=>1)
MYSQL_DB[:items].count.should == 1
MYSQL_DB.call_sproc(:test_sproc)
MYSQL_DB[:items].count.should == 0
end
specify "should be callable on the dataset object" do
MYSQL_DB.execute('CREATE PROCEDURE test_sproc(a INTEGER) BEGIN SELECT *, a AS b FROM items; END')
MYSQL_DB[:items].delete
@d = MYSQL_DB[:items]
@d.call_sproc(:select, :test_sproc, 3).should == []
@d.insert(:value=>1)
@d.call_sproc(:select, :test_sproc, 4).should == [{:id=>nil, :value=>1, :b=>4}]
@d.row_proc = proc{|r| r.keys.each{|k| r[k] *= 2 if r[k].is_a?(Integer)}; r}
@d.call_sproc(:select, :test_sproc, 3).should == [{:id=>nil, :value=>2, :b=>6}]
end
specify "should be callable on the dataset object with multiple arguments" do
MYSQL_DB.execute('CREATE PROCEDURE test_sproc(a INTEGER, c INTEGER) BEGIN SELECT *, a AS b, c AS d FROM items; END')
MYSQL_DB[:items].delete
@d = MYSQL_DB[:items]
@d.call_sproc(:select, :test_sproc, 3, 4).should == []
@d.insert(:value=>1)
@d.call_sproc(:select, :test_sproc, 4, 5).should == [{:id=>nil, :value=>1, :b=>4, :d=>5}]
@d.row_proc = proc{|r| r.keys.each{|k| r[k] *= 2 if r[k].is_a?(Integer)}; r}
@d.call_sproc(:select, :test_sproc, 3, 4).should == [{:id=>nil, :value=>2, :b=>6, :d => 8}]
end
end
end
if MYSQL_DB.class.adapter_scheme == :mysql
context "MySQL bad date/time conversions" do
after do
Sequel::MySQL.convert_invalid_date_time = false
end
specify "should raise an exception when a bad date/time is used and convert_invalid_date_time is false" do
Sequel::MySQL.convert_invalid_date_time = false
proc{MYSQL_DB["SELECT CAST('0000-00-00' AS date)"].single_value}.should raise_error(Sequel::InvalidValue)
proc{MYSQL_DB["SELECT CAST('0000-00-00 00:00:00' AS datetime)"].single_value}.should raise_error(Sequel::InvalidValue)
proc{MYSQL_DB["SELECT CAST('25:00:00' AS time)"].single_value}.should raise_error(Sequel::InvalidValue)
end
specify "should not use a nil value bad date/time is used and convert_invalid_date_time is nil or :nil" do
Sequel::MySQL.convert_invalid_date_time = nil
MYSQL_DB["SELECT CAST('0000-00-00' AS date)"].single_value.should == nil
MYSQL_DB["SELECT CAST('0000-00-00 00:00:00' AS datetime)"].single_value.should == nil
MYSQL_DB["SELECT CAST('25:00:00' AS time)"].single_value.should == nil
Sequel::MySQL.convert_invalid_date_time = :nil
MYSQL_DB["SELECT CAST('0000-00-00' AS date)"].single_value.should == nil
MYSQL_DB["SELECT CAST('0000-00-00 00:00:00' AS datetime)"].single_value.should == nil
MYSQL_DB["SELECT CAST('25:00:00' AS time)"].single_value.should == nil
end
specify "should not use a nil value bad date/time is used and convert_invalid_date_time is :string" do
Sequel::MySQL.convert_invalid_date_time = :string
MYSQL_DB["SELECT CAST('0000-00-00' AS date)"].single_value.should == '0000-00-00'
MYSQL_DB["SELECT CAST('0000-00-00 00:00:00' AS datetime)"].single_value.should == '0000-00-00 00:00:00'
MYSQL_DB["SELECT CAST('25:00:00' AS time)"].single_value.should == '25:00:00'
end
end
context "MySQL multiple result sets" do
before do
MYSQL_DB.create_table!(:a){Integer :a}
MYSQL_DB.create_table!(:b){Integer :b}
@ds = MYSQL_DB['SELECT * FROM a; SELECT * FROM b']
MYSQL_DB[:a].insert(10)
MYSQL_DB[:a].insert(15)
MYSQL_DB[:b].insert(20)
MYSQL_DB[:b].insert(25)
end
after do
MYSQL_DB.drop_table(:a, :b)
end
specify "should combine all results by default" do
@ds.all.should == [{:a=>10}, {:a=>15}, {:b=>20}, {:b=>25}]
end
specify "should split results returned into arrays if split_multiple_result_sets is used" do
@ds.split_multiple_result_sets.all.should == [[{:a=>10}, {:a=>15}], [{:b=>20}, {:b=>25}]]
end
specify "should have regular row_procs work when splitting multiple result sets" do
@ds.row_proc = proc{|x| x[x.keys.first] *= 2; x}
@ds.split_multiple_result_sets.all.should == [[{:a=>20}, {:a=>30}], [{:b=>40}, {:b=>50}]]
end
specify "should use the columns from the first result set when splitting result sets" do
@ds.split_multiple_result_sets.columns.should == [:a]
end
specify "should not allow graphing a dataset that splits multiple statements" do
proc{@ds.split_multiple_result_sets.graph(:b, :b=>:a)}.should raise_error(Sequel::Error)
end
specify "should not allow splitting a graphed dataset" do
proc{MYSQL_DB[:a].graph(:b, :b=>:a).split_multiple_result_sets}.should raise_error(Sequel::Error)
end
end
end