Bug 153578 - Attachment modified date is meant to be attachment creation

date
r=preed, joel

3 files changed, 49 insertions, 12 deletions

diff --git a/attachment.cgi b/attachment.cgi
index 8a6c1b033..5614549e4 100755
--- a/attachment.cgi
+++ b/attachment.cgi
@@ -382,11 +382,6 @@ sub viewall
     ($a{'attachid'}, $a{'date'}, $a{'contenttype'}, 
      $a{'description'}, $a{'ispatch'}, $a{'isobsolete'}) = FetchSQLData();
 
-    # Format the attachment's creation/modification date into something readable.
-    if ($a{'date'} =~ /^(\d\d)(\d\d)(\d\d)(\d\d)(\d\d)(\d\d)(\d\d)$/) {
-        $a{'date'} = "$3/$4/$2 $5:$6";
-    }
-
     # Flag attachments as to whether or not they can be viewed (as opposed to
     # being downloaded).  Currently I decide they are viewable if their MIME type 
     # is either text/*, image/*, or application/vnd.mozilla.*.
@@ -480,8 +475,8 @@ sub insert
   my $thedata = SqlQuote($::FORM{'data'});
 
   # Insert the attachment into the database.
-  SendSQL("INSERT INTO attachments (bug_id, filename, description, mimetype, ispatch, submitter_id, thedata) 
-           VALUES ($::FORM{'bugid'}, $filename, $description, $contenttype, $::FORM{'ispatch'}, $::userid, $thedata)");
+  SendSQL("INSERT INTO attachments (bug_id, creation_ts, filename, description, mimetype, ispatch, submitter_id, thedata) 
+           VALUES ($::FORM{'bugid'}, now(), $filename, $description, $contenttype, $::FORM{'ispatch'}, $::userid, $thedata)");
 
   # Retrieve the ID of the newly created attachment record.
   SendSQL("SELECT LAST_INSERT_ID()");
@@ -676,8 +671,7 @@ sub update
            SET     description = $quoteddescription , 
                    mimetype = $quotedcontenttype , 
                    ispatch = $::FORM{'ispatch'} , 
-                   isobsolete = $::FORM{'isobsolete'} , 
-                   creation_ts = creation_ts
+                   isobsolete = $::FORM{'isobsolete'}
            WHERE   attach_id = $::FORM{'id'}
          ");
 
diff --git a/checksetup.pl b/checksetup.pl
index 9c1150080..7c6b7d399 100755
--- a/checksetup.pl
+++ b/checksetup.pl
@@ -1310,7 +1310,7 @@ $table{bugs_activity} =
 $table{attachments} =
    'attach_id mediumint not null auto_increment primary key,
     bug_id mediumint not null,
-    creation_ts timestamp,
+    creation_ts datetime not null,
     description mediumtext not null,
     mimetype mediumtext not null,
     ispatch tinyint,
@@ -3159,7 +3159,50 @@ if (GetFieldDef("products", "product")) {
     $dbh->do("ALTER TABLE components ADD UNIQUE (product_id, name)");
     $dbh->do("ALTER TABLE components ADD INDEX (name)");
 }
-        
+
+# 2002-08-XX - bbaetz@student.usyd.edu.au - bug 153578
+# attachments creation time needs to be a datetime, not a timestamp
+my $fielddef;
+if (($fielddef = GetFieldDef("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 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";
+
+    ChangeFieldType("attachments", "creation_ts", "datetime NOT NULL");
+}
+
 # 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/template/en/default/attachment/list.html.tmpl b/template/en/default/attachment/list.html.tmpl
index 5bc5217c9..bc25c5721 100644
--- a/template/en/default/attachment/list.html.tmpl
+++ b/template/en/default/attachment/list.html.tmpl
@@ -24,7 +24,7 @@
   <tr>
     <th bgcolor="#cccccc" align="left">Attachment</th>
     <th bgcolor="#cccccc" align="left">Type</th>
-    <th bgcolor="#cccccc" align="left">Modified</th>
+    <th bgcolor="#cccccc" align="left">Created</th>
     <th bgcolor="#cccccc" align="left">Status</th>
     <th bgcolor="#cccccc" align="left">Actions</th>
   </tr>