t/sqlmaker/op_dt.t

   1 use strict;
   2 use warnings;
   3
   4 use Test::More;
   5 use Test::Exception;
   6
   7 use lib qw(t/lib);
   8 use DBICTest::RunMode;
   9 use DBIC::SqlMakerTest;
  10 use DateTime;
  11 use DBIx::Class::SQLMaker::MSSQL;
  12 use Try::Tiny;
  13 use Data::Dumper::Concise;
  14
  15 use DBICTest;
  16
  17 sub unknown_col { +{ dbic_colname => '' } }
  18
  19 my %dbs_to_test = (
  20    sqlite   => 1,
  21    mssql    => 0,
  22    postgres => 0,
  23    oracle   => 0,
  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 };
  33
  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    },
  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    },
  86    ## copypasta'd for great justice
  87    postgres =>  do {
  88       my ($dsn, $user, $pass) = @ENV{map { "DBICTEST_PG_${_}" } qw/DSN USER PASS/};
  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       }
 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 {
 154          DBICTest->init_schema( no_deploy=> 1, storage_type => '::DBI::Oracle::Generic', on_connect_call => 'datetime_setup' )
 155       }
 156    },
 157 );
 158
 159 my %rs = map { $_ => $schema{$_}->resultset('Event') } keys %schema;
 160
 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 }
 168
 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
 178 sub hri_thing {
 179    return {
 180       starts_at => $_[0],
 181       created_on => $_[1],
 182       skip_inflation => $_[2]
 183    }
 184 }
 185
 186 my $date2 = $date->clone->set_day(16);
 187
 188 ## test format:
 189 ##   search => { dbic_search_code/params }
 190 ##   rdbms_name => literal_sql
 191 my @tests = (
 192
 193   {
 194     search => { 'me.created_on' => { -dt => $date } },
 195     sqlite => {
 196       select => '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     },
 201     mssql => {
 202       select => 'me.starts_at, me.created_on, me.skip_inflation',
 203       where  => 'me.created_on = ?',
 204       bind   => [[{ dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12.000' ]],
 205       hri    => [hri_thing('2010-12-12', '2010-12-14 12:12:12.000', '2019-12-12 12:12:12.000')],
 206     },
 207     mysql => {
 208       select => 'me.starts_at, me.created_on, me.skip_inflation',
 209       where  => 'me.created_on = ?',
 210       bind   => [[{ dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12' ]],
 211       hri    => [hri_thing('2010-12-12', '2010-12-14 12:12:12', '2019-12-12 12:12:12')],
 212     },
 213     postgres => {
 214       select => 'me.starts_at, me.created_on, me.skip_inflation',
 215       where  => 'me.created_on = ?',
 216       bind   => [[{ dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12' ]],
 217       hri    => [hri_thing('2010-12-12', '2010-12-14 12:12:12', '2019-12-12 12:12:12')],
 218     },
 219     oracle => {
 220       select => 'me.starts_at, me.created_on, me.skip_inflation',
 221       where  => 'me.created_on = ?',
 222       bind   => [[{ dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12' ]],
 223       hri    => [hri_thing('2010-12-12 00:00:00', '2010-12-14 12:12:12.000000', '2019-12-12 12:12:12.000000')],
 224     },
 225     msg => '-dt_now works',
 226   },
 227
 228   {
 229     search => { 'me.id' => 1 },
 230     select => [ [ -dt_year => { -ident => 'me.created_on' } ] ],
 231     as     => [ 'year' ],
 232     mssql => {
 233       select => "DATEPART(year, me.created_on)",
 234       where => "me.id = ?",
 235       bind   => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
 236       hri    => [{ year => 2010 }],
 237     },
 238     mysql => {
 239       select => "EXTRACT(YEAR FROM me.created_on)",
 240       where => "me.id = ?",
 241       bind   => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
 242       hri    => [{ year => 2010 }],
 243     },
 244     sqlite => {
 245       select => "STRFTIME('%Y', me.created_on)",
 246       where => "me.id = ?",
 247       bind   => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
 248       hri    => [{ year => 2010 }],
 249     },
 250     postgres => {
 251       select => "date_part('year', me.created_on)",
 252       where => "me.id = ?",
 253       bind   => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
 254       hri    => [{ year => 2010 }],
 255     },
 256     oracle => {
 257       select => "EXTRACT(year FROM me.created_on)",
 258       where => "me.id = ?",
 259       bind   => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
 260       hri    => [{ year => 2010 }],
 261     },
 262     msg    => '-dt_year works',
 263   },
 264
 265   {
 266     msg    => '-dt_get (year, month) works',
 267     search => { 'me.id' => 1 },
 268     select => [ [ -dt_get => [[qw(year month)], { -ident => 'me.created_on' }] ] ],
 269     as     => [ qw(year month) ],
 270     mssql => {
 271       select => "DATEPART(year, me.created_on), DATEPART(month, me.created_on)",
 272       where => "me.id = ?",
 273       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
 274       hri    => [{ year => 2010, month => 12 }],
 275     },
 276     mysql => {
 277       select => "EXTRACT(YEAR FROM me.created_on), EXTRACT(MONTH FROM me.created_on)",
 278       where => "me.id = ?",
 279       bind   => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
 280       hri    => [{ year => 2010, month => 12 }],
 281     },
 282     sqlite => {
 283       select => "STRFTIME('%Y', me.created_on), STRFTIME('%m', me.created_on)",
 284       where => "me.id = ?",
 285       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
 286       hri    => [{ year => 2010, month => 12 }],
 287     },
 288     postgres => {
 289       select => "date_part('year', me.created_on), date_part('month', me.created_on)",
 290       where => "me.id = ?",
 291       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
 292       hri    => [{ year => 2010, month => 12 }],
 293     },
 294     oracle => {
 295       select => "EXTRACT(year FROM me.created_on), EXTRACT(month FROM me.created_on)",
 296       where => "me.id = ?",
 297       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
 298       hri    => [{ year => 2010, month => 12 }],
 299     },
 300   },
 301
 302   {
 303     msg    => '-dt_month works',
 304     search => { 'me.id' => 1 },
 305     select   => [ [ -dt_month => { -ident => 'me.created_on' } ] ],
 306     as       => [ 'month' ],
 307     sqlite => {
 308       select   => "STRFTIME('%m', me.created_on)",
 309       where => "me.id = ?",
 310       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
 311       hri    => [{ month => 12 }],
 312     },
 313     mssql => {
 314       select => "DATEPART(month, me.created_on)",
 315       where => "me.id = ?",
 316       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
 317       hri    => [{ month => 12 }],
 318     },
 319     mysql => {
 320       select => "EXTRACT(MONTH FROM me.created_on)",
 321       where => "me.id = ?",
 322       bind   => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
 323       hri    => [{ month => 12 }],
 324     },
 325     postgres => {
 326       select => "date_part('month', me.created_on)",
 327       where => "me.id = ?",
 328       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
 329       hri    => [{ month => 12 }],
 330     },
 331     oracle => {
 332       select => "EXTRACT(month FROM me.created_on)",
 333       where => "me.id = ?",
 334       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
 335       hri    => [{ month => 12 }],
 336     },
 337   },
 338
 339   {
 340     msg    => '-dt_day works',
 341     search => { 'me.id' => 1 },
 342     select   => [ [ -dt_day => { -ident => 'me.created_on' } ] ],
 343     as       => [ 'day' ],
 344     sqlite => {
 345       select   => "STRFTIME('%d', me.created_on)",
 346       where => "me.id = ?",
 347       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
 348       hri    => [{ day => 14 }],
 349     },
 350     mssql => {
 351       select => "DATEPART(day, me.created_on)",
 352       where => "me.id = ?",
 353       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
 354       hri    => [{ day => 14 }],
 355     },
 356     mysql => {
 357       select => "EXTRACT(DAY FROM me.created_on)",
 358       where => "me.id = ?",
 359       bind   => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
 360       hri    => [{ day => 14 }],
 361     },
 362     postgres => {
 363       select => "date_part('day', me.created_on)",
 364       where => "me.id = ?",
 365       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
 366       hri    => [{ day => 14 }],
 367     },
 368     oracle => {
 369       select => "EXTRACT(day FROM me.created_on)",
 370       where => "me.id = ?",
 371       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
 372       hri    => [{ day => 14 }],
 373     },
 374   },
 375
 376   {
 377     msg    => '-dt_hour works',
 378     search => { 'me.id' => 1 },
 379     select   => [ [ -dt_hour => { -ident => 'me.created_on' } ] ],
 380     as       => [ 'hour' ],
 381     sqlite => {
 382       select   => "STRFTIME('%H', me.created_on)",
 383       where => "me.id = ?",
 384       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
 385       hri    => [{ hour => 12 }],
 386     },
 387     mssql => {
 388       select => "DATEPART(hour, me.created_on)",
 389       where => "me.id = ?",
 390       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
 391       hri    => [{ hour => 12 }],
 392     },
 393     mysql => {
 394       select => "EXTRACT(HOUR FROM me.created_on)",
 395       where => "me.id = ?",
 396       bind   => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
 397       hri    => [{ hour => 12 }],
 398     },
 399     postgres => {
 400       select => "date_part('hour', me.created_on)",
 401       where => "me.id = ?",
 402       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
 403       hri    => [{ hour => 12 }],
 404     },
 405     oracle => {
 406       select => "EXTRACT(hour FROM me.created_on)",
 407       where => "me.id = ?",
 408       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
 409       hri    => [{ hour => 12 }],
 410     },
 411   },
 412
 413   {
 414     msg    => '-dt_minute works',
 415     search => { 'me.id' => 1 },
 416     select   => [ [ -dt_minute => { -ident => 'me.created_on' } ] ],
 417     as       => [ 'minute' ],
 418     sqlite => {
 419       select   => "STRFTIME('%M', me.created_on)",
 420       where => "me.id = ?",
 421       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
 422       hri    => [{ minute => 12 }],
 423     },
 424     mssql => {
 425       select => "DATEPART(minute, me.created_on)",
 426       where => "me.id = ?",
 427       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
 428       hri    => [{ minute => 12 }],
 429     },
 430     mysql => {
 431       select => "EXTRACT(MINUTE FROM me.created_on)",
 432       where => "me.id = ?",
 433       bind   => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
 434       hri    => [{ minute => 12 }],
 435     },
 436     postgres => {
 437       select => "date_part('minute', me.created_on)",
 438       where => "me.id = ?",
 439       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
 440       hri    => [{ minute => 12 }],
 441     },
 442     oracle => {
 443       select => "EXTRACT(minute FROM me.created_on)",
 444       where => "me.id = ?",
 445       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
 446       hri    => [{ minute => 12 }],
 447     },
 448   },
 449
 450   {
 451     msg    => '-dt_second works',
 452     search => { 'me.id' => 1 },
 453     select   => [ [ -dt_second => { -ident => 'me.created_on' } ] ],
 454     as       => [ 'second' ],
 455     sqlite => {
 456       select   => "STRFTIME('%S', me.created_on)",
 457       where => "me.id = ?",
 458       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
 459       hri    => [{ second => 12 }],
 460     },
 461     mssql => {
 462       select => "DATEPART(second, me.created_on)",
 463       where => "me.id = ?",
 464       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
 465       hri    => [{ second => 12 }],
 466     },
 467     mysql => {
 468       select => "EXTRACT(SECOND FROM me.created_on)",
 469       where => "me.id = ?",
 470       bind   => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
 471       hri    => [{ second => 12 }],
 472     },
 473     postgres => {
 474       select => "date_part('second', me.created_on)",
 475       where => "me.id = ?",
 476       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
 477       hri    => [{ second => 12 }],
 478     },
 479     oracle => {
 480       select => "EXTRACT(second FROM me.created_on)",
 481       where => "me.id = ?",
 482       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
 483       hri    => [{ second => 12 }],
 484     },
 485   },
 486
 487   {
 488     msg    => '-dt_diff (second) works',
 489     search => { 'me.id' => 2 },
 490     select   => [ [ -dt_diff => [second => { -ident => 'me.created_on' }, \'me.skip_inflation' ] ] ],
 491     as => [ 'sec_diff' ],
 492     sqlite => {
 493       select   => "(STRFTIME('%s', me.created_on) - STRFTIME('%s', me.skip_inflation))",
 494       where => "me.id = ?",
 495       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 496       hri => [{ sec_diff => 2*24*60*60 }],
 497     },
 498     mssql => {
 499       select   => "DATEDIFF(second, me.skip_inflation, me.created_on)",
 500       where => "me.id = ?",
 501       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 502       hri => [{ sec_diff => 2*24*60*60 }],
 503     },
 504     mysql => {
 505       select   => "TIMESTAMPDIFF(SECOND, me.skip_inflation, me.created_on)",
 506       where => "me.id = ?",
 507       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 508       hri => [{ sec_diff => 2*24*60*60 }],
 509     },
 510     postgres => {
 511       select   => "date_part('EPOCH', me.created_on) - date_part('EPOCH', me.skip_inflation)",
 512       where => "me.id = ?",
 513       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 514       hri => [{ sec_diff => 2*24*60*60 }],
 515     },
 516     oracle => {
 517       select   => "TRUNC(MONTHS_BETWEEN(me.created_on, me.skip_inflation) * 31 * 24 * 60 * 60)",
 518       where => "me.id = ?",
 519       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 520       hri => [{ sec_diff => 2*24*60*60 }],
 521     },
 522   },
 523
 524   {
 525     msg    => '-dt_diff (day) works',
 526     search => { 'me.id' => 2 },
 527     select   => [ [ -dt_diff => [day => { -ident => 'me.created_on' }, \'me.skip_inflation' ] ] ],
 528     as => [ 'day_diff' ],
 529     sqlite => {
 530       select   => "(JULIANDAY(me.created_on) - JULIANDAY(me.skip_inflation))",
 531       where => "me.id = ?",
 532       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 533       hri => [{ day_diff => 2 }],
 534     },
 535     mssql => {
 536       select   => "DATEDIFF(dayofyear, me.skip_inflation, me.created_on)",
 537       where => "me.id = ?",
 538       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 539       hri => [{ day_diff => 2 }],
 540     },
 541     mysql => {
 542       select   => "TIMESTAMPDIFF(DAY, me.skip_inflation, me.created_on)",
 543       where => "me.id = ?",
 544       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 545       hri => [{ day_diff => 2 }],
 546     },
 547     postgres => {
 548       select   => "date_part('DAY', me.created_on) - date_part('DAY', me.skip_inflation)",
 549       where => "me.id = ?",
 550       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 551       hri => [{ day_diff => 2 }],
 552     },
 553     oracle => {
 554       select   => "TRUNC(MONTHS_BETWEEN(me.created_on, me.skip_inflation) * 31)",
 555       where => "me.id = ?",
 556       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 557       hri => [{ day_diff => 2 }],
 558     },
 559   },
 560
 561   {
 562     msg => '-dt_diff (year) works',
 563     search => { 'me.id' => 2 },
 564     select   => [ [ -dt_diff => [year => \'me.starts_at', { -ident => 'me.created_on' } ] ] ],
 565     as       => [ 'year' ],
 566     sqlite => {
 567       exception_like => qr/date diff not supported for part "year" with database "SQLite"/,
 568     },
 569     mssql => {
 570       select   => "DATEDIFF(year, me.created_on, me.starts_at)",
 571       where => "me.id = ?",
 572       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 573       hri    => [{ year => -1 }],
 574     },
 575     mysql => {
 576       select   => "TIMESTAMPDIFF(YEAR, me.created_on, me.starts_at)",
 577       where => "me.id = ?",
 578       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 579       hri => [{ year => -1 }],
 580     },
 581     postgres => {
 582       select   => "date_part('YEAR', me.starts_at) - date_part('YEAR', me.created_on)",
 583       where => "me.id = ?",
 584       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 585       hri    => [{ year => -1 }],
 586     },
 587     oracle => {
 588       select   => "TRUNC(MONTHS_BETWEEN(me.starts_at, me.created_on) / 12)",
 589       where => "me.id = ?",
 590       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 591       hri    => [{ year => -1 }],
 592     },
 593   },
 594
 595   {
 596     msg    => '-dt_add (year) works',
 597     search => { 'me.id' => 2 },
 598     select   => [ [ -dt_add => [year => 3, { -ident => 'me.created_on' } ] ] ],
 599     as   => [ 'date' ],
 600     sqlite => {
 601       select => "(datetime(me.created_on, ? || ' years'))",
 602       where => "me.id = ?",
 603       bind   => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 604       hri    => [{ date => '2014-12-14 12:12:12' }],
 605     },
 606     mssql => {
 607       select => "(DATEADD(year, CAST(? AS INTEGER), me.created_on))",
 608       where => "me.id = ?",
 609       bind   => [[{sqlt_datatype => 'integer'}, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 610       hri    => [{ date => '2014-12-14 12:12:12.000' }],
 611     },
 612     mysql => {
 613       select => "DATE_ADD(me.created_on, INTERVAL ? YEAR)",
 614       where => "me.id = ?",
 615       bind   => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 616       hri    => [{ date => '2014-12-14 12:12:12' }],
 617     },
 618     postgres => {
 619       select => "(me.created_on + ? * interval '1 YEAR')",
 620       where => "me.id = ?",
 621       bind   => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 622       hri    => [{ date => '2014-12-14 12:12:12' }],
 623
 624     },
 625     oracle => {
 626       select => "(me.created_on + NUMTOYMINTERVAL(?, 'year'))",
 627       where => "me.id = ?",
 628       bind   => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 629       hri    => [{ date => '2014-12-14 12:12:12.000000000' }],
 630     },
 631   },
 632
 633   {
 634     msg    => '-dt_add (month) works',
 635     search => { 'me.id' => 2 },
 636     select   => [ [ -dt_add => [month => 3, { -ident => 'me.created_on' } ] ] ],
 637     as   => [ 'date' ],
 638     sqlite => {
 639       select => "(datetime(me.created_on, ? || ' months'))",
 640       where => "me.id = ?",
 641       bind   => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 642       hri    => [{ date => '2012-03-14 12:12:12' }],
 643     },
 644     mssql => {
 645       select => "(DATEADD(month, CAST(? AS INTEGER), me.created_on))",
 646       where => "me.id = ?",
 647       bind   => [[{sqlt_datatype => 'integer'}, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 648       hri    => [{ date => '2012-03-14 12:12:12.000' }],
 649     },
 650     postgres => {
 651       select => "(me.created_on + ? * interval '1 MONTH')",
 652       where => "me.id = ?",
 653       bind   => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 654       hri    => [{ date => '2012-03-14 12:12:12' }],
 655     },
 656
 657     mysql => {
 658       select => "DATE_ADD(me.created_on, INTERVAL ? MONTH)",
 659       where => "me.id = ?",
 660       bind   => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 661       hri    => [{ date => '2012-03-14 12:12:12' }],
 662     },
 663     oracle => {
 664       select => "(me.created_on + NUMTOYMINTERVAL(?, 'month'))",
 665       where => "me.id = ?",
 666       bind   => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 667       hri    => [{ date => '2012-03-14 12:12:12.000000000' }],
 668     },
 669   },
 670
 671   {
 672     msg    => '-dt_add (day) works',
 673     search => { 'me.id' => 2 },
 674     select   => [ [ -dt_add => [day => 3, { -ident => 'me.created_on' } ] ] ],
 675     as   => [ 'date' ],
 676     sqlite => {
 677       select => "(datetime(me.created_on, ? || ' days'))",
 678       where => "me.id = ?",
 679       bind   => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 680       hri    => [{ date => '2011-12-17 12:12:12' }],
 681     },
 682     mssql => {
 683       select => "(DATEADD(dayofyear, CAST(? AS INTEGER), me.created_on))",
 684       where => "me.id = ?",
 685       bind   => [[{sqlt_datatype => 'integer'}, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 686       hri    => [{ date => '2011-12-17 12:12:12.000' }],
 687     },
 688     postgres => {
 689       select => "(me.created_on + ? * interval '1 DAY')",
 690       where => "me.id = ?",
 691       bind   => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 692       hri    => [{ date => '2011-12-17 12:12:12' }],
 693     },
 694
 695     mysql => {
 696       select => "DATE_ADD(me.created_on, INTERVAL ? DAY)",
 697       where => "me.id = ?",
 698       bind   => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 699       hri    => [{ date => '2011-12-17 12:12:12' }],
 700     },
 701     oracle => {
 702       select => "(me.created_on + NUMTODSINTERVAL(?, 'day'))",
 703       where => "me.id = ?",
 704       bind   => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 705       hri    => [{ date => '2011-12-17 12:12:12.000000000' }],
 706     },
 707   },
 708
 709   {
 710     msg    => '-dt_add (hour) works',
 711     search => { 'me.id' => 2 },
 712     select   => [ [ -dt_add => [hour => 3, { -ident => 'me.created_on' } ] ] ],
 713     as   => [ 'date' ],
 714     sqlite => {
 715       select => "(datetime(me.created_on, ? || ' hours'))",
 716       where => "me.id = ?",
 717       bind   => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 718       hri    => [{ date => '2011-12-14 15:12:12' }],
 719     },
 720     mssql => {
 721       select => "(DATEADD(hour, CAST(? AS INTEGER), me.created_on))",
 722       where => "me.id = ?",
 723       bind   => [[{sqlt_datatype => 'integer'}, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 724       hri    => [{ date => '2011-12-14 15:12:12.000' }],
 725     },
 726     postgres => {
 727       select => "(me.created_on + ? * interval '1 HOUR')",
 728       where => "me.id = ?",
 729       bind   => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 730       hri    => [{ date => '2011-12-14 15:12:12' }],
 731     },
 732     mysql => {
 733       select => "DATE_ADD(me.created_on, INTERVAL ? HOUR)",
 734       where => "me.id = ?",
 735       bind   => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 736       hri    => [{ date => '2011-12-14 15:12:12' }],
 737     },
 738     oracle => {
 739       select => "(me.created_on + NUMTODSINTERVAL(?, 'hour'))",
 740       where => "me.id = ?",
 741       bind   => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 742       hri    => [{ date => '2011-12-14 15:12:12.000000000' }],
 743     },
 744   },
 745
 746   {
 747     msg    => '-dt_add (minute) works',
 748     search => { 'me.id' => 2 },
 749     select   => [ [ -dt_add => [minute => 3, { -ident => 'me.created_on' } ] ] ],
 750     as   => [ 'date' ],
 751     sqlite => {
 752       select => "(datetime(me.created_on, ? || ' minutes'))",
 753       where => "me.id = ?",
 754       bind   => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 755       hri    => [{ date => '2011-12-14 12:15:12' }],
 756     },
 757     mssql => {
 758       select => "(DATEADD(minute, CAST(? AS INTEGER), me.created_on))",
 759       where => "me.id = ?",
 760       bind   => [[{sqlt_datatype => 'integer'}, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 761       hri    => [{ date => '2011-12-14 12:15:12.000' }],
 762     },
 763     postgres => {
 764       select => "(me.created_on + ? * interval '1 MINUTE')",
 765       where => "me.id = ?",
 766       bind   => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 767       hri    => [{ date => '2011-12-14 12:15:12' }],
 768     },
 769     mysql => {
 770       select => "DATE_ADD(me.created_on, INTERVAL ? MINUTE)",
 771       where => "me.id = ?",
 772       bind   => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 773       hri    => [{ date => '2011-12-14 12:15:12' }],
 774     },
 775     oracle => {
 776       select => "(me.created_on + NUMTODSINTERVAL(?, 'minute'))",
 777       where => "me.id = ?",
 778       bind   => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 779       hri    => [{ date => '2011-12-14 12:15:12.000000000' }],
 780     },
 781   },
 782
 783   {
 784     msg    => '-dt_add (second) works',
 785     search => { 'me.id' => 2 },
 786     select   => [ [ -dt_add => [second => 3, { -ident => 'me.created_on' } ] ] ],
 787     as   => [ 'date' ],
 788     sqlite => {
 789       select => "(datetime(me.created_on, ? || ' seconds'))",
 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     },
 794     mssql => {
 795       select => "(DATEADD(second, CAST(? AS INTEGER), me.created_on))",
 796       where => "me.id = ?",
 797       bind   => [[{sqlt_datatype => 'integer'}, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 798       hri    => [{ date => '2011-12-14 12:12:15.000' }],
 799     },
 800     postgres => {
 801       select => "(me.created_on + ? * interval '1 SECOND')",
 802       where => "me.id = ?",
 803       bind   => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 804       hri    => [{ date => '2011-12-14 12:12:15' }],
 805     },
 806     mysql => {
 807       select => "DATE_ADD(me.created_on, INTERVAL ? SECOND)",
 808       where => "me.id = ?",
 809       bind   => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 810       hri    => [{ date => '2011-12-14 12:12:15' }],
 811     },
 812     oracle => {
 813       select => "(me.created_on + NUMTODSINTERVAL(?, 'second'))",
 814       where => "me.id = ?",
 815       bind   => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 816       hri    => [{ date => '2011-12-14 12:12:15.000000000' }],
 817     },
 818   },
 819
 820   {
 821     msg    => 'nested -dt_add works',
 822     search => { 'me.id' => 2 },
 823     select   => [ [ -dt_add => [second => 3, { -dt_add => [ day => 1, { -ident => 'me.created_on' } ] } ] ] ],
 824     as       => [ 'date' ],
 825     sqlite => {
 826       select   => "(datetime((datetime(me.created_on, ? || ' days')), ? || ' seconds'))",
 827       where => "me.id = ?",
 828       bind   => [[unknown_col, 1], [unknown_col, 3 ], [{dbic_colname => 'me.id', sqlt_datatype => 'integer' }, 2]],
 829       hri    => [{ date => '2011-12-15 12:12:15' }],
 830     },
 831     mssql => {
 832       select => "(DATEADD(second, CAST(? AS INTEGER), (DATEADD(dayofyear, CAST(? AS INTEGER), me.created_on))))",
 833       where => "me.id = ?",
 834       bind   => [[{sqlt_datatype => 'integer'}, 3 ], [{sqlt_datatype => 'integer'}, 1], [{dbic_colname => 'me.id', sqlt_datatype => 'integer' }, 2]],
 835       hri    => [{ date => '2011-12-15 12:12:15.000' }],
 836     },
 837      postgres => {
 838       select => "((me.created_on + ? * interval '1 DAY') + ? * interval '1 SECOND')",
 839       where => "me.id = ?",
 840       bind   => [[unknown_col, 1], [unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 841       hri    => [{ date => '2011-12-15 12:12:15' }],
 842     },
 843     mysql => {
 844       select => "DATE_ADD(DATE_ADD(me.created_on, INTERVAL ? DAY), INTERVAL ? SECOND)",
 845       where => "me.id = ?",
 846       bind   => [[unknown_col, 1], [unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 847       hri    => [{ date => '2011-12-15 12:12:15' }],
 848     },
 849     oracle => {
 850       select => "((me.created_on + NUMTODSINTERVAL(?, 'day')) + NUMTODSINTERVAL(?, 'second'))",
 851       where => "me.id = ?",
 852       bind   => [[unknown_col, 1], [unknown_col, 3 ], [{dbic_colname => 'me.id', sqlt_datatype => 'integer' }, 2]],
 853       hri    => [{ date => '2011-12-15 12:12:15.000000000' }],
 854     },
 855   },
 856 );
 857
 858 for my $t (@tests) {
 859
 860   DB_TEST:
 861   for my $db (keys %rs) {
 862      my $db_test = $t->{$db};
 863      unless ($db_test) {
 864         ok 0, "$t->{msg} ($db not tested!)";
 865         next DB_TEST;
 866      }
 867
 868      my ($r, $my_rs);
 869
 870      my $cref = sub {
 871        my $stuff = {
 872          ( exists $t->{select}
 873            ? ( select => $t->{select}, as => $t->{as} )
 874            : ( columns => [qw(starts_at created_on skip_inflation)] )
 875          )
 876        };
 877        $my_rs = $rs{$db}->search($t->{search}, $stuff);
 878        $r = $my_rs->as_query
 879      };
 880
 881      if ($db_test->{exception_like}) {
 882        throws_ok(
 883          sub { $cref->() },
 884          $db_test->{exception_like},
 885          "throws the expected exception ($db_test->{exception_like})",
 886        );
 887      } else {
 888        if ($db_test->{warning_like}) {
 889          warning_like(
 890            sub { $cref->() },
 891            $db_test->{warning_like},
 892            "issues the expected warning ($db_test->{warning_like})"
 893          );
 894        }
 895        else {
 896          $cref->();
 897        }
 898        is_same_sql_bind(
 899          $r,
 900          "(SELECT $db_test->{select} FROM event me WHERE $db_test->{where})",
 901          $db_test->{bind},
 902          ($t->{msg} ? "$t->{msg} ($db)" : ())
 903        );
 904
 905        SKIP: {
 906        if (my $hri = $db_test->{hri}) {
 907           skip "Cannot test database we are not connected to ($db)", 1 unless $dbs_to_test{$db};
 908           skip $db_test->{skip} . " ($db)", 1 if $db_test->{skip};
 909
 910           my $msg = ($t->{msg} ? "$t->{msg} ($db actually pulls expected data)" : '');
 911           try {
 912              my $got = [ $my_rs->hri_dump->all ];
 913              my $success = is_deeply $got, $hri, $msg;
 914              unless ($success) {
 915                 warn "$db: $t->{msg} got:      " . Dumper $got;
 916                 warn "$db: $t->{msg} expected: " . Dumper $hri;
 917              }
 918           } catch {
 919              ok 0, $msg . " $_";
 920           }
 921         } }
 922      }
 923   }
 924 }
 925
 926 done_testing;