diff options
| -rw-r--r-- | Bugzilla/Auth/Persist/Cookie.pm | 5 | ||||
| -rw-r--r-- | Bugzilla/DB.pm | 25 | ||||
| -rw-r--r-- | Bugzilla/DB/Mysql.pm | 6 | ||||
| -rw-r--r-- | Bugzilla/DB/Oracle.pm | 10 | ||||
| -rw-r--r-- | Bugzilla/DB/Pg.pm | 6 | ||||
| -rw-r--r-- | Bugzilla/Token.pm | 28 | ||||
| -rw-r--r-- | Bugzilla/User.pm | 5 | ||||
| -rwxr-xr-x | contrib/sendunsentbugmail.pl | 3 | ||||
| -rwxr-xr-x | duplicates.cgi | 4 | ||||
| -rwxr-xr-x | sanitycheck.cgi | 8 | ||||
| -rwxr-xr-x | userprefs.cgi | 5 | ||||
| -rwxr-xr-x | whine.pl | 44 |
12 files changed, 86 insertions, 63 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}; diff --git a/contrib/sendunsentbugmail.pl b/contrib/sendunsentbugmail.pl index ec92a97a0..6ddbd2e4c 100755 --- a/contrib/sendunsentbugmail.pl +++ b/contrib/sendunsentbugmail.pl @@ -35,7 +35,8 @@ my $list = $dbh->selectcol_arrayref( 'SELECT bug_id FROM bugs WHERE lastdiffed IS NULL OR lastdiffed < delta_ts - AND delta_ts < NOW() - ' . $dbh->sql_interval(30, 'MINUTE') . + AND delta_ts < ' + . $dbh->sql_date_math('NOW()', '-', 30, 'MINUTE') . ' ORDER BY bug_id'); if (scalar(@$list) > 0) { diff --git a/duplicates.cgi b/duplicates.cgi index 2a52742c6..798599ed0 100755 --- a/duplicates.cgi +++ b/duplicates.cgi @@ -181,8 +181,8 @@ my %since_dups = @{$dbh->selectcol_arrayref( FROM duplicates INNER JOIN bugs_activity ON bugs_activity.bug_id = duplicates.dupe WHERE added = 'DUPLICATE' AND fieldid = ? - AND bug_when >= LOCALTIMESTAMP(0) - " - . $dbh->sql_interval('?', 'DAY') . + AND bug_when >= " + . $dbh->sql_date_math('LOCALTIMESTAMP(0)', '-', '?', 'DAY') . " GROUP BY dupe_of", {Columns=>[1,2]}, $reso_field_id, $changedsince)}; add_indirect_dups(\%since_dups, \%dupe_relation); diff --git a/sanitycheck.cgi b/sanitycheck.cgi index 63872bd49..a4f9832b0 100755 --- a/sanitycheck.cgi +++ b/sanitycheck.cgi @@ -229,14 +229,14 @@ if ($cgi->param('rescanallBugMail')) { require Bugzilla::BugMail; Status('send_bugmail_start'); - my $time = $dbh->sql_interval(30, 'MINUTE'); + my $time = $dbh->sql_date_math('NOW()', '-', 30, 'MINUTE'); my $list = $dbh->selectcol_arrayref(qq{ SELECT bug_id FROM bugs WHERE (lastdiffed IS NULL OR lastdiffed < delta_ts) - AND delta_ts < now() - $time + AND delta_ts < $time ORDER BY bug_id}); Status('send_bugmail_status', {bug_count => scalar(@$list)}); @@ -857,12 +857,12 @@ BugCheck("bugs Status('unsent_bugmail_check'); -my $time = $dbh->sql_interval(30, 'MINUTE'); +my $time = $dbh->sql_date_math('NOW()', '-', 30, 'MINUTE'); my $badbugs = $dbh->selectcol_arrayref(qq{ SELECT bug_id FROM bugs WHERE (lastdiffed IS NULL OR lastdiffed < delta_ts) - AND delta_ts < now() - $time + AND delta_ts < $time ORDER BY bug_id}); diff --git a/userprefs.cgi b/userprefs.cgi index d15bcd13a..68c7d2748 100755 --- a/userprefs.cgi +++ b/userprefs.cgi @@ -57,8 +57,9 @@ sub DoAccount { Bugzilla::Token::CleanTokenTable(); my @token = $dbh->selectrow_array( - "SELECT tokentype, issuedate + " . - $dbh->sql_interval(MAX_TOKEN_AGE, 'DAY') . ", eventdata + "SELECT tokentype, " . + $dbh->sql_date_math('issuedate', '+', MAX_TOKEN_AGE, 'DAY') + . ", eventdata FROM tokens WHERE userid = ? AND tokentype LIKE 'email%' @@ -150,20 +150,22 @@ while (my ($schedule_id, $day, $time) = $sched_h->fetchrow_array) { # A time greater than now means it still has to run today elsif ($time >= $now_hour) { # set it to today + number of hours - $sth = $dbh->prepare("UPDATE whine_schedules " . - "SET run_next = CURRENT_DATE + " . - $dbh->sql_interval('?', 'HOUR') . - " WHERE id = ?"); + $sth = $dbh->prepare( + "UPDATE whine_schedules " . + "SET run_next = " . + $dbh->sql_date_math('CURRENT_DATE', '+', '?', 'HOUR') . + " WHERE id = ?"); $sth->execute($time, $schedule_id); } # the target time is less than the current time else { # set it for the next applicable day $day = &get_next_date($day); + my $run_next = $dbh->sql_date_math('(' + . $dbh->sql_date_math('CURRENT_DATE', '+', '?', 'DAY') + . ')', '+', '?', 'HOUR'); $sth = $dbh->prepare("UPDATE whine_schedules " . - "SET run_next = (CURRENT_DATE + " . - $dbh->sql_interval('?', 'DAY') . ") + " . - $dbh->sql_interval('?', 'HOUR') . - " WHERE id = ?"); + "SET run_next = $run_next + WHERE id = ?"); $sth->execute($day, $time, $schedule_id); } @@ -176,11 +178,12 @@ while (my ($schedule_id, $day, $time) = $sched_h->fetchrow_array) { # midnight my $target_time = ($time =~ /^\d+$/) ? $time : 0; + my $run_next = $dbh->sql_date_math('(' + . $dbh->sql_date_math('CURRENT_DATE', '+', '?', 'DAY') + . ')', '+', '?', 'HOUR'); $sth = $dbh->prepare("UPDATE whine_schedules " . - "SET run_next = (CURRENT_DATE + " . - $dbh->sql_interval('?', 'DAY') . ") + " . - $dbh->sql_interval('?', 'HOUR') . - " WHERE id = ?"); + "SET run_next = $run_next + WHERE id = ?"); $sth->execute($target_date, $target_time, $schedule_id); } } @@ -584,21 +587,22 @@ sub reset_timer { my $target_time = ($run_time =~ /^\d+$/) ? $run_time : 0; my $nextdate = &get_next_date($run_day); - + my $run_next = $dbh->sql_date_math('(' + . $dbh->sql_date_math('CURRENT_DATE', '+', '?', 'DAY') + . ')', '+', '?', 'HOUR'); $sth = $dbh->prepare("UPDATE whine_schedules " . - "SET run_next = (CURRENT_DATE + " . - $dbh->sql_interval('?', 'DAY') . ") + " . - $dbh->sql_interval('?', 'HOUR') . - " WHERE id = ?"); + "SET run_next = $run_next + WHERE id = ?"); $sth->execute($nextdate, $target_time, $schedule_id); return; } if ($minute_offset > 0) { # Scheduling is done in terms of whole minutes. - my $next_run = $dbh->selectrow_array('SELECT NOW() + ' . - $dbh->sql_interval('?', 'MINUTE'), - undef, $minute_offset); + + my $next_run = $dbh->selectrow_array( + 'SELECT ' . $dbh->sql_date_math('NOW()', '+', '?', 'MINUTE'), + undef, $minute_offset); $next_run = format_time($next_run, "%Y-%m-%d %R"); $sth = $dbh->prepare("UPDATE whine_schedules " . |