user_newpassword NVARCHAR(255) NOT NULL DEFAULT '',
user_newpass_time varchar(14) NULL DEFAULT NULL,
user_email NVARCHAR(255) NOT NULL DEFAULT '',
- user_options NVARCHAR(MAX) NOT NULL DEFAULT '',
user_touched varchar(14) NOT NULL DEFAULT '',
user_token NCHAR(32) NOT NULL DEFAULT '',
user_email_authenticated varchar(14) DEFAULT NULL,
CREATE UNIQUE CLUSTERED INDEX /*i*/user_properties_user_property ON /*_*/user_properties (up_user,up_property);
CREATE INDEX /*i*/user_properties_property ON /*_*/user_properties (up_property);
+--
+-- This table contains a user's bot passwords: passwords that allow access to
+-- the account via the API with limited rights.
+--
+CREATE TABLE /*_*/bot_passwords (
+ bp_user int NOT NULL REFERENCES /*_*/mwuser(user_id) ON DELETE CASCADE,
+ bp_app_id nvarchar(32) NOT NULL,
+ bp_password nvarchar(255) NOT NULL,
+ bp_token nvarchar(255) NOT NULL,
+ bp_restrictions nvarchar(max) NOT NULL,
+ bp_grants nvarchar(max) NOT NULL,
+ PRIMARY KEY (bp_user, bp_app_id)
+);
+
--
-- Core of the wiki: each page has an entry here which identifies
--
CREATE TABLE /*_*/pagelinks (
pl_from INT NOT NULL REFERENCES /*_*/page(page_id) ON DELETE CASCADE,
+ pl_from_namespace int NOT NULL DEFAULT 0,
pl_namespace INT NOT NULL DEFAULT 0,
pl_title NVARCHAR(255) NOT NULL DEFAULT '',
);
CREATE UNIQUE INDEX /*i*/pl_from ON /*_*/pagelinks (pl_from,pl_namespace,pl_title);
CREATE UNIQUE INDEX /*i*/pl_namespace ON /*_*/pagelinks (pl_namespace,pl_title,pl_from);
+CREATE INDEX /*i*/pl_backlinks_namespace ON /*_*/pagelinks (pl_from_namespace,pl_namespace,pl_title,pl_from);
--
--
CREATE TABLE /*_*/templatelinks (
tl_from int NOT NULL REFERENCES /*_*/page(page_id) ON DELETE CASCADE,
+ tl_from_namespace int NOT NULL default 0,
tl_namespace int NOT NULL default 0,
tl_title nvarchar(255) NOT NULL default ''
);
CREATE UNIQUE INDEX /*i*/tl_from ON /*_*/templatelinks (tl_from,tl_namespace,tl_title);
CREATE UNIQUE INDEX /*i*/tl_namespace ON /*_*/templatelinks (tl_namespace,tl_title,tl_from);
+CREATE INDEX /*i*/tl_backlinks_namespace ON /*_*/templatelinks (tl_from_namespace,tl_namespace,tl_title,tl_from);
--
CREATE TABLE /*_*/imagelinks (
-- Key to page_id of the page containing the image / media link.
il_from int NOT NULL REFERENCES /*_*/page(page_id) ON DELETE CASCADE,
+ il_from_namespace int NOT NULL default 0,
-- Filename of target image.
-- This is also the page_title of the file's description page;
CREATE UNIQUE INDEX /*i*/il_from ON /*_*/imagelinks (il_from,il_to);
CREATE UNIQUE INDEX /*i*/il_to ON /*_*/imagelinks (il_to,il_from);
+CREATE INDEX /*i*/il_backlinks_namespace ON /*_*/imagelinks (il_from_namespace,il_to,il_from);
--
-- Track category inclusions *used inline*
-- Used by the API (and some extensions)
CREATE INDEX /*i*/cl_timestamp ON /*_*/categorylinks (cl_to,cl_timestamp);
--- FIXME: Not used, delete this
-CREATE INDEX /*i*/cl_collation ON /*_*/categorylinks (cl_collation);
+-- Used when updating collation (e.g. updateCollation.php)
+CREATE INDEX /*i*/cl_collation_ext ON /*_*/categorylinks (cl_collation, cl_to, cl_type, cl_from);
--
-- Track all existing categories. Something is a category if 1) it has an en-
CREATE INDEX /*i*/el_from ON /*_*/externallinks (el_from);
CREATE INDEX /*i*/el_index ON /*_*/externallinks (el_index);
+-- el_to index intentionally not added; we cannot index nvarchar(max) columns,
+-- but we also cannot restrict el_to to a smaller column size as the external
+-- link may be larger.
--
-- Track interlanguage links
-- 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 0x PRIMARY KEY,
+ img_name nvarchar(255) NOT NULL default '' PRIMARY KEY,
-- File size in bytes.
img_size int NOT NULL default 0,
--
CREATE TABLE /*_*/oldimage (
-- Base filename: key to image.img_name
- oi_name varbinary(255) NOT NULL default 0x REFERENCES /*_*/image(img_name) ON DELETE CASCADE ON UPDATE CASCADE,
+ -- Not a FK because deleting images removes them from image
+ oi_name nvarchar(255) 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 0x,
+ oi_archive_name nvarchar(255) NOT NULL default '',
-- Other fields as in image...
oi_size int NOT NULL default 0,
oi_user_text nvarchar(255) NOT NULL,
oi_timestamp varchar(14) NOT NULL default '',
- oi_metadata nvarchar(max) NOT NULL,
+ oi_metadata varbinary(max) NOT NULL,
oi_media_type varchar(16) default null,
oi_major_mime varchar(16) not null default 'unknown',
oi_minor_mime nvarchar(100) NOT NULL default 'unknown',
CREATE INDEX /*i*/oi_usertext_timestamp ON /*_*/oldimage (oi_user_text,oi_timestamp);
CREATE INDEX /*i*/oi_name_timestamp ON /*_*/oldimage (oi_name,oi_timestamp);
--- oi_archive_name truncated to 14 to avoid key length overflow
CREATE INDEX /*i*/oi_name_archive_name ON /*_*/oldimage (oi_name,oi_archive_name);
CREATE INDEX /*i*/oi_sha1 ON /*_*/oldimage (oi_sha1);
fa_size int default 0,
fa_width int default 0,
fa_height int default 0,
- fa_metadata nvarchar(max),
+ fa_metadata varbinary(max),
fa_bits int default 0,
fa_media_type varchar(16) default null,
fa_major_mime varchar(16) not null default 'unknown',
rc_id int NOT NULL PRIMARY KEY IDENTITY,
rc_timestamp varchar(14) not null default '',
- -- This is no longer used
- -- Field kept in database for downgrades
- -- @todo: add drop patch with 1.24
- rc_cur_time varchar(14) NOT NULL default '',
-
-- As in revision
rc_user int NOT NULL default 0 REFERENCES /*_*/mwuser(user_id),
rc_user_text nvarchar(255) NOT NULL,
CREATE TABLE /*_*/page_props (
pp_page int NOT NULL REFERENCES /*_*/page(page_id) ON DELETE CASCADE,
pp_propname nvarchar(60) NOT NULL,
- pp_value nvarchar(max) NOT NULL
+ pp_value nvarchar(max) NOT NULL,
+ pp_sortkey float DEFAULT NULL
);
CREATE UNIQUE INDEX /*i*/pp_page_propname ON /*_*/page_props (pp_page,pp_propname);
CREATE UNIQUE INDEX /*i*/pp_propname_page ON /*_*/page_props (pp_propname,pp_page);
+CREATE UNIQUE INDEX /*i*/pp_propname_sortkey_page ON /*_*/page_props (pp_propname,pp_sortkey,pp_page);
-- A table to log updates, one text key row per update.