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