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); |
90676d46 |
8 | use DBICTest::RunMode; |
5e6893d4 |
9 | use DBIC::SqlMakerTest; |
10 | use DateTime; |
f94672f9 |
11 | use DBIx::Class::SQLMaker::MSSQL; |
12 | use Try::Tiny; |
2292bd08 |
13 | use Data::Dumper::Concise; |
5e6893d4 |
14 | |
f94672f9 |
15 | use DBICTest; |
5e6893d4 |
16 | |
553def31 |
17 | sub unknown_col { +{ dbic_colname => '' } } |
18 | |
f94672f9 |
19 | my %dbs_to_test = ( |
35621f12 |
20 | sqlite => 1, |
21 | mssql => 0, |
553def31 |
22 | postgres => 0, |
23 | oracle => 0, |
f94672f9 |
24 | ); |
25 | |
26 | my %schema = ( |
27 | sqlite => DBICTest->init_schema( no_populate => 1 ), |
28 | mssql => do { |
29 | my ($dsn, $user, $pass) = @ENV{map { "DBICTEST_MSSQL_ODBC_${_}" } qw/DSN USER PASS/}; |
30 | if ($dsn && $user) { |
31 | my $s = DBICTest::Schema->connect($dsn, $user, $pass); |
32 | try { $s->storage->ensure_connected }; |
5e6893d4 |
33 | |
f94672f9 |
34 | $s->storage->dbh_do (sub { |
35 | my ($storage, $dbh) = @_; |
36 | eval { $dbh->do("DROP TABLE event") }; |
37 | $dbh->do(<<'SQL'); |
38 | CREATE TABLE event ( |
39 | id INT IDENTITY NOT NULL, |
40 | starts_at DATE NOT NULL, |
41 | created_on DATETIME NOT NULL, |
42 | varchar_date VARCHAR(20), |
43 | varchar_datetime VARCHAR(20), |
44 | skip_inflation DATETIME, |
45 | ts_without_tz DATETIME, |
46 | |
47 | primary key(id) |
48 | ) |
49 | SQL |
50 | $dbs_to_test{mssql} = 1; |
51 | }); |
52 | $s; |
53 | } else { |
54 | DBICTest->init_schema( no_deploy=> 1, storage_type => '::DBI::MSSQL' ) |
55 | } |
56 | }, |
a53284b5 |
57 | mysql => do { |
58 | my ($dsn, $user, $pass) = @ENV{map { "DBICTEST_MYSQL_${_}" } qw/DSN USER PASS/}; |
59 | if ($dsn && $user) { |
60 | my $s = DBICTest::Schema->connect($dsn, $user, $pass); |
61 | try { $s->storage->ensure_connected }; |
62 | |
63 | $s->storage->dbh_do (sub { |
64 | my ($storage, $dbh) = @_; |
65 | eval { $dbh->do("DROP TABLE event") }; |
66 | $dbh->do(<<'SQL'); |
67 | CREATE TABLE event ( |
68 | id INT AUTO_INCREMENT NOT NULL, |
69 | starts_at DATE NOT NULL, |
70 | created_on DATETIME NOT NULL, |
71 | varchar_date VARCHAR(20), |
72 | varchar_datetime VARCHAR(20), |
73 | skip_inflation DATETIME, |
74 | ts_without_tz DATETIME, |
75 | |
76 | primary key(id) |
77 | ) |
78 | SQL |
79 | $dbs_to_test{mysql} = 1; |
80 | }); |
81 | $s; |
82 | } else { |
83 | DBICTest->init_schema( no_deploy=> 1, storage_type => '::DBI::mysql' ) |
84 | } |
85 | }, |
35621f12 |
86 | ## copypasta'd for great justice |
87 | postgres => do { |
576734c7 |
88 | my ($dsn, $user, $pass) = @ENV{map { "DBICTEST_PG_${_}" } qw/DSN USER PASS/}; |
35621f12 |
89 | if ($dsn && $user) { |
90 | my $s = DBICTest::Schema->connect($dsn, $user, $pass); |
91 | try { $s->storage->ensure_connected }; |
92 | |
93 | $s->storage->dbh_do (sub { |
94 | my ($storage, $dbh) = @_; |
95 | eval { $dbh->do("DROP TABLE event") }; |
96 | $dbh->do(<<'SQL'); |
97 | CREATE TABLE event ( |
98 | id SERIAL NOT NULL PRIMARY KEY, |
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 WITHOUT TIME ZONE |
105 | ) |
106 | SQL |
107 | $dbs_to_test{postgres} = 1; |
108 | }); |
109 | $s; |
110 | } else { |
111 | DBICTest->init_schema( no_deploy=> 1, storage_type => '::DBI::Pg' ) |
112 | } |
08c123d2 |
113 | }, |
114 | oracle => do { |
115 | my ($dsn, $user, $pass) = @ENV{map { "DBICTEST_ORA_${_}" } qw/DSN USER PASS/}; |
116 | if ($dsn && $user) { |
117 | my $s = DBICTest::Schema->connect($dsn, $user, $pass, { on_connect_call => 'datetime_setup' }); |
118 | try { $s->storage->ensure_connected }; |
119 | |
120 | $s->storage->dbh_do (sub { |
121 | my ($storage, $dbh) = @_; |
122 | eval { $dbh->do("DROP TRIGGER trq_event_id") }; |
123 | eval { $dbh->do("DROP SEQUENCE sq_event_id") }; |
124 | eval { $dbh->do("DROP TABLE event") }; |
125 | $dbh->do('CREATE SEQUENCE sq_event_id'); |
126 | $dbh->do(<<'SQL'); |
127 | CREATE TABLE event ( |
128 | id NUMBER NOT NULL, |
129 | starts_at DATE NOT NULL, |
130 | created_on TIMESTAMP NOT NULL, |
131 | varchar_date VARCHAR(20), |
132 | varchar_datetime VARCHAR(20), |
133 | skip_inflation TIMESTAMP, |
134 | ts_without_tz TIMESTAMP, |
135 | CONSTRAINT PK_EVENT PRIMARY KEY (id) |
136 | ) |
137 | SQL |
138 | $dbh->do(<<'SQL'); |
139 | CREATE TRIGGER trg_event_id |
140 | BEFORE INSERT ON event |
141 | FOR EACH ROW WHEN ( |
142 | new.id IS NULL OR new.id = 0 |
143 | ) |
144 | BEGIN |
145 | SELECT sq_event_id.nextval |
146 | INTO :new.id |
147 | FROM dual; |
148 | END; |
149 | SQL |
150 | $dbs_to_test{oracle} = 1; |
151 | }); |
152 | $s; |
153 | } else { |
553def31 |
154 | DBICTest->init_schema( no_deploy=> 1, storage_type => '::DBI::Oracle::Generic', on_connect_call => 'datetime_setup' ) |
08c123d2 |
155 | } |
156 | }, |
fcaf47ee |
157 | ); |
5e6893d4 |
158 | |
f94672f9 |
159 | my %rs = map { $_ => $schema{$_}->resultset('Event') } keys %schema; |
160 | |
7605a609 |
161 | for (grep { $schema{$_}->storage->connected } keys %rs) { |
162 | $rs{$_}->populate([ |
163 | [qw(starts_at created_on skip_inflation)], |
164 | ['2010-12-12', '2010-12-14 12:12:12', '2019-12-12 12:12:12'], |
165 | ['2010-12-12', '2011-12-14 12:12:12', '2011-12-12 12:12:12'], |
166 | ]) |
167 | } |
35621f12 |
168 | |
5e6893d4 |
169 | my $date = DateTime->new( |
170 | year => 2010, |
171 | month => 12, |
172 | day => 14, |
173 | hour => 12, |
174 | minute => 12, |
175 | second => 12, |
176 | ); |
177 | |
f94672f9 |
178 | sub hri_thing { |
179 | return { |
180 | starts_at => $_[0], |
181 | created_on => $_[1], |
182 | skip_inflation => $_[2] |
183 | } |
184 | } |
185 | |
5e6893d4 |
186 | my $date2 = $date->clone->set_day(16); |
187 | |
35621f12 |
188 | ## test format: |
189 | ## search => { dbic_search_code/params } |
190 | ## rdbms_name => literal_sql |
fcaf47ee |
191 | my @tests = ( |
a53284b5 |
192 | |
fcaf47ee |
193 | { |
8ff2414a |
194 | msg => '-dt_now works', |
f94672f9 |
195 | search => { 'me.created_on' => { -dt => $date } }, |
8ff2414a |
196 | select_sql => 'me.starts_at, me.created_on, me.skip_inflation', |
197 | where => 'me.created_on = ?', |
198 | bind => [[{ dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12' ]], |
199 | hri => [hri_thing('2010-12-12', '2010-12-14 12:12:12', '2019-12-12 12:12:12')], |
200 | sqlite => { }, |
f94672f9 |
201 | mssql => { |
553def31 |
202 | bind => [[{ dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12.000' ]], |
f94672f9 |
203 | hri => [hri_thing('2010-12-12', '2010-12-14 12:12:12.000', '2019-12-12 12:12:12.000')], |
fcaf47ee |
204 | }, |
8ff2414a |
205 | mysql => { }, |
206 | postgres => { }, |
08c123d2 |
207 | oracle => { |
08c123d2 |
208 | hri => [hri_thing('2010-12-12 00:00:00', '2010-12-14 12:12:12.000000', '2019-12-12 12:12:12.000000')], |
209 | }, |
fcaf47ee |
210 | }, |
a53284b5 |
211 | |
fcaf47ee |
212 | { |
5972c6a7 |
213 | msg => '-dt_year works', |
f94672f9 |
214 | search => { 'me.id' => 1 }, |
215 | select => [ [ -dt_year => { -ident => 'me.created_on' } ] ], |
216 | as => [ 'year' ], |
8ff2414a |
217 | where => "me.id = ?", |
218 | bind => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]], |
219 | hri => [{ year => 2010 }], |
f94672f9 |
220 | mssql => { |
221 | select => "DATEPART(year, me.created_on)", |
f94672f9 |
222 | }, |
a53284b5 |
223 | mysql => { |
224 | select => "EXTRACT(YEAR FROM me.created_on)", |
a53284b5 |
225 | }, |
fcaf47ee |
226 | sqlite => { |
f94672f9 |
227 | select => "STRFTIME('%Y', me.created_on)", |
fcaf47ee |
228 | }, |
35621f12 |
229 | postgres => { |
a963b71d |
230 | select => "date_part('year', me.created_on)", |
35621f12 |
231 | }, |
08c123d2 |
232 | oracle => { |
233 | select => "EXTRACT(year FROM me.created_on)", |
08c123d2 |
234 | }, |
5972c6a7 |
235 | }, |
236 | |
237 | { |
238 | msg => '-dt_year works with DateTime obj', |
239 | search => { 'me.id' => 1 }, |
240 | select => [ [ -dt_year => $date ] ], |
241 | as => [ 'year' ], |
8ff2414a |
242 | where => "me.id = ?", |
243 | hri => [{ year => 2010 }], |
244 | bind => [[{ sqlt_datatype => 'timestamp' } => '2010-12-14 12:12:12' ], [{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]], |
5972c6a7 |
245 | mssql => { |
246 | select => "DATEPART(year, ?)", |
5972c6a7 |
247 | bind => [[{ sqlt_datatype => 'timestamp' } => '2010-12-14 12:12:12.000' ], [{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]], |
5972c6a7 |
248 | }, |
249 | mysql => { |
250 | select => "EXTRACT(YEAR FROM ?)", |
5972c6a7 |
251 | }, |
252 | sqlite => { |
253 | select => "STRFTIME('%Y', ?)", |
5972c6a7 |
254 | }, |
255 | postgres => { |
256 | select => "date_part('year', ?)", |
5972c6a7 |
257 | }, |
258 | oracle => { |
259 | select => "EXTRACT(year FROM ?)", |
5972c6a7 |
260 | }, |
fcaf47ee |
261 | }, |
553def31 |
262 | |
fcaf47ee |
263 | { |
553def31 |
264 | msg => '-dt_get (year, month) works', |
f94672f9 |
265 | search => { 'me.id' => 1 }, |
464a9709 |
266 | select => [ [ -dt_get => [[qw(year month)], { -ident => 'me.created_on' }] ] ], |
267 | as => [ qw(year month) ], |
8ff2414a |
268 | where => "me.id = ?", |
269 | bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]], |
270 | hri => [{ year => 2010, month => 12 }], |
464a9709 |
271 | mssql => { |
272 | select => "DATEPART(year, me.created_on), DATEPART(month, me.created_on)", |
464a9709 |
273 | }, |
a53284b5 |
274 | mysql => { |
275 | select => "EXTRACT(YEAR FROM me.created_on), EXTRACT(MONTH FROM me.created_on)", |
a53284b5 |
276 | }, |
464a9709 |
277 | sqlite => { |
278 | select => "STRFTIME('%Y', me.created_on), STRFTIME('%m', me.created_on)", |
464a9709 |
279 | }, |
35621f12 |
280 | postgres => { |
a963b71d |
281 | select => "date_part('year', me.created_on), date_part('month', me.created_on)", |
35621f12 |
282 | }, |
08c123d2 |
283 | oracle => { |
284 | select => "EXTRACT(year FROM me.created_on), EXTRACT(month FROM me.created_on)", |
08c123d2 |
285 | }, |
464a9709 |
286 | }, |
553def31 |
287 | |
464a9709 |
288 | { |
553def31 |
289 | msg => '-dt_month works', |
464a9709 |
290 | search => { 'me.id' => 1 }, |
8ff2414a |
291 | select => [ [ -dt_month => { -ident => 'me.created_on' } ] ], |
292 | as => [ 'month' ], |
293 | where => "me.id = ?", |
294 | bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]], |
295 | hri => [{ month => 12 }], |
fcaf47ee |
296 | sqlite => { |
f94672f9 |
297 | select => "STRFTIME('%m', me.created_on)", |
f94672f9 |
298 | }, |
299 | mssql => { |
300 | select => "DATEPART(month, me.created_on)", |
fcaf47ee |
301 | }, |
a53284b5 |
302 | mysql => { |
303 | select => "EXTRACT(MONTH FROM me.created_on)", |
a53284b5 |
304 | }, |
35621f12 |
305 | postgres => { |
a963b71d |
306 | select => "date_part('month', me.created_on)", |
08c123d2 |
307 | }, |
308 | oracle => { |
309 | select => "EXTRACT(month FROM me.created_on)", |
35621f12 |
310 | }, |
fcaf47ee |
311 | }, |
553def31 |
312 | |
fcaf47ee |
313 | { |
553def31 |
314 | msg => '-dt_day works', |
f94672f9 |
315 | search => { 'me.id' => 1 }, |
8ff2414a |
316 | select => [ [ -dt_day => { -ident => 'me.created_on' } ] ], |
317 | as => [ 'day' ], |
318 | where => "me.id = ?", |
319 | bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]], |
320 | hri => [{ day => 14 }], |
fcaf47ee |
321 | sqlite => { |
f94672f9 |
322 | select => "STRFTIME('%d', me.created_on)", |
f94672f9 |
323 | }, |
324 | mssql => { |
325 | select => "DATEPART(day, me.created_on)", |
fcaf47ee |
326 | }, |
a53284b5 |
327 | mysql => { |
328 | select => "EXTRACT(DAY FROM me.created_on)", |
a53284b5 |
329 | }, |
35621f12 |
330 | postgres => { |
a963b71d |
331 | select => "date_part('day', me.created_on)", |
08c123d2 |
332 | }, |
333 | oracle => { |
334 | select => "EXTRACT(day FROM me.created_on)", |
35621f12 |
335 | }, |
fcaf47ee |
336 | }, |
553def31 |
337 | |
fcaf47ee |
338 | { |
553def31 |
339 | msg => '-dt_hour works', |
f94672f9 |
340 | search => { 'me.id' => 1 }, |
8ff2414a |
341 | select => [ [ -dt_hour => { -ident => 'me.created_on' } ] ], |
342 | as => [ 'hour' ], |
343 | where => "me.id = ?", |
344 | bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]], |
345 | hri => [{ hour => 12 }], |
fcaf47ee |
346 | sqlite => { |
f94672f9 |
347 | select => "STRFTIME('%H', me.created_on)", |
f94672f9 |
348 | }, |
349 | mssql => { |
350 | select => "DATEPART(hour, me.created_on)", |
fcaf47ee |
351 | }, |
a53284b5 |
352 | mysql => { |
353 | select => "EXTRACT(HOUR FROM me.created_on)", |
a53284b5 |
354 | }, |
35621f12 |
355 | postgres => { |
a963b71d |
356 | select => "date_part('hour', me.created_on)", |
35621f12 |
357 | }, |
08c123d2 |
358 | oracle => { |
359 | select => "EXTRACT(hour FROM me.created_on)", |
08c123d2 |
360 | }, |
fcaf47ee |
361 | }, |
553def31 |
362 | |
fcaf47ee |
363 | { |
553def31 |
364 | msg => '-dt_minute works', |
f94672f9 |
365 | search => { 'me.id' => 1 }, |
8ff2414a |
366 | select => [ [ -dt_minute => { -ident => 'me.created_on' } ] ], |
367 | as => [ 'minute' ], |
368 | where => "me.id = ?", |
369 | bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]], |
370 | hri => [{ minute => 12 }], |
fcaf47ee |
371 | sqlite => { |
f94672f9 |
372 | select => "STRFTIME('%M', me.created_on)", |
f94672f9 |
373 | }, |
374 | mssql => { |
375 | select => "DATEPART(minute, me.created_on)", |
fcaf47ee |
376 | }, |
a53284b5 |
377 | mysql => { |
378 | select => "EXTRACT(MINUTE FROM me.created_on)", |
a53284b5 |
379 | }, |
35621f12 |
380 | postgres => { |
a963b71d |
381 | select => "date_part('minute', me.created_on)", |
35621f12 |
382 | }, |
08c123d2 |
383 | oracle => { |
384 | select => "EXTRACT(minute FROM me.created_on)", |
08c123d2 |
385 | }, |
fcaf47ee |
386 | }, |
553def31 |
387 | |
fcaf47ee |
388 | { |
553def31 |
389 | msg => '-dt_second works', |
f94672f9 |
390 | search => { 'me.id' => 1 }, |
8ff2414a |
391 | select => [ [ -dt_second => { -ident => 'me.created_on' } ] ], |
392 | as => [ 'second' ], |
393 | where => "me.id = ?", |
394 | bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]], |
395 | hri => [{ second => 12 }], |
fcaf47ee |
396 | sqlite => { |
f94672f9 |
397 | select => "STRFTIME('%S', me.created_on)", |
f94672f9 |
398 | }, |
399 | mssql => { |
400 | select => "DATEPART(second, me.created_on)", |
fcaf47ee |
401 | }, |
a53284b5 |
402 | mysql => { |
403 | select => "EXTRACT(SECOND FROM me.created_on)", |
a53284b5 |
404 | }, |
35621f12 |
405 | postgres => { |
a963b71d |
406 | select => "date_part('second', me.created_on)", |
08c123d2 |
407 | }, |
408 | oracle => { |
409 | select => "EXTRACT(second FROM me.created_on)", |
35621f12 |
410 | }, |
fcaf47ee |
411 | }, |
553def31 |
412 | |
fcaf47ee |
413 | { |
553def31 |
414 | msg => '-dt_diff (second) works', |
f94672f9 |
415 | search => { 'me.id' => 2 }, |
8ff2414a |
416 | select => [ [ -dt_diff => [second => { -ident => 'me.created_on' }, \'me.skip_inflation' ] ] ], |
417 | as => [ 'sec_diff' ], |
418 | where => "me.id = ?", |
419 | bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]], |
420 | hri => [{ sec_diff => 2*24*60*60 }], |
fcaf47ee |
421 | sqlite => { |
f94672f9 |
422 | select => "(STRFTIME('%s', me.created_on) - STRFTIME('%s', me.skip_inflation))", |
f94672f9 |
423 | }, |
424 | mssql => { |
425 | select => "DATEDIFF(second, me.skip_inflation, me.created_on)", |
fcaf47ee |
426 | }, |
a53284b5 |
427 | mysql => { |
428 | select => "TIMESTAMPDIFF(SECOND, me.skip_inflation, me.created_on)", |
a53284b5 |
429 | }, |
35621f12 |
430 | postgres => { |
a963b71d |
431 | select => "date_part('EPOCH', me.created_on) - date_part('EPOCH', me.skip_inflation)", |
35621f12 |
432 | }, |
08c123d2 |
433 | oracle => { |
434 | select => "TRUNC(MONTHS_BETWEEN(me.created_on, me.skip_inflation) * 31 * 24 * 60 * 60)", |
08c123d2 |
435 | }, |
fcaf47ee |
436 | }, |
f94672f9 |
437 | |
fcaf47ee |
438 | { |
553def31 |
439 | msg => '-dt_diff (day) works', |
f94672f9 |
440 | search => { 'me.id' => 2 }, |
8ff2414a |
441 | select => [ [ -dt_diff => [day => { -ident => 'me.created_on' }, \'me.skip_inflation' ] ] ], |
442 | as => [ 'day_diff' ], |
443 | where => "me.id = ?", |
444 | bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]], |
445 | hri => [{ day_diff => 2 }], |
fcaf47ee |
446 | sqlite => { |
f94672f9 |
447 | select => "(JULIANDAY(me.created_on) - JULIANDAY(me.skip_inflation))", |
f94672f9 |
448 | }, |
449 | mssql => { |
450 | select => "DATEDIFF(dayofyear, me.skip_inflation, me.created_on)", |
fcaf47ee |
451 | }, |
a53284b5 |
452 | mysql => { |
453 | select => "TIMESTAMPDIFF(DAY, me.skip_inflation, me.created_on)", |
a53284b5 |
454 | }, |
35621f12 |
455 | postgres => { |
a963b71d |
456 | select => "date_part('DAY', me.created_on) - date_part('DAY', me.skip_inflation)", |
35621f12 |
457 | }, |
08c123d2 |
458 | oracle => { |
459 | select => "TRUNC(MONTHS_BETWEEN(me.created_on, me.skip_inflation) * 31)", |
08c123d2 |
460 | }, |
fcaf47ee |
461 | }, |
f94672f9 |
462 | |
fcaf47ee |
463 | { |
8ff2414a |
464 | msg => '-dt_diff (year) works', |
0e2e56cc |
465 | search => { 'me.id' => 2 }, |
8ff2414a |
466 | select => [ [ -dt_diff => [year => \'me.starts_at', { -ident => 'me.created_on' } ] ] ], |
467 | as => [ 'year' ], |
468 | where => "me.id = ?", |
469 | bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]], |
470 | hri => [{ year => -1 }], |
0e2e56cc |
471 | sqlite => { |
472 | exception_like => qr/date diff not supported for part "year" with database "SQLite"/, |
473 | }, |
474 | mssql => { |
475 | select => "DATEDIFF(year, me.created_on, me.starts_at)", |
0e2e56cc |
476 | }, |
a53284b5 |
477 | mysql => { |
478 | select => "TIMESTAMPDIFF(YEAR, me.created_on, me.starts_at)", |
a53284b5 |
479 | }, |
a963b71d |
480 | postgres => { |
481 | select => "date_part('YEAR', me.starts_at) - date_part('YEAR', me.created_on)", |
a963b71d |
482 | }, |
0e2e56cc |
483 | oracle => { |
484 | select => "TRUNC(MONTHS_BETWEEN(me.starts_at, me.created_on) / 12)", |
0e2e56cc |
485 | }, |
486 | }, |
487 | |
488 | { |
08c123d2 |
489 | msg => '-dt_add (year) works', |
f94672f9 |
490 | search => { 'me.id' => 2 }, |
8ff2414a |
491 | select => [ [ -dt_add => [year => 3, { -ident => 'me.created_on' } ] ] ], |
492 | as => [ 'date' ], |
493 | where => "me.id = ?", |
494 | bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]], |
495 | hri => [{ date => '2014-12-14 12:12:12' }], |
fcaf47ee |
496 | sqlite => { |
f94672f9 |
497 | select => "(datetime(me.created_on, ? || ' years'))", |
f94672f9 |
498 | }, |
499 | mssql => { |
90676d46 |
500 | select => "(DATEADD(year, CAST(? AS INTEGER), me.created_on))", |
90676d46 |
501 | bind => [[{sqlt_datatype => 'integer'}, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]], |
f94672f9 |
502 | hri => [{ date => '2014-12-14 12:12:12.000' }], |
fcaf47ee |
503 | }, |
a53284b5 |
504 | mysql => { |
505 | select => "DATE_ADD(me.created_on, INTERVAL ? YEAR)", |
a53284b5 |
506 | }, |
a963b71d |
507 | postgres => { |
508 | select => "(me.created_on + ? * interval '1 YEAR')", |
a963b71d |
509 | }, |
08c123d2 |
510 | oracle => { |
511 | select => "(me.created_on + NUMTOYMINTERVAL(?, 'year'))", |
08c123d2 |
512 | hri => [{ date => '2014-12-14 12:12:12.000000000' }], |
513 | }, |
fcaf47ee |
514 | }, |
f94672f9 |
515 | |
fcaf47ee |
516 | { |
08c123d2 |
517 | msg => '-dt_add (month) works', |
f94672f9 |
518 | search => { 'me.id' => 2 }, |
8ff2414a |
519 | select => [ [ -dt_add => [month => 3, { -ident => 'me.created_on' } ] ] ], |
520 | as => [ 'date' ], |
521 | where => "me.id = ?", |
522 | bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]], |
523 | hri => [{ date => '2012-03-14 12:12:12' }], |
fcaf47ee |
524 | sqlite => { |
f94672f9 |
525 | select => "(datetime(me.created_on, ? || ' months'))", |
f94672f9 |
526 | }, |
527 | mssql => { |
90676d46 |
528 | select => "(DATEADD(month, CAST(? AS INTEGER), me.created_on))", |
90676d46 |
529 | bind => [[{sqlt_datatype => 'integer'}, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]], |
f94672f9 |
530 | hri => [{ date => '2012-03-14 12:12:12.000' }], |
fcaf47ee |
531 | }, |
a963b71d |
532 | postgres => { |
533 | select => "(me.created_on + ? * interval '1 MONTH')", |
a963b71d |
534 | }, |
535 | |
a53284b5 |
536 | mysql => { |
537 | select => "DATE_ADD(me.created_on, INTERVAL ? MONTH)", |
a53284b5 |
538 | }, |
08c123d2 |
539 | oracle => { |
540 | select => "(me.created_on + NUMTOYMINTERVAL(?, 'month'))", |
08c123d2 |
541 | hri => [{ date => '2012-03-14 12:12:12.000000000' }], |
542 | }, |
fcaf47ee |
543 | }, |
f94672f9 |
544 | |
fcaf47ee |
545 | { |
08c123d2 |
546 | msg => '-dt_add (day) works', |
f94672f9 |
547 | search => { 'me.id' => 2 }, |
8ff2414a |
548 | select => [ [ -dt_add => [day => 3, { -ident => 'me.created_on' } ] ] ], |
549 | as => [ 'date' ], |
550 | where => "me.id = ?", |
551 | bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]], |
552 | hri => [{ date => '2011-12-17 12:12:12' }], |
fcaf47ee |
553 | sqlite => { |
f94672f9 |
554 | select => "(datetime(me.created_on, ? || ' days'))", |
f94672f9 |
555 | }, |
556 | mssql => { |
90676d46 |
557 | select => "(DATEADD(dayofyear, CAST(? AS INTEGER), me.created_on))", |
90676d46 |
558 | bind => [[{sqlt_datatype => 'integer'}, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]], |
f94672f9 |
559 | hri => [{ date => '2011-12-17 12:12:12.000' }], |
fcaf47ee |
560 | }, |
a963b71d |
561 | postgres => { |
562 | select => "(me.created_on + ? * interval '1 DAY')", |
a963b71d |
563 | }, |
a53284b5 |
564 | mysql => { |
565 | select => "DATE_ADD(me.created_on, INTERVAL ? DAY)", |
a53284b5 |
566 | }, |
08c123d2 |
567 | oracle => { |
568 | select => "(me.created_on + NUMTODSINTERVAL(?, 'day'))", |
08c123d2 |
569 | hri => [{ date => '2011-12-17 12:12:12.000000000' }], |
570 | }, |
fcaf47ee |
571 | }, |
f94672f9 |
572 | |
fcaf47ee |
573 | { |
08c123d2 |
574 | msg => '-dt_add (hour) works', |
f94672f9 |
575 | search => { 'me.id' => 2 }, |
8ff2414a |
576 | select => [ [ -dt_add => [hour => 3, { -ident => 'me.created_on' } ] ] ], |
577 | as => [ 'date' ], |
578 | where => "me.id = ?", |
579 | bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]], |
580 | hri => [{ date => '2011-12-14 15:12:12' }], |
fcaf47ee |
581 | sqlite => { |
f94672f9 |
582 | select => "(datetime(me.created_on, ? || ' hours'))", |
f94672f9 |
583 | }, |
584 | mssql => { |
90676d46 |
585 | select => "(DATEADD(hour, CAST(? AS INTEGER), me.created_on))", |
90676d46 |
586 | bind => [[{sqlt_datatype => 'integer'}, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]], |
f94672f9 |
587 | hri => [{ date => '2011-12-14 15:12:12.000' }], |
fcaf47ee |
588 | }, |
a963b71d |
589 | postgres => { |
590 | select => "(me.created_on + ? * interval '1 HOUR')", |
a963b71d |
591 | }, |
a53284b5 |
592 | mysql => { |
593 | select => "DATE_ADD(me.created_on, INTERVAL ? HOUR)", |
a53284b5 |
594 | }, |
08c123d2 |
595 | oracle => { |
596 | select => "(me.created_on + NUMTODSINTERVAL(?, 'hour'))", |
08c123d2 |
597 | hri => [{ date => '2011-12-14 15:12:12.000000000' }], |
598 | }, |
fcaf47ee |
599 | }, |
f94672f9 |
600 | |
fcaf47ee |
601 | { |
08c123d2 |
602 | msg => '-dt_add (minute) works', |
f94672f9 |
603 | search => { 'me.id' => 2 }, |
8ff2414a |
604 | select => [ [ -dt_add => [minute => 3, { -ident => 'me.created_on' } ] ] ], |
605 | as => [ 'date' ], |
606 | where => "me.id = ?", |
607 | bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]], |
608 | hri => [{ date => '2011-12-14 12:15:12' }], |
fcaf47ee |
609 | sqlite => { |
f94672f9 |
610 | select => "(datetime(me.created_on, ? || ' minutes'))", |
f94672f9 |
611 | }, |
612 | mssql => { |
90676d46 |
613 | select => "(DATEADD(minute, CAST(? AS INTEGER), me.created_on))", |
90676d46 |
614 | bind => [[{sqlt_datatype => 'integer'}, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]], |
f94672f9 |
615 | hri => [{ date => '2011-12-14 12:15:12.000' }], |
fcaf47ee |
616 | }, |
a963b71d |
617 | postgres => { |
618 | select => "(me.created_on + ? * interval '1 MINUTE')", |
a963b71d |
619 | }, |
a53284b5 |
620 | mysql => { |
621 | select => "DATE_ADD(me.created_on, INTERVAL ? MINUTE)", |
a53284b5 |
622 | }, |
08c123d2 |
623 | oracle => { |
624 | select => "(me.created_on + NUMTODSINTERVAL(?, 'minute'))", |
08c123d2 |
625 | hri => [{ date => '2011-12-14 12:15:12.000000000' }], |
626 | }, |
fcaf47ee |
627 | }, |
f94672f9 |
628 | |
fcaf47ee |
629 | { |
08c123d2 |
630 | msg => '-dt_add (second) works', |
f94672f9 |
631 | search => { 'me.id' => 2 }, |
8ff2414a |
632 | select => [ [ -dt_add => [second => 3, { -ident => 'me.created_on' } ] ] ], |
633 | as => [ 'date' ], |
634 | where => "me.id = ?", |
635 | bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]], |
636 | hri => [{ date => '2011-12-14 12:12:15' }], |
fcaf47ee |
637 | sqlite => { |
f94672f9 |
638 | select => "(datetime(me.created_on, ? || ' seconds'))", |
f94672f9 |
639 | }, |
640 | mssql => { |
90676d46 |
641 | select => "(DATEADD(second, CAST(? AS INTEGER), me.created_on))", |
90676d46 |
642 | bind => [[{sqlt_datatype => 'integer'}, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]], |
f94672f9 |
643 | hri => [{ date => '2011-12-14 12:12:15.000' }], |
fcaf47ee |
644 | }, |
a963b71d |
645 | postgres => { |
646 | select => "(me.created_on + ? * interval '1 SECOND')", |
a963b71d |
647 | }, |
a53284b5 |
648 | mysql => { |
649 | select => "DATE_ADD(me.created_on, INTERVAL ? SECOND)", |
a53284b5 |
650 | }, |
08c123d2 |
651 | oracle => { |
652 | select => "(me.created_on + NUMTODSINTERVAL(?, 'second'))", |
08c123d2 |
653 | hri => [{ date => '2011-12-14 12:12:15.000000000' }], |
654 | }, |
fcaf47ee |
655 | }, |
f94672f9 |
656 | |
fcaf47ee |
657 | { |
08c123d2 |
658 | msg => 'nested -dt_add works', |
f94672f9 |
659 | search => { 'me.id' => 2 }, |
8ff2414a |
660 | select => [ [ -dt_add => [second => 3, { -dt_add => [ day => 1, { -ident => 'me.created_on' } ] } ] ] ], |
661 | as => [ 'date' ], |
662 | where => "me.id = ?", |
663 | hri => [{ date => '2011-12-15 12:12:15' }], |
664 | bind => [[unknown_col, 1], [unknown_col, 3 ], [{dbic_colname => 'me.id', sqlt_datatype => 'integer' }, 2]], |
f94672f9 |
665 | sqlite => { |
666 | select => "(datetime((datetime(me.created_on, ? || ' days')), ? || ' seconds'))", |
f94672f9 |
667 | }, |
668 | mssql => { |
90676d46 |
669 | select => "(DATEADD(second, CAST(? AS INTEGER), (DATEADD(dayofyear, CAST(? AS INTEGER), me.created_on))))", |
90676d46 |
670 | bind => [[{sqlt_datatype => 'integer'}, 3 ], [{sqlt_datatype => 'integer'}, 1], [{dbic_colname => 'me.id', sqlt_datatype => 'integer' }, 2]], |
f94672f9 |
671 | hri => [{ date => '2011-12-15 12:12:15.000' }], |
f94672f9 |
672 | }, |
a963b71d |
673 | postgres => { |
674 | select => "((me.created_on + ? * interval '1 DAY') + ? * interval '1 SECOND')", |
a963b71d |
675 | }, |
a53284b5 |
676 | mysql => { |
677 | select => "DATE_ADD(DATE_ADD(me.created_on, INTERVAL ? DAY), INTERVAL ? SECOND)", |
a53284b5 |
678 | }, |
08c123d2 |
679 | oracle => { |
680 | select => "((me.created_on + NUMTODSINTERVAL(?, 'day')) + NUMTODSINTERVAL(?, 'second'))", |
08c123d2 |
681 | hri => [{ date => '2011-12-15 12:12:15.000000000' }], |
682 | }, |
f94672f9 |
683 | }, |
69320ddf |
684 | |
685 | { |
8ff2414a |
686 | msg => '-dt_before works', |
687 | search => { 'me.created_on' => { -dt_before => '2011-12-14 12:12:12' } }, |
69320ddf |
688 | select => [ [ -ident => 'me.created_on' ] ], |
689 | as => [ 'date' ], |
8ff2414a |
690 | select_sql => "me.created_on", |
691 | where => "me.created_on < ?", |
692 | bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2011-12-14 12:12:12']], |
693 | hri => [{ date => '2010-12-14 12:12:12' }], |
694 | sqlite => { }, |
695 | postgres => { }, |
696 | mysql => { }, |
697 | mssql => { |
698 | hri => [{ date => '2010-12-14 12:12:12.000' }], |
699 | }, |
700 | oracle => { |
701 | hri => [{ date => '2010-12-14 12:12:12.000000' }], |
69320ddf |
702 | }, |
703 | }, |
704 | |
705 | { |
8ff2414a |
706 | msg => '-dt_on_or_before works', |
707 | search => { 'me.created_on' => { -dt_on_or_before => '2011-12-14 12:12:12' } }, |
69320ddf |
708 | select => [ [ -ident => 'me.created_on' ] ], |
709 | as => [ 'date' ], |
8ff2414a |
710 | select_sql => "me.created_on", |
711 | where => "me.created_on <= ?", |
712 | bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2011-12-14 12:12:12']], |
713 | hri => [{ date => '2010-12-14 12:12:12' }, { date => '2011-12-14 12:12:12' }], |
714 | sqlite => { }, |
715 | postgres => { }, |
716 | mysql => { }, |
717 | mssql => { |
718 | hri => [{ date => '2010-12-14 12:12:12.000' }, { date => '2011-12-14 12:12:12.000' }], |
719 | }, |
720 | oracle => { |
721 | hri => [{ date => '2010-12-14 12:12:12.000000' }, { date => '2011-12-14 12:12:12.000000' }], |
69320ddf |
722 | }, |
723 | }, |
724 | |
725 | { |
8ff2414a |
726 | msg => '-dt_after works', |
727 | search => { 'me.created_on' => { -dt_after => '2010-12-14 12:12:12' } }, |
69320ddf |
728 | select => [ [ -ident => 'me.created_on' ] ], |
729 | as => [ 'date' ], |
8ff2414a |
730 | select_sql => "me.created_on", |
731 | where => "me.created_on > ?", |
732 | bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12']], |
733 | hri => [{ date => '2011-12-14 12:12:12' }], |
734 | sqlite => { }, |
735 | postgres => { }, |
736 | mysql => { }, |
737 | mssql => { |
738 | hri => [{ date => '2011-12-14 12:12:12.000' }], |
739 | }, |
740 | oracle => { |
741 | hri => [{ date => '2011-12-14 12:12:12.000000' }], |
69320ddf |
742 | }, |
743 | }, |
744 | |
745 | { |
8ff2414a |
746 | msg => '-dt_on_or_after works', |
747 | search => { 'me.created_on' => { -dt_on_or_after => '2010-12-14 12:12:12' } }, |
69320ddf |
748 | select => [ [ -ident => 'me.created_on' ] ], |
749 | as => [ 'date' ], |
8ff2414a |
750 | select_sql => "me.created_on", |
751 | where => "me.created_on >= ?", |
752 | bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12']], |
753 | hri => [{ date => '2010-12-14 12:12:12' }, { date => '2011-12-14 12:12:12' }], |
754 | sqlite => { }, |
755 | postgres => { }, |
756 | mysql => { }, |
757 | mssql => { |
758 | hri => [{ date => '2010-12-14 12:12:12.000' }, { date => '2011-12-14 12:12:12.000' }], |
759 | }, |
760 | oracle => { |
761 | hri => [{ date => '2010-12-14 12:12:12.000000' }, { date => '2011-12-14 12:12:12.000000' }], |
69320ddf |
762 | }, |
763 | }, |
764 | |
fcaf47ee |
765 | ); |
5a44a8ec |
766 | |
fcaf47ee |
767 | for my $t (@tests) { |
5a44a8ec |
768 | |
fcaf47ee |
769 | DB_TEST: |
f94672f9 |
770 | for my $db (keys %rs) { |
fcaf47ee |
771 | my $db_test = $t->{$db}; |
a53284b5 |
772 | unless ($db_test) { |
773 | ok 0, "$t->{msg} ($db not tested!)"; |
774 | next DB_TEST; |
775 | } |
5e6893d4 |
776 | |
f94672f9 |
777 | my ($r, $my_rs); |
fcaf47ee |
778 | |
779 | my $cref = sub { |
f94672f9 |
780 | my $stuff = { |
781 | ( exists $t->{select} |
782 | ? ( select => $t->{select}, as => $t->{as} ) |
783 | : ( columns => [qw(starts_at created_on skip_inflation)] ) |
784 | ) |
785 | }; |
786 | $my_rs = $rs{$db}->search($t->{search}, $stuff); |
787 | $r = $my_rs->as_query |
fcaf47ee |
788 | }; |
5e6893d4 |
789 | |
fcaf47ee |
790 | if ($db_test->{exception_like}) { |
791 | throws_ok( |
792 | sub { $cref->() }, |
793 | $db_test->{exception_like}, |
794 | "throws the expected exception ($db_test->{exception_like})", |
795 | ); |
796 | } else { |
797 | if ($db_test->{warning_like}) { |
798 | warning_like( |
799 | sub { $cref->() }, |
800 | $db_test->{warning_like}, |
801 | "issues the expected warning ($db_test->{warning_like})" |
802 | ); |
803 | } |
804 | else { |
805 | $cref->(); |
806 | } |
807 | is_same_sql_bind( |
f94672f9 |
808 | $r, |
8ff2414a |
809 | '(SELECT ' . ($db_test->{select} || $t->{select_sql}) . ' FROM event me WHERE ' . ($db_test->{where} || $t->{where}) . ')', |
810 | $db_test->{bind} || $t->{bind}, |
f94672f9 |
811 | ($t->{msg} ? "$t->{msg} ($db)" : ()) |
fcaf47ee |
812 | ); |
f94672f9 |
813 | |
814 | SKIP: { |
8ff2414a |
815 | if (my $hri = $db_test->{hri} || $t->{hri}) { |
f94672f9 |
816 | skip "Cannot test database we are not connected to ($db)", 1 unless $dbs_to_test{$db}; |
817 | skip $db_test->{skip} . " ($db)", 1 if $db_test->{skip}; |
818 | |
819 | my $msg = ($t->{msg} ? "$t->{msg} ($db actually pulls expected data)" : ''); |
820 | try { |
2292bd08 |
821 | my $got = [ $my_rs->hri_dump->all ]; |
822 | my $success = is_deeply $got, $hri, $msg; |
823 | unless ($success) { |
824 | warn "$db: $t->{msg} got: " . Dumper $got; |
825 | warn "$db: $t->{msg} expected: " . Dumper $hri; |
826 | } |
f94672f9 |
827 | } catch { |
828 | ok 0, $msg . " $_"; |
829 | } |
830 | } } |
fcaf47ee |
831 | } |
832 | } |
833 | } |
6edb8b2f |
834 | |
5e6893d4 |
835 | done_testing; |