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 => "EXTRACT(year FROM 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 => "EXTRACT(year FROM me.created_on), EXTRACT(month FROM 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 => "EXTRACT(month FROM 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 => "EXTRACT(day FROM 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 => "EXTRACT(hour FROM 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 => "EXTRACT(minute FROM 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 => "EXTRACT(second FROM 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 => "EXTRACT(epoch FROM (me.created_on::timestamp with time zone - me.skip_inflation::timestamp with time zone))",
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 => "EXTRACT(DAY FROM (me.created_on::timestamp with time zone - me.skip_inflation::timestamp with time zone))",
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 => "TRUNC(MONTHS_BETWEEN(me.starts_at, me.created_on) / 12)",
605 where => "me.id = ?",
606 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
607 hri => [{ year => -1 }],
612 msg => '-dt_add (year) works',
613 search => { 'me.id' => 2 },
614 select => [ [ -dt_add => [year => 3, { -ident => 'me.created_on' } ] ] ],
617 select => "(datetime(me.created_on, ? || ' years'))",
618 where => "me.id = ?",
619 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
620 hri => [{ date => '2014-12-14 12:12:12' }],
623 select => "(DATEADD(year, CAST(? AS INTEGER), me.created_on))",
624 where => "me.id = ?",
625 bind => [[{sqlt_datatype => 'integer'}, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
626 hri => [{ date => '2014-12-14 12:12:12.000' }],
629 select => "DATE_ADD(me.created_on, INTERVAL ? YEAR)",
630 where => "me.id = ?",
631 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
632 hri => [{ date => '2014-12-14 12:12:12' }],
635 select => "(me.created_on + NUMTOYMINTERVAL(?, '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.000000000' }],
643 msg => '-dt_add (month) works',
644 search => { 'me.id' => 2 },
645 select => [ [ -dt_add => [month => 3, { -ident => 'me.created_on' } ] ] ],
648 select => "(datetime(me.created_on, ? || ' months'))",
649 where => "me.id = ?",
650 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
651 hri => [{ date => '2012-03-14 12:12:12' }],
654 select => "(DATEADD(month, CAST(? AS INTEGER), me.created_on))",
655 where => "me.id = ?",
656 bind => [[{sqlt_datatype => 'integer'}, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
657 hri => [{ date => '2012-03-14 12:12:12.000' }],
660 select => "DATE_ADD(me.created_on, INTERVAL ? MONTH)",
661 where => "me.id = ?",
662 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
663 hri => [{ date => '2012-03-14 12:12:12' }],
666 select => "(me.created_on + NUMTOYMINTERVAL(?, 'month'))",
667 where => "me.id = ?",
668 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
669 hri => [{ date => '2012-03-14 12:12:12.000000000' }],
674 msg => '-dt_add (day) works',
675 search => { 'me.id' => 2 },
676 select => [ [ -dt_add => [day => 3, { -ident => 'me.created_on' } ] ] ],
679 select => "(datetime(me.created_on, ? || ' days'))",
680 where => "me.id = ?",
681 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
682 hri => [{ date => '2011-12-17 12:12:12' }],
685 select => "(DATEADD(dayofyear, CAST(? AS INTEGER), me.created_on))",
686 where => "me.id = ?",
687 bind => [[{sqlt_datatype => 'integer'}, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
688 hri => [{ date => '2011-12-17 12:12:12.000' }],
691 select => "DATE_ADD(me.created_on, INTERVAL ? DAY)",
692 where => "me.id = ?",
693 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
694 hri => [{ date => '2011-12-17 12:12:12' }],
697 select => "(me.created_on + NUMTODSINTERVAL(?, 'day'))",
698 where => "me.id = ?",
699 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
700 hri => [{ date => '2011-12-17 12:12:12.000000000' }],
705 msg => '-dt_add (hour) works',
706 search => { 'me.id' => 2 },
707 select => [ [ -dt_add => [hour => 3, { -ident => 'me.created_on' } ] ] ],
710 select => "(datetime(me.created_on, ? || ' hours'))",
711 where => "me.id = ?",
712 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
713 hri => [{ date => '2011-12-14 15:12:12' }],
716 select => "(DATEADD(hour, CAST(? AS INTEGER), me.created_on))",
717 where => "me.id = ?",
718 bind => [[{sqlt_datatype => 'integer'}, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
719 hri => [{ date => '2011-12-14 15:12:12.000' }],
722 select => "DATE_ADD(me.created_on, INTERVAL ? HOUR)",
723 where => "me.id = ?",
724 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
725 hri => [{ date => '2011-12-14 15:12:12' }],
728 select => "(me.created_on + NUMTODSINTERVAL(?, 'hour'))",
729 where => "me.id = ?",
730 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
731 hri => [{ date => '2011-12-14 15:12:12.000000000' }],
736 msg => '-dt_add (minute) works',
737 search => { 'me.id' => 2 },
738 select => [ [ -dt_add => [minute => 3, { -ident => 'me.created_on' } ] ] ],
741 select => "(datetime(me.created_on, ? || ' minutes'))",
742 where => "me.id = ?",
743 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
744 hri => [{ date => '2011-12-14 12:15:12' }],
747 select => "(DATEADD(minute, CAST(? AS INTEGER), me.created_on))",
748 where => "me.id = ?",
749 bind => [[{sqlt_datatype => 'integer'}, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
750 hri => [{ date => '2011-12-14 12:15:12.000' }],
753 select => "DATE_ADD(me.created_on, INTERVAL ? MINUTE)",
754 where => "me.id = ?",
755 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
756 hri => [{ date => '2011-12-14 12:15:12' }],
759 select => "(me.created_on + NUMTODSINTERVAL(?, 'minute'))",
760 where => "me.id = ?",
761 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
762 hri => [{ date => '2011-12-14 12:15:12.000000000' }],
767 msg => '-dt_add (second) works',
768 search => { 'me.id' => 2 },
769 select => [ [ -dt_add => [second => 3, { -ident => 'me.created_on' } ] ] ],
772 select => "(datetime(me.created_on, ? || ' seconds'))",
773 where => "me.id = ?",
774 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
775 hri => [{ date => '2011-12-14 12:12:15' }],
778 select => "(DATEADD(second, CAST(? AS INTEGER), me.created_on))",
779 where => "me.id = ?",
780 bind => [[{sqlt_datatype => 'integer'}, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
781 hri => [{ date => '2011-12-14 12:12:15.000' }],
784 select => "DATE_ADD(me.created_on, INTERVAL ? SECOND)",
785 where => "me.id = ?",
786 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
787 hri => [{ date => '2011-12-14 12:12:15' }],
790 select => "(me.created_on + NUMTODSINTERVAL(?, 'second'))",
791 where => "me.id = ?",
792 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
793 hri => [{ date => '2011-12-14 12:12:15.000000000' }],
798 msg => 'nested -dt_add works',
799 search => { 'me.id' => 2 },
800 select => [ [ -dt_add => [second => 3, { -dt_add => [ day => 1, { -ident => 'me.created_on' } ] } ] ] ],
803 select => "(datetime((datetime(me.created_on, ? || ' days')), ? || ' seconds'))",
804 where => "me.id = ?",
805 bind => [[unknown_col, 1], [unknown_col, 3 ], [{dbic_colname => 'me.id', sqlt_datatype => 'integer' }, 2]],
806 hri => [{ date => '2011-12-15 12:12:15' }],
809 select => "(DATEADD(second, CAST(? AS INTEGER), (DATEADD(dayofyear, CAST(? AS INTEGER), me.created_on))))",
810 where => "me.id = ?",
811 bind => [[{sqlt_datatype => 'integer'}, 3 ], [{sqlt_datatype => 'integer'}, 1], [{dbic_colname => 'me.id', sqlt_datatype => 'integer' }, 2]],
812 hri => [{ date => '2011-12-15 12:12:15.000' }],
815 select => "DATE_ADD(DATE_ADD(me.created_on, INTERVAL ? DAY), INTERVAL ? SECOND)",
816 where => "me.id = ?",
817 bind => [[unknown_col, 1], [unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
818 hri => [{ date => '2011-12-15 12:12:15' }],
821 select => "((me.created_on + NUMTODSINTERVAL(?, 'day')) + NUMTODSINTERVAL(?, 'second'))",
822 where => "me.id = ?",
823 bind => [[unknown_col, 1], [unknown_col, 3 ], [{dbic_colname => 'me.id', sqlt_datatype => 'integer' }, 2]],
824 hri => [{ date => '2011-12-15 12:12:15.000000000' }],
832 for my $db (keys %rs) {
833 my $db_test = $t->{$db};
835 ok 0, "$t->{msg} ($db not tested!)";
843 ( exists $t->{select}
844 ? ( select => $t->{select}, as => $t->{as} )
845 : ( columns => [qw(starts_at created_on skip_inflation)] )
848 $my_rs = $rs{$db}->search($t->{search}, $stuff);
849 $r = $my_rs->as_query
852 if ($db_test->{exception_like}) {
855 $db_test->{exception_like},
856 "throws the expected exception ($db_test->{exception_like})",
859 if ($db_test->{warning_like}) {
862 $db_test->{warning_like},
863 "issues the expected warning ($db_test->{warning_like})"
871 "(SELECT $db_test->{select} FROM event me WHERE $db_test->{where})",
873 ($t->{msg} ? "$t->{msg} ($db)" : ())
877 if (my $hri = $db_test->{hri}) {
878 skip "Cannot test database we are not connected to ($db)", 1 unless $dbs_to_test{$db};
879 skip $db_test->{skip} . " ($db)", 1 if $db_test->{skip};
881 my $msg = ($t->{msg} ? "$t->{msg} ($db actually pulls expected data)" : '');
883 my $got = [ $my_rs->hri_dump->all ];
884 my $success = is_deeply $got, $hri, $msg;
886 warn "$db: $t->{msg} got: " . Dumper $got;
887 warn "$db: $t->{msg} expected: " . Dumper $hri;