Added a "votes" field to the bugs table, which caches the total number

of votes that have been cast for that bug.  This let me simplify the
SQL in buglist.cgi, which makes things more efficient and fixes
several strange bugs.

5 files changed, 100 insertions, 14 deletions

diff --git a/CHANGES b/CHANGES
index b71d84a87..b7c7abe76 100644
--- a/CHANGES
+++ b/CHANGES
@@ -10,6 +10,23 @@ query the CVS tree.  For example,
 will tell you what has been changed in the last week.
 
 
+10/11/99 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.
+
+You must feed the following to MySQL:
+
+	alter table bugs drop column area;
+	alter table bugs add column votes mediumint not null, add index (votes);
+
+If you have been using the voting feature at all, then you will need
+to update the cache.  You can do this by visiting the sanitycheck.cgi
+page, and taking it up on its offer to rebuild the votes stuff.
+
+
 10/7/99 Added voting ability.  You must run the new script
 "makevotestable.sh".  You must also feed the following to mysql:
 
diff --git a/buglist.cgi b/buglist.cgi
index f712f0b3e..6b8ae1446 100755
--- a/buglist.cgi
+++ b/buglist.cgi
@@ -173,7 +173,7 @@ DefCol("version", "substring(bugs.version, 1, 5)", "Vers", "bugs.version");
 DefCol("os", "substring(bugs.op_sys, 1, 4)", "OS", "bugs.op_sys");
 DefCol("target_milestone", "bugs.target_milestone", "TargetM",
        "bugs.target_milestone");
