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 $rs{sqlite}->populate([
162 [qw(starts_at created_on skip_inflation)],
163 ['2010-12-12', '2010-12-14 12:12:12', '2019-12-12 12:12:12'],
164 ['2010-12-12', '2011-12-14 12:12:12', '2011-12-12 12:12:12'],
167 $rs{mssql}->populate([
168 [qw(starts_at created_on skip_inflation)],
169 ['2010-12-12', '2010-12-14 12:12:12.000', '2019-12-12 12:12:12.000'],
170 ['2010-12-12', '2011-12-14 12:12:12.000', '2011-12-12 12:12:12.000'],
171 ]) if $schema{mssql}->storage->connected;
173 $rs{mysql}->populate([
174 [qw(starts_at created_on skip_inflation)],
175 ['2010-12-12', '2010-12-14 12:12:12.000', '2019-12-12 12:12:12.000'],
176 ['2010-12-12', '2011-12-14 12:12:12.000', '2011-12-12 12:12:12.000'],
177 ]) if $schema{mysql}->storage->connected;
179 $rs{postgres}->populate([
180 [qw(starts_at created_on skip_inflation)],
181 ['2010-12-12', '2010-12-14 12:12:12', '2019-12-12 12:12:12'],
182 ['2010-12-12', '2011-12-14 12:12:12', '2011-12-12 12:12:12'],
183 ]) if $schema{postgres}->storage->connected;
185 $rs{oracle}->populate([
186 [qw(starts_at created_on skip_inflation)],
187 ['2010-12-12', '2010-12-14 12:12:12', '2019-12-12 12:12:12'],
188 ['2010-12-12', '2011-12-14 12:12:12', '2011-12-12 12:12:12'],
189 ]) if $schema{oracle}->storage->connected;
191 my $date = DateTime->new(
204 skip_inflation => $_[2]
208 my $date2 = $date->clone->set_day(16);
211 ## search => { dbic_search_code/params }
212 ## rdbms_name => literal_sql
216 search => { 'me.created_on' => { -dt => $date } },
218 select => 'me.starts_at, me.created_on, me.skip_inflation',
219 where => 'me.created_on = ?',
220 bind => [[{ dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12' ]],
221 hri => [hri_thing('2010-12-12', '2010-12-14 12:12:12', '2019-12-12 12:12:12')],
224 select => 'me.starts_at, me.created_on, me.skip_inflation',
225 where => 'me.created_on = ?',
226 bind => [[{ dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12.000' ]],
227 hri => [hri_thing('2010-12-12', '2010-12-14 12:12:12.000', '2019-12-12 12:12:12.000')],
230 select => 'me.starts_at, me.created_on, me.skip_inflation',
231 where => 'me.created_on = ?',
232 bind => [[{ dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12' ]],
233 hri => [hri_thing('2010-12-12', '2010-12-14 12:12:12', '2019-12-12 12:12:12')],
236 select => 'me.starts_at, me.created_on, me.skip_inflation',
237 where => 'me.created_on = ?',
238 bind => [[{ dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12' ]],
239 hri => [hri_thing('2010-12-12', '2010-12-14 12:12:12', '2019-12-12 12:12:12')],
242 select => 'me.starts_at, me.created_on, me.skip_inflation',
243 where => 'me.created_on = ?',
244 bind => [[{ dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12' ]],
245 hri => [hri_thing('2010-12-12 00:00:00', '2010-12-14 12:12:12.000000', '2019-12-12 12:12:12.000000')],
247 msg => '-dt_now works',
251 search => { 'me.id' => 1 },
252 select => [ [ -dt_year => { -ident => 'me.created_on' } ] ],
255 select => "DATEPART(year, me.created_on)",
256 where => "me.id = ?",
257 bind => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
258 hri => [{ year => 2010 }],
261 select => "EXTRACT(YEAR FROM me.created_on)",
262 where => "me.id = ?",
263 bind => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
264 hri => [{ year => 2010 }],
267 select => "STRFTIME('%Y', me.created_on)",
268 where => "me.id = ?",
269 bind => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
270 hri => [{ year => 2010 }],
273 select => "date_part('year', me.created_on)",
274 where => "me.id = ?",
275 bind => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
276 hri => [{ year => 2010 }],
279 select => "EXTRACT(year FROM me.created_on)",
280 where => "me.id = ?",
281 bind => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
282 hri => [{ year => 2010 }],
284 msg => '-dt_year works',
288 msg => '-dt_get (year, month) works',
289 search => { 'me.id' => 1 },
290 select => [ [ -dt_get => [[qw(year month)], { -ident => 'me.created_on' }] ] ],
291 as => [ qw(year month) ],
293 select => "DATEPART(year, me.created_on), DATEPART(month, me.created_on)",
294 where => "me.id = ?",
295 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
296 hri => [{ year => 2010, month => 12 }],
299 select => "EXTRACT(YEAR FROM me.created_on), EXTRACT(MONTH FROM me.created_on)",
300 where => "me.id = ?",
301 bind => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
302 hri => [{ year => 2010, month => 12 }],
305 select => "STRFTIME('%Y', me.created_on), STRFTIME('%m', me.created_on)",
306 where => "me.id = ?",
307 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
308 hri => [{ year => 2010, month => 12 }],
311 select => "date_part('year', me.created_on), date_part('month', me.created_on)",
312 where => "me.id = ?",
313 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
314 hri => [{ year => 2010, month => 12 }],
317 select => "EXTRACT(year FROM me.created_on), EXTRACT(month FROM me.created_on)",
318 where => "me.id = ?",
319 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
320 hri => [{ year => 2010, month => 12 }],
325 msg => '-dt_month works',
326 search => { 'me.id' => 1 },
327 select => [ [ -dt_month => { -ident => 'me.created_on' } ] ],
330 select => "STRFTIME('%m', me.created_on)",
331 where => "me.id = ?",
332 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
333 hri => [{ month => 12 }],
336 select => "DATEPART(month, me.created_on)",
337 where => "me.id = ?",
338 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
339 hri => [{ month => 12 }],
342 select => "EXTRACT(MONTH FROM me.created_on)",
343 where => "me.id = ?",
344 bind => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
345 hri => [{ month => 12 }],
348 select => "date_part('month', me.created_on)",
349 where => "me.id = ?",
350 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
351 hri => [{ month => 12 }],
354 select => "EXTRACT(month FROM me.created_on)",
355 where => "me.id = ?",
356 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
357 hri => [{ month => 12 }],
362 msg => '-dt_day works',
363 search => { 'me.id' => 1 },
364 select => [ [ -dt_day => { -ident => 'me.created_on' } ] ],
367 select => "STRFTIME('%d', me.created_on)",
368 where => "me.id = ?",
369 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
370 hri => [{ day => 14 }],
373 select => "DATEPART(day, me.created_on)",
374 where => "me.id = ?",
375 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
376 hri => [{ day => 14 }],
379 select => "EXTRACT(DAY FROM me.created_on)",
380 where => "me.id = ?",
381 bind => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
382 hri => [{ day => 14 }],
385 select => "date_part('day', me.created_on)",
386 where => "me.id = ?",
387 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
388 hri => [{ day => 14 }],
391 select => "EXTRACT(day FROM me.created_on)",
392 where => "me.id = ?",
393 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
394 hri => [{ day => 14 }],
399 msg => '-dt_hour works',
400 search => { 'me.id' => 1 },
401 select => [ [ -dt_hour => { -ident => 'me.created_on' } ] ],
404 select => "STRFTIME('%H', me.created_on)",
405 where => "me.id = ?",
406 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
407 hri => [{ hour => 12 }],
410 select => "DATEPART(hour, me.created_on)",
411 where => "me.id = ?",
412 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
413 hri => [{ hour => 12 }],
416 select => "EXTRACT(HOUR FROM me.created_on)",
417 where => "me.id = ?",
418 bind => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
419 hri => [{ hour => 12 }],
422 select => "date_part('hour', me.created_on)",
423 where => "me.id = ?",
424 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
425 hri => [{ hour => 12 }],
428 select => "EXTRACT(hour FROM me.created_on)",
429 where => "me.id = ?",
430 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
431 hri => [{ hour => 12 }],
436 msg => '-dt_minute works',
437 search => { 'me.id' => 1 },
438 select => [ [ -dt_minute => { -ident => 'me.created_on' } ] ],
441 select => "STRFTIME('%M', me.created_on)",
442 where => "me.id = ?",
443 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
444 hri => [{ minute => 12 }],
447 select => "DATEPART(minute, me.created_on)",
448 where => "me.id = ?",
449 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
450 hri => [{ minute => 12 }],
453 select => "EXTRACT(MINUTE FROM me.created_on)",
454 where => "me.id = ?",
455 bind => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
456 hri => [{ minute => 12 }],
459 select => "date_part('minute', me.created_on)",
460 where => "me.id = ?",
461 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
462 hri => [{ minute => 12 }],
465 select => "EXTRACT(minute FROM me.created_on)",
466 where => "me.id = ?",
467 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
468 hri => [{ minute => 12 }],
473 msg => '-dt_second works',
474 search => { 'me.id' => 1 },
475 select => [ [ -dt_second => { -ident => 'me.created_on' } ] ],
478 select => "STRFTIME('%S', me.created_on)",
479 where => "me.id = ?",
480 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
481 hri => [{ second => 12 }],
484 select => "DATEPART(second, me.created_on)",
485 where => "me.id = ?",
486 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
487 hri => [{ second => 12 }],
490 select => "EXTRACT(SECOND FROM me.created_on)",
491 where => "me.id = ?",
492 bind => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
493 hri => [{ second => 12 }],
496 select => "date_part('second', me.created_on)",
497 where => "me.id = ?",
498 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
499 hri => [{ second => 12 }],
502 select => "EXTRACT(second FROM me.created_on)",
503 where => "me.id = ?",
504 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
505 hri => [{ second => 12 }],
510 msg => '-dt_diff (second) works',
511 search => { 'me.id' => 2 },
512 select => [ [ -dt_diff => [second => { -ident => 'me.created_on' }, \'me.skip_inflation' ] ] ],
513 as => [ 'sec_diff' ],
515 select => "(STRFTIME('%s', me.created_on) - STRFTIME('%s', me.skip_inflation))",
516 where => "me.id = ?",
517 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
518 hri => [{ sec_diff => 2*24*60*60 }],
521 select => "DATEDIFF(second, me.skip_inflation, me.created_on)",
522 where => "me.id = ?",
523 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
524 hri => [{ sec_diff => 2*24*60*60 }],
527 select => "TIMESTAMPDIFF(SECOND, me.skip_inflation, me.created_on)",
528 where => "me.id = ?",
529 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
530 hri => [{ sec_diff => 2*24*60*60 }],
533 select => "date_part('EPOCH', me.created_on) - date_part('EPOCH', me.skip_inflation)",
534 where => "me.id = ?",
535 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
536 hri => [{ sec_diff => 2*24*60*60 }],
539 select => "TRUNC(MONTHS_BETWEEN(me.created_on, me.skip_inflation) * 31 * 24 * 60 * 60)",
540 where => "me.id = ?",
541 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
542 hri => [{ sec_diff => 2*24*60*60 }],
547 msg => '-dt_diff (day) works',
548 search => { 'me.id' => 2 },
549 select => [ [ -dt_diff => [day => { -ident => 'me.created_on' }, \'me.skip_inflation' ] ] ],
550 as => [ 'day_diff' ],
552 select => "(JULIANDAY(me.created_on) - JULIANDAY(me.skip_inflation))",
553 where => "me.id = ?",
554 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
555 hri => [{ day_diff => 2 }],
558 select => "DATEDIFF(dayofyear, me.skip_inflation, me.created_on)",
559 where => "me.id = ?",
560 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
561 hri => [{ day_diff => 2 }],
564 select => "TIMESTAMPDIFF(DAY, me.skip_inflation, me.created_on)",
565 where => "me.id = ?",
566 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
567 hri => [{ day_diff => 2 }],
570 select => "date_part('DAY', me.created_on) - date_part('DAY', me.skip_inflation)",
571 where => "me.id = ?",
572 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
573 hri => [{ day_diff => 2 }],
576 select => "TRUNC(MONTHS_BETWEEN(me.created_on, me.skip_inflation) * 31)",
577 where => "me.id = ?",
578 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
579 hri => [{ day_diff => 2 }],
584 msg => '-dt_diff (year) works',
585 search => { 'me.id' => 2 },
586 select => [ [ -dt_diff => [year => \'me.starts_at', { -ident => 'me.created_on' } ] ] ],
589 exception_like => qr/date diff not supported for part "year" with database "SQLite"/,
592 select => "DATEDIFF(year, me.created_on, me.starts_at)",
593 where => "me.id = ?",
594 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
595 hri => [{ year => -1 }],
598 select => "TIMESTAMPDIFF(YEAR, me.created_on, me.starts_at)",
599 where => "me.id = ?",
600 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
601 hri => [{ year => -1 }],
604 select => "date_part('YEAR', me.starts_at) - date_part('YEAR', me.created_on)",
605 where => "me.id = ?",
606 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
607 hri => [{ year => -1 }],
610 select => "TRUNC(MONTHS_BETWEEN(me.starts_at, me.created_on) / 12)",
611 where => "me.id = ?",
612 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
613 hri => [{ year => -1 }],
618 msg => '-dt_add (year) works',
619 search => { 'me.id' => 2 },
620 select => [ [ -dt_add => [year => 3, { -ident => 'me.created_on' } ] ] ],
623 select => "(datetime(me.created_on, ? || ' years'))",
624 where => "me.id = ?",
625 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
626 hri => [{ date => '2014-12-14 12:12:12' }],
629 select => "(DATEADD(year, CAST(? AS INTEGER), me.created_on))",
630 where => "me.id = ?",
631 bind => [[{sqlt_datatype => 'integer'}, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
632 hri => [{ date => '2014-12-14 12:12:12.000' }],
635 select => "DATE_ADD(me.created_on, INTERVAL ? YEAR)",
636 where => "me.id = ?",
637 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
638 hri => [{ date => '2014-12-14 12:12:12' }],
641 select => "(me.created_on + ? * interval '1 YEAR')",
642 where => "me.id = ?",
643 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
644 hri => [{ date => '2014-12-14 12:12:12' }],
648 select => "(me.created_on + NUMTOYMINTERVAL(?, 'year'))",
649 where => "me.id = ?",
650 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
651 hri => [{ date => '2014-12-14 12:12:12.000000000' }],
656 msg => '-dt_add (month) works',
657 search => { 'me.id' => 2 },
658 select => [ [ -dt_add => [month => 3, { -ident => 'me.created_on' } ] ] ],
661 select => "(datetime(me.created_on, ? || ' months'))",
662 where => "me.id = ?",
663 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
664 hri => [{ date => '2012-03-14 12:12:12' }],
667 select => "(DATEADD(month, CAST(? AS INTEGER), me.created_on))",
668 where => "me.id = ?",
669 bind => [[{sqlt_datatype => 'integer'}, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
670 hri => [{ date => '2012-03-14 12:12:12.000' }],
673 select => "(me.created_on + ? * interval '1 MONTH')",
674 where => "me.id = ?",
675 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
676 hri => [{ date => '2012-03-14 12:12:12' }],
680 select => "DATE_ADD(me.created_on, INTERVAL ? MONTH)",
681 where => "me.id = ?",
682 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
683 hri => [{ date => '2012-03-14 12:12:12' }],
686 select => "(me.created_on + NUMTOYMINTERVAL(?, 'month'))",
687 where => "me.id = ?",
688 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
689 hri => [{ date => '2012-03-14 12:12:12.000000000' }],
694 msg => '-dt_add (day) works',
695 search => { 'me.id' => 2 },
696 select => [ [ -dt_add => [day => 3, { -ident => 'me.created_on' } ] ] ],
699 select => "(datetime(me.created_on, ? || ' days'))",
700 where => "me.id = ?",
701 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
702 hri => [{ date => '2011-12-17 12:12:12' }],
705 select => "(DATEADD(dayofyear, CAST(? AS INTEGER), me.created_on))",
706 where => "me.id = ?",
707 bind => [[{sqlt_datatype => 'integer'}, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
708 hri => [{ date => '2011-12-17 12:12:12.000' }],
711 select => "(me.created_on + ? * interval '1 DAY')",
712 where => "me.id = ?",
713 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
714 hri => [{ date => '2011-12-17 12:12:12' }],
718 select => "DATE_ADD(me.created_on, INTERVAL ? DAY)",
719 where => "me.id = ?",
720 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
721 hri => [{ date => '2011-12-17 12:12:12' }],
724 select => "(me.created_on + NUMTODSINTERVAL(?, 'day'))",
725 where => "me.id = ?",
726 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
727 hri => [{ date => '2011-12-17 12:12:12.000000000' }],
732 msg => '-dt_add (hour) works',
733 search => { 'me.id' => 2 },
734 select => [ [ -dt_add => [hour => 3, { -ident => 'me.created_on' } ] ] ],
737 select => "(datetime(me.created_on, ? || ' hours'))",
738 where => "me.id = ?",
739 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
740 hri => [{ date => '2011-12-14 15:12:12' }],
743 select => "(DATEADD(hour, CAST(? AS INTEGER), me.created_on))",
744 where => "me.id = ?",
745 bind => [[{sqlt_datatype => 'integer'}, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
746 hri => [{ date => '2011-12-14 15:12:12.000' }],
749 select => "(me.created_on + ? * interval '1 HOUR')",
750 where => "me.id = ?",
751 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
752 hri => [{ date => '2011-12-14 15:12:12' }],
755 select => "DATE_ADD(me.created_on, INTERVAL ? HOUR)",
756 where => "me.id = ?",
757 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
758 hri => [{ date => '2011-12-14 15:12:12' }],
761 select => "(me.created_on + NUMTODSINTERVAL(?, 'hour'))",
762 where => "me.id = ?",
763 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
764 hri => [{ date => '2011-12-14 15:12:12.000000000' }],
769 msg => '-dt_add (minute) works',
770 search => { 'me.id' => 2 },
771 select => [ [ -dt_add => [minute => 3, { -ident => 'me.created_on' } ] ] ],
774 select => "(datetime(me.created_on, ? || ' minutes'))",
775 where => "me.id = ?",
776 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
777 hri => [{ date => '2011-12-14 12:15:12' }],
780 select => "(DATEADD(minute, CAST(? AS INTEGER), me.created_on))",
781 where => "me.id = ?",
782 bind => [[{sqlt_datatype => 'integer'}, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
783 hri => [{ date => '2011-12-14 12:15:12.000' }],
786 select => "(me.created_on + ? * interval '1 MINUTE')",
787 where => "me.id = ?",
788 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
789 hri => [{ date => '2011-12-14 12:15:12' }],
792 select => "DATE_ADD(me.created_on, INTERVAL ? MINUTE)",
793 where => "me.id = ?",
794 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
795 hri => [{ date => '2011-12-14 12:15:12' }],
798 select => "(me.created_on + NUMTODSINTERVAL(?, 'minute'))",
799 where => "me.id = ?",
800 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
801 hri => [{ date => '2011-12-14 12:15:12.000000000' }],
806 msg => '-dt_add (second) works',
807 search => { 'me.id' => 2 },
808 select => [ [ -dt_add => [second => 3, { -ident => 'me.created_on' } ] ] ],
811 select => "(datetime(me.created_on, ? || ' seconds'))",
812 where => "me.id = ?",
813 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
814 hri => [{ date => '2011-12-14 12:12:15' }],
817 select => "(DATEADD(second, CAST(? AS INTEGER), me.created_on))",
818 where => "me.id = ?",
819 bind => [[{sqlt_datatype => 'integer'}, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
820 hri => [{ date => '2011-12-14 12:12:15.000' }],
823 select => "(me.created_on + ? * interval '1 SECOND')",
824 where => "me.id = ?",
825 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
826 hri => [{ date => '2011-12-14 12:12:15' }],
829 select => "DATE_ADD(me.created_on, INTERVAL ? SECOND)",
830 where => "me.id = ?",
831 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
832 hri => [{ date => '2011-12-14 12:12:15' }],
835 select => "(me.created_on + NUMTODSINTERVAL(?, 'second'))",
836 where => "me.id = ?",
837 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
838 hri => [{ date => '2011-12-14 12:12:15.000000000' }],
843 msg => 'nested -dt_add works',
844 search => { 'me.id' => 2 },
845 select => [ [ -dt_add => [second => 3, { -dt_add => [ day => 1, { -ident => 'me.created_on' } ] } ] ] ],
848 select => "(datetime((datetime(me.created_on, ? || ' days')), ? || ' seconds'))",
849 where => "me.id = ?",
850 bind => [[unknown_col, 1], [unknown_col, 3 ], [{dbic_colname => 'me.id', sqlt_datatype => 'integer' }, 2]],
851 hri => [{ date => '2011-12-15 12:12:15' }],
854 select => "(DATEADD(second, CAST(? AS INTEGER), (DATEADD(dayofyear, CAST(? AS INTEGER), me.created_on))))",
855 where => "me.id = ?",
856 bind => [[{sqlt_datatype => 'integer'}, 3 ], [{sqlt_datatype => 'integer'}, 1], [{dbic_colname => 'me.id', sqlt_datatype => 'integer' }, 2]],
857 hri => [{ date => '2011-12-15 12:12:15.000' }],
860 select => "((me.created_on + ? * interval '1 DAY') + ? * interval '1 SECOND')",
861 where => "me.id = ?",
862 bind => [[unknown_col, 1], [unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
863 hri => [{ date => '2011-12-15 12:12:15' }],
866 select => "DATE_ADD(DATE_ADD(me.created_on, INTERVAL ? DAY), INTERVAL ? SECOND)",
867 where => "me.id = ?",
868 bind => [[unknown_col, 1], [unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
869 hri => [{ date => '2011-12-15 12:12:15' }],
872 select => "((me.created_on + NUMTODSINTERVAL(?, 'day')) + NUMTODSINTERVAL(?, 'second'))",
873 where => "me.id = ?",
874 bind => [[unknown_col, 1], [unknown_col, 3 ], [{dbic_colname => 'me.id', sqlt_datatype => 'integer' }, 2]],
875 hri => [{ date => '2011-12-15 12:12:15.000000000' }],
883 for my $db (keys %rs) {
884 my $db_test = $t->{$db};
886 ok 0, "$t->{msg} ($db not tested!)";
894 ( exists $t->{select}
895 ? ( select => $t->{select}, as => $t->{as} )
896 : ( columns => [qw(starts_at created_on skip_inflation)] )
899 $my_rs = $rs{$db}->search($t->{search}, $stuff);
900 $r = $my_rs->as_query
903 if ($db_test->{exception_like}) {
906 $db_test->{exception_like},
907 "throws the expected exception ($db_test->{exception_like})",
910 if ($db_test->{warning_like}) {
913 $db_test->{warning_like},
914 "issues the expected warning ($db_test->{warning_like})"
922 "(SELECT $db_test->{select} FROM event me WHERE $db_test->{where})",
924 ($t->{msg} ? "$t->{msg} ($db)" : ())
928 if (my $hri = $db_test->{hri}) {
929 skip "Cannot test database we are not connected to ($db)", 1 unless $dbs_to_test{$db};
930 skip $db_test->{skip} . " ($db)", 1 if $db_test->{skip};
932 my $msg = ($t->{msg} ? "$t->{msg} ($db actually pulls expected data)" : '');
934 my $got = [ $my_rs->hri_dump->all ];
935 my $success = is_deeply $got, $hri, $msg;
937 warn "$db: $t->{msg} got: " . Dumper $got;
938 warn "$db: $t->{msg} expected: " . Dumper $hri;