Moved all the long descriptions out of the bugs table, and into a new

table.  There is now a separate entry for each new long description,
rather than appending them all together in one big field.

5 files changed, 200 insertions, 21 deletions

diff --git a/buglist.cgi b/buglist.cgi
index 27e67e390..2309b3205 100755
--- a/buglist.cgi
+++ b/buglist.cgi
@@ -480,15 +480,23 @@ foreach my $f ("short_desc", "long_desc", "bug_file_loc",
     if (defined $::FORM{$f}) {
         my $s = trim($::FORM{$f});
         if ($s ne "") {
+            my $n = $f;
             $s = SqlQuote($s);
-            if ($::FORM{$f . "_type"} eq "regexp") {
-                $query .= "and $f regexp $s\n";
-            } elsif ($::FORM{$f . "_type"} eq "notregexp") {
-                $query .= "and $f not regexp $s\n";
-            } elsif ($::FORM{$f . "_type"} eq "casesubstring") {
-                $query .= "and instr($f, $s)\n";
+            my $type = $::FORM{$f . "_type"};
+            if ($f eq "long_desc") {
+                # Patch in the longdescs table.
+                $query =~ s/where/, longdescs where/;
+                $query .= "and longdescs.bug_id = bugs.bug_id\n";
+                $n = "longdescs.thetext";
+            }
+            if ($type eq "regexp") {
+                $query .= "and $n regexp $s\n";
+            } elsif ($type eq "notregexp") {
+                $query .= "and $n not regexp $s\n";
+            } elsif ($type eq "casesubstring") {
+                $query .= "and instr($n, $s)\n";
             } else {
-                $query .= "and instr(lower($f), lower($s))\n";
+                $query .= "and instr(lower($n), lower($s))\n";
             }
         }
     }
diff --git a/checksetup.pl b/checksetup.pl
index 69e31919a..14445c2b6 100755
--- a/checksetup.pl
+++ b/checksetup.pl
@@ -107,6 +107,14 @@ use vars qw(
 );
 
 
+# Trim whitespace from front and back.
+
+sub trim {
+    ($_) = (@_);
+    s/^\s+//g;
+    s/\s+$//g;
+    return $_;
+}
 
 
 
@@ -519,7 +527,6 @@ $table{bugs} =
     creation_ts datetime not null,
     delta_ts timestamp,
     short_desc mediumtext,
-    long_desc mediumtext,
     op_sys enum($opsys) not null,
     priority enum($priorities) not null,
     product varchar(64) not null,
@@ -561,6 +568,16 @@ $table{cc} =
     index(who)';
 
 
+$table{longdescs} = 
+   'bug_id mediumint not null,
+    who mediumint not null,
+    bug_when datetime not null,
+    thetext mediumtext,
+
+    index(bug_id),
+    index(bug_when)';
+
+
 $table{components} =
    'value tinytext,
     program varchar(64),
@@ -1035,6 +1052,142 @@ AddField('profiles', 'disabledtext',  'mediumtext not null');
 
 
 
+# 2000-01-20 Added a new "longdescs" table, which is supposed to have all the
+# long descriptions in it, replacing the old long_desc field in the bugs 
+# table.  The below hideous code populates this new table with things from
+# the old field, with ugly parsing and heuristics.
+
+sub WriteOneDesc {
+    my ($id, $who, $when, $buffer) = (@_);
+    $buffer = trim($buffer);
+    if ($buffer eq '') {
+        return;
+    }
+    $dbh->do("INSERT INTO longdescs (bug_id, who, bug_when, thetext) VALUES " .
+             "($id, $who, " .  time2str("'%Y/%m/%d %H:%M:%S'", $when) .
+             ", " . $dbh->quote($buffer) . ")");
+}
+
+
+if (GetFieldDef('bugs', 'long_desc')) {
+    eval("use Date::Parse");
+    eval("use Date::Format");
+    my $sth = $dbh->prepare("SELECT count(*) FROM bugs");
+    $sth->execute();
+    my ($total) = ($sth->fetchrow_array);
+
+    print "Populating new long_desc table.  This is slow.  There are $total\n";
+    print "bugs to process; a line of dots will be printed for each 50.\n\n";
+    $| = 1;
+
+    $dbh->do("LOCK TABLES bugs write, longdescs write, profiles write");
+
+    $dbh->do('DELETE FROM longdescs');
+
+    $sth = $dbh->prepare("SELECT bug_id, creation_ts, reporter, long_desc " .
+                         "FROM bugs ORDER BY bug_id");
+    $sth->execute();
+    my $count = 0;
+    while (1) {
+        my ($id, $createtime, $reporterid, $desc) = ($sth->fetchrow_array());
+        if (!$id) {
+            last;
+        }
+        print ".";
+        $count++;
+        if ($count % 10 == 0) {
+            print " ";
+            if ($count % 50 == 0) {
+                print "$count/$total (" . int($count * 100 / $total) . "%)\n";
+            }
+        }
+        $desc =~ s/\r//g;
+        my $who = $reporterid;
+        my $when = str2time($createtime);
+        my $buffer = "";
+        foreach my $line (split(/\n/, $desc)) {
+            $line =~ s/\s+$//g;       # Trim trailing whitespace.
+            if ($line =~ /^------- Additional Comments From ([^\s]+)\s+(\d.+\d)\s+-------$/) {
+                my $name = $1;
+                my $date = str2time($2);
+                $date += 59;    # Oy, what a hack.  The creation time is
+                                # accurate to the second.  But we the long
+                                # text only contains things accurate to the
+                                # minute.  And so, if someone makes a comment
+                                # within a minute of the original bug creation,
+                                # then the comment can come *before* the
+                                # bug creation.  So, we add 59 seconds to
+                                # the time of all comments, so that they
+                                # are always considered to have happened at
+                                # the *end* of the given minute, not the
+                                # beginning.
+                if ($date >= $when) {
+                    WriteOneDesc($id, $who, $when, $buffer);
+                    $buffer = "";
+                    $when = $date;
+                    my $s2 = $dbh->prepare("SELECT userid FROM profiles " .
+                                           "WHERE login_name = " .
+                                           $dbh->quote($name));
+                    $s2->execute();
+                    ($who) = ($s2->fetchrow_array());
+                    if (!$who) {
+                        # This username doesn't exist.  Try a special
+                        # netscape-only hack (sorry about that, but I don't
+                        # think it will hurt any other installations).  We
+                        # have many entries in the bugsystem from an ancient
+                        # world where the "@netscape.com" part of the loginname
+                        # was omitted.  So, look up the user again with that
+                        # appended, and use it if it's there.
+                        if ($name !~ /\@/) {
+                            my $nsname = $name . "\@netscape.com";
+                            $s2 =
+                                $dbh->prepare("SELECT userid FROM profiles " .
+                                              "WHERE login_name = " .
+                                              $dbh->quote($nsname));
+                            $s2->execute();
+                            ($who) = ($s2->fetchrow_array());
+                        }
+                    }
+                            
+                    if (!$who) {
+                        # This username doesn't exist.  Maybe someone renamed
+                        # him or something.  Invent a new profile entry,
+                        # disabled, just to represent him.
+                        $dbh->do("INSERT INTO profiles " .
+                                 "(login_name, password, cryptpassword," .
+                                 " disabledtext) VALUES (" .
+                                 $dbh->quote($name) .
+                                 ", 'okthen', encrypt('okthen'), " .
+                                 "'Account created only to maintain database integrity')");
+                        $s2 = $dbh->prepare("SELECT LAST_INSERT_ID()");
+                        $s2->execute();
+                        ($who) = ($s2->fetchrow_array());
+                    }
+                    next;
+                } else {
+#                    print "\nDecided this line of bug $id has a date of " .
+#                        time2str("'%Y/%m/%d %H:%M:%S'", $date) .
+#                            "\nwhich is less than previous line:\n$line\n\n";
+                }
+
+            }
+            $buffer .= $line . "\n";
+        }
+        WriteOneDesc($id, $who, $when, $buffer);
+    }
+                
+
+    print "\n\n";
+
+    DropField('bugs', 'long_desc');
+
+    $dbh->do("UNLOCK TABLES");
+    print "Now regenerating the shadow database for all bugs.\n";
+    system("./processmail regenerate");
+
+}
+
+
 #
 # 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/globals.pl b/globals.pl
index 856048c4d..90d2513b7 100644
--- a/globals.pl
+++ b/globals.pl
@@ -51,6 +51,7 @@ sub globals_pl_sillyness {
 use Mysql;
 
 use Date::Format;               # For time2str().
+use Date::Parse;               # For str2time().
 # use Carp;                       # for confess
 
 # Contains the version string for the current running Bugzilla.
@@ -109,14 +110,11 @@ sub AppendComment {
     if ($comment =~ /^\s*$/) {  # Nothin' but whitespace.
         return;
     }
-    SendSQL("select long_desc from bugs where bug_id = $bugid");
-    
-    my $desc = FetchOneColumn();
-    my $now = time2str("%Y-%m-%d %H:%M", time());
-    $desc .= "\n\n------- Additional Comments From $who  $now -------\n";
-    $desc .= $comment;
-    SendSQL("update bugs set long_desc=" . SqlQuote($desc) .
-            " where bug_id=$bugid");
+
+    my $whoid = DBNameToIdAndCheck($who);
+
+    SendSQL("INSERT INTO longdescs (bug_id, who, bug_when, thetext) " .
+            "VALUES($bugid, $whoid, now(), " . SqlQuote($comment) . ")");
 }
 
 sub lsearch {
@@ -464,8 +462,25 @@ sub DBNameToIdAndCheck {
 
 sub GetLongDescription {
     my ($id) = (@_);
-    SendSQL("select long_desc from bugs where bug_id = $id");
-    return FetchOneColumn();
+    my $result = "";
+    SendSQL("SELECT profiles.login_name, longdescs.bug_when, " .
+            "       longdescs.thetext " .
+            "FROM longdescs, profiles " .
+            "WHERE profiles.userid = longdescs.who " .
+            "      AND longdescs.bug_id = $id " .
+            "ORDER BY longdescs.bug_when");
+    my $count = 0;
+    while (MoreSQLData()) {
+        my ($who, $when, $text) = (FetchSQLData());
+        if ($count) {
+            $result .= "\n\n------- Additional Comments From $who  " .
+                time2str("%Y-%m-%d %H:%M", str2time($when)) . " -------\n";
+        }
+        $result .= $text;
+        $count++;
+    }
+
+    return $result;
 }
 
 
diff --git a/post_bug.cgi b/post_bug.cgi
index 93e7b3226..c3be5c67b 100755
--- a/post_bug.cgi
+++ b/post_bug.cgi
@@ -128,7 +128,7 @@ foreach my $f (@bug_fields) {
 }
 
 my $query = "insert into bugs (\n" . join(",\n", @used_fields) . ",
-creation_ts, long_desc )
+creation_ts )
 values (
 ";
 
@@ -142,7 +142,7 @@ $comment =~ s/\r\n/\n/g;     # Get rid of windows-style line endings.
 $comment =~ s/\r/\n/g;       # Get rid of mac-style line endings.
 $comment = trim($comment);
 
-$query .= "now(), " . SqlQuote($comment) . " )\n";
+$query .= "now())\n";
 
 
 my %ccids;
@@ -164,6 +164,9 @@ SendSQL($query);
 SendSQL("select LAST_INSERT_ID()");
 my $id = FetchOneColumn();
 
+SendSQL("INSERT INTO longdescs (bug_id, who, bug_when, thetext) VALUES " .
+        "($id, $::FORM{'reporter'}, now(), " . SqlQuote($comment) . ")");
+
 foreach my $person (keys %ccids) {
     SendSQL("insert into cc (bug_id, who) values ($id, $person)");
 }
diff --git a/process_bug.cgi b/process_bug.cgi
index 5016d5b8a..027579cee 100755
--- a/process_bug.cgi
+++ b/process_bug.cgi
@@ -476,7 +476,7 @@ sub LogDependencyActivity {
 #
 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");
+    SendSQL("lock tables bugs write, bugs_activity write, cc write, profiles write, dependencies write, votes write, keywords write, longdescs write, keyworddefs read");
     my @oldvalues = SnapShotBug($id);
 
     if (defined $::FORM{'delta_ts'} && $::FORM{'delta_ts'} ne $delta_ts) {