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_add (year) works',
488 search => { 'me.id' => 2 },
489 select => [ [ -dt_add => [year => 3, { -ident => 'me.created_on' } ] ] ],
492 select => "(datetime(me.created_on, ? || ' years'))",
493 where => "me.id = ?",
494 bind => [['', 3], ['me.id' => 2 ]],
495 hri => [{ date => '2014-12-14 12:12:12' }],
498 select => "(DATEADD(year, ?, me.created_on))",
499 where => "me.id = ?",
500 bind => [['', 3], ['me.id' => 2 ]],
501 hri => [{ date => '2014-12-14 12:12:12.000' }],
502 skip => 'need working bindtypes',
505 select => "(me.created_on + NUMTOYMINTERVAL(?, 'year'))",
506 where => "me.id = ?",
507 bind => [['', 3], ['me.id' => 2 ]],
508 hri => [{ date => '2014-12-14 12:12:12.000000000' }],
513 msg => '-dt_add (month) works',
514 search => { 'me.id' => 2 },
515 select => [ [ -dt_add => [month => 3, { -ident => 'me.created_on' } ] ] ],
518 select => "(datetime(me.created_on, ? || ' months'))",
519 where => "me.id = ?",
520 bind => [['', 3], ['me.id' => 2 ]],
521 hri => [{ date => '2012-03-14 12:12:12' }],
524 select => "(DATEADD(month, ?, me.created_on))",
525 where => "me.id = ?",
526 bind => [['', 3], ['me.id' => 2 ]],
527 hri => [{ date => '2012-03-14 12:12:12.000' }],
528 skip => 'need working bindtypes',
531 select => "(me.created_on + NUMTOYMINTERVAL(?, 'month'))",
532 where => "me.id = ?",
533 bind => [['', 3], ['me.id' => 2 ]],
534 hri => [{ date => '2012-03-14 12:12:12.000000000' }],
539 msg => '-dt_add (day) works',
540 search => { 'me.id' => 2 },
541 select => [ [ -dt_add => [day => 3, { -ident => 'me.created_on' } ] ] ],
544 select => "(datetime(me.created_on, ? || ' days'))",
545 where => "me.id = ?",
546 bind => [['', 3], ['me.id' => 2 ]],
547 hri => [{ date => '2011-12-17 12:12:12' }],
550 select => "(DATEADD(dayofyear, ?, me.created_on))",
551 where => "me.id = ?",
552 bind => [['', 3], ['me.id' => 2 ]],
553 hri => [{ date => '2011-12-17 12:12:12.000' }],
554 skip => 'need working bindtypes',
557 select => "(me.created_on + NUMTODSINTERVAL(?, 'day'))",
558 where => "me.id = ?",
559 bind => [['', 3], ['me.id' => 2 ]],
560 hri => [{ date => '2011-12-17 12:12:12.000000000' }],
565 msg => '-dt_add (hour) works',
566 search => { 'me.id' => 2 },
567 select => [ [ -dt_add => [hour => 3, { -ident => 'me.created_on' } ] ] ],
570 select => "(datetime(me.created_on, ? || ' hours'))",
571 where => "me.id = ?",
572 bind => [['', 3], ['me.id' => 2 ]],
573 hri => [{ date => '2011-12-14 15:12:12' }],
576 select => "(DATEADD(hour, ?, me.created_on))",
577 where => "me.id = ?",
578 bind => [['', 3], ['me.id' => 2 ]],
579 hri => [{ date => '2011-12-14 15:12:12.000' }],
580 skip => 'need working bindtypes',
583 select => "(me.created_on + NUMTODSINTERVAL(?, 'hour'))",
584 where => "me.id = ?",
585 bind => [['', 3], ['me.id' => 2 ]],
586 hri => [{ date => '2011-12-14 15:12:12.000000000' }],
591 msg => '-dt_add (minute) works',
592 search => { 'me.id' => 2 },
593 select => [ [ -dt_add => [minute => 3, { -ident => 'me.created_on' } ] ] ],
596 select => "(datetime(me.created_on, ? || ' minutes'))",
597 where => "me.id = ?",
598 bind => [['', 3], ['me.id' => 2 ]],
599 hri => [{ date => '2011-12-14 12:15:12' }],
602 select => "(DATEADD(minute, ?, me.created_on))",
603 where => "me.id = ?",
604 bind => [['', 3], ['me.id' => 2 ]],
605 hri => [{ date => '2011-12-14 12:15:12.000' }],
606 skip => 'need working bindtypes',
609 select => "(me.created_on + NUMTODSINTERVAL(?, 'minute'))",
610 where => "me.id = ?",
611 bind => [['', 3], ['me.id' => 2 ]],
612 hri => [{ date => '2011-12-14 12:15:12.000000000' }],
617 msg => '-dt_add (second) works',
618 search => { 'me.id' => 2 },
619 select => [ [ -dt_add => [second => 3, { -ident => 'me.created_on' } ] ] ],
622 select => "(datetime(me.created_on, ? || ' seconds'))",
623 where => "me.id = ?",
624 bind => [['', 3], ['me.id' => 2 ]],
625 hri => [{ date => '2011-12-14 12:12:15' }],
628 select => "(DATEADD(second, ?, me.created_on))",
629 where => "me.id = ?",
630 bind => [['', 3], ['me.id' => 2 ]],
631 hri => [{ date => '2011-12-14 12:12:15.000' }],
632 skip => 'need working bindtypes',
635 select => "(me.created_on + NUMTODSINTERVAL(?, 'second'))",
636 where => "me.id = ?",
637 bind => [['', 3], ['me.id' => 2 ]],
638 hri => [{ date => '2011-12-14 12:12:15.000000000' }],
643 msg => 'nested -dt_add works',
644 search => { 'me.id' => 2 },
645 select => [ [ -dt_add => [second => 3, { -dt_add => [ day => 1, { -ident => 'me.created_on' } ] } ] ] ],
648 select => "(datetime((datetime(me.created_on, ? || ' days')), ? || ' seconds'))",
649 where => "me.id = ?",
650 bind => [['', 1], [ '', 3 ], ['me.id', 2]],
651 hri => [{ date => '2011-12-15 12:12:15' }],
654 select => "(DATEADD(second, ?, (DATEADD(dayofyear, ?, me.created_on))))",
655 where => "me.id = ?",
656 bind => [['', 3], [ '', 1 ], ['me.id', 2]],
657 hri => [{ date => '2011-12-15 12:12:15.000' }],
658 skip => 'need working bindtypes',
661 select => "((me.created_on + NUMTODSINTERVAL(?, 'day')) + NUMTODSINTERVAL(?, 'second'))",
662 where => "me.id = ?",
663 bind => [['', 1], [ '', 3 ], ['me.id', 2]],
664 hri => [{ date => '2011-12-15 12:12:15.000000000' }],
669 msg => '-dt_diff (year) works',
670 search => { 'me.id' => 2 },
671 select => [ [ -dt_diff => [year => \'me.starts_at', { -ident => 'me.created_on' } ] ] ],
674 exception_like => qr/date diff not supported for part "year" with database "SQLite"/,
677 select => "DATEDIFF(year, me.created_on, me.starts_at)",
678 where => "me.id = ?",
679 bind => [['me.id', 2]],
680 hri => [{ year => -1 }],
683 select => "TRUNC(MONTHS_BETWEEN(me.starts_at, me.created_on) / 12)",
684 where => "me.id = ?",
685 bind => [['me.id', 2]],
686 hri => [{ year => -1 }],
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;