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