-DefCol("votes", "sum(votes.count) as votesum", "Votes", "votesum");
+DefCol("votes", "bugs.votes", "Votes", "bugs.votes");
 
 my @collist;
 if (defined $::COOKIE{'COLUMNLIST'}) {
@@ -229,7 +229,7 @@ bugs.bug_status";
 
 
 $query .= "
-from   bugs left join votes on bugs.bug_id = votes.bug_id,
+from   bugs,
        profiles assign,
        profiles report
        left join profiles qacont on bugs.qa_contact = qacont.userid,
@@ -248,7 +248,7 @@ if ((defined $::FORM{'emailcc1'} && $::FORM{'emailcc1'}) ||
     # We need to poke into the CC table.  Do weird SQL left join stuff so that
     # we can look in the CC table, but won't reject any bugs that don't have
     # any CC fields.
-    $query =~ s/bugs left join/bugs left join cc on bugs.bug_id = cc.bug_id left join profiles ccname on cc.who = ccname.userid left join/;
+    $query =~ s/bugs,/bugs left join cc on bugs.bug_id = cc.bug_id left join profiles ccname on cc.who = ccname.userid,/;
 }
 
 if (defined $::FORM{'sql'}) {
@@ -366,6 +366,10 @@ if (defined $::FORM{'changedin'}) {
     }
 }
 
+if (defined $minvotes) {
+    $query .= "and votes >= $minvotes ";
+}
+
 
 my $ref = $::MFORM{'chfield'};
 
@@ -407,7 +411,7 @@ if (defined $ref) {
 if (defined $ref && 0 < @$ref) {
     # Do surgery on the query to tell it to patch in the bugs_activity
     # table.
-    $query =~ s/profiles assign,/profiles assign, bugs_activity,/;
+    $query =~ s/bugs,/bugs, bugs_activity,/;
     
     my @list;
     foreach my $f (@$ref) {
@@ -552,11 +556,6 @@ while (@row = FetchSQLData()) {
                                 # the same group.  If they don't, we leave
                                 # it alone.
     }
-    if (defined $minvotes) {
-        if ($row[$votecolnum] < $minvotes) {
-            next;
-        }
-    }
     if (!defined $seen{$bug_id}) {
         $seen{$bug_id} = 1;
         $count++;
diff --git a/doeditvotes.cgi b/doeditvotes.cgi
index 03c4c1d88..c499af494 100755
--- a/doeditvotes.cgi
+++ b/doeditvotes.cgi
@@ -86,12 +86,28 @@ foreach my $prod (keys(%prodcount)) {
     }
 }
 
+my %affected;
+SendSQL("lock tables bugs write, votes write");
+SendSQL("select bug_id from votes where who = $who");
+while (MoreSQLData()) {
+    my $id = FetchOneColumn();
+    $affected{$id} = 1;
+}
 SendSQL("delete from votes where who = $who");
 foreach my $id (@buglist) {
     if ($::FORM{$id} > 0) {
         SendSQL("insert into votes (who, bug_id, count) values ($who, $id, $::FORM{$id})");
     }
+    $affected{$id} = 1;
+}
+foreach my $id (keys %affected) {
+    SendSQL("select sum(count) from votes where bug_id = $id");
+    my $v = FetchOneColumn();
+    SendSQL("update bugs set votes = $v where bug_id = $id");
 }
+SendSQL("unlock tables");
+
+
 
 PutHeader("Voting tabulated", "Voting tabulated", $::COOKIE{'Bugzilla_login'});
 print "Your votes have been recorded.\n";
diff --git a/globals.pl b/globals.pl
index ccb0e8167..ac18305e1 100644
--- a/globals.pl
+++ b/globals.pl
@@ -535,6 +535,7 @@ sub RemoveVotes {
             close SENDMAIL;
         }
         SendSQL("delete from votes where bug_id = $id");
+        SendSQL("update bugs set votes = 0 where bug_id = $id");
     }
 }
 
diff --git a/sanitycheck.cgi b/sanitycheck.cgi
index f13fb4a13..b1b2022eb 100755
--- a/sanitycheck.cgi
+++ b/sanitycheck.cgi
@@ -24,11 +24,14 @@ use strict;
 
 require "CGI.pl";
 
+use vars %::FORM;
+
 print "Content-type: text/html\n";
 print "\n";
 
 ConnectToDatabase();
 
+my $offervotecacherebuild = 0;
 
 sub Status {
     my ($str) = (@_);
@@ -45,14 +48,37 @@ sub BugLink {
     return "<a href='show_bug.cgi?id=$id'>$id</a>";
 }
 
+sub AlertBadVoteCache {
+    my ($id) = (@_);
+    Alert("Bad vote cache for bug " . BugLink($id));
+    $offervotecacherebuild = 1;
+}
 
-PutHeader("Bugzilla Sanity Check");
-
-print "OK, now running sanity checks.<P>\n";
 
 my @row;
 my @checklist;
 
+PutHeader("Bugzilla Sanity Check");
+
+if (exists $::FORM{'rebuildvotecache'}) {
+    Status("OK, now rebuilding vote cache.");
+    SendSQL("lock tables bugs write, votes read");
+    SendSQL("update bugs set votes = 0");
+    SendSQL("select bug_id, sum(count) from votes group by bug_id");
+    my %votes;
+    while (@row = FetchSQLData()) {
+        my ($id, $v) = (@row);
+        $votes{$id} = $v;
+    }
+    foreach my $id (keys %votes) {
+        SendSQL("update bugs set votes = $votes{$id} where bug_id = $id");
+    }
+    SendSQL("unlock tables");
+    Status("Vote cache has been rebuild.");
+}
+
+print "OK, now running sanity checks.<P>\n";
+
 Status("Checking groups");
 SendSQL("select bit from groups where bit != pow(2, round(log(bit) / log(2)))");
 while (my $bit = FetchOneColumn()) {
@@ -124,12 +150,13 @@ undef $profid{0};
 
 
 Status("Checking reporter/assigned_to/qa_contact ids");
-SendSQL("select bug_id,reporter,assigned_to,qa_contact from bugs");
+SendSQL("select bug_id,reporter,assigned_to,qa_contact,votes from bugs");
 
+my %votes;
 my %bugid;
 
 while (@row = FetchSQLData()) {
-    my($id, $reporter, $assigned_to, $qa_contact) = (@row);
+    my($id, $reporter, $assigned_to, $qa_contact, $v) = (@row);
     $bugid{$id} = 1;
     if (!defined $profid{$reporter}) {
         Alert("Bad reporter $reporter in " . BugLink($id));
@@ -140,8 +167,34 @@ while (@row = FetchSQLData()) {
     if ($qa_contact != 0 && !defined $profid{$qa_contact}) {
         Alert("Bad qa_contact $qa_contact in" . BugLink($id));
     }
+    if ($v != 0) {
+        $votes{$id} = $v;
+    }
+}
+
+Status("Checking cached vote counts");
+SendSQL("select bug_id, sum(count) from votes group by bug_id");
+
+while (@row = FetchSQLData()) {
+    my ($id, $v) = (@row);
+    if ($v <= 0) {
+        Alert("Bad vote sum for bug $id");
+    } else {
+        if (!defined $votes{$id} || $votes{$id} != $v) {
+            AlertBadVoteCache($id);
+        }
+        delete $votes{$id};
+    }
+}
+foreach my $id (keys %votes) {
+    AlertBadVoteCache($id);
 }
 
+if ($offervotecacherebuild) {
+    print qq{<a href="sanitycheck.cgi?rebuildvotecache=1">Click here to rebuild the vote cache</a><p>\n};
+}
+
+
 Status("Checking CC table");
 
 SendSQL("select bug_id,who from cc");