From 61ddf0a32846fdf2607043d94af1a0a86b80f6fc Mon Sep 17 00:00:00 2001 From: "bbaetz%student.usyd.edu.au" <> Date: Mon, 12 Aug 2002 12:42:42 +0000 Subject: Bug 43600 - Convert products/components to use ids instead of names. Initial attempt by jake@bugzilla.org, updated by me r=joel, preed --- Bug.pm | 9 ++- Bugzilla/Bug.pm | 9 ++- Bugzilla/Search.pm | 58 +++++++++++++++--- CGI.pl | 2 +- attachment.cgi | 4 +- bug_form.pl | 14 ++++- buglist.cgi | 6 +- checksetup.pl | 155 ++++++++++++++++++++++++++++++++++++++++--------- collectstats.pl | 7 ++- contrib/bug_email.pl | 36 +++++++----- describecomponents.cgi | 13 +++-- duplicates.cgi | 24 +++++--- editattachstatuses.cgi | 32 +++++----- editcomponents.cgi | 114 ++++++++++++++++-------------------- editgroups.cgi | 4 +- editmilestones.cgi | 54 +++++++++-------- editproducts.cgi | 121 +++++++++++++++++++------------------- editusers.cgi | 14 +++-- editversions.cgi | 73 ++++++++++------------- enter_bug.cgi | 10 ++-- globals.pl | 62 ++++++++++++++++++-- importxml.pl | 7 ++- long_list.cgi | 9 +-- post_bug.cgi | 27 ++++++--- process_bug.cgi | 68 ++++++++++++++++++---- queryhelp.cgi | 8 ++- reports.cgi | 14 +++-- sanitycheck.cgi | 52 ++++++++--------- votes.cgi | 11 ++-- 29 files changed, 651 insertions(+), 366 deletions(-) diff --git a/Bug.pm b/Bug.pm index df7a91553..d73bc536f 100755 --- a/Bug.pm +++ b/Bug.pm @@ -111,13 +111,16 @@ sub initBug { my $query = " select - bugs.bug_id, alias, product, version, rep_platform, op_sys, bug_status, - resolution, priority, bug_severity, component, assigned_to, reporter, + bugs.bug_id, alias, products.name, version, rep_platform, op_sys, bug_status, + resolution, priority, bug_severity, components.name, assigned_to, reporter, bug_file_loc, short_desc, target_milestone, qa_contact, status_whiteboard, date_format(creation_ts,'%Y-%m-%d %H:%i'), groupset, delta_ts, sum(votes.count) - from bugs left join votes using(bug_id) + from bugs left join votes using(bug_id), + products, components where bugs.bug_id = $bug_id + AND products.id = bugs.product_id + AND components.id = bugs.component_id group by bugs.bug_id"; &::SendSQL(&::SelectVisible($query, $user_id, $usergroupset)); diff --git a/Bugzilla/Bug.pm b/Bugzilla/Bug.pm index df7a91553..d73bc536f 100755 --- a/Bugzilla/Bug.pm +++ b/Bugzilla/Bug.pm @@ -111,13 +111,16 @@ sub initBug { my $query = " select - bugs.bug_id, alias, product, version, rep_platform, op_sys, bug_status, - resolution, priority, bug_severity, component, assigned_to, reporter, + bugs.bug_id, alias, products.name, version, rep_platform, op_sys, bug_status, + resolution, priority, bug_severity, components.name, assigned_to, reporter, bug_file_loc, short_desc, target_milestone, qa_contact, status_whiteboard, date_format(creation_ts,'%Y-%m-%d %H:%i'), groupset, delta_ts, sum(votes.count) - from bugs left join votes using(bug_id) + from bugs left join votes using(bug_id), + products, components where bugs.bug_id = $bug_id + AND products.id = bugs.product_id + AND components.id = bugs.component_id group by bugs.bug_id"; &::SendSQL(&::SelectVisible($query, $user_id, $usergroupset)); diff --git a/Bugzilla/Search.pm b/Bugzilla/Search.pm index 0f311f07e..16dd493d6 100644 --- a/Bugzilla/Search.pm +++ b/Bugzilla/Search.pm @@ -66,13 +66,29 @@ sub init { my @andlist; # First, deal with all the old hard-coded non-chart-based poop. - unshift(@supptables, - ("profiles map_assigned_to", - "profiles map_reporter", - "LEFT JOIN profiles map_qa_contact ON bugs.qa_contact = map_qa_contact.userid")); - unshift(@wherepart, - ("bugs.assigned_to = map_assigned_to.userid", - "bugs.reporter = map_reporter.userid")); + if (&::lsearch($fieldsref, 'map_assigned_to.login_name') >= 0) { + push @supptables, "profiles AS map_assigned_to"; + push @wherepart, "bugs.assigned_to = map_assigned_to.userid"; + } + + if (&::lsearch($fieldsref, 'map_reporter.login_name') >= 0) { + push @supptables, "profiles AS map_reporter"; + push @wherepart, "bugs.assigned_to = map_reporter.userid"; + } + + if (&::lsearch($fieldsref, 'map_qa_contact.login_name') >= 0) { + push @supptables, "LEFT JOIN profiles map_qa_contact ON bugs.qa_contact = map_qa_contact.userid"; + } + + if (&::lsearch($fieldsref, 'map_products.name') >= 0) { + push @supptables, "products AS map_products"; + push @wherepart, "bugs.product_id = map_products.id"; + } + + if (&::lsearch($fieldsref, 'map_components.name') >= 0) { + push @supptables, "components AS map_components"; + push @wherepart, "bugs.component_id = map_components.id"; + } my $minvotes; if (defined $F{'votes'}) { @@ -108,6 +124,18 @@ sub init { } } + if ($F{'product'}) { + push(@supptables, "products products_"); + push(@wherepart, "products_.id = bugs.product_id"); + push(@specialchart, ["products_.name", "anyexact", $F{'product'}]); + } + + if ($F{'component'}) { + push(@supptables, "components components_"); + push(@wherepart, "components_.id = bugs.component_id"); + push(@specialchart, ["components_.name", "anyexact", $F{'component'}]); + } + if ($F{'keywords'}) { my $t = $F{'keywords_type'}; if (!$t || $t eq "or") { @@ -248,7 +276,7 @@ sub init { my @funcdefs = ( "^(assigned_to|reporter)," => sub { - push(@supptables, "profiles map_$f"); + push(@supptables, "profiles AS map_$f"); push(@wherepart, "bugs.$f = map_$f.userid"); $f = "map_$f.login_name"; }, @@ -391,6 +419,20 @@ sub init { $f = "(to_days(now()) - to_days(bugs.delta_ts))"; }, + "^component,(?!changed)" => sub { + my $table = "components_$chartid"; + push(@supptables, "components $table"); + push(@wherepart, "bugs.component_id = $table.id"); + $f = $ff = "$table.name"; + }, + + "^product,(?!changed)" => sub { + my $table = "products_$chartid"; + push(@supptables, "products $table"); + push(@wherepart, "bugs.product_id = $table.id"); + $f = $ff = "$table.name"; + }, + "^keywords," => sub { &::GetVersionTable(); my @list; diff --git a/CGI.pl b/CGI.pl index d3ca9f8f9..de2d38085 100644 --- a/CGI.pl +++ b/CGI.pl @@ -947,7 +947,7 @@ sub CheckIfVotedConfirmed { SendSQL("SELECT bugs.votes, bugs.bug_status, products.votestoconfirm, " . " bugs.everconfirmed " . "FROM bugs, products " . - "WHERE bugs.bug_id = $id AND products.product = bugs.product"); + "WHERE bugs.bug_id = $id AND products.product_id = bugs.product_id"); my ($votes, $status, $votestoconfirm, $everconfirmed) = (FetchSQLData()); if ($votes >= $votestoconfirm && $status eq $::unconfirmedstate) { SendSQL("UPDATE bugs SET bug_status = 'NEW', everconfirmed = 1 " . diff --git a/attachment.cgi b/attachment.cgi index a755d3b26..8a6c1b033 100755 --- a/attachment.cgi +++ b/attachment.cgi @@ -252,7 +252,7 @@ sub validateStatuses FROM attachments, bugs, attachstatusdefs WHERE attachments.attach_id = $::FORM{'id'} AND attachments.bug_id = bugs.bug_id - AND attachstatusdefs.product = bugs.product"); + AND attachstatusdefs.product_id = bugs.product_id"); my @statusdefs; push(@statusdefs, FetchSQLData()) while MoreSQLData(); PopGlobalSQLState(); @@ -573,7 +573,7 @@ sub edit SendSQL("SELECT id, name FROM attachstatusdefs, bugs WHERE bug_id = $bugid - AND attachstatusdefs.product = bugs.product + AND attachstatusdefs.product_id = bugs.product_id ORDER BY sortkey"); while ( MoreSQLData() ) { diff --git a/bug_form.pl b/bug_form.pl index 65c7b41c1..fb6a60112 100644 --- a/bug_form.pl +++ b/bug_form.pl @@ -77,19 +77,27 @@ sub show_bug { # Populate the bug hash with the info we get directly from the DB. my $query = " - SELECT bugs.bug_id, alias, product, version, rep_platform, + SELECT bugs.bug_id, alias, products.name, version, rep_platform, op_sys, bug_status, resolution, priority, - bug_severity, component, assigned_to, reporter, + bug_severity, components.name, assigned_to, reporter, bug_file_loc, short_desc, target_milestone, qa_contact, status_whiteboard, date_format(creation_ts,'%Y-%m-%d %H:%i'), groupset, delta_ts, sum(votes.count) - FROM bugs LEFT JOIN votes USING(bug_id) + FROM bugs LEFT JOIN votes USING(bug_id), products, components WHERE bugs.bug_id = $id + AND bugs.product_id = products.id + AND bugs.component_id = components.id GROUP BY bugs.bug_id"; SendSQL($query); + # The caller is meant to have checked this. Abort here so that + # we don't get obscure SQL errors, below + if (!MoreSQLData()) { + ThrowCodeError("No data when fetching bug $id"); + } + my $value; my @row = FetchSQLData(); foreach my $field ("bug_id", "alias", "product", "version", "rep_platform", diff --git a/buglist.cgi b/buglist.cgi index 589719962..9570cdab3 100755 --- a/buglist.cgi +++ b/buglist.cgi @@ -397,8 +397,8 @@ DefineColumn("resolution" , "bugs.resolution" , "Result" DefineColumn("summary" , "bugs.short_desc" , "Summary" ); DefineColumn("summaryfull" , "bugs.short_desc" , "Summary" ); DefineColumn("status_whiteboard" , "bugs.status_whiteboard" , "Status Summary" ); -DefineColumn("component" , "bugs.component" , "Component" ); -DefineColumn("product" , "bugs.product" , "Product" ); +DefineColumn("component" , "map_components.name" , "Component" ); +DefineColumn("product" , "map_products.name" , "Product" ); DefineColumn("version" , "bugs.version" , "Version" ); DefineColumn("os" , "bugs.op_sys" , "OS" ); DefineColumn("target_milestone" , "bugs.target_milestone" , "Target Milestone" ); @@ -561,7 +561,7 @@ if ($order) { # change it to order by the sortkey of the target_milestone first. if ($db_order =~ /bugs.target_milestone/) { $db_order =~ s/bugs.target_milestone/ms_order.sortkey,ms_order.value/; - $query =~ s/\sWHERE\s/ LEFT JOIN milestones ms_order ON ms_order.value = bugs.target_milestone AND ms_order.product = bugs.product WHERE /; + $query =~ s/\sWHERE\s/ LEFT JOIN milestones ms_order ON ms_order.value = bugs.target_milestone AND ms_order.product_id = bugs.product_id WHERE /; } # If we are sorting by votes, sort in descending order if no explicit diff --git a/checksetup.pl b/checksetup.pl index 74d008708..919fb0354 100755 --- a/checksetup.pl +++ b/checksetup.pl @@ -23,6 +23,8 @@ # Dan Mosedale # Dave Miller # Zach Lipton +# Jacob Steenhagen +# Bradley Baetz # # # Direct any questions on this source code to @@ -1324,7 +1326,7 @@ $table{attachstatusdefs} = name VARCHAR(50) NOT NULL , description MEDIUMTEXT NULL , sortkey SMALLINT NOT NULL DEFAULT 0 , - product VARCHAR(64) NOT NULL + product_id SMALLINT NOT NULL '; # @@ -1343,11 +1345,11 @@ $table{bugs} = short_desc mediumtext, op_sys enum($my_opsys) not null, priority enum($my_priorities) not null, - product varchar(64) not null, + product_id smallint not null, rep_platform enum($my_platforms), reporter mediumint not null, version varchar(64) not null, - component varchar(50) not null, + component_id smallint not null, resolution enum("", "FIXED", "INVALID", "WONTFIX", "LATER", "REMIND", "DUPLICATE", "WORKSFORME", "MOVED") not null, target_milestone varchar(20) not null default "---", qa_contact mediumint not null, @@ -1369,10 +1371,10 @@ $table{bugs} = index (bug_status), index (op_sys), index (priority), - index (product), + index (product_id), index (reporter), index (version), - index (component), + index (component_id), index (resolution), index (target_milestone), index (qa_contact), @@ -1408,11 +1410,15 @@ $table{longdescs} = $table{components} = - 'value tinytext, - program varchar(64), + 'id smallint not null auto_increment primary key, + name varchar(64) not null, + product_id smallint not null, initialowner mediumint not null, initialqacontact mediumint not null, - description mediumtext not null'; + description mediumtext not null, + + unique(product_id,name), + index(name)'; $table{dependencies} = @@ -1462,15 +1468,17 @@ $table{logincookies} = $table{products} = - 'product varchar(64), + 'id smallint not null auto_increment primary key, + name varchar(64) not null, description mediumtext, milestoneurl tinytext not null, disallownew tinyint not null, votesperuser smallint not null, maxvotesperbug smallint not null default 10000, votestoconfirm smallint not null, - defaultmilestone varchar(20) not null default "---" -'; + defaultmilestone varchar(20) not null default "---", + + unique(name)'; $table{profiles} = @@ -1521,7 +1529,7 @@ $table{fielddefs} = $table{versions} = 'value tinytext, - program varchar(64) not null'; + product_id smallint not null'; $table{votes} = @@ -1548,10 +1556,10 @@ $table{keyworddefs} = $table{milestones} = - 'value varchar(20) not null, - product varchar(64) not null, + 'product_id smallint not null, + value varchar(20) not null, sortkey smallint not null, - unique (product, value)'; + unique (product_id, value)'; $table{shadowlog} = 'id int not null auto_increment primary key, @@ -2077,22 +2085,27 @@ _End_Of_SQL_ $sth->execute; my ($adminuid) = $sth->fetchrow_array; if (!$adminuid) { die "No administator!" } # should never get here -$sth = $dbh->prepare("SELECT product FROM products"); +$sth = $dbh->prepare("SELECT description FROM products"); $sth->execute; unless ($sth->rows) { print "Creating initial dummy product 'TestProduct' ...\n"; - $dbh->do('INSERT INTO products(product, description, milestoneurl, disallownew, votesperuser, votestoconfirm) VALUES ("TestProduct", + $dbh->do('INSERT INTO products(name, description, milestoneurl, disallownew, votesperuser, votestoconfirm) VALUES ("TestProduct", "This is a test product. This ought to be blown away and ' . 'replaced with real stuff in a finished installation of ' . 'bugzilla.", "", 0, 0, 0)'); - $dbh->do('INSERT INTO versions (value, program) VALUES ("other", "TestProduct")'); - $dbh->do("INSERT INTO components (value, program, description, initialowner, initialqacontact) + # We could probably just assume that this is "1", but better + # safe than sorry... + $sth = $dbh->prepare("SELECT LAST_INSERT_ID()"); + $sth->execute; + my ($product_id) = $sth->fetchrow_array; + $dbh->do(qq{INSERT INTO versions (value, product_id) VALUES ("other", $product_id)}); + $dbh->do("INSERT INTO components (name, product_id, description, initialowner, initialqacontact) VALUES (" . - "'TestComponent', 'TestProduct', " . + "'TestComponent', $product_id, " . "'This is a test component in the test product database. " . "This ought to be blown away and replaced with real stuff in " . "a finished installation of bugzilla.', $adminuid, 0)"); - $dbh->do('INSERT INTO milestones (product, value) VALUES ("TestProduct","---")'); + $dbh->do(qq{INSERT INTO milestones (product_id, value) VALUES ($product_id,"---")}); } @@ -2206,8 +2219,6 @@ AddField('products', 'disallownew', 'tinyint not null'); AddField('products', 'milestoneurl', 'tinytext not null'); AddField('components', 'initialqacontact', 'tinytext not null'); AddField('components', 'description', 'mediumtext not null'); -ChangeFieldType('components', 'program', 'varchar(64)'); - # 1999-06-22 Added an entry to the attachments table to record who the # submitter was. Nothing uses this yet, but it still should be recorded. @@ -2258,10 +2269,14 @@ AddField('products', 'votesperuser', 'mediumint not null'); # tinytext is equivalent to varchar(255), which is quite huge, so I change # them all to varchar(64). -ChangeFieldType ('bugs', 'product', 'varchar(64) not null'); -ChangeFieldType ('components', 'program', 'varchar(64)'); -ChangeFieldType ('products', 'product', 'varchar(64)'); -ChangeFieldType ('versions', 'program', 'varchar(64) not null'); +# Only do this if these fields still exist - they're removed below, in +# a later change +if (GetFieldDef('products', 'product')) { + ChangeFieldType ('bugs', 'product', 'varchar(64) not null'); + ChangeFieldType ('components', 'program', 'varchar(64)'); + ChangeFieldType ('products', 'product', 'varchar(64)'); + ChangeFieldType ('versions', 'program', 'varchar(64) not null'); +} # 2000-01-16 Added a "keywords" field to the bugs table, which # contains a string copy of the entries of the keywords table for this @@ -3045,6 +3060,92 @@ if (-e 'data/comments.bak' && !$renamed_comments_file) { if (GetFieldDef("namedqueries", "watchfordiffs")) { DropField("namedqueries", "watchfordiffs"); } + +# 2002-08-?? jake@acutex.net/bbaetz@student.usyd.edu.au - bug 43600 +# Use integer IDs for products and components. +if (GetFieldDef("products", "product")) { + print "Updating database to use product IDs.\n"; + + # First, we need to remove possible NULL entries + # NULLs may exist, but won't have been used, since all the uses of them + # are in NOT NULL fields in other tables + $dbh->do("DELETE FROM products WHERE product IS NULL"); + $dbh->do("DELETE FROM components WHERE value IS NULL"); + + AddField("products", "id", "smallint not null auto_increment primary key"); + AddField("components", "product_id", "smallint not null"); + AddField("versions", "product_id", "smallint not null"); + AddField("milestones", "product_id", "smallint not null"); + AddField("bugs", "product_id", "smallint not null"); + AddField("attachstatusdefs", "product_id", "smallint not null"); + my %products; + my $sth = $dbh->prepare("SELECT id, product FROM products"); + $sth->execute; + while (my ($product_id, $product) = $sth->fetchrow_array()) { + if (exists $products{$product}) { + print "Ignoring duplicate product $product\n"; + $dbh->do("DELETE FROM products WHERE id = $product_id"); + next; + } + $products{$product} = 1; + $dbh->do("UPDATE components SET product_id = $product_id " . + "WHERE program = " . $dbh->quote($product)); + $dbh->do("UPDATE versions SET product_id = $product_id " . + "WHERE program = " . $dbh->quote($product)); + $dbh->do("UPDATE milestones SET product_id = $product_id " . + "WHERE product = " . $dbh->quote($product)); + $dbh->do("UPDATE bugs SET product_id = $product_id, delta_ts=delta_ts " . + "WHERE product = " . $dbh->quote($product)); + $dbh->do("UPDATE attachstatusdefs SET product_id = $product_id " . + "WHERE product = " . $dbh->quote($product)); + } + + print "Updating the database to use component IDs.\n"; + AddField("components", "id", "smallint not null auto_increment primary key"); + AddField("bugs", "component_id", "smallint not null"); + my %components; + $sth = $dbh->prepare("SELECT id, value, product_id FROM components"); + $sth->execute; + while (my ($component_id, $component, $product_id) = $sth->fetchrow_array()) { + if (exists $components{$component}) { + if (exists $components{$component}{$product_id}) { + print "Ignoring duplicate component $component for product $product_id\n"; + $dbh->do("DELETE FROM components WHERE id = $component_id"); + next; + } + } else { + $components{$component} = {}; + } + $components{$component}{$product_id} = 1; + $dbh->do("UPDATE bugs SET component_id = $component_id, delta_ts=delta_ts " . + "WHERE component = " . $dbh->quote($component) . + " AND product_id = $product_id"); + } + print "Fixing Indexes and Uniqueness.\n"; + $dbh->do("ALTER TABLE milestones DROP INDEX product"); + $dbh->do("ALTER TABLE milestones ADD UNIQUE (product_id, value)"); + $dbh->do("ALTER TABLE bugs DROP INDEX product"); + $dbh->do("ALTER TABLE bugs ADD INDEX (product_id)"); + $dbh->do("ALTER TABLE bugs DROP INDEX component"); + $dbh->do("ALTER TABLE bugs ADD INDEX (component_id)"); + + print "Removing, renaming, and retyping old product and component fields.\n"; + DropField("components", "program"); + DropField("versions", "program"); + DropField("milestones", "product"); + DropField("bugs", "product"); + DropField("bugs", "component"); + DropField("attachstatusdefs", "product"); + RenameField("products", "product", "name"); + ChangeFieldType("products", "name", "varchar(64) not null"); + RenameField("components", "value", "name"); + ChangeFieldType("components", "name", "varchar(64) not null"); + + print "Adding indexes for products and components tables.\n"; + $dbh->do("ALTER TABLE products ADD UNIQUE (name)"); + $dbh->do("ALTER TABLE components ADD UNIQUE (product_id, name)"); + $dbh->do("ALTER TABLE components ADD INDEX (name)"); +} # If you had to change the --TABLE-- definition in any way, then add your # differential change code *** A B O V E *** this comment. diff --git a/collectstats.pl b/collectstats.pl index 4e69ab9b5..a47e2174d 100755 --- a/collectstats.pl +++ b/collectstats.pl @@ -67,6 +67,9 @@ sub collect_stats { my $dir = shift; my $product = shift; my $when = localtime (time); + my $product_id = get_product_id($product) unless $product eq '-All-'; + + die "Unknown product $product" unless ($product_id or $product eq '-All-'); # NB: Need to mangle the product for the filename, but use the real # product name in the query @@ -82,7 +85,7 @@ sub collect_stats { 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='$product'"); + SendSQL("select count(bug_status) from bugs where bug_status='$status' and product_id=$product_id"); } push @row, FetchOneColumn(); @@ -92,7 +95,7 @@ sub collect_stats { 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='$product'"); + SendSQL("select count(resolution) from bugs where resolution='$resolution' and product_id=$product_id"); } push @row, FetchOneColumn(); diff --git a/contrib/bug_email.pl b/contrib/bug_email.pl index cb103bbfb..7817a0f80 100755 --- a/contrib/bug_email.pl +++ b/contrib/bug_email.pl @@ -37,7 +37,7 @@ # # You need to work with bug_email.pl the MIME::Parser installed. # -# $Id: bug_email.pl,v 1.10 2002/07/25 01:47:19 justdave%syndicomm.com Exp $ +# $Id: bug_email.pl,v 1.11 2002/08/12 05:43:05 bbaetz%student.usyd.edu.au Exp $ ############################################################### # 02/12/2000 (SML) @@ -196,7 +196,7 @@ sub CheckPermissions { sub CheckProduct { my $Product = shift; - SendSQL("select product from products where product='$Product'"); + SendSQL("select name from products where name = " . SqlQuote($Product)); my $Result = FetchOneColumn(); if (lc($Result) eq lc($Product)) { return $Result; @@ -211,7 +211,7 @@ sub CheckComponent { my $Product = shift; my $Component = shift; - SendSQL("select value from components where program=" . SqlQuote($Product) . " and value=" . SqlQuote($Component) . ""); + SendSQL("select components.name from components, products where components.product_id = products.id AND products.name=" . SqlQuote($Product) . " and components.name=" . SqlQuote($Component)); my $Result = FetchOneColumn(); if (lc($Result) eq lc($Component)) { return $Result; @@ -226,7 +226,7 @@ sub CheckVersion { my $Product = shift; my $Version = shift; - SendSQL("select value from versions where program=" . SqlQuote($Product) . " and value=" . SqlQuote($Version) . ""); + SendSQL("select value from versions, products where versions.product_id = products.id AND products.name=" . SqlQuote($Product) . " and value=" . SqlQuote($Version)); my $Result = FetchOneColumn(); if (lc($Result) eq lc($Version)) { return $Result; @@ -840,9 +840,9 @@ if (! CheckPermissions("CreateBugs", $SenderShort ) ) { # Set QA if (Param("useqacontact")) { - SendSQL("select initialqacontact from components where program=" . + SendSQL("select initialqacontact from components, products where components.product_id = products.id AND products.name=" . SqlQuote($Control{'product'}) . - " and value=" . SqlQuote($Control{'component'})); + " and components.name=" . SqlQuote($Control{'component'})); $Control{'qacontact'} = FetchOneColumn(); } @@ -863,7 +863,7 @@ if ( $Product eq "" ) { $Text .= "Valid products are:\n\t"; - SendSQL("select product from products"); + SendSQL("select name from products ORDER BY name"); @all_products = FetchAllSQLData(); $Text .= join( "\n\t", @all_products ) . "\n\n"; $Text .= horLine(); @@ -903,7 +903,7 @@ if ( $Component eq "" ) { foreach my $prod ( @all_products ) { $Text .= "\nValid components for product `$prod' are: \n\t"; - SendSQL("select value from components where program=" . SqlQuote( $prod ) . ""); + SendSQL("SELECT components.name FROM components, products WHERE components.product_id=products.id AND products.name = " . SqlQuote($prod)); @val_components = FetchAllSQLData(); $Text .= join( "\n\t", @val_components ) . "\n"; @@ -936,9 +936,10 @@ if ( defined($Control{'assigned_to'}) && $Control{'assigned_to'} !~ /^\s*$/ ) { $Control{'assigned_to'} = DBname_to_id($Control{'assigned_to'}); } else { - SendSQL("select initialowner from components where program=" . + SendSQL("select initialowner from components, products where " . + " components.product_id=products.id AND products.name=" . SqlQuote($Control{'product'}) . - " and value=" . SqlQuote($Control{'component'})); + " and components.name=" . SqlQuote($Control{'component'})); $Control{'assigned_to'} = FetchOneColumn(); } @@ -982,7 +983,7 @@ if ( $Version eq "" ) { foreach my $prod ( @all_products ) { $Text .= "Valid versions for product " . SqlQuote( $prod ) . " are: \n\t"; - SendSQL("select value from versions where program=" . SqlQuote( $prod ) . ""); + SendSQL("select value from versions, products where versions.product_id=products.id AND products.name=" . SqlQuote( $prod )); @all_versions = FetchAllSQLData(); $anz_versions = @all_versions; $Text .= join( "\n\t", @all_versions ) . "\n" ; @@ -1176,11 +1177,20 @@ END my $query = "insert into bugs (\n" . join(",\n", @used_fields ) . ", bug_status, creation_ts, everconfirmed) values ( "; + # 'Yuck'. Then again, this whole file should be rewritten anyway... + $query =~ s/product/product_id/; + $query =~ s/component/component_id/; + my $tmp_reply = "These values were stored by bugzilla:\n"; my $val; foreach my $field (@used_fields) { if( $field eq "groupset" ) { $query .= $Control{$field} . ",\n"; + } elsif ( $field eq 'product' ) { + $query .= get_product_id($Control{$field}) . ",\n"; + } elsif ( $field eq 'component' ) { + $query .= get_component_id(get_product_id($Control{'product'}), + $Control{$field}) . ",\n"; } else { $query .= SqlQuote($Control{$field}) . ",\n"; } @@ -1210,8 +1220,8 @@ END my $ever_confirmed = 0; my $state = SqlQuote("UNCONFIRMED"); - SendSQL("SELECT votestoconfirm FROM products WHERE product = " . - SqlQuote($Control{'product'}) . ";"); + SendSQL("SELECT votestoconfirm FROM products WHERE name = " . + SqlQuote($Control{'product'})); if (!FetchOneColumn()) { $ever_confirmed = 1; $state = SqlQuote("NEW"); diff --git a/describecomponents.cgi b/describecomponents.cgi index 2f723757e..edf9349ab 100755 --- a/describecomponents.cgi +++ b/describecomponents.cgi @@ -23,6 +23,7 @@ use vars qw( %FORM + %legal_product $userid ); @@ -85,9 +86,9 @@ my $product = $::FORM{'product'}; # which could enable people guessing product names to determine # whether or not certain products exist in Bugzilla, even if they # cannot get any other information about that product. -grep($product eq $_ , @::legal_product) - || DisplayError("The product name is invalid.") - && exit; +my $product_id = get_product_id($product); + +ThrowUserError("The product name is invalid.") unless $product_id; # Make sure the user is authorized to access this product. if (Param("usebuggroups") && GroupExists($product)) { @@ -102,9 +103,9 @@ if (Param("usebuggroups") && GroupExists($product)) { ###################################################################### my @components; -SendSQL("SELECT value, initialowner, initialqacontact, description FROM " . - "components WHERE program = " . SqlQuote($product) . " ORDER BY " . - "value"); +SendSQL("SELECT name, initialowner, initialqacontact, description FROM " . + "components WHERE product_id = $product_id ORDER BY " . + "name"); while (MoreSQLData()) { my ($name, $initialowner, $initialqacontact, $description) = FetchSQLData(); diff --git a/duplicates.cgi b/duplicates.cgi index 3d0875313..715b4be28 100755 --- a/duplicates.cgi +++ b/duplicates.cgi @@ -62,6 +62,15 @@ my $product = formvalue("product"); my $sortvisible = formvalue("sortvisible"); my @buglist = (split(/[:,]/, formvalue("bug_id"))); +my $product_id; +if ($product) { + $product_id = get_product_id($product); + if (!$product_id) { + ThrowUserError("The product " . html_quote($product) . + " does not exist"); + } +} + # Small backwards-compatibility hack, dated 2002-04-10. $sortby = "count" if $sortby eq "dup_count"; @@ -143,16 +152,17 @@ if (scalar(%count)) { # WONTFIX. We want to see VERIFIED INVALID and WONTFIX because common # "bugs" which aren't bugs end up in this state. my $query = " - SELECT bugs.bug_id, component, bug_severity, op_sys, target_milestone, - short_desc, bug_status, resolution - FROM bugs - WHERE (bug_status != 'CLOSED') - AND ((bug_status = 'VERIFIED' AND resolution IN ('INVALID', 'WONTFIX')) + SELECT bugs.bug_id, components.name, bug_severity, op_sys, + target_milestone, short_desc, bug_status, resolution + FROM bugs, components + WHERE (bugs.component_id = components.id) + AND (bug_status != 'CLOSED') + AND ((bug_status = 'VERIFIED' AND resolution IN ('INVALID', 'WONTFIX')) OR (bug_status != 'VERIFIED')) AND bugs.bug_id IN (" . join(", ", keys %count) . ")"; - # Limit to a single product if requested - $query .= (" AND product = " . SqlQuote($product)) if $product; + # Limit to a single product if requested + $query .= (" AND bugs.product_id = " . $product_id) if $product_id; SendSQL(SelectVisible($query, $userid, diff --git a/editattachstatuses.cgi b/editattachstatuses.cgi index 910379b99..ff7822181 100755 --- a/editattachstatuses.cgi +++ b/editattachstatuses.cgi @@ -155,7 +155,7 @@ sub validateSortKey sub validateProduct { # Retrieve a list of products. - SendSQL("SELECT product FROM products"); + SendSQL("SELECT name FROM products"); my @products; push(@products, FetchSQLData()) while MoreSQLData(); @@ -180,11 +180,13 @@ sub list # Retrieve a list of attachment status flags and create an array of hashes # in which each hash contains the data for one flag. - SendSQL("SELECT id, name, description, sortkey, product, count(statusid) - FROM attachstatusdefs LEFT JOIN attachstatuses - ON attachstatusdefs.id=attachstatuses.statusid - GROUP BY id - ORDER BY sortkey"); + SendSQL("SELECT attachstatusdefs.id, attachstatusdefs.name, " . + "attachstatusdefs.description, attachstatusdefs.sortkey, products.name, " . + "count(attachstatusdefs.id) " . + "FROM attachstatusdefs, products " . + "WHERE products.id = attachstatusdefs.product_id " . + "GROUP BY id " . + "ORDER BY attachstatusdefs.sortkey"); my @statusdefs; while ( MoreSQLData() ) { @@ -212,7 +214,7 @@ sub create # Display a form for creating a new attachment status flag. # Retrieve a list of products to which the attachment status may apply. - SendSQL("SELECT product FROM products"); + SendSQL("SELECT name FROM products"); my @products; push(@products, FetchSQLData()) while MoreSQLData(); @@ -236,14 +238,13 @@ sub insert # in a SQL statement. my $name = SqlQuote($::FORM{'name'}); my $desc = SqlQuote($::FORM{'desc'}); - my $product = SqlQuote($::FORM{'product'}); + my $product_id = get_product_id($::FORM{'product'}); SendSQL("LOCK TABLES attachstatusdefs WRITE"); SendSQL("SELECT MAX(id) FROM attachstatusdefs"); - my $id = FetchSQLData() || 0; - $id++; - SendSQL("INSERT INTO attachstatusdefs (id, name, description, sortkey, product) - VALUES ($id, $name, $desc, $::FORM{'sortkey'}, $product)"); + my $id = FetchSQLData() + 1; + SendSQL("INSERT INTO attachstatusdefs (id, name, description, sortkey, product_id) + VALUES ($id, $name, $desc, $::FORM{'sortkey'}, $product_id)"); SendSQL("UNLOCK TABLES"); # Display the "administer attachment status flags" page @@ -257,8 +258,11 @@ sub edit # Display a form for editing an existing attachment status flag. # Retrieve the definition from the database. - SendSQL("SELECT name, description, sortkey, product - FROM attachstatusdefs WHERE id = $::FORM{'id'}"); + SendSQL("SELECT attachstatusdefs.name, attachstatusdefs.description, " . + " attachstatusdefs.sortkey, products.name " . + "FROM attachstatusdefs, products " . + "WHERE attachstatusdefs.product_id = products.id " . + " AND attachstatusdefs.id = $::FORM{'id'}"); my ($name, $desc, $sortkey, $product) = FetchSQLData(); # Define the variables and functions that will be passed to the UI template. diff --git a/editcomponents.cgi b/editcomponents.cgi index 576f01375..3f1619739 100755 --- a/editcomponents.cgi +++ b/editcomponents.cgi @@ -55,9 +55,9 @@ sub TestProduct ($) my $prod = shift; # does the product exist? - SendSQL("SELECT product + SendSQL("SELECT name FROM products - WHERE product=" . SqlQuote($prod)); + WHERE name=" . SqlQuote($prod)); return FetchOneColumn(); } @@ -84,9 +84,10 @@ sub TestComponent ($$) my ($prod,$comp) = @_; # does the product exist? - SendSQL("SELECT program,value - FROM components - WHERE program=" . SqlQuote($prod) . " and value=" . SqlQuote($comp)); + SendSQL("SELECT components.name + FROM components, products + WHERE products.id = components.product_id + AND products.name=" . SqlQuote($prod) . " AND components.name=" . SqlQuote($comp)); return FetchOneColumn(); } @@ -225,15 +226,14 @@ unless ($product) { PutHeader("Select product"); if ($dobugcounts){ - SendSQL("SELECT products.product,products.description,COUNT(bug_id) - FROM products LEFT JOIN bugs - ON products.product=bugs.product - GROUP BY products.product - ORDER BY products.product"); + SendSQL("SELECT products.name,products.description,COUNT(bug_id) + FROM products LEFT JOIN bugs ON products.id = bugs.product_id + GROUP BY products.name + ORDER BY products.name"); } else { - SendSQL("SELECT products.product,products.description + SendSQL("SELECT products.name,products.description FROM products - ORDER BY products.product"); + ORDER BY products.name"); } print "\n"; print " \n"; @@ -270,18 +270,18 @@ unless ($product) { unless ($action) { PutHeader("Select component of $product"); CheckProduct($product); + my $product_id = get_product_id($product); if ($dobugcounts) { - SendSQL("SELECT value,description,initialowner,initialqacontact,COUNT(bug_id) - FROM components LEFT JOIN bugs - ON components.program=bugs.product AND components.value=bugs.component - WHERE program=" . SqlQuote($product) . " - GROUP BY value"); + SendSQL("SELECT name,description,initialowner,initialqacontact,COUNT(bug_id) + FROM components LEFT JOIN bugs ON components.id = bugs.component_id + WHERE components.product_id=$product_id + GROUP BY name"); } else { - SendSQL("SELECT value,description,initialowner,initialqacontact + SendSQL("SELECT name,description,initialowner,initialqacontact FROM components - WHERE program=" . SqlQuote($product) . " - GROUP BY value"); + WHERE product_id=$product_id + GROUP BY name"); } print "
Edit components of ...
\n"; print " \n"; @@ -370,6 +370,7 @@ if ($action eq 'add') { if ($action eq 'new') { PutHeader("Adding new component of $product"); CheckProduct($product); + my $product_id = get_product_id($product); # Cleanups and valididy checks @@ -426,9 +427,9 @@ if ($action eq 'new') { # Add the new component SendSQL("INSERT INTO components ( " . - "program, value, description, initialowner, initialqacontact " . + "product_id, name, description, initialowner, initialqacontact " . " ) VALUES ( " . - SqlQuote($product) . "," . + $product_id . "," . SqlQuote($component) . "," . SqlQuote($description) . "," . SqlQuote($initialownerid) . "," . @@ -457,20 +458,20 @@ if ($action eq 'new') { if ($action eq 'del') { PutHeader("Delete component of $product"); CheckComponent($product, $component); + my $component_id = get_component_id(get_product_id($product), $component); # display some data about the component - SendSQL("SELECT products.product,products.description, + SendSQL("SELECT products.name,products.description, products.milestoneurl,products.disallownew, - components.program,components.value,components.initialowner, + components.name,components.initialowner, components.initialqacontact,components.description FROM products - LEFT JOIN components on product=program - WHERE product=" . SqlQuote($product) . " - AND value=" . SqlQuote($component) ); + LEFT JOIN components ON products.id = components.product_id + WHERE components.id = $component_id"); my ($product,$pdesc,$milestoneurl,$disallownew, - $dummy,$component,$initialownerid,$initialqacontactid,$cdesc) = FetchSQLData(); + $component,$initialownerid,$initialqacontactid,$cdesc) = FetchSQLData(); my $initialowner = $initialownerid ? DBID_to_name ($initialownerid) : "missing"; my $initialqacontact = $initialqacontactid ? DBID_to_name ($initialqacontactid) : "missing"; @@ -503,8 +504,7 @@ if ($action eq 'del') { } SendSQL("SELECT count(bug_id) FROM bugs - WHERE product=" . SqlQuote($product) . " - AND component=" . SqlQuote($component)); + WHERE component_id = $component_id"); print "\n"; print " \n"; @@ -574,6 +574,7 @@ one."; if ($action eq 'delete') { PutHeader("Deleting component of $product"); CheckComponent($product,$component); + my $component_id = get_component_id(get_product_id($product),$component); # lock the tables before we start to change everything: @@ -590,8 +591,7 @@ if ($action eq 'delete') { if (Param("allowbugdeletion")) { SendSQL("SELECT bug_id FROM bugs - WHERE product=" . SqlQuote($product) . " - AND component=" . SqlQuote($component)); + WHERE component_id=$component_id"); while (MoreSQLData()) { my $bugid = FetchOneColumn(); @@ -607,14 +607,12 @@ if ($action eq 'delete') { # Deleting the rest is easier: SendSQL("DELETE FROM bugs - WHERE product=" . SqlQuote($product) . " - AND component=" . SqlQuote($component)); + WHERE component_id=$component_id"); print "Bugs deleted.
\n"; } SendSQL("DELETE FROM components - WHERE program=" . SqlQuote($product) . " - AND value=" . SqlQuote($component)); + WHERE id=$component_id"); print "Components deleted.

\n"; SendSQL("UNLOCK TABLES"); @@ -634,19 +632,18 @@ if ($action eq 'delete') { if ($action eq 'edit') { PutHeader("Edit component of $product"); CheckComponent($product,$component); + my $component_id = get_component_id(get_product_id($product),$component); # get data of component - SendSQL("SELECT products.product,products.description, + SendSQL("SELECT products.name,products.description, products.milestoneurl,products.disallownew, - components.program,components.value,components.initialowner, + components.name,components.initialowner, components.initialqacontact,components.description - FROM products - LEFT JOIN components on product=program - WHERE product=" . SqlQuote($product) . " - AND value=" . SqlQuote($component) ); + FROM products LEFT JOIN components ON products.id = components.product_id + WHERE components.id = $component_id"); my ($product,$pdesc,$milestoneurl,$disallownew, - $dummy,$component,$initialownerid,$initialqacontactid,$cdesc) = FetchSQLData(); + $component,$initialownerid,$initialqacontactid,$cdesc) = FetchSQLData(); my $initialowner = $initialownerid ? DBID_to_name ($initialownerid) : ''; my $initialqacontact = $initialqacontactid ? DBID_to_name ($initialqacontactid) : ''; @@ -663,8 +660,7 @@ if ($action eq 'edit') { print "

Edit component ...
Component of product:"; SendSQL("SELECT count(*) FROM bugs - WHERE product=" . SqlQuote($product) . - " and component=" . SqlQuote($component)); + WHERE component_id=$component_id"); my $bugs = ''; $bugs = FetchOneColumn() if MoreSQLData(); print $bugs || 'none'; @@ -707,13 +703,13 @@ if ($action eq 'update') { my $initialqacontact = trim($::FORM{initialqacontact} || ''); my $initialqacontactold = trim($::FORM{initialqacontactold} || ''); - CheckComponent($product,$componentold); - # Note that the order of this tests is important. If you change # them, be sure to test for WHERE='$component' or WHERE='$componentold' - SendSQL("LOCK TABLES bugs WRITE, - components WRITE, profiles READ"); + SendSQL("LOCK TABLES components WRITE, products READ, profiles READ"); + CheckComponent($product,$componentold); + my $component_id = get_component_id(get_product_id($product), + $componentold); if ($description ne $descriptionold) { unless ($description) { @@ -724,8 +720,7 @@ if ($action eq 'update') { } SendSQL("UPDATE components SET description=" . SqlQuote($description) . " - WHERE program=" . SqlQuote($product) . " - AND value=" . SqlQuote($componentold)); + WHERE id=$component_id"); print "Updated description.
\n"; } @@ -748,8 +743,7 @@ if ($action eq 'update') { SendSQL("UPDATE components SET initialowner=" . SqlQuote($initialownerid) . " - WHERE program=" . SqlQuote($product) . " - AND value=" . SqlQuote($componentold)); + WHERE id = $component_id"); print "Updated initial owner.
\n"; } @@ -764,8 +758,7 @@ if ($action eq 'update') { SendSQL("UPDATE components SET initialqacontact=" . SqlQuote($initialqacontactid) . " - WHERE program=" . SqlQuote($product) . " - AND value=" . SqlQuote($componentold)); + WHERE id = $component_id"); print "Updated initial QA contact.
\n"; } @@ -784,15 +777,8 @@ if ($action eq 'update') { exit; } - SendSQL("UPDATE bugs - SET component=" . SqlQuote($component) . ", - delta_ts = delta_ts - WHERE component=" . SqlQuote($componentold) . " - AND product=" . SqlQuote($product)); - SendSQL("UPDATE components - SET value=" . SqlQuote($component) . " - WHERE value=" . SqlQuote($componentold) . " - AND program=" . SqlQuote($product)); + SendSQL("UPDATE components SET name=" . SqlQuote($component) . + "WHERE id=$component_id"); unlink "data/versioncache"; print "Updated component name.
\n"; diff --git a/editgroups.cgi b/editgroups.cgi index 9c93363c0..623cf47d3 100755 --- a/editgroups.cgi +++ b/editgroups.cgi @@ -422,7 +422,7 @@ this box. It is strongly suggested that you review the bugs in this group before checking the box.

"; } - SendSQL("SELECT product FROM products WHERE product=" . SqlQuote($name)); + SendSQL("SELECT name FROM products WHERE name=" . SqlQuote($name)); if (MoreSQLData()) { $cantdelete = 1; print " @@ -489,7 +489,7 @@ if ($action eq 'delete') { $cantdelete = 1; } } - SendSQL("SELECT product FROM products WHERE product=" . SqlQuote($name)); + SendSQL("SELECT name FROM products WHERE name=" . SqlQuote($name)); if (FetchOneColumn()) { if (!defined $::FORM{'unbind'}) { $cantdelete = 1; diff --git a/editmilestones.cgi b/editmilestones.cgi index fccf72533..67d84fcce 100755 --- a/editmilestones.cgi +++ b/editmilestones.cgi @@ -33,9 +33,9 @@ sub TestProduct ($) my $prod = shift; # does the product exist? - SendSQL("SELECT product + SendSQL("SELECT name FROM products - WHERE product=" . SqlQuote($prod)); + WHERE name=" . SqlQuote($prod)); return FetchOneColumn(); } @@ -62,9 +62,9 @@ sub TestMilestone ($$) my ($prod,$mile) = @_; # does the product exist? - SendSQL("SELECT product,value - FROM milestones - WHERE product=" . SqlQuote($prod) . " and value=" . SqlQuote($mile)); + SendSQL("SELECT products.name, value + FROM milestones, products + WHERE milestones.product_id=products.id AND products.name=" . SqlQuote($prod) . " and value=" . SqlQuote($mile)); return FetchOneColumn(); } @@ -183,10 +183,10 @@ if ($milestone) { unless ($product) { PutHeader("Select product"); - SendSQL("SELECT products.product,products.description,'xyzzy' + SendSQL("SELECT products.name,products.description,'xyzzy' FROM products - GROUP BY products.product - ORDER BY products.product"); + GROUP BY products.name + ORDER BY products.name"); print "\n"; print " \n"; print " \n"; @@ -216,10 +216,11 @@ unless ($product) { unless ($action) { PutHeader("Select milestone for $product"); CheckProduct($product); + my $product_id = get_product_id($product); SendSQL("SELECT value,sortkey FROM milestones - WHERE product=" . SqlQuote($product) . " + WHERE product_id=$product_id ORDER BY sortkey,value"); print "
Edit milestones of ...Description
\n"; @@ -259,6 +260,7 @@ unless ($action) { if ($action eq 'add') { PutHeader("Add milestone for $product"); CheckProduct($product); + my $product_id = get_product_id($product); #print "This page lets you add a new milestone to a $::bugzilla_name tracked product.\n"; @@ -287,6 +289,7 @@ if ($action eq 'add') { if ($action eq 'new') { PutHeader("Adding new milestone for $product"); CheckProduct($product); + my $product_id = get_product_id($product); # Cleanups and valididy checks @@ -305,10 +308,9 @@ if ($action eq 'new') { # Add the new milestone SendSQL("INSERT INTO milestones ( " . - "value, product, sortkey" . + "value, product_id, sortkey" . " ) VALUES ( " . - SqlQuote($milestone) . "," . - SqlQuote($product) . ", $sortkey)"); + SqlQuote($milestone) . ", $product_id, $sortkey)"); # Make versioncache flush unlink "data/versioncache"; @@ -330,16 +332,17 @@ if ($action eq 'new') { if ($action eq 'del') { PutHeader("Delete milestone of $product"); CheckMilestone($product, $milestone); + my $product_id = get_product_id($product); - SendSQL("SELECT count(bug_id),product,target_milestone + SendSQL("SELECT count(bug_id), product_id, target_milestone FROM bugs - GROUP BY product,target_milestone - HAVING product=" . SqlQuote($product) . " + GROUP BY product_id, target_milestone + HAVING product_id=$product_id AND target_milestone=" . SqlQuote($milestone)); my $bugs = FetchOneColumn(); SendSQL("SELECT defaultmilestone FROM products " . - "WHERE product=" . SqlQuote($product)); + "WHERE id=$product_id"); my $defaultmilestone = FetchOneColumn(); print "
\n"; @@ -405,6 +408,7 @@ one."; if ($action eq 'delete') { PutHeader("Deleting milestone of $product"); CheckMilestone($product,$milestone); + my $product_id = get_product_id($product); # lock the tables before we start to change everything: @@ -422,7 +426,7 @@ if ($action eq 'delete') { SendSQL("SELECT bug_id FROM bugs - WHERE product=" . SqlQuote($product) . " + WHERE product_id=$product_id AND target_milestone=" . SqlQuote($milestone)); while (MoreSQLData()) { my $bugid = FetchOneColumn(); @@ -439,13 +443,13 @@ if ($action eq 'delete') { # Deleting the rest is easier: SendSQL("DELETE FROM bugs - WHERE product=" . SqlQuote($product) . " + WHERE product_id=$product_id AND target_milestone=" . SqlQuote($milestone)); print "Bugs deleted.
\n"; } SendSQL("DELETE FROM milestones - WHERE product=" . SqlQuote($product) . " + WHERE product_id=$product_id AND value=" . SqlQuote($milestone)); print "Milestone deleted.

\n"; SendSQL("UNLOCK TABLES"); @@ -466,9 +470,10 @@ if ($action eq 'delete') { if ($action eq 'edit') { PutHeader("Edit milestone of $product"); CheckMilestone($product,$milestone); + my $product_id = get_product_id($product); - SendSQL("SELECT sortkey FROM milestones WHERE product=" . - SqlQuote($product) . " AND value = " . SqlQuote($milestone)); + SendSQL("SELECT sortkey FROM milestones WHERE product_id=$product_id " . + " AND value = " . SqlQuote($milestone)); my $sortkey = FetchOneColumn(); print "
\n"; @@ -506,6 +511,7 @@ if ($action eq 'update') { my $sortkeyold = trim($::FORM{sortkeyold} || '0'); CheckMilestone($product,$milestoneold); + my $product_id = get_product_id($product); SendSQL("LOCK TABLES bugs WRITE, milestones WRITE, @@ -535,14 +541,14 @@ if ($action eq 'update') { SET target_milestone=" . SqlQuote($milestone) . ", delta_ts=delta_ts WHERE target_milestone=" . SqlQuote($milestoneold) . " - AND product=" . SqlQuote($product)); + AND product_id=$product_id"); SendSQL("UPDATE milestones SET value=" . SqlQuote($milestone) . " - WHERE product=" . SqlQuote($product) . " + WHERE product_id=$product_id AND value=" . SqlQuote($milestoneold)); SendSQL("UPDATE products " . "SET defaultmilestone = " . SqlQuote($milestone) . - "WHERE product = " . SqlQuote($product) . + " WHERE id = $product_id" . " AND defaultmilestone = " . SqlQuote($milestoneold)); unlink "data/versioncache"; print "Updated milestone.
\n"; diff --git a/editproducts.cgi b/editproducts.cgi index 1b3441a8a..4b0698b35 100755 --- a/editproducts.cgi +++ b/editproducts.cgi @@ -51,9 +51,9 @@ sub TestProduct ($) my $prod = shift; # does the product exist? - SendSQL("SELECT product + SendSQL("SELECT name FROM products - WHERE product=" . SqlQuote($prod)); + WHERE name=" . SqlQuote($prod)); return FetchOneColumn(); } @@ -206,12 +206,11 @@ my $localtrailer = "edit more products"; unless ($action) { PutHeader("Select product"); - SendSQL("SELECT products.product,description,disallownew, + SendSQL("SELECT products.name,description,disallownew, votesperuser,maxvotesperbug,votestoconfirm,COUNT(bug_id) - FROM products LEFT JOIN bugs - ON products.product=bugs.product - GROUP BY products.product - ORDER BY products.product"); + FROM products LEFT JOIN bugs ON products.id = bugs.product_id + GROUP BY products.name + ORDER BY products.name"); print "

\n"; print " \n"; print " \n"; @@ -330,7 +329,7 @@ if ($action eq 'new') { # Add the new product. SendSQL("INSERT INTO products ( " . - "product, description, milestoneurl, disallownew, votesperuser, " . + "name, description, milestoneurl, disallownew, votesperuser, " . "maxvotesperbug, votestoconfirm, defaultmilestone" . " ) VALUES ( " . SqlQuote($product) . "," . @@ -339,14 +338,16 @@ if ($action eq 'new') { $disallownew . "," . "$votesperuser, $maxvotesperbug, $votestoconfirm, " . SqlQuote($defaultmilestone) . ")"); + SendSQL("SELECT LAST_INSERT_ID()"); + my $product_id = FetchOneColumn(); SendSQL("INSERT INTO versions ( " . - "value, program" . + "value, product_id" . " ) VALUES ( " . SqlQuote($version) . "," . - SqlQuote($product) . ")" ); + $product_id . ")" ); - SendSQL("INSERT INTO milestones (product, value) VALUES (" . - SqlQuote($product) . ", " . SqlQuote($defaultmilestone) . ")"); + SendSQL("INSERT INTO milestones (product_id, value) VALUES (" . + $product_id . ", " . SqlQuote($defaultmilestone) . ")"); # If we're using bug groups, then we need to create a group for this # product as well. -JMR, 2/16/00 @@ -416,10 +417,10 @@ if ($action eq 'del') { CheckProduct($product); # display some data about the product - SendSQL("SELECT description, milestoneurl, disallownew + SendSQL("SELECT product_id, description, milestoneurl, disallownew FROM products - WHERE product=" . SqlQuote($product)); - my ($description, $milestoneurl, $disallownew) = FetchSQLData(); + WHERE name=" . SqlQuote($product)); + my ($product_id, $description, $milestoneurl, $disallownew) = FetchSQLData(); my $milestonelink = $milestoneurl ? "$milestoneurl" : "missing"; $description ||= "description missing"; @@ -468,9 +469,9 @@ if ($action eq 'del') { print "\n"; print " \n"; print "
Edit product ...Description
Components:"; - SendSQL("SELECT value,description + SendSQL("SELECT name,description FROM components - WHERE program=" . SqlQuote($product)); + WHERE product_id=$product_id"); if (MoreSQLData()) { print ""; while ( MoreSQLData() ) { @@ -489,7 +490,7 @@ if ($action eq 'del') { print " \n\n"; print " \n"; print " \n"; print " \n"; print "
"; SendSQL("SELECT value FROM versions - WHERE program=" . SqlQuote($product) . " + WHERE product_id=$product_id ORDER BY value"); if (MoreSQLData()) { my $br = 0; @@ -512,7 +513,7 @@ if ($action eq 'del') { print " "; SendSQL("SELECT value FROM milestones - WHERE product=" . SqlQuote($product) . " + WHERE product_id=$product_id ORDER BY sortkey,value"); if(MoreSQLData()) { my $br = 0; @@ -530,10 +531,10 @@ if ($action eq 'del') { print "
Bugs:"; - SendSQL("SELECT count(bug_id),product + SendSQL("SELECT count(bug_id),product_id FROM bugs - GROUP BY product - HAVING product=" . SqlQuote($product)); + GROUP BY product_id + HAVING product_id=$product_id"); my $bugs = FetchOneColumn(); print $bugs || 'none'; @@ -578,6 +579,7 @@ one."; if ($action eq 'delete') { PutHeader("Deleting product"); CheckProduct($product); + my $product_id = get_product_id($product); # lock the tables before we start to change everything: @@ -599,7 +601,7 @@ if ($action eq 'delete') { if (Param("allowbugdeletion")) { SendSQL("SELECT bug_id FROM bugs - WHERE product=" . SqlQuote($product)); + WHERE product_id=$product_id"); while (MoreSQLData()) { my $bugid = FetchOneColumn(); @@ -615,25 +617,25 @@ if ($action eq 'delete') { # Deleting the rest is easier: SendSQL("DELETE FROM bugs - WHERE product=" . SqlQuote($product)); + WHERE product_id=$product_id"); print "Bugs deleted.
\n"; } SendSQL("DELETE FROM components - WHERE program=" . SqlQuote($product)); + WHERE product_id=$product_id"); print "Components deleted.
\n"; SendSQL("DELETE FROM versions - WHERE program=" . SqlQuote($product)); + WHERE product_id=$product_id"); print "Versions deleted.

