From e4fa51953ec018c733040ccf9a478b2e8aac0bac Mon Sep 17 00:00:00 2001 From: "lpsolit%gmail.com" <> Date: Mon, 14 Nov 2005 01:32:10 +0000 Subject: Bug 301062: [PostgreSQL] whine.pl fails when using PostgreSQL 8.0.x - Patch by Frédéric Buclin r=mkanat r=manu a=justdave MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit --- Bugzilla/DB.pm | 4 +-- Bugzilla/DB/Mysql.pm | 4 +-- Bugzilla/DB/Pg.pm | 4 +-- Bugzilla/Search.pm | 2 +- Bugzilla/Token.pm | 2 +- contrib/sendunsentbugmail.pl | 2 +- sanitycheck.cgi | 4 +-- userprefs.cgi | 2 +- whine.pl | 65 ++++++++++++++++++++++---------------------- 9 files changed, 44 insertions(+), 45 deletions(-) diff --git a/Bugzilla/DB.pm b/Bugzilla/DB.pm index f031637d5..09c941ec8 100644 --- a/Bugzilla/DB.pm +++ b/Bugzilla/DB.pm @@ -1146,8 +1146,8 @@ formatted SQL command have prefix C. All other methods have prefix C. Description: Outputs proper SQL syntax for a time interval function. Abstract method, should be overriden by database specific code. - Params: $interval = the time interval requested (e.g. '30 minutes') - (scalar) + Params: $interval - the time interval requested (e.g. '30') (integer) + $units - the units the interval is in (e.g. 'MINUTE') (string) Returns: formatted SQL for interval function (scalar) =item C diff --git a/Bugzilla/DB/Mysql.pm b/Bugzilla/DB/Mysql.pm index 952d49ff9..a8f78bb9a 100644 --- a/Bugzilla/DB/Mysql.pm +++ b/Bugzilla/DB/Mysql.pm @@ -148,9 +148,9 @@ sub sql_date_format { } sub sql_interval { - my ($self, $interval) = @_; + my ($self, $interval, $units) = @_; - return "INTERVAL $interval"; + return "INTERVAL $interval $units"; } sub sql_position { diff --git a/Bugzilla/DB/Pg.pm b/Bugzilla/DB/Pg.pm index ff1e6abae..f4569b9fd 100644 --- a/Bugzilla/DB/Pg.pm +++ b/Bugzilla/DB/Pg.pm @@ -135,9 +135,9 @@ sub sql_date_format { } sub sql_interval { - my ($self, $interval) = @_; + my ($self, $interval, $units) = @_; - return "INTERVAL '$interval'"; + return "$interval * INTERVAL '1 $units'"; } sub sql_string_concat { diff --git a/Bugzilla/Search.pm b/Bugzilla/Search.pm index 468457844..cc24d11e0 100644 --- a/Bugzilla/Search.pm +++ b/Bugzilla/Search.pm @@ -996,7 +996,7 @@ sub init { $unitinterval = 'YEAR'; } my $cutoff = "NOW() - " . - $dbh->sql_interval("$quantity $unitinterval"); + $dbh->sql_interval($quantity, $unitinterval); my $assigned_fieldid = get_field_id('assigned_to'); push(@supptables, "LEFT JOIN longdescs AS comment_$table " . "ON comment_$table.who = bugs.assigned_to " . diff --git a/Bugzilla/Token.pm b/Bugzilla/Token.pm index 6a263cbee..42dca47d6 100644 --- a/Bugzilla/Token.pm +++ b/Bugzilla/Token.pm @@ -103,7 +103,7 @@ sub IssuePasswordToken { ON tokens.userid = profiles.userid AND tokens.tokentype = 'password' AND tokens.issuedate > NOW() - " . - $dbh->sql_interval('10 MINUTE') . " + $dbh->sql_interval(10, 'MINUTE') . " WHERE " . $dbh->sql_istrcmp('login_name', $quotedloginname)); my ($userid, $toosoon) = &::FetchSQLData(); diff --git a/contrib/sendunsentbugmail.pl b/contrib/sendunsentbugmail.pl index 9453a6538..bf31f2d4b 100644 --- a/contrib/sendunsentbugmail.pl +++ b/contrib/sendunsentbugmail.pl @@ -33,7 +33,7 @@ my $dbh = Bugzilla->dbh; SendSQL("SELECT bug_id FROM bugs WHERE lastdiffed IS NULL OR lastdiffed < delta_ts AND delta_ts < NOW() - " - . $dbh->sql_interval('30 minute') . + . $dbh->sql_interval(30, 'MINUTE') . " ORDER BY bug_id"); my @list; while (MoreSQLData()) { diff --git a/sanitycheck.cgi b/sanitycheck.cgi index 317464749..7520430a2 100755 --- a/sanitycheck.cgi +++ b/sanitycheck.cgi @@ -196,7 +196,7 @@ if (defined $cgi->param('rescanallBugMail')) { Status("OK, now attempting to send unsent mail"); SendSQL("SELECT bug_id FROM bugs WHERE (lastdiffed IS NULL OR lastdiffed < delta_ts) AND - delta_ts < now() - " . $dbh->sql_interval('30 minute') . + delta_ts < now() - " . $dbh->sql_interval(30, 'MINUTE') . " ORDER BY bug_id"); my @list; while (MoreSQLData()) { @@ -808,7 +808,7 @@ Status("Checking for unsent mail"); SendSQL("SELECT bug_id " . "FROM bugs WHERE (lastdiffed IS NULL OR lastdiffed < delta_ts) AND " . - "delta_ts < now() - " . $dbh->sql_interval('30 minute') . + "delta_ts < now() - " . $dbh->sql_interval(30, 'MINUTE') . " ORDER BY bug_id"); while (@row = FetchSQLData()) { diff --git a/userprefs.cgi b/userprefs.cgi index 101dead40..24c9136f4 100755 --- a/userprefs.cgi +++ b/userprefs.cgi @@ -53,7 +53,7 @@ sub DoAccount { if(Param('allowemailchange')) { my @token = $dbh->selectrow_array( "SELECT tokentype, issuedate + " . - $dbh->sql_interval('3 DAY') . ", eventdata + $dbh->sql_interval(3, 'DAY') . ", eventdata FROM tokens WHERE userid = ? AND tokentype LIKE 'email%' diff --git a/whine.pl b/whine.pl index 937cbbace..52e584011 100755 --- a/whine.pl +++ b/whine.pl @@ -34,6 +34,7 @@ use Bugzilla::Constants; use Bugzilla::Search; use Bugzilla::User; use Bugzilla::BugMail; +use Bugzilla::Util; # create some handles that we'll need my $template = Bugzilla->template; @@ -164,20 +165,21 @@ 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 = CURRENT_DATE + " . + $dbh->sql_interval('?', '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 - my $nextdate = &get_next_date($day); - $sth = $dbh->prepare( "UPDATE whine_schedules " . - "SET run_next = ? + " . - $dbh->sql_interval('? HOUR') . - " WHERE id = ?"); - $sth->execute($nextdate, $time, $schedule_id); + $day = &get_next_date($day); + $sth = $dbh->prepare("UPDATE whine_schedules " . + "SET run_next = CURRENT_DATE + " . + $dbh->sql_interval('?', 'DAY') . " + " . + $dbh->sql_interval('?', 'HOUR') . + " WHERE id = ?"); + $sth->execute($day, $time, $schedule_id); } } @@ -189,10 +191,11 @@ while (my ($schedule_id, $day, $time) = $sched_h->fetchrow_array) { # midnight my $target_time = ($time =~ /^\d+$/) ? $time : 0; - $sth = $dbh->prepare( "UPDATE whine_schedules " . - "SET run_next = ? + " . - $dbh->sql_interval('? HOUR') . - " WHERE id = ?"); + $sth = $dbh->prepare("UPDATE whine_schedules " . + "SET run_next = CURRENT_DATE + " . + $dbh->sql_interval('?', 'DAY') . " + " . + $dbh->sql_interval('?', 'HOUR') . + " WHERE id = ?"); $sth->execute($target_date, $target_time, $schedule_id); } } @@ -592,23 +595,25 @@ sub reset_timer { my $nextdate = &get_next_date($run_day); - $sth = $dbh->prepare( "UPDATE whine_schedules " . - "SET run_next = ? + " . - $dbh->sql_interval('? HOUR') . - " WHERE id = ?"); + $sth = $dbh->prepare("UPDATE whine_schedules " . + "SET run_next = CURRENT_DATE + " . + $dbh->sql_interval('?', 'DAY') . " + " . + $dbh->sql_interval('?', 'HOUR') . + " WHERE id = ?"); $sth->execute($nextdate, $target_time, $schedule_id); return; } - # Scheduling is done in terms of whole minutes, so we use subtraction to - # drop the seconds from the time. 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); + $next_run = format_time($next_run, "%Y-%m-%d %R"); + $sth = $dbh->prepare("UPDATE whine_schedules " . - "SET run_next = NOW() + " . - $dbh->sql_interval('? MINUTE') . " - " . - $dbh->sql_interval('SECOND(NOW()) SECOND') . - " WHERE id = ?"); - $sth->execute($minute_offset, $schedule_id); + "SET run_next = ? WHERE id = ?"); + $sth->execute($next_run, $schedule_id); } else { # The minute offset is zero or less, which is not supposed to happen. # complain to STDERR @@ -632,7 +637,7 @@ sub null_schedule { # time a schedule should run, excluding today # # It takes a run_day argument (see check_today, above, for an explanation), -# and returns an SQL date +# and returns an integer, representing a number of days. sub get_next_date { my $day = shift; @@ -685,11 +690,5 @@ sub get_next_date { $add_days += $daysinmonth[$now_month]; } } - - # Get a date in whatever format the database will accept - $sth = $dbh->prepare("SELECT CURRENT_DATE() + " . - $dbh->sql_interval('? DAY')); - $sth->execute($add_days); - return $sth->fetch->[0]; + return $add_days; } - -- cgit v1.2.3-24-g4f1b