-- conflicts. Spaces are allowed, and are _not_ converted
-- to underscores like titles. See the User::newFromName() for
-- the specific tests that usernames have to pass.
- user_name varbinary(255) NOT NULL default '',
+ user_name varchar(255) binary NOT NULL default '',
-- Optional 'real name' to be displayed in credit listings
- user_real_name varbinary(255) NOT NULL default '',
+ user_real_name varchar(255) binary NOT NULL default '',
-- Password hashes, see User::crypt() and User::comparePasswords()
-- in User.php for the algorithm
-- Same with passwords.
user_email tinytext NOT NULL,
- -- Newline-separated list of name=value defining the user
- -- preferences
- -- Now obsolete in favour of user_properties table;
- -- old values will be migrated from here transparently.
- user_options blob NOT NULL,
-
-- 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
CREATE UNIQUE INDEX /*i*/user_name ON /*_*/user (user_name);
CREATE INDEX /*i*/user_email_token ON /*_*/user (user_email_token);
+CREATE INDEX /*i*/user_email ON /*_*/user (user_email(50));
--
-- with particular permissions. A user will have the combined
-- permissions of any group they're explicitly in, plus
-- the implicit '*' and 'user' groups.
- ug_group varbinary(16) NOT NULL default ''
+ ug_group varbinary(32) NOT NULL default ''
) /*$wgDBTableOptions*/;
CREATE UNIQUE INDEX /*i*/ug_user_group ON /*_*/user_groups (ug_user,ug_group);
CREATE INDEX /*i*/ug_group ON /*_*/user_groups (ug_group);
+-- Stores the groups the user has once belonged to.
+-- The user may still belong these groups. Check user_groups.
+CREATE TABLE /*_*/user_former_groups (
+ -- Key to user_id
+ ufg_user int unsigned NOT NULL default 0,
+ ufg_group varbinary(16) NOT NULL default ''
+) /*$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
user_ip varbinary(40) NOT NULL default '',
-- The highest timestamp of revisions of the talk page viewed
-- by this user
- user_last_timestamp binary(14) NOT NULL default ''
+ user_last_timestamp varbinary(14) NULL default NULL
) /*$wgDBTableOptions*/;
-- Indexes renamed for SQLite in 1.14
up_user int NOT NULL,
-- Name of the option being saved. This is indexed for bulk lookup.
- up_property varbinary(32) NOT NULL,
+ up_property varbinary(255) NOT NULL,
-- Property value as a string.
up_value blob
-- The rest of the title, as text.
-- Spaces are transformed into underscores in title storage.
- page_title varbinary(255) NOT NULL,
+ page_title varchar(255) binary NOT NULL,
-- Comma-separated set of permission keys indicating who
-- can move or edit the page.
CREATE UNIQUE INDEX /*i*/name_title ON /*_*/page (page_namespace,page_title);
CREATE INDEX /*i*/page_random ON /*_*/page (page_random);
CREATE INDEX /*i*/page_len ON /*_*/page (page_len);
-
+CREATE INDEX /*i*/page_redirect_namespace_len ON /*_*/page (page_is_redirect, page_namespace, page_len);
--
-- Every edit of a page creates also a revision row.
-- to the text storage backend.
--
CREATE TABLE /*_*/revision (
+ -- Unique ID to identify each revision
rev_id int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
-- Key to page_id. This should _never_ be invalid.
rev_user int unsigned NOT NULL default 0,
-- Text username or IP address of the editor.
- rev_user_text varbinary(255) NOT NULL default '',
+ rev_user_text varchar(255) binary NOT NULL default '',
- -- Timestamp
+ -- Timestamp of when revision was created
rev_timestamp binary(14) NOT NULL default '',
-- Records whether the user marked the 'minor edit' checkbox.
-- Many automated edits are marked as minor.
rev_minor_edit tinyint unsigned NOT NULL default 0,
- -- Not yet used; reserved for future changes to the deletion system.
+ -- Restrictions on who can access this revision
rev_deleted tinyint unsigned NOT NULL default 0,
-- Length of this revision in bytes
-- Key to revision.rev_id
-- This field is used to add support for a tree structure (The Adjacency List Model)
- rev_parent_id int unsigned default NULL
+ rev_parent_id int unsigned default NULL,
+
+ -- SHA-1 text content hash in base-36
+ rev_sha1 varbinary(32) NOT NULL default ''
) /*$wgDBTableOptions*/ MAX_ROWS=10000000 AVG_ROW_LENGTH=1024;
-- In case tables are created as MyISAM, use row hints for MySQL <5.0 to avoid 4GB limit
--
CREATE TABLE /*_*/archive (
ar_namespace int NOT NULL default 0,
- ar_title varbinary(255) NOT NULL default '',
+ 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.
-- Basic revision stuff...
ar_comment tinyblob NOT NULL,
ar_user int unsigned NOT NULL default 0,
- ar_user_text varbinary(255) NOT NULL,
+ ar_user_text varchar(255) binary NOT NULL,
ar_timestamp binary(14) NOT NULL default '',
ar_minor_edit tinyint NOT NULL default 0,
ar_page_id int unsigned,
-- Original previous revision
- ar_parent_id int unsigned default NULL
+ ar_parent_id int unsigned default NULL,
+
+ -- SHA-1 text content hash in base-36
+ ar_sha1 varbinary(32) NOT NULL default ''
) /*$wgDBTableOptions*/;
CREATE INDEX /*i*/name_title_timestamp ON /*_*/archive (ar_namespace,ar_title,ar_timestamp);
-- and deletions may refer to different page records as time
-- goes by.
pl_namespace int NOT NULL default 0,
- pl_title varbinary(255) NOT NULL default ''
+ pl_title varchar(255) binary NOT NULL default ''
) /*$wgDBTableOptions*/;
CREATE UNIQUE INDEX /*i*/pl_from ON /*_*/pagelinks (pl_from,pl_namespace,pl_title);
-- and deletions may refer to different page records as time
-- goes by.
tl_namespace int NOT NULL default 0,
- tl_title varbinary(255) NOT NULL default ''
+ tl_title varchar(255) binary NOT NULL default ''
) /*$wgDBTableOptions*/;
CREATE UNIQUE INDEX /*i*/tl_from ON /*_*/templatelinks (tl_from,tl_namespace,tl_title);
-- 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 varbinary(255) NOT NULL default ''
+ il_to varchar(255) binary NOT NULL default ''
) /*$wgDBTableOptions*/;
CREATE UNIQUE INDEX /*i*/il_from ON /*_*/imagelinks (il_from,il_to);
-- 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 varbinary(255) NOT NULL default '',
+ cl_to varchar(255) binary NOT NULL default '',
-- A binary string obtained by applying a sortkey generation algorithm
-- (Collation::getSortKey()) to page_title, or cl_sortkey_prefix . "\n"
-- collations without reparsing all pages.
-- Note: If you change the length of this field, you also need to change
-- code in LinksUpdate.php. See bug 25254.
- cl_sortkey_prefix varbinary(255) NOT NULL default '',
+ cl_sortkey_prefix varchar(255) binary NOT NULL default '',
-- This isn't really used at present. Provided for an optional
-- sorting method by approximate addition time.
-- Name of the category, in the same form as page_title (with underscores).
-- If there is a category page corresponding to this category, by definition,
-- it has this name (in the Category namespace).
- cat_title varbinary(255) NOT NULL,
+ cat_title varchar(255) binary NOT NULL,
-- The numbers of member pages (including categories and media), subcatego-
-- ries, and Image: namespace members, respectively. These are signed to
eu_local_id int unsigned NOT NULL PRIMARY KEY,
-- Some opaque identifier provided by the external database
- eu_external_id varbinary(255) NOT NULL
+ eu_external_id varchar(255) binary NOT NULL
) /*$wgDBTableOptions*/;
CREATE UNIQUE INDEX /*i*/eu_external_id ON /*_*/external_user (eu_external_id);
ll_lang varbinary(20) NOT NULL default '',
-- Title of the target, including namespace
- ll_title varbinary(255) NOT NULL default ''
+ ll_title varchar(255) binary NOT NULL default ''
) /*$wgDBTableOptions*/;
CREATE UNIQUE INDEX /*i*/ll_from ON /*_*/langlinks (ll_from, ll_lang);
iwl_prefix varbinary(20) NOT NULL default '',
-- Title of the target, including namespace
- iwl_title varbinary(255) NOT NULL default ''
+ iwl_title varchar(255) binary NOT NULL default ''
) /*$wgDBTableOptions*/;
CREATE UNIQUE INDEX /*i*/iwl_from ON /*_*/iwlinks (iwl_from, iwl_prefix, iwl_title);
ipb_by int unsigned NOT NULL default 0,
-- User name of blocker
- ipb_by_text varbinary(255) NOT NULL default '',
+ ipb_by_text varchar(255) binary NOT NULL default '',
-- Text comment made by blocker.
ipb_reason tinyblob NOT NULL,
-- Filename.
-- This is also the title of the associated description page,
-- which will be in namespace 6 (NS_FILE).
- img_name varbinary(255) NOT NULL default '' PRIMARY KEY,
+ img_name varchar(255) binary NOT NULL default '' PRIMARY KEY,
-- File size in bytes.
img_size int unsigned NOT NULL default 0,
-- user_id and user_name of uploader.
img_user int unsigned NOT NULL default 0,
- img_user_text varbinary(255) NOT NULL,
+ img_user_text varchar(255) binary NOT NULL,
-- Time of the upload.
img_timestamp varbinary(14) NOT NULL default '',
--
CREATE TABLE /*_*/oldimage (
-- Base filename: key to image.img_name
- oi_name varbinary(255) NOT NULL default '',
+ 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 varbinary(255) NOT NULL default '',
+ oi_archive_name varchar(255) binary NOT NULL default '',
-- Other fields as in image...
oi_size int unsigned NOT NULL default 0,
oi_bits int NOT NULL default 0,
oi_description tinyblob NOT NULL,
oi_user int unsigned NOT NULL default 0,
- oi_user_text varbinary(255) NOT NULL,
+ oi_user_text varchar(255) binary NOT NULL,
oi_timestamp binary(14) NOT NULL default '',
oi_metadata mediumblob NOT NULL,
fa_id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
-- Original base filename; key to image.img_name, page.page_title, etc
- fa_name varbinary(255) NOT NULL default '',
+ fa_name varchar(255) binary NOT NULL default '',
-- Filename of archived file, if an old revision
- fa_archive_name varbinary(255) default '',
+ fa_archive_name varchar(255) binary default '',
-- Which storage bin (directory tree or object store) the file data
-- is stored in. Should be 'deleted' for files that have been deleted;
fa_minor_mime varbinary(100) default "unknown",
fa_description tinyblob,
fa_user int unsigned default 0,
- fa_user_text varbinary(255),
+ fa_user_text varchar(255) binary,
fa_timestamp binary(14) default '',
-- Visibility of deleted revisions, bitfield
CREATE INDEX /*i*/fa_user_timestamp ON /*_*/filearchive (fa_user_text,fa_timestamp);
+--
+-- Store information about newly uploaded files before they're
+-- moved into the actual filestore
+--
+CREATE TABLE /*_*/uploadstash (
+ us_id int unsigned NOT NULL PRIMARY KEY auto_increment,
+
+ -- the user who uploaded the file.
+ us_user int unsigned NOT NULL,
+
+ -- file key. this is how applications actually search for the file.
+ -- this might go away, or become the primary key.
+ us_key varchar(255) NOT NULL,
+
+ -- the original path
+ us_orig_path varchar(255) NOT NULL,
+
+ -- the temporary path at which the file is actually stored
+ us_path varchar(255) NOT NULL,
+
+ -- which type of upload the file came from (sometimes)
+ us_source_type varchar(50),
+
+ -- the date/time on which the file was added
+ us_timestamp varbinary(14) not null,
+
+ us_status varchar(50) not null,
+
+ -- file properties from File::getPropsFromPath. these may prove unnecessary.
+ --
+ us_size int unsigned NOT NULL,
+ -- this hash comes from File::sha1Base36(), and is 31 characters
+ 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,
+ -- image-specific properties
+ us_image_width int unsigned,
+ us_image_height int unsigned,
+ us_image_bits smallint unsigned
+
+) /*$wgDBTableOptions*/;
+
+-- sometimes there's a delete for all of a user's stuff.
+CREATE INDEX /*i*/us_user ON /*_*/uploadstash (us_user);
+-- pick out files by key, enforce key uniqueness
+CREATE UNIQUE INDEX /*i*/us_key ON /*_*/uploadstash (us_key);
+-- the abandoned upload cleanup script needs this
+CREATE INDEX /*i*/us_timestamp ON /*_*/uploadstash (us_timestamp);
+
+
--
-- Primarily a summary table for Special:Recentchanges,
-- this table contains some additional info on edits from
CREATE TABLE /*_*/recentchanges (
rc_id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
rc_timestamp varbinary(14) NOT NULL default '',
+
+ -- This is no longer used
rc_cur_time varbinary(14) NOT NULL default '',
-- As in revision
rc_user int unsigned NOT NULL default 0,
- rc_user_text varbinary(255) NOT NULL,
+ rc_user_text varchar(255) binary NOT NULL,
-- When pages are renamed, their RC entries do _not_ change.
rc_namespace int NOT NULL default 0,
- rc_title varbinary(255) NOT NULL default '',
+ rc_title varchar(255) binary NOT NULL default '',
-- as in revision...
- rc_comment varbinary(255) NOT NULL default '',
+ rc_comment varchar(255) binary NOT NULL default '',
rc_minor tinyint unsigned NOT NULL default 0,
-- Edits by user accounts with the 'bot' rights key are
-- default view.
rc_bot tinyint unsigned NOT NULL default 0,
+ -- Set if this change corresponds to a page creation
rc_new tinyint unsigned NOT NULL default 0,
-- Key to page_id (was cur_id prior to 1.5).
-- rev_id of the prior revision, for generating diff links.
rc_last_oldid int unsigned NOT NULL default 0,
- -- These may no longer be used, with the new move log.
+ -- The type of change entry (RC_EDIT,RC_NEW,RC_LOG)
rc_type tinyint unsigned NOT NULL default 0,
+
+ -- These may no longer be used, with the new move log.
rc_moved_to_ns tinyint unsigned NOT NULL default 0,
- rc_moved_to_title varbinary(255) NOT NULL default '',
+ 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
-- Note that users may watch pages which do not exist yet,
-- or existed in the past but have been deleted.
wl_namespace int NOT NULL default 0,
- wl_title varbinary(255) NOT NULL default '',
+ 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.
CREATE INDEX /*i*/namespace_title ON /*_*/watchlist (wl_namespace, wl_title);
---
--- Used by the math module to keep track
--- of previously-rendered items.
---
-CREATE TABLE /*_*/math (
- -- Binary MD5 hash of the latex fragment, used as an identifier key.
- math_inputhash varbinary(16) NOT NULL,
-
- -- Not sure what this is, exactly...
- math_outputhash varbinary(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 NOT NULL,
-
- -- HTML output from texvc, if any
- math_html text,
-
- -- MathML output from texvc, if any
- math_mathml text
-) /*$wgDBTableOptions*/;
-
-CREATE UNIQUE INDEX /*i*/math_inputhash ON /*_*/math (math_inputhash);
-
-
--
-- When using the default MySQL search backend, page titles
-- and text are munged to strip markup, do Unicode case folding,
si_page int unsigned NOT NULL,
-- Munged version of title
- si_title varbinary(255) NOT NULL default '',
+ si_title varchar(255) NOT NULL default '',
-- Munged version of body text
si_text mediumtext NOT NULL
--
CREATE TABLE /*_*/interwiki (
-- The interwiki prefix, (e.g. "Meatball", or the language prefix "de")
- iw_prefix varbinary(32) NOT NULL,
+ iw_prefix varchar(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
iw_api blob NOT NULL,
-- The name of the database (for a connection to be established with wfGetLB( 'wikiid' ))
- iw_wikiid varbinary(64) NOT NULL,
+ iw_wikiid varchar(64) NOT NULL,
-- A boolean value indicating whether the wiki is in this project
-- (used, for example, to detect redirect loops)
-- Target namespace+title
qc_namespace int NOT NULL default 0,
- qc_title varbinary(255) NOT NULL default ''
+ qc_title varchar(255) binary NOT NULL default ''
) /*$wgDBTableOptions*/;
CREATE INDEX /*i*/qc_type ON /*_*/querycache (qc_type,qc_value);
log_user int unsigned NOT NULL default 0,
-- Name of the user who performed this action
- log_user_text varbinary(255) NOT NULL default '',
+ log_user_text varchar(255) binary NOT NULL default '',
-- Key to the page affected. Where a user is the target,
-- this will point to the user page.
log_namespace int NOT NULL default 0,
- log_title varbinary(255) NOT NULL default '',
+ log_title varchar(255) binary NOT NULL default '',
log_page int unsigned NULL,
-- Freeform text. Interpreted as edit history comments.
- log_comment varbinary(255) NOT NULL default '',
+ log_comment varchar(255) NOT NULL default '',
-- LF separated list of miscellaneous parameters
log_params blob NOT NULL,
CREATE INDEX /*i*/times ON /*_*/logging (log_timestamp);
CREATE INDEX /*i*/log_user_type_time ON /*_*/logging (log_user, log_type, log_timestamp);
CREATE INDEX /*i*/log_page_id_time ON /*_*/logging (log_page,log_timestamp);
+CREATE INDEX /*i*/type_action ON /*_*/logging(log_type, log_action, log_timestamp);
CREATE TABLE /*_*/log_search (
-- The type of ID (rev ID, log ID, rev timestamp, username)
ls_field varbinary(32) NOT NULL,
-- The value of the ID
- ls_value varbinary(255) NOT NULL,
+ ls_value varchar(255) NOT NULL,
-- Key to log_id
ls_log_id int unsigned NOT NULL default 0
) /*$wgDBTableOptions*/;
CREATE INDEX /*i*/ls_log_id ON /*_*/log_search (ls_log_id);
-CREATE TABLE /*_*/trackbacks (
- tb_id int PRIMARY KEY AUTO_INCREMENT,
- tb_page int REFERENCES /*_*/page(page_id) ON DELETE CASCADE,
- tb_title varbinary(255) NOT NULL,
- tb_url blob NOT NULL,
- tb_ex text,
- tb_name varbinary(255)
-) /*$wgDBTableOptions*/;
-CREATE INDEX /*i*/tb_page ON /*_*/trackbacks (tb_page);
-
-
-- Jobs performed by parallel apache threads or a command-line daemon
CREATE TABLE /*_*/job (
job_id int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
-- Namespace and title to act on
-- Should be 0 and '' if the command does not operate on a title
job_namespace int NOT NULL,
- job_title varbinary(255) NOT NULL,
+ job_title varchar(255) binary NOT NULL,
-- Any other parameters to the command
-- Stored as a PHP serialized array, or an empty string if there are no parameters
-- and deletions may refer to different page records as time
-- goes by.
rd_namespace int NOT NULL default 0,
- rd_title varbinary(255) NOT NULL default '',
- rd_interwiki varbinary(32) default NULL,
- rd_fragment varbinary(255) default NULL
+ rd_title varchar(255) binary NOT NULL default '',
+ rd_interwiki varchar(32) default NULL,
+ rd_fragment varchar(255) binary default NULL
) /*$wgDBTableOptions*/;
CREATE INDEX /*i*/rd_ns_title ON /*_*/redirect (rd_namespace,rd_title,rd_from);
-- Target namespace+title
qcc_namespace int NOT NULL default 0,
- qcc_title varbinary(255) NOT NULL default '',
+ qcc_title varchar(255) binary NOT NULL default '',
-- Target namespace+title2
qcc_namespacetwo int NOT NULL default 0,
- qcc_titletwo varbinary(255) NOT NULL default ''
+ qcc_titletwo varchar(255) binary NOT NULL default ''
) /*$wgDBTableOptions*/;
CREATE INDEX /*i*/qcc_type ON /*_*/querycachetwo (qcc_type,qcc_value);
-- Protected titles - nonexistent pages that have been protected
CREATE TABLE /*_*/protected_titles (
pt_namespace int NOT NULL,
- pt_title varbinary(255) NOT NULL,
+ pt_title varchar(255) binary NOT NULL,
pt_user int unsigned NOT NULL,
pt_reason tinyblob,
pt_timestamp binary(14) NOT NULL,
-- A table to log updates, one text key row per update.
CREATE TABLE /*_*/updatelog (
- ul_key varbinary(255) NOT NULL PRIMARY KEY,
+ ul_key varchar(255) NOT NULL PRIMARY KEY,
ul_value blob
) /*$wgDBTableOptions*/;
-- REVID for the change
ct_rev_id int NULL,
-- Tag applied
- ct_tag varbinary(255) NOT NULL,
+ ct_tag varchar(255) NOT NULL,
-- Parameters for the tag, presently unused
ct_params blob NULL
) /*$wgDBTableOptions*/;
CREATE TABLE /*_*/valid_tag (
- vt_tag varbinary(255) NOT NULL PRIMARY KEY
+ vt_tag varchar(255) NOT NULL PRIMARY KEY
) /*$wgDBTableOptions*/;
-- Table for storing localisation data
-- Language code
lc_lang varbinary(32) NOT NULL,
-- Cache key
- lc_key varbinary(255) NOT NULL,
+ lc_key varchar(255) NOT NULL,
-- Value
lc_value mediumblob NOT NULL
) /*$wgDBTableOptions*/;
) /*$wgDBTableOptions*/;
CREATE UNIQUE INDEX /*i*/md_module_skin ON /*_*/module_deps (md_module, md_skin);
+-- Table for holding configuration changes
+CREATE TABLE /*_*/config (
+ -- Config var name
+ cf_name varbinary(255) NOT NULL PRIMARY KEY,
+ -- Config var value
+ cf_value blob NOT NULL
+) /*$wgDBTableOptions*/;
+-- Should cover *most* configuration - strings, ints, bools, etc.
+CREATE INDEX /*i*/cf_name_value ON /*_*/config (cf_name,cf_value(255));
+
-- vim: sw=2 sts=2 et