From 5de2bb1d2561764555836986416b9e4e58eec047 Mon Sep 17 00:00:00 2001 From: "lpsolit%gmail.com" <> Date: Fri, 7 Apr 2006 05:26:28 +0000 Subject: Bug 303690: Eliminate deprecated Bugzilla::DB routines from collectstats.pl and whineatnews.pl - Patch by Gabriel Sales de Oliveira r=LpSolit a=justdave --- collectstats.pl | 170 +++++++++++++++++++++++++++++--------------------------- whineatnews.pl | 24 ++++---- 2 files changed, 103 insertions(+), 91 deletions(-) diff --git a/collectstats.pl b/collectstats.pl index f6bfbdae1..9367ebc8e 100755 --- a/collectstats.pl +++ b/collectstats.pl @@ -126,6 +126,7 @@ sub collect_stats { my $product = shift; my $when = localtime (time); my $product_id = get_product_id($product) unless $product eq '-All-'; + my $dbh = Bugzilla->dbh; die "Unknown product $product" unless ($product_id or $product eq '-All-'); @@ -137,26 +138,35 @@ sub collect_stats { my $exists = -f $file; if (open DATA, ">>$file") { - push my @row, &today; - + push (my @row, &today); + my $status_sql = q{SELECT COUNT(*) + FROM bugs + WHERE bug_status = ?}; + + my $reso_sql = q{SELECT COUNT(*) + FROM bugs + WHERE resolution = ?}; + + if ($product ne '-All-') { + $status_sql .= q{ AND product_id = ?}; + $reso_sql .= q{ AND product_id = ?}; + } + + my $sth_status = $dbh->prepare($status_sql); + my $sth_reso = $dbh->prepare($reso_sql); + + my @values ; foreach my $status ('NEW', 'ASSIGNED', 'REOPENED', 'UNCONFIRMED', 'RESOLVED', 'VERIFIED', 'CLOSED') { - if( $product eq "-All-" ) { - SendSQL("SELECT COUNT(bug_status) FROM bugs WHERE bug_status='$status'"); - } else { - SendSQL("SELECT COUNT(bug_status) FROM bugs WHERE bug_status='$status' AND product_id=$product_id"); - } - - push @row, FetchOneColumn(); + @values = ($status); + push (@values, $product_id) if ($product ne '-All-'); + my $count = $dbh->selectrow_array($sth_status, undef, @values); + push(@row, $count); } - foreach my $resolution ('FIXED', 'INVALID', 'WONTFIX', 'LATER', 'REMIND', 'DUPLICATE', 'WORKSFORME', 'MOVED') { - if( $product eq "-All-" ) { - SendSQL("SELECT COUNT(resolution) FROM bugs WHERE resolution='$resolution'"); - } else { - SendSQL("SELECT COUNT(resolution) FROM bugs WHERE resolution='$resolution' AND product_id=$product_id"); - } - - push @row, FetchOneColumn(); + @values = ($resolution); + push (@values, $product_id) if ($product ne '-All-'); + my $count = $dbh->selectrow_array($sth_reso, undef, @values); + push(@row, $count); } if (! $exists) { @@ -219,7 +229,7 @@ sub calculate_dupes { } $count{$dupe_of}++; - } + } # Now we collapse the dupe tree by iterating over %count until # there is no further change. @@ -271,29 +281,32 @@ sub regenerate_stats { my $and_product = ""; my $from_product = ""; - + + my @values = (); if ($product ne '-All-') { - $and_product = " AND products.name = " . SqlQuote($product); - $from_product = "INNER JOIN products " . - "ON bugs.product_id = products.id"; - } - + $and_product = q{AND products.name = ?}; + $from_product = q{INNER JOIN products + ON bugs.product_id = products.id}; + push (@values, $product); + } + # Determine the start date from the date the first bug in the # database was created, and the end date from the current day. # If there were no bugs in the search, return early. - SendSQL("SELECT " . $dbh->sql_to_days('creation_ts') . " AS start, " . - $dbh->sql_to_days('current_date') . " AS end, " . - $dbh->sql_to_days("'1970-01-01'") . - " FROM bugs $from_product WHERE " . - $dbh->sql_to_days('creation_ts') . " IS NOT NULL " . - $and_product . - " ORDER BY start " . $dbh->sql_limit(1)); - - my ($start, $end, $base) = FetchSQLData(); + my $query = q{SELECT } . + $dbh->sql_to_days('creation_ts') . q{ AS start, } . + $dbh->sql_to_days('current_date') . q{ AS end, } . + $dbh->sql_to_days("'1970-01-01'") . + qq{ FROM bugs $from_product + WHERE } . $dbh->sql_to_days('creation_ts') . + qq{ IS NOT NULL $and_product + ORDER BY start } . $dbh->sql_limit(1); + my ($start, $end, $base) = $dbh->selectrow_array($query, undef, @values); + if (!defined $start) { return; } - + if (open DATA, ">$file") { DATA->autoflush(1); print DATA <sql_from_days($day - 1) . - " AND bugs.creation_ts >= " . $dbh->sql_from_days($day - 2) . - $and_product . - " ORDER BY bug_id"); - - my @row; - while (@row = FetchSQLData()) { - push @bugs, $row[0]; - } + $query = qq{SELECT bug_id + FROM bugs $from_product + WHERE bugs.creation_ts < } . + $dbh->sql_from_days($day - 1) . + q{ AND bugs.creation_ts >= } . + $dbh->sql_from_days($day - 2) . + $and_product . q{ ORDER BY bug_id}; + + my $bug_ids = $dbh->selectcol_arrayref($query, undef, @values); + + push(@bugs, @$bug_ids); # For each bug that existed on that day, determine its status # at the beginning of the day. If there were no status @@ -344,51 +358,45 @@ FIN foreach (@logresolutions) { $bugcount{$_} = 0; } + # Get information on bug states and resolutions. + $query = qq{SELECT bugs_activity.removed + FROM bugs_activity + INNER JOIN fielddefs + ON bugs_activity.fieldid = fielddefs.fieldid + WHERE fielddefs.name = ? + AND bugs_activity.bug_id = ? + AND bugs_activity.bug_when >= } . + $dbh->sql_from_days($day) . + " ORDER BY bugs_activity.bug_when " . + $dbh->sql_limit(1); + + my $sth_bug = $dbh->prepare($query); + my $sth_status = $dbh->prepare(q{SELECT bug_status + FROM bugs + WHERE bug_id = ?}); + my $sth_reso = $dbh->prepare(q{SELECT resolution + FROM bugs + WHERE bug_id = ?}); + for my $bug (@bugs) { - # First, get information on various bug states. - SendSQL("SELECT bugs_activity.removed " . - " FROM bugs_activity " . - "INNER JOIN fielddefs " . - " ON bugs_activity.fieldid = fielddefs.fieldid " . - " WHERE fielddefs.name = 'bug_status' " . - " AND bugs_activity.bug_id = $bug " . - " AND bugs_activity.bug_when >= " . $dbh->sql_from_days($day) . - " ORDER BY bugs_activity.bug_when " . - $dbh->sql_limit(1)); - - my $status; - if (@row = FetchSQLData()) { - $status = $row[0]; - } else { - SendSQL("SELECT bug_status FROM bugs WHERE bug_id = $bug"); - $status = FetchOneColumn(); + my $status = $dbh->selectrow_array($sth_bug, undef, + 'bug_status', $bug); + unless ($status) { + $status = $dbh->selectrow_array($sth_status, undef, $bug); } - + if (defined $bugcount{$status}) { $bugcount{$status}++; } - - # Next, get information on various bug resolutions. - SendSQL("SELECT bugs_activity.removed " . - " FROM bugs_activity " . - "INNER JOIN fielddefs " . - " ON bugs_activity.fieldid = fielddefs.fieldid " . - " WHERE fielddefs.name = 'resolution' " . - " AND bugs_activity.bug_id = $bug " . - " AND bugs_activity.bug_when >= " . $dbh->sql_from_days($day) . - " ORDER BY bugs_activity.bug_when " . - $dbh->sql_limit(1)); - - if (@row = FetchSQLData()) { - $status = $row[0]; - } else { - SendSQL("SELECT resolution FROM bugs WHERE bug_id = $bug"); - $status = FetchOneColumn(); + my $resolution = $dbh->selectrow_array($sth_bug, undef, + 'resolution', $bug); + unless ($resolution) { + $resolution = $dbh->selectrow_array($sth_reso, undef, $bug); } - if (defined $bugcount{$status}) { - $bugcount{$status}++; + if (defined $bugcount{$resolution}) { + $bugcount{$resolution}++; } } diff --git a/whineatnews.pl b/whineatnews.pl index 50e06383e..62e6388ac 100755 --- a/whineatnews.pl +++ b/whineatnews.pl @@ -32,6 +32,7 @@ use lib '.'; require "globals.pl"; +use Bugzilla; use Bugzilla::BugMail; use Bugzilla::Util; @@ -39,20 +40,23 @@ use Bugzilla::Util; exit unless Param('whinedays') >= 1; my $dbh = Bugzilla->dbh; -SendSQL("SELECT bug_id, short_desc, login_name " . - "FROM bugs INNER JOIN profiles ON userid = assigned_to " . - "WHERE (bug_status = 'NEW' OR bug_status = 'REOPENED') " . - "AND " . $dbh->sql_to_days('NOW()') . " - " . - $dbh->sql_to_days('delta_ts') . " > " . - Param('whinedays') . " " . - "ORDER BY bug_id"); +my $query = q{SELECT bug_id, short_desc, login_name + FROM bugs + INNER JOIN profiles + ON userid = assigned_to + WHERE (bug_status = ? OR bug_status = ?) + AND } . $dbh->sql_to_days('NOW()') . " - " . + $dbh->sql_to_days('delta_ts') . " > " . + Param('whinedays') . + " ORDER BY bug_id"; my %bugs; my %desc; -my @row; -while (@row = FetchSQLData()) { - my ($id, $desc, $email) = (@row); +my $slt_bugs = $dbh->selectall_arrayref($query, undef, 'NEW', 'REOPENED'); + +foreach my $bug (@$slt_bugs) { + my ($id, $desc, $email) = @$bug; if (!defined $bugs{$email}) { $bugs{$email} = []; } -- cgit v1.2.3-24-g4f1b