--
CREATE TABLE /*_*/user_properties (
-- Foreign key to user.user_id
- up_user int NOT NULL,
+ up_user int unsigned NOT NULL,
-- Name of the option being saved. This is indexed for bulk lookup.
up_property varbinary(255) NOT NULL,
--
CREATE TABLE /*_*/bot_passwords (
-- User ID obtained from CentralIdLookup.
- bp_user int NOT NULL,
+ bp_user int unsigned NOT NULL,
-- Application identifier
bp_app_id varbinary(32) NOT NULL,
-- 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
-- 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 '',
-- rows, such as change_tag.
CREATE INDEX /*i*/ar_revid ON /*_*/archive (ar_rev_id);
+--
+-- Slots represent an n:m relation between revisions and content objects.
+-- A content object can have a specific "role" in one or more revisions.
+-- Each revision can have multiple content objects, each having a different role.
+--
+CREATE TABLE /*_*/slots (
+
+ -- reference to rev_id
+ slot_revision_id bigint unsigned NOT NULL,
+
+ -- reference to role_id
+ slot_role_id smallint unsigned NOT NULL,
+
+ -- reference to content_id
+ slot_content_id bigint unsigned NOT NULL,
+
+ -- whether the content is inherited (1) or new in this revision (0)
+ slot_inherited tinyint unsigned NOT NULL DEFAULT 0,
+
+ PRIMARY KEY ( slot_revision_id, slot_role_id )
+) /*$wgDBTableOptions*/;
+
+-- Index for finding revisions that modified a specific slot
+CREATE INDEX /*i*/slot_role_inherited ON /*_*/slots (slot_revision_id, slot_role_id, slot_inherited);
+
+--
+-- The content table represents content objects. It's primary purpose is to provide the necessary
+-- meta-data for loading and interpreting a serialized data blob to create a content object.
+--
+CREATE TABLE /*_*/content (
+
+ -- ID of the content object
+ content_id bigint unsigned PRIMARY KEY AUTO_INCREMENT,
+
+ -- Nominal size of the content object (not necessarily of the serialized blob)
+ content_size int unsigned NOT NULL,
+
+ -- Nominal hash of the content object (not necessarily of the serialized blob)
+ content_sha1 varbinary(32) NOT NULL,
+
+ -- reference to model_id
+ content_model smallint unsigned NOT NULL,
+
+ -- URL-like address of the content blob
+ content_address varbinary(255) NOT NULL
+) /*$wgDBTableOptions*/;
+
+--
+-- Normalization table for role names
+--
+CREATE TABLE /*_*/slot_roles (
+ role_id smallint PRIMARY KEY AUTO_INCREMENT,
+ role_name varbinary(64) NOT NULL
+) /*$wgDBTableOptions*/;
+
+-- Index for looking of the internal ID of for a name
+CREATE UNIQUE INDEX /*i*/role_name ON /*_*/slot_roles (role_name);
+
+--
+-- Normalization table for content model names
+--
+CREATE TABLE /*_*/content_models (
+ model_id smallint PRIMARY KEY AUTO_INCREMENT,
+ model_name varbinary(64) NOT NULL
+) /*$wgDBTableOptions*/;
+
+-- Index for looking of the internal ID of for a name
+CREATE UNIQUE INDEX /*i*/model_name ON /*_*/content_models (model_name);
--
-- Track page-to-page hyperlinks within the wiki.
-- 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.
-- 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 '',
-- 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_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 '',
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
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,
-- Whether or not to cascade the protection down to pages transcluded.
pr_cascade tinyint NOT NULL,
-- Field for future support of per-user restriction.
- pr_user int NULL,
+ pr_user int unsigned NULL,
-- Field for time-limited protection.
pr_expiry varbinary(14) NULL
) /*$wgDBTableOptions*/;
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
-- RCID for the change
ct_rc_id int NULL,
-- LOGID for the change
- ct_log_id int NULL,
+ ct_log_id int unsigned NULL,
-- REVID for the change
- ct_rev_id int NULL,
+ ct_rev_id int unsigned NULL,
-- Tag applied
ct_tag varchar(255) NOT NULL,
-- Parameters for the tag, presently unused
-- RCID for the change
ts_rc_id int NULL,
-- LOGID for the change
- ts_log_id int NULL,
+ ts_log_id int unsigned NULL,
-- REVID for the change
- ts_rev_id int NULL,
+ ts_rev_id int unsigned NULL,
-- Comma-separated list of tags
ts_tags blob NOT NULL
) /*$wgDBTableOptions*/;
-- Cache key
lc_key varchar(255) NOT NULL,
-- Value
- lc_value mediumblob NOT NULL
+ lc_value mediumblob NOT NULL,
+ PRIMARY KEY (lc_lang, lc_key)
) /*$wgDBTableOptions*/;
-CREATE INDEX /*i*/lc_lang_key ON /*_*/l10n_cache (lc_lang, lc_key);
-- Table caching which local files a module depends on that aren't
-- registered directly, used for fast retrieval of file dependency.