maintenance/archives/patch-restructure.sql

   1 -- The Great Restructuring of October 2004
   2 -- Creates 'page', 'revision' tables and transforms the classic
   3 -- cur+old into a separate page+revision+text structure.
   4 --
   5 -- The pre-conversion 'old' table is renamed to 'text' and used
   6 -- without internal restructuring to avoid rebuilding the entire
   7 -- table. (This can be done separately if desired.)
   8 --
   9 -- The pre-conversion 'cur' table is now redundant and can be
  10 -- discarded when done.
  11
  12 CREATE TABLE /*$wgDBprefix*/page (
  13   page_id int unsigned NOT NULL auto_increment,
  14   page_namespace tinyint NOT NULL,
  15   page_title varbinary(255) NOT NULL,
  16   page_restrictions tinyblob NOT NULL,
  17   page_counter bigint unsigned NOT NULL default '0',
  18   page_is_redirect tinyint unsigned NOT NULL default '0',
  19   page_is_new tinyint unsigned NOT NULL default '0',
  20   page_random real unsigned NOT NULL,
  21   page_touched binary(14) NOT NULL default '',
  22   page_latest int unsigned NOT NULL,
  23   page_len int unsigned NOT NULL,
  24
  25   PRIMARY KEY page_id (page_id),
  26   UNIQUE INDEX name_title (page_namespace,page_title),
  27   INDEX (page_random),
  28   INDEX (page_len)
  29 );
  30
  31 CREATE TABLE /*$wgDBprefix*/revision (
  32   rev_id int unsigned NOT NULL auto_increment,
  33   rev_page int unsigned NOT NULL,
  34   rev_comment tinyblob NOT NULL,
  35   rev_user int unsigned NOT NULL default '0',
  36   rev_user_text varbinary(255) NOT NULL default '',
  37   rev_timestamp binary(14) NOT NULL default '',
  38   rev_minor_edit tinyint unsigned NOT NULL default '0',
  39   rev_deleted tinyint unsigned NOT NULL default '0',
  40
  41
  42   PRIMARY KEY rev_page_id (rev_page, rev_id),
  43   UNIQUE INDEX rev_id (rev_id),
  44   INDEX rev_timestamp (rev_timestamp),
  45   INDEX page_timestamp (rev_page,rev_timestamp),
  46   INDEX user_timestamp (rev_user,rev_timestamp),
  47   INDEX usertext_timestamp (rev_user_text,rev_timestamp)
  48 );
  49
  50 -- If creating new 'text' table it would look like this:
  51 --
  52 -- CREATE TABLE /*$wgDBprefix*/text (
  53 --   old_id int(8) unsigned NOT NULL auto_increment,
  54 --   old_text mediumtext NOT NULL,
  55 --   old_flags tinyblob NOT NULL,
  56 --
  57 --   PRIMARY KEY old_id (old_id)
  58 -- );
  59
  60
  61 -- Lock!
  62 LOCK TABLES /*$wgDBprefix*/page WRITE, /*$wgDBprefix*/revision WRITE, /*$wgDBprefix*/old WRITE, /*$wgDBprefix*/cur WRITE;
  63
  64 -- Save the last old_id value for later
  65 SELECT (@maxold:=MAX(old_id)) FROM /*$wgDBprefix*/old;
  66
  67 -- First, copy all current entries into the old table.
  68 INSERT
  69   INTO /*$wgDBprefix*/old
  70     (old_namespace,
  71     old_title,
  72     old_text,
  73     old_comment,
  74     old_user,
  75     old_user_text,
  76     old_timestamp,
  77     old_minor_edit,
  78     old_flags)
  79   SELECT
  80     cur_namespace,
  81     cur_title,
  82     cur_text,
  83     cur_comment,
  84     cur_user,
  85     cur_user_text,
  86     cur_timestamp,
  87     cur_minor_edit,
  88     ''
  89   FROM /*$wgDBprefix*/cur;
  90
  91 -- Now, copy all old data except the text into revisions
  92 INSERT
  93   INTO /*$wgDBprefix*/revision
  94     (rev_id,
  95     rev_page,
  96     rev_comment,
  97     rev_user,
  98     rev_user_text,
  99     rev_timestamp,
 100     rev_minor_edit)
 101   SELECT
 102     old_id,
 103     cur_id,
 104     old_comment,
 105     old_user,
 106     old_user_text,
 107     old_timestamp,
 108     old_minor_edit
 109   FROM /*$wgDBprefix*/old,/*$wgDBprefix*/cur
 110   WHERE old_namespace=cur_namespace
 111     AND old_title=cur_title;
 112
 113 -- And, copy the cur data into page
 114 INSERT
 115   INTO /*$wgDBprefix*/page
 116     (page_id,
 117     page_namespace,
 118     page_title,
 119     page_restrictions,
 120     page_counter,
 121     page_is_redirect,
 122     page_is_new,
 123     page_random,
 124     page_touched,
 125     page_latest)
 126   SELECT
 127     cur_id,
 128     cur_namespace,
 129     cur_title,
 130     cur_restrictions,
 131     cur_counter,
 132     cur_is_redirect,
 133     cur_is_new,
 134     cur_random,
 135     cur_touched,
 136     rev_id
 137   FROM /*$wgDBprefix*/cur,/*$wgDBprefix*/revision
 138   WHERE cur_id=rev_page
 139     AND rev_timestamp=cur_timestamp
 140     AND rev_id > @maxold;
 141
 142 UNLOCK TABLES;
 143
 144 -- Keep the old table around as the text store.
 145 -- Its extra fields will be ignored, but trimming them is slow
 146 -- so we won't bother doing it for now.
 147 ALTER TABLE /*$wgDBprefix*/old RENAME TO /*$wgDBprefix*/text;