8 use DBIC::SqlMakerTest;
10 use DBIx::Class::SQLMaker::MSSQL;
12 use Data::Dumper::Concise;
24 sqlite => DBICTest->init_schema( no_populate => 1 ),
26 my ($dsn, $user, $pass) = @ENV{map { "DBICTEST_MSSQL_ODBC_${_}" } qw/DSN USER PASS/};
28 my $s = DBICTest::Schema->connect($dsn, $user, $pass);
29 try { $s->storage->ensure_connected };
31 $s->storage->dbh_do (sub {
32 my ($storage, $dbh) = @_;
33 eval { $dbh->do("DROP 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,
47 $dbs_to_test{mssql} = 1;
51 DBICTest->init_schema( no_deploy=> 1, storage_type => '::DBI::MSSQL' )
54 ## copypasta'd for great justice
56 my ($dsn, $user, $pass) = @ENV{map { "DBICTEST_POSTGRES_${_}" } qw/DSN USER PASS/};
58 my $s = DBICTest::Schema->connect($dsn, $user, $pass);
59 try { $s->storage->ensure_connected };
61 $s->storage->dbh_do (sub {
62 my ($storage, $dbh) = @_;
63 eval { $dbh->do("DROP 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
75 $dbs_to_test{postgres} = 1;
79 DBICTest->init_schema( no_deploy=> 1, storage_type => '::DBI::Pg' )
83 my ($dsn, $user, $pass) = @ENV{map { "DBICTEST_ORA_${_}" } qw/DSN USER PASS/};
85 my $s = DBICTest::Schema->connect($dsn, $user, $pass, { on_connect_call => 'datetime_setup' });
86 try { $s->storage->ensure_connected };
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');
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)
107 CREATE TRIGGER trg_event_id
108 BEFORE INSERT ON event
110 new.id IS NULL OR new.id = 0
113 SELECT sq_event_id.nextval
118 $dbs_to_test{oracle} = 1;
122 DBICTest->init_schema( no_deploy=> 1, storage_type => '::DBI::Oracle', on_connect_call => 'datetime_setup' )
127 my %rs = map { $_ => $schema{$_}->resultset('Event') } keys %schema;
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'],
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;
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;
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;
153 my $date = DateTime->new(
166 skip_inflation => $_[2]
170 my $date2 = $date->clone->set_day(16);
173 ## search => { dbic_search_code/params }
174 ## rdbms_name => literal_sql
178 search => { 'me.created_on' => { -dt => $date } },
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')],
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')],
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')],
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')],
203 msg => '-dt_now works',
207 search => { 'me.id' => 1 },
208 select => [ [ -dt_year => { -ident => 'me.created_on' } ] ],
211 select => "DATEPART(year, me.created_on)",
212 where => "me.id = ?",
213 bind => [['me.id' => 1 ]],
214 hri => [{ year => 2010 }],
217 select => "STRFTIME('%Y', me.created_on)",
218 where => "me.id = ?",
219 bind => [['me.id' => 1 ]],
220 hri => [{ year => 2010 }],
223 select => "EXTRACT(year FROM me.created_on)",
224 where => "me.id = ?",
225 bind => [['me.id' => 1 ]],
226 hri => [{ year => 2010 }],
229 select => "EXTRACT(year FROM me.created_on)",
230 where => "me.id = ?",
231 bind => [['me.id' => 1 ]],
232 hri => [{ year => 2010 }],
234 msg => '-dt_year works',
236 ## -dt_get(year, month) tests
238 search => { 'me.id' => 1 },
239 select => [ [ -dt_get => [[qw(year month)], { -ident => 'me.created_on' }] ] ],
240 as => [ qw(year month) ],
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 }],
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 }],
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 }],
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 }],
265 msg => '-dt_get (year, month) works',
269 search => { 'me.id' => 1 },
270 select => [ [ -dt_month => { -ident => 'me.created_on' } ] ],
273 select => "STRFTIME('%m', me.created_on)",
274 where => "me.id = ?",
275 bind => [['me.id' => 1 ]],
276 hri => [{ month => 12 }],
279 select => "DATEPART(month, me.created_on)",
280 where => "me.id = ?",
281 bind => [['me.id' => 1 ]],
282 hri => [{ month => 12 }],
285 select => "EXTRACT(month FROM me.created_on)",
286 where => "me.id = ?",
287 bind => [['me.id' => 1 ]],
288 hri => [{ month => 12 }],
291 select => "EXTRACT(month FROM me.created_on)",
292 where => "me.id = ?",
293 bind => [['me.id' => 1 ]],
294 hri => [{ month => 12 }],
296 msg => '-dt_month works',
300 search => { 'me.id' => 1 },
301 select => [ [ -dt_day => { -ident => 'me.created_on' } ] ],
304 select => "STRFTIME('%d', me.created_on)",
305 where => "me.id = ?",
306 bind => [['me.id' => 1 ]],
307 hri => [{ day => 14 }],
310 select => "DATEPART(day, me.created_on)",
311 where => "me.id = ?",
312 bind => [['me.id' => 1 ]],
313 hri => [{ day => 14 }],
316 select => "EXTRACT(day FROM me.created_on)",
317 where => "me.id = ?",
318 bind => [['me.id' => 1 ]],
319 hri => [{ day => 14 }],
322 select => "EXTRACT(day FROM me.created_on)",
323 where => "me.id = ?",
324 bind => [['me.id' => 1 ]],
325 hri => [{ day => 14 }],
327 msg => '-dt_day works',
331 search => { 'me.id' => 1 },
332 select => [ [ -dt_hour => { -ident => 'me.created_on' } ] ],
335 select => "STRFTIME('%H', me.created_on)",
336 where => "me.id = ?",
337 bind => [['me.id' => 1 ]],
338 hri => [{ hour => 12 }],
341 select => "DATEPART(hour, me.created_on)",
342 where => "me.id = ?",
343 bind => [['me.id' => 1 ]],
344 hri => [{ hour => 12 }],
347 select => "EXTRACT(hour FROM me.created_on)",
348 where => "me.id = ?",
349 bind => [['me.id' => 1 ]],
350 hri => [{ hour => 12 }],
353 select => "EXTRACT(hour FROM me.created_on)",
354 where => "me.id = ?",
355 bind => [['me.id' => 1 ]],
356 hri => [{ hour => 12 }],
358 msg => '-dt_hour works',
362 search => { 'me.id' => 1 },
363 select => [ [ -dt_minute => { -ident => 'me.created_on' } ] ],
366 select => "STRFTIME('%M', me.created_on)",
367 where => "me.id = ?",
368 bind => [['me.id' => 1 ]],
369 hri => [{ minute => 12 }],
372 select => "DATEPART(minute, me.created_on)",
373 where => "me.id = ?",
374 bind => [['me.id' => 1 ]],
375 hri => [{ minute => 12 }],
378 select => "EXTRACT(minute FROM me.created_on)",
379 where => "me.id = ?",
380 bind => [['me.id' => 1 ]],
381 hri => [{ minute => 12 }],
384 select => "EXTRACT(minute FROM me.created_on)",
385 where => "me.id = ?",
386 bind => [['me.id' => 1 ]],
387 hri => [{ minute => 12 }],
389 msg => '-dt_minute works',
393 search => { 'me.id' => 1 },
394 select => [ [ -dt_second => { -ident => 'me.created_on' } ] ],
397 select => "STRFTIME('%S', me.created_on)",
398 where => "me.id = ?",
399 bind => [['me.id' => 1 ]],
400 hri => [{ second => 12 }],
403 select => "DATEPART(second, me.created_on)",
404 where => "me.id = ?",
405 bind => [['me.id' => 1 ]],
406 hri => [{ second => 12 }],
409 select => "EXTRACT(second FROM me.created_on)",
410 where => "me.id = ?",
411 bind => [['me.id' => 1 ]],
412 hri => [{ second => 12 }],
415 select => "EXTRACT(second FROM me.created_on)",
416 where => "me.id = ?",
417 bind => [['me.id' => 1 ]],
418 hri => [{ second => 12 }],
420 msg => '-dt_second works',
422 ## -dt_diff(second) tests
424 search => { 'me.id' => 2 },
425 select => [ [ -dt_diff => [second => { -ident => 'me.created_on' }, \'me.skip_inflation' ] ] ],
426 as => [ 'sec_diff' ],
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 }],
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 }],
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 }],
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 }],
451 msg => '-dt_diff (second) works',
454 # -dt_diff(day) tests
456 search => { 'me.id' => 2 },
457 select => [ [ -dt_diff => [day => { -ident => 'me.created_on' }, \'me.skip_inflation' ] ] ],
458 as => [ 'day_diff' ],
460 select => "(JULIANDAY(me.created_on) - JULIANDAY(me.skip_inflation))",
461 where => "me.id = ?",
462 bind => [['me.id' => 2 ]],
463 hri => [{ day_diff => 2 }],
466 select => "DATEDIFF(dayofyear, me.skip_inflation, me.created_on)",
467 where => "me.id = ?",
468 bind => [['me.id' => 2 ]],
469 hri => [{ day_diff => 2 }],
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 }],
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 }],
483 msg => '-dt_diff (day) works',
487 msg => '-dt_diff (year) works',
488 search => { 'me.id' => 2 },
489 select => [ [ -dt_diff => [year => \'me.starts_at', { -ident => 'me.created_on' } ] ] ],
492 exception_like => qr/date diff not supported for part "year" with database "SQLite"/,
495 select => "DATEDIFF(year, me.created_on, me.starts_at)",
496 where => "me.id = ?",
497 bind => [['me.id', 2]],
498 hri => [{ year => -1 }],
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 }],
509 msg => '-dt_add (year) works',
510 search => { 'me.id' => 2 },
511 select => [ [ -dt_add => [year => 3, { -ident => 'me.created_on' } ] ] ],
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' }],
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',
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' }],
535 msg => '-dt_add (month) works',
536 search => { 'me.id' => 2 },
537 select => [ [ -dt_add => [month => 3, { -ident => 'me.created_on' } ] ] ],
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' }],
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',
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' }],
561 msg => '-dt_add (day) works',
562 search => { 'me.id' => 2 },
563 select => [ [ -dt_add => [day => 3, { -ident => 'me.created_on' } ] ] ],
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' }],
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',
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' }],
587 msg => '-dt_add (hour) works',
588 search => { 'me.id' => 2 },
589 select => [ [ -dt_add => [hour => 3, { -ident => 'me.created_on' } ] ] ],
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' }],
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',
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' }],
613 msg => '-dt_add (minute) works',
614 search => { 'me.id' => 2 },
615 select => [ [ -dt_add => [minute => 3, { -ident => 'me.created_on' } ] ] ],
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' }],
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',
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' }],
639 msg => '-dt_add (second) works',
640 search => { 'me.id' => 2 },
641 select => [ [ -dt_add => [second => 3, { -ident => 'me.created_on' } ] ] ],
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' }],
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',
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' }],
665 msg => 'nested -dt_add works',
666 search => { 'me.id' => 2 },
667 select => [ [ -dt_add => [second => 3, { -dt_add => [ day => 1, { -ident => 'me.created_on' } ] } ] ] ],
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' }],
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',
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' }],
694 for my $db (keys %rs) {
695 my $db_test = $t->{$db};
696 next DB_TEST unless $db_test;
702 ( exists $t->{select}
703 ? ( select => $t->{select}, as => $t->{as} )
704 : ( columns => [qw(starts_at created_on skip_inflation)] )
707 $my_rs = $rs{$db}->search($t->{search}, $stuff);
708 $r = $my_rs->as_query
711 if ($db_test->{exception_like}) {
714 $db_test->{exception_like},
715 "throws the expected exception ($db_test->{exception_like})",
718 if ($db_test->{warning_like}) {
721 $db_test->{warning_like},
722 "issues the expected warning ($db_test->{warning_like})"
730 "(SELECT $db_test->{select} FROM event me WHERE $db_test->{where})",
732 ($t->{msg} ? "$t->{msg} ($db)" : ())
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};
740 my $msg = ($t->{msg} ? "$t->{msg} ($db actually pulls expected data)" : '');
742 my $got = [ $my_rs->hri_dump->all ];
743 my $success = is_deeply $got, $hri, $msg;
745 warn "$db: $t->{msg} got: " . Dumper $got;
746 warn "$db: $t->{msg} expected: " . Dumper $hri;