Bug 305333: Move attachments.thedata to its own table

Patch by Joel Peshkin <bugreport@peshkin.net>
r=lpsolit, a=justdave

11 files changed, 88 insertions, 21 deletions

diff --git a/Bugzilla/Attachment.pm b/Bugzilla/Attachment.pm
index 78f4ceed5..558d7f8bc 100644
--- a/Bugzilla/Attachment.pm
+++ b/Bugzilla/Attachment.pm
@@ -78,6 +78,8 @@ sub query
                                       isobsolete, isprivate, LENGTH(thedata),
                                       submitter_id
                                       FROM attachments
+                                      INNER JOIN attach_data
+                                      ON id = attach_id
                                       WHERE bug_id = ? ORDER BY attach_id",
                                       undef, $bugid);
 
diff --git a/Bugzilla/DB/Schema.pm b/Bugzilla/DB/Schema.pm
index 3e86cc50d..e77e6d85b 100644
--- a/Bugzilla/DB/Schema.pm
+++ b/Bugzilla/DB/Schema.pm
@@ -301,7 +301,6 @@ use constant ABSTRACT_SCHEMA => {
             mimetype     => {TYPE => 'MEDIUMTEXT', NOTNULL => 1},
             ispatch      => {TYPE => 'BOOLEAN'},
             filename     => {TYPE => 'varchar(100)', NOTNULL => 1},
-            thedata      => {TYPE => 'LONGBLOB', NOTNULL => 1},
             submitter_id => {TYPE => 'INT3', NOTNULL => 1},
             isobsolete   => {TYPE => 'BOOLEAN', NOTNULL => 1,
                              DEFAULT => 'FALSE'},
@@ -314,6 +313,13 @@ use constant ABSTRACT_SCHEMA => {
             attachments_submitter_id_idx => ['submitter_id', 'bug_id'],
         ],
     },
