use Test::Exception;
use lib qw(t/lib);
+use DBICTest::RunMode;
use DBIC::SqlMakerTest;
use DateTime;
use DBIx::Class::SQLMaker::MSSQL;
DBICTest->init_schema( no_deploy=> 1, storage_type => '::DBI::MSSQL' )
}
},
+ mysql => do {
+ my ($dsn, $user, $pass) = @ENV{map { "DBICTEST_MYSQL_${_}" } qw/DSN USER PASS/};
+ if ($dsn && $user) {
+ my $s = DBICTest::Schema->connect($dsn, $user, $pass);
+ try { $s->storage->ensure_connected };
+
+ $s->storage->dbh_do (sub {
+ my ($storage, $dbh) = @_;
+ eval { $dbh->do("DROP TABLE event") };
+ $dbh->do(<<'SQL');
+CREATE TABLE event (
+ id INT AUTO_INCREMENT NOT NULL,
+ starts_at DATE NOT NULL,
+ created_on DATETIME NOT NULL,
+ varchar_date VARCHAR(20),
+ varchar_datetime VARCHAR(20),
+ skip_inflation DATETIME,
+ ts_without_tz DATETIME,
+
+ primary key(id)
+)
+SQL
+ $dbs_to_test{mysql} = 1;
+});
+$s;
+ } else {
+ DBICTest->init_schema( no_deploy=> 1, storage_type => '::DBI::mysql' )
+ }
+ },
## copypasta'd for great justice
postgres => do {
my ($dsn, $user, $pass) = @ENV{map { "DBICTEST_PG_${_}" } qw/DSN USER PASS/};
my %rs = map { $_ => $schema{$_}->resultset('Event') } keys %schema;
-$rs{sqlite}->populate([
- [qw(starts_at created_on skip_inflation)],
- ['2010-12-12', '2010-12-14 12:12:12', '2019-12-12 12:12:12'],
- ['2010-12-12', '2011-12-14 12:12:12', '2011-12-12 12:12:12'],
-]);
-
-$rs{mssql}->populate([
- [qw(starts_at created_on skip_inflation)],
- ['2010-12-12', '2010-12-14 12:12:12.000', '2019-12-12 12:12:12.000'],
- ['2010-12-12', '2011-12-14 12:12:12.000', '2011-12-12 12:12:12.000'],
-]) if $schema{mssql}->storage->connected;
-
-$rs{postgres}->populate([
- [qw(starts_at created_on skip_inflation)],
- ['2010-12-12', '2010-12-14 12:12:12', '2019-12-12 12:12:12'],
- ['2010-12-12', '2011-12-14 12:12:12', '2011-12-12 12:12:12'],
-]) if $schema{postgres}->storage->connected;
-
-$rs{oracle}->populate([
- [qw(starts_at created_on skip_inflation)],
- ['2010-12-12', '2010-12-14 12:12:12', '2019-12-12 12:12:12'],
- ['2010-12-12', '2011-12-14 12:12:12', '2011-12-12 12:12:12'],
-]) if $schema{oracle}->storage->connected;
+for (grep { $schema{$_}->storage->connected } keys %rs) {
+ $rs{$_}->populate([
+ [qw(starts_at created_on skip_inflation)],
+ ['2010-12-12', '2010-12-14 12:12:12', '2019-12-12 12:12:12'],
+ ['2010-12-12', '2011-12-14 12:12:12', '2011-12-12 12:12:12'],
+ ])
+}
my $date = DateTime->new(
year => 2010,
## search => { dbic_search_code/params }
## rdbms_name => literal_sql
my @tests = (
-## -dt-now tests
+
{
search => { 'me.created_on' => { -dt => $date } },
sqlite => {
bind => [[{ dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12.000' ]],
hri => [hri_thing('2010-12-12', '2010-12-14 12:12:12.000', '2019-12-12 12:12:12.000')],
},
+ mysql => {
+ select => 'me.starts_at, me.created_on, me.skip_inflation',
+ where => 'me.created_on = ?',
+ bind => [[{ dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12' ]],
+ hri => [hri_thing('2010-12-12', '2010-12-14 12:12:12', '2019-12-12 12:12:12')],
+ },
postgres => {
select => 'me.starts_at, me.created_on, me.skip_inflation',
where => 'me.created_on = ?',
},
msg => '-dt_now works',
},
-## -dt_year tests
+
{
search => { 'me.id' => 1 },
select => [ [ -dt_year => { -ident => 'me.created_on' } ] ],
bind => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
hri => [{ year => 2010 }],
},
+ mysql => {
+ select => "EXTRACT(YEAR FROM me.created_on)",
+ where => "me.id = ?",
+ bind => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
+ hri => [{ year => 2010 }],
+ },
sqlite => {
select => "STRFTIME('%Y', me.created_on)",
where => "me.id = ?",
hri => [{ year => 2010 }],
},
postgres => {
- select => "EXTRACT(year FROM me.created_on)",
+ select => "date_part('year', me.created_on)",
where => "me.id = ?",
bind => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
hri => [{ year => 2010 }],
bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
hri => [{ year => 2010, month => 12 }],
},
+ mysql => {
+ select => "EXTRACT(YEAR FROM me.created_on), EXTRACT(MONTH FROM me.created_on)",
+ where => "me.id = ?",
+ bind => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
+ hri => [{ year => 2010, month => 12 }],
+ },
sqlite => {
select => "STRFTIME('%Y', me.created_on), STRFTIME('%m', me.created_on)",
where => "me.id = ?",
hri => [{ year => 2010, month => 12 }],
},
postgres => {
- select => "EXTRACT(year FROM me.created_on), EXTRACT(month FROM me.created_on)",
+ select => "date_part('year', me.created_on), date_part('month', me.created_on)",
where => "me.id = ?",
bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
hri => [{ year => 2010, month => 12 }],
bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
hri => [{ month => 12 }],
},
+ mysql => {
+ select => "EXTRACT(MONTH FROM me.created_on)",
+ where => "me.id = ?",
+ bind => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
+ hri => [{ month => 12 }],
+ },
postgres => {
- select => "EXTRACT(month FROM me.created_on)",
+ select => "date_part('month', me.created_on)",
where => "me.id = ?",
bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
hri => [{ month => 12 }],
bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
hri => [{ day => 14 }],
},
+ mysql => {
+ select => "EXTRACT(DAY FROM me.created_on)",
+ where => "me.id = ?",
+ bind => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
+ hri => [{ day => 14 }],
+ },
postgres => {
- select => "EXTRACT(day FROM me.created_on)",
+ select => "date_part('day', me.created_on)",
where => "me.id = ?",
bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
hri => [{ day => 14 }],
bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
hri => [{ hour => 12 }],
},
+ mysql => {
+ select => "EXTRACT(HOUR FROM me.created_on)",
+ where => "me.id = ?",
+ bind => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
+ hri => [{ hour => 12 }],
+ },
postgres => {
- select => "EXTRACT(hour FROM me.created_on)",
+ select => "date_part('hour', me.created_on)",
where => "me.id = ?",
bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
hri => [{ hour => 12 }],
bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
hri => [{ minute => 12 }],
},
+ mysql => {
+ select => "EXTRACT(MINUTE FROM me.created_on)",
+ where => "me.id = ?",
+ bind => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
+ hri => [{ minute => 12 }],
+ },
postgres => {
- select => "EXTRACT(minute FROM me.created_on)",
+ select => "date_part('minute', me.created_on)",
where => "me.id = ?",
bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
hri => [{ minute => 12 }],
bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
hri => [{ second => 12 }],
},
+ mysql => {
+ select => "EXTRACT(SECOND FROM me.created_on)",
+ where => "me.id = ?",
+ bind => [[{ dbic_colname => 'me.id', sqlt_datatype => 'integer' } => 1 ]],
+ hri => [{ second => 12 }],
+ },
postgres => {
- select => "EXTRACT(second FROM me.created_on)",
+ select => "date_part('second', me.created_on)",
where => "me.id = ?",
bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 1 ]],
hri => [{ second => 12 }],
bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
hri => [{ sec_diff => 2*24*60*60 }],
},
+ mysql => {
+ select => "TIMESTAMPDIFF(SECOND, me.skip_inflation, me.created_on)",
+ where => "me.id = ?",
+ bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
+ hri => [{ sec_diff => 2*24*60*60 }],
+ },
postgres => {
- select => "EXTRACT(epoch FROM (me.created_on::timestamp with time zone - me.skip_inflation::timestamp with time zone))",
+ select => "date_part('EPOCH', me.created_on) - date_part('EPOCH', me.skip_inflation)",
where => "me.id = ?",
bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
hri => [{ sec_diff => 2*24*60*60 }],
bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
hri => [{ day_diff => 2 }],
},
+ mysql => {
+ select => "TIMESTAMPDIFF(DAY, me.skip_inflation, me.created_on)",
+ where => "me.id = ?",
+ bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
+ hri => [{ day_diff => 2 }],
+ },
postgres => {
- select => "EXTRACT(DAY FROM (me.created_on::timestamp with time zone - me.skip_inflation::timestamp with time zone))",
+ select => "date_part('DAY', me.created_on) - date_part('DAY', me.skip_inflation)",
where => "me.id = ?",
bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
hri => [{ day_diff => 2 }],
bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
hri => [{ year => -1 }],
},
+ mysql => {
+ select => "TIMESTAMPDIFF(YEAR, me.created_on, me.starts_at)",
+ where => "me.id = ?",
+ bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
+ hri => [{ year => -1 }],
+ },
+ postgres => {
+ select => "date_part('YEAR', me.starts_at) - date_part('YEAR', me.created_on)",
+ where => "me.id = ?",
+ bind => [[{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
+ hri => [{ year => -1 }],
+ },
oracle => {
select => "TRUNC(MONTHS_BETWEEN(me.starts_at, me.created_on) / 12)",
where => "me.id = ?",
hri => [{ date => '2014-12-14 12:12:12' }],
},
mssql => {
- select => "(DATEADD(year, ?, me.created_on))",
+ select => "(DATEADD(year, CAST(? AS INTEGER), me.created_on))",
where => "me.id = ?",
- bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
+ bind => [[{sqlt_datatype => 'integer'}, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
hri => [{ date => '2014-12-14 12:12:12.000' }],
- skip => 'need working bindtypes',
+ },
+ mysql => {
+ select => "DATE_ADD(me.created_on, INTERVAL ? YEAR)",
+ where => "me.id = ?",
+ bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
+ hri => [{ date => '2014-12-14 12:12:12' }],
+ },
+ postgres => {
+ select => "(me.created_on + ? * interval '1 YEAR')",
+ where => "me.id = ?",
+ bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
+ hri => [{ date => '2014-12-14 12:12:12' }],
+
},
oracle => {
select => "(me.created_on + NUMTOYMINTERVAL(?, 'year'))",
hri => [{ date => '2012-03-14 12:12:12' }],
},
mssql => {
- select => "(DATEADD(month, ?, me.created_on))",
+ select => "(DATEADD(month, CAST(? AS INTEGER), me.created_on))",
where => "me.id = ?",
- bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
+ bind => [[{sqlt_datatype => 'integer'}, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
hri => [{ date => '2012-03-14 12:12:12.000' }],
- skip => 'need working bindtypes',
+ },
+ postgres => {
+ select => "(me.created_on + ? * interval '1 MONTH')",
+ where => "me.id = ?",
+ bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
+ hri => [{ date => '2012-03-14 12:12:12' }],
+ },
+
+ mysql => {
+ select => "DATE_ADD(me.created_on, INTERVAL ? MONTH)",
+ where => "me.id = ?",
+ bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
+ hri => [{ date => '2012-03-14 12:12:12' }],
},
oracle => {
select => "(me.created_on + NUMTOYMINTERVAL(?, 'month'))",
hri => [{ date => '2011-12-17 12:12:12' }],
},
mssql => {
- select => "(DATEADD(dayofyear, ?, me.created_on))",
+ select => "(DATEADD(dayofyear, CAST(? AS INTEGER), me.created_on))",
where => "me.id = ?",
- bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
+ bind => [[{sqlt_datatype => 'integer'}, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
hri => [{ date => '2011-12-17 12:12:12.000' }],
- skip => 'need working bindtypes',
+ },
+ postgres => {
+ select => "(me.created_on + ? * interval '1 DAY')",
+ where => "me.id = ?",
+ bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
+ hri => [{ date => '2011-12-17 12:12:12' }],
+ },
+
+ mysql => {
+ select => "DATE_ADD(me.created_on, INTERVAL ? DAY)",
+ where => "me.id = ?",
+ bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
+ hri => [{ date => '2011-12-17 12:12:12' }],
},
oracle => {
select => "(me.created_on + NUMTODSINTERVAL(?, 'day'))",
hri => [{ date => '2011-12-14 15:12:12' }],
},
mssql => {
- select => "(DATEADD(hour, ?, me.created_on))",
+ select => "(DATEADD(hour, CAST(? AS INTEGER), me.created_on))",
where => "me.id = ?",
- bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
+ bind => [[{sqlt_datatype => 'integer'}, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
hri => [{ date => '2011-12-14 15:12:12.000' }],
- skip => 'need working bindtypes',
+ },
+ postgres => {
+ select => "(me.created_on + ? * interval '1 HOUR')",
+ where => "me.id = ?",
+ bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
+ hri => [{ date => '2011-12-14 15:12:12' }],
+ },
+ mysql => {
+ select => "DATE_ADD(me.created_on, INTERVAL ? HOUR)",
+ where => "me.id = ?",
+ bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
+ hri => [{ date => '2011-12-14 15:12:12' }],
},
oracle => {
select => "(me.created_on + NUMTODSINTERVAL(?, 'hour'))",
hri => [{ date => '2011-12-14 12:15:12' }],
},
mssql => {
- select => "(DATEADD(minute, ?, me.created_on))",
+ select => "(DATEADD(minute, CAST(? AS INTEGER), me.created_on))",
where => "me.id = ?",
- bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
+ bind => [[{sqlt_datatype => 'integer'}, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
hri => [{ date => '2011-12-14 12:15:12.000' }],
- skip => 'need working bindtypes',
+ },
+ postgres => {
+ select => "(me.created_on + ? * interval '1 MINUTE')",
+ where => "me.id = ?",
+ bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
+ hri => [{ date => '2011-12-14 12:15:12' }],
+ },
+ mysql => {
+ select => "DATE_ADD(me.created_on, INTERVAL ? MINUTE)",
+ where => "me.id = ?",
+ bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
+ hri => [{ date => '2011-12-14 12:15:12' }],
},
oracle => {
select => "(me.created_on + NUMTODSINTERVAL(?, 'minute'))",
hri => [{ date => '2011-12-14 12:12:15' }],
},
mssql => {
- select => "(DATEADD(second, ?, me.created_on))",
+ select => "(DATEADD(second, CAST(? AS INTEGER), me.created_on))",
where => "me.id = ?",
- bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
+ bind => [[{sqlt_datatype => 'integer'}, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
hri => [{ date => '2011-12-14 12:12:15.000' }],
- skip => 'need working bindtypes',
+ },
+ postgres => {
+ select => "(me.created_on + ? * interval '1 SECOND')",
+ where => "me.id = ?",
+ bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
+ hri => [{ date => '2011-12-14 12:12:15' }],
+ },
+ mysql => {
+ select => "DATE_ADD(me.created_on, INTERVAL ? SECOND)",
+ where => "me.id = ?",
+ bind => [[unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
+ hri => [{ date => '2011-12-14 12:12:15' }],
},
oracle => {
select => "(me.created_on + NUMTODSINTERVAL(?, 'second'))",
hri => [{ date => '2011-12-15 12:12:15' }],
},
mssql => {
- select => "(DATEADD(second, ?, (DATEADD(dayofyear, ?, me.created_on))))",
+ select => "(DATEADD(second, CAST(? AS INTEGER), (DATEADD(dayofyear, CAST(? AS INTEGER), me.created_on))))",
where => "me.id = ?",
- bind => [[unknown_col, 3 ], [unknown_col, 1], [{dbic_colname => 'me.id', sqlt_datatype => 'integer' }, 2]],
+ bind => [[{sqlt_datatype => 'integer'}, 3 ], [{sqlt_datatype => 'integer'}, 1], [{dbic_colname => 'me.id', sqlt_datatype => 'integer' }, 2]],
hri => [{ date => '2011-12-15 12:12:15.000' }],
- skip => 'need working bindtypes',
+ },
+ postgres => {
+ select => "((me.created_on + ? * interval '1 DAY') + ? * interval '1 SECOND')",
+ where => "me.id = ?",
+ bind => [[unknown_col, 1], [unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
+ hri => [{ date => '2011-12-15 12:12:15' }],
+ },
+ mysql => {
+ select => "DATE_ADD(DATE_ADD(me.created_on, INTERVAL ? DAY), INTERVAL ? SECOND)",
+ where => "me.id = ?",
+ bind => [[unknown_col, 1], [unknown_col, 3], [{dbic_colname => 'me.id', sqlt_datatype => 'integer'} => 2 ]],
+ hri => [{ date => '2011-12-15 12:12:15' }],
},
oracle => {
select => "((me.created_on + NUMTODSINTERVAL(?, 'day')) + NUMTODSINTERVAL(?, 'second'))",
hri => [{ date => '2011-12-15 12:12:15.000000000' }],
},
},
+
+ {
+ msg => '-dt_before works',
+ search => { 'me.created_on' => { -dt_before => '2011-12-14 12:12:12' } },
+ select => [ [ -ident => 'me.created_on' ] ],
+ as => [ 'date' ],
+ sqlite => {
+ select => "me.created_on",
+ where => "me.created_on < ?",
+ bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2011-12-14 12:12:12']],
+ hri => [{ date => '2010-12-14 12:12:12' }],
+ },
+ postgres => {
+ select => "me.created_on",
+ where => "me.created_on < ?",
+ bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2011-12-14 12:12:12']],
+ hri => [{ date => '2010-12-14 12:12:12' }],
+ },
+ mysql => {
+ select => "me.created_on",
+ where => "me.created_on < ?",
+ bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2011-12-14 12:12:12']],
+ hri => [{ date => '2010-12-14 12:12:12' }],
+ },
+ mssql => {
+ select => "me.created_on",
+ where => "me.created_on < ?",
+ bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2011-12-14 12:12:12']],
+ hri => [{ date => '2010-12-14 12:12:12' }],
+ },
+ oracle => {
+ select => "me.created_on",
+ where => "me.created_on < ?",
+ bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2011-12-14 12:12:12']],
+ hri => [{ date => '2010-12-14 12:12:12' }],
+ },
+ },
+
+ {
+ msg => '-dt_on_or_before works',
+ search => { 'me.created_on' => { -dt_on_or_before => '2011-12-14 12:12:12' } },
+ select => [ [ -ident => 'me.created_on' ] ],
+ as => [ 'date' ],
+ sqlite => {
+ select => "me.created_on",
+ where => "me.created_on <= ?",
+ bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2011-12-14 12:12:12']],
+ hri => [{ date => '2010-12-14 12:12:12' }, { date => '2011-12-14 12:12:12' }],
+ },
+ postgres => {
+ select => "me.created_on",
+ where => "me.created_on <= ?",
+ bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2011-12-14 12:12:12']],
+ hri => [{ date => '2010-12-14 12:12:12' }, { date => '2011-12-14 12:12:12' }],
+ },
+ mysql => {
+ select => "me.created_on",
+ where => "me.created_on <= ?",
+ bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2011-12-14 12:12:12']],
+ hri => [{ date => '2010-12-14 12:12:12' }, { date => '2011-12-14 12:12:12' }],
+ },
+ mssql => {
+ select => "me.created_on",
+ where => "me.created_on <= ?",
+ bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2011-12-14 12:12:12']],
+ hri => [{ date => '2010-12-14 12:12:12' }, { date => '2011-12-14 12:12:12' }],
+ },
+ oracle => {
+ select => "me.created_on",
+ where => "me.created_on <= ?",
+ bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2011-12-14 12:12:12']],
+ hri => [{ date => '2010-12-14 12:12:12' }, { date => '2011-12-14 12:12:12' }],
+ },
+ },
+
+ {
+ msg => '-dt_after works',
+ search => { 'me.created_on' => { -dt_after => '2010-12-14 12:12:12' } },
+ select => [ [ -ident => 'me.created_on' ] ],
+ as => [ 'date' ],
+ sqlite => {
+ select => "me.created_on",
+ where => "me.created_on > ?",
+ bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12']],
+ hri => [{ date => '2011-12-14 12:12:12' }],
+ },
+ postgres => {
+ select => "me.created_on",
+ where => "me.created_on > ?",
+ bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12']],
+ hri => [{ date => '2011-12-14 12:12:12' }],
+ },
+ mysql => {
+ select => "me.created_on",
+ where => "me.created_on > ?",
+ bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12']],
+ hri => [{ date => '2011-12-14 12:12:12' }],
+ },
+ mssql => {
+ select => "me.created_on",
+ where => "me.created_on > ?",
+ bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12']],
+ hri => [{ date => '2011-12-14 12:12:12' }],
+ },
+ oracle => {
+ select => "me.created_on",
+ where => "me.created_on > ?",
+ bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12']],
+ hri => [{ date => '2011-12-14 12:12:12' }],
+ },
+ },
+
+ {
+ msg => '-dt_on_or_after works',
+ search => { 'me.created_on' => { -dt_on_or_after => '2010-12-14 12:12:12' } },
+ select => [ [ -ident => 'me.created_on' ] ],
+ as => [ 'date' ],
+ sqlite => {
+ select => "me.created_on",
+ where => "me.created_on >= ?",
+ bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12']],
+ hri => [{ date => '2010-12-14 12:12:12' }, { date => '2011-12-14 12:12:12' }],
+ },
+ postgres => {
+ select => "me.created_on",
+ where => "me.created_on >= ?",
+ bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12']],
+ hri => [{ date => '2010-12-14 12:12:12' }, { date => '2011-12-14 12:12:12' }],
+ },
+ mysql => {
+ select => "me.created_on",
+ where => "me.created_on >= ?",
+ bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12']],
+ hri => [{ date => '2010-12-14 12:12:12' }, { date => '2011-12-14 12:12:12' }],
+ },
+ mssql => {
+ select => "me.created_on",
+ where => "me.created_on >= ?",
+ bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12']],
+ hri => [{ date => '2010-12-14 12:12:12' }, { date => '2011-12-14 12:12:12' }],
+ },
+ oracle => {
+ select => "me.created_on",
+ where => "me.created_on >= ?",
+ bind => [[{dbic_colname => 'me.created_on', sqlt_datatype => 'timestamp' }, '2010-12-14 12:12:12']],
+ hri => [{ date => '2010-12-14 12:12:12' }, { date => '2011-12-14 12:12:12' }],
+ },
+ },
+
);
for my $t (@tests) {
DB_TEST:
for my $db (keys %rs) {
my $db_test = $t->{$db};
- next DB_TEST unless $db_test;
+ unless ($db_test) {
+ ok 0, "$t->{msg} ($db not tested!)";
+ next DB_TEST;
+ }
my ($r, $my_rs);