Proposed Database Schema Changes

This text is taken from the wikitech-l mailing list post of 11 Dec 2003.

See Also


 * Database queries on MediaWiki
 * The current schema

-- Change cur/old into page/revisions

-- page_sortkey will contain a munged version of the title for ---            language-specific sorting -- page_current is a foreign key on revisions.rev_id DROP TABLE IF EXISTS page; CREATE TABLE page ( page_id int(8) unsigned NOT NULL auto_increment,  page_namespace tinyint(2) unsigned NOT NULL default '0',  page_title varchar(255) binary NOT NULL default ,  page_sortkey varchar(255) binary NOT NULL default ,  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_random real unsigned NOT NULL,  page_touched char(14) binary NOT NULL default ,  page_current int(8) unsigned NOT NULL default 0,  UNIQUE KEY page_id (page_id),  UNIQUE KEY namespace_title (page_namespace,page_title),  KEY (page_random), );

-- rev_page is a foreign key to page.page_id -- rev_prior will key to another rev_id for the previous revision, to simplify --          making diff links etc DROP TABLE IF EXISTS revisions; CREATE TABLE revisions ( rev_id int(8) unsigned NOT NULL auto_increment,  rev_page int(8) unsigned NOT NULL default 0,  rev_prior int(8) unsigned NOT NULL default 0,  rev_text mediumtext NOT NULL default ,  rev_comment tinyblob NOT NULL default ,  rev_user int(5) unsigned NOT NULL default '0',  rev_user_text varchar(255) binary NOT NULL,  rev_timestamp char(14) binary NOT NULL default '',  rev_minor_edit tinyint(1) NOT NULL default '0',  rev_current tinyint(1) NOT NULL default 0,  UNIQUE KEY rev_id (rev_id),  ADD INDEX (rev_timestamp),  ADD INDEX page_timestamp (rev_page,rev_timestamp),  ADD INDEX user_timestamp (rev_user,rev_timestamp),  ADD INDEX usertext_timestamp (rev_user_text,rev_timestamp); );

-- Import old revisisons from old to revisions INSERT INTO revisions (rev_id,rev_page,rev_text,rev_comment,rev_user,rev_user_text,   rev_timestamp,rev_minor_edit,rev_flags) SELECT old_id,cur_id,old_text,old_comment,old_user,old_user_text, old_timestamp,old_minor_edit,old_flags)   FROM old,cur    WHERE old_namespace=cur_namespace and old_title=cur_title;

-- Import cur revisions from cur to revisions -- This will create new rev_id revisions keys. INSERT INTO revisions (rev_page,rev_text,rev_comment,rev_user,rev_user_text,   rev_timestamp,rev_minor_edit,rev_cur_edit) SELECT cur_id,cur_text,cur_comment,cur_user,cur_user_text, cur_timestamp,cur_minor_edit FROM cur;

-- Import page definitions from cur to page INSERT INTO page (page_id,page_namespace,page_title,page_restrictions,   page_counter,page_is_redirect,page_random,page_touched,    page_current) SELECT cur_id,cur_namespace,cur_title,cur_restrictions, cur_counter,cur_is_redirect,cur_random,cur_touched, rev_id FROM cur,revisions WHERE cur_id=rev_page and rev_is_current;

-- rev_prior and page_sortkey need to be filled out by a php script?