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;