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
|
require_relative "spec_helper"
asd = begin
require 'active_support/duration'
true
rescue LoadError
warn "Skipping some tests of date_arithmetic extension: can't load active_support/duration"
false
end
Sequel.extension :date_arithmetic
describe "date_arithmetic extension" do
dbf = lambda do |db_type|
db = Sequel.connect("mock://#{db_type}")
db.extension :date_arithmetic
db
end
before do
@h0 = {:days=>0}
@h1 = {:days=>1, :years=>nil, :hours=>0}
@h2 = {:years=>1, :months=>1, :days=>1, :hours=>1, :minutes=>1, :seconds=>1}
end
it "should have Sequel.date_add with an interval hash return an appropriate Sequel::SQL::DateAdd expression" do
da = Sequel.date_add(:a, :days=>1)
da.must_be_kind_of(Sequel::SQL::DateAdd)
da.expr.must_equal :a
da.interval.must_equal(:days=>1)
Sequel.date_add(:a, :years=>1, :months=>2, :days=>3, :hours=>1, :minutes=>1, :seconds=>1).interval.must_equal(:years=>1, :months=>2, :days=>3, :hours=>1, :minutes=>1, :seconds=>1)
end
it "should have Sequel.date_sub with an interval hash return an appropriate Sequel::SQL::DateAdd expression" do
da = Sequel.date_sub(:a, :days=>1)
da.must_be_kind_of(Sequel::SQL::DateAdd)
da.expr.must_equal :a
da.interval.must_equal(:days=>-1)
Sequel.date_sub(:a, :years=>1, :months=>2, :days=>3, :hours=>1, :minutes=>1, :seconds=>1).interval.must_equal(:years=>-1, :months=>-2, :days=>-3, :hours=>-1, :minutes=>-1, :seconds=>-1)
end
it "should have Sequel.date_* with an interval hash handle nil values" do
Sequel.date_sub(:a, :days=>1, :hours=>nil).interval.must_equal(:days=>-1)
end
it "should raise an error if given string values in an interval hash" do
lambda{Sequel.date_add(:a, :days=>'1')}.must_raise(Sequel::InvalidValue)
end
if asd
it "should have Sequel.date_add with an ActiveSupport::Duration return an appropriate Sequel::SQL::DateAdd expression" do
da = Sequel.date_add(:a, ActiveSupport::Duration.new(1, [[:days, 1]]))
da.must_be_kind_of(Sequel::SQL::DateAdd)
da.expr.must_equal :a
da.interval.must_equal(:days=>1)
Sequel.date_add(:a, ActiveSupport::Duration.new(1, [[:years, 1], [:months, 1], [:days, 1], [:minutes, 61], [:seconds, 1]])).interval.must_equal(:years=>1, :months=>1, :days=>1, :minutes=>61, :seconds=>1)
end
it "should have Sequel.date_sub with an ActiveSupport::Duration return an appropriate Sequel::SQL::DateAdd expression" do
da = Sequel.date_sub(:a, ActiveSupport::Duration.new(1, [[:days, 1]]))
da.must_be_kind_of(Sequel::SQL::DateAdd)
da.expr.must_equal :a
da.interval.must_equal(:days=>-1)
Sequel.date_sub(:a, ActiveSupport::Duration.new(1, [[:years, 1], [:months, 1], [:days, 1], [:minutes, 61], [:seconds, 1]])).interval.must_equal(:years=>-1, :months=>-1, :days=>-1, :minutes=>-61, :seconds=>-1)
end
end
it "should use existing method" do
db = Sequel.mock
db.extend_datasets do
def date_add_sql_append(sql, da)
interval = String.new
each_valid_interval_unit(da.interval, Sequel::SQL::DateAdd::DatasetMethods::DEF_DURATION_UNITS) do |value, sql_unit|
interval << "#{value} #{sql_unit} "
end
literal_append(sql, Sequel.function(:da, da.expr, interval))
end
end
db.extension :date_arithmetic
db.literal(Sequel.date_add(:a, @h0)).must_equal "da(a, '')"
db.literal(Sequel.date_add(:a, @h1)).must_equal "da(a, '1 days ')"
db.literal(Sequel.date_add(:a, @h2)).must_equal "da(a, '1 years 1 months 1 days 1 hours 1 minutes 1 seconds ')"
end
it "should correctly literalize on Postgres" do
db = dbf.call(:postgres).dataset.with_quote_identifiers(false)
db.literal(Sequel.date_add(:a, @h0)).must_equal "CAST(a AS timestamp)"
db.literal(Sequel.date_add(:a, @h1)).must_equal "(CAST(a AS timestamp) + CAST('1 days ' AS interval))"
db.literal(Sequel.date_add(:a, @h2)).must_equal "(CAST(a AS timestamp) + CAST('1 years 1 months 1 days 1 hours 1 minutes 1 seconds ' AS interval))"
db.literal(Sequel.date_add(:a, @h0, :cast=>:timestamptz)).must_equal "CAST(a AS timestamptz)"
db.literal(Sequel.date_sub(:a, @h0, :cast=>:timestamptz)).must_equal "CAST(a AS timestamptz)"
db.literal(Sequel.date_add(:a, @h2, :cast=>:timestamptz)).must_equal "(CAST(a AS timestamptz) + CAST('1 years 1 months 1 days 1 hours 1 minutes 1 seconds ' AS interval))"
end
it "should correctly literalize on SQLite" do
db = dbf.call(:sqlite).dataset.with_quote_identifiers(false)
db.literal(Sequel.date_add(:a, @h0)).must_equal "datetime(a)"
db.literal(Sequel.date_add(:a, @h1)).must_equal "datetime(a, '1 days')"
db.literal(Sequel.date_add(:a, @h2)).must_equal "datetime(a, '1 years', '1 months', '1 days', '1 hours', '1 minutes', '1 seconds')"
end
it "should correctly literalize on MySQL" do
db = dbf.call(:mysql).dataset.with_quote_identifiers(false)
db.literal(Sequel.date_add(:a, @h0)).must_equal "CAST(a AS DATETIME)"
db.literal(Sequel.date_add(:a, @h1)).must_equal "DATE_ADD(a, INTERVAL 1 DAY)"
db.literal(Sequel.date_add(:a, @h2)).must_equal "DATE_ADD(DATE_ADD(DATE_ADD(DATE_ADD(DATE_ADD(DATE_ADD(a, INTERVAL 1 YEAR), INTERVAL 1 MONTH), INTERVAL 1 DAY), INTERVAL 1 HOUR), INTERVAL 1 MINUTE), INTERVAL 1 SECOND)"
db.literal(Sequel.date_add(:a, @h0, :cast=>:timestamp)).must_equal "CAST(a AS timestamp)"
end
it "should correctly literalize on HSQLDB" do
db = Sequel.mock
def db.database_type; :hsqldb end
db.extension :date_arithmetic
db.literal(Sequel.date_add(:a, @h0)).must_equal "CAST(CAST(a AS timestamp) AS timestamp)"
db.literal(Sequel.date_add(:a, @h1)).must_equal "DATE_ADD(CAST(a AS timestamp), INTERVAL 1 DAY)"
db.literal(Sequel.date_add(:a, @h2)).must_equal "DATE_ADD(DATE_ADD(DATE_ADD(DATE_ADD(DATE_ADD(DATE_ADD(CAST(a AS timestamp), INTERVAL 1 YEAR), INTERVAL 1 MONTH), INTERVAL 1 DAY), INTERVAL 1 HOUR), INTERVAL 1 MINUTE), INTERVAL 1 SECOND)"
db.literal(Sequel.date_add(:a, @h0, :cast=>:datetime)).must_equal "CAST(CAST(a AS datetime) AS datetime)"
db.literal(Sequel.date_add(:a, @h2, :cast=>:datetime)).must_equal "DATE_ADD(DATE_ADD(DATE_ADD(DATE_ADD(DATE_ADD(DATE_ADD(CAST(a AS datetime), INTERVAL 1 YEAR), INTERVAL 1 MONTH), INTERVAL 1 DAY), INTERVAL 1 HOUR), INTERVAL 1 MINUTE), INTERVAL 1 SECOND)"
end
it "should correctly literalize on MSSQL" do
db = dbf.call(:mssql).dataset.with_quote_identifiers(false)
db.literal(Sequel.date_add(:A, @h0)).must_equal "CAST(A AS datetime)"
db.literal(Sequel.date_add(:A, @h1)).must_equal "DATEADD(day, 1, A)"
db.literal(Sequel.date_add(:A, @h2)).must_equal "DATEADD(second, 1, DATEADD(minute, 1, DATEADD(hour, 1, DATEADD(day, 1, DATEADD(month, 1, DATEADD(year, 1, A))))))"
db.literal(Sequel.date_add(:A, @h0, :cast=>:timestamp)).must_equal "CAST(A AS timestamp)"
end
it "should correctly literalize on H2" do
db = Sequel.mock
def db.database_type; :h2 end
db.extension :date_arithmetic
db.literal(Sequel.date_add(:a, @h0)).must_equal "CAST(a AS timestamp)"
db.literal(Sequel.date_add(:a, @h1)).must_equal "DATEADD('day', 1, a)"
db.literal(Sequel.date_add(:a, @h2)).must_equal "DATEADD('second', 1, DATEADD('minute', 1, DATEADD('hour', 1, DATEADD('day', 1, DATEADD('month', 1, DATEADD('year', 1, a))))))"
db.literal(Sequel.date_add(:a, @h0, :cast=>:datetime)).must_equal "CAST(a AS datetime)"
end
it "should correctly literalize on access" do
db = dbf.call(:access).dataset.with_quote_identifiers(false)
db.literal(Sequel.date_add(:a, @h0)).must_equal "CDate(a)"
db.literal(Sequel.date_add(:a, @h1)).must_equal "DATEADD('d', 1, a)"
db.literal(Sequel.date_add(:a, @h2)).must_equal "DATEADD('s', 1, DATEADD('n', 1, DATEADD('h', 1, DATEADD('d', 1, DATEADD('m', 1, DATEADD('yyyy', 1, a))))))"
end
it "should correctly literalize on Derby" do
db = Sequel.mock
def db.database_type; :derby end
db.extension :date_arithmetic
db.literal(Sequel.date_add(:a, @h0)).must_equal "CAST(a AS timestamp)"
db.literal(Sequel.date_add(:a, @h1)).must_equal "{fn timestampadd(SQL_TSI_DAY, 1, timestamp(a))}"
db.literal(Sequel.date_add(:a, @h2)).must_equal "{fn timestampadd(SQL_TSI_SECOND, 1, timestamp({fn timestampadd(SQL_TSI_MINUTE, 1, timestamp({fn timestampadd(SQL_TSI_HOUR, 1, timestamp({fn timestampadd(SQL_TSI_DAY, 1, timestamp({fn timestampadd(SQL_TSI_MONTH, 1, timestamp({fn timestampadd(SQL_TSI_YEAR, 1, timestamp(a))}))}))}))}))}))}"
db.literal(Sequel.date_add(Date.civil(2012, 11, 12), @h1)).must_equal "{fn timestampadd(SQL_TSI_DAY, 1, timestamp((CAST('2012-11-12' AS varchar(255)) || ' 00:00:00')))}"
db.literal(Sequel.date_add(:a, @h0, :cast=>:datetime)).must_equal "CAST(a AS datetime)"
end
it "should correctly literalize on Oracle" do
db = dbf.call(:oracle).dataset.with_quote_identifiers(false)
db.literal(Sequel.date_add(:A, @h0)).must_equal "CAST(A AS timestamp)"
db.literal(Sequel.date_add(:A, @h1)).must_equal "(A + INTERVAL '1' DAY)"
db.literal(Sequel.date_add(:A, @h2)).must_equal "(A + INTERVAL '1' YEAR + INTERVAL '1' MONTH + INTERVAL '1' DAY + INTERVAL '1' HOUR + INTERVAL '1' MINUTE + INTERVAL '1' SECOND)"
db.literal(Sequel.date_add(:A, @h0, :cast=>:datetime)).must_equal "CAST(A AS datetime)"
end
it "should correctly literalize on DB2" do
db = dbf.call(:db2)
db.literal(Sequel.date_add(:A, @h0)).must_equal "CAST(A AS timestamp)"
db.literal(Sequel.date_add(:A, @h1)).must_equal "(CAST(A AS timestamp) + 1 days)"
db.literal(Sequel.date_add(:A, @h0)).must_equal "CAST(A AS timestamp)"
db.literal(Sequel.date_add(:A, @h1, :cast=>:datetime)).must_equal "(CAST(A AS datetime) + 1 days)"
db.literal(Sequel.date_add(:A, @h2, :cast=>:datetime)).must_equal "(CAST(A AS datetime) + 1 years + 1 months + 1 days + 1 hours + 1 minutes + 1 seconds)"
end
it "should raise error if literalizing on an unsupported database" do
db = Sequel.mock
db.extension :date_arithmetic
lambda{db.literal(Sequel.date_add(:a, @h0))}.must_raise(Sequel::Error)
end
end
|
