Bug 290089: Move timestamp updates to happen before any other Schema updates

Patch By Max Kanat-Alexander <mkanat@bugzilla.org> r=Tomas.Kopal, a=justdave

3 files changed, 92 insertions, 79 deletions

diff --git a/Bugzilla/DB.pm b/Bugzilla/DB.pm
index b0106314e..6cd938aa6 100644
--- a/Bugzilla/DB.pm
+++ b/Bugzilla/DB.pm
@@ -589,13 +589,18 @@ sub bz_index_info {
 # XXX - Needs to be made cross-db compatible.
 sub bz_get_field_def ($$) {
     my ($self, $table, $field) = @_;
-    my $sth = $self->prepare("SHOW COLUMNS FROM $table");
-    $sth->execute;
 
-    while (my $ref = $sth->fetchrow_arrayref) {
-        next if $$ref[0] ne $field;
-        return $ref;
-   }
+    if ($self->bz_table_exists($table)) {
+
+        my $sth = $self->prepare("SHOW COLUMNS FROM $table");
+        $sth->execute;
+
+        while (my $ref = $sth->fetchrow_arrayref) {
+            next if $$ref[0] ne $field;
+            return $ref;
+        }
+    }
+    return undef;
 }
 
 # XXX - Needs to be made cross-db compatible
diff --git a/Bugzilla/DB/Mysql.pm b/Bugzilla/DB/Mysql.pm
index 1d10838e9..d80b1bbbb 100644
--- a/Bugzilla/DB/Mysql.pm
+++ b/Bugzilla/DB/Mysql.pm
@@ -371,6 +371,79 @@ sub bz_setup_database {
         } # foreach table
     } # if old-name indexes
 
+    # The old timestamp fields need to be adjusted here instead of in
+    # checksetup. Otherwise the UPDATE statements inside of bz_add_column
+    # will cause accidental timestamp updates.
+    # The code that does this was moved here from checksetup.
+
+    # 2002-08-14 - bbaetz@student.usyd.edu.au - bug 153578
+    # attachments creation time needs to be a datetime, not a timestamp
+    my $attach_creation = 
+        $self->bz_get_field_def("attachments", "creation_ts");
+    if ($attach_creation && $attach_creation->[1] =~ /^timestamp/) {
+        print "Fixing creation time on attachments...\n";
+
+        my $sth = $self->prepare("SELECT COUNT(attach_id) FROM attachments");
+        $sth->execute();
+        my ($attach_count) = $sth->fetchrow_array();
+
+        if ($attach_count > 1000) {
+            print "This may take a while...\n";
+        }
+        my $i = 0;
+
+        # This isn't just as simple as changing the field type, because
+        # the creation_ts was previously updated when an attachment was made
+        # obsolete from the attachment creation screen. So we have to go
+        # and recreate these times from the comments..
+        $sth = $self->prepare("SELECT bug_id, attach_id, submitter_id " .
+                               "FROM attachments");
+        $sth->execute();
+
+        # Restrict this as much as possible in order to avoid false 
+        # positives, and keep the db search time down
+        my $sth2 = $self->prepare("SELECT bug_when FROM longdescs 
+                                    WHERE bug_id=? AND who=? 
+                                          AND thetext LIKE ?
+                                 ORDER BY bug_when " . $self->sql_limit(1));
+        while (my ($bug_id, $attach_id, $submitter_id) 
+                  = $sth->fetchrow_array()) 
+        {
+            $sth2->execute($bug_id, $submitter_id, 
+                "Created an attachment (id=$attach_id)%");
+            my ($when) = $sth2->fetchrow_array();
+            if ($when) {
+                $self->do("UPDATE attachments " .
+                             "SET creation_ts='$when' " .
+                           "WHERE attach_id=$attach_id");
+            } else {
+                print "Warning - could not determine correct creation"
+                      . " time for attachment $attach_id on bug $bug_id\n";
+            }
+            ++$i;
+            print "Converted $i of $attach_count attachments\n" if !($i % 1000);
+        }
+        print "Done - converted $i attachments\n";
+
+        $self->bz_change_field_type("attachments", "creation_ts", 
+            'datetime NOT NULL');
+    }
+
+    # 2004-08-29 - Tomas.Kopal@altap.cz, bug 257303
+    # Change logincookies.lastused type from timestamp to datetime
+    my $login_lastused = $self->bz_get_field_def("logincookies", "lastused");
+    if ($login_lastused && $login_lastused->[1] =~ /^timestamp/) {
+        $self->bz_change_field_type('logincookies', 'lastused', 
+                                    'DATETIME NOT NULL');
+    }
+
+    # 2005-01-17 - Tomas.Kopal@altap.cz, bug 257315
+    # Change bugs.delta_ts type from timestamp to datetime 
+    my $bugs_deltats = $self->bz_get_field_def("bugs", "delta_ts");
+    if ($bugs_deltats && $bugs_deltats->[1] =~ /^timestamp/) {
+        $self->bz_change_field_type('bugs', 'delta_ts', 'DATETIME NOT NULL');
+    }
+
     $self->SUPER::bz_setup_database();
 }
 
