8 use DBIC::SqlMakerTest;
10 use DBIx::Class::SQLMaker::MSSQL;
22 sqlite => DBICTest->init_schema( no_populate => 1 ),
24 my ($dsn, $user, $pass) = @ENV{map { "DBICTEST_MSSQL_ODBC_${_}" } qw/DSN USER PASS/};
26 my $s = DBICTest::Schema->connect($dsn, $user, $pass);
27 try { $s->storage->ensure_connected };
29 $s->storage->dbh_do (sub {
30 my ($storage, $dbh) = @_;
31 eval { $dbh->do("DROP TABLE event") };
34 id INT IDENTITY NOT NULL,
35 starts_at DATE NOT NULL,
36 created_on DATETIME NOT NULL,
37 varchar_date VARCHAR(20),
38 varchar_datetime VARCHAR(20),
39 skip_inflation DATETIME,
40 ts_without_tz DATETIME,
45 $dbs_to_test{mssql} = 1;
49 DBICTest->init_schema( no_deploy=> 1, storage_type => '::DBI::MSSQL' )
52 ## copypasta'd for great justice
54 my ($dsn, $user, $pass) = @ENV{map { "DBICTEST_POSTGRES_${_}" } qw/DSN USER PASS/};
56 my $s = DBICTest::Schema->connect($dsn, $user, $pass);
57 try { $s->storage->ensure_connected };
59 $s->storage->dbh_do (sub {
60 my ($storage, $dbh) = @_;
61 eval { $dbh->do("DROP TABLE event") };
64 id SERIAL NOT NULL PRIMARY KEY,
65 starts_at DATE NOT NULL,
66 created_on TIMESTAMP NOT NULL,
67 varchar_date VARCHAR(20),
68 varchar_datetime VARCHAR(20),
69 skip_inflation TIMESTAMP,
70 ts_without_tz TIMESTAMP WITHOUT TIME ZONE
73 $dbs_to_test{postgres} = 1;
77 DBICTest->init_schema( no_deploy=> 1, storage_type => '::DBI::Pg' )
82 my %rs = map { $_ => $schema{$_}->resultset('Event') } keys %schema;
84 $rs{sqlite}->populate([
85 [qw(starts_at created_on skip_inflation)],
86 ['2010-12-12', '2010-12-14 12:12:12', '2019-12-12 12:12:12'],
87 ['2010-12-12', '2011-12-14 12:12:12', '2011-12-12 12:12:12'],
90 $rs{mssql}->populate([
91 [qw(starts_at created_on skip_inflation)],
92 ['2010-12-12', '2010-12-14 12:12:12.000', '2019-12-12 12:12:12.000'],
93 ['2010-12-12', '2011-12-14 12:12:12.000', '2011-12-12 12:12:12.000'],
94 ]) if $schema{mssql}->storage->connected;
96 $rs{postgres}->populate([
97 [qw(starts_at created_on skip_inflation)],
98 ['2010-12-12', '2010-12-14 12:12:12', '2019-12-12 12:12:12'],
99 ['2010-12-12', '2011-12-14 12:12:12', '2011-12-12 12:12:12'],
100 ]) if $schema{postgres}->storage->connected;
103 my $date = DateTime->new(
116 skip_inflation => $_[2]
120 my $date2 = $date->clone->set_day(16);
123 ## search => { dbic_search_code/params }
124 ## rdbms_name => literal_sql
128 search => { 'me.created_on' => { -dt => $date } },
130 select => 'me.starts_at, me.created_on, me.skip_inflation',
131 where => 'me.created_on = ?',
132 bind => [[ 'me.created_on', '2010-12-14 12:12:12' ]],
133 hri => [hri_thing('2010-12-12', '2010-12-14 12:12:12', '2019-12-12 12:12:12')],
136 select => 'me.starts_at, me.created_on, me.skip_inflation',
137 where => 'me.created_on = ?',
138 bind => [[ 'me.created_on', '2010-12-14 12:12:12.000' ]],
139 hri => [hri_thing('2010-12-12', '2010-12-14 12:12:12.000', '2019-12-12 12:12:12.000')],
142 select => 'me.starts_at, me.created_on, me.skip_inflation',
143 where => 'me.created_on = ?',
144 bind => [[ 'me.created_on', '2010-12-14 12:12:12' ]],
145 hri => [hri_thing('2010-12-12', '2010-12-14 12:12:12', '2019-12-12 12:12:12')],
147 msg => '-dt_now works',
151 search => { 'me.id' => 1 },
152 select => [ [ -dt_year => { -ident => 'me.created_on' } ] ],
155 select => "DATEPART(year, me.created_on)",
156 where => "me.id = ?",
157 bind => [['me.id' => 1 ]],
158 hri => [{ year => 2010 }],
161 select => "STRFTIME('%Y', me.created_on)",
162 where => "me.id = ?",
163 bind => [['me.id' => 1 ]],
164 hri => [{ year => 2010 }],
167 select => "EXTRACT(year FROM me.created_on)",
168 where => "me.id = ?",
169 bind => [['me.id' => 1 ]],
170 hri => [{ year => 2010 }],
172 msg => '-dt_year works',
174 ## -dt_get(year, month) tests
176 search => { 'me.id' => 1 },
177 select => [ [ -dt_get => [[qw(year month)], { -ident => 'me.created_on' }] ] ],
178 as => [ qw(year month) ],
180 select => "DATEPART(year, me.created_on), DATEPART(month, me.created_on)",
181 where => "me.id = ?",
182 bind => [['me.id' => 1 ]],
183 hri => [{ year => 2010, month => 12 }],
186 select => "STRFTIME('%Y', me.created_on), STRFTIME('%m', me.created_on)",
187 where => "me.id = ?",
188 bind => [['me.id' => 1 ]],
189 hri => [{ year => 2010, month => 12 }],
192 select => "EXTRACT(year FROM me.created_on), EXTRACT(month FROM me.created_on)",
193 where => "me.id = ?",
194 bind => [['me.id' => 1 ]],
195 hri => [{ year => 2010, month => 12 }],
197 msg => '-dt_get (year, month) works',
201 search => { 'me.id' => 1 },
202 select => [ [ -dt_month => { -ident => 'me.created_on' } ] ],
205 select => "STRFTIME('%m', me.created_on)",
206 where => "me.id = ?",
207 bind => [['me.id' => 1 ]],
208 hri => [{ month => 12 }],
211 select => "DATEPART(month, me.created_on)",
212 where => "me.id = ?",
213 bind => [['me.id' => 1 ]],
214 hri => [{ month => 12 }],
217 select => "EXTRACT(month FROM me.created_on)",
218 where => "me.id = ?",
219 bind => [['me.id' => 1 ]],
220 hri => [{ month => 12 }],
223 msg => '-dt_month works',
227 search => { 'me.id' => 1 },
228 select => [ [ -dt_day => { -ident => 'me.created_on' } ] ],
231 select => "STRFTIME('%d', me.created_on)",
232 where => "me.id = ?",
233 bind => [['me.id' => 1 ]],
234 hri => [{ day => 14 }],
237 select => "DATEPART(day, me.created_on)",
238 where => "me.id = ?",
239 bind => [['me.id' => 1 ]],
240 hri => [{ day => 14 }],
243 select => "EXTRACT(day FROM me.created_on)",
244 where => "me.id = ?",
245 bind => [['me.id' => 1 ]],
246 hri => [{ day => 14 }],
249 msg => '-dt_day works',
253 search => { 'me.id' => 1 },
254 select => [ [ -dt_hour => { -ident => 'me.created_on' } ] ],
257 select => "STRFTIME('%H', me.created_on)",
258 where => "me.id = ?",
259 bind => [['me.id' => 1 ]],
260 hri => [{ hour => 12 }],
263 select => "DATEPART(hour, me.created_on)",
264 where => "me.id = ?",
265 bind => [['me.id' => 1 ]],
266 hri => [{ hour => 12 }],
269 select => "EXTRACT(hour FROM me.created_on)",
270 where => "me.id = ?",
271 bind => [['me.id' => 1 ]],
272 hri => [{ hour => 12 }],
274 msg => '-dt_hour works',
278 search => { 'me.id' => 1 },
279 select => [ [ -dt_minute => { -ident => 'me.created_on' } ] ],
282 select => "STRFTIME('%M', me.created_on)",
283 where => "me.id = ?",
284 bind => [['me.id' => 1 ]],
285 hri => [{ minute => 12 }],
288 select => "DATEPART(minute, me.created_on)",
289 where => "me.id = ?",
290 bind => [['me.id' => 1 ]],
291 hri => [{ minute => 12 }],
294 select => "EXTRACT(minute FROM me.created_on)",
295 where => "me.id = ?",
296 bind => [['me.id' => 1 ]],
297 hri => [{ minute => 12 }],
299 msg => '-dt_minute works',
303 search => { 'me.id' => 1 },
304 select => [ [ -dt_second => { -ident => 'me.created_on' } ] ],
307 select => "STRFTIME('%S', me.created_on)",
308 where => "me.id = ?",
309 bind => [['me.id' => 1 ]],
310 hri => [{ second => 12 }],
313 select => "DATEPART(second, me.created_on)",
314 where => "me.id = ?",
315 bind => [['me.id' => 1 ]],
316 hri => [{ second => 12 }],
319 select => "EXTRACT(second FROM me.created_on)",
320 where => "me.id = ?",
321 bind => [['me.id' => 1 ]],
322 hri => [{ second => 12 }],
325 msg => '-dt_second works',
327 ## -dt_diff(second) tests
329 search => { 'me.id' => 2 },
330 select => [ [ -dt_diff => [second => { -ident => 'me.created_on' }, \'me.skip_inflation' ] ] ],
331 as => [ 'sec_diff' ],
333 select => "(STRFTIME('%s', me.created_on) - STRFTIME('%s', me.skip_inflation))",
334 where => "me.id = ?",
335 bind => [['me.id' => 2 ]],
336 hri => [{ sec_diff => 2*24*60*60 }],
339 select => "DATEDIFF(second, me.skip_inflation, me.created_on)",
340 where => "me.id = ?",
341 bind => [['me.id' => 2 ]],
342 hri => [{ sec_diff => 2*24*60*60 }],
345 select => "EXTRACT(epoch FROM (me.created_on::timestamp with time zone - me.skip_inflation::timestamp with time zone))",
346 where => "me.id = ?",
347 bind => [['me.id' => 2 ]],
348 hri => [{ sec_diff => 2*24*60*60 }],
350 msg => '-dt_diff (second) works',
353 # -dt_diff(day) tests
355 search => { 'me.id' => 2 },
356 select => [ [ -dt_diff => [day => { -ident => 'me.created_on' }, \'me.skip_inflation' ] ] ],
357 as => [ 'day_diff' ],
359 select => "(JULIANDAY(me.created_on) - JULIANDAY(me.skip_inflation))",
360 where => "me.id = ?",
361 bind => [['me.id' => 2 ]],
362 hri => [{ day_diff => 2 }],
365 select => "DATEDIFF(dayofyear, me.skip_inflation, me.created_on)",
366 where => "me.id = ?",
367 bind => [['me.id' => 2 ]],
368 hri => [{ day_diff => 2 }],
371 select => "EXTRACT(DAY FROM (me.created_on::timestamp with time zone - me.skip_inflation::timestamp with time zone))",
372 where => "me.id = ?",
373 bind => [['me.id' => 2 ]],
374 hri => [{ day_diff => 2 }],
377 msg => '-dt_diff (day) works',
381 search => { 'me.id' => 2 },
382 select => [ [ -dt_add => [year => 3, { -ident => 'me.created_on' } ] ] ],
385 select => "(datetime(me.created_on, ? || ' years'))",
386 where => "me.id = ?",
387 bind => [['', 3], ['me.id' => 2 ]],
388 hri => [{ date => '2014-12-14 12:12:12' }],
391 select => "(DATEADD(year, ?, me.created_on))",
392 where => "me.id = ?",
393 bind => [['', 3], ['me.id' => 2 ]],
394 hri => [{ date => '2014-12-14 12:12:12.000' }],
395 skip => 'need working bindtypes',
397 msg => '-dt_add (year) works',
401 search => { 'me.id' => 2 },
402 select => [ [ -dt_add => [month => 3, { -ident => 'me.created_on' } ] ] ],
405 select => "(datetime(me.created_on, ? || ' months'))",
406 where => "me.id = ?",
407 bind => [['', 3], ['me.id' => 2 ]],
408 hri => [{ date => '2012-03-14 12:12:12' }],
411 select => "(DATEADD(month, ?, me.created_on))",
412 where => "me.id = ?",
413 bind => [['', 3], ['me.id' => 2 ]],
414 hri => [{ date => '2012-03-14 12:12:12.000' }],
415 skip => 'need working bindtypes',
417 msg => '-dt_add (month) works',
421 search => { 'me.id' => 2 },
422 select => [ [ -dt_add => [day => 3, { -ident => 'me.created_on' } ] ] ],
425 select => "(datetime(me.created_on, ? || ' days'))",
426 where => "me.id = ?",
427 bind => [['', 3], ['me.id' => 2 ]],
428 hri => [{ date => '2011-12-17 12:12:12' }],
431 select => "(DATEADD(dayofyear, ?, me.created_on))",
432 where => "me.id = ?",
433 bind => [['', 3], ['me.id' => 2 ]],
434 hri => [{ date => '2011-12-17 12:12:12.000' }],
435 skip => 'need working bindtypes',
437 msg => '-dt_add (day) works',
441 search => { 'me.id' => 2 },
442 select => [ [ -dt_add => [hour => 3, { -ident => 'me.created_on' } ] ] ],
445 select => "(datetime(me.created_on, ? || ' hours'))",
446 where => "me.id = ?",
447 bind => [['', 3], ['me.id' => 2 ]],
448 hri => [{ date => '2011-12-14 15:12:12' }],
451 select => "(DATEADD(hour, ?, me.created_on))",
452 where => "me.id = ?",
453 bind => [['', 3], ['me.id' => 2 ]],
454 hri => [{ date => '2011-12-14 15:12:12.000' }],
455 skip => 'need working bindtypes',
457 msg => '-dt_add (hour) works',
461 search => { 'me.id' => 2 },
462 select => [ [ -dt_add => [minute => 3, { -ident => 'me.created_on' } ] ] ],
465 select => "(datetime(me.created_on, ? || ' minutes'))",
466 where => "me.id = ?",
467 bind => [['', 3], ['me.id' => 2 ]],
468 hri => [{ date => '2011-12-14 12:15:12' }],
471 select => "(DATEADD(minute, ?, me.created_on))",
472 where => "me.id = ?",
473 bind => [['', 3], ['me.id' => 2 ]],
474 hri => [{ date => '2011-12-14 12:15:12.000' }],
475 skip => 'need working bindtypes',
477 msg => '-dt_add (minute) works',
481 search => { 'me.id' => 2 },
482 select => [ [ -dt_add => [second => 3, { -ident => 'me.created_on' } ] ] ],
485 select => "(datetime(me.created_on, ? || ' seconds'))",
486 where => "me.id = ?",
487 bind => [['', 3], ['me.id' => 2 ]],
488 hri => [{ date => '2011-12-14 12:12:15' }],
491 select => "(DATEADD(second, ?, me.created_on))",
492 where => "me.id = ?",
493 bind => [['', 3], ['me.id' => 2 ]],
494 hri => [{ date => '2011-12-14 12:12:15.000' }],
495 skip => 'need working bindtypes',
497 msg => '-dt_add (second) works',
501 search => { 'me.id' => 2 },
502 select => [ [ -dt_add => [second => 3, { -dt_add => [ day => 1, { -ident => 'me.created_on' } ] } ] ] ],
505 select => "(datetime((datetime(me.created_on, ? || ' days')), ? || ' seconds'))",
506 where => "me.id = ?",
507 bind => [['', 1], [ '', 3 ], ['me.id', 2]],
508 hri => [{ date => '2011-12-15 12:12:15' }],
511 select => "(DATEADD(second, ?, (DATEADD(dayofyear, ?, me.created_on))))",
512 where => "me.id = ?",
513 bind => [['', 3], [ '', 1 ], ['me.id', 2]],
514 hri => [{ date => '2011-12-15 12:12:15.000' }],
515 skip => 'need working bindtypes',
517 msg => 'nested -dt_add works',
521 search => { 'me.id' => 2 },
522 select => [ [ -dt_diff => [year => \'me.starts_at', { -ident => 'me.created_on' } ] ] ],
525 exception_like => qr/date diff not supported for part "year" with database "SQLite"/,
528 select => "DATEDIFF(year, me.created_on, me.starts_at)",
529 where => "me.id = ?",
530 bind => [['me.id', 2]],
531 hri => [{ year => -1 }],
533 msg => '-dt_diff (year) works',
540 for my $db (keys %rs) {
541 my $db_test = $t->{$db};
542 next DB_TEST unless $db_test;
548 ( exists $t->{select}
549 ? ( select => $t->{select}, as => $t->{as} )
550 : ( columns => [qw(starts_at created_on skip_inflation)] )
553 $my_rs = $rs{$db}->search($t->{search}, $stuff);
554 $r = $my_rs->as_query
557 if ($db_test->{exception_like}) {
560 $db_test->{exception_like},
561 "throws the expected exception ($db_test->{exception_like})",
564 if ($db_test->{warning_like}) {
567 $db_test->{warning_like},
568 "issues the expected warning ($db_test->{warning_like})"
576 "(SELECT $db_test->{select} FROM event me WHERE $db_test->{where})",
578 ($t->{msg} ? "$t->{msg} ($db)" : ())
582 if (my $hri = $db_test->{hri}) {
583 skip "Cannot test database we are not connected to ($db)", 1 unless $dbs_to_test{$db};
584 skip $db_test->{skip} . " ($db)", 1 if $db_test->{skip};
586 my $msg = ($t->{msg} ? "$t->{msg} ($db actually pulls expected data)" : '');
588 is_deeply [ $my_rs->hri_dump->all ], $hri, $msg;