From bc7607c1c4e31b9583a6a772c41567c1b1928e6d Mon Sep 17 00:00:00 2001 From: "bugreport%peshkin.net" <> Date: Sat, 27 Aug 2005 06:11:30 +0000 Subject: Bug 305333: Move attachments.thedata to its own table Patch by Joel Peshkin r=lpsolit, a=justdave --- Bugzilla/Attachment.pm | 2 ++ Bugzilla/DB/Schema.pm | 8 +++++- Bugzilla/Search.pm | 13 ++++++++++ Bugzilla/Search/Quicksearch.pm | 4 +-- attachment.cgi | 30 +++++++++++++++++----- checksetup.pl | 26 +++++++++++++++++-- contrib/bug_email.pl | 8 +++--- contrib/cmdline/query.conf | 2 +- contrib/gnatsparse/gnatsparse.py | 7 +++-- contrib/jb2bz.py | 7 +++-- .../en/default/pages/quicksearchhack.html.tmpl | 2 +- 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 @@   attachmentdata attachdata - Attachment Data (“attachments.thedata”) + Attachment Data (“attach_data.thedata”)   -- cgit v1.2.3-24-g4f1b