From 02a049703de6055f09f44f89a5a821814696ce32 Mon Sep 17 00:00:00 2001 From: Max Kanat-Alexander Date: Thu, 7 Oct 2010 03:58:28 -0700 Subject: Bug 602165: Change sql_interval to sql_date_math, in preparation for MS-SQL and SQLite support. --- Bugzilla/Auth/Persist/Cookie.pm | 5 +++-- Bugzilla/DB.pm | 25 +++++++++++++++++++------ Bugzilla/DB/Mysql.pm | 6 +++--- Bugzilla/DB/Oracle.pm | 10 ++++++---- Bugzilla/DB/Pg.pm | 6 +++--- Bugzilla/Token.pm | 28 ++++++++++++++-------------- Bugzilla/User.pm | 5 +++-- 7 files changed, 51 insertions(+), 34 deletions(-) (limited to 'Bugzilla') 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 +=item C =over =item B -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 The date being added to or subtracted from. + +=item C<$operator> + +C Either C<-> or C<+>, depending on whether you're subtracting +or adding. + +=item C<$interval> + +C The time interval you're adding or subtracting (e.g. C<30>) + +=item C<$units> + +C the units the interval is in (e.g. 'MINUTE') =back =item B -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}; -- cgit v1.2.3-24-g4f1b