Commit | Line | Data |
5e6893d4 |
1 | use strict; |
2 | use warnings; |
3 | |
4 | use Test::More; |
fcaf47ee |
5 | use Test::Exception; |
5e6893d4 |
6 | |
7 | use lib qw(t/lib); |
8 | use DBIC::SqlMakerTest; |
9 | use DateTime; |
f94672f9 |
10 | use DBIx::Class::SQLMaker::MSSQL; |
11 | use Try::Tiny; |
5e6893d4 |
12 | |
f94672f9 |
13 | use DBICTest; |
5e6893d4 |
14 | |
f94672f9 |
15 | my %dbs_to_test = ( |
16 | sqlite => 1, |
17 | mssql => 0, |
18 | ); |
19 | |
20 | my %schema = ( |
21 | sqlite => DBICTest->init_schema( no_populate => 1 ), |
22 | mssql => do { |
23 | my ($dsn, $user, $pass) = @ENV{map { "DBICTEST_MSSQL_ODBC_${_}" } qw/DSN USER PASS/}; |
24 | if ($dsn && $user) { |
25 | my $s = DBICTest::Schema->connect($dsn, $user, $pass); |
26 | try { $s->storage->ensure_connected }; |
5e6893d4 |
27 | |
f94672f9 |
28 | $s->storage->dbh_do (sub { |
29 | my ($storage, $dbh) = @_; |
30 | eval { $dbh->do("DROP TABLE event") }; |
31 | $dbh->do(<<'SQL'); |
32 | CREATE TABLE event ( |
33 | id INT IDENTITY NOT NULL, |
34 | starts_at DATE NOT NULL, |
35 | created_on DATETIME NOT NULL, |
36 | varchar_date VARCHAR(20), |
37 | varchar_datetime VARCHAR(20), |
38 | skip_inflation DATETIME, |
39 | ts_without_tz DATETIME, |
40 | |
41 | primary key(id) |
42 | ) |
43 | SQL |
44 | $dbs_to_test{mssql} = 1; |
45 | }); |
46 | $s; |
47 | } else { |
48 | DBICTest->init_schema( no_deploy=> 1, storage_type => '::DBI::MSSQL' ) |
49 | } |
50 | }, |
fcaf47ee |
51 | ); |
5e6893d4 |
52 | |
f94672f9 |
53 | my %rs = map { $_ => $schema{$_}->resultset('Event') } keys %schema; |
54 | |
55 | $rs{sqlite}->populate([ |
56 | [qw(starts_at created_on skip_inflation)], |
57 | ['2010-12-12', '2010-12-14 12:12:12', '2019-12-12 12:12:12'], |
58 | ['2010-12-12', '2011-12-14 12:12:12', '2011-12-12 12:12:12'], |
59 | ]); |
60 | |
61 | $rs{mssql}->populate([ |
62 | [qw(starts_at created_on skip_inflation)], |
63 | ['2010-12-12', '2010-12-14 12:12:12.000', '2019-12-12 12:12:12.000'], |
64 | ['2010-12-12', '2011-12-14 12:12:12.000', '2011-12-12 12:12:12.000'], |
65 | ]) if $schema{mssql}->storage->connected; |
66 | |
5e6893d4 |
67 | my $date = DateTime->new( |
68 | year => 2010, |
69 | month => 12, |
70 | day => 14, |
71 | hour => 12, |
72 | minute => 12, |
73 | second => 12, |
74 | ); |
75 | |
f94672f9 |
76 | sub hri_thing { |
77 | return { |
78 | starts_at => $_[0], |
79 | created_on => $_[1], |
80 | skip_inflation => $_[2] |
81 | } |
82 | } |
83 | |
5e6893d4 |
84 | my $date2 = $date->clone->set_day(16); |
85 | |
fcaf47ee |
86 | my @tests = ( |
87 | { |
f94672f9 |
88 | search => { 'me.created_on' => { -dt => $date } }, |
fcaf47ee |
89 | sqlite => { |
f94672f9 |
90 | select => 'me.starts_at, me.created_on, me.skip_inflation', |
91 | where => 'me.created_on = ?', |
92 | bind => [[ 'me.created_on', '2010-12-14 12:12:12' ]], |
93 | hri => [hri_thing('2010-12-12', '2010-12-14 12:12:12', '2019-12-12 12:12:12')], |
fcaf47ee |
94 | }, |
f94672f9 |
95 | mssql => { |
96 | select => 'me.starts_at, me.created_on, me.skip_inflation', |
97 | where => 'me.created_on = ?', |
98 | bind => [[ 'me.created_on', '2010-12-14 12:12:12.000' ]], |
99 | hri => [hri_thing('2010-12-12', '2010-12-14 12:12:12.000', '2019-12-12 12:12:12.000')], |
fcaf47ee |
100 | }, |
101 | msg => '-dt_now works', |
102 | }, |
f94672f9 |
103 | |
fcaf47ee |
104 | { |
f94672f9 |
105 | search => { 'me.id' => 1 }, |
106 | select => [ [ -dt_year => { -ident => 'me.created_on' } ] ], |
107 | as => [ 'year' ], |
108 | mssql => { |
109 | select => "DATEPART(year, me.created_on)", |
110 | where => "me.id = ?", |
111 | bind => [['me.id' => 1 ]], |
112 | hri => [{ year => 2010 }], |
113 | }, |
fcaf47ee |
114 | sqlite => { |
f94672f9 |
115 | select => "STRFTIME('%Y', me.created_on)", |
116 | where => "me.id = ?", |
117 | bind => [['me.id' => 1 ]], |
118 | hri => [{ year => 2010 }], |
fcaf47ee |
119 | }, |
120 | msg => '-dt_year works', |
121 | }, |
f94672f9 |
122 | |
fcaf47ee |
123 | { |
f94672f9 |
124 | search => { 'me.id' => 1 }, |
464a9709 |
125 | select => [ [ -dt_get => [[qw(year month)], { -ident => 'me.created_on' }] ] ], |
126 | as => [ qw(year month) ], |
127 | mssql => { |
128 | select => "DATEPART(year, me.created_on), DATEPART(month, me.created_on)", |
129 | where => "me.id = ?", |
130 | bind => [['me.id' => 1 ]], |
131 | hri => [{ year => 2010, month => 12 }], |
132 | }, |
133 | sqlite => { |
134 | select => "STRFTIME('%Y', me.created_on), STRFTIME('%m', me.created_on)", |
135 | where => "me.id = ?", |
136 | bind => [['me.id' => 1 ]], |
137 | hri => [{ year => 2010, month => 12 }], |
138 | }, |
139 | msg => '-dt_get (year, month) works', |
140 | }, |
141 | |
142 | { |
143 | search => { 'me.id' => 1 }, |
f94672f9 |
144 | select => [ [ -dt_month => { -ident => 'me.created_on' } ] ], |
145 | as => [ 'month' ], |
fcaf47ee |
146 | sqlite => { |
f94672f9 |
147 | select => "STRFTIME('%m', me.created_on)", |
148 | where => "me.id = ?", |
149 | bind => [['me.id' => 1 ]], |
150 | hri => [{ month => 12 }], |
151 | }, |
152 | mssql => { |
153 | select => "DATEPART(month, me.created_on)", |
154 | where => "me.id = ?", |
155 | bind => [['me.id' => 1 ]], |
156 | hri => [{ month => 12 }], |
fcaf47ee |
157 | }, |
158 | msg => '-dt_month works', |
159 | }, |
f94672f9 |
160 | |
fcaf47ee |
161 | { |
f94672f9 |
162 | search => { 'me.id' => 1 }, |
163 | select => [ [ -dt_day => { -ident => 'me.created_on' } ] ], |
164 | as => [ 'day' ], |
fcaf47ee |
165 | sqlite => { |
f94672f9 |
166 | select => "STRFTIME('%d', me.created_on)", |
167 | where => "me.id = ?", |
168 | bind => [['me.id' => 1 ]], |
169 | hri => [{ day => 14 }], |
170 | }, |
171 | mssql => { |
172 | select => "DATEPART(day, me.created_on)", |
173 | where => "me.id = ?", |
174 | bind => [['me.id' => 1 ]], |
175 | hri => [{ day => 14 }], |
fcaf47ee |
176 | }, |
177 | msg => '-dt_day works', |
178 | }, |
f94672f9 |
179 | |
fcaf47ee |
180 | { |
f94672f9 |
181 | search => { 'me.id' => 1 }, |
182 | select => [ [ -dt_hour => { -ident => 'me.created_on' } ] ], |
183 | as => [ 'hour' ], |
fcaf47ee |
184 | sqlite => { |
f94672f9 |
185 | select => "STRFTIME('%H', me.created_on)", |
186 | where => "me.id = ?", |
187 | bind => [['me.id' => 1 ]], |
188 | hri => [{ hour => 12 }], |
189 | }, |
190 | mssql => { |
191 | select => "DATEPART(hour, me.created_on)", |
192 | where => "me.id = ?", |
193 | bind => [['me.id' => 1 ]], |
194 | hri => [{ hour => 12 }], |
fcaf47ee |
195 | }, |
196 | msg => '-dt_hour works', |
197 | }, |
f94672f9 |
198 | |
fcaf47ee |
199 | { |
f94672f9 |
200 | search => { 'me.id' => 1 }, |
201 | select => [ [ -dt_minute => { -ident => 'me.created_on' } ] ], |
202 | as => [ 'minute' ], |
fcaf47ee |
203 | sqlite => { |
f94672f9 |
204 | select => "STRFTIME('%M', me.created_on)", |
205 | where => "me.id = ?", |
206 | bind => [['me.id' => 1 ]], |
207 | hri => [{ minute => 12 }], |
208 | }, |
209 | mssql => { |
210 | select => "DATEPART(minute, me.created_on)", |
211 | where => "me.id = ?", |
212 | bind => [['me.id' => 1 ]], |
213 | hri => [{ minute => 12 }], |
fcaf47ee |
214 | }, |
215 | msg => '-dt_minute works', |
216 | }, |
f94672f9 |
217 | |
fcaf47ee |
218 | { |
f94672f9 |
219 | search => { 'me.id' => 1 }, |
220 | select => [ [ -dt_second => { -ident => 'me.created_on' } ] ], |
221 | as => [ 'second' ], |
fcaf47ee |
222 | sqlite => { |
f94672f9 |
223 | select => "STRFTIME('%S', me.created_on)", |
224 | where => "me.id = ?", |
225 | bind => [['me.id' => 1 ]], |
226 | hri => [{ second => 12 }], |
227 | }, |
228 | mssql => { |
229 | select => "DATEPART(second, me.created_on)", |
230 | where => "me.id = ?", |
231 | bind => [['me.id' => 1 ]], |
232 | hri => [{ second => 12 }], |
fcaf47ee |
233 | }, |
234 | msg => '-dt_second works', |
235 | }, |
f94672f9 |
236 | |
fcaf47ee |
237 | { |
f94672f9 |
238 | search => { 'me.id' => 2 }, |
239 | select => [ [ -dt_diff => [second => { -ident => 'me.created_on' }, \'me.skip_inflation' ] ] ], |
240 | as => [ 'sec_diff' ], |
fcaf47ee |
241 | sqlite => { |
f94672f9 |
242 | select => "(STRFTIME('%s', me.created_on) - STRFTIME('%s', me.skip_inflation))", |
243 | where => "me.id = ?", |
244 | bind => [['me.id' => 2 ]], |
245 | hri => [{ sec_diff => 2*24*60*60 }], |
246 | }, |
247 | mssql => { |
248 | select => "DATEDIFF(second, me.skip_inflation, me.created_on)", |
249 | where => "me.id = ?", |
250 | bind => [['me.id' => 2 ]], |
251 | hri => [{ sec_diff => 2*24*60*60 }], |
fcaf47ee |
252 | }, |
253 | msg => '-dt_diff (second) works', |
254 | }, |
f94672f9 |
255 | |
fcaf47ee |
256 | { |
f94672f9 |
257 | search => { 'me.id' => 2 }, |
258 | select => [ [ -dt_diff => [day => { -ident => 'me.created_on' }, \'me.skip_inflation' ] ] ], |
259 | as => [ 'day_diff' ], |
fcaf47ee |
260 | sqlite => { |
f94672f9 |
261 | select => "(JULIANDAY(me.created_on) - JULIANDAY(me.skip_inflation))", |
262 | where => "me.id = ?", |
263 | bind => [['me.id' => 2 ]], |
264 | hri => [{ day_diff => 2 }], |
265 | }, |
266 | mssql => { |
267 | select => "DATEDIFF(dayofyear, me.skip_inflation, me.created_on)", |
268 | where => "me.id = ?", |
269 | bind => [['me.id' => 2 ]], |
270 | hri => [{ day_diff => 2 }], |
fcaf47ee |
271 | }, |
272 | msg => '-dt_diff (day) works', |
273 | }, |
f94672f9 |
274 | |
fcaf47ee |
275 | { |
f94672f9 |
276 | search => { 'me.id' => 2 }, |
277 | select => [ [ -dt_add => [year => 3, { -ident => 'me.created_on' } ] ] ], |
278 | as => [ 'date' ], |
fcaf47ee |
279 | sqlite => { |
f94672f9 |
280 | select => "(datetime(me.created_on, ? || ' years'))", |
281 | where => "me.id = ?", |
282 | bind => [['', 3], ['me.id' => 2 ]], |
283 | hri => [{ date => '2014-12-14 12:12:12' }], |
284 | }, |
285 | mssql => { |
286 | select => "(DATEADD(year, ?, me.created_on))", |
287 | where => "me.id = ?", |
288 | bind => [['', 3], ['me.id' => 2 ]], |
289 | hri => [{ date => '2014-12-14 12:12:12.000' }], |
290 | skip => 'need working bindtypes', |
fcaf47ee |
291 | }, |
292 | msg => '-dt_add (year) works', |
293 | }, |
f94672f9 |
294 | |
fcaf47ee |
295 | { |
f94672f9 |
296 | search => { 'me.id' => 2 }, |
297 | select => [ [ -dt_add => [month => 3, { -ident => 'me.created_on' } ] ] ], |
298 | as => [ 'date' ], |
fcaf47ee |
299 | sqlite => { |
f94672f9 |
300 | select => "(datetime(me.created_on, ? || ' months'))", |
301 | where => "me.id = ?", |
302 | bind => [['', 3], ['me.id' => 2 ]], |
303 | hri => [{ date => '2012-03-14 12:12:12' }], |
304 | }, |
305 | mssql => { |
306 | select => "(DATEADD(month, ?, me.created_on))", |
307 | where => "me.id = ?", |
308 | bind => [['', 3], ['me.id' => 2 ]], |
309 | hri => [{ date => '2012-03-14 12:12:12.000' }], |
310 | skip => 'need working bindtypes', |
fcaf47ee |
311 | }, |
312 | msg => '-dt_add (month) works', |
313 | }, |
f94672f9 |
314 | |
fcaf47ee |
315 | { |
f94672f9 |
316 | search => { 'me.id' => 2 }, |
317 | select => [ [ -dt_add => [day => 3, { -ident => 'me.created_on' } ] ] ], |
318 | as => [ 'date' ], |
fcaf47ee |
319 | sqlite => { |
f94672f9 |
320 | select => "(datetime(me.created_on, ? || ' days'))", |
321 | where => "me.id = ?", |
322 | bind => [['', 3], ['me.id' => 2 ]], |
323 | hri => [{ date => '2011-12-17 12:12:12' }], |
324 | }, |
325 | mssql => { |
326 | select => "(DATEADD(dayofyear, ?, me.created_on))", |
327 | where => "me.id = ?", |
328 | bind => [['', 3], ['me.id' => 2 ]], |
329 | hri => [{ date => '2011-12-17 12:12:12.000' }], |
330 | skip => 'need working bindtypes', |
fcaf47ee |
331 | }, |
332 | msg => '-dt_add (day) works', |
333 | }, |
f94672f9 |
334 | |
fcaf47ee |
335 | { |
f94672f9 |
336 | search => { 'me.id' => 2 }, |
337 | select => [ [ -dt_add => [hour => 3, { -ident => 'me.created_on' } ] ] ], |
338 | as => [ 'date' ], |
fcaf47ee |
339 | sqlite => { |
f94672f9 |
340 | select => "(datetime(me.created_on, ? || ' hours'))", |
341 | where => "me.id = ?", |
342 | bind => [['', 3], ['me.id' => 2 ]], |
343 | hri => [{ date => '2011-12-14 15:12:12' }], |
344 | }, |
345 | mssql => { |
346 | select => "(DATEADD(hour, ?, me.created_on))", |
347 | where => "me.id = ?", |
348 | bind => [['', 3], ['me.id' => 2 ]], |
349 | hri => [{ date => '2011-12-14 15:12:12.000' }], |
350 | skip => 'need working bindtypes', |
fcaf47ee |
351 | }, |
352 | msg => '-dt_add (hour) works', |
353 | }, |
f94672f9 |
354 | |
fcaf47ee |
355 | { |
f94672f9 |
356 | search => { 'me.id' => 2 }, |
357 | select => [ [ -dt_add => [minute => 3, { -ident => 'me.created_on' } ] ] ], |
358 | as => [ 'date' ], |
fcaf47ee |
359 | sqlite => { |
f94672f9 |
360 | select => "(datetime(me.created_on, ? || ' minutes'))", |
361 | where => "me.id = ?", |
362 | bind => [['', 3], ['me.id' => 2 ]], |
363 | hri => [{ date => '2011-12-14 12:15:12' }], |
364 | }, |
365 | mssql => { |
366 | select => "(DATEADD(minute, ?, me.created_on))", |
367 | where => "me.id = ?", |
368 | bind => [['', 3], ['me.id' => 2 ]], |
369 | hri => [{ date => '2011-12-14 12:15:12.000' }], |
370 | skip => 'need working bindtypes', |
fcaf47ee |
371 | }, |
372 | msg => '-dt_add (minute) works', |
373 | }, |
f94672f9 |
374 | |
fcaf47ee |
375 | { |
f94672f9 |
376 | search => { 'me.id' => 2 }, |
377 | select => [ [ -dt_add => [second => 3, { -ident => 'me.created_on' } ] ] ], |
378 | as => [ 'date' ], |
fcaf47ee |
379 | sqlite => { |
f94672f9 |
380 | select => "(datetime(me.created_on, ? || ' seconds'))", |
381 | where => "me.id = ?", |
382 | bind => [['', 3], ['me.id' => 2 ]], |
383 | hri => [{ date => '2011-12-14 12:12:15' }], |
384 | }, |
385 | mssql => { |
386 | select => "(DATEADD(second, ?, me.created_on))", |
387 | where => "me.id = ?", |
388 | bind => [['', 3], ['me.id' => 2 ]], |
389 | hri => [{ date => '2011-12-14 12:12:15.000' }], |
390 | skip => 'need working bindtypes', |
fcaf47ee |
391 | }, |
392 | msg => '-dt_add (second) works', |
393 | }, |
f94672f9 |
394 | |
fcaf47ee |
395 | { |
f94672f9 |
396 | search => { 'me.id' => 2 }, |
397 | select => [ [ -dt_add => [second => 3, { -dt_add => [ day => 1, { -ident => 'me.created_on' } ] } ] ] ], |
398 | as => [ 'date' ], |
399 | sqlite => { |
400 | select => "(datetime((datetime(me.created_on, ? || ' days')), ? || ' seconds'))", |
401 | where => "me.id = ?", |
402 | bind => [['', 1], [ '', 3 ], ['me.id', 2]], |
403 | hri => [{ date => '2011-12-15 12:12:15' }], |
404 | }, |
405 | mssql => { |
406 | select => "(DATEADD(second, ?, (DATEADD(dayofyear, ?, me.created_on))))", |
407 | where => "me.id = ?", |
408 | bind => [['', 3], [ '', 1 ], ['me.id', 2]], |
409 | hri => [{ date => '2011-12-15 12:12:15.000' }], |
410 | skip => 'need working bindtypes', |
411 | }, |
412 | msg => 'nested -dt_add works', |
413 | }, |
414 | |
415 | { |
416 | search => { 'me.id' => 2 }, |
417 | select => [ [ -dt_diff => [year => \'me.starts_at', { -ident => 'me.created_on' } ] ] ], |
418 | as => [ 'year' ], |
fcaf47ee |
419 | sqlite => { |
420 | exception_like => qr/date diff not supported for part "year" with database "SQLite"/, |
421 | }, |
f94672f9 |
422 | mssql => { |
423 | select => "DATEDIFF(year, me.created_on, me.starts_at)", |
424 | where => "me.id = ?", |
425 | bind => [['me.id', 2]], |
426 | hri => [{ year => -1 }], |
427 | }, |
428 | msg => '-dt_diff (year) works', |
fcaf47ee |
429 | }, |
430 | ); |
5a44a8ec |
431 | |
fcaf47ee |
432 | for my $t (@tests) { |
5a44a8ec |
433 | |
fcaf47ee |
434 | DB_TEST: |
f94672f9 |
435 | for my $db (keys %rs) { |
fcaf47ee |
436 | my $db_test = $t->{$db}; |
437 | next DB_TEST unless $db_test; |
5e6893d4 |
438 | |
f94672f9 |
439 | my ($r, $my_rs); |
fcaf47ee |
440 | |
441 | my $cref = sub { |
f94672f9 |
442 | my $stuff = { |
443 | ( exists $t->{select} |
444 | ? ( select => $t->{select}, as => $t->{as} ) |
445 | : ( columns => [qw(starts_at created_on skip_inflation)] ) |
446 | ) |
447 | }; |
448 | $my_rs = $rs{$db}->search($t->{search}, $stuff); |
449 | $r = $my_rs->as_query |
fcaf47ee |
450 | }; |
5e6893d4 |
451 | |
fcaf47ee |
452 | if ($db_test->{exception_like}) { |
453 | throws_ok( |
454 | sub { $cref->() }, |
455 | $db_test->{exception_like}, |
456 | "throws the expected exception ($db_test->{exception_like})", |
457 | ); |
458 | } else { |
459 | if ($db_test->{warning_like}) { |
460 | warning_like( |
461 | sub { $cref->() }, |
462 | $db_test->{warning_like}, |
463 | "issues the expected warning ($db_test->{warning_like})" |
464 | ); |
465 | } |
466 | else { |
467 | $cref->(); |
468 | } |
469 | is_same_sql_bind( |
f94672f9 |
470 | $r, |
471 | "(SELECT $db_test->{select} FROM event me WHERE $db_test->{where})", |
fcaf47ee |
472 | $db_test->{bind}, |
f94672f9 |
473 | ($t->{msg} ? "$t->{msg} ($db)" : ()) |
fcaf47ee |
474 | ); |
f94672f9 |
475 | |
476 | SKIP: { |
477 | if (my $hri = $db_test->{hri}) { |
478 | skip "Cannot test database we are not connected to ($db)", 1 unless $dbs_to_test{$db}; |
479 | skip $db_test->{skip} . " ($db)", 1 if $db_test->{skip}; |
480 | |
481 | my $msg = ($t->{msg} ? "$t->{msg} ($db actually pulls expected data)" : ''); |
482 | try { |
483 | is_deeply [ $my_rs->hri_dump->all ], $hri, $msg; |
484 | } catch { |
485 | ok 0, $msg . " $_"; |
486 | } |
487 | } } |
fcaf47ee |
488 | } |
489 | } |
490 | } |
6edb8b2f |
491 | |
5e6893d4 |
492 | done_testing; |