From 7cb62ca9a2742e10334cba6bf965a5c0d8ad40ba Mon Sep 17 00:00:00 2001 From: "mkanat%kerio.com" <> Date: Thu, 3 Mar 2005 15:19:09 +0000 Subject: Bug: 284244: DATE_SUB and DATE_ADD are not ANSI SQL Patch By Tomas Kopal r=wicked, a=justdave --- Bugzilla/Search.pm | 2 +- Bugzilla/Token.pm | 4 ++-- contrib/sendunsentbugmail.pl | 2 +- sanitycheck.cgi | 8 ++++---- whine.pl | 39 +++++++++++++++++++-------------------- 5 files changed, 27 insertions(+), 28 deletions(-) diff --git a/Bugzilla/Search.pm b/Bugzilla/Search.pm index f2e630784..1a0cb9a7f 100644 --- a/Bugzilla/Search.pm +++ b/Bugzilla/Search.pm @@ -922,7 +922,7 @@ sub init { } elsif ($unit eq 'y') { $unitinterval = 'YEAR'; } - my $cutoff = "DATE_SUB(NOW(), " . + my $cutoff = "NOW() - " . $dbh->sql_interval("$quantity $unitinterval"); my $assigned_fieldid = &::GetFieldID('assigned_to'); push(@supptables, "LEFT JOIN longdescs comment_$table " . diff --git a/Bugzilla/Token.pm b/Bugzilla/Token.pm index 73f074185..feca00c3b 100644 --- a/Bugzilla/Token.pm +++ b/Bugzilla/Token.pm @@ -119,8 +119,8 @@ sub IssuePasswordToken { LEFT JOIN tokens ON tokens.userid = profiles.userid AND tokens.tokentype = 'password' - AND tokens.issuedate > DATE_SUB(NOW(), " . - $dbh->sql_interval('10 MINUTE') . ") + AND tokens.issuedate > NOW() - " . + $dbh->sql_interval('10 MINUTE') . " WHERE login_name = $quotedloginname"); my ($userid, $toosoon) = &::FetchSQLData(); diff --git a/contrib/sendunsentbugmail.pl b/contrib/sendunsentbugmail.pl index b17289592..35b6a4941 100644 --- a/contrib/sendunsentbugmail.pl +++ b/contrib/sendunsentbugmail.pl @@ -31,7 +31,7 @@ use Bugzilla::BugMail; my $dbh = Bugzilla->dbh; SendSQL("SELECT bug_id FROM bugs WHERE lastdiffed < delta_ts AND - delta_ts < date_sub(now(), " . $dbh->sql_interval('30 minute') . + delta_ts < NOW() - " . $dbh->sql_interval('30 minute') . " ORDER BY bug_id"); my @list; while (MoreSQLData()) { diff --git a/sanitycheck.cgi b/sanitycheck.cgi index 8b7701eb1..63ddf181f 100755 --- a/sanitycheck.cgi +++ b/sanitycheck.cgi @@ -177,8 +177,8 @@ if (defined $cgi->param('rescanallBugMail')) { Status("OK, now attempting to send unsent mail"); SendSQL("SELECT bug_id FROM bugs WHERE lastdiffed < delta_ts AND - delta_ts < date_sub(now(), " . $dbh->sql_interval('30 minute') . - ") ORDER BY bug_id"); + delta_ts < now() - " . $dbh->sql_interval('30 minute') . + " ORDER BY bug_id"); my @list; while (MoreSQLData()) { push (@list, FetchOneColumn()); @@ -736,8 +736,8 @@ Status("Checking for unsent mail"); SendSQL("SELECT bug_id " . "FROM bugs WHERE lastdiffed < delta_ts AND ". - "delta_ts < date_sub(now(), " . $dbh->sql_interval('30 minute') . - ") ORDER BY bug_id"); + "delta_ts < now() - " . $dbh->sql_interval('30 minute') . + " ORDER BY bug_id"); while (@row = FetchSQLData()) { my ($id) = (@row); diff --git a/whine.pl b/whine.pl index 97378a178..a13a129b4 100755 --- a/whine.pl +++ b/whine.pl @@ -172,18 +172,18 @@ while (my ($schedule_id, $day, $time) = $sched_h->fetchrow_array) { elsif ($time >= $now_hour) { # set it to today + number of hours $sth = $dbh->prepare( "UPDATE whine_schedules " . - "SET run_next=DATE_ADD(CURRENT_DATE(), " . - $dbh->sql_interval('? HOUR') . ") " . - "WHERE id=?"); + "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=" . - "DATE_ADD(?, " . $dbh->sql_interval('? HOUR') . ") " . - "WHERE id=?"); + "SET run_next = ? + " . + $dbh->sql_interval('? HOUR') . + " WHERE id = ?"); $sth->execute($nextdate, $time, $schedule_id); } @@ -197,9 +197,9 @@ while (my ($schedule_id, $day, $time) = $sched_h->fetchrow_array) { my $target_time = ($time =~ /^\d+$/) ? $time : 0; $sth = $dbh->prepare( "UPDATE whine_schedules " . - "SET run_next=DATE_ADD(?, " . - $dbh->sql_interval('? HOUR') . ") " . - "WHERE id=?"); + "SET run_next = ? + " . + $dbh->sql_interval('? HOUR') . + " WHERE id = ?"); $sth->execute($target_date, $target_time, $schedule_id); } } @@ -596,22 +596,21 @@ sub reset_timer { my $nextdate = &get_next_date($run_day); $sth = $dbh->prepare( "UPDATE whine_schedules " . - "SET run_next=DATE_ADD(?, " . - $dbh->sql_interval('? HOUR') . ") " . - "WHERE id=?"); + "SET run_next = ? + " . + $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 DATE_SUB() to + # Scheduling is done in terms of whole minutes, so we use subtraction to # drop the seconds from the time. if ($minute_offset > 0) { $sth = $dbh->prepare("UPDATE whine_schedules " . - "SET run_next = " . - "DATE_SUB(DATE_ADD(NOW(), " . - $dbh->sql_interval('? MINUTE') . "), " . - $dbh->sql_interval('SECOND(NOW()) SECOND') . ") " . - "WHERE id=?"); + "SET run_next = NOW() + " . + $dbh->sql_interval('? MINUTE') . " - " . + $dbh->sql_interval('SECOND(NOW()) SECOND') . + " WHERE id = ?"); $sth->execute($minute_offset, $schedule_id); } else { # The minute offset is zero or less, which is not supposed to happen. @@ -691,8 +690,8 @@ sub get_next_date { } # Get a date in whatever format the database will accept - $sth = $dbh->prepare("SELECT DATE_ADD(CURRENT_DATE(), " . - $dbh->sql_interval('? DAY') . ")"); + $sth = $dbh->prepare("SELECT CURRENT_DATE() + " . + $dbh->sql_interval('? DAY')); $sth->execute($add_days); return $sth->fetch->[0]; } -- cgit v1.2.3-24-g4f1b