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 --- checksetup.pl | 155 ++++++++++++++++++++++++++++++++++++++++++++++++---------- 1 file changed, 128 insertions(+), 27 deletions(-) (limited to 'checksetup.pl') 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. -- cgit v1.2.3-24-g4f1b