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
|
require_relative "spec_helper"
describe "Dataset#and" do
before do
@dataset = Sequel.mock.dataset.from(:test).extension(:sequel_4_dataset_methods)
@d1 = @dataset.where(:x => 1)
end
it "should add a WHERE filter if none exists" do
@dataset.and(:a => 1).sql.must_equal 'SELECT * FROM test WHERE (a = 1)'
end
it "should add an expression to the where clause" do
@d1.and(:y => 2).sql.must_equal 'SELECT * FROM test WHERE ((x = 1) AND (y = 2))'
end
it "should accept placeholder literal string filters" do
@d1.and(Sequel.lit('y > ?', 2)).sql.must_equal 'SELECT * FROM test WHERE ((x = 1) AND (y > 2))'
end
it "should accept expression filters" do
@d1.and(Sequel.expr(:yy) > 3).sql.must_equal 'SELECT * FROM test WHERE ((x = 1) AND (yy > 3))'
end
it "should accept string filters with placeholders" do
@d1.extension(:auto_literal_strings).and('y > ?', 2).sql.must_equal 'SELECT * FROM test WHERE ((x = 1) AND (y > 2))'
end
it "should accept blocks passed to filter" do
@d1.and{yy > 3}.sql.must_equal 'SELECT * FROM test WHERE ((x = 1) AND (yy > 3))'
end
it "should correctly add parens to give predictable results" do
@d1.or(:y => 2).and(:z => 3).sql.must_equal 'SELECT * FROM test WHERE (((x = 1) OR (y = 2)) AND (z = 3))'
@d1.and(:y => 2).or(:z => 3).sql.must_equal 'SELECT * FROM test WHERE (((x = 1) AND (y = 2)) OR (z = 3))'
end
end
describe "Dataset#exclude_where" do
before do
@dataset = Sequel.mock.dataset.from(:test).extension(:sequel_4_dataset_methods)
end
it "should correctly negate the expression and add it to the where clause" do
@dataset.exclude_where(:region=>'Asia').sql.must_equal "SELECT * FROM test WHERE (region != 'Asia')"
@dataset.exclude_where(:region=>'Asia').exclude_where(:region=>'NA').sql.must_equal "SELECT * FROM test WHERE ((region != 'Asia') AND (region != 'NA'))"
end
it "should affect the where clause even if having clause is already used" do
@dataset.group_and_count(:name).having{count > 2}.exclude_where(:region=>'Asia').sql.
must_equal "SELECT name, count(*) AS count FROM test WHERE (region != 'Asia') GROUP BY name HAVING (count > 2)"
end
end
describe "Dataset#interval" do
before do
@db = Sequel.mock(:fetch=>{:v => 1234}).extension(:sequel_4_dataset_methods)
@ds = @db[:test].freeze
end
it "should generate the correct SQL statement" do
5.times do
@ds.interval(:stamp)
@db.sqls.must_equal ["SELECT (max(stamp) - min(stamp)) AS interval FROM test LIMIT 1"]
end
@ds.filter(Sequel.expr(:price) > 100).interval(:stamp)
@db.sqls.must_equal ["SELECT (max(stamp) - min(stamp)) AS interval FROM test WHERE (price > 100) LIMIT 1"]
end
it "should use a subselect for the same conditions as count" do
ds = @ds.order(:stamp).limit(5)
5.times do
ds.interval(:stamp).must_equal 1234
@db.sqls.must_equal ['SELECT (max(stamp) - min(stamp)) AS interval FROM (SELECT * FROM test ORDER BY stamp LIMIT 5) AS t1 LIMIT 1']
end
end
it "should accept virtual row blocks" do
5.times do
@ds.interval{a(b)}
@db.sqls.must_equal ["SELECT (max(a(b)) - min(a(b))) AS interval FROM test LIMIT 1"]
end
end
end
describe "Dataset#range" do
before do
@db = Sequel.mock(:fetch=>{:v1 => 1, :v2 => 10}).extension(:sequel_4_dataset_methods)
@ds = @db[:test].freeze
end
it "should generate a correct SQL statement" do
5.times do
@ds.range(:stamp)
@db.sqls.must_equal ["SELECT min(stamp) AS v1, max(stamp) AS v2 FROM test LIMIT 1"]
end
@ds.filter(Sequel.expr(:price) > 100).range(:stamp)
@db.sqls.must_equal ["SELECT min(stamp) AS v1, max(stamp) AS v2 FROM test WHERE (price > 100) LIMIT 1"]
end
it "should return nil if no row matches" do
@db.fetch = []
5.times do
@ds.range(:tryme).must_be_nil
end
end
it "should return a range object" do
5.times do
@ds.range(:tryme).must_equal(1..10)
end
end
it "should use a subselect for the same conditions as count" do
@ds.order(:stamp).limit(5).range(:stamp).must_equal(1..10)
@db.sqls.must_equal ['SELECT min(stamp) AS v1, max(stamp) AS v2 FROM (SELECT * FROM test ORDER BY stamp LIMIT 5) AS t1 LIMIT 1']
end
it "should accept virtual row blocks" do
5.times do
@ds.range{a(b)}
@db.sqls.must_equal ["SELECT min(a(b)) AS v1, max(a(b)) AS v2 FROM test LIMIT 1"]
end
end
end
|
