1-- SQL to create the initial tables for the MediaWiki database. 2-- This is read and executed by the install script; you should 3-- not have to run it by itself unless doing a manual install. 4 5-- This is a shared schema file used for both MySQL and SQLite installs. 6-- 7-- For more documentation on the database schema, see 8-- https://www.mediawiki.org/wiki/Manual:Database_layout 9-- 10-- General notes: 11-- 12-- If possible, create tables as InnoDB to benefit from the 13-- superior resiliency against crashes and ability to read 14-- during writes (and write during reads!) 15-- 16-- Only the 'searchindex' table requires MyISAM due to the 17-- requirement for fulltext index support, which is missing 18-- from InnoDB. 19-- 20-- 21-- The MySQL table backend for MediaWiki currently uses 22-- 14-character BINARY or VARBINARY fields to store timestamps. 23-- The format is YYYYMMDDHHMMSS, which is derived from the 24-- text format of MySQL's TIMESTAMP fields. 25-- 26-- Historically TIMESTAMP fields were used, but abandoned 27-- in early 2002 after a lot of trouble with the fields 28-- auto-updating. 29-- 30-- The Postgres backend uses TIMESTAMPTZ fields for timestamps, 31-- and we will migrate the MySQL definitions at some point as 32-- well. 33-- 34-- 35-- The /*_*/ comments in this and other files are 36-- replaced with the defined table prefix by the installer 37-- and updater scripts. If you are installing or running 38-- updates manually, you will need to manually insert the 39-- table prefix if any when running these scripts. 40-- 41 42 43-- 44-- The user table contains basic account information, 45-- authentication keys, etc. 46-- 47-- Some multi-wiki sites may share a single central user table 48-- between separate wikis using the $wgSharedDB setting. 49-- 50-- Note that when an external authentication plugin is used, 51-- user table entries still need to be created to store 52-- preferences and to key tracking information in the other 53-- tables. 54-- 55CREATE TABLE /*_*/user ( 56 user_id int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT, 57 58 -- Usernames must be unique, must not be in the form of 59 -- an IP address. _Shouldn't_ allow slashes or case 60 -- conflicts. Spaces are allowed, and are _not_ converted 61 -- to underscores like titles. See the User::newFromName() for 62 -- the specific tests that usernames have to pass. 63 user_name varbinary(255) NOT NULL default '', 64 65 -- Optional 'real name' to be displayed in credit listings 66 user_real_name varbinary(255) NOT NULL default '', 67 68 -- Password hashes, see User::crypt() and User::comparePasswords() 69 -- in User.php for the algorithm 70 user_password tinyblob NOT NULL, 71 72 -- When using 'mail me a new password', a random 73 -- password is generated and the hash stored here. 74 -- The previous password is left in place until 75 -- someone actually logs in with the new password, 76 -- at which point the hash is moved to user_password 77 -- and the old password is invalidated. 78 user_newpassword tinyblob NOT NULL, 79 80 -- Timestamp of the last time when a new password was 81 -- sent, for throttling and expiring purposes 82 -- Emailed passwords will expire $wgNewPasswordExpiry 83 -- (a week) after being set. If user_newpass_time is NULL 84 -- (eg. created by mail) it doesn't expire. 85 user_newpass_time binary(14), 86 87 -- Note: email should be restricted, not public info. 88 -- Same with passwords. 89 user_email tinytext NOT NULL, 90 91 -- If the browser sends an If-Modified-Since header, a 304 response is 92 -- suppressed if the value in this field for the current user is later than 93 -- the value in the IMS header. That is, this field is an invalidation timestamp 94 -- for the browser cache of logged-in users. Among other things, it is used 95 -- to prevent pages generated for a previously logged in user from being 96 -- displayed after a session expiry followed by a fresh login. 97 user_touched binary(14) NOT NULL, 98 99 -- A pseudorandomly generated value that is stored in 100 -- a cookie when the "remember password" feature is 101 -- used (previously, a hash of the password was used, but 102 -- this was vulnerable to cookie-stealing attacks) 103 user_token binary(32) NOT NULL default '', 104 105 -- Initially NULL; when a user's e-mail address has been 106 -- validated by returning with a mailed token, this is 107 -- set to the current timestamp. 108 user_email_authenticated binary(14), 109 110 -- Randomly generated token created when the e-mail address 111 -- is set and a confirmation test mail sent. 112 user_email_token binary(32), 113 114 -- Expiration date for the user_email_token 115 user_email_token_expires binary(14), 116 117 -- Timestamp of account registration. 118 -- Accounts predating this schema addition may contain NULL. 119 user_registration binary(14), 120 121 -- Count of edits and edit-like actions. 122 -- 123 -- *NOT* intended to be an accurate copy of COUNT(*) WHERE rev_actor refers to a user's actor_id 124 -- May contain NULL for old accounts if batch-update scripts haven't been 125 -- run, as well as listing deleted edits and other myriad ways it could be 126 -- out of sync. 127 -- 128 -- Meant primarily for heuristic checks to give an impression of whether 129 -- the account has been used much. 130 -- 131 user_editcount int, 132 133 -- Expiration date for user password. 134 user_password_expires varbinary(14) DEFAULT NULL 135 136) /*$wgDBTableOptions*/; 137 138CREATE UNIQUE INDEX /*i*/user_name ON /*_*/user (user_name); 139CREATE INDEX /*i*/user_email_token ON /*_*/user (user_email_token); 140CREATE INDEX /*i*/user_email ON /*_*/user (user_email(50)); 141 142-- 143-- Every edit of a page creates also a revision row. 144-- This stores metadata about the revision, and a reference 145-- to the text storage backend. 146-- 147CREATE TABLE /*_*/revision ( 148 -- Unique ID to identify each revision 149 rev_id int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT, 150 151 -- Key to page_id. This should _never_ be invalid. 152 rev_page int unsigned NOT NULL, 153 154 -- Key to comment.comment_id. Comment summarizing the change. 155 rev_comment_id bigint unsigned NOT NULL default 0, 156 157 -- Key to actor.actor_id of the user or IP who made this edit. 158 rev_actor bigint unsigned NOT NULL default 0, 159 160 -- Timestamp of when revision was created 161 rev_timestamp binary(14) NOT NULL default '', 162 163 -- Records whether the user marked the 'minor edit' checkbox. 164 -- Many automated edits are marked as minor. 165 rev_minor_edit tinyint unsigned NOT NULL default 0, 166 167 -- Restrictions on who can access this revision 168 rev_deleted tinyint unsigned NOT NULL default 0, 169 170 -- Length of this revision in bytes 171 rev_len int unsigned, 172 173 -- Key to revision.rev_id 174 -- This field is used to add support for a tree structure (The Adjacency List Model) 175 rev_parent_id int unsigned default NULL, 176 177 -- SHA-1 text content hash in base-36 178 rev_sha1 varbinary(32) NOT NULL default '' 179) /*$wgDBTableOptions*/ MAX_ROWS=10000000 AVG_ROW_LENGTH=1024; 180-- In case tables are created as MyISAM, use row hints for MySQL <5.0 to avoid 4GB limit 181 182-- The index is proposed for removal, do not use it in new code: T163532. 183-- Used for ordering revisions within a page by rev_id, which is usually 184-- incorrect, since rev_timestamp is normally the correct order. It can also 185-- be used by dumpBackup.php, if a page and rev_id range is specified. 186CREATE INDEX /*i*/rev_page_id ON /*_*/revision (rev_page, rev_id); 187 188-- Used by ApiQueryAllRevisions 189CREATE INDEX /*i*/rev_timestamp ON /*_*/revision (rev_timestamp); 190 191-- History index 192CREATE INDEX /*i*/page_timestamp ON /*_*/revision (rev_page,rev_timestamp); 193 194-- User contributions index 195CREATE INDEX /*i*/rev_actor_timestamp ON /*_*/revision (rev_actor,rev_timestamp,rev_id); 196 197-- Credits index. This is scanned in order to compile credits lists for pages, 198-- in ApiQueryContributors. Also for ApiQueryRevisions if rvuser is specified. 199CREATE INDEX /*i*/rev_page_actor_timestamp ON /*_*/revision (rev_page,rev_actor,rev_timestamp); 200 201 202-- 203-- When using the default MySQL search backend, page titles 204-- and text are munged to strip markup, do Unicode case folding, 205-- and prepare the result for MySQL's fulltext index. 206-- 207-- This table must be MyISAM; InnoDB does not support the needed 208-- fulltext index. 209-- 210CREATE TABLE /*_*/searchindex ( 211 -- Key to page_id 212 si_page int unsigned NOT NULL, 213 214 -- Munged version of title 215 si_title varchar(255) NOT NULL default '', 216 217 -- Munged version of body text 218 si_text mediumtext NOT NULL 219) ENGINE=MyISAM DEFAULT CHARSET=utf8; 220 221CREATE UNIQUE INDEX /*i*/si_page ON /*_*/searchindex (si_page); 222CREATE FULLTEXT INDEX /*i*/si_title ON /*_*/searchindex (si_title); 223CREATE FULLTEXT INDEX /*i*/si_text ON /*_*/searchindex (si_text); 224 225-- vim: sw=2 sts=2 et 226