9 use DBIC::SqlMakerTest;
11 use DBIx::Class::SQLMaker::MSSQL;
13 use Data::Dumper::Concise;
17 sub unknown_col { +{ dbic_colname => '' } }
27 sqlite => DBICTest->init_schema( no_populate => 1 ),
29 my ($dsn, $user, $pass) = @ENV{map { "DBICTEST_MSSQL_ODBC_${_}" } qw/DSN USER PASS/};
31 my $s = DBICTest::Schema->connect($dsn, $user, $pass);
32 try { $s->storage->ensure_connected };
34 $s->storage->dbh_do (sub {
35 my ($storage, $dbh) = @_;
36 eval { $dbh->do("DROP TABLE event") };
39 id INT IDENTITY NOT NULL,
40 starts_at DATE NOT NULL,
41 created_on DATETIME NOT NULL,
42 varchar_date VARCHAR(20),
43 varchar_datetime VARCHAR(20),
44 skip_inflation DATETIME,
45 ts_without_tz DATETIME,
50 $dbs_to_test{mssql} = 1;
54 DBICTest->init_schema( no_deploy=> 1, storage_type => '::DBI::MSSQL' )
58 my ($dsn, $user, $pass) = @ENV{map { "DBICTEST_MYSQL_${_}" } qw/DSN USER PASS/};
60 my $s = DBICTest::Schema->connect($dsn, $user, $pass);
61 try { $s->storage->ensure_connected };
63 $s->storage->dbh_do (sub {
64 my ($storage, $dbh) = @_;
65 eval { $dbh->do("DROP TABLE event") };
68 id INT AUTO_INCREMENT NOT NULL,
69 starts_at DATE NOT NULL,
70 created_on DATETIME NOT NULL,
71 varchar_date VARCHAR(20),
72 varchar_datetime VARCHAR(20),
73 skip_inflation DATETIME,
74 ts_without_tz DATETIME,
79 $dbs_to_test{mysql} = 1;
83 DBICTest->init_schema( no_deploy=> 1, storage_type => '::DBI::mysql' )
86 ## copypasta'd for great justice
88 my ($dsn, $user, $pass) = @ENV{map { "DBICTEST_PG_${_}" } qw/DSN USER PASS/};
90 my $s = DBICTest::Schema->connect($dsn, $user, $pass);
91 try { $s->storage->ensure_connected };
93 $s->storage->dbh_do (sub {
94 my ($storage, $dbh) = @_;
95 eval { $dbh->do("DROP TABLE event") };
98 id SERIAL NOT NULL PRIMARY KEY,
99 starts_at DATE NOT NULL,
100 created_on TIMESTAMP NOT NULL,
101 varchar_date VARCHAR(20),
102 varchar_datetime VARCHAR(20),
103 skip_inflation TIMESTAMP,
104 ts_without_tz TIMESTAMP WITHOUT TIME ZONE
107 $dbs_to_test{postgres} = 1;
111 DBICTest->init_schema( no_deploy=> 1, storage_type => '::DBI::Pg' )
115 my ($dsn, $user, $pass) = @ENV{map { "DBICTEST_ORA_${_}" } qw/DSN USER PASS/};
117 my $s = DBICTest::Schema->connect($dsn, $user, $pass, { on_connect_call => 'datetime_setup' });
118 try { $s->storage->ensure_connected };
120 $s->storage->dbh_do (sub {
121 my ($storage, $dbh) = @_;
122 eval { $dbh->do("DROP TRIGGER trq_event_id") };
123 eval { $dbh->do("DROP SEQUENCE sq_event_id") };
124 eval { $dbh->do("DROP TABLE event") };
125 $dbh->do('CREATE SEQUENCE sq_event_id');
129 starts_at DATE NOT NULL,
130 created_on TIMESTAMP NOT NULL,
131 varchar_date VARCHAR(20),
132 varchar_datetime VARCHAR(20),
133 skip_inflation TIMESTAMP,
134 ts_without_tz TIMESTAMP,
135 CONSTRAINT PK_EVENT PRIMARY KEY (id)
139 CREATE TRIGGER trg_event_id
140 BEFORE INSERT ON event
142 new.id IS NULL OR new.id = 0
145 SELECT sq_event_id.nextval
150 $dbs_to_test{oracle} = 1;
154 DBICTest->init_schema( no_deploy=> 1, storage_type => '::DBI::Oracle::Generic', on_connect_call => 'datetime_setup' )
159 my %rs = map { $_ => $schema{$_}->resultset('Event') } keys %schema;
161 for (grep { $schema{$_}->storage->connected } keys %rs) {
163 [qw(starts_at created_on skip_inflation)],
164 ['2010-12-12', '2010-12-14 12:12:12', '2019-12-12 12:12:12'],
165 ['2010-12-12', '2011-12-14 12:12:12', '2011-12-12 12:12:12'],
169 my $date = DateTime->new(
182 skip_inflation => $_[2]
186 my $date2 = $date->clone->set_day(16);
189 ## search => { dbic_search_code/params }
190 ## rdbms_name => literal_sql
194 search => { 'me.created_on' => { -dt => $date } },
196 select => 'me.starts_at, me.created_on, me.skip_inflation',
197 where => 'me.created_on = ?',
198 bind => [[{ dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12' ]],
199 hri => [hri_thing('2010-12-12', '2010-12-14 12:12:12', '2019-12-12 12:12:12')],
202 select => 'me.starts_at, me.created_on, me.skip_inflation',
203 where => 'me.created_on = ?',
204 bind => [[{ dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12.000' ]],
205 hri => [hri_thing('2010-12-12', '2010-12-14 12:12:12.000', '2019-12-12 12:12:12.000')],
208 select => 'me.starts_at, me.created_on, me.skip_inflation',
209 where => 'me.created_on = ?',
210 bind => [[{ dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12' ]],
211 hri => [hri_thing('2010-12-12', '2010-12-14 12:12:12', '2019-12-12 12:12:12')],
214 select => 'me.starts_at, me.created_on, me.skip_inflation',
215 where => 'me.created_on = ?',
216 bind => [[{ dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12' ]],
217 hri => [hri_thing('2010-12-12', '2010-12-14 12:12:12', '2019-12-12 12:12:12')],
220 select => 'me.starts_at, me.created_on, me.skip_inflation',
221 where => 'me.created_on = ?',
222 bind => [[{ dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12' ]],
223 hri => [hri_thing('2010-12-12 00:00:00', '2010-12-14 12:12:12.000000', '2019-12-12 12:12:12.000000')],
225 msg => '-dt_now works',
229 search => { 'me.id' => 1 },
230 select => [ [ -dt_year => { -ident => 'me.created_on' } ] ],
233 select => "DATEPART(year, me.created_on)",
234 where => "me.id = ?",
235 bind => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
236 hri => [{ year => 2010 }],
239 select => "EXTRACT(YEAR FROM me.created_on)",
240 where => "me.id = ?",
241 bind => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
242 hri => [{ year => 2010 }],
245 select => "STRFTIME('%Y', me.created_on)",
246 where => "me.id = ?",
247 bind => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
248 hri => [{ year => 2010 }],
251 select => "date_part('year', me.created_on)",
252 where => "me.id = ?",
253 bind => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
254 hri => [{ year => 2010 }],
257 select => "EXTRACT(year FROM me.created_on)",
258 where => "me.id = ?",
259 bind => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
260 hri => [{ year => 2010 }],
262 msg => '-dt_year works',
266 msg => '-dt_get (year, month) works',
267 search => { 'me.id' => 1 },
268 select => [ [ -dt_get => [[qw(year month)], { -ident => 'me.created_on' }] ] ],
269 as => [ qw(year month) ],
271 select => "DATEPART(year, me.created_on), DATEPART(month, me.created_on)",
272 where => "me.id = ?",
273 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
274 hri => [{ year => 2010, month => 12 }],
277 select => "EXTRACT(YEAR FROM me.created_on), EXTRACT(MONTH FROM me.created_on)",
278 where => "me.id = ?",
279 bind => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
280 hri => [{ year => 2010, month => 12 }],
283 select => "STRFTIME('%Y', me.created_on), STRFTIME('%m', me.created_on)",
284 where => "me.id = ?",
285 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
286 hri => [{ year => 2010, month => 12 }],
289 select => "date_part('year', me.created_on), date_part('month', me.created_on)",
290 where => "me.id = ?",
291 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
292 hri => [{ year => 2010, month => 12 }],
295 select => "EXTRACT(year FROM me.created_on), EXTRACT(month FROM me.created_on)",
296 where => "me.id = ?",
297 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
298 hri => [{ year => 2010, month => 12 }],
303 msg => '-dt_month works',
304 search => { 'me.id' => 1 },
305 select => [ [ -dt_month => { -ident => 'me.created_on' } ] ],
308 select => "STRFTIME('%m', me.created_on)",
309 where => "me.id = ?",
310 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
311 hri => [{ month => 12 }],
314 select => "DATEPART(month, me.created_on)",
315 where => "me.id = ?",
316 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
317 hri => [{ month => 12 }],
320 select => "EXTRACT(MONTH FROM me.created_on)",
321 where => "me.id = ?",
322 bind => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
323 hri => [{ month => 12 }],
326 select => "date_part('month', me.created_on)",
327 where => "me.id = ?",
328 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
329 hri => [{ month => 12 }],
332 select => "EXTRACT(month FROM me.created_on)",
333 where => "me.id = ?",
334 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
335 hri => [{ month => 12 }],
340 msg => '-dt_day works',
341 search => { 'me.id' => 1 },
342 select => [ [ -dt_day => { -ident => 'me.created_on' } ] ],
345 select => "STRFTIME('%d', me.created_on)",
346 where => "me.id = ?",
347 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
348 hri => [{ day => 14 }],
351 select => "DATEPART(day, me.created_on)",
352 where => "me.id = ?",
353 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
354 hri => [{ day => 14 }],
357 select => "EXTRACT(DAY FROM me.created_on)",
358 where => "me.id = ?",
359 bind => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
360 hri => [{ day => 14 }],
363 select => "date_part('day', me.created_on)",
364 where => "me.id = ?",
365 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
366 hri => [{ day => 14 }],
369 select => "EXTRACT(day FROM me.created_on)",
370 where => "me.id = ?",
371 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
372 hri => [{ day => 14 }],
377 msg => '-dt_hour works',
378 search => { 'me.id' => 1 },
379 select => [ [ -dt_hour => { -ident => 'me.created_on' } ] ],
382 select => "STRFTIME('%H', me.created_on)",
383 where => "me.id = ?",
384 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
385 hri => [{ hour => 12 }],
388 select => "DATEPART(hour, me.created_on)",
389 where => "me.id = ?",
390 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
391 hri => [{ hour => 12 }],
394 select => "EXTRACT(HOUR FROM me.created_on)",
395 where => "me.id = ?",
396 bind => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
397 hri => [{ hour => 12 }],
400 select => "date_part('hour', me.created_on)",
401 where => "me.id = ?",
402 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
403 hri => [{ hour => 12 }],
406 select => "EXTRACT(hour FROM me.created_on)",
407 where => "me.id = ?",
408 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
409 hri => [{ hour => 12 }],
414 msg => '-dt_minute works',
415 search => { 'me.id' => 1 },
416 select => [ [ -dt_minute => { -ident => 'me.created_on' } ] ],
419 select => "STRFTIME('%M', me.created_on)",
420 where => "me.id = ?",
421 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
422 hri => [{ minute => 12 }],
425 select => "DATEPART(minute, me.created_on)",
426 where => "me.id = ?",
427 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
428 hri => [{ minute => 12 }],
431 select => "EXTRACT(MINUTE FROM me.created_on)",
432 where => "me.id = ?",
433 bind => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
434 hri => [{ minute => 12 }],
437 select => "date_part('minute', me.created_on)",
438 where => "me.id = ?",
439 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
440 hri => [{ minute => 12 }],
443 select => "EXTRACT(minute FROM me.created_on)",
444 where => "me.id = ?",
445 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
446 hri => [{ minute => 12 }],
451 msg => '-dt_second works',
452 search => { 'me.id' => 1 },
453 select => [ [ -dt_second => { -ident => 'me.created_on' } ] ],
456 select => "STRFTIME('%S', me.created_on)",
457 where => "me.id = ?",
458 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
459 hri => [{ second => 12 }],
462 select => "DATEPART(second, me.created_on)",
463 where => "me.id = ?",
464 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
465 hri => [{ second => 12 }],
468 select => "EXTRACT(SECOND FROM me.created_on)",
469 where => "me.id = ?",
470 bind => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
471 hri => [{ second => 12 }],
474 select => "date_part('second', me.created_on)",
475 where => "me.id = ?",
476 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
477 hri => [{ second => 12 }],
480 select => "EXTRACT(second FROM me.created_on)",
481 where => "me.id = ?",
482 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
483 hri => [{ second => 12 }],
488 msg => '-dt_diff (second) works',
489 search => { 'me.id' => 2 },
490 select => [ [ -dt_diff => [second => { -ident => 'me.created_on' }, \'me.skip_inflation' ] ] ],
491 as => [ 'sec_diff' ],
493 select => "(STRFTIME('%s', me.created_on) - STRFTIME('%s', me.skip_inflation))",
494 where => "me.id = ?",
495 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
496 hri => [{ sec_diff => 2*24*60*60 }],
499 select => "DATEDIFF(second, me.skip_inflation, me.created_on)",
500 where => "me.id = ?",
501 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
502 hri => [{ sec_diff => 2*24*60*60 }],
505 select => "TIMESTAMPDIFF(SECOND, me.skip_inflation, me.created_on)",
506 where => "me.id = ?",
507 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
508 hri => [{ sec_diff => 2*24*60*60 }],
511 select => "date_part('EPOCH', me.created_on) - date_part('EPOCH', me.skip_inflation)",
512 where => "me.id = ?",
513 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
514 hri => [{ sec_diff => 2*24*60*60 }],
517 select => "TRUNC(MONTHS_BETWEEN(me.created_on, me.skip_inflation) * 31 * 24 * 60 * 60)",
518 where => "me.id = ?",
519 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
520 hri => [{ sec_diff => 2*24*60*60 }],
525 msg => '-dt_diff (day) works',
526 search => { 'me.id' => 2 },
527 select => [ [ -dt_diff => [day => { -ident => 'me.created_on' }, \'me.skip_inflation' ] ] ],
528 as => [ 'day_diff' ],
530 select => "(JULIANDAY(me.created_on) - JULIANDAY(me.skip_inflation))",
531 where => "me.id = ?",
532 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
533 hri => [{ day_diff => 2 }],
536 select => "DATEDIFF(dayofyear, me.skip_inflation, me.created_on)",
537 where => "me.id = ?",
538 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
539 hri => [{ day_diff => 2 }],
542 select => "TIMESTAMPDIFF(DAY, me.skip_inflation, me.created_on)",
543 where => "me.id = ?",
544 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
545 hri => [{ day_diff => 2 }],
548 select => "date_part('DAY', me.created_on) - date_part('DAY', me.skip_inflation)",
549 where => "me.id = ?",
550 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
551 hri => [{ day_diff => 2 }],
554 select => "TRUNC(MONTHS_BETWEEN(me.created_on, me.skip_inflation) * 31)",
555 where => "me.id = ?",
556 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
557 hri => [{ day_diff => 2 }],
562 msg => '-dt_diff (year) works',
563 search => { 'me.id' => 2 },
564 select => [ [ -dt_diff => [year => \'me.starts_at', { -ident => 'me.created_on' } ] ] ],
567 exception_like => qr/date diff not supported for part "year" with database "SQLite"/,
570 select => "DATEDIFF(year, me.created_on, me.starts_at)",
571 where => "me.id = ?",
572 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
573 hri => [{ year => -1 }],
576 select => "TIMESTAMPDIFF(YEAR, me.created_on, me.starts_at)",
577 where => "me.id = ?",
578 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
579 hri => [{ year => -1 }],
582 select => "date_part('YEAR', me.starts_at) - date_part('YEAR', me.created_on)",
583 where => "me.id = ?",
584 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
585 hri => [{ year => -1 }],
588 select => "TRUNC(MONTHS_BETWEEN(me.starts_at, me.created_on) / 12)",
589 where => "me.id = ?",
590 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
591 hri => [{ year => -1 }],
596 msg => '-dt_add (year) works',
597 search => { 'me.id' => 2 },
598 select => [ [ -dt_add => [year => 3, { -ident => 'me.created_on' } ] ] ],
601 select => "(datetime(me.created_on, ? || ' years'))",
602 where => "me.id = ?",
603 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
604 hri => [{ date => '2014-12-14 12:12:12' }],
607 select => "(DATEADD(year, CAST(? AS INTEGER), me.created_on))",
608 where => "me.id = ?",
609 bind => [[{sqlt_datatype => 'integer'}, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
610 hri => [{ date => '2014-12-14 12:12:12.000' }],
613 select => "DATE_ADD(me.created_on, INTERVAL ? YEAR)",
614 where => "me.id = ?",
615 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
616 hri => [{ date => '2014-12-14 12:12:12' }],
619 select => "(me.created_on + ? * interval '1 YEAR')",
620 where => "me.id = ?",
621 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
622 hri => [{ date => '2014-12-14 12:12:12' }],
626 select => "(me.created_on + NUMTOYMINTERVAL(?, 'year'))",
627 where => "me.id = ?",
628 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
629 hri => [{ date => '2014-12-14 12:12:12.000000000' }],
634 msg => '-dt_add (month) works',
635 search => { 'me.id' => 2 },
636 select => [ [ -dt_add => [month => 3, { -ident => 'me.created_on' } ] ] ],
639 select => "(datetime(me.created_on, ? || ' months'))",
640 where => "me.id = ?",
641 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
642 hri => [{ date => '2012-03-14 12:12:12' }],
645 select => "(DATEADD(month, CAST(? AS INTEGER), me.created_on))",
646 where => "me.id = ?",
647 bind => [[{sqlt_datatype => 'integer'}, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
648 hri => [{ date => '2012-03-14 12:12:12.000' }],
651 select => "(me.created_on + ? * interval '1 MONTH')",
652 where => "me.id = ?",
653 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
654 hri => [{ date => '2012-03-14 12:12:12' }],
658 select => "DATE_ADD(me.created_on, INTERVAL ? MONTH)",
659 where => "me.id = ?",
660 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
661 hri => [{ date => '2012-03-14 12:12:12' }],
664 select => "(me.created_on + NUMTOYMINTERVAL(?, 'month'))",
665 where => "me.id = ?",
666 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
667 hri => [{ date => '2012-03-14 12:12:12.000000000' }],
672 msg => '-dt_add (day) works',
673 search => { 'me.id' => 2 },
674 select => [ [ -dt_add => [day => 3, { -ident => 'me.created_on' } ] ] ],
677 select => "(datetime(me.created_on, ? || ' days'))",
678 where => "me.id = ?",
679 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
680 hri => [{ date => '2011-12-17 12:12:12' }],
683 select => "(DATEADD(dayofyear, CAST(? AS INTEGER), me.created_on))",
684 where => "me.id = ?",
685 bind => [[{sqlt_datatype => 'integer'}, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
686 hri => [{ date => '2011-12-17 12:12:12.000' }],
689 select => "(me.created_on + ? * interval '1 DAY')",
690 where => "me.id = ?",
691 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
692 hri => [{ date => '2011-12-17 12:12:12' }],
696 select => "DATE_ADD(me.created_on, INTERVAL ? DAY)",
697 where => "me.id = ?",
698 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
699 hri => [{ date => '2011-12-17 12:12:12' }],
702 select => "(me.created_on + NUMTODSINTERVAL(?, 'day'))",
703 where => "me.id = ?",
704 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
705 hri => [{ date => '2011-12-17 12:12:12.000000000' }],
710 msg => '-dt_add (hour) works',
711 search => { 'me.id' => 2 },
712 select => [ [ -dt_add => [hour => 3, { -ident => 'me.created_on' } ] ] ],
715 select => "(datetime(me.created_on, ? || ' hours'))",
716 where => "me.id = ?",
717 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
718 hri => [{ date => '2011-12-14 15:12:12' }],
721 select => "(DATEADD(hour, CAST(? AS INTEGER), me.created_on))",
722 where => "me.id = ?",
723 bind => [[{sqlt_datatype => 'integer'}, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
724 hri => [{ date => '2011-12-14 15:12:12.000' }],
727 select => "(me.created_on + ? * interval '1 HOUR')",
728 where => "me.id = ?",
729 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
730 hri => [{ date => '2011-12-14 15:12:12' }],
733 select => "DATE_ADD(me.created_on, INTERVAL ? HOUR)",
734 where => "me.id = ?",
735 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
736 hri => [{ date => '2011-12-14 15:12:12' }],
739 select => "(me.created_on + NUMTODSINTERVAL(?, 'hour'))",
740 where => "me.id = ?",
741 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
742 hri => [{ date => '2011-12-14 15:12:12.000000000' }],
747 msg => '-dt_add (minute) works',
748 search => { 'me.id' => 2 },
749 select => [ [ -dt_add => [minute => 3, { -ident => 'me.created_on' } ] ] ],
752 select => "(datetime(me.created_on, ? || ' minutes'))",
753 where => "me.id = ?",
754 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
755 hri => [{ date => '2011-12-14 12:15:12' }],
758 select => "(DATEADD(minute, CAST(? AS INTEGER), me.created_on))",
759 where => "me.id = ?",
760 bind => [[{sqlt_datatype => 'integer'}, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
761 hri => [{ date => '2011-12-14 12:15:12.000' }],
764 select => "(me.created_on + ? * interval '1 MINUTE')",
765 where => "me.id = ?",
766 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
767 hri => [{ date => '2011-12-14 12:15:12' }],
770 select => "DATE_ADD(me.created_on, INTERVAL ? MINUTE)",
771 where => "me.id = ?",
772 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
773 hri => [{ date => '2011-12-14 12:15:12' }],
776 select => "(me.created_on + NUMTODSINTERVAL(?, 'minute'))",
777 where => "me.id = ?",
778 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
779 hri => [{ date => '2011-12-14 12:15:12.000000000' }],
784 msg => '-dt_add (second) works',
785 search => { 'me.id' => 2 },
786 select => [ [ -dt_add => [second => 3, { -ident => 'me.created_on' } ] ] ],
789 select => "(datetime(me.created_on, ? || ' seconds'))",
790 where => "me.id = ?",
791 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
792 hri => [{ date => '2011-12-14 12:12:15' }],
795 select => "(DATEADD(second, CAST(? AS INTEGER), me.created_on))",
796 where => "me.id = ?",
797 bind => [[{sqlt_datatype => 'integer'}, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
798 hri => [{ date => '2011-12-14 12:12:15.000' }],
801 select => "(me.created_on + ? * interval '1 SECOND')",
802 where => "me.id = ?",
803 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
804 hri => [{ date => '2011-12-14 12:12:15' }],
807 select => "DATE_ADD(me.created_on, INTERVAL ? SECOND)",
808 where => "me.id = ?",
809 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
810 hri => [{ date => '2011-12-14 12:12:15' }],
813 select => "(me.created_on + NUMTODSINTERVAL(?, 'second'))",
814 where => "me.id = ?",
815 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
816 hri => [{ date => '2011-12-14 12:12:15.000000000' }],
821 msg => 'nested -dt_add works',
822 search => { 'me.id' => 2 },
823 select => [ [ -dt_add => [second => 3, { -dt_add => [ day => 1, { -ident => 'me.created_on' } ] } ] ] ],
826 select => "(datetime((datetime(me.created_on, ? || ' days')), ? || ' seconds'))",
827 where => "me.id = ?",
828 bind => [[unknown_col, 1], [unknown_col, 3 ], [{dbic_colname => 'me.id', sqlt_datatype => 'integer' }, 2]],
829 hri => [{ date => '2011-12-15 12:12:15' }],
832 select => "(DATEADD(second, CAST(? AS INTEGER), (DATEADD(dayofyear, CAST(? AS INTEGER), me.created_on))))",
833 where => "me.id = ?",
834 bind => [[{sqlt_datatype => 'integer'}, 3 ], [{sqlt_datatype => 'integer'}, 1], [{dbic_colname => 'me.id', sqlt_datatype => 'integer' }, 2]],
835 hri => [{ date => '2011-12-15 12:12:15.000' }],
838 select => "((me.created_on + ? * interval '1 DAY') + ? * interval '1 SECOND')",
839 where => "me.id = ?",
840 bind => [[unknown_col, 1], [unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
841 hri => [{ date => '2011-12-15 12:12:15' }],
844 select => "DATE_ADD(DATE_ADD(me.created_on, INTERVAL ? DAY), INTERVAL ? SECOND)",
845 where => "me.id = ?",
846 bind => [[unknown_col, 1], [unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
847 hri => [{ date => '2011-12-15 12:12:15' }],
850 select => "((me.created_on + NUMTODSINTERVAL(?, 'day')) + NUMTODSINTERVAL(?, 'second'))",
851 where => "me.id = ?",
852 bind => [[unknown_col, 1], [unknown_col, 3 ], [{dbic_colname => 'me.id', sqlt_datatype => 'integer' }, 2]],
853 hri => [{ date => '2011-12-15 12:12:15.000000000' }],
858 msg => '-dt_before works',
859 search => { 'me.created_on' => { -dt_before => '2011-12-14 12:12:12' } },
860 select => [ [ -ident => 'me.created_on' ] ],
863 select => "me.created_on",
864 where => "me.created_on < ?",
865 bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2011-12-14 12:12:12']],
866 hri => [{ date => '2010-12-14 12:12:12' }],
869 select => "me.created_on",
870 where => "me.created_on < ?",
871 bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2011-12-14 12:12:12']],
872 hri => [{ date => '2010-12-14 12:12:12' }],
875 select => "me.created_on",
876 where => "me.created_on < ?",
877 bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2011-12-14 12:12:12']],
878 hri => [{ date => '2010-12-14 12:12:12' }],
881 select => "me.created_on",
882 where => "me.created_on < ?",
883 bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2011-12-14 12:12:12']],
884 hri => [{ date => '2010-12-14 12:12:12' }],
887 select => "me.created_on",
888 where => "me.created_on < ?",
889 bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2011-12-14 12:12:12']],
890 hri => [{ date => '2010-12-14 12:12:12' }],
895 msg => '-dt_on_or_before works',
896 search => { 'me.created_on' => { -dt_on_or_before => '2011-12-14 12:12:12' } },
897 select => [ [ -ident => 'me.created_on' ] ],
900 select => "me.created_on",
901 where => "me.created_on <= ?",
902 bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2011-12-14 12:12:12']],
903 hri => [{ date => '2010-12-14 12:12:12' }, { date => '2011-12-14 12:12:12' }],
906 select => "me.created_on",
907 where => "me.created_on <= ?",
908 bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2011-12-14 12:12:12']],
909 hri => [{ date => '2010-12-14 12:12:12' }, { date => '2011-12-14 12:12:12' }],
912 select => "me.created_on",
913 where => "me.created_on <= ?",
914 bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2011-12-14 12:12:12']],
915 hri => [{ date => '2010-12-14 12:12:12' }, { date => '2011-12-14 12:12:12' }],
918 select => "me.created_on",
919 where => "me.created_on <= ?",
920 bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2011-12-14 12:12:12']],
921 hri => [{ date => '2010-12-14 12:12:12' }, { date => '2011-12-14 12:12:12' }],
924 select => "me.created_on",
925 where => "me.created_on <= ?",
926 bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2011-12-14 12:12:12']],
927 hri => [{ date => '2010-12-14 12:12:12' }, { date => '2011-12-14 12:12:12' }],
932 msg => '-dt_after works',
933 search => { 'me.created_on' => { -dt_after => '2010-12-14 12:12:12' } },
934 select => [ [ -ident => 'me.created_on' ] ],
937 select => "me.created_on",
938 where => "me.created_on > ?",
939 bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12']],
940 hri => [{ date => '2011-12-14 12:12:12' }],
943 select => "me.created_on",
944 where => "me.created_on > ?",
945 bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12']],
946 hri => [{ date => '2011-12-14 12:12:12' }],
949 select => "me.created_on",
950 where => "me.created_on > ?",
951 bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12']],
952 hri => [{ date => '2011-12-14 12:12:12' }],
955 select => "me.created_on",
956 where => "me.created_on > ?",
957 bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12']],
958 hri => [{ date => '2011-12-14 12:12:12' }],
961 select => "me.created_on",
962 where => "me.created_on > ?",
963 bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12']],
964 hri => [{ date => '2011-12-14 12:12:12' }],
969 msg => '-dt_on_or_after works',
970 search => { 'me.created_on' => { -dt_on_or_after => '2010-12-14 12:12:12' } },
971 select => [ [ -ident => 'me.created_on' ] ],
974 select => "me.created_on",
975 where => "me.created_on >= ?",
976 bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12']],
977 hri => [{ date => '2010-12-14 12:12:12' }, { date => '2011-12-14 12:12:12' }],
980 select => "me.created_on",
981 where => "me.created_on >= ?",
982 bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12']],
983 hri => [{ date => '2010-12-14 12:12:12' }, { date => '2011-12-14 12:12:12' }],
986 select => "me.created_on",
987 where => "me.created_on >= ?",
988 bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12']],
989 hri => [{ date => '2010-12-14 12:12:12' }, { date => '2011-12-14 12:12:12' }],
992 select => "me.created_on",
993 where => "me.created_on >= ?",
994 bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12']],
995 hri => [{ date => '2010-12-14 12:12:12' }, { date => '2011-12-14 12:12:12' }],
998 select => "me.created_on",
999 where => "me.created_on >= ?",
1000 bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12']],
1001 hri => [{ date => '2010-12-14 12:12:12' }, { date => '2011-12-14 12:12:12' }],
1007 for my $t (@tests) {
1010 for my $db (keys %rs) {
1011 my $db_test = $t->{$db};
1013 ok 0, "$t->{msg} ($db not tested!)";
1021 ( exists $t->{select}
1022 ? ( select => $t->{select}, as => $t->{as} )
1023 : ( columns => [qw(starts_at created_on skip_inflation)] )
1026 $my_rs = $rs{$db}->search($t->{search}, $stuff);
1027 $r = $my_rs->as_query
1030 if ($db_test->{exception_like}) {
1033 $db_test->{exception_like},
1034 "throws the expected exception ($db_test->{exception_like})",
1037 if ($db_test->{warning_like}) {
1040 $db_test->{warning_like},
1041 "issues the expected warning ($db_test->{warning_like})"
1049 "(SELECT $db_test->{select} FROM event me WHERE $db_test->{where})",
1051 ($t->{msg} ? "$t->{msg} ($db)" : ())
1055 if (my $hri = $db_test->{hri}) {
1056 skip "Cannot test database we are not connected to ($db)", 1 unless $dbs_to_test{$db};
1057 skip $db_test->{skip} . " ($db)", 1 if $db_test->{skip};
1059 my $msg = ($t->{msg} ? "$t->{msg} ($db actually pulls expected data)" : '');
1061 my $got = [ $my_rs->hri_dump->all ];
1062 my $success = is_deeply $got, $hri, $msg;
1064 warn "$db: $t->{msg} got: " . Dumper $got;
1065 warn "$db: $t->{msg} expected: " . Dumper $hri;