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
 * Experimental new database schema
 * Proposed Database Schema Changes II

Has been subsequently edited.

-- 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 if it is zero, all revisions are marked deleted 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 -- rev_deletion is a foreign key to deletions.del_id. If it is nonzero, the --             revision has been deleted -- rev_namespace, rev_title should never be used as keys! they store the _old_ --                         title after a page is renamed, for later display. 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_deletion int(8) unsigned NOT NULL   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,  rev_flags tinyblob NOT NULL default ,  rev_namespace tinyint(2) unsigned NOT NULL default '0',  rev_title varchar(255) binary NOT NULL default '',  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); );

DROP TABLE IF EXISTS deletions; CREATE TABLE deletions ( del_id int(8) unsigned NOT NULL auto_increment,  del_page int(8) unsigned NOT NULL default 0,  del_user int(5) unsigned NOT NULL default '0',  del_user_text varchar(255) binary NOT NULL,  del_comment tinyblob NOT NULL default ,  del_timestamp char(14) binary NOT NULL default ,  UNIQUE KEY del_id (del_id),  ADD INDEX (del_timestamp),  ADD INDEX page_timestamp (del_page,del_timestamp),  ADD INDEX user_timestamp (del_user,del_timestamp),  ADD INDEX usertext_timestamp (del_user_text,del_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?

---

Following are table definitions. To do: conversion

DROP TABLE IF EXISTS recentchanges; CREATE TABLE recentchanges ( rc_timestamp varchar(14) binary NOT NULL default ,  rc_cur_time varchar(14) binary NOT NULL default ,  rc_user int(10) unsigned NOT NULL default '0',  rc_user_text varchar(255) binary NOT NULL default ,  rc_namespace tinyint(3) unsigned NOT NULL default '0',  rc_title varchar(255) binary NOT NULL default ,  rc_comment varchar(255) binary NOT NULL default '',  rc_minor tinyint(3) unsigned NOT NULL default '0',  rc_bot tinyint(3) unsigned NOT NULL default '0',  rc_new tinyint(3) unsigned NOT NULL default '0',  rc_page int(10) unsigned NOT NULL default '0',  rc_this_revision int(10) unsigned NOT NULL default '0',  rc_last_revision int(10) unsigned NOT NULL default '0' ) TYPE=MyISAM PACK_KEYS=1;