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.
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.)
9 -- The pre-conversion 'cur' table is now redundant and can be
10 -- discarded when done.
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,
24 PRIMARY KEY page_id (page_id),
25 UNIQUE INDEX name_title (page_namespace,page_title),
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 rev_deleted tinyint(1) unsigned NOT NULL default '0',
40 PRIMARY KEY rev_page_id (rev_page, rev_id),
41 UNIQUE INDEX rev_id (rev_id),
42 INDEX rev_timestamp (rev_timestamp),
43 INDEX page_timestamp (rev_page,rev_timestamp),
44 INDEX user_timestamp (rev_user,rev_timestamp),
45 INDEX usertext_timestamp (rev_user_text,rev_timestamp)
48 -- If creating new 'text' table it would look like this:
50 -- CREATE TABLE /*$wgDBprefix*/text (
51 -- old_id int(8) unsigned NOT NULL auto_increment,
52 -- old_text mediumtext NOT NULL default '',
53 -- old_flags tinyblob NOT NULL default '',
55 -- PRIMARY KEY old_id (old_id)
60 LOCK TABLES /*$wgDBprefix*/page WRITE, /*$wgDBprefix*/revision WRITE, /*$wgDBprefix*/old WRITE, /*$wgDBprefix*/cur WRITE;
62 -- Save the last old_id value for later
63 SELECT (@maxold:=MAX(old_id)) FROM /*$wgDBprefix*/old;
65 -- First, copy all current entries into the old table.
67 INTO /*$wgDBprefix*/old
87 FROM /*$wgDBprefix*/cur;
89 -- Now, copy all old data except the text into revisions
91 INTO /*$wgDBprefix*/revision
107 FROM /*$wgDBprefix*/old,/*$wgDBprefix*/cur
108 WHERE old_namespace=cur_namespace
109 AND old_title=cur_title;
111 -- And, copy the cur data into page
113 INTO /*$wgDBprefix*/page
135 FROM /*$wgDBprefix*/cur,/*$wgDBprefix*/revision
136 WHERE cur_id=rev_page
137 AND rev_timestamp=cur_timestamp
138 AND rev_id > @maxold;
142 -- Keep the old table around as the text store.
143 -- Its extra fields will be ignored, but trimming them is slow
144 -- so we won't bother doing it for now.
145 ALTER TABLE /*$wgDBprefix*/old RENAME TO /*$wgDBprefix*/text;