From f5f31fc070588c2075dd13a0fbabe8117e3aad76 Mon Sep 17 00:00:00 2001 From: "mkanat%kerio.com" <> Date: Tue, 5 Apr 2005 04:52:03 +0000 Subject: Bug 286235: Implicit joins should be replaced by explicit joins - installment A Patch By Tomas Kopal r=joel, a=myk --- CGI.pl | 32 +++++++++++++++++--------------- attachment.cgi | 15 +++++++++------ buglist.cgi | 25 ++++++++++++++----------- checksetup.pl | 12 ++++++------ collectstats.pl | 36 +++++++++++++++++++----------------- editclassifications.cgi | 8 +++++--- editcomponents.cgi | 7 ++++--- editflagtypes.cgi | 16 ++++++++++------ editgroups.cgi | 9 +++++---- editmilestones.cgi | 7 ++++--- process_bug.cgi | 28 ++++++++++++++-------------- request.cgi | 47 ++++++++++++++++++++++++----------------------- sanitycheck.cgi | 41 +++++++++++++++++++++++------------------ showdependencytree.cgi | 21 +++++++++++---------- summarize_time.cgi | 39 ++++++++++++++++++++++----------------- userprefs.cgi | 12 +++++++----- votes.cgi | 19 ++++++++++--------- whineatnews.pl | 12 +++++++----- 18 files changed, 211 insertions(+), 175 deletions(-) diff --git a/CGI.pl b/CGI.pl index d1c738ff9..f700d3702 100644 --- a/CGI.pl +++ b/CGI.pl @@ -176,7 +176,7 @@ sub ValidateBugID { sub PasswordForLogin { my ($login) = (@_); - SendSQL("select cryptpassword from profiles where login_name = " . + SendSQL("SELECT cryptpassword FROM profiles WHERE login_name = " . SqlQuote($login)); my $result = FetchOneColumn(); if (!defined $result) { @@ -223,8 +223,8 @@ sub CheckIfVotedConfirmed { PushGlobalSQLState(); SendSQL("SELECT bugs.votes, bugs.bug_status, products.votestoconfirm, " . " bugs.everconfirmed, NOW() " . - "FROM bugs, products " . - "WHERE bugs.bug_id = $id AND products.id = bugs.product_id"); + "FROM bugs INNER JOIN products ON products.id = bugs.product_id " . + "WHERE bugs.bug_id = $id"); my ($votes, $status, $votestoconfirm, $everconfirmed, $timestamp) = (FetchSQLData()); my $sql_timestamp = SqlQuote($timestamp); my $ret = 0; @@ -298,7 +298,7 @@ sub GetBugActivity { die "Invalid id: $id" unless $id=~/^\s*\d+\s*$/; if (defined $starttime) { - $datepart = "and bugs_activity.bug_when > " . SqlQuote($starttime); + $datepart = "AND bugs_activity.bug_when > " . SqlQuote($starttime); } my $suppjoins = ""; my $suppwhere = ""; @@ -309,17 +309,19 @@ sub GetBugActivity { } my $query = " SELECT COALESCE(fielddefs.description, bugs_activity.fieldid), - fielddefs.name, - bugs_activity.attach_id, " . - $dbh->sql_date_format('bugs_activity.bug_when', '%Y.%m.%d %H:%i:%s') . - ", bugs_activity.removed, bugs_activity.added, - profiles.login_name - FROM bugs_activity $suppjoins LEFT JOIN fielddefs ON - bugs_activity.fieldid = fielddefs.fieldid, - profiles - WHERE bugs_activity.bug_id = $id $datepart - AND profiles.userid = bugs_activity.who $suppwhere - ORDER BY bugs_activity.bug_when"; + fielddefs.name, bugs_activity.attach_id, " . + $dbh->sql_date_format('bugs_activity.bug_when', '%Y.%m.%d %H:%i:%s') . + ", bugs_activity.removed, bugs_activity.added, profiles.login_name + FROM bugs_activity + $suppjoins + LEFT JOIN fielddefs + ON bugs_activity.fieldid = fielddefs.fieldid + INNER JOIN profiles + ON profiles.userid = bugs_activity.who + WHERE bugs_activity.bug_id = $id + $datepart + $suppwhere + ORDER BY bugs_activity.bug_when"; SendSQL($query); diff --git a/attachment.cgi b/attachment.cgi index 65157d740..fadbe8b97 100755 --- a/attachment.cgi +++ b/attachment.cgi @@ -265,9 +265,10 @@ sub validateCanChangeAttachment { my ($attachid) = @_; SendSQL("SELECT product_id - FROM attachments, bugs - WHERE attach_id = $attachid - AND bugs.bug_id = attachments.bug_id"); + FROM attachments + INNER JOIN bugs + ON bugs.bug_id = attachments.bug_id + WHERE attach_id = $attachid"); my $productid = FetchOneColumn(); CanEditProductId($productid) || ThrowUserError("illegal_attachment_edit", @@ -993,9 +994,11 @@ sub insert my @fields = ("assigned_to", "bug_status", "resolution", "login_name"); # Get the old values, for the bugs_activity table - SendSQL("SELECT " . join(", ", @fields) . " FROM bugs, profiles " . - "WHERE bugs.bug_id = $::FORM{'bugid'} " . - "AND profiles.userid = bugs.assigned_to"); + SendSQL("SELECT " . join(", ", @fields) . " " . + "FROM bugs " . + "INNER JOIN profiles " . + "ON profiles.userid = bugs.assigned_to " . + "WHERE bugs.bug_id = $::FORM{'bugid'}"); my @oldvalues = FetchSQLData(); my @newvalues = ($::userid, "ASSIGNED", "", DBID_to_name($::userid)); diff --git a/buglist.cgi b/buglist.cgi index d6567852b..cfcefee90 100755 --- a/buglist.cgi +++ b/buglist.cgi @@ -307,9 +307,11 @@ sub GetGroupsByUserId { # the columns for that row. my $groups = $dbh->selectall_arrayref( "SELECT DISTINCT groups.id, name, description, isactive - FROM groups, user_group_map - WHERE user_id = ? AND isbless = 0 - AND user_group_map.group_id = groups.id + FROM groups + INNER JOIN user_group_map + ON user_group_map.group_id = groups.id + WHERE user_id = ? + AND isbless = 0 AND isbuggroup = 1 ORDER BY description " , {Slice => {}}, ($userid)); @@ -873,14 +875,15 @@ while (my @row = $buglist_sth->fetchrow_array()) { # or because of human choice my %min_membercontrol; if (@bugidlist) { - my $sth = $dbh->prepare("SELECT DISTINCT bugs.bug_id, " . - "MIN(group_control_map.membercontrol) " . - "FROM bugs, bug_group_map " . - "LEFT JOIN group_control_map " . - "ON group_control_map.product_id=bugs.product_id " . - "AND group_control_map.group_id=bug_group_map.group_id " . - "WHERE bugs.bug_id = bug_group_map.bug_id " . - "AND bugs.bug_id IN (" . join(',',@bugidlist) . ") " . + my $sth = $dbh->prepare( + "SELECT DISTINCT bugs.bug_id, MIN(group_control_map.membercontrol) " . + "FROM bugs " . + "INNER JOIN bug_group_map " . + "ON bugs.bug_id = bug_group_map.bug_id " . + "LEFT JOIN group_control_map " . + "ON group_control_map.product_id = bugs.product_id " . + "AND group_control_map.group_id = bug_group_map.group_id " . + "WHERE bugs.bug_id IN (" . join(',',@bugidlist) . ") " . $dbh->sql_group_by('bugs.bug_id')); $sth->execute(); while (my ($bug_id, $min_membercontrol) = $sth->fetchrow_array()) { diff --git a/checksetup.pl b/checksetup.pl index b4f9fb930..c46bcfec9 100755 --- a/checksetup.pl +++ b/checksetup.pl @@ -4090,7 +4090,7 @@ if (@admins) { my @groups = (); -$sth = $dbh->prepare("select id from groups"); +$sth = $dbh->prepare("SELECT id FROM groups"); $sth->execute(); while ( my @row = $sth->fetchrow_array() ) { push (@groups, $row[0]); @@ -4099,8 +4099,8 @@ while ( my @row = $sth->fetchrow_array() ) { # Prompt the user for the email address and name of an administrator. Create # that login, if it doesn't exist already, and make it a member of all groups. -$sth = $dbh->prepare("SELECT user_id FROM groups, user_group_map " . - "WHERE name = 'admin' AND id = group_id"); +$sth = $dbh->prepare("SELECT user_id FROM groups INNER JOIN user_group_map " . + "ON id = group_id WHERE name = 'admin'"); $sth->execute; # when we have no admin users, prompt for admin email address and password ... if ($sth->rows == 0) { @@ -4285,9 +4285,9 @@ if ($sth->rows == 0) { # Final checks... $sth = $dbh->prepare("SELECT user_id " . - "FROM groups, user_group_map " . - "WHERE groups.name = 'admin' " . - "AND groups.id = user_group_map.group_id"); + "FROM groups INNER JOIN user_group_map " . + "ON groups.id = user_group_map.group_id " . + "WHERE groups.name = 'admin'"); $sth->execute; my ($adminuid) = $sth->fetchrow_array; if (!$adminuid) { die "No administrator!" } # should never get here diff --git a/collectstats.pl b/collectstats.pl index ae44b0b75..eca072e61 100755 --- a/collectstats.pl +++ b/collectstats.pl @@ -275,9 +275,9 @@ sub regenerate_stats { my $from_product = ""; if ($product ne '-All-') { - $and_product = "AND bugs.product_id = products.id " . - "AND products.name = " . SqlQuote($product) . " "; - $from_product = ", products"; + $and_product = " AND products.name = " . SqlQuote($product); + $from_product = "INNER JOIN products " . + "ON bugs.product_id = products.id"; } # Determine the start date from the date the first bug in the @@ -287,9 +287,9 @@ sub regenerate_stats { $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') . " != 'NULL' " . + $dbh->sql_to_days('creation_ts') . " != 'NULL'" . $and_product . - "ORDER BY start " . $dbh->sql_limit(1)); + " ORDER BY start " . $dbh->sql_limit(1)); my ($start, $end, $base) = FetchSQLData(); if (!defined $start) { @@ -350,12 +350,13 @@ FIN for my $bug (@bugs) { # First, get information on various bug states. SendSQL("SELECT bugs_activity.removed " . - "FROM bugs_activity,fielddefs " . - "WHERE bugs_activity.fieldid = fielddefs.fieldid " . - "AND fielddefs.name = 'bug_status' " . - "AND bugs_activity.bug_id = $bug " . - "AND bugs_activity.bug_when >= from_days($day) " . - "ORDER BY bugs_activity.bug_when " . + " 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 >= from_days($day) " . + "ORDER BY bugs_activity.bug_when " . $dbh->sql_limit(1)); my $status; @@ -372,12 +373,13 @@ FIN # Next, get information on various bug resolutions. SendSQL("SELECT bugs_activity.removed " . - "FROM bugs_activity,fielddefs " . - "WHERE bugs_activity.fieldid = fielddefs.fieldid " . - "AND fielddefs.name = 'resolution' " . - "AND bugs_activity.bug_id = $bug " . - "AND bugs_activity.bug_when >= from_days($day) " . - "ORDER BY bugs_activity.bug_when " . + " 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 >= from_days($day) " . + "ORDER BY bugs_activity.bug_when " . $dbh->sql_limit(1)); if (@row = FetchSQLData()) { diff --git a/editclassifications.cgi b/editclassifications.cgi index 9dad9ae37..5f31c50a8 100755 --- a/editclassifications.cgi +++ b/editclassifications.cgi @@ -374,9 +374,11 @@ if ($action eq 'reclassify') { products.name, classifications.name, classifications.id > 1 as unknown - FROM products,classifications - WHERE classifications.id=products.classification_id - ORDER BY unknown, products.name, classifications.name"); + FROM products + INNER JOIN classifications + ON classifications.id = products.classification_id + ORDER BY unknown, products.name, + classifications.name"); $sth->execute(); while ( my ($clid, $name, $clname) = $sth->fetchrow_array() ) { if ($clid == $classification_id) { diff --git a/editcomponents.cgi b/editcomponents.cgi index a86329d9d..9dd290db3 100755 --- a/editcomponents.cgi +++ b/editcomponents.cgi @@ -81,9 +81,10 @@ sub TestComponent ($$) # does the product/component combination exist? SendSQL("SELECT components.name - FROM components, products - WHERE products.id = components.product_id - AND products.name = " . SqlQuote($prod) . " + FROM components + INNER JOIN products + ON products.id = components.product_id + WHERE products.name = " . SqlQuote($prod) . " AND components.name = " . SqlQuote($comp)); return FetchOneColumn(); } diff --git a/editflagtypes.cgi b/editflagtypes.cgi index 7936823dc..8ae3a7408 100755 --- a/editflagtypes.cgi +++ b/editflagtypes.cgi @@ -329,12 +329,14 @@ sub update { # the list of inclusions or that have been added to the list of exclusions. SendSQL(" SELECT flags.id - FROM flags, bugs LEFT OUTER JOIN flaginclusions AS i - ON (flags.type_id = i.type_id + FROM flags + INNER JOIN bugs + ON flags.bug_id = bugs.bug_id + LEFT OUTER JOIN flaginclusions AS i + ON (flags.type_id = i.type_id AND (bugs.product_id = i.product_id OR i.product_id IS NULL) AND (bugs.component_id = i.component_id OR i.component_id IS NULL)) WHERE flags.type_id = $::FORM{'id'} - AND flags.bug_id = bugs.bug_id AND flags.is_active = 1 AND i.type_id IS NULL "); @@ -342,10 +344,12 @@ sub update { SendSQL(" SELECT flags.id - FROM flags, bugs, flagexclusions AS e + FROM flags + INNER JOIN bugs + ON flags.bug_id = bugs.bug_id + INNER JOIN flagexclusions AS e + ON flags.type_id = e.type_id WHERE flags.type_id = $::FORM{'id'} - AND flags.bug_id = bugs.bug_id - AND flags.type_id = e.type_id AND flags.is_active = 1 AND (bugs.product_id = e.product_id OR e.product_id IS NULL) AND (bugs.component_id = e.component_id OR e.component_id IS NULL) diff --git a/editgroups.cgi b/editgroups.cgi index 88099b543..87419a9cc 100755 --- a/editgroups.cgi +++ b/editgroups.cgi @@ -68,7 +68,7 @@ sub RederiveRegexp ($$) AND grant_type = ? and isbless = 0"); $sth->execute(); while (my ($uid, $login) = $sth->fetchrow_array()) { - my $present = $dbh->selectrow_array($sthqry, undef, + my $present = $dbh->selectrow_array($sthqry, undef, $uid, $gid, GRANT_REGEXP); if (($regexp =~ /\S+/) && ($login =~ m/$regexp/i)) { @@ -494,9 +494,10 @@ if (($action eq 'remove_all_regexp') || ($action eq 'remove_all')) { $dbh->bz_lock_tables('groups WRITE', 'profiles READ', 'user_group_map WRITE'); $sth = $dbh->prepare("SELECT user_group_map.user_id, profiles.login_name - FROM user_group_map, profiles - WHERE user_group_map.user_id = profiles.userid - AND user_group_map.group_id = ? + FROM user_group_map + INNER JOIN profiles + ON user_group_map.user_id = profiles.userid + WHERE user_group_map.group_id = ? AND grant_type = ? AND isbless = 0"); $sth->execute($gid, GRANT_DIRECT); diff --git a/editmilestones.cgi b/editmilestones.cgi index 0edbd1897..796643e9e 100755 --- a/editmilestones.cgi +++ b/editmilestones.cgi @@ -79,9 +79,10 @@ sub TestMilestone ($$) # does the product exist? my $sth = $dbh->prepare_cached(" SELECT products.name, value - FROM milestones, products - WHERE milestones.product_id = products.id - AND products.name = ? + FROM milestones + INNER JOIN products + ON milestones.product_id = products.id + WHERE products.name = ? AND value = ?"); trick_taint($product); diff --git a/process_bug.cgi b/process_bug.cgi index 55f40bda3..94d86c936 100755 --- a/process_bug.cgi +++ b/process_bug.cgi @@ -227,8 +227,8 @@ sub CheckonComment( $ ) { # and make the user verify the version, component, target milestone, # and bug groups if so. if ( $::FORM{'id'} ) { - SendSQL("SELECT name FROM products, bugs " . - "WHERE products.id = bugs.product_id AND bug_id = $::FORM{'id'}"); + SendSQL("SELECT name FROM products INNER JOIN bugs " . + "ON products.id = bugs.product_id WHERE bug_id = $::FORM{'id'}"); $::oldproduct = FetchSQLData(); } if ((($::FORM{'id'} && $::FORM{'product'} ne $::oldproduct) @@ -445,7 +445,7 @@ sub CheckCanChangeField { # At this point, the user is either the reporter or an # unprivileged user. We first check for fields the reporter - # is not allowed to change. + # is not allowed to change. # The reporter may not: # - reassign bugs, unless the bugs are assigned to him; @@ -673,10 +673,10 @@ sub ChangeResolution { my @groupAdd = (); my @groupDel = (); -SendSQL("SELECT groups.id, isactive FROM groups, user_group_map WHERE " . - "groups.id = user_group_map.group_id AND " . - "user_group_map.user_id = $whoid AND " . - "isbless = 0 AND isbuggroup = 1"); +SendSQL("SELECT groups.id, isactive FROM groups INNER JOIN user_group_map " . + "ON groups.id = user_group_map.group_id " . + "WHERE user_group_map.user_id = $whoid " . + "AND isbless = 0 AND isbuggroup = 1"); while (my ($b, $isactive) = FetchSQLData()) { # The multiple change page may not show all groups a bug is in # (eg product groups when listing more than one product) @@ -1099,8 +1099,8 @@ my $delta_ts; sub SnapShotBug { my ($id) = (@_); - SendSQL("select delta_ts, " . join(',', @::log_columns) . - " from bugs where bug_id = $id"); + SendSQL("SELECT delta_ts, " . join(',', @::log_columns) . + " FROM bugs WHERE bug_id = $id"); my @row = FetchSQLData(); $delta_ts = shift @row; @@ -1110,7 +1110,7 @@ sub SnapShotBug { sub SnapShotDeps { my ($i, $target, $me) = (@_); - SendSQL("select $target from dependencies where $me = $i order by $target"); + SendSQL("SELECT $target FROM dependencies WHERE $me = $i ORDER BY $target"); my @list; while (MoreSQLData()) { push(@list, FetchOneColumn()); @@ -1344,7 +1344,7 @@ foreach my $id (@idlist) { my @stack = @{$deps{$target}}; while (@stack) { my $i = shift @stack; - SendSQL("select $target from dependencies where $me = " . + SendSQL("SELECT $target FROM dependencies WHERE $me = " . SqlQuote($i)); while (MoreSQLData()) { my $t = FetchOneColumn(); @@ -1390,7 +1390,7 @@ foreach my $id (@idlist) { SendSQL("select now()"); $timestamp = FetchOneColumn(); - my $sql_timestamp = SqlQuote($timestamp); + my $sql_timestamp = SqlQuote($timestamp); my $work_time; if (UserInGroup(Param('timetrackinggroup'))) { @@ -1435,9 +1435,9 @@ foreach my $id (@idlist) { } if ($changed) { SendSQL("SELECT keyworddefs.name - FROM keyworddefs, keywords + FROM keyworddefs INNER JOIN keywords + ON keyworddefs.id = keywords.keywordid WHERE keywords.bug_id = $id - AND keyworddefs.id = keywords.keywordid ORDER BY keyworddefs.name"); my @list; while (MoreSQLData()) { diff --git a/request.cgi b/request.cgi index 7f3e6351c..36f6c9ce7 100755 --- a/request.cgi +++ b/request.cgi @@ -92,33 +92,34 @@ sub queue { # so we can display product and component names, and the bug_group_map # and user_group_map tables to help us weed out secure bugs to which # the user should not have access. - " FROM flags - LEFT JOIN attachments ON ($attach_join_clause), - flagtypes, - profiles AS requesters - LEFT JOIN profiles AS requestees - ON flags.requestee_id = requestees.userid, - bugs - LEFT JOIN products ON bugs.product_id = products.id - LEFT JOIN components ON bugs.component_id = components.id - LEFT JOIN bug_group_map AS bgmap - ON bgmap.bug_id = bugs.bug_id - LEFT JOIN user_group_map AS ugmap - ON bgmap.group_id = ugmap.group_id - AND ugmap.user_id = $::userid + " FROM flags + LEFT JOIN attachments + ON ($attach_join_clause) + INNER JOIN flagtypes + ON flags.type_id = flagtypes.id + INNER JOIN profiles AS requesters + ON flags.setter_id = requesters.userid + LEFT JOIN profiles AS requestees + ON flags.requestee_id = requestees.userid + INNER JOIN bugs + ON flags.bug_id = bugs.bug_id + LEFT JOIN products + ON bugs.product_id = products.id + LEFT JOIN components + ON bugs.component_id = components.id + LEFT JOIN bug_group_map AS bgmap + ON bgmap.bug_id = bugs.bug_id + LEFT JOIN user_group_map AS ugmap + ON bgmap.group_id = ugmap.group_id + AND ugmap.user_id = $::userid AND ugmap.isbless = 0 - LEFT JOIN cc AS ccmap - ON ccmap.who = $::userid AND ccmap.bug_id = bugs.bug_id - " . - # All of these are inner join clauses. Actual match criteria are added - # in the code below. - " WHERE flags.type_id = flagtypes.id - AND flags.setter_id = requesters.userid - AND flags.bug_id = bugs.bug_id + LEFT JOIN cc AS ccmap + ON ccmap.who = $::userid + AND ccmap.bug_id = bugs.bug_id "; # Non-deleted flags only - $query .= " AND flags.is_active = 1 "; + $query .= " WHERE flags.is_active = 1 "; # Limit query to pending requests. $query .= " AND flags.status = '?' " unless $cgi->param('status'); diff --git a/sanitycheck.cgi b/sanitycheck.cgi index c05f0e50d..89b657e90 100755 --- a/sanitycheck.cgi +++ b/sanitycheck.cgi @@ -546,9 +546,11 @@ if (defined $cgi->param('rebuildkeywordcache')) { } SendSQL("SELECT keywords.bug_id, keyworddefs.name " . - "FROM keywords, keyworddefs, bugs " . - "WHERE keyworddefs.id = keywords.keywordid " . - " AND keywords.bug_id = bugs.bug_id " . + "FROM keywords " . + "INNER JOIN keyworddefs " . + " ON keyworddefs.id = keywords.keywordid " . + "INNER JOIN bugs " . + " ON keywords.bug_id = bugs.bug_id " . "ORDER BY keywords.bug_id, keyworddefs.name"); my $lastb = 0; @@ -629,9 +631,8 @@ sub BugCheck ($$) { Status("Checking resolution/duplicates"); -BugCheck("bugs, duplicates WHERE " . - "bugs.resolution != 'DUPLICATE' AND " . - "bugs.bug_id = duplicates.dupe", +BugCheck("bugs INNER JOIN duplicates ON bugs.bug_id = duplicates.dupe " . + "WHERE bugs.resolution != 'DUPLICATE'", "Bug(s) found on duplicates table that are not marked duplicate"); BugCheck("bugs LEFT JOIN duplicates ON bugs.bug_id = duplicates.dupe WHERE " . @@ -662,10 +663,8 @@ BugCheck("bugs WHERE bug_status IN ('NEW', 'ASSIGNED', 'REOPENED') AND everconfi Status("Checking votes/everconfirmed"); -BugCheck("bugs, products WHERE " . - "bugs.product_id = products.id AND " . - "everconfirmed = 0 AND " . - "votestoconfirm <= votes", +BugCheck("bugs INNER JOIN products ON bugs.product_id = products.id " . + "WHERE everconfirmed = 0 AND votestoconfirm <= votes", "Bugs that have enough votes to be confirmed but haven't been"); ########################################################################### @@ -711,20 +710,26 @@ if ($c) { Status("Checking for bugs with groups violating their product's group controls"); BugCheck("bugs - INNER JOIN bug_group_map ON bugs.bug_id = bug_group_map.bug_id - INNER JOIN groups ON bug_group_map.group_id = groups.id - LEFT JOIN group_control_map ON bugs.product_id = group_control_map.product_id - AND bug_group_map.group_id = group_control_map.group_id + INNER JOIN bug_group_map + ON bugs.bug_id = bug_group_map.bug_id + INNER JOIN groups + ON bug_group_map.group_id = groups.id + LEFT JOIN group_control_map + ON bugs.product_id = group_control_map.product_id + AND bug_group_map.group_id = group_control_map.group_id WHERE groups.isactive != 0 AND ((group_control_map.membercontrol = " . CONTROLMAPNA . ") OR (group_control_map.membercontrol IS NULL))", "Have groups not permitted for their products"); BugCheck("bugs - INNER JOIN bug_group_map ON bugs.bug_id = bug_group_map.bug_id - INNER JOIN groups ON bug_group_map.group_id = groups.id - LEFT JOIN group_control_map ON bugs.product_id = group_control_map.product_id - AND bug_group_map.group_id = group_control_map.group_id + INNER JOIN bug_group_map + ON bugs.bug_id = bug_group_map.bug_id + INNER JOIN groups + ON bug_group_map.group_id = groups.id + LEFT JOIN group_control_map + ON bugs.product_id = group_control_map.product_id + AND bug_group_map.group_id = group_control_map.group_id WHERE groups.isactive != 0 AND group_control_map.membercontrol = " . CONTROLMAPMANDATORY . " AND bug_group_map.group_id IS NULL", diff --git a/showdependencytree.cgi b/showdependencytree.cgi index b373563c1..76ef0ddee 100755 --- a/showdependencytree.cgi +++ b/showdependencytree.cgi @@ -149,16 +149,17 @@ sub GetBug { my $bug = {}; if (Bugzilla->user->can_see_bug($id)) { SendSQL("SELECT 1, - bug_status, - short_desc, - $milestone_column, - assignee.userid, - assignee.login_name - FROM bugs, profiles AS assignee - WHERE bugs.bug_id = $id - AND bugs.assigned_to = assignee.userid"); - - + bug_status, + short_desc, + $milestone_column, + assignee.userid, + assignee.login_name + FROM bugs + INNER JOIN profiles AS assignee + ON bugs.assigned_to = assignee.userid + WHERE bugs.bug_id = $id"); + + ($bug->{'exists'}, $bug->{'status'}, $bug->{'summary'}, diff --git a/summarize_time.cgi b/summarize_time.cgi index 077678c2e..67bc255e8 100755 --- a/summarize_time.cgi +++ b/summarize_time.cgi @@ -149,11 +149,12 @@ sub include_tt_details { my $q = qq{SELECT bugs.bug_id, profiles.login_name, bugs.deadline, bugs.estimated_time, bugs.remaining_time - FROM longdescs, bugs, profiles - WHERE longdescs.bug_id in ($buglist) AND - longdescs.bug_id = bugs.bug_id AND - longdescs.who = profiles.userid - $date_bits}; + FROM longdescs + INNER JOIN bugs + ON longdescs.bug_id = bugs.bug_id + INNER JOIN profiles + ON longdescs.who = profiles.userid + WHERE longdescs.bug_id in ($buglist) $date_bits}; my %res = %{$res}; my $sth = $dbh->prepare($q); @@ -203,10 +204,10 @@ sub get_blocker_ids_unique { } sub get_blocker_ids_deep { - my ($bug_id, $ret) = @_; + my ($bug_id, $ret) = @_; my $deps = Bugzilla::Bug::EmitDependList("blocked", "dependson", $bug_id); push @{$ret}, @$deps; - foreach $bug_id (@$deps) { + foreach $bug_id (@$deps) { get_blocker_ids_deep($bug_id, $ret); } } @@ -232,10 +233,12 @@ sub query_work_by_buglist { longdescs.bug_id, bugs.short_desc, bugs.bug_status - FROM longdescs, profiles, bugs - WHERE longdescs.bug_id IN ($buglist) AND - longdescs.who = profiles.userid AND - bugs.bug_id = longdescs.bug_id + FROM longdescs + INNER JOIN profiles + ON longdescs.who = profiles.userid + INNER JOIN bugs + ON bugs.bug_id = longdescs.bug_id + WHERE longdescs.bug_id IN ($buglist) $date_bits } . $dbh->sql_group_by('longdescs.bug_id, profiles.login_name', 'bugs.short_desc, bugs.bug_status, longdescs.bug_when') . qq{ @@ -296,9 +299,10 @@ sub get_inactive_bugs { # them in %res here and then remove them below. my $q = qq{SELECT DISTINCT bugs.bug_id, bugs.short_desc , bugs.bug_status - FROM longdescs, bugs - WHERE longdescs.bug_id in ($buglist) AND - longdescs.bug_id = bugs.bug_id}; + FROM longdescs + INNER JOIN bugs + ON longdescs.bug_id = bugs.bug_id + WHERE longdescs.bug_id in ($buglist)}; my $sth = $dbh->prepare($q); $sth->execute(); while (my $row = $sth->fetch) { @@ -312,9 +316,10 @@ sub get_inactive_bugs { longdescs.bug_id, bugs.short_desc, bugs.bug_status - FROM longdescs, bugs - WHERE longdescs.bug_id IN ($buglist) AND - bugs.bug_id = longdescs.bug_id + FROM longdescs + INNER JOIN bugs + ON bugs.bug_id = longdescs.bug_id + WHERE longdescs.bug_id IN ($buglist) $date_bits } . $dbh->sql_group_by('longdescs.bug_id', 'bugs.short_desc, bugs.bug_status') . qq{ diff --git a/userprefs.cgi b/userprefs.cgi index eac0bb108..92e110f3b 100755 --- a/userprefs.cgi +++ b/userprefs.cgi @@ -177,8 +177,9 @@ sub DoEmail { ########################################################################### if (Param("supportwatchers")) { my $watched_ref = $dbh->selectcol_arrayref( - "SELECT profiles.login_name FROM watch, profiles" - . " WHERE watcher = ? AND watch.watched = profiles.userid", + "SELECT profiles.login_name FROM watch INNER JOIN profiles" . + " ON watch.watched = profiles.userid" . + " WHERE watcher = ?", undef, $userid); $vars->{'watchedusers'} = join(',', @$watched_ref); @@ -307,9 +308,10 @@ sub SaveEmail { sub DoPermissions { my (@has_bits, @set_bits); - SendSQL("SELECT DISTINCT name, description FROM groups, user_group_map " . - "WHERE user_group_map.group_id = groups.id " . - "AND user_id = $::userid " . + SendSQL("SELECT DISTINCT name, description FROM groups " . + "INNER JOIN user_group_map " . + "ON user_group_map.group_id = groups.id " . + "WHERE user_id = $::userid " . "AND isbless = 0 " . "ORDER BY name"); while (MoreSQLData()) { diff --git a/votes.cgi b/votes.cgi index 3a22c90b0..128dcba81 100755 --- a/votes.cgi +++ b/votes.cgi @@ -95,9 +95,9 @@ sub show_bug { my @users; SendSQL("SELECT profiles.login_name, votes.who, votes.vote_count - FROM votes, profiles - WHERE votes.bug_id = $bug_id - AND profiles.userid = votes.who"); + FROM votes INNER JOIN profiles + ON profiles.userid = votes.who + WHERE votes.bug_id = $bug_id"); while (MoreSQLData()) { my ($name, $userid, $count) = (FetchSQLData()); @@ -170,10 +170,10 @@ sub show_user { SendSQL("SELECT votes.bug_id, votes.vote_count, bugs.short_desc, bugs.bug_status - FROM votes, bugs, products + FROM votes + INNER JOIN bugs ON votes.bug_id = bugs.bug_id + INNER JOIN products ON bugs.product_id = products.id WHERE votes.who = $who - AND votes.bug_id = bugs.bug_id - AND bugs.product_id = products.id AND products.name = " . SqlQuote($product) . "ORDER BY votes.bug_id"); @@ -280,9 +280,10 @@ sub record_votes { # the ballot box. if (scalar(@buglist)) { SendSQL("SELECT bugs.bug_id, products.name, products.maxvotesperbug - FROM bugs, products - WHERE products.id = bugs.product_id - AND bugs.bug_id IN (" . join(", ", @buglist) . ")"); + FROM bugs + INNER JOIN products + ON products.id = bugs.product_id + WHERE bugs.bug_id IN (" . join(", ", @buglist) . ")"); my %prodcount; diff --git a/whineatnews.pl b/whineatnews.pl index d90e775fa..286b0c542 100755 --- a/whineatnews.pl +++ b/whineatnews.pl @@ -34,11 +34,13 @@ require "globals.pl"; use Bugzilla::BugMail; my $dbh = Bugzilla->dbh; -SendSQL("SELECT bug_id, short_desc, login_name FROM bugs, profiles WHERE " . - "(bug_status = 'NEW' OR bug_status = 'REOPENED') AND " . - $dbh->sql_to_days('NOW()') . " - " . - $dbh->sql_to_days('delta_ts') . " > " . Param('whinedays') . - " AND userid = assigned_to ORDER BY bug_id"); +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 %bugs; my %desc; -- cgit v1.2.3-24-g4f1b