- Patch #31354 by Junyor et al: move node revision information for files to a separate table. (Today's critical bugfix #1.)

5 files changed, 264 insertions, 125 deletions

diff --git a/database/database.mysql b/database/database.mysql
index 38f8bb272..a7a9ee061 100644
--- a/database/database.mysql
+++ b/database/database.mysql
@@ -276,17 +276,26 @@ CREATE TABLE client_system (
 --
 
 CREATE TABLE files (
-  fid int(10) unsigned NOT NULL default '0',
-  nid int(10) unsigned NOT NULL default '0',
-  vid int(10) unsigned NOT NULL default '0',
+  fid int(10) unsigned NOT NULL default 0,
+  nid int(10) unsigned NOT NULL default 0,
   filename varchar(255) NOT NULL default '',
-  description varchar(255) NOT NULL default '',
   filepath varchar(255) NOT NULL default '',
   filemime varchar(255) NOT NULL default '',
-  filesize int(10) unsigned NOT NULL default '0',
-  list tinyint(1) unsigned NOT NULL default '0',
-  KEY vid (vid),
-  KEY fid (fid)
+  filesize int(10) unsigned NOT NULL default 0,
+  PRIMARY KEY (fid)
+) TYPE=MyISAM
+/*!40100 DEFAULT CHARACTER SET utf8 */ ;
+
+--
+-- Table structure for table 'file_revisions'
+--
+
+CREATE TABLE file_revisions (
+  fid int(10) unsigned NOT NULL default 0,
+  vid int(10) unsigned NOT NULL default 0,
+  description varchar(255) NOT NULL default '',
+  list tinyint(1) unsigned NOT NULL default 0,
+  PRIMARY KEY (fid, vid)
 ) TYPE=MyISAM
 /*!40100 DEFAULT CHARACTER SET utf8 */ ;
 
diff --git a/database/database.pgsql b/database/database.pgsql
index 1202df56c..839b771c0 100644
--- a/database/database.pgsql
+++ b/database/database.pgsql
@@ -264,17 +264,25 @@ CREATE TABLE client_system (
 
 CREATE TABLE files (
   fid SERIAL,
-  nid integer NOT NULL default '0',
-  vid integer NOT NULL default '0',
-  description varchar(255) NOT NULL default '',
+  nid integer NOT NULL default 0,
   filename varchar(255) NOT NULL default '',
   filepath varchar(255) NOT NULL default '',
   filemime varchar(255) NOT NULL default '',
-  filesize integer NOT NULL default '0',
-  list smallint NOT NULL default '0'
+  filesize integer NOT NULL default 0,
+  PRIMARY KEY (fid)
+);
+
+--
+-- Table structure for table 'file_revisions'
+--
+
+CREATE TABLE file_revisions (
+  fid integer NOT NULL default 0,
+  vid integer NOT NULL default 0,
+  description varchar(255) NOT NULL default '',
+  list smallint NOT NULL default 0,
+  PRIMARY KEY (fid, vid)
 );
-CREATE INDEX files_fid_idx ON files(fid);
-CREATE INDEX files_vid_idx ON files(vid);
 
 --
 -- Table structure for table 'filter_formats'
diff --git a/database/updates.inc b/database/updates.inc
index 095176103..08741457d 100644
--- a/database/updates.inc
+++ b/database/updates.inc
@@ -1623,3 +1623,65 @@ function system_update_172() {
   }
   return array('#finished' => $_SESSION['system_update_172'] / $_SESSION['system_update_172_max']);
 }
+
+function system_update_173() {
+  $ret = array();
+
+  switch ($GLOBALS['db_type']) {
+    case 'pgsql':
+      // create file_revisions table
+      $ret[] = update_sql("CREATE TABLE {file_revisions} (
+        fid integer NOT NULL default 0,
+        vid integer NOT NULL default 0,
+        description varchar(255) NOT NULL default '',
+        list smallint NOT NULL default 0,
+        PRIMARY KEY (fid, vid))");
+      $ret[] = update_sql("INSERT INTO {file_revisions} SELECT fid, vid, description, list FROM {files}");
+
+      // alter files table
+      $ret[] = update_sql("CREATE TABLE {files_copy} AS SELECT * FROM {files}");
+      $ret[] = update_sql("DROP TABLE {files}");
+      $ret[] = update_sql("CREATE TABLE {files} (
+        fid SERIAL,
+        nid integer NOT NULL default 0,
+        filename varchar(255) NOT NULL default '',
+        filepath varchar(255) NOT NULL default '',
+        filemime varchar(255) NOT NULL default '',
+        filesize integer NOT NULL default 0,
+        PRIMARY KEY (fid))");
+      $ret[] = update_sql("INSERT INTO {files} SELECT DISTINCT ON (fid) fid, nid, filename, filepath, filemime, filesize FROM {files_copy}");
+      $ret[] = update_sql("SELECT setval('{files}_fid_seq', max(fid)) FROM {files}");
+      $ret[] = update_sql("DROP TABLE {files_copy}");
+      break;
+    case 'mysqli':
+    case 'mysql':
+      // create file_revisions table
+      $ret[] = update_sql("CREATE TABLE {file_revisions} (
+        fid int(10) unsigned NOT NULL default 0,
+        vid int(10) unsigned NOT NULL default 0,
+        description varchar(255) NOT NULL default '',
+        list tinyint(1) unsigned NOT NULL default 0,
+        PRIMARY KEY (fid, vid)
+        ) TYPE=MyISAM");
+      $ret[] = update_sql('INSERT INTO {file_revisions} SELECT fid, vid, description, list FROM {files}');
+
+      // alter files table
+      $ret[] = update_sql("CREATE TABLE {files_copy} AS SELECT * FROM {files}");
+      $ret[] = update_sql("DROP TABLE {files}");
+      $ret[] = update_sql("CREATE TABLE {files} (
+        fid int(10) unsigned NOT NULL default 0,
+        nid int(10) unsigned NOT NULL default 0,
+        filename varchar(255) NOT NULL default '',
+        filepath varchar(255) NOT NULL default '',
+        filemime varchar(255) NOT NULL default '',
+        filesize int(10) unsigned NOT NULL default 0,
+        PRIMARY KEY (fid)
+        ) TYPE=MyISAM
+        /*!40100 DEFAULT CHARACTER SET utf8 */");
+      $ret[] = update_sql("INSERT IGNORE INTO {files} SELECT fid, nid, filename, filepath, filemime, filesize FROM {files_copy}");
+      $ret[] = update_sql("DROP TABLE {files_copy}");
+      break;
+  }
+
+  return $ret;
+}
diff --git a/modules/upload.module b/modules/upload.module
index fc296d6df..5961add23 100644
--- a/modules/upload.module
+++ b/modules/upload.module
@@ -210,6 +210,7 @@ function upload_nodeapi(&$node, $op, $arg) {
           $node->list[$key] = $file->list;
         }
       }
