summaryrefslogtreecommitdiff
path: root/maintenance/archives/patch-restructure.sql
diff options
context:
space:
mode:
Diffstat (limited to 'maintenance/archives/patch-restructure.sql')
-rw-r--r--maintenance/archives/patch-restructure.sql147
1 files changed, 147 insertions, 0 deletions
diff --git a/maintenance/archives/patch-restructure.sql b/maintenance/archives/patch-restructure.sql
new file mode 100644
index 00000000..53f1836b
--- /dev/null
+++ b/maintenance/archives/patch-restructure.sql
@@ -0,0 +1,147 @@
+-- The Great Restructuring of October 2004
+-- Creates 'page', 'revision' tables and transforms the classic
+-- cur+old into a separate page+revision+text structure.
+--
+-- The pre-conversion 'old' table is renamed to 'text' and used
+-- without internal restructuring to avoid rebuilding the entire
+-- table. (This can be done separately if desired.)
+--
+-- The pre-conversion 'cur' table is now redundant and can be
+-- discarded when done.
+
+CREATE TABLE /*$wgDBprefix*/page (
+ page_id int(8) unsigned NOT NULL auto_increment,
+ page_namespace tinyint NOT NULL,
+ page_title varchar(255) binary NOT NULL,
+ page_restrictions tinyblob NOT NULL default '',
+ page_counter bigint(20) unsigned NOT NULL default '0',
+ page_is_redirect tinyint(1) unsigned NOT NULL default '0',
+ page_is_new tinyint(1) unsigned NOT NULL default '0',
+ page_random real unsigned NOT NULL,
+ page_touched char(14) binary NOT NULL default '',
+ page_latest int(8) unsigned NOT NULL,
+ page_len int(8) unsigned NOT NULL,
+
+ PRIMARY KEY page_id (page_id),
+ UNIQUE INDEX name_title (page_namespace,page_title),
+ INDEX (page_random),
+ INDEX (page_len)
+);
+
+CREATE TABLE /*$wgDBprefix*/revision (
+ rev_id int(8) unsigned NOT NULL auto_increment,
+ rev_page int(8) unsigned NOT NULL,
+ rev_comment tinyblob NOT NULL default '',
+ rev_user int(5) unsigned NOT NULL default '0',
+ rev_user_text varchar(255) binary NOT NULL default '',
+ rev_timestamp char(14) binary NOT NULL default '',
+ rev_minor_edit tinyint(1) unsigned NOT NULL default '0',
+ rev_deleted tinyint(1) unsigned NOT NULL default '0',
+
+
+ PRIMARY KEY rev_page_id (rev_page, rev_id),
+ UNIQUE INDEX rev_id (rev_id),
+ INDEX rev_timestamp (rev_timestamp),
+ INDEX page_timestamp (rev_page,rev_timestamp),
+ INDEX user_timestamp (rev_user,rev_timestamp),
+ INDEX usertext_timestamp (rev_user_text,rev_timestamp)
+);
+
+-- If creating new 'text' table it would look like this:
+--
+-- CREATE TABLE /*$wgDBprefix*/text (
+-- old_id int(8) unsigned NOT NULL auto_increment,
+-- old_text mediumtext NOT NULL default '',
+-- old_flags tinyblob NOT NULL default '',
+--
+-- PRIMARY KEY old_id (old_id)
+-- );
+
+
+-- Lock!
+LOCK TABLES /*$wgDBprefix*/page WRITE, /*$wgDBprefix*/revision WRITE, /*$wgDBprefix*/old WRITE, /*$wgDBprefix*/cur WRITE;
+
+-- Save the last old_id value for later
+SELECT (@maxold:=MAX(old_id)) FROM /*$wgDBprefix*/old;
+
+-- First, copy all current entries into the old table.
+INSERT
+ INTO /*$wgDBprefix*/old
+ (old_namespace,
+ old_title,
+ old_text,
+ old_comment,
+ old_user,
+ old_user_text,
+ old_timestamp,
+ old_minor_edit,
+ old_flags)
+ SELECT
+ cur_namespace,
+ cur_title,
+ cur_text,
+ cur_comment,
+ cur_user,
+ cur_user_text,
+ cur_timestamp,
+ cur_minor_edit,
+ ''
+ FROM /*$wgDBprefix*/cur;
+
+-- Now, copy all old data except the text into revisions
+INSERT
+ INTO /*$wgDBprefix*/revision
+ (rev_id,
+ rev_page,
+ rev_comment,
+ rev_user,
+ rev_user_text,
+ rev_timestamp,
+ rev_minor_edit)
+ SELECT
+ old_id,
+ cur_id,
+ old_comment,
+ old_user,
+ old_user_text,
+ old_timestamp,
+ old_minor_edit
+ FROM /*$wgDBprefix*/old,/*$wgDBprefix*/cur
+ WHERE old_namespace=cur_namespace
+ AND old_title=cur_title;
+
+-- And, copy the cur data into page
+INSERT
+ INTO /*$wgDBprefix*/page
+ (page_id,
+ page_namespace,
+ page_title,
+ page_restrictions,
+ page_counter,
+ page_is_redirect,
+ page_is_new,
+ page_random,
+ page_touched,
+ page_latest)
+ SELECT
+ cur_id,
+ cur_namespace,
+ cur_title,
+ cur_restrictions,
+ cur_counter,
+ cur_is_redirect,
+ cur_is_new,
+ cur_random,
+ cur_touched,
+ rev_id
+ FROM /*$wgDBprefix*/cur,/*$wgDBprefix*/revision
+ WHERE cur_id=rev_page
+ AND rev_timestamp=cur_timestamp
+ AND rev_id > @maxold;
+
+UNLOCK TABLES;
+
+-- Keep the old table around as the text store.
+-- Its extra fields will be ignored, but trimming them is slow
+-- so we won't bother doing it for now.
+ALTER TABLE /*$wgDBprefix*/old RENAME TO /*$wgDBprefix*/text;