\n"; # deleting associated target milestones - matthew@zeroknowledge.com SendSQL("DELETE FROM milestones - WHERE product=" . SqlQuote($product)); + WHERE product_id=$product_id"); print "Milestones deleted.
\n"; SendSQL("DELETE FROM products - WHERE product=" . SqlQuote($product)); + WHERE product_id=$product_id"); print "Product '$product' deleted.
\n"; # Added -JMR, 2/16/00 @@ -681,11 +683,11 @@ if ($action eq 'edit') { CheckProduct($product); # get data of product - SendSQL("SELECT description,milestoneurl,disallownew, + SendSQL("SELECT id,description,milestoneurl,disallownew, votesperuser,maxvotesperbug,votestoconfirm,defaultmilestone FROM products - WHERE product=" . SqlQuote($product)); - my ($description, $milestoneurl, $disallownew, + WHERE name=" . SqlQuote($product)); + my ($product_id,$description, $milestoneurl, $disallownew, $votesperuser, $maxvotesperbug, $votestoconfirm, $defaultmilestone) = FetchSQLData(); @@ -707,9 +709,9 @@ if ($action eq 'edit') { print "

Edit components:"; - SendSQL("SELECT value,description + SendSQL("SELECT name,description FROM components - WHERE program=" . SqlQuote($product)); + WHERE product_id=$product_id"); if (MoreSQLData()) { print ""; while ( MoreSQLData() ) { @@ -729,7 +731,7 @@ if ($action eq 'edit') { print " \n\n"; print " \n"; print "
"; SendSQL("SELECT value FROM versions - WHERE program=" . SqlQuote($product) . " + WHERE product_id=$product_id ORDER BY value"); if (MoreSQLData()) { my $br = 0; @@ -752,7 +754,7 @@ if ($action eq 'edit') { print " "; SendSQL("SELECT value FROM milestones - WHERE product=" . SqlQuote($product) . " + WHERE product_id=$product_id ORDER BY sortkey,value"); if(MoreSQLData()) { my $br = 0; @@ -770,10 +772,10 @@ if ($action eq 'edit') { print "
Bugs:"; - SendSQL("SELECT count(bug_id),product + SendSQL("SELECT count(bug_id),product_id FROM bugs - GROUP BY product - HAVING product=" . SqlQuote($product)); + GROUP BY product_id + HAVING product_id=$product_id"); my $bugs = ''; $bugs = FetchOneColumn() if MoreSQLData(); print $bugs || 'none'; @@ -837,6 +839,7 @@ if ($action eq 'update') { my $checkvotes = 0; CheckProduct($productold); + my $product_id = get_product_id($productold); if ($maxvotesperbug !~ /^\d+$/ || $maxvotesperbug <= 0) { print "Sorry, the max votes per bug must be a positive integer."; @@ -844,22 +847,20 @@ if ($action eq 'update') { exit; } - # Note that the order of this tests is important. If you change - # them, be sure to test for WHERE='$product' or WHERE='$productold' + # Note that we got the $product_id using $productold above so it will + # remain static even after we rename the product in the database. - SendSQL("LOCK TABLES bugs WRITE, - components WRITE, - products WRITE, - versions WRITE, + SendSQL("LOCK TABLES products WRITE, + versions READ, groups WRITE, profiles WRITE, - milestones WRITE"); + milestones READ"); if ($disallownew ne $disallownewold) { $disallownew ||= 0; SendSQL("UPDATE products SET disallownew=$disallownew - WHERE product=" . SqlQuote($productold)); + WHERE id=$product_id"); print "Updated bug submit status.
\n"; } @@ -872,14 +873,14 @@ if ($action eq 'update') { } SendSQL("UPDATE products SET description=" . SqlQuote($description) . " - WHERE product=" . SqlQuote($productold)); + WHERE id=$product_id"); print "Updated description.
\n"; } if (Param('usetargetmilestone') && $milestoneurl ne $milestoneurlold) { SendSQL("UPDATE products SET milestoneurl=" . SqlQuote($milestoneurl) . " - WHERE product=" . SqlQuote($productold)); + WHERE id=$product_id"); print "Updated mile stone URL.
\n"; } @@ -949,7 +950,7 @@ if ($action eq 'update') { if ($votesperuser ne $votesperuserold) { SendSQL("UPDATE products SET votesperuser=$votesperuser - WHERE product=" . SqlQuote($productold)); + WHERE id=$product_id"); print "Updated votes per user.
\n"; $checkvotes = 1; } @@ -958,7 +959,7 @@ if ($action eq 'update') { if ($maxvotesperbug ne $maxvotesperbugold) { SendSQL("UPDATE products SET maxvotesperbug=$maxvotesperbug - WHERE product=" . SqlQuote($productold)); + WHERE id=$product_id"); print "Updated max votes per bug.
\n"; $checkvotes = 1; } @@ -967,7 +968,7 @@ if ($action eq 'update') { if ($votestoconfirm ne $votestoconfirmold) { SendSQL("UPDATE products SET votestoconfirm=$votestoconfirm - WHERE product=" . SqlQuote($productold)); + WHERE id=$product_id"); print "Updated votes to confirm.
\n"; $checkvotes = 1; } @@ -976,7 +977,7 @@ if ($action eq 'update') { if ($defaultmilestone ne $defaultmilestoneold) { SendSQL("SELECT value FROM milestones " . "WHERE value = " . SqlQuote($defaultmilestone) . - " AND product = " . SqlQuote($productold)); + " AND product_id = $product_id"); if (!FetchOneColumn()) { print "Sorry, the milestone $defaultmilestone must be defined first."; SendSQL("UNLOCK TABLES"); @@ -985,7 +986,7 @@ if ($action eq 'update') { } SendSQL("UPDATE products " . "SET defaultmilestone = " . SqlQuote($defaultmilestone) . - "WHERE product=" . SqlQuote($productold)); + "WHERE id=$product_id"); print "Updated default milestone.
\n"; } @@ -1006,11 +1007,7 @@ if ($action eq 'update') { exit; } - SendSQL("UPDATE bugs SET product=$qp, delta_ts=delta_ts WHERE product=$qpold"); - SendSQL("UPDATE components SET program=$qp WHERE program=$qpold"); - SendSQL("UPDATE products SET product=$qp WHERE product=$qpold"); - SendSQL("UPDATE versions SET program=$qp WHERE program=$qpold"); - SendSQL("UPDATE milestones SET product=$qp WHERE product=$qpold"); + SendSQL("UPDATE products SET name=$qp WHERE id=$product_id"); # Need to do an update to groups as well. If there is a corresponding # bug group, whether usebuggroups is currently set or not, we want to # update it so it will match in the future. If there is no group, this @@ -1031,7 +1028,7 @@ if ($action eq 'update') { SendSQL("SELECT votes.who, votes.bug_id " . "FROM votes, bugs " . "WHERE bugs.bug_id = votes.bug_id " . - " AND bugs.product = $qp " . + " AND bugs.product_id = $product_id " . " AND votes.count > $maxvotesperbug"); my @list; while (MoreSQLData()) { @@ -1047,7 +1044,7 @@ if ($action eq 'update') { } SendSQL("SELECT votes.who, votes.count FROM votes, bugs " . "WHERE bugs.bug_id = votes.bug_id " . - " AND bugs.product = $qp"); + " AND bugs.product_id = $product_id"); my %counts; while (MoreSQLData()) { my ($who, $count) = (FetchSQLData()); @@ -1061,7 +1058,7 @@ if ($action eq 'update') { if ($counts{$who} > $votesperuser) { SendSQL("SELECT votes.bug_id FROM votes, bugs " . "WHERE bugs.bug_id = votes.bug_id " . - " AND bugs.product = $qp " . + " AND bugs.product_id = $product_id " . " AND votes.who = $who"); while (MoreSQLData()) { my $id = FetchSQLData(); @@ -1073,7 +1070,7 @@ if ($action eq 'update') { } } SendSQL("SELECT bug_id FROM bugs " . - "WHERE product = $qp " . + "WHERE product_id = $product_id " . " AND bug_status = '$::unconfirmedstate' " . " AND votes >= $votestoconfirm"); my @list; diff --git a/editusers.cgi b/editusers.cgi index f9800c704..0e25f0ac3 100755 --- a/editusers.cgi +++ b/editusers.cgi @@ -579,9 +579,10 @@ if ($action eq 'del') { # Check if the user is an initialowner my $nodelete = ''; - SendSQL("SELECT program, value - FROM components - WHERE initialowner=" . DBname_to_id($user)); + SendSQL("SELECT products.name, components.name " . + "FROM products, components " . + "WHERE products.id = components.product_id " . + " AND initialowner=" . DBname_to_id($user)); $found = 0; while (MoreSQLData()) { if ($found) { @@ -603,9 +604,10 @@ if ($action eq 'del') { # Check if the user is an initialqacontact - SendSQL("SELECT program, value - FROM components - WHERE initialqacontact=" . DBname_to_id($user)); + SendSQL("SELECT products.name, components.name " . + "FROM products, components " . + "WHERE products.id = components.id " . + " AND initialqacontact=" . DBname_to_id($user)); $found = 0; while (MoreSQLData()) { if ($found) { diff --git a/editversions.cgi b/editversions.cgi index 950d597a7..abeed2570 100755 --- a/editversions.cgi +++ b/editversions.cgi @@ -46,9 +46,9 @@ sub TestProduct ($) my $prod = shift; # does the product exist? - SendSQL("SELECT product + SendSQL("SELECT name FROM products - WHERE product=" . SqlQuote($prod)); + WHERE name=" . SqlQuote($prod)); return FetchOneColumn(); } @@ -75,9 +75,9 @@ sub TestVersion ($$) my ($prod,$ver) = @_; # does the product exist? - SendSQL("SELECT program,value - FROM versions - WHERE program=" . SqlQuote($prod) . " and value=" . SqlQuote($ver)); + SendSQL("SELECT products.name,value + FROM versions, products + WHERE versions.product_id=products.id AND products.name=" . SqlQuote($prod) . " and value=" . SqlQuote($ver)); return FetchOneColumn(); } @@ -191,10 +191,10 @@ if ($version) { unless ($product) { PutHeader("Select product"); - SendSQL("SELECT products.product,products.description,'xyzzy' + SendSQL("SELECT products.name,products.description,'xyzzy' FROM products - GROUP BY products.product - ORDER BY products.product"); + GROUP BY products.name + ORDER BY products.name"); print "\n"; print " \n"; print " \n"; @@ -217,8 +217,6 @@ unless ($product) { exit; } - - # # action='' -> Show nice list of versions # @@ -226,24 +224,11 @@ unless ($product) { unless ($action) { PutHeader("Select version of $product"); CheckProduct($product); + my $product_id = get_product_id($product); -=for me - - # Das geht nicht wie vermutet. Ich bekomme nicht alle Versionen - # angezeigt! Schade. Ich würde gerne sehen, wieviel Bugs pro - # Version angegeben sind ... - - SendSQL("SELECT value,program,COUNT(bug_id) - FROM versions LEFT JOIN bugs - ON program=product AND value=version - WHERE program=" . SqlQuote($product) . " - GROUP BY value"); - -=cut - - SendSQL("SELECT value,program - FROM versions - WHERE program=" . SqlQuote($product) . " + SendSQL("SELECT value + FROM versions + WHERE product_id=$product_id ORDER BY value"); print "
Edit versions of ...Description
\n"; @@ -252,8 +237,7 @@ unless ($action) { print " \n"; print ""; while ( MoreSQLData() ) { - my ($version,$dummy,$bugs) = FetchSQLData(); - $bugs ||= 'none'; + my $version = FetchOneColumn(); print "\n"; print " \n"; #print " \n"; @@ -281,6 +265,7 @@ unless ($action) { if ($action eq 'add') { PutHeader("Add version of $product"); CheckProduct($product); + my $product_id = get_product_id($product); #print "This page lets you add a new version to a bugzilla-tracked product.\n"; @@ -309,6 +294,7 @@ if ($action eq 'add') { if ($action eq 'new') { PutHeader("Adding new version"); CheckProduct($product); + my $product_id = get_product_id($product); # Cleanups and valididy checks @@ -327,10 +313,9 @@ if ($action eq 'new') { # Add the new version SendSQL("INSERT INTO versions ( " . - "value, program" . + "value, product_id" . " ) VALUES ( " . - SqlQuote($version) . "," . - SqlQuote($product) . ")"); + SqlQuote($version) . ", $product_id)"); # Make versioncache flush unlink "data/versioncache"; @@ -352,12 +337,12 @@ if ($action eq 'new') { if ($action eq 'del') { PutHeader("Delete version of $product"); CheckVersion($product, $version); + my $product_id = get_product_id($product); - SendSQL("SELECT count(bug_id),product,version + SendSQL("SELECT count(bug_id) FROM bugs - GROUP BY product,version - HAVING product=" . SqlQuote($product) . " - AND version=" . SqlQuote($version)); + WHERE product_id = $product_id + AND version = " . SqlQuote($version)); my $bugs = FetchOneColumn(); print "
Action
$version$bugs
\n"; @@ -416,6 +401,7 @@ one."; if ($action eq 'delete') { PutHeader("Deleting version of $product"); CheckVersion($product,$version); + my $product_id = get_product_id($product); # lock the tables before we start to change everything: @@ -433,7 +419,7 @@ if ($action eq 'delete') { SendSQL("SELECT bug_id FROM bugs - WHERE product=" . SqlQuote($product) . " + WHERE product_id=$product_id AND version=" . SqlQuote($version)); while (MoreSQLData()) { my $bugid = FetchOneColumn(); @@ -450,13 +436,13 @@ if ($action eq 'delete') { # Deleting the rest is easier: SendSQL("DELETE FROM bugs - WHERE product=" . SqlQuote($product) . " + WHERE product_id = $product_id AND version=" . SqlQuote($version)); print "Bugs deleted.
\n"; } SendSQL("DELETE FROM versions - WHERE program=" . SqlQuote($product) . " + WHERE product_id = $product_id AND value=" . SqlQuote($version)); print "Version deleted.

\n"; SendSQL("UNLOCK TABLES"); @@ -477,6 +463,7 @@ if ($action eq 'delete') { if ($action eq 'edit') { PutHeader("Edit version of $product"); CheckVersion($product,$version); + my $product_id = get_product_id($product); print "\n"; print "

\n"; @@ -510,12 +497,14 @@ if ($action eq 'update') { my $versionold = trim($::FORM{versionold} || ''); CheckVersion($product,$versionold); + my $product_id = get_product_id($product); # Note that the order of this tests is important. If you change # them, be sure to test for WHERE='$version' or WHERE='$versionold' SendSQL("LOCK TABLES bugs WRITE, - versions WRITE"); + versions WRITE, + products READ"); if ($version ne $versionold) { unless ($version) { @@ -534,10 +523,10 @@ if ($action eq 'update') { SET version=" . SqlQuote($version) . ", delta_ts = delta_ts WHERE version=" . SqlQuote($versionold) . " - AND product=" . SqlQuote($product)); + AND product_id = $product_id"); SendSQL("UPDATE versions SET value=" . SqlQuote($version) . " - WHERE program=" . SqlQuote($product) . " + WHERE product_id = $product_id AND value=" . SqlQuote($versionold)); unlink "data/versioncache"; print "Updated version.
\n"; diff --git a/enter_bug.cgi b/enter_bug.cgi index 726179722..83f1126f9 100755 --- a/enter_bug.cgi +++ b/enter_bug.cgi @@ -239,7 +239,9 @@ if (lsearch(\@::enterable_products, $product) == -1) { DisplayError("'" . html_quote($product) . "' is not a valid product."); exit; } - + +my $product_id = get_product_id($product); + if (0 == @{$::components{$product}}) { my $error = "Sorry; there needs to be at least one component for this " . "product in order to create a new bug. "; @@ -261,8 +263,8 @@ elsif (1 == @{$::components{$product}}) { } my @components; -SendSQL("SELECT value, description FROM components " . - "WHERE program = " . SqlQuote($product) . " ORDER BY value"); +SendSQL("SELECT name, description FROM components " . + "WHERE product_id = $product_id ORDER BY name"); while (MoreSQLData()) { my ($name, $description) = FetchSQLData(); @@ -315,7 +317,7 @@ if (exists $::COOKIE{"VERSION-$product"} && my @status = "NEW"; if (UserInGroup("editbugs") || UserInGroup("canconfirm")) { - SendSQL("SELECT votestoconfirm FROM products WHERE product = " . + SendSQL("SELECT votestoconfirm FROM products WHERE name = " . SqlQuote($product)); push(@status, $unconfirmedstate) if (FetchOneColumn()); } diff --git a/globals.pl b/globals.pl index 16cdba73b..3e119644b 100644 --- a/globals.pl +++ b/globals.pl @@ -434,7 +434,10 @@ sub GenerateArrayCode { sub GenerateVersionTable { - SendSQL("select value, program from versions order by value"); + SendSQL("SELECT versions.value, products.name " . + "FROM versions, products " . + "WHERE products.id = versions.product_id " . + "ORDER BY versions.value"); my @line; my %varray; my %carray; @@ -446,7 +449,10 @@ sub GenerateVersionTable { push @{$::versions{$p1}}, $v; $varray{$v} = 1; } - SendSQL("select value, program from components order by value"); + SendSQL("SELECT components.name, products.name " . + "FROM components, products " . + "WHERE products.id = components.product_id " . + "ORDER BY components.name"); while (@line = FetchSQLData()) { my ($c,$p) = (@line); if (!defined $::components{$p}) { @@ -464,7 +470,7 @@ sub GenerateVersionTable { # about them anyway. my $mpart = $dotargetmilestone ? ", milestoneurl" : ""; - SendSQL("select product, description, votesperuser, disallownew$mpart from products ORDER BY product"); + SendSQL("select name, description, votesperuser, disallownew$mpart from products ORDER BY name"); while (@line = FetchSQLData()) { my ($p, $d, $votesperuser, $dis, $u) = (@line); $::proddesc{$p} = $d; @@ -546,7 +552,10 @@ sub GenerateVersionTable { if ($dotargetmilestone) { # reading target milestones in from the database - matthew@zeroknowledge.com - SendSQL("SELECT value, product FROM milestones ORDER BY sortkey, value"); + SendSQL("SELECT milestones.value, products.name " . + "FROM milestones, products " . + "WHERE products.id = milestones.product_id " . + "ORDER BY milestones.sortkey, milestones.value"); my @line; my %tmarray; @::legal_target_milestone = (); @@ -943,6 +952,49 @@ sub DBNameToIdAndCheck { registered for a Bugzilla account."); } +sub get_product_id { + my ($prod) = @_; + PushGlobalSQLState(); + SendSQL("SELECT id FROM products WHERE name = " . SqlQuote($prod)); + my ($prod_id) = FetchSQLData(); + PopGlobalSQLState(); + return $prod_id; +} + +sub get_product_name { + my ($prod_id) = @_; + die "non-numeric prod_id '$prod_id' passed to get_product_name" + unless ($prod_id =~ /^\d+$/); + PushGlobalSQLState(); + SendSQL("SELECT name FROM products WHERE id = $prod_id"); + my ($prod) = FetchSQLData(); + PopGlobalSQLState(); + return $prod; +} + +sub get_component_id { + my ($prod_id, $comp) = @_; + die "non-numeric prod_id '$prod_id' passed to get_component_id" + unless ($prod_id =~ /^\d+$/); + PushGlobalSQLState(); + SendSQL("SELECT id FROM components " . + "WHERE product_id = $prod_id AND name = " . SqlQuote($comp)); + my ($comp_id) = FetchSQLData(); + PopGlobalSQLState(); + return $comp_id; +} + +sub get_component_name { + my ($comp_id) = @_; + die "non-numeric comp_id '$comp_id' passed to get_component_name" + unless ($comp_id =~ /^\d+$/); + PushGlobalSQLState(); + SendSQL("SELECT name FROM components WHERE id = $comp_id"); + my ($comp) = FetchSQLData(); + PopGlobalSQLState(); + return $comp; +} + # Use trick_taint() when you know that there is no way that the data # in a scalar can be tainted, but taint mode still bails on it. # WARNING!! Using this routine on data that really could be tainted @@ -1330,7 +1382,7 @@ sub RemoveVotes { "FROM profiles " . "LEFT JOIN votes ON profiles.userid = votes.who " . "LEFT JOIN bugs USING(bug_id) " . - "LEFT JOIN products USING(product)" . + "LEFT JOIN products ON products.id = bugs.product_id " . "WHERE votes.bug_id = $id " . $whopart); my @list; diff --git a/importxml.pl b/importxml.pl index 964d29a6f..cffeca68c 100755 --- a/importxml.pl +++ b/importxml.pl @@ -570,9 +570,10 @@ for (my $k=1 ; $k <= $bugqty ; $k++) { push (@values, SqlQuote($qa_contact)); push (@query, "qa_contact"); } else { - SendSQL("select initialqacontact from components where program=" . - SqlQuote($product[0]) . - " and value=" . SqlQuote($component[0]) ); + SendSQL("SELECT initialqacontact FROM components, products " + "WHERE components.product_id = products.id" . + " AND products.name = " . SqlQuote($product[0]) . + " AND components.name = " . SqlQuote($component[0]) ); $qa_contact = FetchOneColumn(); push (@values, SqlQuote(DBname_to_id($qa_contact)) ); push (@query, "qa_contact"); diff --git a/long_list.cgi b/long_list.cgi index 33975f5b1..933e90f15 100755 --- a/long_list.cgi +++ b/long_list.cgi @@ -41,7 +41,7 @@ GetVersionTable(); my $generic_query = " SELECT bugs.bug_id, - bugs.product, + products.name, bugs.version, bugs.rep_platform, bugs.op_sys, @@ -49,7 +49,7 @@ my $generic_query = " bugs.resolution, bugs.priority, bugs.bug_severity, - bugs.component, + components.name, assign.login_name, report.login_name, bugs.bug_file_loc, @@ -58,8 +58,9 @@ my $generic_query = " bugs.qa_contact, bugs.status_whiteboard, bugs.keywords - FROM bugs,profiles assign,profiles report - WHERE assign.userid = bugs.assigned_to AND report.userid = bugs.reporter"; + FROM bugs,profiles assign,profiles report, products, components + WHERE assign.userid = bugs.assigned_to AND report.userid = bugs.reporter + AND bugs.product_id=products.id AND bugs.component_id=components.id"; my $buglist = $::FORM{'buglist'} || $::FORM{'bug_id'} || diff --git a/post_bug.cgi b/post_bug.cgi index 2edb1aca4..2b4859ccd 100755 --- a/post_bug.cgi +++ b/post_bug.cgi @@ -70,6 +70,11 @@ $template->process("$template_name.txt.tmpl", $vars, \$comment) ValidateComment($comment); my $product = $::FORM{'product'}; +my $product_id = get_product_id($product); +if (!$product_id) { + ThrowUserError("Sorry, the product " . html_quote($product) . + " does not exist"); +} # Set cookies my $cookiepath = Param("cookiepath"); @@ -100,10 +105,11 @@ if(Param("usebuggroupsentry") && GroupExists($product)) { } } -if (!$::FORM{'component'}) { +my $component_id = get_component_id($product_id, $::FORM{component}); +if (!$component_id) { DisplayError("You must choose a component that corresponds to this bug. If necessary, just guess."); - exit; + exit; } if (!defined $::FORM{'short_desc'} || trim($::FORM{'short_desc'}) eq "") { @@ -121,20 +127,20 @@ my $sql_component = SqlQuote($::FORM{'component'}); # Default assignee is the component owner. if ($::FORM{'assigned_to'} eq "") { SendSQL("SELECT initialowner FROM components " . - "WHERE program=$sql_product AND value=$sql_component"); + "WHERE id = $component_id"); $::FORM{'assigned_to'} = FetchOneColumn(); } else { $::FORM{'assigned_to'} = DBNameToIdAndCheck(trim($::FORM{'assigned_to'})); } -my @bug_fields = ("product", "version", "rep_platform", +my @bug_fields = ("version", "rep_platform", "bug_severity", "priority", "op_sys", "assigned_to", - "bug_status", "bug_file_loc", "short_desc", "component", + "bug_status", "bug_file_loc", "short_desc", "target_milestone"); if (Param("useqacontact")) { SendSQL("SELECT initialqacontact FROM components " . - "WHERE program=$sql_product AND value=$sql_component"); + "WHERE id = $component_id"); my $qa_contact = FetchOneColumn(); if (defined $qa_contact && $qa_contact != 0) { $::FORM{'qa_contact'} = $qa_contact; @@ -155,14 +161,14 @@ if (exists $::FORM{'bug_status'}) { if (!exists $::FORM{'bug_status'}) { $::FORM{'bug_status'} = $::unconfirmedstate; - SendSQL("SELECT votestoconfirm FROM products WHERE product=$sql_product"); + SendSQL("SELECT votestoconfirm FROM products WHERE id = $product_id"); if (!FetchOneColumn()) { $::FORM{'bug_status'} = "NEW"; } } if (!exists $::FORM{'target_milestone'}) { - SendSQL("SELECT defaultmilestone FROM products WHERE product=$sql_product"); + SendSQL("SELECT defaultmilestone FROM products WHERE name=$sql_product"); $::FORM{'target_milestone'} = FetchOneColumn(); } @@ -200,6 +206,11 @@ if (exists $::FORM{'bug_status'} $::FORM{'everconfirmed'} = 1; } +$::FORM{'product_id'} = $product_id; +push(@used_fields, "product_id"); +$::FORM{component_id} = $component_id; +push(@used_fields, "component_id"); + my %ccids; my @cc; diff --git a/process_bug.cgi b/process_bug.cgi index d13f51b6c..0a1ff2728 100755 --- a/process_bug.cgi +++ b/process_bug.cgi @@ -174,7 +174,8 @@ sub CheckonComment( $ ) { # and make the user verify the version, component, target milestone, # and bug groups if so. if ( $::FORM{'id'} ) { - SendSQL("SELECT product FROM bugs WHERE bug_id = $::FORM{'id'}"); + SendSQL("SELECT name FROM products, bugs " . + "WHERE products.id = bugs.product_id AND bug_id = $::FORM{'id'}"); $::oldproduct = FetchSQLData(); } if ((($::FORM{'id'} && $::FORM{'product'} ne $::oldproduct) @@ -503,8 +504,8 @@ if($::usergroupset ne '0') { } foreach my $field ("rep_platform", "priority", "bug_severity", - "summary", "component", "bug_file_loc", "short_desc", - "product", "version", "op_sys", + "summary", "bug_file_loc", "short_desc", + "version", "op_sys", "target_milestone", "status_whiteboard") { if (defined $::FORM{$field}) { if ($::FORM{$field} ne $::dontchange) { @@ -514,6 +515,41 @@ foreach my $field ("rep_platform", "priority", "bug_severity", } } +my $prod_id; # Remember, can't use this for mass changes +if ($::FORM{'product'} ne $::dontchange) { + $prod_id = get_product_id($::FORM{'product'}); + if (! $prod_id) { + DisplayError("The " . html_quote($::FORM{'product'}) . + " product doesn't exist."); + exit; + } + DoComma(); + $::query .= "product_id = $prod_id"; +} else { + SendSQL("SELECT DISTINCT product_id FROM bugs WHERE bug_id IN (" . + join(',', @idlist) . ") LIMIT 2"); + $prod_id = FetchOneColumn(); + $prod_id = undef if (FetchOneColumn()); +} + +my $comp_id; # Remember, can't use this for mass changes +if ($::FORM{'component'} ne $::dontchange) { + if (!defined $prod_id) { + ThrowUserError("You cannot change the component from a list of bugs " . + "covering more than one product"); + } + $comp_id = get_component_id($prod_id, + $::FORM{'component'}); + if (! $comp_id) { + DisplayError("The " . html_quote($::FORM{'component'}) . + " component doesn't exist in the " . + html_quote($::FORM{'product'}) . " product"); + exit; + } + DoComma(); + $::query .= "component_id = $comp_id"; +} + # If this installation uses bug aliases, and the user is changing the alias, # add this change to the query. if (Param("usebugaliases") && defined($::FORM{'alias'})) { @@ -708,17 +744,15 @@ SWITCH: for ($::FORM{'knob'}) { DoConfirm(); } ChangeStatus('NEW'); - SendSQL("select initialowner from components where program=" . - SqlQuote($::FORM{'product'}) . " and value=" . - SqlQuote($::FORM{'component'})); + SendSQL("SELECT initialowner FROM components " . + "WHERE components.id = $comp_id"); my $newid = FetchOneColumn(); my $newname = DBID_to_name($newid); DoComma(); $::query .= "assigned_to = $newid"; if (Param("useqacontact")) { - SendSQL("select initialqacontact from components where program=" . - SqlQuote($::FORM{'product'}) . - " and value=" . SqlQuote($::FORM{'component'})); + SendSQL("SELECT initialqacontact FROM components " . + "WHERE components.id = $comp_id"); my $qacontact = FetchOneColumn(); if (defined $qacontact && $qacontact != 0) { DoComma(); @@ -923,8 +957,9 @@ foreach my $id (@idlist) { SendSQL("LOCK TABLES bugs $write, bugs_activity $write, cc $write, " . "cc AS selectVisible_cc $write, " . "profiles $write, dependencies $write, votes $write, " . + "products READ, components READ, " . "keywords $write, longdescs $write, fielddefs $write, " . - "keyworddefs READ, groups READ, attachments READ, products READ"); + "keyworddefs READ, groups READ, attachments READ"); my @oldvalues = SnapShotBug($id); my %oldhash; my $i = 0; @@ -1270,6 +1305,19 @@ foreach my $id (@idlist) { } if ($old ne $new) { + # Products and components are now stored in the DB using ID's + # We need to translate this to English before logging it + if ($col eq 'product_id') { + $old = get_product_name($old); + $new = get_product_name($new); + $col = 'product'; + } + if ($col eq 'component_id') { + $old = get_component_name($old); + $new = get_component_name($new); + $col = 'component'; + } + # save off the old value for passing to processmail so the old # owner can be notified # diff --git a/queryhelp.cgi b/queryhelp.cgi index 89db7b5cb..16acf73f1 100755 --- a/queryhelp.cgi +++ b/queryhelp.cgi @@ -660,7 +660,7 @@ print qq{ }; -SendSQL("SELECT product,description FROM products ORDER BY product"); +SendSQL("SELECT name, description FROM products ORDER BY name"); while (MoreSQLData()) { my ($product, $productdesc) = FetchSQLData(); @@ -725,7 +725,11 @@ components and their associated products: foreach $product (@products) { - SendSQL("SELECT value,description FROM components WHERE program=" . SqlQuote($product) . " ORDER BY value"); + SendSQL("SELECT components.name, components.description " . + "FROM components, products " . + "WHERE components.product_id = products.id" . + " AND products.name = " . SqlQuote($product) . + "ORDER BY name"); while (MoreSQLData()) { diff --git a/reports.cgi b/reports.cgi index e04a9fd6d..ac77c89fd 100755 --- a/reports.cgi +++ b/reports.cgi @@ -263,6 +263,7 @@ $when

