8 use DBIC::SqlMakerTest;
10 use DBIx::Class::SQLMaker::MSSQL;
12 use Data::Dumper::Concise;
16 sub unknown_col { +{ dbic_colname => '' } }
26 sqlite => DBICTest->init_schema( no_populate => 1 ),
28 my ($dsn, $user, $pass) = @ENV{map { "DBICTEST_MSSQL_ODBC_${_}" } qw/DSN USER PASS/};
30 my $s = DBICTest::Schema->connect($dsn, $user, $pass);
31 try { $s->storage->ensure_connected };
33 $s->storage->dbh_do (sub {
34 my ($storage, $dbh) = @_;
35 eval { $dbh->do("DROP TABLE event") };
38 id INT IDENTITY NOT NULL,
39 starts_at DATE NOT NULL,
40 created_on DATETIME NOT NULL,
41 varchar_date VARCHAR(20),
42 varchar_datetime VARCHAR(20),
43 skip_inflation DATETIME,
44 ts_without_tz DATETIME,
49 $dbs_to_test{mssql} = 1;
53 DBICTest->init_schema( no_deploy=> 1, storage_type => '::DBI::MSSQL' )
56 ## copypasta'd for great justice
58 my ($dsn, $user, $pass) = @ENV{map { "DBICTEST_POSTGRES_${_}" } 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 SERIAL NOT NULL PRIMARY KEY,
69 starts_at DATE NOT NULL,
70 created_on TIMESTAMP NOT NULL,
71 varchar_date VARCHAR(20),
72 varchar_datetime VARCHAR(20),
73 skip_inflation TIMESTAMP,
74 ts_without_tz TIMESTAMP WITHOUT TIME ZONE
77 $dbs_to_test{postgres} = 1;
81 DBICTest->init_schema( no_deploy=> 1, storage_type => '::DBI::Pg' )
85 my ($dsn, $user, $pass) = @ENV{map { "DBICTEST_ORA_${_}" } qw/DSN USER PASS/};
87 my $s = DBICTest::Schema->connect($dsn, $user, $pass, { on_connect_call => 'datetime_setup' });
88 try { $s->storage->ensure_connected };
90 $s->storage->dbh_do (sub {
91 my ($storage, $dbh) = @_;
92 eval { $dbh->do("DROP TRIGGER trq_event_id") };
93 eval { $dbh->do("DROP SEQUENCE sq_event_id") };
94 eval { $dbh->do("DROP TABLE event") };
95 $dbh->do('CREATE SEQUENCE sq_event_id');
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,
105 CONSTRAINT PK_EVENT PRIMARY KEY (id)
109 CREATE TRIGGER trg_event_id
110 BEFORE INSERT ON event
112 new.id IS NULL OR new.id = 0
115 SELECT sq_event_id.nextval
120 $dbs_to_test{oracle} = 1;
124 DBICTest->init_schema( no_deploy=> 1, storage_type => '::DBI::Oracle::Generic', on_connect_call => 'datetime_setup' )
129 my %rs = map { $_ => $schema{$_}->resultset('Event') } keys %schema;
131 $rs{sqlite}->populate([
132 [qw(starts_at created_on skip_inflation)],
133 ['2010-12-12', '2010-12-14 12:12:12', '2019-12-12 12:12:12'],
134 ['2010-12-12', '2011-12-14 12:12:12', '2011-12-12 12:12:12'],
137 $rs{mssql}->populate([
138 [qw(starts_at created_on skip_inflation)],
139 ['2010-12-12', '2010-12-14 12:12:12.000', '2019-12-12 12:12:12.000'],
140 ['2010-12-12', '2011-12-14 12:12:12.000', '2011-12-12 12:12:12.000'],
141 ]) if $schema{mssql}->storage->connected;
143 $rs{postgres}->populate([
144 [qw(starts_at created_on skip_inflation)],
145 ['2010-12-12', '2010-12-14 12:12:12', '2019-12-12 12:12:12'],
146 ['2010-12-12', '2011-12-14 12:12:12', '2011-12-12 12:12:12'],
147 ]) if $schema{postgres}->storage->connected;
149 $rs{oracle}->populate([
150 [qw(starts_at created_on skip_inflation)],
151 ['2010-12-12', '2010-12-14 12:12:12', '2019-12-12 12:12:12'],
152 ['2010-12-12', '2011-12-14 12:12:12', '2011-12-12 12:12:12'],
153 ]) if $schema{oracle}->storage->connected;
155 my $date = DateTime->new(
168 skip_inflation => $_[2]
172 my $date2 = $date->clone->set_day(16);
175 ## search => { dbic_search_code/params }
176 ## rdbms_name => literal_sql
180 search => { 'me.created_on' => { -dt => $date } },
182 select => 'me.starts_at, me.created_on, me.skip_inflation',
183 where => 'me.created_on = ?',
184 bind => [[{ dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12' ]],
185 hri => [hri_thing('2010-12-12', '2010-12-14 12:12:12', '2019-12-12 12:12:12')],
188 select => 'me.starts_at, me.created_on, me.skip_inflation',
189 where => 'me.created_on = ?',
190 bind => [[{ dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12.000' ]],
191 hri => [hri_thing('2010-12-12', '2010-12-14 12:12:12.000', '2019-12-12 12:12:12.000')],
194 select => 'me.starts_at, me.created_on, me.skip_inflation',
195 where => 'me.created_on = ?',
196 bind => [[{ dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12' ]],
197 hri => [hri_thing('2010-12-12', '2010-12-14 12:12:12', '2019-12-12 12:12:12')],
200 select => 'me.starts_at, me.created_on, me.skip_inflation',
201 where => 'me.created_on = ?',
202 bind => [[{ dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12' ]],
203 hri => [hri_thing('2010-12-12 00:00:00', '2010-12-14 12:12:12.000000', '2019-12-12 12:12:12.000000')],
205 msg => '-dt_now works',
209 search => { 'me.id' => 1 },
210 select => [ [ -dt_year => { -ident => 'me.created_on' } ] ],
213 select => "DATEPART(year, me.created_on)",
214 where => "me.id = ?",
215 bind => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
216 hri => [{ year => 2010 }],
219 select => "STRFTIME('%Y', me.created_on)",
220 where => "me.id = ?",
221 bind => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
222 hri => [{ year => 2010 }],
225 select => "EXTRACT(year FROM me.created_on)",
226 where => "me.id = ?",
227 bind => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
228 hri => [{ year => 2010 }],
231 select => "EXTRACT(year FROM me.created_on)",
232 where => "me.id = ?",
233 bind => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
234 hri => [{ year => 2010 }],
236 msg => '-dt_year works',
240 msg => '-dt_get (year, month) works',
241 search => { 'me.id' => 1 },
242 select => [ [ -dt_get => [[qw(year month)], { -ident => 'me.created_on' }] ] ],
243 as => [ qw(year month) ],
245 select => "DATEPART(year, me.created_on), DATEPART(month, me.created_on)",
246 where => "me.id = ?",
247 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
248 hri => [{ year => 2010, month => 12 }],
251 select => "STRFTIME('%Y', me.created_on), STRFTIME('%m', me.created_on)",
252 where => "me.id = ?",
253 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
254 hri => [{ year => 2010, month => 12 }],
257 select => "EXTRACT(year FROM me.created_on), EXTRACT(month FROM me.created_on)",
258 where => "me.id = ?",
259 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
260 hri => [{ year => 2010, month => 12 }],
263 select => "EXTRACT(year FROM me.created_on), EXTRACT(month FROM me.created_on)",
264 where => "me.id = ?",
265 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
266 hri => [{ year => 2010, month => 12 }],
271 msg => '-dt_month works',
272 search => { 'me.id' => 1 },
273 select => [ [ -dt_month => { -ident => 'me.created_on' } ] ],
276 select => "STRFTIME('%m', me.created_on)",
277 where => "me.id = ?",
278 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
279 hri => [{ month => 12 }],
282 select => "DATEPART(month, me.created_on)",
283 where => "me.id = ?",
284 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
285 hri => [{ month => 12 }],
288 select => "EXTRACT(month FROM me.created_on)",
289 where => "me.id = ?",
290 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
291 hri => [{ month => 12 }],
294 select => "EXTRACT(month FROM me.created_on)",
295 where => "me.id = ?",
296 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
297 hri => [{ month => 12 }],
302 msg => '-dt_day works',
303 search => { 'me.id' => 1 },
304 select => [ [ -dt_day => { -ident => 'me.created_on' } ] ],
307 select => "STRFTIME('%d', me.created_on)",
308 where => "me.id = ?",
309 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
310 hri => [{ day => 14 }],
313 select => "DATEPART(day, me.created_on)",
314 where => "me.id = ?",
315 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
316 hri => [{ day => 14 }],
319 select => "EXTRACT(day FROM me.created_on)",
320 where => "me.id = ?",
321 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
322 hri => [{ day => 14 }],
325 select => "EXTRACT(day FROM me.created_on)",
326 where => "me.id = ?",
327 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
328 hri => [{ day => 14 }],
333 msg => '-dt_hour works',
334 search => { 'me.id' => 1 },
335 select => [ [ -dt_hour => { -ident => 'me.created_on' } ] ],
338 select => "STRFTIME('%H', me.created_on)",
339 where => "me.id = ?",
340 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
341 hri => [{ hour => 12 }],
344 select => "DATEPART(hour, me.created_on)",
345 where => "me.id = ?",
346 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
347 hri => [{ hour => 12 }],
350 select => "EXTRACT(hour FROM me.created_on)",
351 where => "me.id = ?",
352 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
353 hri => [{ hour => 12 }],
356 select => "EXTRACT(hour FROM me.created_on)",
357 where => "me.id = ?",
358 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
359 hri => [{ hour => 12 }],
364 msg => '-dt_minute works',
365 search => { 'me.id' => 1 },
366 select => [ [ -dt_minute => { -ident => 'me.created_on' } ] ],
369 select => "STRFTIME('%M', me.created_on)",
370 where => "me.id = ?",
371 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
372 hri => [{ minute => 12 }],
375 select => "DATEPART(minute, me.created_on)",
376 where => "me.id = ?",
377 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
378 hri => [{ minute => 12 }],
381 select => "EXTRACT(minute FROM me.created_on)",
382 where => "me.id = ?",
383 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
384 hri => [{ minute => 12 }],
387 select => "EXTRACT(minute FROM me.created_on)",
388 where => "me.id = ?",
389 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
390 hri => [{ minute => 12 }],
395 msg => '-dt_second works',
396 search => { 'me.id' => 1 },
397 select => [ [ -dt_second => { -ident => 'me.created_on' } ] ],
400 select => "STRFTIME('%S', me.created_on)",
401 where => "me.id = ?",
402 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
403 hri => [{ second => 12 }],
406 select => "DATEPART(second, me.created_on)",
407 where => "me.id = ?",
408 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
409 hri => [{ second => 12 }],
412 select => "EXTRACT(second FROM me.created_on)",
413 where => "me.id = ?",
414 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
415 hri => [{ second => 12 }],
418 select => "EXTRACT(second FROM me.created_on)",
419 where => "me.id = ?",
420 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
421 hri => [{ second => 12 }],
426 msg => '-dt_diff (second) works',
427 search => { 'me.id' => 2 },
428 select => [ [ -dt_diff => [second => { -ident => 'me.created_on' }, \'me.skip_inflation' ] ] ],
429 as => [ 'sec_diff' ],
431 select => "(STRFTIME('%s', me.created_on) - STRFTIME('%s', me.skip_inflation))",
432 where => "me.id = ?",
433 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
434 hri => [{ sec_diff => 2*24*60*60 }],
437 select => "DATEDIFF(second, me.skip_inflation, me.created_on)",
438 where => "me.id = ?",
439 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
440 hri => [{ sec_diff => 2*24*60*60 }],
443 select => "EXTRACT(epoch FROM (me.created_on::timestamp with time zone - me.skip_inflation::timestamp with time zone))",
444 where => "me.id = ?",
445 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
446 hri => [{ sec_diff => 2*24*60*60 }],
449 select => "TRUNC(MONTHS_BETWEEN(me.created_on, me.skip_inflation) * 31 * 24 * 60 * 60)",
450 where => "me.id = ?",
451 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
452 hri => [{ sec_diff => 2*24*60*60 }],
457 msg => '-dt_diff (day) works',
458 search => { 'me.id' => 2 },
459 select => [ [ -dt_diff => [day => { -ident => 'me.created_on' }, \'me.skip_inflation' ] ] ],
460 as => [ 'day_diff' ],
462 select => "(JULIANDAY(me.created_on) - JULIANDAY(me.skip_inflation))",
463 where => "me.id = ?",
464 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
465 hri => [{ day_diff => 2 }],
468 select => "DATEDIFF(dayofyear, me.skip_inflation, me.created_on)",
469 where => "me.id = ?",
470 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
471 hri => [{ day_diff => 2 }],
474 select => "EXTRACT(DAY FROM (me.created_on::timestamp with time zone - me.skip_inflation::timestamp with time zone))",
475 where => "me.id = ?",
476 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
477 hri => [{ day_diff => 2 }],
480 select => "TRUNC(MONTHS_BETWEEN(me.created_on, me.skip_inflation) * 31)",
481 where => "me.id = ?",
482 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
483 hri => [{ day_diff => 2 }],
488 msg => '-dt_diff (year) works',
489 search => { 'me.id' => 2 },
490 select => [ [ -dt_diff => [year => \'me.starts_at', { -ident => 'me.created_on' } ] ] ],
493 exception_like => qr/date diff not supported for part "year" with database "SQLite"/,
496 select => "DATEDIFF(year, me.created_on, me.starts_at)",
497 where => "me.id = ?",
498 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
499 hri => [{ year => -1 }],
502 select => "TRUNC(MONTHS_BETWEEN(me.starts_at, me.created_on) / 12)",
503 where => "me.id = ?",
504 bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
505 hri => [{ year => -1 }],
510 msg => '-dt_add (year) works',
511 search => { 'me.id' => 2 },
512 select => [ [ -dt_add => [year => 3, { -ident => 'me.created_on' } ] ] ],
515 select => "(datetime(me.created_on, ? || ' years'))",
516 where => "me.id = ?",
517 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
518 hri => [{ date => '2014-12-14 12:12:12' }],
521 select => "(DATEADD(year, ?, me.created_on))",
522 where => "me.id = ?",
523 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
524 hri => [{ date => '2014-12-14 12:12:12.000' }],
525 skip => 'need working bindtypes',
528 select => "(me.created_on + NUMTOYMINTERVAL(?, 'year'))",
529 where => "me.id = ?",
530 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
531 hri => [{ date => '2014-12-14 12:12:12.000000000' }],
536 msg => '-dt_add (month) works',
537 search => { 'me.id' => 2 },
538 select => [ [ -dt_add => [month => 3, { -ident => 'me.created_on' } ] ] ],
541 select => "(datetime(me.created_on, ? || ' months'))",
542 where => "me.id = ?",
543 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
544 hri => [{ date => '2012-03-14 12:12:12' }],
547 select => "(DATEADD(month, ?, me.created_on))",
548 where => "me.id = ?",
549 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
550 hri => [{ date => '2012-03-14 12:12:12.000' }],
551 skip => 'need working bindtypes',
554 select => "(me.created_on + NUMTOYMINTERVAL(?, 'month'))",
555 where => "me.id = ?",
556 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
557 hri => [{ date => '2012-03-14 12:12:12.000000000' }],
562 msg => '-dt_add (day) works',
563 search => { 'me.id' => 2 },
564 select => [ [ -dt_add => [day => 3, { -ident => 'me.created_on' } ] ] ],
567 select => "(datetime(me.created_on, ? || ' days'))",
568 where => "me.id = ?",
569 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
570 hri => [{ date => '2011-12-17 12:12:12' }],
573 select => "(DATEADD(dayofyear, ?, me.created_on))",
574 where => "me.id = ?",
575 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
576 hri => [{ date => '2011-12-17 12:12:12.000' }],
577 skip => 'need working bindtypes',
580 select => "(me.created_on + NUMTODSINTERVAL(?, 'day'))",
581 where => "me.id = ?",
582 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
583 hri => [{ date => '2011-12-17 12:12:12.000000000' }],
588 msg => '-dt_add (hour) works',
589 search => { 'me.id' => 2 },
590 select => [ [ -dt_add => [hour => 3, { -ident => 'me.created_on' } ] ] ],
593 select => "(datetime(me.created_on, ? || ' hours'))",
594 where => "me.id = ?",
595 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
596 hri => [{ date => '2011-12-14 15:12:12' }],
599 select => "(DATEADD(hour, ?, me.created_on))",
600 where => "me.id = ?",
601 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
602 hri => [{ date => '2011-12-14 15:12:12.000' }],
603 skip => 'need working bindtypes',
606 select => "(me.created_on + NUMTODSINTERVAL(?, 'hour'))",
607 where => "me.id = ?",
608 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
609 hri => [{ date => '2011-12-14 15:12:12.000000000' }],
614 msg => '-dt_add (minute) works',
615 search => { 'me.id' => 2 },
616 select => [ [ -dt_add => [minute => 3, { -ident => 'me.created_on' } ] ] ],
619 select => "(datetime(me.created_on, ? || ' minutes'))",
620 where => "me.id = ?",
621 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
622 hri => [{ date => '2011-12-14 12:15:12' }],
625 select => "(DATEADD(minute, ?, me.created_on))",
626 where => "me.id = ?",
627 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
628 hri => [{ date => '2011-12-14 12:15:12.000' }],
629 skip => 'need working bindtypes',
632 select => "(me.created_on + NUMTODSINTERVAL(?, 'minute'))",
633 where => "me.id = ?",
634 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
635 hri => [{ date => '2011-12-14 12:15:12.000000000' }],
640 msg => '-dt_add (second) works',
641 search => { 'me.id' => 2 },
642 select => [ [ -dt_add => [second => 3, { -ident => 'me.created_on' } ] ] ],
645 select => "(datetime(me.created_on, ? || ' seconds'))",
646 where => "me.id = ?",
647 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
648 hri => [{ date => '2011-12-14 12:12:15' }],
651 select => "(DATEADD(second, ?, me.created_on))",
652 where => "me.id = ?",
653 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
654 hri => [{ date => '2011-12-14 12:12:15.000' }],
655 skip => 'need working bindtypes',
658 select => "(me.created_on + NUMTODSINTERVAL(?, 'second'))",
659 where => "me.id = ?",
660 bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
661 hri => [{ date => '2011-12-14 12:12:15.000000000' }],
666 msg => 'nested -dt_add works',
667 search => { 'me.id' => 2 },
668 select => [ [ -dt_add => [second => 3, { -dt_add => [ day => 1, { -ident => 'me.created_on' } ] } ] ] ],
671 select => "(datetime((datetime(me.created_on, ? || ' days')), ? || ' seconds'))",
672 where => "me.id = ?",
673 bind => [[unknown_col, 1], [unknown_col, 3 ], [{dbic_colname => 'me.id', sqlt_datatype => 'integer' }, 2]],
674 hri => [{ date => '2011-12-15 12:12:15' }],
677 select => "(DATEADD(second, ?, (DATEADD(dayofyear, ?, me.created_on))))",
678 where => "me.id = ?",
679 bind => [[unknown_col, 3 ], [unknown_col, 1], [{dbic_colname => 'me.id', sqlt_datatype => 'integer' }, 2]],
680 hri => [{ date => '2011-12-15 12:12:15.000' }],
681 skip => 'need working bindtypes',
684 select => "((me.created_on + NUMTODSINTERVAL(?, 'day')) + NUMTODSINTERVAL(?, 'second'))",
685 where => "me.id = ?",
686 bind => [[unknown_col, 1], [unknown_col, 3 ], [{dbic_colname => 'me.id', sqlt_datatype => 'integer' }, 2]],
687 hri => [{ date => '2011-12-15 12:12:15.000000000' }],
695 for my $db (keys %rs) {
696 my $db_test = $t->{$db};
697 next DB_TEST unless $db_test;
703 ( exists $t->{select}
704 ? ( select => $t->{select}, as => $t->{as} )
705 : ( columns => [qw(starts_at created_on skip_inflation)] )
708 $my_rs = $rs{$db}->search($t->{search}, $stuff);
709 $r = $my_rs->as_query
712 if ($db_test->{exception_like}) {
715 $db_test->{exception_like},
716 "throws the expected exception ($db_test->{exception_like})",
719 if ($db_test->{warning_like}) {
722 $db_test->{warning_like},
723 "issues the expected warning ($db_test->{warning_like})"
731 "(SELECT $db_test->{select} FROM event me WHERE $db_test->{where})",
733 ($t->{msg} ? "$t->{msg} ($db)" : ())
737 if (my $hri = $db_test->{hri}) {
738 skip "Cannot test database we are not connected to ($db)", 1 unless $dbs_to_test{$db};
739 skip $db_test->{skip} . " ($db)", 1 if $db_test->{skip};
741 my $msg = ($t->{msg} ? "$t->{msg} ($db actually pulls expected data)" : '');
743 my $got = [ $my_rs->hri_dump->all ];
744 my $success = is_deeply $got, $hri, $msg;
746 warn "$db: $t->{msg} got: " . Dumper $got;
747 warn "$db: $t->{msg} expected: " . Dumper $hri;