Bug 602165: Change sql_interval to sql_date_math, in preparation for

MS-SQL and SQLite support.

7 files changed, 51 insertions, 34 deletions

diff --git a/Bugzilla/Auth/Persist/Cookie.pm b/Bugzilla/Auth/Persist/Cookie.pm
index 232212075..57fa9624e 100644
--- a/Bugzilla/Auth/Persist/Cookie.pm
+++ b/Bugzilla/Auth/Persist/Cookie.pm
@@ -69,8 +69,9 @@ sub persist_login {
 
     # Issuing a new cookie is a good time to clean up the old
     # cookies.
-    $dbh->do("DELETE FROM logincookies WHERE lastused < LOCALTIMESTAMP(0) - "
-             . $dbh->sql_interval(MAX_LOGINCOOKIE_AGE, 'DAY'));
+    $dbh->do("DELETE FROM logincookies WHERE lastused < "
+             . $dbh->sql_date_math('LOCALTIMESTAMP(0)', '-',
+                                   MAX_LOGINCOOKIE_AGE, 'DAY'));
 
     $dbh->bz_commit_transaction();
 
diff --git a/Bugzilla/DB.pm b/Bugzilla/DB.pm
index 6f1d3e6de..27644fba5 100644
--- a/Bugzilla/DB.pm
+++ b/Bugzilla/DB.pm
@@ -307,7 +307,7 @@ EOT
 
 # List of abstract methods we are checking the derived class implements
 our @_abstract_methods = qw(new sql_regexp sql_not_regexp sql_limit sql_to_days
-                            sql_date_format sql_interval bz_explain
+                            sql_date_format sql_date_math bz_explain
                             sql_group_concat);
 
 # This overridden import method will check implementation of inherited classes
@@ -1904,13 +1904,13 @@ Formatted SQL for date formatting (scalar)
 
 =back
 
-=item C<sql_interval>
+=item C<sql_date_math>
 
 =over
 
 =item B<Description>
 
-Outputs proper SQL syntax for a time interval function.
+Outputs proper SQL syntax for adding some amount of time to a date.
 
 Abstract method, should be overridden by database specific code.
 
@@ -1918,15 +1918,28 @@ Abstract method, should be overridden by database specific code.
 
 =over
 
-=item C<$interval> - the time interval requested (e.g. '30') (integer)
+=item C<$date>
 
-=item C<$units> - the units the interval is in (e.g. 'MINUTE') (string)
+C<string> The date being added to or subtracted from.
+
+=item C<$operator>
+
+C<string> Either C<-> or C<+>, depending on whether you're subtracting
+or adding.
+
+=item C<$interval>
+
+C<integer> The time interval you're adding or subtracting (e.g. C<30>)
+
+=item C<$units> 
+
+C<string> the units the interval is in (e.g. 'MINUTE')
 
 =back
 
 =item B<Returns>
 
-Formatted SQL for interval function (scalar)
+Formatted SQL for adding or subtracting a date and some amount of time (scalar)
 
 =back
 
diff --git a/Bugzilla/DB/Mysql.pm b/Bugzilla/DB/Mysql.pm
index 1829bc450..7b7e4e9b9 100644
--- a/Bugzilla/DB/Mysql.pm
+++ b/Bugzilla/DB/Mysql.pm
@@ -226,10 +226,10 @@ sub sql_date_format {
     return "DATE_FORMAT($date, " . $self->quote($format) . ")";
 }
 
-sub sql_interval {
-    my ($self, $interval, $units) = @_;
+sub sql_date_math {
+    my ($self, $date, $operator, $interval, $units) = @_;
     
-    return "INTERVAL $interval $units";
+    return "$date $operator INTERVAL $interval $units";
 }
 
 sub sql_iposition {
diff --git a/Bugzilla/DB/Oracle.pm b/Bugzilla/DB/Oracle.pm
index a7ac6e93e..9fdacf24c 100644
--- a/Bugzilla/DB/Oracle.pm
+++ b/Bugzilla/DB/Oracle.pm
@@ -197,13 +197,15 @@ sub sql_date_format {
     return "TO_CHAR($date, " . $self->quote($format) . ")";
 }
 
-sub sql_interval {
-    my ($self, $interval, $units) = @_;
+sub sql_date_math {
+    my ($self, $date, $operator, $interval, $units) = @_;
+    my $time_sql;
     if ($units =~ /YEAR|MONTH/i) {
-        return "NUMTOYMINTERVAL($interval,'$units')";
+        $time_sql = "NUMTOYMINTERVAL($interval,'$units')";
     } else{
-        return "NUMTODSINTERVAL($interval,'$units')";
+        $time_sql = "NUMTODSINTERVAL($interval,'$units')";
     }
+   return "$date $operator $time_sql";
 }
 
 sub sql_position {
diff --git a/Bugzilla/DB/Pg.pm b/Bugzilla/DB/Pg.pm
index 0373fb1ce..c1c656a69 100644
--- a/Bugzilla/DB/Pg.pm
+++ b/Bugzilla/DB/Pg.pm
@@ -177,10 +177,10 @@ sub sql_date_format {
     return "TO_CHAR($date, " . $self->quote($format) . ")";
 }
 
-sub sql_interval {
-    my ($self, $interval, $units) = @_;
+sub sql_date_math {
+    my ($self, $date, $operator, $interval, $units) = @_;
     
-    return "$interval * INTERVAL '1 $units'";
+    return "$date $operator $interval * INTERVAL '1 $units'";
 }
 
 sub sql_string_concat {
diff --git a/Bugzilla/Token.pm b/Bugzilla/Token.pm
index 06e95bb50..e15991f37 100644
--- a/Bugzilla/Token.pm
+++ b/Bugzilla/Token.pm
@@ -63,13 +63,14 @@ sub issue_new_user_account_token {
     # But to prevent using this way to mailbomb an email address, make sure
     # the last request is at least 10 minutes old before sending a new email.
 
-    my $pending_requests =
-        $dbh->selectrow_array('SELECT COUNT(*)
-                                 FROM tokens
-                                WHERE tokentype = ?
-                                  AND ' . $dbh->sql_istrcmp('eventdata', '?') . '
-                                  AND issuedate > NOW() - ' . $dbh->sql_interval(10, 'MINUTE'),
-                               undef, ('account', $login_name));
+    my $pending_requests = $dbh->selectrow_array(
+        'SELECT COUNT(*)
+           FROM tokens
+          WHERE tokentype = ?
+                AND ' . $dbh->sql_istrcmp('eventdata', '?') . '
+                AND issuedate > '
+                    . $dbh->sql_date_math('NOW()', '-', 10, 'MINUTE'),
+        undef, ('account', $login_name));
 
     ThrowUserError('too_soon_for_new_token', {'type' => 'account'}) if $pending_requests;
 
@@ -131,13 +132,12 @@ sub IssuePasswordToken {
     my $user = shift;
     my $dbh = Bugzilla->dbh;
 
-    my $too_soon =
-        $dbh->selectrow_array('SELECT 1 FROM tokens
-                                WHERE userid = ?
-                                  AND tokentype = ?
-                                  AND issuedate > NOW() - ' .
-                                      $dbh->sql_interval(10, 'MINUTE'),
-                                undef, ($user->id, 'password'));
+    my $too_soon = $dbh->selectrow_array(
+        'SELECT 1 FROM tokens
+          WHERE userid = ? AND tokentype = ?
+                AND issuedate > ' 
+                    . $dbh->sql_date_math('NOW()', '-', 10, 'MINUTE'),
+        undef, ($user->id, 'password'));
 
     ThrowUserError('too_soon_for_new_token', {'type' => 'password'}) if $too_soon;
 
diff --git a/Bugzilla/User.pm b/Bugzilla/User.pm
index 595964bf9..8f056dca6 100644
--- a/Bugzilla/User.pm
+++ b/Bugzilla/User.pm
@@ -1791,12 +1791,13 @@ sub clear_login_failures {
 sub account_ip_login_failures {
     my $self = shift;
     my $dbh = Bugzilla->dbh;
-    my $time = $dbh->sql_interval(LOGIN_LOCKOUT_INTERVAL, 'MINUTE');
+    my $time = $dbh->sql_date_math('LOCALTIMESTAMP(0)', '-', 
+                                   LOGIN_LOCKOUT_INTERVAL, 'MINUTE');
     my $ip_addr = remote_ip();
     trick_taint($ip_addr);
     $self->{account_ip_login_failures} ||= Bugzilla->dbh->selectall_arrayref(
         "SELECT login_time, ip_addr, user_id FROM login_failure
-          WHERE user_id = ? AND login_time > LOCALTIMESTAMP(0) - $time
+          WHERE user_id = ? AND login_time > $time
                 AND ip_addr = ?
        ORDER BY login_time", {Slice => {}}, $self->id, $ip_addr);
     return $self->{account_ip_login_failures};