Experimental new database schema
From MediaWiki.org
See also Proposed Database Schema Changes which was posted on 11 Dec 2003 on wikitech-l by Brion Vibber.
#########################################
##
## NAMESPACES
##
## Odd namespaces are 'Talk' pages.
##
## 0 = article 1 = article talk
## 2 = Special 3 = Special talk
## 4 = Wikipedia 5 = Wikipedia talk
## 6 = User 7 = User talk
## 8 = Media 9 = Media talk
## (includes Images)
##
#########################################
##
## USER PROPERTIES
##
## USER PROPERTY POSSIBLE VALUES
## newmessages comma-separated list of langcodes
## email e-mail address
## nickname:<langcode> Users can choose a differing nickname for
## each language Wikipedia. Their User page
## and their signatures will be under this
## nickname, but the username must be used to
## log in.
## sysop comma-separated list of langcodes, or "*".
## Has sysop capability for the specified
## language, or all of them, i.e. privilege
## to edit protected pages, ban users, etc.
## translator Capability to edit interface text.
##
#########################################
##
## ARTICLE PROPERTIES
##
## ARTICLE PROPERTY POSSIBLE VALUES
## redirect articleid
## protected 1
## linksto number of links to this page
## length length (size) of this page
## numviews number of times article was viewed
## lastedit datetime stamp of last edit
##
#########################################
------------------------------------------------------------------------------
--- WikiMedia-global tables (will have one such database for all of WikiMedia)
CREATE TABLE users (
userid int NOT NULL auto_increment,
username char(50) NOT NULL,
pwhash char(16) NOT NULL,
PRIMARY KEY (userid),
UNIQUE KEY (username)
);
------------------------------------------------------------------------------
--- Wikipedia database
CREATE TABLE langs (
langid int unsigned NOT NULL auto_increment,
langcode varchar(6) NOT NULL,
langname varchar(255) NOT NULL,
rtl enum('0','1') NOT NULL DEFAULT '0',
PRIMARY KEY (langid),
KEY (langcode)
);
CREATE TABLE interlanglinks (
lang_from int unsigned NOT NULL,
page_from int unsigned NOT NULL,
lang_to int unsigned NOT NULL,
page_to int unsigned NOT NULL,
PRIMARY KEY (lang_from, page_from),
KEY (lang_to, page_to)
);
CREATE TABLE userproplist (
upropid int unsigned NOT NULL auto_increment,
name varchar(50) NOT NULL,
indexed enum('0','1') NOT NULL DEFAULT '1',
PRIMARY KEY (upropid),
UNIQUE KEY (name)
);
CREATE TABLE userprop (
userid int NOT NULL,
upropid int NOT NULL,
value varchar(255) NOT NULL,
PRIMARY KEY (userid, upropid)
);
CREATE TABLE userpropindexed (
userid int NOT NULL,
upropid int NOT NULL,
value varchar(255) NOT NULL,
PRIMARY KEY (userid, upropid),
KEY (upropid, value)
);
CREATE TABLE loggedin (
sessionid int unsigned NOT NULL auto_increment,
userid int unsigned NOT NULL,
authcode char(16) NOT NULL,
expires datetime NOT NULL,
PRIMARY KEY (sessionid)
);
CREATE TABLE bans (
ip char(15) NOT NULL,
bannerid int unsigned NOT NULL,
reason varchar(255) NOT NULL,
timestamp int unsigned NOT NULL,
PRIMARY KEY (ip)
);
CREATE TABLE articles (
articleid int unsigned NOT NULL auto_increment,
curblobid int unsigned NULL,
curblobmd5 char(32) NULL,
langid int unsigned NOT NULL,
namespace int unsigned NOT NULL,
title varchar(255) NOT NULL,
PRIMARY KEY (articleid),
UNIQUE KEY (langid, namespace, title),
KEY (langid),
KEY (namespace)
);
CREATE TABLE articlehistory (
histid int NOT NULL auto_increment,
articleid int NOT NULL,
blobid int NOT NULL,
userid int NOT NULL,
summary varchar(255) NOT NULL DEFAULT '',
timestamp int unsigned NOT NULL,
minor enum('0','1') NOT NULL,
new enum('0','1') NOT NULL,
PRIMARY KEY (histid),
KEY (articleid),
KEY (userid),
KEY (timestamp)
);
CREATE TABLE articleproplist (
apropid int NOT NULL auto_increment,
name varchar(50) NOT NULL,
indexed enum('0','1') NOT NULL DEFAULT '1',
PRIMARY KEY (apropid),
UNIQUE KEY (name)
);
CREATE TABLE articleprop (
articleid int NOT NULL,
apropid int NOT NULL,
value varchar(255) NOT NULL,
PRIMARY KEY (articleid, apropid)
);
CREATE TABLE articlepropindexed (
articleid int NOT NULL,
apropid int NOT NULL,
value varchar(255) NOT NULL,
PRIMARY KEY (articleid, apropid),
KEY (apropid, value)
);
CREATE TABLE links (
toid int unsigned NOT NULL,
fromid int unsigned NOT NULL,
PRIMARY KEY (toid)
);
CREATE TABLE watch (
userid int unsigned NOT NULL,
articleid int unsigned NOT NULL,
PRIMARY KEY (userid)
);
CREATE TABLE blobs (
blobid int unsigned NOT NULL auto_increment,
theblob mediumblob NOT NULL,
PRIMARY KEY (blobid)
);
CREATE TABLE translations (
trid int unsigned NOT NULL auto_increment,
stringid int unsigned NOT NULL,
langid int unsigned NOT NULL,
text varchar(255) NOT NULL,
PRIMARY KEY (trid),
UNIQUE KEY (stringid, langid)
);
CREATE TABLE articlesearchindex (
articleid int unsigned NOT NULL,
namespace int unsigned NOT NULL,
langid int unsigned NOT NULL,
title varchar(255) NOT NULL DEFAULT '',
text mediumtext NOT NULL DEFAULT '',
PRIMARY KEY (articleid),
FULLTEXT INDEX (title),
FULLTEXT INDEX (text)
) TYPE=MyISAM;
