From: Brion Vibber Date: Mon, 2 May 2005 08:07:38 +0000 (+0000) Subject: Move schema documentation to inline comments in tables.sql, X-Git-Tag: 1.5.0alpha1~25 X-Git-Url: http://git.heureux-cyclage.org/?p=lhc%2Fweb%2Fwiklou.git;a=commitdiff_plain;h=5fdbef206f35adeb11ee0b97894f8fdb5c451a0e Move schema documentation to inline comments in tables.sql, where it's less likely to be left years out of date. --- diff --git a/docs/schema.txt b/docs/schema.txt index cec9caa6f4..f73484629e 100644 --- a/docs/schema.txt +++ b/docs/schema.txt @@ -1,286 +1,6 @@ -SCHEMA.DOC - The most up-to-date schema for the tables in the database -should always be "tables.sql" in the maintenance directory, -which is called from the installation script. Here are a -few highlights that may be out of date: - -user (MediaWiki users) - - user_id - integer, primary key, autoincrement - user_name - Usernames must be unique, must not be in the form of - an IP address. _Shouldn't_ allow slashes or case - conflicts. Spaces are allowed, and are _not_ converted - to underscores like titles. (Conflicts?) - user_password - Hash of current password. - user_newpassword - Generated for mail-a-new-password feature - user_email - Note -- email should be restricted, not public info. - Same with passwords. ;) - user_options - Newline-separated list of name=value pairs. - user_token - A pseudorandomly generated value that is stored in - a cookie when the "remember password" feature is - used (previously, a hash of the password was used, but - this was vulnerable to cookie-stealing attacks) - - - -cur (MediaWiki "current" articles) - - cur_id - integer, primary key, autoincrement - cur_namespace - integer index into list of namespaces. See the - Namespace class for more details. - cur_title - Title of article (in dbkey form--see Title), without - namespace. The combination of namespace,title should - be unique in this table. - cur_text - Wikitext of the article. - cur_comment - The summary of the last change. - cur_user - User id who made the last change, or 0 if unknown. - cur_user_text - Name of the user above, or IP address. - cur_timestamp - Time of the last change. - cur_minor_edit - Flag: 0 or 1 is last change was a "minor" edit. - cur_restrictions - Who may or may not edit the article. - cur_counter - Number of times this page has been viewed. - cur_ind_title - Text version of title for fulltext searches. - cur_ind_text - Plaintext version of text for fulltext searches. - cur_is_redirect - 1 indicates the article is a redirect. - cur_minor_edit - 1 indicates this was a minor edit. - cur_is_new - 1 indicates this is the first revision of a new entry. - cur_random - Random value between 0 and 1, used for - Special:Randompage - - - -old (Historical versions articles. Most fields - correspond to the same fields in "cur") - - old_id - old_namespace - old_title - old_text - old_comment - old_user - old_user_text - old_timestamp - old_minor_edit - old_flags - This last is currently unused. - - - -archive (Temporary storage of deleted articles which may be restored. - Fields correspond to those of "cur" and "old") - ar_namespace - ar_title - ar_text - ar_comment - ar_user - ar_user_text - ar_timestamp - ar_minor_edit - ar_flags - This last is currently unused. - - - -links (Internal links to existing articles) - - l_from - ID of source article. (currently title, may be changed) - l_to - ID of target article. - - - -brokenlinks (Internal links to non-existent articles) - - bl_from - ID of source link. - bl_to - Title of target link. - - - -imagelinks (Internal links to images via [[Image:filename]] syntax) - - il_from - Title of target article. - il_to - Filename of target image. - - - -categorylinks (Track category inclusions) - - cl_from - corresponds to cur_id of the linking page - cl_to - corresponds to cur_title of the category page - cl_sortkey - the title of the linking page, or an optional override - cl_timestampe - when the link was last added - - - -linkscc (Stores (possibly gzipped) serialized objects with - cache arrays to reduce database load slurping up - from links and brokenlinks.) - - lcc_pageid - The ID of the linking page - lcc_cacheobj - A serialized LinkCache object - - - -image (Uploaded images and other files) - - img_name - Filename. - img_size - File size in bytes. - img_description - Description field given during upload. - img_user - User ID who uploaded the file. - img_user_text - User name who uploaded the file. - img_timestamp - Timestamp when upload took place. - - - -oldimage (Old versions of images stored for potential revert) - - oi_name - Original filename. - oi_archive_name - Filename of stored old revision; timestamp and - exclaimation point prepended to oi_name - oi_size - File size in bytes. - oi_description - Description field given during upload. - oi_user - User ID who uploaded the file. - oi_user_text - User name who uploaded the file. - oi_timestamp - Timestamp when upload took place. - - - -ipblocks (IP addresses and users blocked from editing) - ipb_id - Primary key, introduced for privacy. - ipb_address - Blocked IP address in dotted-quad form or user name. - ipb_user - Blocked user ID or 0 for IP blocks. - ipb_by - User ID who made the block. - ipb_reason - Text comment made by blocker. - ipb_timestamp - Creation (or refresh) date in standard YMDHMS form. IP - blocks expire automatically. - ipb_auto - Indicates that the IP address was banned because a banned - user accessed a page through it. If this is 1, ipb_address - will be hidden. - - -site_stats (Site-wide statistics) - - ss_row_id - Token for where clauses. There's only one row in - this table. At some point we might want to use a - date here so we can get stats-by-date. - ss_total_views - Number of total views of all pages. - ss_total_edits - Number of total page edits. - ss_good_articles - Number of "countable" articles. - - - -hitcounter (Stores an ID for every time any article is visited; - depending on $wgHitcounterUpdateFreq, it is - periodically cleared and the cur_counter column - in the cur table updated for the all articles - that have been visited.) - hc_id - The ID of an article, representing one hit - - - -recentchanges - - (Will document further when working) - - - -watchlist - - wl_user - Foreign key -> user_id - wl_namespace - Namespace -> cur_namespace - Note that these should only include even-numbered - namespaces for regular pages; associated talk pages - (odd numbered namespaces) are folded in. - wl_title - Page title -> cur_title - Note also that the linked page may not exist in page - or talk namespace, or at all. - - -searchindex (Used for MySQL fulltext searching) - - si_page - The ID of an article - si_title - The title of an article, indexed for searching - si_text - The text of an article, indexed for searching - - +will always be "tables.sql" in the maintenance directory, +which is called from the installation script. -interwiki (Recognized interwiki link prefixes) - iw_prefix - The interwiki prefix, (e.g. "Meatball", or the - language prefix "de") - iw_url - The URL of the wiki, with "$1" as a placeholder - for an article name - iw_local - A boolean value indicating whether the wiki is - in this project (used, for example, to detect - redirect loops) - - +That file has been commented with details of the usage for +each table and field. diff --git a/maintenance/tables.sql b/maintenance/tables.sql index 0d5da0deb2..b239619e33 100644 --- a/maintenance/tables.sql +++ b/maintenance/tables.sql @@ -1,22 +1,106 @@ --- SQL to create the initial tables for the Wikipedia database. +-- SQL to create the initial tables for the MediaWiki database. -- This is read and executed by the install script; you should --- never have to run it by itself. +-- not have to run it by itself unless doing a manual install. + +-- +-- General notes: +-- +-- If possible, create tables as InnoDB to benefit from the +-- superior resiliency against crashes and ability to read +-- during writes (and write during reads!) +-- +-- Only the 'searchindex' table requires MyISAM due to the +-- requirement for fulltext index support, which is missing +-- from InnoDB. +-- +-- +-- The MySQL table backend for MediaWiki currently uses +-- 14-character CHAR or VARCHAR fields to store timestamps. +-- The format is YYYYMMDDHHMMSS, which is derived from the +-- text format of MySQL's TIMESTAMP fields. +-- +-- Historically TIMESTAMP fields were used, but abandoned +-- in early 2002 after a lot of trouble with the fields +-- auto-updating. +-- +-- The PostgreSQL backend uses DATETIME fields for timestamps, +-- and we will migrate the MySQL definitions at some point as +-- well. +-- +-- +-- The /*$wgDBprefix*/ comments in this and other files are +-- replaced with the defined table prefix by the installer +-- and updater scripts. If you are installing or running +-- updates manually, you will need to manually insert the +-- table prefix if any when running these scripts. -- --- Indexes should be defined here; please import the rest from indexes.sql. + +-- +-- The user table contains basic account information, +-- authentication keys, etc. +-- +-- Some multi-wiki sites may share a single central user table +-- between separate wikis using the $wgSharedDB setting. +-- +-- Note that when a external authentication plugin is used, +-- user table entries still need to be created to store +-- preferences and to key tracking information in the other +-- tables. +-- CREATE TABLE /*$wgDBprefix*/user ( user_id int(5) unsigned NOT NULL auto_increment, + + -- Usernames must be unique, must not be in the form of + -- an IP address. _Shouldn't_ allow slashes or case + -- conflicts. Spaces are allowed, and are _not_ converted + -- to underscores like titles. (Conflicts?) user_name varchar(255) binary NOT NULL default '', + + -- Optional 'real name' to be displayed in credit listings user_real_name varchar(255) binary NOT NULL default '', + + -- Password hashes, normally hashed like so: + -- MD5(CONCAT(user_id,'-',MD5(plaintext_password))) user_password tinyblob NOT NULL default '', + + -- When using 'mail me a new password', a random + -- password is generated and the hash stored here. + -- The previous password is left in place until + -- someone actually logs in with the new password, + -- at which point the hash is moved to user_password + -- and the old password is invalidated. user_newpassword tinyblob NOT NULL default '', + + -- Note: email should be restricted, not public info. + -- Same with passwords. ;) user_email tinytext NOT NULL default '', - user_emailauthenticationtimestamp varchar(14) binary NOT NULL default '0', + + -- Newline-separated list of name=value pairs. user_options blob NOT NULL default '', + + -- This is a timestamp which is updated when a user + -- logs in, logs out, changes preferences, or performs + -- some other action requiring HTML cache invalidation + -- to ensure that the UI is updated. user_touched char(14) binary NOT NULL default '', + + -- A pseudorandomly generated value that is stored in + -- a cookie when the "remember password" feature is + -- used (previously, a hash of the password was used, but + -- this was vulnerable to cookie-stealing attacks) user_token char(32) binary NOT NULL default '', + + -- Initially NULL; when a user's e-mail address has been + -- validated by returning with a mailed token, this is + -- set to the current timestamp. user_email_authenticated CHAR(14) BINARY, + + -- Randomly generated token created when the e-mail address + -- is set and a confirmation test mail sent. user_email_token CHAR(32) BINARY, + + -- Expiration date for the user_email_token_expires CHAR(14) BINARY, PRIMARY KEY user_id (user_id), @@ -24,10 +108,20 @@ CREATE TABLE /*$wgDBprefix*/user ( INDEX (user_email_token) ); +-- +-- User permissions have been broken out to a separate table; +-- this allows sites with a shared user table to have different +-- permissions assigned to a user in each project. +-- -- TODO: de-blob this; it should be a property table +-- CREATE TABLE /*$wgDBprefix*/user_rights ( + -- Key to user_id ur_user int(5) unsigned NOT NULL, + + -- Comma-separated list of permission keys ur_rights tinyblob NOT NULL default '', + UNIQUE KEY ur_user (ur_user) ); @@ -40,22 +134,56 @@ CREATE TABLE /*$wgDBprefix*/user_rights ( -- INDEX user_ip (user_ip) -- ); + +-- +-- Core of the wiki: each page has an entry here which identifies +-- it by title and contains some essential metadata. +-- CREATE TABLE /*$wgDBprefix*/page ( - -- Identifiers: + -- Unique identifier number. The page_id will be preserved across + -- edits and rename operations, but not deletions and recreations. page_id int(8) unsigned NOT NULL auto_increment, + + -- A page name is broken into a namespace and a title. + -- The namespace keys are UI-language-independent constants, + -- defined in Namespace.php. page_namespace tinyint NOT NULL, + + -- The rest of the title, as text. + -- Spaces are transformed into underscores in title storage. page_title varchar(255) binary NOT NULL, - -- Mutable information + -- Comma-separated set of permission keys indicating who + -- can move or edit the page. page_restrictions tinyblob NOT NULL default '', + + -- Number of times this page has been viewed. page_counter bigint(20) unsigned NOT NULL default '0', + + -- 1 indicates the article is a redirect. page_is_redirect tinyint(1) unsigned NOT NULL default '0', + + -- 1 indicates this is a new entry, with only one edit. + -- Not all pages with one edit are new pages. page_is_new tinyint(1) unsigned NOT NULL default '0', + + -- Random value between 0 and 1, used for Special:Randompage page_random real unsigned NOT NULL, + + -- This timestamp is updated whenever the page changes in + -- a way requiring it to be re-rendered, invalidating caches. + -- On top of editing this includes permission changes, + -- creation or deletion of linked pages, and alteration + -- of contained templates. page_touched char(14) binary NOT NULL default '', - -- Handy key to revision.rev_id of the current revision + + -- Handy key to revision.rev_id of the current revision. + -- This may be 0 during page creation, but that shouldn't + -- happen outside of a transaction... hopefully. page_latest int(8) unsigned NOT NULL, + + -- Uncompressed length in bytes of the page's current source text. page_len int(8) unsigned NOT NULL, PRIMARY KEY page_id (page_id), @@ -66,15 +194,43 @@ CREATE TABLE /*$wgDBprefix*/page ( INDEX (page_len) ); +-- +-- Every edit of a page creates also a revision row. +-- This stores metadata about the revision, and a reference +-- to the text storage backend. +-- CREATE TABLE /*$wgDBprefix*/revision ( rev_id int(8) unsigned NOT NULL auto_increment, + + -- Key to page_id. This should _never_ be invalid. rev_page int(8) unsigned NOT NULL, + + -- Key to text.old_id, where the actual bulk text is stored. + -- It's possible for multiple revisions to use the same text, + -- for instance revisions where only metadata is altered + -- or a rollback to a previous version. rev_text_id int(8) unsigned NOT NULL, + + -- Text comment summarizing the change. + -- This text is shown in the history and other changes lists, + -- rendered in a subset of wiki markup. rev_comment tinyblob NOT NULL default '', + + -- Key to user_id of the user who made this edit. + -- Stores 0 for anonymous edits and for some mass imports. rev_user int(5) unsigned NOT NULL default '0', + + -- Text username or IP address of the editor. rev_user_text varchar(255) binary NOT NULL default '', + + -- Timestamp rev_timestamp char(14) binary NOT NULL default '', + + -- Records whether the user marked the 'minor edit' checkbox. + -- Many automated edits are marked as minor. rev_minor_edit tinyint(1) unsigned NOT NULL default '0', + + -- Not yet used; reserved for future changes to the deletion system. rev_deleted tinyint(1) unsigned NOT NULL default '0', PRIMARY KEY rev_page_id (rev_page, rev_id), @@ -89,48 +245,117 @@ CREATE TABLE /*$wgDBprefix*/revision ( -- -- Holds text of individual page revisions. -- +-- Field names are a holdover from the 'old' revisions table in +-- MediaWiki 1.4 and earlier: an upgrade will transform that +-- table into the 'text' table to minimize unnecessary churning +-- and downtime. If upgrading, the other fields will be left unused. +-- CREATE TABLE /*$wgDBprefix*/text ( + -- Unique text storage key number. + -- Note that the 'oldid' parameter used in URLs does *not* + -- refer to this number anymore, but to rev_id. old_id int(8) unsigned NOT NULL auto_increment, + + -- Depending on the contents of the old_flags field, the text + -- may be convenient plain text, or it may be funkily encoded. old_text mediumtext NOT NULL default '', + + -- Comma-separated list of flags: + -- gzip: text is compressed with PHP's gzdeflate() function. + -- utf8: text was stored as UTF-8. + -- If $wgLegacyEncoding option is on, rows *without* this flag + -- will be converted to UTF-8 transparently at load time. + -- object: text field contained a serialized PHP object. + -- The object either contains multiple versions compressed + -- together to achieve a better compression ratio, or it refers + -- to another row where the text can be found. old_flags tinyblob NOT NULL default '', PRIMARY KEY old_id (old_id) ); +-- +-- Holding area for deleted articles, which may be viewed +-- or restored by admins through the Special:Undelete interface. +-- The fields generally correspond to the page, revision, and text +-- fields, with several caveats. +-- CREATE TABLE /*$wgDBprefix*/archive ( ar_namespace tinyint(2) unsigned NOT NULL default '0', ar_title varchar(255) binary NOT NULL default '', + + -- Newly deleted pages will not store text in this table, + -- but will reference the separately existing text rows. + -- This field is retained for backwards compatibility, + -- so old archived pages will remain accessible after + -- upgrading from 1.4 to 1.5. ar_text mediumtext NOT NULL default '', + + -- Basic revision stuff... ar_comment tinyblob NOT NULL default '', ar_user int(5) unsigned NOT NULL default '0', ar_user_text varchar(255) binary NOT NULL, ar_timestamp char(14) binary NOT NULL default '', ar_minor_edit tinyint(1) NOT NULL default '0', + + -- See ar_text note. ar_flags tinyblob NOT NULL default '', + + -- When revisions are deleted, their unique rev_id is stored + -- here so it can be retained after undeletion. This is necessary + -- to retain permalinks to given revisions after accidental delete + -- cycles or messy operations like history merges. + -- + -- Old entries from 1.4 will be NULL here, and a new rev_id will + -- be created on undeletion for those revisions. ar_rev_id int(8) unsigned, + + -- For newly deleted revisions, this is the text.old_id key to the + -- actual stored text. To avoid breaking the block-compression scheme + -- and otherwise making storage changes harder, the actual text is + -- *not* deleted from the text table, merely hidden by removal of the + -- page and revision entries. + -- + -- Old entries deleted under 1.2-1.4 will have NULL here, and their + -- ar_text and ar_flags fields will be used to create a new text + -- row upon undeletion. ar_text_id int(8) unsigned, KEY name_title_timestamp (ar_namespace,ar_title,ar_timestamp) ); -- --- Track links that do exist --- l_from and l_to key to cur_id +-- Track links within the wiki that do exist. +-- These rows must be removed when the target page is +-- deleted, and replaced with brokenlinks entries. +-- They must also be updated if a target page is renamed. -- CREATE TABLE /*$wgDBprefix*/links ( + -- Key to the page_id of the page containing the link. l_from int(8) unsigned NOT NULL default '0', + + -- Key to the page_id of the link target. + -- An unfortunate consequence of this is that rename + -- operations require changing the links entries for + -- all links to the moved page. l_to int(8) unsigned NOT NULL default '0', + UNIQUE KEY l_from(l_from,l_to), KEY (l_to) ); -- -- Track links to pages that don't yet exist. --- bl_from keys to cur_id --- bl_to is a text link (namespace:title) +-- These rows must be removed when the target page +-- is created, and replaced with links table entries. -- CREATE TABLE /*$wgDBprefix*/brokenlinks ( + -- Key to the page_id of the page containing the link. bl_from int(8) unsigned NOT NULL default '0', + + -- Text of the target page title ("namesapce:title"). + -- Unfortunately this doesn't split the namespace index + -- key and therefore can't easily be joined to anything. bl_to varchar(255) binary NOT NULL default '', UNIQUE KEY bl_from(bl_from,bl_to), KEY (bl_to) @@ -138,29 +363,53 @@ CREATE TABLE /*$wgDBprefix*/brokenlinks ( -- -- Track links to images *used inline* --- il_from keys to cur_id, il_to keys to image_name. --- We don't distinguish live from broken links. +-- We don't distinguish live from broken links here, so +-- they do not need to be changed on upload/removal. -- CREATE TABLE /*$wgDBprefix*/imagelinks ( + -- Key to page_id of the page containing the image / media link. il_from int(8) unsigned NOT NULL default '0', + + -- Filename of target image. + -- This is also the page_title of the file's description page; + -- all such pages are in namespace 6 (NS_IMAGE). il_to varchar(255) binary NOT NULL default '', + UNIQUE KEY il_from(il_from,il_to), KEY (il_to) ); -- -- Track category inclusions *used inline* --- cl_from keys to cur_id, cl_to keys to cur_title of the category page. --- cl_sortkey is the title of the linking page or an optional override --- cl_timestamp marks when the link was last added +-- This tracks a single level of category membership +-- (folksonomic tagging, really). -- CREATE TABLE /*$wgDBprefix*/categorylinks ( + -- Key to page_id of the page defined as a category member. cl_from int(8) unsigned NOT NULL default '0', + + -- Name of the category. + -- This is also the page_title of the category's description page; + -- all such pages are in namespace 14 (NS_CATEGORY). cl_to varchar(255) binary NOT NULL default '', + + -- The title of the linking page, or an optional override + -- to determine sort order. Sorting is by binary order, which + -- isn't always ideal, but collations seem to be an exciting + -- and dangerous new world in MySQL... cl_sortkey varchar(255) binary NOT NULL default '', + + -- This isn't really used at present. Provided for an optional + -- sorting method by approximate addition time. cl_timestamp timestamp NOT NULL, + UNIQUE KEY cl_from(cl_from,cl_to), + + -- This key is trouble. It's incomplete, AND it's too big + -- when collation is set to UTF-8. Bleeeacch! KEY cl_sortkey(cl_to,cl_sortkey(128)), + + -- Not really used? KEY cl_timestamp(cl_to,cl_timestamp) ); @@ -174,26 +423,71 @@ CREATE TABLE /*$wgDBprefix*/linkscc ( lcc_cacheobj MEDIUMBLOB NOT NULL ); +-- +-- Contains a single row with some aggregate info +-- on the state of the site. +-- CREATE TABLE /*$wgDBprefix*/site_stats ( + -- The single row should contain 1 here. ss_row_id int(8) unsigned NOT NULL, + + -- Total number of page views, if hit counters are enabled. ss_total_views bigint(20) unsigned default '0', + + -- Total number of edits performed. ss_total_edits bigint(20) unsigned default '0', + + -- An approximate count of pages matching the following criteria: + -- * in namespace 0 + -- * not a redirect + -- * contains the text '[[' ss_good_articles bigint(20) unsigned default '0', + UNIQUE KEY ss_row_id (ss_row_id) ); +-- +-- Stores an ID for every time any article is visited; +-- depending on $wgHitcounterUpdateFreq, it is +-- periodically cleared and the cur_counter column +-- in the cur table updated for the all articles +-- that have been visited.) +-- CREATE TABLE /*$wgDBprefix*/hitcounter ( hc_id INTEGER UNSIGNED NOT NULL ) TYPE=HEAP MAX_ROWS=25000; + +-- +-- The internet is full of jerks, alas. Sometimes it's handy +-- to block a vandal or troll account. +-- CREATE TABLE /*$wgDBprefix*/ipblocks ( + -- Primary key, introduced for privacy. ipb_id int(8) NOT NULL auto_increment, + + -- Blocked IP address in dotted-quad form or user name. ipb_address varchar(40) binary NOT NULL default '', + + -- Blocked user ID or 0 for IP blocks. ipb_user int(8) unsigned NOT NULL default '0', + + -- User ID who made the block. ipb_by int(8) unsigned NOT NULL default '0', + + -- Text comment made by blocker. ipb_reason tinyblob NOT NULL default '', + + -- Creation (or refresh) date in standard YMDHMS form. + -- IP blocks expire automatically. ipb_timestamp char(14) binary NOT NULL default '', + + -- Indicates that the IP address was banned because a banned + -- user accessed a page through it. If this is 1, ipb_address + -- will be hidden, and the block identified by block ID number. ipb_auto tinyint(1) NOT NULL default '0', + + -- Time at which the block will expire. ipb_expiry char(14) binary NOT NULL default '', PRIMARY KEY ipb_id (ipb_id), @@ -201,27 +495,67 @@ CREATE TABLE /*$wgDBprefix*/ipblocks ( INDEX ipb_user (ipb_user) ); + +-- +-- Uploaded images and other files. +-- CREATE TABLE /*$wgDBprefix*/image ( + -- Filename. + -- This is also the title of the associated description page, + -- which will be in namespace 6 (NS_IMAGE). img_name varchar(255) binary NOT NULL default '', + + -- File size in bytes. img_size int(8) unsigned NOT NULL default '0', + + -- For images, size in pixels. img_width int(5) NOT NULL default '0', img_height int(5) NOT NULL default '0', + + -- Extracted EXIF metadata stored as a serialized PHP array. img_metadata mediumblob NOT NULL, + + -- For images, bits per pixel if known. img_bits int(3) NOT NULL default '0', + + -- File type key returned by getimagesize(). + -- See http://www.php.net/getimagesize for possible values. img_type int(3) NOT NULL default '0', + + -- Description field as entered by the uploader. + -- This is displayed in image upload history and logs. img_description tinyblob NOT NULL default '', + + -- user_id and user_name of uploader. img_user int(5) unsigned NOT NULL default '0', img_user_text varchar(255) binary NOT NULL default '', + + -- Time of the upload. img_timestamp char(14) binary NOT NULL default '', PRIMARY KEY img_name (img_name), + + -- Used by Special:Imagelist for sort-by-size INDEX img_size (img_size), + + -- Used by Special:Newimages and Special:Imagelist INDEX img_timestamp (img_timestamp) ); +-- +-- Previous revisions of uploaded files. +-- Awkwardly, image rows have to be moved into +-- this table at re-upload time. +-- CREATE TABLE /*$wgDBprefix*/oldimage ( + -- Base filename: key to image.img_name oi_name varchar(255) binary NOT NULL default '', + + -- Filename of the archived file. + -- This is generally a timestamp and '!' prepended to the base name. oi_archive_name varchar(255) binary NOT NULL default '', + + -- Other fields as in image... oi_size int(8) unsigned NOT NULL default 0, oi_width int(5) NOT NULL default 0, oi_height int(5) NOT NULL default 0, @@ -235,25 +569,60 @@ CREATE TABLE /*$wgDBprefix*/oldimage ( INDEX oi_name (oi_name(10)) ); + +-- +-- Primarily a summary table for Special:Recentchanges, +-- this table contains some additional info on edits from +-- the last few days. +-- CREATE TABLE /*$wgDBprefix*/recentchanges ( rc_id int(8) NOT NULL auto_increment, rc_timestamp varchar(14) binary NOT NULL default '', rc_cur_time varchar(14) binary NOT NULL default '', + + -- As in revision rc_user int(10) unsigned NOT NULL default '0', rc_user_text varchar(255) binary NOT NULL default '', + + -- When pages are renamed, their RC entries do _not_ change. rc_namespace tinyint(3) NOT NULL default '0', rc_title varchar(255) binary NOT NULL default '', + + -- as in revision... rc_comment varchar(255) binary NOT NULL default '', rc_minor tinyint(3) unsigned NOT NULL default '0', + + -- Edits by user accounts with the 'bot' rights key are + -- marked with a 1 here, and will be hidden from the + -- default view. rc_bot tinyint(3) unsigned NOT NULL default '0', + rc_new tinyint(3) unsigned NOT NULL default '0', + + -- Key to page_id (was cur_id prior to 1.5). + -- This will keep links working after moves while + -- retaining the at-the-time name in the changes list. rc_cur_id int(10) unsigned NOT NULL default '0', + + -- rev_id of the given revision rc_this_oldid int(10) unsigned NOT NULL default '0', + + -- rev_id of the prior revision, for generating diff links. rc_last_oldid int(10) unsigned NOT NULL default '0', + + -- These may no longer be used, with the new move log. rc_type tinyint(3) unsigned NOT NULL default '0', rc_moved_to_ns tinyint(3) unsigned NOT NULL default '0', rc_moved_to_title varchar(255) binary NOT NULL default '', + + -- If the Recent Changes Patrol option is enabled, + -- users may mark edits as having been reviewed to + -- remove a warning flag on the RC list. + -- A value of 1 indicates the page has been reviewed. rc_patrolled tinyint(3) unsigned NOT NULL default '0', + + -- Recorded IP address the edit was made from, if the + -- $wgPutIPinRC option is enabled. rc_ip char(15) NOT NULL default '', PRIMARY KEY rc_id (rc_id), @@ -265,53 +634,112 @@ CREATE TABLE /*$wgDBprefix*/recentchanges ( ); CREATE TABLE /*$wgDBprefix*/watchlist ( + -- Key to user_id wl_user int(5) unsigned NOT NULL, + + -- Key to page_namespace/page_title + -- Note that users may watch patches which do not exist yet, + -- or existed in the past but have been deleted. wl_namespace tinyint(2) unsigned NOT NULL default '0', wl_title varchar(255) binary NOT NULL default '', + + -- Timestamp when user was last sent a notification e-mail; + -- cleared when the user visits the page. + -- FIXME: add proper null support etc wl_notificationtimestamp varchar(14) binary NOT NULL default '0', + UNIQUE KEY (wl_user, wl_namespace, wl_title), KEY namespace_title (wl_namespace,wl_title) ); + +-- +-- Used by texvc math-rendering extension to keep track +-- of previously-rendered items. +-- CREATE TABLE /*$wgDBprefix*/math ( + -- Binary MD5 hash of the latex fragment, used as an identifier key. math_inputhash varchar(16) NOT NULL, + + -- Not sure what this is, exactly... math_outputhash varchar(16) NOT NULL, + + -- texvc reports how well it thinks the HTML conversion worked; + -- if it's a low level the PNG rendering may be preferred. math_html_conservativeness tinyint(1) NOT NULL, + + -- HTML output from texvc, if any math_html text, + + -- MathML output from texvc, if any math_mathml text, + UNIQUE KEY math_inputhash (math_inputhash) ); - --- Table searchindex must be MyISAM for fulltext support - +-- +-- When using the default MySQL search backend, page titles +-- and text are munged to strip markup, do Unicode case folding, +-- and prepare the result for MySQL's fulltext index. +-- +-- This table must be MyISAM; InnoDB does not support the needed +-- fulltext index. +-- CREATE TABLE /*$wgDBprefix*/searchindex ( + -- Key to page_id si_page int(8) unsigned NOT NULL, + + -- Munged version of title si_title varchar(255) NOT NULL default '', + + -- Munged version of body text si_text mediumtext NOT NULL default '', + UNIQUE KEY (si_page), FULLTEXT si_title (si_title), FULLTEXT si_text (si_text) ) TYPE=MyISAM; +-- +-- Recognized interwiki link prefixes +-- CREATE TABLE /*$wgDBprefix*/interwiki ( + -- The interwiki prefix, (e.g. "Meatball", or the language prefix "de") iw_prefix char(32) NOT NULL, + + -- The URL of the wiki, with "$1" as a placeholder for an article name. + -- Any spaces in the name will be transformed to underscores before + -- insertion. iw_url char(127) NOT NULL, + + -- A boolean value indicating whether the wiki is in this project + -- (used, for example, to detect redirect loops) iw_local BOOL NOT NULL, + UNIQUE KEY iw_prefix (iw_prefix) ); +-- -- Used for caching expensive grouped queries +-- CREATE TABLE /*$wgDBprefix*/querycache ( + -- A key name, generally the base name of of the special page. qc_type char(32) NOT NULL, + + -- Some sort of stored value. Sizes, counts... qc_value int(5) unsigned NOT NULL default '0', + + -- Target namespace+title qc_namespace tinyint(2) unsigned NOT NULL default '0', qc_title char(255) binary NOT NULL default '', + KEY (qc_type,qc_value) ); +-- -- For a few generic cache operations if not using Memcached +-- CREATE TABLE /*$wgDBprefix*/objectcache ( keyname char(255) binary not null default '', value mediumblob, @@ -321,6 +749,7 @@ CREATE TABLE /*$wgDBprefix*/objectcache ( ); -- For storing revision text +-- This isn't used... CREATE TABLE /*$wgDBprefix*/blobs ( blob_index char(255) binary NOT NULL default '', blob_data longblob NOT NULL default '', @@ -328,7 +757,6 @@ CREATE TABLE /*$wgDBprefix*/blobs ( ); -- For article validation - CREATE TABLE /*$wgDBprefix*/validate ( `val_user` int(11) NOT NULL default '0', `val_page` int(11) unsigned NOT NULL default '0', @@ -339,6 +767,7 @@ CREATE TABLE /*$wgDBprefix*/validate ( KEY `val_user` (`val_user`,`val_revision`) ) TYPE=MyISAM; + CREATE TABLE /*$wgDBprefix*/logging ( -- Symbolic keys for the general log type and the action type -- within the log. The output format will be controlled by the