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
  13 use DBICTest;
  14
  15 my %dbs_to_test = (
  16    sqlite   => 1,
  17    mssql    => 0,
  18    postgres => 1,
  19 );
  20
  21 my %schema = (
  22    sqlite => DBICTest->init_schema( no_populate => 1 ),
  23    mssql => do {
  24       my ($dsn, $user, $pass) = @ENV{map { "DBICTEST_MSSQL_ODBC_${_}" } qw/DSN USER PASS/};
  25       if ($dsn && $user) {
  26          my $s = DBICTest::Schema->connect($dsn, $user, $pass);
  27          try { $s->storage->ensure_connected };
  28
  29          $s->storage->dbh_do (sub {
  30              my ($storage, $dbh) = @_;
  31              eval { $dbh->do("DROP TABLE event") };
  32              $dbh->do(<<'SQL');
  33 CREATE TABLE event (
  34    id INT IDENTITY NOT NULL,
  35    starts_at DATE NOT NULL,
  36    created_on DATETIME NOT NULL,
  37    varchar_date VARCHAR(20),
  38    varchar_datetime VARCHAR(20),
  39    skip_inflation DATETIME,
  40    ts_without_tz DATETIME,
  41
  42    primary key(id)
  43 )
  44 SQL
  45         $dbs_to_test{mssql} = 1;
  46 });
  47 $s;
  48       } else {
  49          DBICTest->init_schema( no_deploy=> 1, storage_type => '::DBI::MSSQL' )
  50       }
  51    },
  52    ## copypasta'd for great justice
  53    postgres =>  do {
  54       my ($dsn, $user, $pass) = @ENV{map { "DBICTEST_POSTGRES_${_}" } qw/DSN USER PASS/};
  55       if ($dsn && $user) {
  56          my $s = DBICTest::Schema->connect($dsn, $user, $pass);
  57          try { $s->storage->ensure_connected };
  58
  59          $s->storage->dbh_do (sub {
  60              my ($storage, $dbh) = @_;
  61              eval { $dbh->do("DROP TABLE event") };
  62              $dbh->do(<<'SQL');
  63 CREATE TABLE event (
  64    id SERIAL NOT NULL PRIMARY KEY,
  65    starts_at DATE NOT NULL,
  66    created_on TIMESTAMP NOT NULL,
  67    varchar_date VARCHAR(20),
  68    varchar_datetime VARCHAR(20),
  69    skip_inflation TIMESTAMP,
  70    ts_without_tz TIMESTAMP WITHOUT TIME ZONE
  71 )
  72 SQL
  73         $dbs_to_test{postgres} = 1;
  74 });
  75 $s;
  76       } else {
  77          DBICTest->init_schema( no_deploy=> 1, storage_type => '::DBI::Pg' )
  78       }
  79    }
  80 );
  81
  82 my %rs = map { $_ => $schema{$_}->resultset('Event') } keys %schema;
  83
  84 $rs{sqlite}->populate([
  85  [qw(starts_at created_on skip_inflation)],
  86  ['2010-12-12', '2010-12-14 12:12:12', '2019-12-12 12:12:12'],
  87  ['2010-12-12', '2011-12-14 12:12:12', '2011-12-12 12:12:12'],
  88 ]);
  89
  90 $rs{mssql}->populate([
  91  [qw(starts_at created_on skip_inflation)],
  92  ['2010-12-12', '2010-12-14 12:12:12.000', '2019-12-12 12:12:12.000'],
  93  ['2010-12-12', '2011-12-14 12:12:12.000', '2011-12-12 12:12:12.000'],
  94 ]) if $schema{mssql}->storage->connected;
  95
  96 $rs{postgres}->populate([
  97  [qw(starts_at created_on skip_inflation)],
  98  ['2010-12-12', '2010-12-14 12:12:12', '2019-12-12 12:12:12'],
  99  ['2010-12-12', '2011-12-14 12:12:12', '2011-12-12 12:12:12'],
 100 ]) if $schema{postgres}->storage->connected;
 101
 102
 103 my $date = DateTime->new(
 104    year => 2010,
 105    month => 12,
 106    day   => 14,
 107    hour  => 12,
 108    minute => 12,
 109    second => 12,
 110 );
 111
 112 sub hri_thing {
 113    return {
 114       starts_at => $_[0],
 115       created_on => $_[1],
 116       skip_inflation => $_[2]
 117    }
 118 }
 119
 120 my $date2 = $date->clone->set_day(16);
 121
 122 ## test format:
 123 ##   search => { dbic_search_code/params }
 124 ##   rdbms_name => literal_sql
 125 my @tests = (
 126 ## -dt-now tests
 127   {
 128     search => { 'me.created_on' => { -dt => $date } },
 129     sqlite => {
 130       select => 'me.starts_at, me.created_on, me.skip_inflation',
 131       where  => 'me.created_on = ?',
 132       bind   => [[ 'me.created_on', '2010-12-14 12:12:12' ]],
 133       hri    => [hri_thing('2010-12-12', '2010-12-14 12:12:12', '2019-12-12 12:12:12')],
 134     },
 135     mssql => {
 136       select => 'me.starts_at, me.created_on, me.skip_inflation',
 137       where  => 'me.created_on = ?',
 138       bind   => [[ 'me.created_on', '2010-12-14 12:12:12.000' ]],
 139       hri    => [hri_thing('2010-12-12', '2010-12-14 12:12:12.000', '2019-12-12 12:12:12.000')],
 140     },
 141     postgres => {
 142       select => 'me.starts_at, me.created_on, me.skip_inflation',
 143       where  => 'me.created_on = ?',
 144       bind   => [[ 'me.created_on', '2010-12-14 12:12:12' ]],
 145       hri    => [hri_thing('2010-12-12', '2010-12-14 12:12:12', '2019-12-12 12:12:12')],
 146     },
 147     msg => '-dt_now works',
 148   },
 149 ## -dt_year tests
 150   {
 151     search => { 'me.id' => 1 },
 152     select => [ [ -dt_year => { -ident => 'me.created_on' } ] ],
 153     as     => [ 'year' ],
 154     mssql => {
 155       select => "DATEPART(year, me.created_on)",
 156       where => "me.id = ?",
 157       bind   => [['me.id' => 1 ]],
 158       hri    => [{ year => 2010 }],
 159     },
 160     sqlite => {
 161       select => "STRFTIME('%Y', me.created_on)",
 162       where => "me.id = ?",
 163       bind   => [['me.id' => 1 ]],
 164       hri    => [{ year => 2010 }],
 165     },
 166     postgres => {
 167       select => "EXTRACT(year FROM me.created_on)",
 168       where => "me.id = ?",
 169       bind   => [['me.id' => 1 ]],
 170       hri    => [{ year => 2010 }],
 171     },
 172     msg    => '-dt_year works',
 173   },
 174 ## -dt_get(year, month) tests
 175   {
 176     search => { 'me.id' => 1 },
 177     select => [ [ -dt_get => [[qw(year month)], { -ident => 'me.created_on' }] ] ],
 178     as     => [ qw(year month) ],
 179     mssql => {
 180       select => "DATEPART(year, me.created_on), DATEPART(month, me.created_on)",
 181       where => "me.id = ?",
 182       bind   => [['me.id' => 1 ]],
 183       hri    => [{ year => 2010, month => 12 }],
 184     },
 185     sqlite => {
 186       select => "STRFTIME('%Y', me.created_on), STRFTIME('%m', me.created_on)",
 187       where => "me.id = ?",
 188       bind   => [['me.id' => 1 ]],
 189       hri    => [{ year => 2010, month => 12 }],
 190     },
 191     postgres => {
 192       select => "EXTRACT(year FROM me.created_on), EXTRACT(month FROM me.created_on)",
 193       where => "me.id = ?",
 194       bind   => [['me.id' => 1 ]],
 195       hri    => [{ year => 2010, month => 12 }],
 196     },
 197     msg    => '-dt_get (year, month) works',
 198   },
 199 ## -dt_month tests
 200   {
 201     search => { 'me.id' => 1 },
 202     select   => [ [ -dt_month => { -ident => 'me.created_on' } ] ],
 203     as       => [ 'month' ],
 204     sqlite => {
 205       select   => "STRFTIME('%m', me.created_on)",
 206       where => "me.id = ?",
 207       bind   => [['me.id' => 1 ]],
 208       hri    => [{ month => 12 }],
 209     },
 210     mssql => {
 211       select => "DATEPART(month, me.created_on)",
 212       where => "me.id = ?",
 213       bind   => [['me.id' => 1 ]],
 214       hri    => [{ month => 12 }],
 215     },
 216     postgres => {
 217       select => "EXTRACT(month FROM me.created_on)",
 218       where => "me.id = ?",
 219       bind   => [['me.id' => 1 ]],
 220       hri    => [{ month => 12 }],
 221
 222     },
 223     msg    => '-dt_month works',
 224   },
 225 ## -dt_day tests
 226   {
 227     search => { 'me.id' => 1 },
 228     select   => [ [ -dt_day => { -ident => 'me.created_on' } ] ],
 229     as       => [ 'day' ],
 230     sqlite => {
 231       select   => "STRFTIME('%d', me.created_on)",
 232       where => "me.id = ?",
 233       bind   => [['me.id' => 1 ]],
 234       hri    => [{ day => 14 }],
 235     },
 236     mssql => {
 237       select => "DATEPART(day, me.created_on)",
 238       where => "me.id = ?",
 239       bind   => [['me.id' => 1 ]],
 240       hri    => [{ day => 14 }],
 241     },
 242     postgres => {
 243       select => "EXTRACT(day FROM me.created_on)",
 244       where => "me.id = ?",
 245       bind   => [['me.id' => 1 ]],
 246       hri    => [{ day => 14 }],
 247
 248     },
 249     msg    => '-dt_day works',
 250   },
 251 ## -dt_hour tests
 252   {
 253     search => { 'me.id' => 1 },
 254     select   => [ [ -dt_hour => { -ident => 'me.created_on' } ] ],
 255     as       => [ 'hour' ],
 256     sqlite => {
 257       select   => "STRFTIME('%H', me.created_on)",
 258       where => "me.id = ?",
 259       bind   => [['me.id' => 1 ]],
 260       hri    => [{ hour => 12 }],
 261     },
 262     mssql => {
 263       select => "DATEPART(hour, me.created_on)",
 264       where => "me.id = ?",
 265       bind   => [['me.id' => 1 ]],
 266       hri    => [{ hour => 12 }],
 267     },
 268     postgres => {
 269       select => "EXTRACT(hour FROM me.created_on)",
 270       where => "me.id = ?",
 271       bind   => [['me.id' => 1 ]],
 272       hri    => [{ hour => 12 }],
 273     },
 274     msg    => '-dt_hour works',
 275   },
 276 ## -dt_minute tests
 277   {
 278     search => { 'me.id' => 1 },
 279     select   => [ [ -dt_minute => { -ident => 'me.created_on' } ] ],
 280     as       => [ 'minute' ],
 281     sqlite => {
 282       select   => "STRFTIME('%M', me.created_on)",
 283       where => "me.id = ?",
 284       bind   => [['me.id' => 1 ]],
 285       hri    => [{ minute => 12 }],
 286     },
 287     mssql => {
 288       select => "DATEPART(minute, me.created_on)",
 289       where => "me.id = ?",
 290       bind   => [['me.id' => 1 ]],
 291       hri    => [{ minute => 12 }],
 292     },
 293     postgres => {
 294       select => "EXTRACT(minute FROM me.created_on)",
 295       where => "me.id = ?",
 296       bind   => [['me.id' => 1 ]],
 297       hri    => [{ minute => 12 }],
 298     },
 299     msg    => '-dt_minute works',
 300   },
 301 ## -dt_second tests
 302   {
 303     search => { 'me.id' => 1 },
 304     select   => [ [ -dt_second => { -ident => 'me.created_on' } ] ],
 305     as       => [ 'second' ],
 306     sqlite => {
 307       select   => "STRFTIME('%S', me.created_on)",
 308       where => "me.id = ?",
 309       bind   => [['me.id' => 1 ]],
 310       hri    => [{ second => 12 }],
 311     },
 312     mssql => {
 313       select => "DATEPART(second, me.created_on)",
 314       where => "me.id = ?",
 315       bind   => [['me.id' => 1 ]],
 316       hri    => [{ second => 12 }],
 317     },
 318     postgres => {
 319       select => "EXTRACT(second FROM me.created_on)",
 320       where => "me.id = ?",
 321       bind   => [['me.id' => 1 ]],
 322       hri    => [{ second => 12 }],
 323
 324     },
 325     msg    => '-dt_second works',
 326   },
 327 ## -dt_diff(second) tests
 328   {
 329     search => { 'me.id' => 2 },
 330     select   => [ [ -dt_diff => [second => { -ident => 'me.created_on' }, \'me.skip_inflation' ] ] ],
 331     as => [ 'sec_diff' ],
 332     sqlite => {
 333       select   => "(STRFTIME('%s', me.created_on) - STRFTIME('%s', me.skip_inflation))",
 334       where => "me.id = ?",
 335       bind   => [['me.id' => 2 ]],
 336       hri => [{ sec_diff => 2*24*60*60 }],
 337     },
 338     mssql => {
 339       select   => "DATEDIFF(second, me.skip_inflation, me.created_on)",
 340       where => "me.id = ?",
 341       bind   => [['me.id' => 2 ]],
 342       hri => [{ sec_diff => 2*24*60*60 }],
 343     },
 344     postgres => {
 345       select   => "EXTRACT(epoch FROM (me.created_on::timestamp with time zone - me.skip_inflation::timestamp with time zone))",
 346       where => "me.id = ?",
 347       bind   => [['me.id' => 2 ]],
 348       hri => [{ sec_diff => 2*24*60*60 }],
 349     },
 350     msg    => '-dt_diff (second) works',
 351   },
 352
 353 # -dt_diff(day) tests
 354   {
 355     search => { 'me.id' => 2 },
 356     select   => [ [ -dt_diff => [day => { -ident => 'me.created_on' }, \'me.skip_inflation' ] ] ],
 357     as => [ 'day_diff' ],
 358     sqlite => {
 359       select   => "(JULIANDAY(me.created_on) - JULIANDAY(me.skip_inflation))",
 360       where => "me.id = ?",
 361       bind   => [['me.id' => 2 ]],
 362       hri => [{ day_diff => 2 }],
 363     },
 364     mssql => {
 365       select   => "DATEDIFF(dayofyear, me.skip_inflation, me.created_on)",
 366       where => "me.id = ?",
 367       bind   => [['me.id' => 2 ]],
 368       hri => [{ day_diff => 2 }],
 369     },
 370     postgres => {
 371       select   => "EXTRACT(DAY FROM (me.created_on::timestamp with time zone - me.skip_inflation::timestamp with time zone))",
 372       where => "me.id = ?",
 373       bind   => [['me.id' => 2 ]],
 374       hri => [{ day_diff => 2 }],
 375     },
 376
 377     msg    => '-dt_diff (day) works',
 378   },
 379
 380   {
 381     search => { 'me.id' => 2 },
 382     select   => [ [ -dt_add => [year => 3, { -ident => 'me.created_on' } ] ] ],
 383     as   => [ 'date' ],
 384     sqlite => {
 385       select => "(datetime(me.created_on, ? || ' years'))",
 386       where => "me.id = ?",
 387       bind   => [['', 3], ['me.id' => 2 ]],
 388       hri    => [{ date => '2014-12-14 12:12:12' }],
 389     },
 390     mssql => {
 391       select => "(DATEADD(year, ?, me.created_on))",
 392       where => "me.id = ?",
 393       bind   => [['', 3], ['me.id' => 2 ]],
 394       hri    => [{ date => '2014-12-14 12:12:12.000' }],
 395       skip   => 'need working bindtypes',
 396     },
 397     msg    => '-dt_add (year) works',
 398   },
 399
 400   {
 401     search => { 'me.id' => 2 },
 402     select   => [ [ -dt_add => [month => 3, { -ident => 'me.created_on' } ] ] ],
 403     as   => [ 'date' ],
 404     sqlite => {
 405       select => "(datetime(me.created_on, ? || ' months'))",
 406       where => "me.id = ?",
 407       bind   => [['', 3], ['me.id' => 2 ]],
 408       hri    => [{ date => '2012-03-14 12:12:12' }],
 409     },
 410     mssql => {
 411       select => "(DATEADD(month, ?, me.created_on))",
 412       where => "me.id = ?",
 413       bind   => [['', 3], ['me.id' => 2 ]],
 414       hri    => [{ date => '2012-03-14 12:12:12.000' }],
 415       skip   => 'need working bindtypes',
 416     },
 417     msg    => '-dt_add (month) works',
 418   },
 419
 420   {
 421     search => { 'me.id' => 2 },
 422     select   => [ [ -dt_add => [day => 3, { -ident => 'me.created_on' } ] ] ],
 423     as   => [ 'date' ],
 424     sqlite => {
 425       select => "(datetime(me.created_on, ? || ' days'))",
 426       where => "me.id = ?",
 427       bind   => [['', 3], ['me.id' => 2 ]],
 428       hri    => [{ date => '2011-12-17 12:12:12' }],
 429     },
 430     mssql => {
 431       select => "(DATEADD(dayofyear, ?, me.created_on))",
 432       where => "me.id = ?",
 433       bind   => [['', 3], ['me.id' => 2 ]],
 434       hri    => [{ date => '2011-12-17 12:12:12.000' }],
 435       skip   => 'need working bindtypes',
 436     },
 437     msg    => '-dt_add (day) works',
 438   },
 439
 440   {
 441     search => { 'me.id' => 2 },
 442     select   => [ [ -dt_add => [hour => 3, { -ident => 'me.created_on' } ] ] ],
 443     as   => [ 'date' ],
 444     sqlite => {
 445       select => "(datetime(me.created_on, ? || ' hours'))",
 446       where => "me.id = ?",
 447       bind   => [['', 3], ['me.id' => 2 ]],
 448       hri    => [{ date => '2011-12-14 15:12:12' }],
 449     },
 450     mssql => {
 451       select => "(DATEADD(hour, ?, me.created_on))",
 452       where => "me.id = ?",
 453       bind   => [['', 3], ['me.id' => 2 ]],
 454       hri    => [{ date => '2011-12-14 15:12:12.000' }],
 455       skip   => 'need working bindtypes',
 456     },
 457     msg    => '-dt_add (hour) works',
 458   },
 459
 460   {
 461     search => { 'me.id' => 2 },
 462     select   => [ [ -dt_add => [minute => 3, { -ident => 'me.created_on' } ] ] ],
 463     as   => [ 'date' ],
 464     sqlite => {
 465       select => "(datetime(me.created_on, ? || ' minutes'))",
 466       where => "me.id = ?",
 467       bind   => [['', 3], ['me.id' => 2 ]],
 468       hri    => [{ date => '2011-12-14 12:15:12' }],
 469     },
 470     mssql => {
 471       select => "(DATEADD(minute, ?, me.created_on))",
 472       where => "me.id = ?",
 473       bind   => [['', 3], ['me.id' => 2 ]],
 474       hri    => [{ date => '2011-12-14 12:15:12.000' }],
 475       skip   => 'need working bindtypes',
 476     },
 477     msg    => '-dt_add (minute) works',
 478   },
 479
 480   {
 481     search => { 'me.id' => 2 },
 482     select   => [ [ -dt_add => [second => 3, { -ident => 'me.created_on' } ] ] ],
 483     as   => [ 'date' ],
 484     sqlite => {
 485       select => "(datetime(me.created_on, ? || ' seconds'))",
 486       where => "me.id = ?",
 487       bind   => [['', 3], ['me.id' => 2 ]],
 488       hri    => [{ date => '2011-12-14 12:12:15' }],
 489     },
 490     mssql => {
 491       select => "(DATEADD(second, ?, me.created_on))",
 492       where => "me.id = ?",
 493       bind   => [['', 3], ['me.id' => 2 ]],
 494       hri    => [{ date => '2011-12-14 12:12:15.000' }],
 495       skip   => 'need working bindtypes',
 496     },
 497     msg    => '-dt_add (second) works',
 498   },
 499
 500   {
 501     search => { 'me.id' => 2 },
 502     select   => [ [ -dt_add => [second => 3, { -dt_add => [ day => 1, { -ident => 'me.created_on' } ] } ] ] ],
 503     as       => [ 'date' ],
 504     sqlite => {
 505       select   => "(datetime((datetime(me.created_on, ? || ' days')), ? || ' seconds'))",
 506       where => "me.id = ?",
 507       bind   => [['', 1], [ '', 3 ], ['me.id', 2]],
 508       hri    => [{ date => '2011-12-15 12:12:15' }],
 509     },
 510     mssql => {
 511       select => "(DATEADD(second, ?, (DATEADD(dayofyear, ?, me.created_on))))",
 512       where => "me.id = ?",
 513       bind   => [['', 3], [ '', 1 ], ['me.id', 2]],
 514       hri    => [{ date => '2011-12-15 12:12:15.000' }],
 515       skip   => 'need working bindtypes',
 516     },
 517     msg    => 'nested -dt_add works',
 518   },
 519
 520   {
 521     search => { 'me.id' => 2 },
 522     select   => [ [ -dt_diff => [year => \'me.starts_at', { -ident => 'me.created_on' } ] ] ],
 523     as       => [ 'year' ],
 524     sqlite => {
 525       exception_like => qr/date diff not supported for part "year" with database "SQLite"/,
 526     },
 527     mssql => {
 528       select   => "DATEDIFF(year, me.created_on, me.starts_at)",
 529       where => "me.id = ?",
 530       bind   => [['me.id', 2]],
 531       hri    => [{ year => -1 }],
 532     },
 533     msg => '-dt_diff (year) works',
 534   },
 535 );
 536
 537 for my $t (@tests) {
 538
 539   DB_TEST:
 540   for my $db (keys %rs) {
 541      my $db_test = $t->{$db};
 542      next DB_TEST unless $db_test;
 543
 544      my ($r, $my_rs);
 545
 546      my $cref = sub {
 547        my $stuff = {
 548          ( exists $t->{select}
 549            ? ( select => $t->{select}, as => $t->{as} )
 550            : ( columns => [qw(starts_at created_on skip_inflation)] )
 551          )
 552        };
 553        $my_rs = $rs{$db}->search($t->{search}, $stuff);
 554        $r = $my_rs->as_query
 555      };
 556
 557      if ($db_test->{exception_like}) {
 558        throws_ok(
 559          sub { $cref->() },
 560          $db_test->{exception_like},
 561          "throws the expected exception ($db_test->{exception_like})",
 562        );
 563      } else {
 564        if ($db_test->{warning_like}) {
 565          warning_like(
 566            sub { $cref->() },
 567            $db_test->{warning_like},
 568            "issues the expected warning ($db_test->{warning_like})"
 569          );
 570        }
 571        else {
 572          $cref->();
 573        }
 574        is_same_sql_bind(
 575          $r,
 576          "(SELECT $db_test->{select} FROM event me WHERE $db_test->{where})",
 577          $db_test->{bind},
 578          ($t->{msg} ? "$t->{msg} ($db)" : ())
 579        );
 580
 581        SKIP: {
 582        if (my $hri = $db_test->{hri}) {
 583           skip "Cannot test database we are not connected to ($db)", 1 unless $dbs_to_test{$db};
 584           skip $db_test->{skip} . " ($db)", 1 if $db_test->{skip};
 585
 586           my $msg = ($t->{msg} ? "$t->{msg} ($db actually pulls expected data)" : '');
 587           try {
 588              is_deeply [ $my_rs->hri_dump->all ], $hri, $msg;
 589           } catch {
 590              ok 0, $msg . " $_";
 591           }
 592         } }
 593      }
 594   }
 595 }
 596
 597 done_testing;