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