Proposed Database Schema Changes/August 2006

-- 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;