+    attach_data => {
+        FIELDS => [
+            id      => {TYPE => 'INT3', NOTNULL => 1,
+                        PRIMARYKEY => 1},
+            thedata => {TYPE => 'LONGBLOB', NOTNULL => 1},
+        ],
+    },
 
     duplicates => {
         FIELDS => [
diff --git a/Bugzilla/Search.pm b/Bugzilla/Search.pm
index f30fe733b..64342d17c 100644
--- a/Bugzilla/Search.pm
+++ b/Bugzilla/Search.pm
@@ -756,6 +756,19 @@ sub init {
                     "ON groups_$chartid.id = bug_group_map_$chartid.group_id");
             $f = "groups_$chartid.name";
          },
+         "^attach_data\.thedata," => sub {
+             my $atable = "attachments_$chartid";
+             my $dtable = "attachdata_$chartid";
+             my $extra = "";
+             if (Param("insidergroup") && !UserInGroup(Param("insidergroup"))) {
+                 $extra = "AND $atable.isprivate = 0";
+             }
+             push(@supptables, "INNER JOIN attachments AS $atable " .
+                               "ON bugs.bug_id = $atable.bug_id $extra");
+             push(@supptables, "INNER JOIN attach_data AS $dtable " .
+                               "ON $dtable.id = $atable.attach_id");
+             $f = "$dtable.thedata";
+         },
          "^attachments\..*," => sub {
              my $table = "attachments_$chartid";
              my $extra = "";
diff --git a/Bugzilla/Search/Quicksearch.pm b/Bugzilla/Search/Quicksearch.pm
index 9f6724507..365e56469 100644
--- a/Bugzilla/Search/Quicksearch.pm
+++ b/Bugzilla/Search/Quicksearch.pm
@@ -80,8 +80,8 @@ my %mappings = (# Status, Resolution, Platform, OS, Priority, Severity
                 "attachment" => "attachments.description",
                 "attachmentdesc" => "attachments.description",
                 "attachdesc" => "attachments.description",
-                "attachmentdata" => "attachments.thedata",
-                "attachdata" => "attachments.thedata",
+                "attachmentdata" => "attach_data.thedata",
+                "attachdata" => "attach_data.thedata",
                 "attachmentmimetype" => "attachments.mimetype",
                 "attachmimetype" => "attachments.mimetype");
 
diff --git a/attachment.cgi b/attachment.cgi
index eec1e0a2e..86cd1698d 100755
--- a/attachment.cgi
+++ b/attachment.cgi
@@ -472,6 +472,7 @@ sub view
 
     # Retrieve the attachment content and its content type from the database.
     SendSQL("SELECT mimetype, filename, thedata FROM attachments " .
+            "INNER JOIN attach_data ON id = attach_id " .
             "WHERE attach_id = $attach_id");
     my ($contenttype, $filename, $thedata) = FetchSQLData();
    
@@ -595,7 +596,11 @@ sub get_unified_diff
   require File::Temp;
 
   # Get the patch
-  SendSQL("SELECT bug_id, description, ispatch, thedata FROM attachments WHERE attach_id = $id");
+  SendSQL("SELECT bug_id, description, ispatch, thedata " . 
+          "FROM attachments " .
+          "INNER JOIN attach_data " .
+          "ON id = attach_id " .
+          "WHERE attach_id = $id");
   my ($bugid, $description, $ispatch, $thedata) = FetchSQLData();
   if (!$ispatch) {
     $vars->{'attach_id'} = $id;
@@ -726,6 +731,7 @@ sub diff
 
   # Get patch data
   SendSQL("SELECT bug_id, description, ispatch, thedata FROM attachments " .
+          "INNER JOIN attach_data ON id = attach_id " .
           "WHERE attach_id = $attach_id");
   my ($bugid, $description, $ispatch, $thedata) = FetchSQLData();
 
@@ -797,7 +803,10 @@ sub viewall
             $dbh->sql_date_format('creation_ts', '%Y.%m.%d %H:%i') . ",
             mimetype, description, ispatch, isobsolete, isprivate, 
             LENGTH(thedata)
-            FROM attachments WHERE bug_id = $bugid $privacy 
+            FROM attachments 
+            INNER JOIN attach_data
+            ON attach_id = id
+            WHERE bug_id = $bugid $privacy 
             ORDER BY attach_id");
   my @attachments; # the attachments array
   while (MoreSQLData())
@@ -945,19 +954,23 @@ sub insert
 
   # Insert the attachment into the database.
   my $sth = $dbh->prepare("INSERT INTO attachments
-      (thedata, bug_id, creation_ts, filename, description,
+      (bug_id, creation_ts, filename, description,
        mimetype, ispatch, isprivate, submitter_id) 
-      VALUES (?, $bugid, $sql_timestamp, $sql_filename,
+      VALUES ($bugid, $sql_timestamp, $sql_filename,
               $description, $contenttype, " . $cgi->param('ispatch') . ",
               $isprivate, $userid)");
+  $sth->execute();
+  # Retrieve the ID of the newly created attachment record.
+  my $attachid = $dbh->bz_last_key('attachments', 'attach_id');
+
   # We only use $data here in this INSERT with a placeholder,
   # so it's safe.
+  $sth = $dbh->prepare("INSERT INTO attach_data
+                           (id, thedata) VALUES ($attachid, ?)");
   trick_taint($data);
   $sth->bind_param(1, $data, $dbh->BLOB_TYPE);
   $sth->execute();
 
-  # Retrieve the ID of the newly created attachment record.
-  my $attachid = $dbh->bz_last_key('attachments', 'attach_id');
 
   # If the file is to be stored locally, stream the file from the webserver
   # to the local file without reading it into a local variable.
@@ -1083,7 +1096,10 @@ sub edit
 
   # Retrieve the attachment from the database.
   SendSQL("SELECT description, mimetype, filename, bug_id, ispatch, isobsolete, isprivate, LENGTH(thedata)
-           FROM attachments WHERE attach_id = $attach_id");
+           FROM attachments
+           INNER JOIN attach_data
+           ON id = attach_id
+           WHERE attach_id = $attach_id");
   my ($description, $contenttype, $filename, $bugid, $ispatch, $isobsolete, $isprivate, $datasize) = FetchSQLData();
 
   my $isviewable = isViewable($contenttype);
diff --git a/checksetup.pl b/checksetup.pl
index 7e57587a5..5fe6cea97 100755
--- a/checksetup.pl
+++ b/checksetup.pl
@@ -1702,7 +1702,6 @@ AddFDef("cc", "CC", 1);
 AddFDef("dependson", "BugsThisDependsOn", 1);
 AddFDef("blocked", "OtherBugsDependingOnThis", 1);
 AddFDef("attachments.description", "Attachment description", 0);
-AddFDef("attachments.thedata", "Attachment data", 0);
 AddFDef("attachments.filename", "Attachment filename", 0);
 AddFDef("attachments.mimetype", "Attachment mime type", 0);
 AddFDef("attachments.ispatch", "Attachment is patch", 0);
@@ -1740,6 +1739,9 @@ AddFDef("percentage_complete", "Percentage Complete", 0);
 
 AddFDef("content", "Content", 0);
 
+$dbh->do("DELETE FROM fielddefs WHERE name='attachments.thedata'");
+AddFDef("attach_data.thedata", "Attachment data", 0);
+
 ###########################################################################
 # Detect changed local settings
 ###########################################################################
@@ -4009,6 +4011,27 @@ if ($dbh->bz_index_info('attachments', 'attachments_submitter_id_idx')
 $dbh->bz_add_index('attachments', 'attachments_submitter_id_idx',
                    [qw(submitter_id bug_id)]);
 
+# 2005-08-25 - bugreport@peshkin.net - Bug 305333
+if ($dbh->bz_column_info("attachments", "thedata")) {
+    print "Migrating attachment data to its own table...\n";
+    print "(This may take a very long time)\n";
+    my $sth_get1 = $dbh->prepare("SELECT attach_id 
+                                   FROM attachments");
+    my $sth_get2 = $dbh->prepare("SELECT thedata 
+                                   FROM attachments WHERE attach_id = ?");
+    $sth_get1->execute();
+    while (my ($id) = $sth_get1->fetchrow_array) {
+        $sth_get2->execute($id);
+        my ($thedata) = $sth_get2->fetchrow_array;
+        my $sth_put = $dbh->prepare("INSERT INTO attach_data
+                                     (id, thedata) VALUES ($id, ?)");
+        $sth_put->bind_param(1, $thedata, $dbh->BLOB_TYPE);
+        $sth_put->execute();
+    }
+    $dbh->bz_drop_column("attachments", "thedata");    
+}
+
+
 # If you had to change the --TABLE-- definition in any way, then add your
 # differential change code *** A B O V E *** this comment.
 #
@@ -4134,7 +4157,6 @@ add_setting ('csv_colsepchar', {',' => 1, ';' => 2 }, ',' );
 # Create Administrator  --ADMIN--
 ###########################################################################
 
-
 sub bailout {   # this is just in case we get interrupted while getting passwd
     if ($^O !~ /MSWin32/i) {
         system("stty","echo"); # re-enable input echoing
diff --git a/contrib/bug_email.pl b/contrib/bug_email.pl
index 1590387e6..98b8d157e 100755
--- a/contrib/bug_email.pl
+++ b/contrib/bug_email.pl
@@ -38,7 +38,7 @@
 #
 # You need to work with bug_email.pl the MIME::Parser installed.
 # 
-# $Id: bug_email.pl,v 1.28 2005/07/08 02:31:43 mkanat%kerio.com Exp $
+# $Id: bug_email.pl,v 1.29 2005/08/26 23:11:32 bugreport%peshkin.net Exp $
 ###############################################################
 
 # 02/12/2000 (SML)
@@ -164,14 +164,16 @@ sub storeAttachments( $$ )
 
 
         # Make SQL-String
-        my $sql = "insert into attachments (bug_id, creation_ts, description, mimetype, ispatch, filename, thedata, submitter_id) values (";
+        my $sql = "insert into attachments (bug_id, creation_ts, description, mimetype, ispatch, filename, submitter_id) values (";
         $sql .= "$bugid, now(), " . SqlQuote( $description ) . ", ";
         $sql .= SqlQuote( $mime ) . ", ";
         $sql .= "0, ";
         $sql .= SqlQuote( $decoded_file ) . ", ";
-        $sql .= SqlQuote( $data ) . ", ";
         $sql .= "$submitter_id );";
         SendSQL( $sql ) unless( $test );
+        $sql = "insert into attach_data (id, thedata) values (LAST_INSERT_ID(), ";
+        $sql .= SqlQuote( $data ) . ")";
+        SendSQL( $sql ) unless( $test );
     }
     
     return( $att_count );
diff --git a/contrib/cmdline/query.conf b/contrib/cmdline/query.conf
index a44347b2b..87390cd3f 100644
--- a/contrib/cmdline/query.conf
+++ b/contrib/cmdline/query.conf
@@ -43,7 +43,7 @@ bug_file_loc            substring       "u","url"
 status_whiteboard       substring       "w","whiteboard"
 keywords                substring       "k","K","keywords"
 attachments.description substring       "attachdesc"
-attachments.thedata     substring       "attachdata"
+attach_data.thedata     substring       "attachdata"
 attachments.mimetype    substring       "attachmime"
 dependson               substring       # bug 30823
 blocked                 substring       # bug 30823
diff --git a/contrib/gnatsparse/gnatsparse.py b/contrib/gnatsparse/gnatsparse.py
index 5f7cde713..b317b240d 100755
--- a/contrib/gnatsparse/gnatsparse.py
+++ b/contrib/gnatsparse/gnatsparse.py
@@ -451,7 +451,7 @@ class Bugzillabug(object):
             print >>outfile, "  %s, %s, %s, %s);" % (id, who, when, text)
         for name, data, who in self.attachments:
             print >>outfile, "\ninsert into attachments ("
-            print >>outfile, "  bug_id, filename, description, mimetype, ispatch, submitter_id, thedata) values ("
+            print >>outfile, "  bug_id, filename, description, mimetype, ispatch, submitter_id) values ("
 	    ftype = None
 	    # It's *magic*!
 	    if name.endswith(".ii") == 1:
@@ -463,7 +463,10 @@ class Bugzillabug(object):
             if ftype is None:
                 ftype = "application/octet-stream"
             
-            print >>outfile, "%s,%s,%s, %s,0, %s,%s);" %(self.bug_id, SqlQuote(name), SqlQuote(name), SqlQuote (ftype), who, SqlQuote(zlib.compress(data)))
+            print >>outfile, "%s,%s,%s, %s,0, %s,%s);" %(self.bug_id, SqlQuote(name), SqlQuote(name), SqlQuote (ftype), who)
+            print >>outfile, "\ninsert into attach_data ("
+            print >>outfile, "\n(id, thedata) values (last_insert_id(),"
+            print >>outfile, "%s);" % (SqlQuote(zlib.compress(data)))
         for newstate, oldstate, fieldid, changedby, changedwhen in self.bug_activity:
             print >>outfile, "\ninsert into bugs_activity ("
             print >>outfile, "  bug_id, who, bug_when, fieldid, added, removed) values ("
diff --git a/contrib/jb2bz.py b/contrib/jb2bz.py
index ed8231dfc..e2f502927 100644
--- a/contrib/jb2bz.py
+++ b/contrib/jb2bz.py
@@ -248,10 +248,13 @@ def process_jitterbug(filename):
     for a in current['attachments']:
         cursor.execute( "INSERT INTO attachments SET " \
                         "bug_id=%s, creation_ts=%s, description='', mimetype=%s," \
-                        "filename=%s, thedata=%s, submitter_id=%s",
+                        "filename=%s, submitter_id=%s",
                         [ current['number'],
                           time.strftime("%Y-%m-%d %H:%M:%S", current['date-reported'][:9]),
-                          a[1], a[0], a[2], reporter ])
+                          a[1], a[0], reporter ])
+        cursor.execute( "INSERT INTO attach_data SET " \
+                        "id=LAST_INSERT_ID(), thedata=%s",
+                        [ a[2] ])
 
     cursor.close()
     db.close()
diff --git a/template/en/default/pages/quicksearchhack.html.tmpl b/template/en/default/pages/quicksearchhack.html.tmpl
index 529484991..22913ec42 100644
--- a/template/en/default/pages/quicksearchhack.html.tmpl
+++ b/template/en/default/pages/quicksearchhack.html.tmpl
@@ -255,7 +255,7 @@
   <td>&nbsp;</td>
   <td><tt>attachmentdata</tt></td>
   <td><tt>attachdata</tt></td>
-  <td>Attachment Data <i>(&ldquo;attachments.thedata&rdquo;)</i></td>
+  <td>Attachment Data <i>(&ldquo;attach_data.thedata&rdquo;)</i></td>
 </tr>
 <tr>
   <td>&nbsp;</td>