CREATE TABLE /*_*/user_former_groups (
-- Key to user_id
ufg_user int unsigned NOT NULL default 0,
- ufg_group varbinary(255) NOT NULL default ''
+ ufg_group varbinary(255) NOT NULL default '',
+ PRIMARY KEY (ufg_user,ufg_group)
) /*$wgDBTableOptions*/;
-CREATE UNIQUE INDEX /*i*/ufg_user_group ON /*_*/user_former_groups (ufg_user,ufg_group);
-
--
-- Stores notifications of user talk page changes, for the display
-- of the "you have new messages" box
up_property varbinary(255) NOT NULL,
-- Property value as a string.
- up_value blob
+ up_value blob,
+ PRIMARY KEY (up_user,up_property)
) /*$wgDBTableOptions*/;
-CREATE UNIQUE INDEX /*i*/user_properties_user_property ON /*_*/user_properties (up_user,up_property);
CREATE INDEX /*i*/user_properties_property ON /*_*/user_properties (up_property);
--
-- or a rollback to a previous version.
rev_text_id int 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 by Linker::formatComment()
- rev_comment varbinary(767) NOT NULL,
+ -- Text comment summarizing the change. Deprecated in favor of
+ -- revision_comment_temp.revcomment_comment_id.
+ rev_comment varbinary(767) NOT NULL default '',
-- Key to user.user_id of the user who made this edit.
-- Stores 0 for anonymous edits and for some mass imports.
-- and is a logged-in user.
CREATE INDEX /*i*/page_user_timestamp ON /*_*/revision (rev_page,rev_user,rev_timestamp);
+--
+-- Temporary table to avoid blocking on an alter of revision.
+--
+-- On large wikis like the English Wikipedia, altering the revision table is a
+-- months-long process. This table is being created to avoid such an alter, and
+-- will be merged back into revision in the future.
+--
+CREATE TABLE /*_*/revision_comment_temp (
+ -- Key to rev_id
+ revcomment_rev int unsigned NOT NULL,
+ -- Key to comment_id
+ revcomment_comment_id bigint unsigned NOT NULL,
+ PRIMARY KEY (revcomment_rev, revcomment_comment_id)
+) /*$wgDBTableOptions*/;
+-- Ensure uniqueness
+CREATE UNIQUE INDEX /*i*/revcomment_rev ON /*_*/revision_comment_temp (revcomment_rev);
+
+--
+-- Every time an edit by a logged out user is saved,
+-- a row is created in ip_changes. This stores
+-- the IP as a hex representation so that we can more
+-- easily find edits within an IP range.
+--
+CREATE TABLE /*_*/ip_changes (
+ -- Foreign key to the revision table, also serves as the unique primary key
+ ipc_rev_id int unsigned NOT NULL PRIMARY KEY DEFAULT '0',
+
+ -- The timestamp of the revision
+ ipc_rev_timestamp binary(14) NOT NULL DEFAULT '',
+
+ -- Hex representation of the IP address, as returned by IP::toHex()
+ -- For IPv4 it will resemble: ABCD1234
+ -- For IPv6: v6-ABCD1234000000000000000000000000
+ -- BETWEEN is then used to identify revisions within a given range
+ ipc_hex varbinary(35) NOT NULL DEFAULT ''
+
+) /*$wgDBTableOptions*/;
+
+CREATE INDEX /*i*/ipc_rev_timestamp ON /*_*/ip_changes (ipc_rev_timestamp);
+CREATE INDEX /*i*/ipc_hex_time ON /*_*/ip_changes (ipc_hex,ipc_rev_timestamp);
+
--
-- Holds text of individual page revisions.
--
-- In case tables are created as MyISAM, use row hints for MySQL <5.0 to avoid 4GB limit
+--
+-- Edits, blocks, and other actions typically have a textual comment describing
+-- the action. They are stored here to reduce the size of the main tables, and
+-- to allow for deduplication.
+--
+-- Deduplication is currently best-effort to avoid locking on inserts that
+-- would be required for strict deduplication. There MAY be multiple rows with
+-- the same comment_text and comment_data.
+--
+CREATE TABLE /*_*/comment (
+ -- Unique ID to identify each comment
+ comment_id bigint unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
+
+ -- Hash of comment_text and comment_data, for deduplication
+ comment_hash INT 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 by Linker::formatComment()
+ -- Size limits are enforced at the application level, and should
+ -- take care to crop UTF-8 strings appropriately.
+ comment_text BLOB NOT NULL,
+
+ -- JSON data, intended for localizing auto-generated comments.
+ -- This holds structured data that is intended to be used to provide
+ -- localized versions of automatically-generated comments. When not empty,
+ -- comment_text should be the generated comment localized using the wiki's
+ -- content language.
+ comment_data BLOB
+) /*$wgDBTableOptions*/;
+-- Index used for deduplication.
+CREATE INDEX /*i*/comment_hash ON comment (comment_hash);
+
+
--
-- Holding area for deleted articles, which may be viewed
-- or restored by admins through the Special:Undelete interface.
ar_text mediumblob NOT NULL,
-- Basic revision stuff...
- ar_comment varbinary(767) NOT NULL,
+ ar_comment varbinary(767) NOT NULL default '', -- Deprecated in favor of ar_comment_id
+ ar_comment_id bigint unsigned NOT NULL DEFAULT 0, -- ("DEFAULT 0" is temporary, signaling that ar_comment should be used)
ar_user int unsigned NOT NULL default 0,
ar_user_text varchar(255) binary NOT NULL,
ar_timestamp binary(14) NOT NULL default '',
-- and deletions may refer to different page records as time
-- goes by.
pl_namespace int NOT NULL default 0,
- pl_title varchar(255) binary NOT NULL default ''
+ pl_title varchar(255) binary NOT NULL default '',
+ PRIMARY KEY (pl_from,pl_namespace,pl_title)
) /*$wgDBTableOptions*/;
--- Forward index, for page edit, save
-CREATE UNIQUE INDEX /*i*/pl_from ON /*_*/pagelinks (pl_from,pl_namespace,pl_title);
-
-- Reverse index, for Special:Whatlinkshere
CREATE INDEX /*i*/pl_namespace ON /*_*/pagelinks (pl_namespace,pl_title,pl_from);
-- and deletions may refer to different page records as time
-- goes by.
tl_namespace int NOT NULL default 0,
- tl_title varchar(255) binary NOT NULL default ''
+ tl_title varchar(255) binary NOT NULL default '',
+ PRIMARY KEY (tl_from,tl_namespace,tl_title)
) /*$wgDBTableOptions*/;
--- Forward index, for page edit, save
-CREATE UNIQUE INDEX /*i*/tl_from ON /*_*/templatelinks (tl_from,tl_namespace,tl_title);
-
-- Reverse index, for Special:Whatlinkshere
CREATE INDEX /*i*/tl_namespace ON /*_*/templatelinks (tl_namespace,tl_title,tl_from);
-- Filename of target image.
-- This is also the page_title of the file's description page;
-- all such pages are in namespace 6 (NS_FILE).
- il_to varchar(255) binary NOT NULL default ''
+ il_to varchar(255) binary NOT NULL default '',
+ PRIMARY KEY (il_from,il_to)
) /*$wgDBTableOptions*/;
--- Forward index, for cache invalidation on file update, etc.
-CREATE UNIQUE INDEX /*i*/il_from ON /*_*/imagelinks (il_from,il_to);
-
-- Reverse index, for Special:Whatlinkshere and file description page local usage
CREATE INDEX /*i*/il_to ON /*_*/imagelinks (il_to,il_from);
-- paginate the three categories separately. This never has to be updated
-- after the page is created, since none of these page types can be moved to
-- any other.
- cl_type ENUM('page', 'subcat', 'file') NOT NULL default 'page'
+ cl_type ENUM('page', 'subcat', 'file') NOT NULL default 'page',
+ PRIMARY KEY (cl_from,cl_to)
) /*$wgDBTableOptions*/;
-CREATE UNIQUE INDEX /*i*/cl_from ON /*_*/categorylinks (cl_from,cl_to);
-- We always sort within a given category, and within a given type. FIXME:
-- Formerly this index didn't cover cl_type (since that didn't exist), so old
ll_lang varbinary(20) NOT NULL default '',
-- Title of the target, including namespace
- ll_title varchar(255) binary NOT NULL default ''
+ ll_title varchar(255) binary NOT NULL default '',
+ PRIMARY KEY (ll_from,ll_lang)
) /*$wgDBTableOptions*/;
--- Forward index, for page edit, save, ApiQueryLanglinks
-CREATE UNIQUE INDEX /*i*/ll_from ON /*_*/langlinks (ll_from, ll_lang);
-
-- Index for ApiQueryLangbacklinks
CREATE INDEX /*i*/ll_lang ON /*_*/langlinks (ll_lang, ll_title);
iwl_prefix varbinary(20) NOT NULL default '',
-- Title of the target, including namespace
- iwl_title varchar(255) binary NOT NULL default ''
+ iwl_title varchar(255) binary NOT NULL default '',
+ PRIMARY KEY (iwl_from,iwl_prefix,iwl_title)
) /*$wgDBTableOptions*/;
--- Forward index, for page edit, save, ApiQueryIWLinks
-CREATE UNIQUE INDEX /*i*/iwl_from ON /*_*/iwlinks (iwl_from, iwl_prefix, iwl_title);
-
-- Index for ApiQueryIWBacklinks
CREATE INDEX /*i*/iwl_prefix_title_from ON /*_*/iwlinks (iwl_prefix, iwl_title, iwl_from);
--
CREATE TABLE /*_*/site_stats (
-- The single row should contain 1 here.
- ss_row_id int unsigned NOT NULL,
+ ss_row_id int unsigned NOT NULL PRIMARY KEY,
-- Total number of edits performed.
ss_total_edits bigint unsigned default 0,
ss_images int default 0
) /*$wgDBTableOptions*/;
--- Pointless index to assuage developer superstitions
-CREATE UNIQUE INDEX /*i*/ss_row_id ON /*_*/site_stats (ss_row_id);
-
--
-- The internet is full of jerks, alas. Sometimes it's handy
-- to block a vandal or troll account.
-- User name of blocker
ipb_by_text varchar(255) binary NOT NULL default '',
- -- Text comment made by blocker.
- ipb_reason varbinary(767) NOT NULL,
+ -- Text comment made by blocker. Deprecated in favor of ipb_reason_id
+ ipb_reason varbinary(767) NOT NULL default '',
+
+ -- Key to comment_id. Text comment made by blocker.
+ -- ("DEFAULT 0" is temporary, signaling that ipb_reason should be used)
+ ipb_reason_id bigint unsigned NOT NULL DEFAULT 0,
-- Creation (or refresh) date in standard YMDHMS form.
-- IP blocks expire automatically.
img_bits int NOT NULL default 0,
-- Media type as defined by the MEDIATYPE_xxx constants
- img_media_type ENUM("UNKNOWN", "BITMAP", "DRAWING", "AUDIO", "VIDEO", "MULTIMEDIA", "OFFICE", "TEXT", "EXECUTABLE", "ARCHIVE") default NULL,
+ img_media_type ENUM("UNKNOWN", "BITMAP", "DRAWING", "AUDIO", "VIDEO", "MULTIMEDIA", "OFFICE", "TEXT", "EXECUTABLE", "ARCHIVE", "3D") default NULL,
-- major part of a MIME media type as defined by IANA
-- see https://www.iana.org/assignments/media-types/
-- Description field as entered by the uploader.
-- This is displayed in image upload history and logs.
- img_description varbinary(767) NOT NULL,
+ -- Deprecated in favor of image_comment_temp.imgcomment_description_id.
+ img_description varbinary(767) NOT NULL default '',
-- user_id and user_name of uploader.
img_user int unsigned NOT NULL default 0,
-- Used to get media of one type
CREATE INDEX /*i*/img_media_mime ON /*_*/image (img_media_type,img_major_mime,img_minor_mime);
+--
+-- Temporary table to avoid blocking on an alter of image.
+--
+-- On large wikis like Wikimedia Commons, altering the image table is a
+-- months-long process. This table is being created to avoid such an alter, and
+-- will be merged back into image in the future.
+--
+CREATE TABLE /*_*/image_comment_temp (
+ -- Key to img_name (ugh)
+ imgcomment_name varchar(255) binary NOT NULL,
+ -- Key to comment_id
+ imgcomment_description_id bigint unsigned NOT NULL,
+ PRIMARY KEY (imgcomment_name, imgcomment_description_id)
+) /*$wgDBTableOptions*/;
+-- Ensure uniqueness
+CREATE UNIQUE INDEX /*i*/imgcomment_name ON /*_*/image_comment_temp (imgcomment_name);
+
--
-- Previous revisions of uploaded files.
oi_width int NOT NULL default 0,
oi_height int NOT NULL default 0,
oi_bits int NOT NULL default 0,
- oi_description varbinary(767) NOT NULL,
+ oi_description varbinary(767) NOT NULL default '', -- Deprecated.
+ oi_description_id bigint unsigned NOT NULL DEFAULT 0, -- ("DEFAULT 0" is temporary, signaling that oi_description should be used)
oi_user int unsigned NOT NULL default 0,
oi_user_text varchar(255) binary NOT NULL,
oi_timestamp binary(14) NOT NULL default '',
oi_metadata mediumblob NOT NULL,
- oi_media_type ENUM("UNKNOWN", "BITMAP", "DRAWING", "AUDIO", "VIDEO", "MULTIMEDIA", "OFFICE", "TEXT", "EXECUTABLE", "ARCHIVE") default NULL,
+ oi_media_type ENUM("UNKNOWN", "BITMAP", "DRAWING", "AUDIO", "VIDEO", "MULTIMEDIA", "OFFICE", "TEXT", "EXECUTABLE", "ARCHIVE", "3D") default NULL,
oi_major_mime ENUM("unknown", "application", "audio", "image", "text", "video", "message", "model", "multipart", "chemical") NOT NULL default "unknown",
oi_minor_mime varbinary(100) NOT NULL default "unknown",
oi_deleted tinyint unsigned NOT NULL default 0,
-- Deletion information, if this file is deleted.
fa_deleted_user int,
fa_deleted_timestamp binary(14) default '',
- fa_deleted_reason varbinary(767) default '',
+ fa_deleted_reason varbinary(767) default '', -- Deprecated
+ fa_deleted_reason_id bigint unsigned NOT NULL DEFAULT 0, -- ("DEFAULT 0" is temporary, signaling that fa_deleted_reason should be used)
-- Duped fields from image
fa_size int unsigned default 0,
fa_height int default 0,
fa_metadata mediumblob,
fa_bits int default 0,
- fa_media_type ENUM("UNKNOWN", "BITMAP", "DRAWING", "AUDIO", "VIDEO", "MULTIMEDIA", "OFFICE", "TEXT", "EXECUTABLE", "ARCHIVE") default NULL,
+ fa_media_type ENUM("UNKNOWN", "BITMAP", "DRAWING", "AUDIO", "VIDEO", "MULTIMEDIA", "OFFICE", "TEXT", "EXECUTABLE", "ARCHIVE", "3D") default NULL,
fa_major_mime ENUM("unknown", "application", "audio", "image", "text", "video", "message", "model", "multipart", "chemical") default "unknown",
fa_minor_mime varbinary(100) default "unknown",
- fa_description varbinary(767),
+ fa_description varbinary(767) default '', -- Deprecated
+ fa_description_id bigint unsigned NOT NULL DEFAULT 0, -- ("DEFAULT 0" is temporary, signaling that fa_description should be used)
fa_user int unsigned default 0,
fa_user_text varchar(255) binary,
fa_timestamp binary(14) default '',
us_sha1 varchar(31) NOT NULL,
us_mime varchar(255),
-- Media type as defined by the MEDIATYPE_xxx constants, should duplicate definition in the image table
- us_media_type ENUM("UNKNOWN", "BITMAP", "DRAWING", "AUDIO", "VIDEO", "MULTIMEDIA", "OFFICE", "TEXT", "EXECUTABLE", "ARCHIVE") default NULL,
+ us_media_type ENUM("UNKNOWN", "BITMAP", "DRAWING", "AUDIO", "VIDEO", "MULTIMEDIA", "OFFICE", "TEXT", "EXECUTABLE", "ARCHIVE", "3D") default NULL,
-- image-specific properties
us_image_width int unsigned,
us_image_height int unsigned,
rc_title varchar(255) binary NOT NULL default '',
-- as in revision...
- rc_comment varbinary(767) NOT NULL default '',
+ rc_comment varbinary(767) NOT NULL default '', -- Deprecated.
+ rc_comment_id bigint unsigned NOT NULL DEFAULT 0, -- ("DEFAULT 0" is temporary, signaling that rc_comment should be used)
rc_minor tinyint unsigned NOT NULL default 0,
-- Edits by user accounts with the 'bot' rights key are
-- Cache of interwiki transclusion
--
CREATE TABLE /*_*/transcache (
- tc_url varbinary(255) NOT NULL,
+ tc_url varbinary(255) NOT NULL PRIMARY KEY,
tc_contents text,
tc_time binary(14) NOT NULL
) /*$wgDBTableOptions*/;
-CREATE UNIQUE INDEX /*i*/tc_url_idx ON /*_*/transcache (tc_url);
-
CREATE TABLE /*_*/logging (
-- Log ID, for referring to this specific log entry, probably for deletion and such.
log_page int unsigned NULL,
-- Freeform text. Interpreted as edit history comments.
+ -- Deprecated in favor of log_comment_id.
log_comment varbinary(767) NOT NULL default '',
+ -- Key to comment_id. Comment summarizing the change.
+ -- ("DEFAULT 0" is temporary, signaling that log_comment should be used)
+ log_comment_id bigint unsigned NOT NULL DEFAULT 0,
+
-- miscellaneous parameters:
-- LF separated list (old system) or serialized PHP array (new system)
log_params blob NOT NULL,
-- The value of the ID
ls_value varchar(255) NOT NULL,
-- Key to log_id
- ls_log_id int unsigned NOT NULL default 0
+ ls_log_id int unsigned NOT NULL default 0,
+ PRIMARY KEY (ls_field,ls_value,ls_log_id)
) /*$wgDBTableOptions*/;
-CREATE UNIQUE INDEX /*i*/ls_field_val ON /*_*/log_search (ls_field,ls_value,ls_log_id);
CREATE INDEX /*i*/ls_log_id ON /*_*/log_search (ls_log_id);
CREATE TABLE /*_*/querycache_info (
-- Special page name
-- Corresponds to a qc_type value
- qci_type varbinary(32) NOT NULL default '',
+ qci_type varbinary(32) NOT NULL default '' PRIMARY KEY,
-- Timestamp of last update
qci_timestamp binary(14) NOT NULL default '19700101000000'
) /*$wgDBTableOptions*/;
-CREATE UNIQUE INDEX /*i*/qci_type ON /*_*/querycache_info (qci_type);
-
-- For each redirect, this table contains exactly one row defining its target
CREATE TABLE /*_*/redirect (
pt_namespace int NOT NULL,
pt_title varchar(255) binary NOT NULL,
pt_user int unsigned NOT NULL,
- pt_reason varbinary(767),
+ pt_reason varbinary(767) default '', -- Deprecated.
+ pt_reason_id bigint unsigned NOT NULL DEFAULT 0, -- ("DEFAULT 0" is temporary, signaling that pt_reason should be used)
pt_timestamp binary(14) NOT NULL,
pt_expiry varbinary(14) NOT NULL default '',
pt_create_perm varbinary(60) NOT NULL
-- Module context vary (includes skin and language; called "md_skin" for legacy reasons)
md_skin varbinary(32) NOT NULL,
-- JSON blob with file dependencies
- md_deps mediumblob NOT NULL
+ md_deps mediumblob NOT NULL,
+ PRIMARY KEY (md_module,md_skin)
) /*$wgDBTableOptions*/;
-CREATE UNIQUE INDEX /*i*/md_module_skin ON /*_*/module_deps (md_module, md_skin);
-- Holds all the sites known to the wiki.
CREATE TABLE /*_*/sites (