Added a "keywords" field to a bug, which is a cached string-based copy

of all the keywords assigned to the bug.  Right now, we only have code
that generates and maintains this field; soon will come code that
actually uses it.

3 files changed, 176 insertions, 26 deletions

diff --git a/checksetup.pl b/checksetup.pl
index cf7a83978..4a192d70e 100755
--- a/checksetup.pl
+++ b/checksetup.pl
@@ -334,7 +334,7 @@ LocalVar('@platforms', '
 if ($newstuff ne "") {
     print "This version of Bugzilla contains some variables that you may \n",
           "to change and adapt to your local settings. Please edit the file\n",
-          "'localconfig' and return checksetup.pl\n\n",
+          "'localconfig' and rerun checksetup.pl\n\n",
           "The following variables are new to localconfig since you last ran\n",
           "checksetup.pl:  $newstuff\n";
     exit;
@@ -530,6 +530,9 @@ $table{bugs} =
     qa_contact mediumint not null,
     status_whiteboard mediumtext not null,
     votes mediumint not null,
+    keywords mediumtext not null, ' # Note: keywords field is only a cache;
+                                # the real data comes from the keywords table.
+    . '
 
     index (assigned_to),
     index (creation_ts),
@@ -918,7 +921,7 @@ sub DropField ($$)
 
 
 
-# 5/12/99 Added a pref to control how much email you get.  This needs a new
+# 1999-05-12 Added a pref to control how much email you get.  This needs a new
 # column in the profiles table, so feed the following to mysql:
 
 AddField('profiles', 'emailnotification', 'enum("ExcludeSelfChanges", "CConly", 
@@ -926,7 +929,7 @@ AddField('profiles', 'emailnotification', 'enum("ExcludeSelfChanges", "CConly",
 
 
 
-# 6/22/99 Added an entry to the attachments table to record who the
+# 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.
 
 AddField('attachments', 'submitter_id', 'mediumint not null');
@@ -943,7 +946,7 @@ AddField('attachments', 'submitter_id', 'mediumint not null');
 
 
 
-# 9/15/99 Apparently, newer alphas of MySQL won't allow you to have "when"
+# 1999-9-15 Apparently, newer alphas of MySQL won't allow you to have "when"
 # as a column name.  So, I have had to rename a column in the bugs_activity
 # table.
 
@@ -951,11 +954,13 @@ RenameField ('bugs_activity', 'when', 'bug_when');
 
 
 
-# 10/11/99 Restructured voting database to add a cached value in each bug
+# 1999-10-11 Restructured voting database to add a cached value in each bug
 # recording how many total votes that bug has.  While I'm at it, I removed
 # the unused "area" field from the bugs database.  It is distressing to
 # realize that the bugs table has reached the maximum number of indices
 # allowed by MySQL (16), which may make future enhancements awkward.
+# (P.S. All is not lost; it appears that the latest betas of MySQL support
+# a new table format which will allow 32 indices.)
 
 DropField('bugs', 'area');
 AddField('bugs',     'votes',        'mediumint not null, add index (votes)');
@@ -978,6 +983,46 @@ ChangeFieldType ('components', 'program', 'varchar(64)');
 ChangeFieldType ('products',   'product', 'varchar(64)');
 ChangeFieldType ('versions',   'program', 'varchar(64)');
 
+# 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
+# bug.  This is so that I can easily sort and display a keywords
+# column in bug lists.
+
+if (!GetFieldDef('bugs', 'keywords')) {
+    AddField('bugs', 'keywords', 'mediumtext not null');
+
+    my @kwords;
+    print "Making sure 'keywords' field of table 'bugs' is empty ...\n";
+    $dbh->do("UPDATE bugs SET delta_ts = delta_ts, keywords = '' " .
+             "WHERE keywords != ''");
+    print "Repopulating 'keywords' field of table 'bugs' ...\n";
+    my $sth = $dbh->prepare("SELECT keywords.bug_id, keyworddefs.name " .
+                            "FROM keywords, keyworddefs " .
+                            "WHERE keyworddefs.id = keywords.keywordid " .
+                            "ORDER BY keywords.bug_id, keyworddefs.name");
+    $sth->execute;
+    my @list;
+    my $bugid = 0;
+    my @row;
+    while (1) {
+        my ($b, $k) = ($sth->fetchrow_array());
+        if (!defined $b || $b ne $bugid) {
+            if (@list) {
+                $dbh->do("UPDATE bugs SET delta_ts = delta_ts, keywords = " .
+                         $dbh->quote(join(', ', @list)) .
+                         " WHERE bug_id = $bugid");
+            }
+            if (!$b) {
+                last;
+            }
+            $bugid = $b;
+            @list = ();
+        }
+        push(@list, $k);
+    }
+}
+        
+
 
 
 #
diff --git a/process_bug.cgi b/process_bug.cgi
index a11b0a9a1..bc7e2fcbc 100755
--- a/process_bug.cgi
+++ b/process_bug.cgi
@@ -455,20 +455,6 @@ sub SnapShotDeps {
 }
 
 
-sub SnapShotKeywords {
-    my ($id) = (@_);
-    SendSQL("SELECT keyworddefs.name 
-             FROM keyworddefs, keywords
-             WHERE keywords.bug_id = $id AND keyworddefs.id = keywords.keywordid
-             ORDER BY keyworddefs.name");
-    my @list;
-    while (MoreSQLData()) {
-        push(@list, FetchOneColumn());
-    }
-    return join(',', @list);
-}
-    
-
 my $whoid = DBNameToIdAndCheck($::FORM{'who'});
 my $timestamp;
 
@@ -490,7 +476,6 @@ foreach my $id (@idlist) {
     my %dependencychanged;
     SendSQL("lock tables bugs write, bugs_activity write, cc write, profiles write, dependencies write, votes write, keywords write, keyworddefs read");
     my @oldvalues = SnapShotBug($id);
-    my $oldkeywords = SnapShotKeywords($id);
 
     if (defined $::FORM{'delta_ts'} && $::FORM{'delta_ts'} ne $delta_ts) {
         print "
@@ -587,18 +572,36 @@ The changes made were:
         # For add, we delete things we're adding (to make sure we don't
         # end up having them twice), and then we add them.
         # For delete, we just delete things on the list.
+        my $changed = 0;
         if ($keywordaction eq "makeexact") {
             SendSQL("DELETE FROM keywords WHERE bug_id = $id");
+            $changed = 1;
         }
         foreach my $keyword (@keywordlist) {
             if ($keywordaction ne "makeexact") {
                 SendSQL("DELETE FROM keywords
                          WHERE bug_id = $id AND keywordid = $keyword");
+                $changed = 1;
             }
             if ($keywordaction ne "delete") {
                 SendSQL("INSERT INTO keywords 
                          (bug_id, keywordid) VALUES ($id, $keyword)");
+                $changed = 1;
+            }
+        }
+        if ($changed) {
+            SendSQL("SELECT keyworddefs.name 
+                     FROM keyworddefs, keywords
+                     WHERE keywords.bug_id = $id
+                         AND keyworddefs.id = keywords.keywordid
+                     ORDER BY keyworddefs.name");
+            my @list;
+            while (MoreSQLData()) {
+                push(@list, FetchOneColumn());
             }
+            SendSQL("UPDATE bugs SET keywords = " .
+                    SqlQuote(join(', ', @list)) .
+                    " WHERE bug_id = $id");
         }
     }
 
@@ -685,9 +688,7 @@ The changes made were:
     #
     my @newvalues = SnapShotBug($id);
 
-    push(@oldvalues, $oldkeywords);
-    push(@newvalues, SnapShotKeywords($id));
-    foreach my $c (@::log_columns, "keywords") {
+    foreach my $c (@::log_columns) {
         my $col = $c;           # We modify it, don't want to modify array
                                 # values in place.
         my $old = shift @oldvalues;
diff --git a/sanitycheck.cgi b/sanitycheck.cgi
index bfacbf9d2..fe1708986 100755
--- a/sanitycheck.cgi
+++ b/sanitycheck.cgi
@@ -134,7 +134,7 @@ foreach my $ref (@checklist) {
 }
 
 
-Status("Checking profile ids...");
+Status("Checking profile ids");
 
 SendSQL("select userid,login_name from profiles");
 
@@ -154,13 +154,15 @@ undef $profid{0};
 
 
 Status("Checking reporter/assigned_to/qa_contact ids");
-SendSQL("select bug_id,reporter,assigned_to,qa_contact,votes from bugs");
+SendSQL("SELECT bug_id,reporter,assigned_to,qa_contact,votes,keywords " .
+        "FROM bugs");
 
 my %votes;
 my %bugid;
+my %keyword;
 
 while (@row = FetchSQLData()) {
-    my($id, $reporter, $assigned_to, $qa_contact, $v) = (@row);
+    my($id, $reporter, $assigned_to, $qa_contact, $v, $k) = (@row);
     $bugid{$id} = 1;
     if (!defined $profid{$reporter}) {
         Alert("Bad reporter $reporter in " . BugLink($id));
@@ -174,6 +176,9 @@ while (@row = FetchSQLData()) {
     if ($v != 0) {
         $votes{$id} = $v;
     }
+    if ($k) {
+        $keyword{$id} = $k;
+    }
 }
 
 Status("Checking cached vote counts");
@@ -199,6 +204,105 @@ if ($offervotecacherebuild) {
 }
 
 
+Status("Checking keywords table");
+
+my %keywordids;
+SendSQL("SELECT id, name FROM keyworddefs");
+while (@row = FetchSQLData()) {
+    my ($id, $name) = (@row);
+    if ($keywordids{$id}) {
+        Alert("Duplicate entry in keyworddefs for id $id");
+    }
+    $keywordids{$id} = 1;
+    if ($name =~ /,/ || $name =~ /^\s/ || $name =~ /\s$/) {
+        Alert("Bogus name in keyworddefs for id $id");
+    }
+}
+
+
+SendSQL("SELECT bug_id, keywordid FROM keywords ORDER BY bug_id, keywordid");
+my $lastid;
+my $lastk;
+while (@row = FetchSQLData()) {
+    my ($id, $k) = (@row);
+    if (!defined $bugid{$id}) {
+        Alert("Bad bugid " . BugLink($id));
+    }
+    if (!$keywordids{$k}) {
+        Alert("Bogus keywordids $k found in keywords table");
+    }
+    if (defined $lastid && $id eq $lastid && $k eq $lastk) {
+        Alert("Duplicate keyword ids found in bug " . BugLink($id));
+    }
+    $lastid = $id;
+    $lastk = $k;
+}
+
+Status("Checking cached keywords");
+
+my %realk;
+
+if (exists $::FORM{'rebuildkeywordcache'}) {
+    SendSQL("LOCK TABLES bugs write, keywords read, keyworddefs read");
+}
+
+SendSQL("SELECT keywords.bug_id, keyworddefs.name " .
+        "FROM keywords, keyworddefs " .
+        "WHERE keyworddefs.id = keywords.keywordid " .
+        "ORDER BY keywords.bug_id, keyworddefs.name");
+
+my $lastb;
+my @list;
+while (1) {
+    my ($b, $k) = (FetchSQLData());
+    if (!defined $b || $b ne $lastb) {
+        if (@list) {
+            $realk{$lastb} = join(', ', @list);
+        }
+        if (!$b) {
+            last;
+        }
+        $lastb = $b;
+        @list = ();
+    }
+    push(@list, $k);
+}
+
+my @fixlist;
+foreach my $b (keys(%keyword)) {
+    if (!exists $realk{$b} || $realk{$b} ne $keyword{$b}) {
+        push(@fixlist, $b);
+    }
+}
+foreach my $b (keys(%realk)) {
+    if (!exists $keyword{$b}) {
+        push(@fixlist, $b);
+    }
+}
+if (@fixlist) {
+    @fixlist = sort {$a <=> $b} @fixlist;
+    Alert("Bug(s) found with incorrect keyword cache: " .
+          join(', ', @fixlist));
+    if (exists $::FORM{'rebuildkeywordcache'}) {
+        Status("OK, now fixing keyword cache.");
+        foreach my $b (@fixlist) {
+            my $k = '';
+            if (exists($realk{$b})) {
+                $k = $realk{$b};
+            }
+            SendSQL("UPDATE bugs SET delta_ts = delta_ts, keywords = " .
+                    SqlQuote($k) .
+                    " WHERE bug_id = $b");
+        }
+        SendSQL("UNLOCK TABLES");
+        Status("Keyword cache fixed.");
+    } else {
+        print qq{<a href="sanitycheck.cgi?rebuildkeywordcache=1">Click here to rebuild the keyword cache</a><p>\n};
+    }
+}
+
+
+
 Status("Checking CC table");
 
 SendSQL("select bug_id,who from cc");