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 = ( |
35621f12 |
16 | sqlite => 1, |
17 | mssql => 0, |
18 | postgres => 1, |
f94672f9 |
19 | ); |
20 | |
21 | my %schema = ( |
22 | sqlite => DBICTest->init_schema( no_populate => 1 ), |
23 | mssql => do { |
24 | my ($dsn, $user, $pass) = @ENV{map { "DBICTEST_MSSQL_ODBC_${_}" } qw/DSN USER PASS/}; |
25 | if ($dsn && $user) { |
26 | my $s = DBICTest::Schema->connect($dsn, $user, $pass); |
27 | try { $s->storage->ensure_connected }; |
5e6893d4 |
28 | |
f94672f9 |
29 | $s->storage->dbh_do (sub { |
30 | my ($storage, $dbh) = @_; |
31 | eval { $dbh->do("DROP TABLE event") }; |
32 | $dbh->do(<<'SQL'); |
33 | CREATE 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, |
41 | |
42 | primary key(id) |
43 | ) |
44 | SQL |
45 | $dbs_to_test{mssql} = 1; |
46 | }); |
47 | $s; |
48 | } else { |
49 | DBICTest->init_schema( no_deploy=> 1, storage_type => '::DBI::MSSQL' ) |
50 | } |
51 | }, |
35621f12 |
52 | ## copypasta'd for great justice |
53 | postgres => do { |
54 | my ($dsn, $user, $pass) = @ENV{map { "DBICTEST_POSTGRES_${_}" } qw/DSN USER PASS/}; |
55 | if ($dsn && $user) { |
56 | my $s = DBICTest::Schema->connect($dsn, $user, $pass); |
57 | try { $s->storage->ensure_connected }; |
58 | |
59 | $s->storage->dbh_do (sub { |
60 | my ($storage, $dbh) = @_; |
61 | eval { $dbh->do("DROP TABLE event") }; |
62 | $dbh->do(<<'SQL'); |
63 | CREATE 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 |
71 | ) |
72 | SQL |
73 | $dbs_to_test{postgres} = 1; |
74 | }); |
75 | $s; |
76 | } else { |
77 | DBICTest->init_schema( no_deploy=> 1, storage_type => '::DBI::Pg' ) |
78 | } |
79 | } |
fcaf47ee |
80 | ); |
5e6893d4 |
81 | |
f94672f9 |
82 | my %rs = map { $_ => $schema{$_}->resultset('Event') } keys %schema; |
83 | |
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'], |
88 | ]); |
89 | |
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; |
95 | |
35621f12 |
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; |
101 | |
102 | |
5e6893d4 |
103 | my $date = DateTime->new( |
104 | year => 2010, |
105 | month => 12, |
106 | day => 14, |
107 | hour => 12, |
108 | minute => 12, |
109 | second => 12, |
110 | ); |
111 | |
f94672f9 |
112 | sub hri_thing { |
113 | return { |
114 | starts_at => $_[0], |
115 | created_on => $_[1], |
116 | skip_inflation => $_[2] |
117 | } |
118 | } |
119 | |
5e6893d4 |
120 | my $date2 = $date->clone->set_day(16); |
121 | |
35621f12 |
122 | ## test format: |
123 | ## search => { dbic_search_code/params } |
124 | ## rdbms_name => literal_sql |
fcaf47ee |
125 | my @tests = ( |
35621f12 |
126 | ## -dt-now tests |
fcaf47ee |
127 | { |
f94672f9 |
128 | search => { 'me.created_on' => { -dt => $date } }, |
fcaf47ee |
129 | sqlite => { |
f94672f9 |
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')], |
fcaf47ee |
134 | }, |
f94672f9 |
135 | mssql => { |
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')], |
fcaf47ee |
140 | }, |
35621f12 |
141 | postgres => { |
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')], |
146 | }, |
fcaf47ee |
147 | msg => '-dt_now works', |
148 | }, |
35621f12 |
149 | ## -dt_year tests |
fcaf47ee |
150 | { |
f94672f9 |
151 | search => { 'me.id' => 1 }, |
152 | select => [ [ -dt_year => { -ident => 'me.created_on' } ] ], |
153 | as => [ 'year' ], |
154 | mssql => { |
155 | select => "DATEPART(year, me.created_on)", |
156 | where => "me.id = ?", |
157 | bind => [['me.id' => 1 ]], |
158 | hri => [{ year => 2010 }], |
159 | }, |
fcaf47ee |
160 | sqlite => { |
f94672f9 |
161 | select => "STRFTIME('%Y', me.created_on)", |
162 | where => "me.id = ?", |
163 | bind => [['me.id' => 1 ]], |
164 | hri => [{ year => 2010 }], |
fcaf47ee |
165 | }, |
35621f12 |
166 | postgres => { |
167 | select => "EXTRACT(year FROM me.created_on)", |
168 | where => "me.id = ?", |
169 | bind => [['me.id' => 1 ]], |
170 | hri => [{ year => 2010 }], |
171 | }, |
fcaf47ee |
172 | msg => '-dt_year works', |
173 | }, |
35621f12 |
174 | ## -dt_get(year, month) tests |
fcaf47ee |
175 | { |
f94672f9 |
176 | search => { 'me.id' => 1 }, |
464a9709 |
177 | select => [ [ -dt_get => [[qw(year month)], { -ident => 'me.created_on' }] ] ], |
178 | as => [ qw(year month) ], |
179 | mssql => { |
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 }], |
184 | }, |
185 | sqlite => { |
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 }], |
190 | }, |
35621f12 |
191 | postgres => { |
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 }], |
196 | }, |
464a9709 |
197 | msg => '-dt_get (year, month) works', |
198 | }, |
35621f12 |
199 | ## -dt_month tests |
464a9709 |
200 | { |
201 | search => { 'me.id' => 1 }, |
f94672f9 |
202 | select => [ [ -dt_month => { -ident => 'me.created_on' } ] ], |
203 | as => [ 'month' ], |
fcaf47ee |
204 | sqlite => { |
f94672f9 |
205 | select => "STRFTIME('%m', me.created_on)", |
206 | where => "me.id = ?", |
207 | bind => [['me.id' => 1 ]], |
208 | hri => [{ month => 12 }], |
209 | }, |
210 | mssql => { |
211 | select => "DATEPART(month, me.created_on)", |
212 | where => "me.id = ?", |
213 | bind => [['me.id' => 1 ]], |
214 | hri => [{ month => 12 }], |
fcaf47ee |
215 | }, |
35621f12 |
216 | postgres => { |
217 | select => "EXTRACT(month FROM me.created_on)", |
218 | where => "me.id = ?", |
219 | bind => [['me.id' => 1 ]], |
220 | hri => [{ month => 12 }], |
221 | |
222 | }, |
fcaf47ee |
223 | msg => '-dt_month works', |
224 | }, |
35621f12 |
225 | ## -dt_day tests |
fcaf47ee |
226 | { |
f94672f9 |
227 | search => { 'me.id' => 1 }, |
228 | select => [ [ -dt_day => { -ident => 'me.created_on' } ] ], |
229 | as => [ 'day' ], |
fcaf47ee |
230 | sqlite => { |
f94672f9 |
231 | select => "STRFTIME('%d', me.created_on)", |
232 | where => "me.id = ?", |
233 | bind => [['me.id' => 1 ]], |
234 | hri => [{ day => 14 }], |
235 | }, |
236 | mssql => { |
237 | select => "DATEPART(day, me.created_on)", |
238 | where => "me.id = ?", |
239 | bind => [['me.id' => 1 ]], |
240 | hri => [{ day => 14 }], |
fcaf47ee |
241 | }, |
35621f12 |
242 | postgres => { |
243 | select => "EXTRACT(day FROM me.created_on)", |
244 | where => "me.id = ?", |
245 | bind => [['me.id' => 1 ]], |
246 | hri => [{ day => 14 }], |
247 | |
248 | }, |
fcaf47ee |
249 | msg => '-dt_day works', |
250 | }, |
35621f12 |
251 | ## -dt_hour tests |
fcaf47ee |
252 | { |
f94672f9 |
253 | search => { 'me.id' => 1 }, |
254 | select => [ [ -dt_hour => { -ident => 'me.created_on' } ] ], |
255 | as => [ 'hour' ], |
fcaf47ee |
256 | sqlite => { |
f94672f9 |
257 | select => "STRFTIME('%H', me.created_on)", |
258 | where => "me.id = ?", |
259 | bind => [['me.id' => 1 ]], |
260 | hri => [{ hour => 12 }], |
261 | }, |
262 | mssql => { |
263 | select => "DATEPART(hour, me.created_on)", |
264 | where => "me.id = ?", |
265 | bind => [['me.id' => 1 ]], |
266 | hri => [{ hour => 12 }], |
fcaf47ee |
267 | }, |
35621f12 |
268 | postgres => { |
269 | select => "EXTRACT(hour FROM me.created_on)", |
270 | where => "me.id = ?", |
271 | bind => [['me.id' => 1 ]], |
272 | hri => [{ hour => 12 }], |
273 | }, |
fcaf47ee |
274 | msg => '-dt_hour works', |
275 | }, |
35621f12 |
276 | ## -dt_minute tests |
fcaf47ee |
277 | { |
f94672f9 |
278 | search => { 'me.id' => 1 }, |
279 | select => [ [ -dt_minute => { -ident => 'me.created_on' } ] ], |
280 | as => [ 'minute' ], |
fcaf47ee |
281 | sqlite => { |
f94672f9 |
282 | select => "STRFTIME('%M', me.created_on)", |
283 | where => "me.id = ?", |
284 | bind => [['me.id' => 1 ]], |
285 | hri => [{ minute => 12 }], |
286 | }, |
287 | mssql => { |
288 | select => "DATEPART(minute, me.created_on)", |
289 | where => "me.id = ?", |
290 | bind => [['me.id' => 1 ]], |
291 | hri => [{ minute => 12 }], |
fcaf47ee |
292 | }, |
35621f12 |
293 | postgres => { |
294 | select => "EXTRACT(minute FROM me.created_on)", |
295 | where => "me.id = ?", |
296 | bind => [['me.id' => 1 ]], |
297 | hri => [{ minute => 12 }], |
298 | }, |
fcaf47ee |
299 | msg => '-dt_minute works', |
300 | }, |
35621f12 |
301 | ## -dt_second tests |
fcaf47ee |
302 | { |
f94672f9 |
303 | search => { 'me.id' => 1 }, |
304 | select => [ [ -dt_second => { -ident => 'me.created_on' } ] ], |
305 | as => [ 'second' ], |
fcaf47ee |
306 | sqlite => { |
f94672f9 |
307 | select => "STRFTIME('%S', me.created_on)", |
308 | where => "me.id = ?", |
309 | bind => [['me.id' => 1 ]], |
310 | hri => [{ second => 12 }], |
311 | }, |
312 | mssql => { |
313 | select => "DATEPART(second, me.created_on)", |
314 | where => "me.id = ?", |
315 | bind => [['me.id' => 1 ]], |
316 | hri => [{ second => 12 }], |
fcaf47ee |
317 | }, |
35621f12 |
318 | postgres => { |
319 | select => "EXTRACT(second FROM me.created_on)", |
320 | where => "me.id = ?", |
321 | bind => [['me.id' => 1 ]], |
322 | hri => [{ second => 12 }], |
323 | |
324 | }, |
fcaf47ee |
325 | msg => '-dt_second works', |
326 | }, |
35621f12 |
327 | ## -dt_diff(second) tests |
fcaf47ee |
328 | { |
f94672f9 |
329 | search => { 'me.id' => 2 }, |
330 | select => [ [ -dt_diff => [second => { -ident => 'me.created_on' }, \'me.skip_inflation' ] ] ], |
331 | as => [ 'sec_diff' ], |
fcaf47ee |
332 | sqlite => { |
f94672f9 |
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 }], |
337 | }, |
338 | mssql => { |
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 }], |
fcaf47ee |
343 | }, |
35621f12 |
344 | postgres => { |
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 }], |
349 | }, |
fcaf47ee |
350 | msg => '-dt_diff (second) works', |
351 | }, |
f94672f9 |
352 | |
35621f12 |
353 | # -dt_diff(day) tests |
fcaf47ee |
354 | { |
f94672f9 |
355 | search => { 'me.id' => 2 }, |
356 | select => [ [ -dt_diff => [day => { -ident => 'me.created_on' }, \'me.skip_inflation' ] ] ], |
357 | as => [ 'day_diff' ], |
fcaf47ee |
358 | sqlite => { |
f94672f9 |
359 | select => "(JULIANDAY(me.created_on) - JULIANDAY(me.skip_inflation))", |
360 | where => "me.id = ?", |
361 | bind => [['me.id' => 2 ]], |
362 | hri => [{ day_diff => 2 }], |
363 | }, |
364 | mssql => { |
365 | select => "DATEDIFF(dayofyear, me.skip_inflation, me.created_on)", |
366 | where => "me.id = ?", |
367 | bind => [['me.id' => 2 ]], |
368 | hri => [{ day_diff => 2 }], |
fcaf47ee |
369 | }, |
35621f12 |
370 | postgres => { |
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 }], |
375 | }, |
376 | |
fcaf47ee |
377 | msg => '-dt_diff (day) works', |
378 | }, |
f94672f9 |
379 | |
fcaf47ee |
380 | { |
f94672f9 |
381 | search => { 'me.id' => 2 }, |
382 | select => [ [ -dt_add => [year => 3, { -ident => 'me.created_on' } ] ] ], |
383 | as => [ 'date' ], |
fcaf47ee |
384 | sqlite => { |
f94672f9 |
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' }], |
389 | }, |
390 | mssql => { |
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', |
fcaf47ee |
396 | }, |
397 | msg => '-dt_add (year) works', |
398 | }, |
f94672f9 |
399 | |
fcaf47ee |
400 | { |
f94672f9 |
401 | search => { 'me.id' => 2 }, |
402 | select => [ [ -dt_add => [month => 3, { -ident => 'me.created_on' } ] ] ], |
403 | as => [ 'date' ], |
fcaf47ee |
404 | sqlite => { |
f94672f9 |
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' }], |
409 | }, |
410 | mssql => { |
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', |
fcaf47ee |
416 | }, |
417 | msg => '-dt_add (month) works', |
418 | }, |
f94672f9 |
419 | |
fcaf47ee |
420 | { |
f94672f9 |
421 | search => { 'me.id' => 2 }, |
422 | select => [ [ -dt_add => [day => 3, { -ident => 'me.created_on' } ] ] ], |
423 | as => [ 'date' ], |
fcaf47ee |
424 | sqlite => { |
f94672f9 |
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' }], |
429 | }, |
430 | mssql => { |
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', |
fcaf47ee |
436 | }, |
437 | msg => '-dt_add (day) works', |
438 | }, |
f94672f9 |
439 | |
fcaf47ee |
440 | { |
f94672f9 |
441 | search => { 'me.id' => 2 }, |
442 | select => [ [ -dt_add => [hour => 3, { -ident => 'me.created_on' } ] ] ], |
443 | as => [ 'date' ], |
fcaf47ee |
444 | sqlite => { |
f94672f9 |
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' }], |
449 | }, |
450 | mssql => { |
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', |
fcaf47ee |
456 | }, |
457 | msg => '-dt_add (hour) works', |
458 | }, |
f94672f9 |
459 | |
fcaf47ee |
460 | { |
f94672f9 |
461 | search => { 'me.id' => 2 }, |
462 | select => [ [ -dt_add => [minute => 3, { -ident => 'me.created_on' } ] ] ], |
463 | as => [ 'date' ], |
fcaf47ee |
464 | sqlite => { |
f94672f9 |
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' }], |
469 | }, |
470 | mssql => { |
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', |
fcaf47ee |
476 | }, |
477 | msg => '-dt_add (minute) works', |
478 | }, |
f94672f9 |
479 | |
fcaf47ee |
480 | { |
f94672f9 |
481 | search => { 'me.id' => 2 }, |
482 | select => [ [ -dt_add => [second => 3, { -ident => 'me.created_on' } ] ] ], |
483 | as => [ 'date' ], |
fcaf47ee |
484 | sqlite => { |
f94672f9 |
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' }], |
489 | }, |
490 | mssql => { |
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', |
fcaf47ee |
496 | }, |
497 | msg => '-dt_add (second) works', |
498 | }, |
f94672f9 |
499 | |
fcaf47ee |
500 | { |
f94672f9 |
501 | search => { 'me.id' => 2 }, |
502 | select => [ [ -dt_add => [second => 3, { -dt_add => [ day => 1, { -ident => 'me.created_on' } ] } ] ] ], |
503 | as => [ 'date' ], |
504 | sqlite => { |
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' }], |
509 | }, |
510 | mssql => { |
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', |
516 | }, |
517 | msg => 'nested -dt_add works', |
518 | }, |
519 | |
520 | { |
521 | search => { 'me.id' => 2 }, |
522 | select => [ [ -dt_diff => [year => \'me.starts_at', { -ident => 'me.created_on' } ] ] ], |
523 | as => [ 'year' ], |
fcaf47ee |
524 | sqlite => { |
525 | exception_like => qr/date diff not supported for part "year" with database "SQLite"/, |
526 | }, |
f94672f9 |
527 | mssql => { |
528 | select => "DATEDIFF(year, me.created_on, me.starts_at)", |
529 | where => "me.id = ?", |
530 | bind => [['me.id', 2]], |
531 | hri => [{ year => -1 }], |
532 | }, |
533 | msg => '-dt_diff (year) works', |
fcaf47ee |
534 | }, |
535 | ); |
5a44a8ec |
536 | |
fcaf47ee |
537 | for my $t (@tests) { |
5a44a8ec |
538 | |
fcaf47ee |
539 | DB_TEST: |
f94672f9 |
540 | for my $db (keys %rs) { |
fcaf47ee |
541 | my $db_test = $t->{$db}; |
542 | next DB_TEST unless $db_test; |
5e6893d4 |
543 | |
f94672f9 |
544 | my ($r, $my_rs); |
fcaf47ee |
545 | |
546 | my $cref = sub { |
f94672f9 |
547 | my $stuff = { |
548 | ( exists $t->{select} |
549 | ? ( select => $t->{select}, as => $t->{as} ) |
550 | : ( columns => [qw(starts_at created_on skip_inflation)] ) |
551 | ) |
552 | }; |
553 | $my_rs = $rs{$db}->search($t->{search}, $stuff); |
554 | $r = $my_rs->as_query |
fcaf47ee |
555 | }; |
5e6893d4 |
556 | |
fcaf47ee |
557 | if ($db_test->{exception_like}) { |
558 | throws_ok( |
559 | sub { $cref->() }, |
560 | $db_test->{exception_like}, |
561 | "throws the expected exception ($db_test->{exception_like})", |
562 | ); |
563 | } else { |
564 | if ($db_test->{warning_like}) { |
565 | warning_like( |
566 | sub { $cref->() }, |
567 | $db_test->{warning_like}, |
568 | "issues the expected warning ($db_test->{warning_like})" |
569 | ); |
570 | } |
571 | else { |
572 | $cref->(); |
573 | } |
574 | is_same_sql_bind( |
f94672f9 |
575 | $r, |
576 | "(SELECT $db_test->{select} FROM event me WHERE $db_test->{where})", |
fcaf47ee |
577 | $db_test->{bind}, |
f94672f9 |
578 | ($t->{msg} ? "$t->{msg} ($db)" : ()) |
fcaf47ee |
579 | ); |
f94672f9 |
580 | |
581 | SKIP: { |
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}; |
585 | |
586 | my $msg = ($t->{msg} ? "$t->{msg} ($db actually pulls expected data)" : ''); |
587 | try { |
588 | is_deeply [ $my_rs->hri_dump->all ], $hri, $msg; |
589 | } catch { |
590 | ok 0, $msg . " $_"; |
591 | } |
592 | } } |
fcaf47ee |
593 | } |
594 | } |
595 | } |
6edb8b2f |
596 | |
5e6893d4 |
597 | done_testing; |