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 DBIC::SqlMakerTest;
   9 use DateTime;
  10 use DBIx::Class::SQLMaker::MSSQL;
  11 use Try::Tiny;
  12 use Data::Dumper::Concise;
  13
  14 use DBICTest;
  15
  16 sub unknown_col { +{ dbic_colname => '' } }
  17
  18 my %dbs_to_test = (
  19    sqlite   => 1,
  20    mssql    => 0,
  21    postgres => 0,
  22    oracle   => 0,
  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 };
  32
  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    },
  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    },
  85    ## copypasta'd for great justice
  86    postgres =>  do {
  87       my ($dsn, $user, $pass) = @ENV{map { "DBICTEST_PG_${_}" } qw/DSN USER PASS/};
  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       }
 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 {
 153          DBICTest->init_schema( no_deploy=> 1, storage_type => '::DBI::Oracle::Generic', on_connect_call => 'datetime_setup' )
 154       }
 155    },
 156 );
 157
 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
 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
 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
 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;
 189
 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
 199 sub hri_thing {
 200    return {
 201       starts_at => $_[0],
 202       created_on => $_[1],
 203       skip_inflation => $_[2]
 204    }
 205 }
 206
 207 my $date2 = $date->clone->set_day(16);
 208
 209 ## test format:
 210 ##   search => { dbic_search_code/params }
 211 ##   rdbms_name => literal_sql
 212 my @tests = (
 213
 214   {
 215     search => { 'me.created_on' => { -dt => $date } },
 216     sqlite => {
 217       select => 'me.starts_at, me.created_on, me.skip_inflation',
 218       where  => 'me.created_on = ?',
 219       bind   => [[{ dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12' ]],
 220       hri    => [hri_thing('2010-12-12', '2010-12-14 12:12:12', '2019-12-12 12:12:12')],
 221     },
 222     mssql => {
 223       select => 'me.starts_at, me.created_on, me.skip_inflation',
 224       where  => 'me.created_on = ?',
 225       bind   => [[{ dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12.000' ]],
 226       hri    => [hri_thing('2010-12-12', '2010-12-14 12:12:12.000', '2019-12-12 12:12:12.000')],
 227     },
 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     },
 234     postgres => {
 235       select => 'me.starts_at, me.created_on, me.skip_inflation',
 236       where  => 'me.created_on = ?',
 237       bind   => [[{ dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12' ]],
 238       hri    => [hri_thing('2010-12-12', '2010-12-14 12:12:12', '2019-12-12 12:12:12')],
 239     },
 240     oracle => {
 241       select => 'me.starts_at, me.created_on, me.skip_inflation',
 242       where  => 'me.created_on = ?',
 243       bind   => [[{ dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12' ]],
 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     },
 246     msg => '-dt_now works',
 247   },
 248
 249   {
 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 = ?",
 256       bind   => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
 257       hri    => [{ year => 2010 }],
 258     },
 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     },
 265     sqlite => {
 266       select => "STRFTIME('%Y', me.created_on)",
 267       where => "me.id = ?",
 268       bind   => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
 269       hri    => [{ year => 2010 }],
 270     },
 271     postgres => {
 272       select => "EXTRACT(year FROM me.created_on)",
 273       where => "me.id = ?",
 274       bind   => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
 275       hri    => [{ year => 2010 }],
 276     },
 277     oracle => {
 278       select => "EXTRACT(year FROM me.created_on)",
 279       where => "me.id = ?",
 280       bind   => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
 281       hri    => [{ year => 2010 }],
 282     },
 283     msg    => '-dt_year works',
 284   },
 285
 286   {
 287     msg    => '-dt_get (year, month) works',
 288     search => { 'me.id' => 1 },
 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 = ?",
 294       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
 295       hri    => [{ year => 2010, month => 12 }],
 296     },
 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     },
 303     sqlite => {
 304       select => "STRFTIME('%Y', me.created_on), STRFTIME('%m', me.created_on)",
 305       where => "me.id = ?",
 306       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
 307       hri    => [{ year => 2010, month => 12 }],
 308     },
 309     postgres => {
 310       select => "EXTRACT(year FROM me.created_on), EXTRACT(month FROM me.created_on)",
 311       where => "me.id = ?",
 312       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
 313       hri    => [{ year => 2010, month => 12 }],
 314     },
 315     oracle => {
 316       select => "EXTRACT(year FROM me.created_on), EXTRACT(month FROM me.created_on)",
 317       where => "me.id = ?",
 318       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
 319       hri    => [{ year => 2010, month => 12 }],
 320     },
 321   },
 322
 323   {
 324     msg    => '-dt_month works',
 325     search => { 'me.id' => 1 },
 326     select   => [ [ -dt_month => { -ident => 'me.created_on' } ] ],
 327     as       => [ 'month' ],
 328     sqlite => {
 329       select   => "STRFTIME('%m', me.created_on)",
 330       where => "me.id = ?",
 331       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
 332       hri    => [{ month => 12 }],
 333     },
 334     mssql => {
 335       select => "DATEPART(month, me.created_on)",
 336       where => "me.id = ?",
 337       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
 338       hri    => [{ month => 12 }],
 339     },
 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     },
 346     postgres => {
 347       select => "EXTRACT(month FROM me.created_on)",
 348       where => "me.id = ?",
 349       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
 350       hri    => [{ month => 12 }],
 351     },
 352     oracle => {
 353       select => "EXTRACT(month FROM me.created_on)",
 354       where => "me.id = ?",
 355       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
 356       hri    => [{ month => 12 }],
 357     },
 358   },
 359
 360   {
 361     msg    => '-dt_day works',
 362     search => { 'me.id' => 1 },
 363     select   => [ [ -dt_day => { -ident => 'me.created_on' } ] ],
 364     as       => [ 'day' ],
 365     sqlite => {
 366       select   => "STRFTIME('%d', me.created_on)",
 367       where => "me.id = ?",
 368       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
 369       hri    => [{ day => 14 }],
 370     },
 371     mssql => {
 372       select => "DATEPART(day, me.created_on)",
 373       where => "me.id = ?",
 374       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
 375       hri    => [{ day => 14 }],
 376     },
 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     },
 383     postgres => {
 384       select => "EXTRACT(day FROM me.created_on)",
 385       where => "me.id = ?",
 386       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
 387       hri    => [{ day => 14 }],
 388     },
 389     oracle => {
 390       select => "EXTRACT(day FROM me.created_on)",
 391       where => "me.id = ?",
 392       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
 393       hri    => [{ day => 14 }],
 394     },
 395   },
 396
 397   {
 398     msg    => '-dt_hour works',
 399     search => { 'me.id' => 1 },
 400     select   => [ [ -dt_hour => { -ident => 'me.created_on' } ] ],
 401     as       => [ 'hour' ],
 402     sqlite => {
 403       select   => "STRFTIME('%H', me.created_on)",
 404       where => "me.id = ?",
 405       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
 406       hri    => [{ hour => 12 }],
 407     },
 408     mssql => {
 409       select => "DATEPART(hour, me.created_on)",
 410       where => "me.id = ?",
 411       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
 412       hri    => [{ hour => 12 }],
 413     },
 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     },
 420     postgres => {
 421       select => "EXTRACT(hour FROM me.created_on)",
 422       where => "me.id = ?",
 423       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
 424       hri    => [{ hour => 12 }],
 425     },
 426     oracle => {
 427       select => "EXTRACT(hour FROM me.created_on)",
 428       where => "me.id = ?",
 429       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
 430       hri    => [{ hour => 12 }],
 431     },
 432   },
 433
 434   {
 435     msg    => '-dt_minute works',
 436     search => { 'me.id' => 1 },
 437     select   => [ [ -dt_minute => { -ident => 'me.created_on' } ] ],
 438     as       => [ 'minute' ],
 439     sqlite => {
 440       select   => "STRFTIME('%M', me.created_on)",
 441       where => "me.id = ?",
 442       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
 443       hri    => [{ minute => 12 }],
 444     },
 445     mssql => {
 446       select => "DATEPART(minute, me.created_on)",
 447       where => "me.id = ?",
 448       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
 449       hri    => [{ minute => 12 }],
 450     },
 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     },
 457     postgres => {
 458       select => "EXTRACT(minute FROM me.created_on)",
 459       where => "me.id = ?",
 460       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
 461       hri    => [{ minute => 12 }],
 462     },
 463     oracle => {
 464       select => "EXTRACT(minute FROM me.created_on)",
 465       where => "me.id = ?",
 466       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
 467       hri    => [{ minute => 12 }],
 468     },
 469   },
 470
 471   {
 472     msg    => '-dt_second works',
 473     search => { 'me.id' => 1 },
 474     select   => [ [ -dt_second => { -ident => 'me.created_on' } ] ],
 475     as       => [ 'second' ],
 476     sqlite => {
 477       select   => "STRFTIME('%S', me.created_on)",
 478       where => "me.id = ?",
 479       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
 480       hri    => [{ second => 12 }],
 481     },
 482     mssql => {
 483       select => "DATEPART(second, me.created_on)",
 484       where => "me.id = ?",
 485       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
 486       hri    => [{ second => 12 }],
 487     },
 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     },
 494     postgres => {
 495       select => "EXTRACT(second FROM me.created_on)",
 496       where => "me.id = ?",
 497       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
 498       hri    => [{ second => 12 }],
 499     },
 500     oracle => {
 501       select => "EXTRACT(second FROM me.created_on)",
 502       where => "me.id = ?",
 503       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
 504       hri    => [{ second => 12 }],
 505     },
 506   },
 507
 508   {
 509     msg    => '-dt_diff (second) works',
 510     search => { 'me.id' => 2 },
 511     select   => [ [ -dt_diff => [second => { -ident => 'me.created_on' }, \'me.skip_inflation' ] ] ],
 512     as => [ 'sec_diff' ],
 513     sqlite => {
 514       select   => "(STRFTIME('%s', me.created_on) - STRFTIME('%s', me.skip_inflation))",
 515       where => "me.id = ?",
 516       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 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 = ?",
 522       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 523       hri => [{ sec_diff => 2*24*60*60 }],
 524     },
 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     },
 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 = ?",
 534       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 535       hri => [{ sec_diff => 2*24*60*60 }],
 536     },
 537     oracle => {
 538       select   => "TRUNC(MONTHS_BETWEEN(me.created_on, me.skip_inflation) * 31 * 24 * 60 * 60)",
 539       where => "me.id = ?",
 540       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 541       hri => [{ sec_diff => 2*24*60*60 }],
 542     },
 543   },
 544
 545   {
 546     msg    => '-dt_diff (day) works',
 547     search => { 'me.id' => 2 },
 548     select   => [ [ -dt_diff => [day => { -ident => 'me.created_on' }, \'me.skip_inflation' ] ] ],
 549     as => [ 'day_diff' ],
 550     sqlite => {
 551       select   => "(JULIANDAY(me.created_on) - JULIANDAY(me.skip_inflation))",
 552       where => "me.id = ?",
 553       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 554       hri => [{ day_diff => 2 }],
 555     },
 556     mssql => {
 557       select   => "DATEDIFF(dayofyear, me.skip_inflation, me.created_on)",
 558       where => "me.id = ?",
 559       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 560       hri => [{ day_diff => 2 }],
 561     },
 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     },
 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 = ?",
 571       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 572       hri => [{ day_diff => 2 }],
 573     },
 574     oracle => {
 575       select   => "TRUNC(MONTHS_BETWEEN(me.created_on, me.skip_inflation) * 31)",
 576       where => "me.id = ?",
 577       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 578       hri => [{ day_diff => 2 }],
 579     },
 580   },
 581
 582   {
 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 = ?",
 593       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 594       hri    => [{ year => -1 }],
 595     },
 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     },
 602     oracle => {
 603       select   => "TRUNC(MONTHS_BETWEEN(me.starts_at, me.created_on) / 12)",
 604       where => "me.id = ?",
 605       bind   => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 606       hri    => [{ year => -1 }],
 607     },
 608   },
 609
 610   {
 611     msg    => '-dt_add (year) works',
 612     search => { 'me.id' => 2 },
 613     select   => [ [ -dt_add => [year => 3, { -ident => 'me.created_on' } ] ] ],
 614     as   => [ 'date' ],
 615     sqlite => {
 616       select => "(datetime(me.created_on, ? || ' years'))",
 617       where => "me.id = ?",
 618       bind   => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 619       hri    => [{ date => '2014-12-14 12:12:12' }],
 620     },
 621     mssql => {
 622       select => "(DATEADD(year, ?, me.created_on))",
 623       where => "me.id = ?",
 624       bind   => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 625       hri    => [{ date => '2014-12-14 12:12:12.000' }],
 626       skip   => 'need working bindtypes',
 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, ?, me.created_on))",
 655       where => "me.id = ?",
 656       bind   => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 657       hri    => [{ date => '2012-03-14 12:12:12.000' }],
 658       skip   => 'need working bindtypes',
 659     },
 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     },
 666     oracle => {
 667       select => "(me.created_on + NUMTOYMINTERVAL(?, 'month'))",
 668       where => "me.id = ?",
 669       bind   => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 670       hri    => [{ date => '2012-03-14 12:12:12.000000000' }],
 671     },
 672   },
 673
 674   {
 675     msg    => '-dt_add (day) works',
 676     search => { 'me.id' => 2 },
 677     select   => [ [ -dt_add => [day => 3, { -ident => 'me.created_on' } ] ] ],
 678     as   => [ 'date' ],
 679     sqlite => {
 680       select => "(datetime(me.created_on, ? || ' days'))",
 681       where => "me.id = ?",
 682       bind   => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 683       hri    => [{ date => '2011-12-17 12:12:12' }],
 684     },
 685     mssql => {
 686       select => "(DATEADD(dayofyear, ?, me.created_on))",
 687       where => "me.id = ?",
 688       bind   => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 689       hri    => [{ date => '2011-12-17 12:12:12.000' }],
 690       skip   => 'need working bindtypes',
 691     },
 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     },
 698     oracle => {
 699       select => "(me.created_on + NUMTODSINTERVAL(?, 'day'))",
 700       where => "me.id = ?",
 701       bind   => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 702       hri    => [{ date => '2011-12-17 12:12:12.000000000' }],
 703     },
 704   },
 705
 706   {
 707     msg    => '-dt_add (hour) works',
 708     search => { 'me.id' => 2 },
 709     select   => [ [ -dt_add => [hour => 3, { -ident => 'me.created_on' } ] ] ],
 710     as   => [ 'date' ],
 711     sqlite => {
 712       select => "(datetime(me.created_on, ? || ' hours'))",
 713       where => "me.id = ?",
 714       bind   => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 715       hri    => [{ date => '2011-12-14 15:12:12' }],
 716     },
 717     mssql => {
 718       select => "(DATEADD(hour, ?, me.created_on))",
 719       where => "me.id = ?",
 720       bind   => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 721       hri    => [{ date => '2011-12-14 15:12:12.000' }],
 722       skip   => 'need working bindtypes',
 723     },
 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     },
 730     oracle => {
 731       select => "(me.created_on + NUMTODSINTERVAL(?, 'hour'))",
 732       where => "me.id = ?",
 733       bind   => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 734       hri    => [{ date => '2011-12-14 15:12:12.000000000' }],
 735     },
 736   },
 737
 738   {
 739     msg    => '-dt_add (minute) works',
 740     search => { 'me.id' => 2 },
 741     select   => [ [ -dt_add => [minute => 3, { -ident => 'me.created_on' } ] ] ],
 742     as   => [ 'date' ],
 743     sqlite => {
 744       select => "(datetime(me.created_on, ? || ' minutes'))",
 745       where => "me.id = ?",
 746       bind   => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 747       hri    => [{ date => '2011-12-14 12:15:12' }],
 748     },
 749     mssql => {
 750       select => "(DATEADD(minute, ?, me.created_on))",
 751       where => "me.id = ?",
 752       bind   => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 753       hri    => [{ date => '2011-12-14 12:15:12.000' }],
 754       skip   => 'need working bindtypes',
 755     },
 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     },
 762     oracle => {
 763       select => "(me.created_on + NUMTODSINTERVAL(?, 'minute'))",
 764       where => "me.id = ?",
 765       bind   => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 766       hri    => [{ date => '2011-12-14 12:15:12.000000000' }],
 767     },
 768   },
 769
 770   {
 771     msg    => '-dt_add (second) works',
 772     search => { 'me.id' => 2 },
 773     select   => [ [ -dt_add => [second => 3, { -ident => 'me.created_on' } ] ] ],
 774     as   => [ 'date' ],
 775     sqlite => {
 776       select => "(datetime(me.created_on, ? || ' seconds'))",
 777       where => "me.id = ?",
 778       bind   => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 779       hri    => [{ date => '2011-12-14 12:12:15' }],
 780     },
 781     mssql => {
 782       select => "(DATEADD(second, ?, me.created_on))",
 783       where => "me.id = ?",
 784       bind   => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 785       hri    => [{ date => '2011-12-14 12:12:15.000' }],
 786       skip   => 'need working bindtypes',
 787     },
 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     },
 794     oracle => {
 795       select => "(me.created_on + NUMTODSINTERVAL(?, 'second'))",
 796       where => "me.id = ?",
 797       bind   => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
 798       hri    => [{ date => '2011-12-14 12:12:15.000000000' }],
 799     },
 800   },
 801
 802   {
 803     msg    => 'nested -dt_add works',
 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 = ?",
 810       bind   => [[unknown_col, 1], [unknown_col, 3 ], [{dbic_colname => 'me.id', sqlt_datatype => 'integer' }, 2]],
 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 = ?",
 816       bind   => [[unknown_col, 3 ], [unknown_col, 1], [{dbic_colname => 'me.id', sqlt_datatype => 'integer' }, 2]],
 817       hri    => [{ date => '2011-12-15 12:12:15.000' }],
 818       skip   => 'need working bindtypes',
 819     },
 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     },
 826     oracle => {
 827       select => "((me.created_on + NUMTODSINTERVAL(?, 'day')) + NUMTODSINTERVAL(?, 'second'))",
 828       where => "me.id = ?",
 829       bind   => [[unknown_col, 1], [unknown_col, 3 ], [{dbic_colname => 'me.id', sqlt_datatype => 'integer' }, 2]],
 830       hri    => [{ date => '2011-12-15 12:12:15.000000000' }],
 831     },
 832   },
 833 );
 834
 835 for my $t (@tests) {
 836
 837   DB_TEST:
 838   for my $db (keys %rs) {
 839      my $db_test = $t->{$db};
 840      unless ($db_test) {
 841         ok 0, "$t->{msg} ($db not tested!)";
 842         next DB_TEST;
 843      }
 844
 845      my ($r, $my_rs);
 846
 847      my $cref = sub {
 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
 856      };
 857
 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(
 876          $r,
 877          "(SELECT $db_test->{select} FROM event me WHERE $db_test->{where})",
 878          $db_test->{bind},
 879          ($t->{msg} ? "$t->{msg} ($db)" : ())
 880        );
 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 {
 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              }
 895           } catch {
 896              ok 0, $msg . " $_";
 897           }
 898         } }
 899      }
 900   }
 901 }
 902
 903 done_testing;