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