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

3 files changed, 94 insertions, 15 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;
+}