diff --git a/checksetup.pl b/checksetup.pl
index f64bd350a..95746c720 100755
--- a/checksetup.pl
+++ b/checksetup.pl
@@ -1921,7 +1921,7 @@ if (!$dbh->bz_get_field_def('bugs', 'keywords')) {
 
     my @kwords;
     print "Making sure 'keywords' field of table 'bugs' is empty ...\n";
-    $dbh->do("UPDATE bugs SET delta_ts = delta_ts, keywords = '' " .
+    $dbh->do("UPDATE bugs SET keywords = '' " .
               "WHERE keywords != ''");
     print "Repopulating 'keywords' field of table 'bugs' ...\n";
     my $sth = $dbh->prepare("SELECT keywords.bug_id, keyworddefs.name " .
@@ -1936,7 +1936,7 @@ if (!$dbh->bz_get_field_def('bugs', 'keywords')) {
         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->do("UPDATE bugs SET keywords = " .
                          $dbh->quote(join(', ', @list)) .
                          " WHERE bug_id = $bugid");
             }
@@ -2135,7 +2135,7 @@ if ($dbh->bz_get_field_def('bugs_activity', 'field')) {
 
 if (!$dbh->bz_get_field_def('bugs', 'lastdiffed')) {
     $dbh->bz_add_field('bugs', 'lastdiffed', 'datetime');
-    $dbh->do('UPDATE bugs SET lastdiffed = now(), delta_ts = delta_ts');
+    $dbh->do('UPDATE bugs SET lastdiffed = now()');
 }
 
 
@@ -2172,11 +2172,7 @@ if ($dbh->bz_get_index_def('profiles', 'login_name')->[1]) {
                        ["longdescs", "who"]) {
             my ($table, $field) = (@$i);
             print "   Updating $table.$field ...\n";
-            my $extra = "";
-            if ($table eq "bugs") {
-                $extra = ", delta_ts = delta_ts";
-            }
-            $dbh->do("UPDATE $table SET $field = $u1 $extra " .
+            $dbh->do("UPDATE $table SET $field = $u1 " .
                       "WHERE $field = $u2");
         }
         $dbh->do("DELETE FROM profiles WHERE userid = $u2");
@@ -2278,7 +2274,7 @@ if (($_ = $dbh->bz_get_field_def('components', 'initialqacontact')) and
 
 if (!$dbh->bz_get_field_def('bugs', 'everconfirmed')) {
     $dbh->bz_add_field('bugs', 'everconfirmed',  'tinyint not null');
-    $dbh->do("UPDATE bugs SET everconfirmed = 1, delta_ts = delta_ts");
+    $dbh->do("UPDATE bugs SET everconfirmed = 1");
 }
 $dbh->bz_add_field('products', 'maxvotesperbug', 'smallint not null default 10000');
 $dbh->bz_add_field('products', 'votestoconfirm', 'smallint not null');
@@ -2295,7 +2291,7 @@ if (!$milestones_exist && $dbh->bz_get_field_def('bugs', 'product')) {
     print "Replacing blank milestones...\n";
 
     $dbh->do("UPDATE bugs " .
-             "SET target_milestone = '---', delta_ts=delta_ts " .
+             "SET target_milestone = '---' " .
              "WHERE target_milestone = ' '");
 
     # If we are upgrading from 2.8 or earlier, we will have *created*
@@ -2772,7 +2768,7 @@ if ($dbh->bz_get_field_def("products", "product")) {
                  "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 " .
+        $dbh->do("UPDATE bugs SET product_id = $product_id " .
                  "WHERE product = " . $dbh->quote($product));
         $dbh->do("UPDATE attachstatusdefs SET product_id = $product_id " .
                  "WHERE product = " . $dbh->quote($product)) if $dbh->bz_table_exists("attachstatusdefs");
@@ -2795,7 +2791,7 @@ if ($dbh->bz_get_field_def("products", "product")) {
             $components{$component} = {};
         }
         $components{$component}{$product_id} = 1;
-        $dbh->do("UPDATE bugs SET component_id = $component_id, delta_ts=delta_ts " .
+        $dbh->do("UPDATE bugs SET component_id = $component_id " .
                   "WHERE component = " . $dbh->quote($component) .
                    " AND product_id = $product_id");
     }
@@ -2829,53 +2825,6 @@ if ($dbh->bz_get_field_def("products", "product")) {
     $dbh->do("CREATE INDEX components_name_idx ON components(name)");
 }
 
-# 2002-08-14 - bbaetz@student.usyd.edu.au - bug 153578
-# attachments creation time needs to be a datetime, not a timestamp
-my $fielddef;
-if (($fielddef = $dbh->bz_get_field_def("attachments", "creation_ts")) &&
-    $fielddef->[1] =~ /^timestamp/) {
-    print "Fixing creation time on attachments...\n";
-
-    my $sth = $dbh->prepare("SELECT COUNT(attach_id) FROM attachments");
-    $sth->execute();
-    my ($attach_count) = $sth->fetchrow_array();
-
-    if ($attach_count > 1000) {
-        print "This may take a while...\n";
-    }
-    my $i = 0;
-
-    # This isn't just as simple as changing the field type, because
-    # the creation_ts was previously updated when an attachment was made
-    # obsolete from the attachment creation screen. So we have to go
-    # and recreate these times from the comments..
-    $sth = $dbh->prepare("SELECT bug_id, attach_id, submitter_id " .
-                           "FROM attachments");
-    $sth->execute();
-
-    # Restrict this as much as possible in order to avoid false positives, and
-    # keep the db search time down
-    my $sth2 = $dbh->prepare("SELECT bug_when FROM longdescs " .
-                              "WHERE bug_id=? AND who=? AND thetext LIKE ? " .
-                           "ORDER BY bug_when " . $dbh->sql_limit(1));
-    while (my ($bug_id, $attach_id, $submitter_id) = $sth->fetchrow_array()) {
-        $sth2->execute($bug_id, $submitter_id, "Created an attachment (id=$attach_id)%");
-        my ($when) = $sth2->fetchrow_array();
-        if ($when) {
-            $dbh->do("UPDATE attachments " .
-                        "SET creation_ts='$when' " .
-                      "WHERE attach_id=$attach_id");
-        } else {
-            print "Warning - could not determine correct creation time for attachment $attach_id on bug $bug_id\n";
-        }
-        ++$i;
-        print "Converted $i of $attach_count attachments\n" if !($i % 1000);
-    }
-    print "Done - converted $i attachments\n";
-
-    $dbh->bz_change_field_type("attachments", "creation_ts", "datetime NOT NULL");
-}
-
 # 2002-09-22 - bugreport@peshkin.net - bug 157756
 #
 # If the whole groups system is new, but the installation isn't, 
@@ -3718,13 +3667,6 @@ if ($dbh->bz_get_field_def('bugs', 'short_desc')->[2]) { # if it allows nulls
 # Support classification level
 $dbh->bz_add_field('products', 'classification_id', 'smallint NOT NULL DEFAULT 1');
 
-# 2004-08-29 - Tomas.Kopal@altap.cz, bug 257303
-# Change logincookies.lastused type from timestamp to datetime
-if (($fielddef = $dbh->bz_get_field_def("logincookies", "lastused")) &&
-    $fielddef->[1] =~ /^timestamp/) {
-    $dbh->bz_change_field_type('logincookies', 'lastused', 'DATETIME NOT NULL');
-}
-
 # 2005-01-12 Nick Barnes <nb@ravenbrook.com> bug 278010
 # Rename any group which has an empty name.
 # Note that there can be at most one such group (because of
@@ -3755,13 +3697,6 @@ if ($emptygroupid) {
     print "Group $emptygroupid had an empty name; renamed as '$trygroupname'.\n";
 }
 
-# 2005-01-17 - Tomas.Kopal@altap.cz, bug 257315
-# Change bugs.delta_ts type from timestamp to datetime
-if (($fielddef = $dbh->bz_get_field_def("bugs", "delta_ts")) &&
-    $fielddef->[1] =~ /^timestamp/) {
-    $dbh->bz_change_field_type('bugs', 'delta_ts', 'DATETIME NOT NULL');
-}
-
 # 2005-02-12 bugreport@peshkin.net, bug 281787
 if (!defined $dbh->bz_get_index_def('attachments','submitter_id')) {
     print "Adding index for submitter_id column in attachments table...\n";