add on_or_(before|after) ops

diff --git a/lib/DBIx/Class/SQLMaker.pm b/lib/DBIx/Class/SQLMaker.pm
index 93aa7c9..171f8e2 100644 (file)
--- a/lib/DBIx/Class/SQLMaker.pm
+++ b/lib/DBIx/Class/SQLMaker.pm
@@ -197,6 +197,7 @@ sub new {
     { regex => qr/^ dt_diff $/xi, handler => '_where_op_DIFF_DATETIME' },
     { regex => qr/^ dt_add  $/xi, handler => '_where_op_ADD_DATETIME' },
     { regex => qr/^ dt_now  $/xi, handler => '_where_op_DATETIME_NOW' },
+    { regex => qr/^ dt_(:?on_or_)?(:?before|after)  $/xi, handler => '_where_op_CIRCA_DATETIME' },
     map +{ regex => qr/^ dt_$_ $/xi, handler => '_where_op_GET_DATETIME_'.uc($_) },
       qw(year month day hour minute second)
   );

diff --git a/lib/DBIx/Class/SQLMaker/DateOps.pm b/lib/DBIx/Class/SQLMaker/DateOps.pm
index 6826f61..a165109 100644 (file)
--- a/lib/DBIx/Class/SQLMaker/DateOps.pm
+++ b/lib/DBIx/Class/SQLMaker/DateOps.pm
@@ -251,4 +251,34 @@ sub _where_op_DIFF_DATETIME {
   return $self->_datetime_diff_sql($part, $all_sql[0], $all_sql[1]), @all_bind
 }
 
+sub _where_op_CIRCA_DATETIME {
+  my ($self) = @_;
+
+  my ($k, $op, $val);
+
+  if (@_ == 3) {
+     $op = $_[1];
+     $val = $_[2];
+     $k = '';
+  } elsif (@_ == 4) {
+     $k = $_[1];
+     $op = $_[2];
+     $val = $_[3];
+  }
+
+  my ($sql, @bind) = $self->_dt_arg_transform($k, $val);
+
+  my ($equal, $before, $after) = $op =~ /dt_(on_or_)?(before)?(after)?/;
+  my $sym = $before
+   ? '<'
+   : '>'
+  ;
+
+  $sym .= $equal
+   ? '='
+   : ''
+  ;
+  return "$k $sym $sql", @bind;
+}
+
 1;

diff --git a/t/sqlmaker/op_dt.t b/t/sqlmaker/op_dt.t
index 31c3279..5b40621 100644 (file)
--- a/t/sqlmaker/op_dt.t
+++ b/t/sqlmaker/op_dt.t
@@ -853,6 +853,155 @@ my @tests = (
       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) {