Commit | Line | Data |
5e6893d4 |
1 | use strict; |
2 | use warnings; |
3 | |
4 | use Test::More; |
fcaf47ee |
5 | use Test::Exception; |
5e6893d4 |
6 | |
7 | use lib qw(t/lib); |
8 | use DBIC::SqlMakerTest; |
9 | use DateTime; |
f94672f9 |
10 | use DBIx::Class::SQLMaker::MSSQL; |
11 | use Try::Tiny; |
2292bd08 |
12 | use Data::Dumper::Concise; |
5e6893d4 |
13 | |
f94672f9 |
14 | use DBICTest; |
5e6893d4 |
15 | |
f94672f9 |
16 | my %dbs_to_test = ( |
35621f12 |
17 | sqlite => 1, |
18 | mssql => 0, |
19 | postgres => 1, |
08c123d2 |
20 | oracle => 1, |
f94672f9 |
21 | ); |
22 | |
23 | my %schema = ( |
24 | sqlite => DBICTest->init_schema( no_populate => 1 ), |
25 | mssql => do { |
26 | my ($dsn, $user, $pass) = @ENV{map { "DBICTEST_MSSQL_ODBC_${_}" } qw/DSN USER PASS/}; |
27 | if ($dsn && $user) { |
28 | my $s = DBICTest::Schema->connect($dsn, $user, $pass); |
29 | try { $s->storage->ensure_connected }; |
5e6893d4 |
30 | |
f94672f9 |
31 | $s->storage->dbh_do (sub { |
32 | my ($storage, $dbh) = @_; |
33 | eval { $dbh->do("DROP TABLE event") }; |
34 | $dbh->do(<<'SQL'); |
35 | CREATE TABLE event ( |
36 | id INT IDENTITY NOT NULL, |
37 | starts_at DATE NOT NULL, |
38 | created_on DATETIME NOT NULL, |
39 | varchar_date VARCHAR(20), |
40 | varchar_datetime VARCHAR(20), |
41 | skip_inflation DATETIME, |
42 | ts_without_tz DATETIME, |
43 | |
44 | primary key(id) |
45 | ) |
46 | SQL |
47 | $dbs_to_test{mssql} = 1; |
48 | }); |
49 | $s; |
50 | } else { |
51 | DBICTest->init_schema( no_deploy=> 1, storage_type => '::DBI::MSSQL' ) |
52 | } |
53 | }, |
35621f12 |
54 | ## copypasta'd for great justice |
55 | postgres => do { |
56 | my ($dsn, $user, $pass) = @ENV{map { "DBICTEST_POSTGRES_${_}" } qw/DSN USER PASS/}; |
57 | if ($dsn && $user) { |
58 | my $s = DBICTest::Schema->connect($dsn, $user, $pass); |
59 | try { $s->storage->ensure_connected }; |
60 | |
61 | $s->storage->dbh_do (sub { |
62 | my ($storage, $dbh) = @_; |
63 | eval { $dbh->do("DROP TABLE event") }; |
64 | $dbh->do(<<'SQL'); |
65 | CREATE TABLE event ( |
66 | id SERIAL NOT NULL PRIMARY KEY, |
67 | starts_at DATE NOT NULL, |
68 | created_on TIMESTAMP NOT NULL, |
69 | varchar_date VARCHAR(20), |
70 | varchar_datetime VARCHAR(20), |
71 | skip_inflation TIMESTAMP, |
72 | ts_without_tz TIMESTAMP WITHOUT TIME ZONE |
73 | ) |
74 | SQL |
75 | $dbs_to_test{postgres} = 1; |
76 | }); |
77 | $s; |
78 | } else { |
79 | DBICTest->init_schema( no_deploy=> 1, storage_type => '::DBI::Pg' ) |
80 | } |
08c123d2 |
81 | }, |
82 | oracle => do { |
83 | my ($dsn, $user, $pass) = @ENV{map { "DBICTEST_ORA_${_}" } qw/DSN USER PASS/}; |
84 | if ($dsn && $user) { |
85 | my $s = DBICTest::Schema->connect($dsn, $user, $pass, { on_connect_call => 'datetime_setup' }); |
86 | try { $s->storage->ensure_connected }; |
87 | |
88 | $s->storage->dbh_do (sub { |
89 | my ($storage, $dbh) = @_; |
90 | eval { $dbh->do("DROP TRIGGER trq_event_id") }; |
91 | eval { $dbh->do("DROP SEQUENCE sq_event_id") }; |
92 | eval { $dbh->do("DROP TABLE event") }; |
93 | $dbh->do('CREATE SEQUENCE sq_event_id'); |
94 | $dbh->do(<<'SQL'); |
95 | CREATE TABLE event ( |
96 | id NUMBER NOT NULL, |
97 | starts_at DATE NOT NULL, |
98 | created_on TIMESTAMP NOT NULL, |
99 | varchar_date VARCHAR(20), |
100 | varchar_datetime VARCHAR(20), |
101 | skip_inflation TIMESTAMP, |
102 | ts_without_tz TIMESTAMP, |
103 | CONSTRAINT PK_EVENT PRIMARY KEY (id) |
104 | ) |
105 | SQL |
106 | $dbh->do(<<'SQL'); |
107 | CREATE TRIGGER trg_event_id |
108 | BEFORE INSERT ON event |
109 | FOR EACH ROW WHEN ( |
110 | new.id IS NULL OR new.id = 0 |
111 | ) |
112 | BEGIN |
113 | SELECT sq_event_id.nextval |
114 | INTO :new.id |
115 | FROM dual; |
116 | END; |
117 | SQL |
118 | $dbs_to_test{oracle} = 1; |
119 | }); |
120 | $s; |
121 | } else { |
122 | DBICTest->init_schema( no_deploy=> 1, storage_type => '::DBI::Oracle', on_connect_call => 'datetime_setup' ) |
123 | } |
124 | }, |
fcaf47ee |
125 | ); |
5e6893d4 |
126 | |
f94672f9 |
127 | my %rs = map { $_ => $schema{$_}->resultset('Event') } keys %schema; |
128 | |
129 | $rs{sqlite}->populate([ |
130 | [qw(starts_at created_on skip_inflation)], |
131 | ['2010-12-12', '2010-12-14 12:12:12', '2019-12-12 12:12:12'], |
132 | ['2010-12-12', '2011-12-14 12:12:12', '2011-12-12 12:12:12'], |
133 | ]); |
134 | |
135 | $rs{mssql}->populate([ |
136 | [qw(starts_at created_on skip_inflation)], |
137 | ['2010-12-12', '2010-12-14 12:12:12.000', '2019-12-12 12:12:12.000'], |
138 | ['2010-12-12', '2011-12-14 12:12:12.000', '2011-12-12 12:12:12.000'], |
139 | ]) if $schema{mssql}->storage->connected; |
140 | |
35621f12 |
141 | $rs{postgres}->populate([ |
142 | [qw(starts_at created_on skip_inflation)], |
143 | ['2010-12-12', '2010-12-14 12:12:12', '2019-12-12 12:12:12'], |
144 | ['2010-12-12', '2011-12-14 12:12:12', '2011-12-12 12:12:12'], |
145 | ]) if $schema{postgres}->storage->connected; |
146 | |
08c123d2 |
147 | $rs{oracle}->populate([ |
148 | [qw(starts_at created_on skip_inflation)], |
149 | ['2010-12-12', '2010-12-14 12:12:12', '2019-12-12 12:12:12'], |
150 | ['2010-12-12', '2011-12-14 12:12:12', '2011-12-12 12:12:12'], |
151 | ]) if $schema{oracle}->storage->connected; |
35621f12 |
152 | |
5e6893d4 |
153 | my $date = DateTime->new( |
154 | year => 2010, |
155 | month => 12, |
156 | day => 14, |
157 | hour => 12, |
158 | minute => 12, |
159 | second => 12, |
160 | ); |
161 | |
f94672f9 |
162 | sub hri_thing { |
163 | return { |
164 | starts_at => $_[0], |
165 | created_on => $_[1], |
166 | skip_inflation => $_[2] |
167 | } |
168 | } |
169 | |
5e6893d4 |
170 | my $date2 = $date->clone->set_day(16); |
171 | |
35621f12 |
172 | ## test format: |
173 | ## search => { dbic_search_code/params } |
174 | ## rdbms_name => literal_sql |
fcaf47ee |
175 | my @tests = ( |
35621f12 |
176 | ## -dt-now tests |
fcaf47ee |
177 | { |
f94672f9 |
178 | search => { 'me.created_on' => { -dt => $date } }, |
fcaf47ee |
179 | sqlite => { |
f94672f9 |
180 | select => 'me.starts_at, me.created_on, me.skip_inflation', |
181 | where => 'me.created_on = ?', |
182 | bind => [[ 'me.created_on', '2010-12-14 12:12:12' ]], |
183 | hri => [hri_thing('2010-12-12', '2010-12-14 12:12:12', '2019-12-12 12:12:12')], |
fcaf47ee |
184 | }, |
f94672f9 |
185 | mssql => { |
186 | select => 'me.starts_at, me.created_on, me.skip_inflation', |
187 | where => 'me.created_on = ?', |
188 | bind => [[ 'me.created_on', '2010-12-14 12:12:12.000' ]], |
189 | hri => [hri_thing('2010-12-12', '2010-12-14 12:12:12.000', '2019-12-12 12:12:12.000')], |
fcaf47ee |
190 | }, |
35621f12 |
191 | postgres => { |
192 | select => 'me.starts_at, me.created_on, me.skip_inflation', |
193 | where => 'me.created_on = ?', |
194 | bind => [[ 'me.created_on', '2010-12-14 12:12:12' ]], |
195 | hri => [hri_thing('2010-12-12', '2010-12-14 12:12:12', '2019-12-12 12:12:12')], |
196 | }, |
08c123d2 |
197 | oracle => { |
198 | select => 'me.starts_at, me.created_on, me.skip_inflation', |
199 | where => 'me.created_on = ?', |
200 | bind => [[ 'me.created_on', '2010-12-14 12:12:12' ]], |
201 | hri => [hri_thing('2010-12-12 00:00:00', '2010-12-14 12:12:12.000000', '2019-12-12 12:12:12.000000')], |
202 | }, |
fcaf47ee |
203 | msg => '-dt_now works', |
204 | }, |
35621f12 |
205 | ## -dt_year tests |
fcaf47ee |
206 | { |
f94672f9 |
207 | search => { 'me.id' => 1 }, |
208 | select => [ [ -dt_year => { -ident => 'me.created_on' } ] ], |
209 | as => [ 'year' ], |
210 | mssql => { |
211 | select => "DATEPART(year, me.created_on)", |
212 | where => "me.id = ?", |
213 | bind => [['me.id' => 1 ]], |
214 | hri => [{ year => 2010 }], |
215 | }, |
fcaf47ee |
216 | sqlite => { |
f94672f9 |
217 | select => "STRFTIME('%Y', me.created_on)", |
218 | where => "me.id = ?", |
219 | bind => [['me.id' => 1 ]], |
220 | hri => [{ year => 2010 }], |
fcaf47ee |
221 | }, |
35621f12 |
222 | postgres => { |
223 | select => "EXTRACT(year FROM me.created_on)", |
224 | where => "me.id = ?", |
225 | bind => [['me.id' => 1 ]], |
226 | hri => [{ year => 2010 }], |
227 | }, |
08c123d2 |
228 | oracle => { |
229 | select => "EXTRACT(year FROM me.created_on)", |
230 | where => "me.id = ?", |
231 | bind => [['me.id' => 1 ]], |
232 | hri => [{ year => 2010 }], |
233 | }, |
fcaf47ee |
234 | msg => '-dt_year works', |
235 | }, |
35621f12 |
236 | ## -dt_get(year, month) tests |
fcaf47ee |
237 | { |
f94672f9 |
238 | search => { 'me.id' => 1 }, |
464a9709 |
239 | select => [ [ -dt_get => [[qw(year month)], { -ident => 'me.created_on' }] ] ], |
240 | as => [ qw(year month) ], |
241 | mssql => { |
242 | select => "DATEPART(year, me.created_on), DATEPART(month, me.created_on)", |
243 | where => "me.id = ?", |
244 | bind => [['me.id' => 1 ]], |
245 | hri => [{ year => 2010, month => 12 }], |
246 | }, |
247 | sqlite => { |
248 | select => "STRFTIME('%Y', me.created_on), STRFTIME('%m', me.created_on)", |
249 | where => "me.id = ?", |
250 | bind => [['me.id' => 1 ]], |
251 | hri => [{ year => 2010, month => 12 }], |
252 | }, |
35621f12 |
253 | postgres => { |
254 | select => "EXTRACT(year FROM me.created_on), EXTRACT(month FROM me.created_on)", |
255 | where => "me.id = ?", |
256 | bind => [['me.id' => 1 ]], |
257 | hri => [{ year => 2010, month => 12 }], |
258 | }, |
08c123d2 |
259 | oracle => { |
260 | select => "EXTRACT(year FROM me.created_on), EXTRACT(month FROM me.created_on)", |
261 | where => "me.id = ?", |
262 | bind => [['me.id' => 1 ]], |
263 | hri => [{ year => 2010, month => 12 }], |
264 | }, |
464a9709 |
265 | msg => '-dt_get (year, month) works', |
266 | }, |
35621f12 |
267 | ## -dt_month tests |
464a9709 |
268 | { |
269 | search => { 'me.id' => 1 }, |
f94672f9 |
270 | select => [ [ -dt_month => { -ident => 'me.created_on' } ] ], |
271 | as => [ 'month' ], |
fcaf47ee |
272 | sqlite => { |
f94672f9 |
273 | select => "STRFTIME('%m', me.created_on)", |
274 | where => "me.id = ?", |
275 | bind => [['me.id' => 1 ]], |
276 | hri => [{ month => 12 }], |
277 | }, |
278 | mssql => { |
279 | select => "DATEPART(month, me.created_on)", |
280 | where => "me.id = ?", |
281 | bind => [['me.id' => 1 ]], |
282 | hri => [{ month => 12 }], |
fcaf47ee |
283 | }, |
35621f12 |
284 | postgres => { |
285 | select => "EXTRACT(month FROM me.created_on)", |
286 | where => "me.id = ?", |
287 | bind => [['me.id' => 1 ]], |
288 | hri => [{ month => 12 }], |
08c123d2 |
289 | }, |
290 | oracle => { |
291 | select => "EXTRACT(month FROM me.created_on)", |
292 | where => "me.id = ?", |
293 | bind => [['me.id' => 1 ]], |
294 | hri => [{ month => 12 }], |
35621f12 |
295 | }, |
fcaf47ee |
296 | msg => '-dt_month works', |
297 | }, |
35621f12 |
298 | ## -dt_day tests |
fcaf47ee |
299 | { |
f94672f9 |
300 | search => { 'me.id' => 1 }, |
301 | select => [ [ -dt_day => { -ident => 'me.created_on' } ] ], |
302 | as => [ 'day' ], |
fcaf47ee |
303 | sqlite => { |
f94672f9 |
304 | select => "STRFTIME('%d', me.created_on)", |
305 | where => "me.id = ?", |
306 | bind => [['me.id' => 1 ]], |
307 | hri => [{ day => 14 }], |
308 | }, |
309 | mssql => { |
310 | select => "DATEPART(day, me.created_on)", |
311 | where => "me.id = ?", |
312 | bind => [['me.id' => 1 ]], |
313 | hri => [{ day => 14 }], |
fcaf47ee |
314 | }, |
35621f12 |
315 | postgres => { |
316 | select => "EXTRACT(day FROM me.created_on)", |
317 | where => "me.id = ?", |
318 | bind => [['me.id' => 1 ]], |
319 | hri => [{ day => 14 }], |
08c123d2 |
320 | }, |
321 | oracle => { |
322 | select => "EXTRACT(day FROM me.created_on)", |
323 | where => "me.id = ?", |
324 | bind => [['me.id' => 1 ]], |
325 | hri => [{ day => 14 }], |
35621f12 |
326 | }, |
fcaf47ee |
327 | msg => '-dt_day works', |
328 | }, |
35621f12 |
329 | ## -dt_hour tests |
fcaf47ee |
330 | { |
f94672f9 |
331 | search => { 'me.id' => 1 }, |
332 | select => [ [ -dt_hour => { -ident => 'me.created_on' } ] ], |
333 | as => [ 'hour' ], |
fcaf47ee |
334 | sqlite => { |
f94672f9 |
335 | select => "STRFTIME('%H', me.created_on)", |
336 | where => "me.id = ?", |
337 | bind => [['me.id' => 1 ]], |
338 | hri => [{ hour => 12 }], |
339 | }, |
340 | mssql => { |
341 | select => "DATEPART(hour, me.created_on)", |
342 | where => "me.id = ?", |
343 | bind => [['me.id' => 1 ]], |
344 | hri => [{ hour => 12 }], |
fcaf47ee |
345 | }, |
35621f12 |
346 | postgres => { |
347 | select => "EXTRACT(hour FROM me.created_on)", |
348 | where => "me.id = ?", |
349 | bind => [['me.id' => 1 ]], |
350 | hri => [{ hour => 12 }], |
351 | }, |
08c123d2 |
352 | oracle => { |
353 | select => "EXTRACT(hour FROM me.created_on)", |
354 | where => "me.id = ?", |
355 | bind => [['me.id' => 1 ]], |
356 | hri => [{ hour => 12 }], |
357 | }, |
fcaf47ee |
358 | msg => '-dt_hour works', |
359 | }, |
35621f12 |
360 | ## -dt_minute tests |
fcaf47ee |
361 | { |
f94672f9 |
362 | search => { 'me.id' => 1 }, |
363 | select => [ [ -dt_minute => { -ident => 'me.created_on' } ] ], |
364 | as => [ 'minute' ], |
fcaf47ee |
365 | sqlite => { |
f94672f9 |
366 | select => "STRFTIME('%M', me.created_on)", |
367 | where => "me.id = ?", |
368 | bind => [['me.id' => 1 ]], |
369 | hri => [{ minute => 12 }], |
370 | }, |
371 | mssql => { |
372 | select => "DATEPART(minute, me.created_on)", |
373 | where => "me.id = ?", |
374 | bind => [['me.id' => 1 ]], |
375 | hri => [{ minute => 12 }], |
fcaf47ee |
376 | }, |
35621f12 |
377 | postgres => { |
378 | select => "EXTRACT(minute FROM me.created_on)", |
379 | where => "me.id = ?", |
380 | bind => [['me.id' => 1 ]], |
381 | hri => [{ minute => 12 }], |
382 | }, |
08c123d2 |
383 | oracle => { |
384 | select => "EXTRACT(minute FROM me.created_on)", |
385 | where => "me.id = ?", |
386 | bind => [['me.id' => 1 ]], |
387 | hri => [{ minute => 12 }], |
388 | }, |
fcaf47ee |
389 | msg => '-dt_minute works', |
390 | }, |
35621f12 |
391 | ## -dt_second tests |
fcaf47ee |
392 | { |
f94672f9 |
393 | search => { 'me.id' => 1 }, |
394 | select => [ [ -dt_second => { -ident => 'me.created_on' } ] ], |
395 | as => [ 'second' ], |
fcaf47ee |
396 | sqlite => { |
f94672f9 |
397 | select => "STRFTIME('%S', me.created_on)", |
398 | where => "me.id = ?", |
399 | bind => [['me.id' => 1 ]], |
400 | hri => [{ second => 12 }], |
401 | }, |
402 | mssql => { |
403 | select => "DATEPART(second, me.created_on)", |
404 | where => "me.id = ?", |
405 | bind => [['me.id' => 1 ]], |
406 | hri => [{ second => 12 }], |
fcaf47ee |
407 | }, |
35621f12 |
408 | postgres => { |
409 | select => "EXTRACT(second FROM me.created_on)", |
410 | where => "me.id = ?", |
411 | bind => [['me.id' => 1 ]], |
412 | hri => [{ second => 12 }], |
08c123d2 |
413 | }, |
414 | oracle => { |
415 | select => "EXTRACT(second FROM me.created_on)", |
416 | where => "me.id = ?", |
417 | bind => [['me.id' => 1 ]], |
418 | hri => [{ second => 12 }], |
35621f12 |
419 | }, |
fcaf47ee |
420 | msg => '-dt_second works', |
421 | }, |
35621f12 |
422 | ## -dt_diff(second) tests |
fcaf47ee |
423 | { |
f94672f9 |
424 | search => { 'me.id' => 2 }, |
425 | select => [ [ -dt_diff => [second => { -ident => 'me.created_on' }, \'me.skip_inflation' ] ] ], |
426 | as => [ 'sec_diff' ], |
fcaf47ee |
427 | sqlite => { |
f94672f9 |
428 | select => "(STRFTIME('%s', me.created_on) - STRFTIME('%s', me.skip_inflation))", |
429 | where => "me.id = ?", |
430 | bind => [['me.id' => 2 ]], |
431 | hri => [{ sec_diff => 2*24*60*60 }], |
432 | }, |
433 | mssql => { |
434 | select => "DATEDIFF(second, me.skip_inflation, me.created_on)", |
435 | where => "me.id = ?", |
436 | bind => [['me.id' => 2 ]], |
437 | hri => [{ sec_diff => 2*24*60*60 }], |
fcaf47ee |
438 | }, |
35621f12 |
439 | postgres => { |
440 | select => "EXTRACT(epoch FROM (me.created_on::timestamp with time zone - me.skip_inflation::timestamp with time zone))", |
441 | where => "me.id = ?", |
442 | bind => [['me.id' => 2 ]], |
443 | hri => [{ sec_diff => 2*24*60*60 }], |
444 | }, |
08c123d2 |
445 | oracle => { |
446 | select => "TRUNC(MONTHS_BETWEEN(me.created_on, me.skip_inflation) * 31 * 24 * 60 * 60)", |
447 | where => "me.id = ?", |
448 | bind => [['me.id' => 2 ]], |
449 | hri => [{ sec_diff => 2*24*60*60 }], |
450 | }, |
fcaf47ee |
451 | msg => '-dt_diff (second) works', |
452 | }, |
f94672f9 |
453 | |
35621f12 |
454 | # -dt_diff(day) tests |
fcaf47ee |
455 | { |
f94672f9 |
456 | search => { 'me.id' => 2 }, |
457 | select => [ [ -dt_diff => [day => { -ident => 'me.created_on' }, \'me.skip_inflation' ] ] ], |
458 | as => [ 'day_diff' ], |
fcaf47ee |
459 | sqlite => { |
f94672f9 |
460 | select => "(JULIANDAY(me.created_on) - JULIANDAY(me.skip_inflation))", |
461 | where => "me.id = ?", |
462 | bind => [['me.id' => 2 ]], |
463 | hri => [{ day_diff => 2 }], |
464 | }, |
465 | mssql => { |
466 | select => "DATEDIFF(dayofyear, me.skip_inflation, me.created_on)", |
467 | where => "me.id = ?", |
468 | bind => [['me.id' => 2 ]], |
469 | hri => [{ day_diff => 2 }], |
fcaf47ee |
470 | }, |
35621f12 |
471 | postgres => { |
472 | select => "EXTRACT(DAY FROM (me.created_on::timestamp with time zone - me.skip_inflation::timestamp with time zone))", |
473 | where => "me.id = ?", |
474 | bind => [['me.id' => 2 ]], |
475 | hri => [{ day_diff => 2 }], |
476 | }, |
08c123d2 |
477 | oracle => { |
478 | select => "TRUNC(MONTHS_BETWEEN(me.created_on, me.skip_inflation) * 31)", |
479 | where => "me.id = ?", |
480 | bind => [['me.id' => 2 ]], |
481 | hri => [{ day_diff => 2 }], |
482 | }, |
fcaf47ee |
483 | msg => '-dt_diff (day) works', |
484 | }, |
f94672f9 |
485 | |
fcaf47ee |
486 | { |
0e2e56cc |
487 | msg => '-dt_diff (year) works', |
488 | search => { 'me.id' => 2 }, |
489 | select => [ [ -dt_diff => [year => \'me.starts_at', { -ident => 'me.created_on' } ] ] ], |
490 | as => [ 'year' ], |
491 | sqlite => { |
492 | exception_like => qr/date diff not supported for part "year" with database "SQLite"/, |
493 | }, |
494 | mssql => { |
495 | select => "DATEDIFF(year, me.created_on, me.starts_at)", |
496 | where => "me.id = ?", |
497 | bind => [['me.id', 2]], |
498 | hri => [{ year => -1 }], |
499 | }, |
500 | oracle => { |
501 | select => "TRUNC(MONTHS_BETWEEN(me.starts_at, me.created_on) / 12)", |
502 | where => "me.id = ?", |
503 | bind => [['me.id', 2]], |
504 | hri => [{ year => -1 }], |
505 | }, |
506 | }, |
507 | |
508 | { |
08c123d2 |
509 | msg => '-dt_add (year) works', |
f94672f9 |
510 | search => { 'me.id' => 2 }, |
511 | select => [ [ -dt_add => [year => 3, { -ident => 'me.created_on' } ] ] ], |
512 | as => [ 'date' ], |
fcaf47ee |
513 | sqlite => { |
f94672f9 |
514 | select => "(datetime(me.created_on, ? || ' years'))", |
515 | where => "me.id = ?", |
516 | bind => [['', 3], ['me.id' => 2 ]], |
517 | hri => [{ date => '2014-12-14 12:12:12' }], |
518 | }, |
519 | mssql => { |
520 | select => "(DATEADD(year, ?, me.created_on))", |
521 | where => "me.id = ?", |
522 | bind => [['', 3], ['me.id' => 2 ]], |
523 | hri => [{ date => '2014-12-14 12:12:12.000' }], |
524 | skip => 'need working bindtypes', |
fcaf47ee |
525 | }, |
08c123d2 |
526 | oracle => { |
527 | select => "(me.created_on + NUMTOYMINTERVAL(?, 'year'))", |
528 | where => "me.id = ?", |
529 | bind => [['', 3], ['me.id' => 2 ]], |
530 | hri => [{ date => '2014-12-14 12:12:12.000000000' }], |
531 | }, |
fcaf47ee |
532 | }, |
f94672f9 |
533 | |
fcaf47ee |
534 | { |
08c123d2 |
535 | msg => '-dt_add (month) works', |
f94672f9 |
536 | search => { 'me.id' => 2 }, |
537 | select => [ [ -dt_add => [month => 3, { -ident => 'me.created_on' } ] ] ], |
538 | as => [ 'date' ], |
fcaf47ee |
539 | sqlite => { |
f94672f9 |
540 | select => "(datetime(me.created_on, ? || ' months'))", |
541 | where => "me.id = ?", |
542 | bind => [['', 3], ['me.id' => 2 ]], |
543 | hri => [{ date => '2012-03-14 12:12:12' }], |
544 | }, |
545 | mssql => { |
546 | select => "(DATEADD(month, ?, me.created_on))", |
547 | where => "me.id = ?", |
548 | bind => [['', 3], ['me.id' => 2 ]], |
549 | hri => [{ date => '2012-03-14 12:12:12.000' }], |
550 | skip => 'need working bindtypes', |
fcaf47ee |
551 | }, |
08c123d2 |
552 | oracle => { |
553 | select => "(me.created_on + NUMTOYMINTERVAL(?, 'month'))", |
554 | where => "me.id = ?", |
555 | bind => [['', 3], ['me.id' => 2 ]], |
556 | hri => [{ date => '2012-03-14 12:12:12.000000000' }], |
557 | }, |
fcaf47ee |
558 | }, |
f94672f9 |
559 | |
fcaf47ee |
560 | { |
08c123d2 |
561 | msg => '-dt_add (day) works', |
f94672f9 |
562 | search => { 'me.id' => 2 }, |
563 | select => [ [ -dt_add => [day => 3, { -ident => 'me.created_on' } ] ] ], |
564 | as => [ 'date' ], |
fcaf47ee |
565 | sqlite => { |
f94672f9 |
566 | select => "(datetime(me.created_on, ? || ' days'))", |
567 | where => "me.id = ?", |
568 | bind => [['', 3], ['me.id' => 2 ]], |
569 | hri => [{ date => '2011-12-17 12:12:12' }], |
570 | }, |
571 | mssql => { |
572 | select => "(DATEADD(dayofyear, ?, me.created_on))", |
573 | where => "me.id = ?", |
574 | bind => [['', 3], ['me.id' => 2 ]], |
575 | hri => [{ date => '2011-12-17 12:12:12.000' }], |
576 | skip => 'need working bindtypes', |
fcaf47ee |
577 | }, |
08c123d2 |
578 | oracle => { |
579 | select => "(me.created_on + NUMTODSINTERVAL(?, 'day'))", |
580 | where => "me.id = ?", |
581 | bind => [['', 3], ['me.id' => 2 ]], |
582 | hri => [{ date => '2011-12-17 12:12:12.000000000' }], |
583 | }, |
fcaf47ee |
584 | }, |
f94672f9 |
585 | |
fcaf47ee |
586 | { |
08c123d2 |
587 | msg => '-dt_add (hour) works', |
f94672f9 |
588 | search => { 'me.id' => 2 }, |
589 | select => [ [ -dt_add => [hour => 3, { -ident => 'me.created_on' } ] ] ], |
590 | as => [ 'date' ], |
fcaf47ee |
591 | sqlite => { |
f94672f9 |
592 | select => "(datetime(me.created_on, ? || ' hours'))", |
593 | where => "me.id = ?", |
594 | bind => [['', 3], ['me.id' => 2 ]], |
595 | hri => [{ date => '2011-12-14 15:12:12' }], |
596 | }, |
597 | mssql => { |
598 | select => "(DATEADD(hour, ?, me.created_on))", |
599 | where => "me.id = ?", |
600 | bind => [['', 3], ['me.id' => 2 ]], |
601 | hri => [{ date => '2011-12-14 15:12:12.000' }], |
602 | skip => 'need working bindtypes', |
fcaf47ee |
603 | }, |
08c123d2 |
604 | oracle => { |
605 | select => "(me.created_on + NUMTODSINTERVAL(?, 'hour'))", |
606 | where => "me.id = ?", |
607 | bind => [['', 3], ['me.id' => 2 ]], |
608 | hri => [{ date => '2011-12-14 15:12:12.000000000' }], |
609 | }, |
fcaf47ee |
610 | }, |
f94672f9 |
611 | |
fcaf47ee |
612 | { |
08c123d2 |
613 | msg => '-dt_add (minute) works', |
f94672f9 |
614 | search => { 'me.id' => 2 }, |
615 | select => [ [ -dt_add => [minute => 3, { -ident => 'me.created_on' } ] ] ], |
616 | as => [ 'date' ], |
fcaf47ee |
617 | sqlite => { |
f94672f9 |
618 | select => "(datetime(me.created_on, ? || ' minutes'))", |
619 | where => "me.id = ?", |
620 | bind => [['', 3], ['me.id' => 2 ]], |
621 | hri => [{ date => '2011-12-14 12:15:12' }], |
622 | }, |
623 | mssql => { |
624 | select => "(DATEADD(minute, ?, me.created_on))", |
625 | where => "me.id = ?", |
626 | bind => [['', 3], ['me.id' => 2 ]], |
627 | hri => [{ date => '2011-12-14 12:15:12.000' }], |
628 | skip => 'need working bindtypes', |
fcaf47ee |
629 | }, |
08c123d2 |
630 | oracle => { |
631 | select => "(me.created_on + NUMTODSINTERVAL(?, 'minute'))", |
632 | where => "me.id = ?", |
633 | bind => [['', 3], ['me.id' => 2 ]], |
634 | hri => [{ date => '2011-12-14 12:15:12.000000000' }], |
635 | }, |
fcaf47ee |
636 | }, |
f94672f9 |
637 | |
fcaf47ee |
638 | { |
08c123d2 |
639 | msg => '-dt_add (second) works', |
f94672f9 |
640 | search => { 'me.id' => 2 }, |
641 | select => [ [ -dt_add => [second => 3, { -ident => 'me.created_on' } ] ] ], |
642 | as => [ 'date' ], |
fcaf47ee |
643 | sqlite => { |
f94672f9 |
644 | select => "(datetime(me.created_on, ? || ' seconds'))", |
645 | where => "me.id = ?", |
646 | bind => [['', 3], ['me.id' => 2 ]], |
647 | hri => [{ date => '2011-12-14 12:12:15' }], |
648 | }, |
649 | mssql => { |
650 | select => "(DATEADD(second, ?, me.created_on))", |
651 | where => "me.id = ?", |
652 | bind => [['', 3], ['me.id' => 2 ]], |
653 | hri => [{ date => '2011-12-14 12:12:15.000' }], |
654 | skip => 'need working bindtypes', |
fcaf47ee |
655 | }, |
08c123d2 |
656 | oracle => { |
657 | select => "(me.created_on + NUMTODSINTERVAL(?, 'second'))", |
658 | where => "me.id = ?", |
659 | bind => [['', 3], ['me.id' => 2 ]], |
660 | hri => [{ date => '2011-12-14 12:12:15.000000000' }], |
661 | }, |
fcaf47ee |
662 | }, |
f94672f9 |
663 | |
fcaf47ee |
664 | { |
08c123d2 |
665 | msg => 'nested -dt_add works', |
f94672f9 |
666 | search => { 'me.id' => 2 }, |
667 | select => [ [ -dt_add => [second => 3, { -dt_add => [ day => 1, { -ident => 'me.created_on' } ] } ] ] ], |
668 | as => [ 'date' ], |
669 | sqlite => { |
670 | select => "(datetime((datetime(me.created_on, ? || ' days')), ? || ' seconds'))", |
671 | where => "me.id = ?", |
672 | bind => [['', 1], [ '', 3 ], ['me.id', 2]], |
673 | hri => [{ date => '2011-12-15 12:12:15' }], |
674 | }, |
675 | mssql => { |
676 | select => "(DATEADD(second, ?, (DATEADD(dayofyear, ?, me.created_on))))", |
677 | where => "me.id = ?", |
678 | bind => [['', 3], [ '', 1 ], ['me.id', 2]], |
679 | hri => [{ date => '2011-12-15 12:12:15.000' }], |
680 | skip => 'need working bindtypes', |
681 | }, |
08c123d2 |
682 | oracle => { |
683 | select => "((me.created_on + NUMTODSINTERVAL(?, 'day')) + NUMTODSINTERVAL(?, 'second'))", |
684 | where => "me.id = ?", |
685 | bind => [['', 1], [ '', 3 ], ['me.id', 2]], |
686 | hri => [{ date => '2011-12-15 12:12:15.000000000' }], |
687 | }, |
f94672f9 |
688 | }, |
fcaf47ee |
689 | ); |
5a44a8ec |
690 | |
fcaf47ee |
691 | for my $t (@tests) { |
5a44a8ec |
692 | |
fcaf47ee |
693 | DB_TEST: |
f94672f9 |
694 | for my $db (keys %rs) { |
fcaf47ee |
695 | my $db_test = $t->{$db}; |
696 | next DB_TEST unless $db_test; |
5e6893d4 |
697 | |
f94672f9 |
698 | my ($r, $my_rs); |
fcaf47ee |
699 | |
700 | my $cref = sub { |
f94672f9 |
701 | my $stuff = { |
702 | ( exists $t->{select} |
703 | ? ( select => $t->{select}, as => $t->{as} ) |
704 | : ( columns => [qw(starts_at created_on skip_inflation)] ) |
705 | ) |
706 | }; |
707 | $my_rs = $rs{$db}->search($t->{search}, $stuff); |
708 | $r = $my_rs->as_query |
fcaf47ee |
709 | }; |
5e6893d4 |
710 | |
fcaf47ee |
711 | if ($db_test->{exception_like}) { |
712 | throws_ok( |
713 | sub { $cref->() }, |
714 | $db_test->{exception_like}, |
715 | "throws the expected exception ($db_test->{exception_like})", |
716 | ); |
717 | } else { |
718 | if ($db_test->{warning_like}) { |
719 | warning_like( |
720 | sub { $cref->() }, |
721 | $db_test->{warning_like}, |
722 | "issues the expected warning ($db_test->{warning_like})" |
723 | ); |
724 | } |
725 | else { |
726 | $cref->(); |
727 | } |
728 | is_same_sql_bind( |
f94672f9 |
729 | $r, |
730 | "(SELECT $db_test->{select} FROM event me WHERE $db_test->{where})", |
fcaf47ee |
731 | $db_test->{bind}, |
f94672f9 |
732 | ($t->{msg} ? "$t->{msg} ($db)" : ()) |
fcaf47ee |
733 | ); |
f94672f9 |
734 | |
735 | SKIP: { |
736 | if (my $hri = $db_test->{hri}) { |
737 | skip "Cannot test database we are not connected to ($db)", 1 unless $dbs_to_test{$db}; |
738 | skip $db_test->{skip} . " ($db)", 1 if $db_test->{skip}; |
739 | |
740 | my $msg = ($t->{msg} ? "$t->{msg} ($db actually pulls expected data)" : ''); |
741 | try { |
2292bd08 |
742 | my $got = [ $my_rs->hri_dump->all ]; |
743 | my $success = is_deeply $got, $hri, $msg; |
744 | unless ($success) { |
745 | warn "$db: $t->{msg} got: " . Dumper $got; |
746 | warn "$db: $t->{msg} expected: " . Dumper $hri; |
747 | } |
f94672f9 |
748 | } catch { |
749 | ok 0, $msg . " $_"; |
750 | } |
751 | } } |
fcaf47ee |
752 | } |
753 | } |
754 | } |
6edb8b2f |
755 | |
5e6893d4 |
756 | done_testing; |