FIN # Build up $query string + my $prod_table = ($FORM{'product'} ne "-All-") ? ", products" : ""; my $query; $query = <\n

"; if( $FORM{'product'} ne "-All-" ) { - SendSQL("SELECT defaultmilestone FROM products WHERE product = " . - SqlQuote($FORM{'product'})); + SendSQL("SELECT defaultmilestone FROM products WHERE id = $product_id"); $ms = FetchOneColumn(); print "Most Doomed for $ms ($FORM{'product'})"; } else { @@ -661,7 +663,7 @@ sub most_doomed_for_milestone { my $query; $query = "select distinct assigned_to from bugs where target_milestone=\"$ms\""; if ($FORM{'product'} ne "-All-" ) { - $query .= "and bugs.product=".SqlQuote($FORM{'product'}); + $query .= "and bugs.product_id=$product_id "; } $query .= <bug list)}); + Alert(qq{Bug(s) found with invalid product/component ID: $product_id/$component_id}); } } @@ -601,7 +599,7 @@ Status("Checking votes/everconfirmed"); @badbugs = (); SendSQL("SELECT bug_id FROM bugs, products " . - "WHERE bugs.product = products.product " . + "WHERE bugs.product_id = products.id " . "AND bug_status = " . SqlQuote($::unconfirmedstate) . ' ' . "AND votestoconfirm <= votes " . "ORDER BY bug_id"); diff --git a/votes.cgi b/votes.cgi index 7a387e0e1..513add4c3 100755 --- a/votes.cgi +++ b/votes.cgi @@ -153,7 +153,7 @@ sub show_user { # we can do it all in one query. my %maxvotesperbug; if($canedit) { - SendSQL("SELECT products.product, products.maxvotesperbug + SendSQL("SELECT products.name, products.maxvotesperbug FROM products"); while (MoreSQLData()) { my ($prod, $max) = FetchSQLData(); @@ -173,10 +173,11 @@ sub show_user { SendSQL("SELECT votes.bug_id, votes.count, bugs.short_desc, bugs.bug_status - FROM votes, bugs + FROM votes, bugs, products WHERE votes.who = $who AND votes.bug_id = bugs.bug_id - AND bugs.product = " . SqlQuote($product) . + AND bugs.product_id = products.id + AND products.name = " . SqlQuote($product) . "ORDER BY votes.bug_id"); while (MoreSQLData()) { @@ -270,9 +271,9 @@ sub record_votes { # If the user is voting for bugs, make sure they aren't overstuffing # the ballot box. if (scalar(@buglist)) { - SendSQL("SELECT bugs.bug_id, bugs.product, products.maxvotesperbug + SendSQL("SELECT bugs.bug_id, products.name, products.maxvotesperbug FROM bugs, products - WHERE products.product = bugs.product + WHERE products.id = bugs.product_id AND bugs.bug_id IN (" . join(", ", @buglist) . ")"); my %prodcount; -- cgit v1.2.3-24-g4f1b