+
       if (($file = file_check_upload('upload')) && user_access('upload files')) {
         global $user;
 
@@ -319,19 +320,12 @@ function upload_nodeapi(&$node, $op, $arg) {
         upload_save($node);
       }
       break;
-    case 'delete revision':
-      $node->files = upload_load($node);
-      foreach ($node->files as $file) {
-        // Check any other revisions pointing to file first.
-        if( db_result(db_query("SELECT COUNT(fid) FROM {files} WHERE fid = %d", $file->fid)) == 1 ) {
-          file_delete($file->filepath);
-        }
-      }
-      db_query("DELETE FROM {files} WHERE vid = %d", $node->vid);
-      break;
     case 'delete':
       upload_delete($node);
       break;
+    case 'delete revision':
+      upload_delete_revision($node);
+      break;
     case 'search result':
       return $node->files ? format_plural(count($node->files), '1 attachment', '%count attachments') : null;
     case 'rss item':
@@ -363,65 +357,74 @@ function upload_nodeapi(&$node, $op, $arg) {
  * @param $uid
  *   The integer user id of a user.
  * @return
- *   The ammount of disk space used by the user in bytes.
+ *   The amount of disk space used by the user in bytes.
  */
 function upload_space_used($uid) {
-  return db_result(db_query('SELECT SUM(f.filesize) FROM {files} f INNER JOIN {node_revisions} n ON f.vid = n.vid WHERE uid = %d', $uid));
+  return db_result(db_query('SELECT SUM(filesize) FROM {files} f INNER JOIN {node} n ON f.nid = n.nid WHERE n.uid = %d', $uid));
 }
 
 /**
  * Determine how much disk space is occupied by uploaded files.
  *
  * @return
- *   The ammount of disk space used by uploaded files in bytes.
+ *   The amount of disk space used by uploaded files in bytes.
  */
 function upload_total_space_used() {
-  return db_result(db_query('SELECT SUM(f.filesize) FROM {files} f INNER JOIN {node_revisions} n ON f.vid = n.vid'));
+  return db_result(db_query('SELECT SUM(filesize) FROM {files}'));
 }
 
 function upload_save($node) {
-  $node->files = upload_load($node);
+  $node->old_files = isset($node->files) ? $node->files : array();
   upload_nodeapi($node, 'validate', NULL);
-  foreach ((array)$node->files as $key => $file) {
-    if ($file->source && !$file->remove) {
-      // Clean up the session:
-      unset($_SESSION['file_uploads'][$file->source]);
+  $node->files = $node->old_files + $node->files;
 
-      // Insert new files:
-      if ($file = file_save_upload($file, $file->filename)) {
-        $fid = db_next_id('{files}_fid');
-        db_query("INSERT INTO {files} (fid, nid, vid, filename, filepath, filemime, filesize, list, description) VALUES (%d, %d, %d, '%s', '%s', '%s', %d, %d, '%s')",
-                 $fid, $node->nid, $node->vid, $file->filename, $file->filepath, $file->filemime, $file->filesize, $node->list[$key], $node->description[$key]);
-      }
-    }
-  }
-  // Remove existing files, as needed
-  foreach ((array)$node->remove as $key => $value) {
-    if ($node->remove[$key]) {
-      $file = db_fetch_object(db_query('SELECT * FROM {files} WHERE vid = %d AND fid = %d', $node->vid, $key));
-      db_query('DELETE FROM {files} WHERE fid = %d AND vid = %d', $key, $node->vid);
-      // We only delete a file if it isn't used anymore by any revision.
-      $count = db_result(db_query('SELECT COUNT(fid) FROM {files} WHERE fid = %d', $key));
-      if (!($count > 0)) {
-        file_delete($file->filepath);
+  foreach ((array)$node->files as $key => $file) {
+    // New file upload
+    if ($file->source) {
+      // Only add a file if it's not marked for removal
+      if (!$node->remove[$key]) {
+        if ($file = file_save_upload($file, $file->filename)) {
+          $fid = db_next_id('{files}_fid');
+          db_query("INSERT INTO {files} (fid, nid, filename, filepath, filemime, filesize) VALUES (%d, %d, '%s', '%s', '%s', %d)", $fid, $node->nid, $file->filename, $file->filepath, $file->filemime, $file->filesize);
+          db_query("INSERT INTO {file_revisions} (fid, vid, list, description) VALUES (%d, %d, %d, '%s')", $fid, $node->vid, $node->list[$key], $node->description[$key]);
+        }
       }
+
+      // Clean up the session
+      unset($_SESSION['file_uploads'][$file->source]);
     }
-  }
-  // Create a new revision, as needed
-  if ($node->old_vid) {
-    foreach ((array)$node->remove as $key => $remove) {
-      if (!$remove && is_numeric($key)) {
-        $file = db_fetch_object(db_query('SELECT * FROM {files} WHERE vid = %d AND fid = %d', $node->old_vid, $key));
-        db_query("INSERT INTO {files} (fid, nid, vid, filename, filepath, filemime, filesize, list, description) VALUES (%d, %d, %d, '%s', '%s', '%s', %d, %d, '%s')",
-                 $key, $node->nid, $node->vid, $file->filename, $file->filepath, $file->filemime, $file->filesize, $node->list[$key], $node->description[$key]);
+
+    // Update existing file
+    else {
+      // Remove existing file, as needed
+      if ($node->remove[$key]) {
+        db_query('DELETE FROM {file_revisions} WHERE fid = %d AND vid = %d', $key, $node->vid);
+        // Only delete a file if it isn't used by any revision
+        $count = db_result(db_query('SELECT COUNT(fid) FROM {file_revisions} WHERE fid = %d', $key));
+        if ($count < 1) {
+          db_query('DELETE FROM {files} WHERE fid = %d', $key);
+          file_delete($file->filepath);
+        }
       }
-    }
-  }
-  // Update existing files, as needed
-  else {
-    foreach ((array)$node->list as $key => $value) {
-      if (!$node->remove[$key]) {
-        db_query('UPDATE {files} SET list = %d, description = \'%s\' WHERE fid = %d AND vid = %d', $node->list[$key], $node->description[$key], $key, $node->vid);
+
+      else {
+        // Create a new revision, as needed
+        if ($node->old_vid && is_numeric($key)) {
+          // new revision
+          if (isset($node->list)) {
+            db_query("INSERT INTO {file_revisions} (fid, vid, list, description) VALUES (%d, %d, %d, '%s')", $key, $node->vid, $node->list[$key], $node->description[$key]);
+          }
+
+          // copy of old revision
+          else {
+            db_query("INSERT INTO {file_revisions} (fid, vid, list, description) VALUES (%d, %d, %d, '%s')", $key, $node->vid, $file->list, $file->description);
+          }
+        }
+
+        // Update existing revision
+        else {
+          db_query("UPDATE {file_revisions} SET list = %d, description = '%s' WHERE fid = %d AND vid = %d", $node->list[$key], $node->description[$key], $key, $node->vid);
+        }
       }
     }
   }
@@ -430,11 +433,38 @@ function upload_save($node) {
 }
 
 function upload_delete($node) {
-  $node->files = upload_load($node);
-  foreach ($node->files as $file) {
+  $files = array();
+  $result = db_query('SELECT * FROM {files} WHERE nid = %d', $node->nid);
+  while ($file = db_fetch_object($result)) {
+    $files[$file->fid] = $file;
+  }
+
+  foreach ($files as $fid => $file) {
+    // delete all file revision information associated with the node
+    db_query('DELETE FROM {file_revisions} WHERE fid = %d', $fid);
     file_delete($file->filepath);
   }
-  db_query("DELETE FROM {files} WHERE nid = %d", $node->nid);
+
+  // delete all files associated with the node
+  db_query('DELETE FROM {files} WHERE nid = %d', $node->nid);
+}
+
+function upload_delete_revision($node) {
+  $files = upload_load($node);
+
+  foreach ($files as $file) {
+    // check if the file will be used after this revision is deleted
+    $count = db_result(db_query('SELECT COUNT(fid) FROM {file_revisions} WHERE fid = %d', $file->fid));
+
+    // if the file won't be used, delete it
+    if ($count < 2) {
+      db_query('DELETE FROM {files} WHERE fid = %d', $file->fid);
+      file_delete($file->filepath);
+    }
+  }
+
+  // delete the revision
+  db_query('DELETE FROM {file_revisions} WHERE vid = %d', $node->vid);
 }
 
 function _upload_form($node) {
@@ -504,7 +534,7 @@ function upload_load($node) {
   $files = array();
 
   if ($node->vid) {
-    $result = db_query("SELECT * FROM {files} WHERE vid = %d", $node->vid);
+    $result = db_query('SELECT * FROM {files} f INNER JOIN {file_revisions} r ON f.fid = r.fid WHERE r.vid = %d', $node->vid);
     while ($file = db_fetch_object($result)) {
       $files[$file->fid] = $file;
     }
diff --git a/modules/upload/upload.module b/modules/upload/upload.module
index fc296d6df..5961add23 100644
--- a/modules/upload/upload.module
+++ b/modules/upload/upload.module
@@ -210,6 +210,7 @@ function upload_nodeapi(&$node, $op, $arg) {
           $node->list[$key] = $file->list;
         }
       }
+
       if (($file = file_check_upload('upload')) && user_access('upload files')) {
         global $user;
 
@@ -319,19 +320,12 @@ function upload_nodeapi(&$node, $op, $arg) {
         upload_save($node);
       }
       break;
-    case 'delete revision':
-      $node->files = upload_load($node);
-      foreach ($node->files as $file) {
-        // Check any other revisions pointing to file first.
-        if( db_result(db_query("SELECT COUNT(fid) FROM {files} WHERE fid = %d", $file->fid)) == 1 ) {
-          file_delete($file->filepath);
-        }
-      }
-      db_query("DELETE FROM {files} WHERE vid = %d", $node->vid);
-      break;
     case 'delete':
       upload_delete($node);
       break;
+    case 'delete revision':
+      upload_delete_revision($node);
+      break;
     case 'search result':
       return $node->files ? format_plural(count($node->files), '1 attachment', '%count attachments') : null;
     case 'rss item':
@@ -363,65 +357,74 @@ function upload_nodeapi(&$node, $op, $arg) {
  * @param $uid
  *   The integer user id of a user.
  * @return
- *   The ammount of disk space used by the user in bytes.
+ *   The amount of disk space used by the user in bytes.
  */
 function upload_space_used($uid) {
-  return db_result(db_query('SELECT SUM(f.filesize) FROM {files} f INNER JOIN {node_revisions} n ON f.vid = n.vid WHERE uid = %d', $uid));
+  return db_result(db_query('SELECT SUM(filesize) FROM {files} f INNER JOIN {node} n ON f.nid = n.nid WHERE n.uid = %d', $uid));
 }
 
 /**
  * Determine how much disk space is occupied by uploaded files.
  *
  * @return
- *   The ammount of disk space used by uploaded files in bytes.
+ *   The amount of disk space used by uploaded files in bytes.
  */
 function upload_total_space_used() {
-  return db_result(db_query('SELECT SUM(f.filesize) FROM {files} f INNER JOIN {node_revisions} n ON f.vid = n.vid'));
+  return db_result(db_query('SELECT SUM(filesize) FROM {files}'));
 }
 
 function upload_save($node) {
-  $node->files = upload_load($node);
+  $node->old_files = isset($node->files) ? $node->files : array();
   upload_nodeapi($node, 'validate', NULL);
-  foreach ((array)$node->files as $key => $file) {
-    if ($file->source && !$file->remove) {
-      // Clean up the session:
-      unset($_SESSION['file_uploads'][$file->source]);
+  $node->files = $node->old_files + $node->files;
 
-      // Insert new files:
-      if ($file = file_save_upload($file, $file->filename)) {
-        $fid = db_next_id('{files}_fid');
-        db_query("INSERT INTO {files} (fid, nid, vid, filename, filepath, filemime, filesize, list, description) VALUES (%d, %d, %d, '%s', '%s', '%s', %d, %d, '%s')",
-                 $fid, $node->nid, $node->vid, $file->filename, $file->filepath, $file->filemime, $file->filesize, $node->list[$key], $node->description[$key]);
-      }
-    }
-  }
-  // Remove existing files, as needed
-  foreach ((array)$node->remove as $key => $value) {
-    if ($node->remove[$key]) {
-      $file = db_fetch_object(db_query('SELECT * FROM {files} WHERE vid = %d AND fid = %d', $node->vid, $key));
-      db_query('DELETE FROM {files} WHERE fid = %d AND vid = %d', $key, $node->vid);
-      // We only delete a file if it isn't used anymore by any revision.
-      $count = db_result(db_query('SELECT COUNT(fid) FROM {files} WHERE fid = %d', $key));
-      if (!($count > 0)) {
-        file_delete($file->filepath);
+  foreach ((array)$node->files as $key => $file) {
+    // New file upload
+    if ($file->source) {
+      // Only add a file if it's not marked for removal
+      if (!$node->remove[$key]) {
+        if ($file = file_save_upload($file, $file->filename)) {
+          $fid = db_next_id('{files}_fid');
+          db_query("INSERT INTO {files} (fid, nid, filename, filepath, filemime, filesize) VALUES (%d, %d, '%s', '%s', '%s', %d)", $fid, $node->nid, $file->filename, $file->filepath, $file->filemime, $file->filesize);
+          db_query("INSERT INTO {file_revisions} (fid, vid, list, description) VALUES (%d, %d, %d, '%s')", $fid, $node->vid, $node->list[$key], $node->description[$key]);
+        }
       }
+
+      // Clean up the session
+      unset($_SESSION['file_uploads'][$file->source]);
     }
-  }
-  // Create a new revision, as needed
-  if ($node->old_vid) {
-    foreach ((array)$node->remove as $key => $remove) {
-      if (!$remove && is_numeric($key)) {
-        $file = db_fetch_object(db_query('SELECT * FROM {files} WHERE vid = %d AND fid = %d', $node->old_vid, $key));
-        db_query("INSERT INTO {files} (fid, nid, vid, filename, filepath, filemime, filesize, list, description) VALUES (%d, %d, %d, '%s', '%s', '%s', %d, %d, '%s')",
-                 $key, $node->nid, $node->vid, $file->filename, $file->filepath, $file->filemime, $file->filesize, $node->list[$key], $node->description[$key]);
+
+    // Update existing file
+    else {
+      // Remove existing file, as needed
+      if ($node->remove[$key]) {
+        db_query('DELETE FROM {file_revisions} WHERE fid = %d AND vid = %d', $key, $node->vid);
+        // Only delete a file if it isn't used by any revision
+        $count = db_result(db_query('SELECT COUNT(fid) FROM {file_revisions} WHERE fid = %d', $key));
+        if ($count < 1) {
+          db_query('DELETE FROM {files} WHERE fid = %d', $key);
+          file_delete($file->filepath);
+        }
       }
-    }
-  }
-  // Update existing files, as needed
-  else {
-    foreach ((array)$node->list as $key => $value) {
-      if (!$node->remove[$key]) {
-        db_query('UPDATE {files} SET list = %d, description = \'%s\' WHERE fid = %d AND vid = %d', $node->list[$key], $node->description[$key], $key, $node->vid);
+
+      else {
+        // Create a new revision, as needed
+        if ($node->old_vid && is_numeric($key)) {
+          // new revision
+          if (isset($node->list)) {
+            db_query("INSERT INTO {file_revisions} (fid, vid, list, description) VALUES (%d, %d, %d, '%s')", $key, $node->vid, $node->list[$key], $node->description[$key]);
+          }
+
+          // copy of old revision
+          else {
+            db_query("INSERT INTO {file_revisions} (fid, vid, list, description) VALUES (%d, %d, %d, '%s')", $key, $node->vid, $file->list, $file->description);
+          }
+        }
+
+        // Update existing revision
+        else {
+          db_query("UPDATE {file_revisions} SET list = %d, description = '%s' WHERE fid = %d AND vid = %d", $node->list[$key], $node->description[$key], $key, $node->vid);
+        }
       }
     }
   }
@@ -430,11 +433,38 @@ function upload_save($node) {
 }
 
 function upload_delete($node) {
-  $node->files = upload_load($node);
-  foreach ($node->files as $file) {
+  $files = array();
+  $result = db_query('SELECT * FROM {files} WHERE nid = %d', $node->nid);
+  while ($file = db_fetch_object($result)) {
+    $files[$file->fid] = $file;
+  }
+
+  foreach ($files as $fid => $file) {
+    // delete all file revision information associated with the node
+    db_query('DELETE FROM {file_revisions} WHERE fid = %d', $fid);
     file_delete($file->filepath);
   }
-  db_query("DELETE FROM {files} WHERE nid = %d", $node->nid);
+
+  // delete all files associated with the node
+  db_query('DELETE FROM {files} WHERE nid = %d', $node->nid);
+}
+
+function upload_delete_revision($node) {
+  $files = upload_load($node);
+
+  foreach ($files as $file) {
+    // check if the file will be used after this revision is deleted
+    $count = db_result(db_query('SELECT COUNT(fid) FROM {file_revisions} WHERE fid = %d', $file->fid));
+
+    // if the file won't be used, delete it
+    if ($count < 2) {
+      db_query('DELETE FROM {files} WHERE fid = %d', $file->fid);
+      file_delete($file->filepath);
+    }
+  }
+
+  // delete the revision
+  db_query('DELETE FROM {file_revisions} WHERE vid = %d', $node->vid);
 }
 
 function _upload_form($node) {
@@ -504,7 +534,7 @@ function upload_load($node) {
   $files = array();
 
   if ($node->vid) {
-    $result = db_query("SELECT * FROM {files} WHERE vid = %d", $node->vid);
+    $result = db_query('SELECT * FROM {files} f INNER JOIN {file_revisions} r ON f.fid = r.fid WHERE r.vid = %d', $node->vid);
     while ($file = db_fetch_object($result)) {
       $files[$file->fid] = $file;
     }