X-Git-Url: https://git.cyclocoop.org/%7B%24admin_url%7Dmembres/modifier.php?a=blobdiff_plain;f=maintenance%2Fmssql%2Ftables.sql;h=467326403ebdf5a787420fe6adfd5ca7848eaf43;hb=bb8c060128a79532e1b6885fb3ebd6174d3f4d4d;hp=2a672945ea3a7ebf1424d922ab6d8071ec9d5c92;hpb=be656c67a2dde9924e8558563162616ab3ab88dc;p=lhc%2Fweb%2Fwiklou.git diff --git a/maintenance/mssql/tables.sql b/maintenance/mssql/tables.sql index 2a672945ea..65ede90ea8 100644 --- a/maintenance/mssql/tables.sql +++ b/maintenance/mssql/tables.sql @@ -54,6 +54,23 @@ CREATE INDEX /*i*/user_email ON /*_*/mwuser (user_email); -- Insert a dummy user to represent anons INSERT INTO /*_*/mwuser (user_name) VALUES ('##Anonymous##'); +-- +-- The "actor" table associates user names or IP addresses with integers for +-- the benefit of other tables that need to refer to either logged-in or +-- logged-out users. If something can only ever be done by logged-in users, it +-- can refer to the user table directly. +-- +CREATE TABLE /*_*/actor ( + actor_id bigint unsigned NOT NULL CONSTRAINT PK_actor PRIMARY KEY IDENTITY(0,1), + actor_user int unsigned, + actor_name nvarchar(255) NOT NULL +); +CREATE UNIQUE INDEX /*i*/actor_user ON /*_*/actor (actor_user); +CREATE UNIQUE INDEX /*i*/actor_name ON /*_*/actor (actor_name); + +-- Insert a dummy actor to represent no actor +INSERT INTO /*_*/actor (actor_name) VALUES ('##Anonymous##'); + -- -- User permissions have been broken out to a separate table; -- this allows sites with a shared user table to have different @@ -117,6 +134,28 @@ CREATE TABLE /*_*/bot_passwords ( ); +-- +-- 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 ( + comment_id bigint unsigned NOT NULL PRIMARY KEY IDENTITY(0,1), + comment_hash INT NOT NULL, + comment_text nvarchar(max) NOT NULL, + comment_data nvarchar(max) +); +-- Index used for deduplication. +CREATE INDEX /*i*/comment_hash ON /*_*/comment (comment_hash); + +-- dummy row for FKs. Hash is intentionally wrong so CommentStore won't match it. +INSERT INTO /*_*/comment (comment_hash, comment_text) VALUES (-1, '** dummy **'); + + -- -- Core of the wiki: each page has an entry here which identifies -- it by title and contains some essential metadata. @@ -152,8 +191,8 @@ INSERT INTO /*_*/page (page_namespace, page_title, page_restrictions, page_lates CREATE TABLE /*_*/revision ( rev_id INT NOT NULL UNIQUE IDENTITY(0,1), rev_page INT NOT NULL REFERENCES /*_*/page(page_id) ON DELETE CASCADE, - rev_text_id INT NOT NULL, -- FK added later - rev_comment NVARCHAR(255) NOT NULL, + rev_text_id INT NOT NULL CONSTRAINT DF_rev_text_id DEFAULT 0, -- FK added later + rev_comment NVARCHAR(255) NOT NULL CONSTRAINT DF_rev_comment DEFAULT '', rev_user INT REFERENCES /*_*/mwuser(user_id) ON DELETE SET NULL, rev_user_text NVARCHAR(255) NOT NULL DEFAULT '', rev_timestamp varchar(14) NOT NULL default '', @@ -177,6 +216,31 @@ INSERT INTO /*_*/revision (rev_page,rev_text_id,rev_comment,rev_user,rev_len) VA ALTER TABLE /*_*/page ADD CONSTRAINT FK_page_latest_page_id FOREIGN KEY (page_latest) REFERENCES /*_*/revision(rev_id); +-- +-- Temporary tables 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 ( + revcomment_rev INT NOT NULL CONSTRAINT FK_revcomment_rev FOREIGN KEY REFERENCES /*_*/revision(rev_id) ON DELETE CASCADE, + revcomment_comment_id bigint unsigned NOT NULL CONSTRAINT FK_revcomment_comment_id FOREIGN KEY REFERENCES /*_*/comment(comment_id), + CONSTRAINT PK_revision_comment_temp PRIMARY KEY (revcomment_rev, revcomment_comment_id) +); +CREATE UNIQUE INDEX /*i*/revcomment_rev ON /*_*/revision_comment_temp (revcomment_rev); + +CREATE TABLE /*_*/revision_actor_temp ( + revactor_rev int unsigned NOT NULL CONSTRAINT FK_revactor_rev FOREIGN KEY REFERENCES /*_*/revision(rev_id) ON DELETE CASCADE, + revactor_actor bigint unsigned NOT NULL, + revactor_timestamp varchar(14) NOT NULL CONSTRAINT DF_revactor_timestamp DEFAULT '', + revactor_page int unsigned NOT NULL, + CONSTRAINT PK_revision_actor_temp PRIMARY KEY (revactor_rev, revactor_actor) +); +CREATE UNIQUE INDEX /*i*/revactor_rev ON /*_*/revision_actor_temp (revactor_rev); +CREATE INDEX /*i*/actor_timestamp ON /*_*/revision_actor_temp (revactor_actor,revactor_timestamp); +CREATE INDEX /*i*/page_actor_timestamp ON /*_*/revision_actor_temp (revactor_page,revactor_actor,revactor_timestamp); + -- -- Holds TEXT of individual page revisions. -- @@ -206,15 +270,15 @@ CREATE TABLE /*_*/archive ( ar_id int NOT NULL PRIMARY KEY IDENTITY, ar_namespace SMALLINT NOT NULL DEFAULT 0, ar_title NVARCHAR(255) NOT NULL DEFAULT '', - ar_text NVARCHAR(MAX) NOT NULL, - ar_comment NVARCHAR(255) NOT NULL, + ar_comment NVARCHAR(255) NOT NULL CONSTRAINT DF_ar_comment DEFAULT '', + ar_comment_id bigint unsigned NOT NULL CONSTRAINT DF_ar_comment_id DEFAULT 0 CONSTRAINT FK_ar_comment_id FOREIGN KEY REFERENCES /*_*/comment(comment_id), ar_user INT CONSTRAINT ar_user__user_id__fk FOREIGN KEY REFERENCES /*_*/mwuser(user_id), - ar_user_text NVARCHAR(255) NOT NULL, + ar_user_text NVARCHAR(255) NOT NULL CONSTRAINT DF_ar_user_text DEFAULT '', + ar_actor bigint unsigned NOT NULL CONSTRAINT DF_ar_actor DEFAULT 0, ar_timestamp varchar(14) NOT NULL default '', ar_minor_edit BIT NOT NULL DEFAULT 0, - ar_flags NVARCHAR(255) NOT NULL, - ar_rev_id INT NULL, -- NOT a FK, the row gets deleted from revision and moved here - ar_text_id INT CONSTRAINT ar_text_id__old_id__fk FOREIGN KEY REFERENCES /*_*/text(old_id) ON DELETE CASCADE, + ar_rev_id INT NOT NULL, -- NOT a FK, the row gets deleted from revision and moved here + ar_text_id INT NOT NULL CONSTRAINT DF_ar_text_id DEFAULT 0 CONSTRAINT ar_text_id__old_id__fk FOREIGN KEY REFERENCES /*_*/text(old_id) ON DELETE CASCADE, ar_deleted TINYINT NOT NULL DEFAULT 0, ar_len INT, ar_page_id INT NULL, -- NOT a FK, the row gets deleted from page and moved here @@ -225,9 +289,81 @@ CREATE TABLE /*_*/archive ( ); CREATE INDEX /*i*/name_title_timestamp ON /*_*/archive (ar_namespace,ar_title,ar_timestamp); CREATE INDEX /*i*/ar_usertext_timestamp ON /*_*/archive (ar_user_text,ar_timestamp); +CREATE INDEX /*i*/ar_actor_timestamp ON /*_*/archive (ar_actor,ar_timestamp); 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 CONSTRAINT FK_slots_slot_role FOREIGN KEY REFERENCES slot_roles(role_id), + + -- reference to content_id + slot_content_id bigint unsigned NOT NULL CONSTRAINT FK_slots_content_id FOREIGN KEY REFERENCES content(content_id), + + -- The revision ID of the revision that originated the slot's content. + -- To find revisions that changed slots, look for slot_origin = slot_revision_id. + slot_origin bigint NOT NULL, + + CONSTRAINT PK_slots PRIMARY KEY (slot_revision_id, slot_role_id) +); + +-- Index for finding revisions that modified a specific slot +CREATE INDEX /*i*/slot_revision_origin_role ON /*_*/slots (slot_revision_id, slot_origin, slot_role_id); + +-- +-- 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 NOT NULL CONSTRAINT PK_content PRIMARY KEY IDENTITY, + + -- 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 varchar(32) NOT NULL, + + -- reference to model_id + content_model smallint unsigned NOT NULL CONSTRAINT FK_content_content_models FOREIGN KEY REFERENCES /*_*/content_models(model_id), + + -- URL-like address of the content blob + content_address nvarchar(255) NOT NULL +); + +-- +-- Normalization table for role names +-- +CREATE TABLE /*_*/slot_roles ( + role_id smallint NOT NULL CONSTRAINT PK_slot_roles PRIMARY KEY IDENTITY, + role_name nvarchar(64) NOT NULL +); + +-- 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 NOT NULL CONSTRAINT PK_content_models PRIMARY KEY IDENTITY, + model_name nvarchar(64) NOT NULL +); + +-- 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. -- @@ -450,34 +586,27 @@ CREATE INDEX /*i*/iwl_prefix_from_title ON /*_*/iwlinks (iwl_prefix, iwl_from, i -- CREATE TABLE /*_*/site_stats ( -- The single row should contain 1 here. - ss_row_id int NOT NULL, + ss_row_id int NOT NULL CONSTRAINT /*i*/ss_row_id PRIMARY KEY, -- Total number of edits performed. - ss_total_edits bigint default 0, + ss_total_edits bigint default NULL, - -- An approximate count of pages matching the following criteria: - -- * in namespace 0 - -- * not a redirect - -- * contains the text '[[' - -- See Article::isCountable() in includes/Article.php - ss_good_articles bigint default 0, + -- See SiteStatsInit::articles(). + ss_good_articles bigint default NULL, - -- Total pages, theoretically equal to SELECT COUNT(*) FROM page; except faster - ss_total_pages bigint default '-1', + -- Total pages, theoretically equal to SELECT COUNT(*) FROM page. + ss_total_pages bigint default NULL, - -- Number of users, theoretically equal to SELECT COUNT(*) FROM user; - ss_users bigint default '-1', + -- Number of users, theoretically equal to SELECT COUNT(*) FROM user. + ss_users bigint default NULL, - -- Number of users that still edit - ss_active_users bigint default '-1', + -- Number of users that still edit. + ss_active_users bigint default NULL, - -- Number of images, equivalent to SELECT COUNT(*) FROM image - ss_images int default 0 + -- Number of images, equivalent to SELECT COUNT(*) FROM image. + ss_images bigint default NULL ); --- 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 @@ -496,11 +625,18 @@ CREATE TABLE /*_*/ipblocks ( -- User ID who made the block. ipb_by int REFERENCES /*_*/mwuser(user_id) ON DELETE CASCADE, + -- Actor ID who made the block. + ipb_by_actor bigint unsigned NOT NULL CONSTRAINT DF_ipb_by_actor DEFAULT 0, + -- User name of blocker ipb_by_text nvarchar(255) NOT NULL default '', -- Text comment made by blocker. - ipb_reason nvarchar(255) NOT NULL, + ipb_reason nvarchar(255) NOT NULL CONSTRAINT DF_ipb_reason 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 CONSTRAINT DF_ipb_reason_id DEFAULT 0 CONSTRAINT FK_ipb_reason_id FOREIGN KEY REFERENCES /*_*/comment(comment_id), -- Creation (or refresh) date in standard YMDHMS form. -- IP blocks expire automatically. @@ -597,11 +733,13 @@ CREATE TABLE /*_*/image ( -- Description field as entered by the uploader. -- This is displayed in image upload history and logs. - img_description nvarchar(255) NOT NULL, + img_description nvarchar(255) NOT NULL CONSTRAINT DF_img_description DEFAULT '', + img_description_id bigint NOT NULL CONSTRAINT DF_img_description_id DEFAULT 0 CONSTRAINT FK_img_description_id FOREIGN KEY REFERENCES /*_*/comment(comment_id), -- user_id and user_name of uploader. img_user int REFERENCES /*_*/mwuser(user_id) ON DELETE SET NULL, - img_user_text nvarchar(255) NOT NULL, + img_user_text nvarchar(255) NOT NULL CONSTRAINT DF_img_user_text DEFAULT '', + img_actor bigint unsigned NOT NULL CONSTRAINT DF_img_actor DEFAULT 0, -- Time of the upload. img_timestamp nvarchar(14) NOT NULL default '', @@ -614,6 +752,7 @@ CREATE TABLE /*_*/image ( ); CREATE INDEX /*i*/img_usertext_timestamp ON /*_*/image (img_user_text,img_timestamp); +CREATE INDEX /*i*/img_actor_timestamp ON /*_*/image (img_actor, img_timestamp); -- Used by Special:ListFiles for sort-by-size CREATE INDEX /*i*/img_size ON /*_*/image (img_size); -- Used by Special:Newimages and Special:ListFiles @@ -623,6 +762,20 @@ CREATE INDEX /*i*/img_sha1 ON /*_*/image (img_sha1); -- 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 ( + imgcomment_name nvarchar(255) NOT NULL CONSTRAINT FK_imgcomment_name FOREIGN KEY REFERENCES /*_*/image(imgcomment_name) ON DELETE CASCADE, + imgcomment_description_id bigint unsigned NOT NULL CONSTRAINT FK_imgcomment_description_id FOREIGN KEY REFERENCES /*_*/comment(comment_id), + CONSTRAINT PK_image_comment_temp PRIMARY KEY (imgcomment_name, imgcomment_description_id) +); +CREATE UNIQUE INDEX /*i*/imgcomment_name ON /*_*/image_comment_temp (imgcomment_name); + -- -- Previous revisions of uploaded files. @@ -643,9 +796,11 @@ CREATE TABLE /*_*/oldimage ( oi_width int NOT NULL default 0, oi_height int NOT NULL default 0, oi_bits int NOT NULL default 0, - oi_description nvarchar(255) NOT NULL, + oi_description nvarchar(255) NOT NULL CONSTRAINT DF_oi_description DEFAULT '', + oi_description_id bigint unsigned NOT NULL CONSTRAINT DF_oi_description_id DEFAULT 0 CONSTRAINT FK_oi_description_id FOREIGN KEY REFERENCES /*_*/comment(comment_id), oi_user int REFERENCES /*_*/mwuser(user_id), - oi_user_text nvarchar(255) NOT NULL, + oi_user_text nvarchar(255) NOT NULL CONSTRAINT DF_oi_user_text DEFAULT '', + oi_actor bigint unsigned NOT NULL CONSTRAINT DF_oi_actor DEFAULT 0, oi_timestamp varchar(14) NOT NULL default '', oi_metadata varbinary(max) NOT NULL, @@ -660,6 +815,7 @@ CREATE TABLE /*_*/oldimage ( ); CREATE INDEX /*i*/oi_usertext_timestamp ON /*_*/oldimage (oi_user_text,oi_timestamp); +CREATE INDEX /*i*/oi_actor_timestamp ON /*_*/oldimage (oi_actor,oi_timestamp); CREATE INDEX /*i*/oi_name_timestamp ON /*_*/oldimage (oi_name,oi_timestamp); CREATE INDEX /*i*/oi_sha1 ON /*_*/oldimage (oi_sha1); @@ -692,7 +848,8 @@ CREATE TABLE /*_*/filearchive ( -- Deletion information, if this file is deleted. fa_deleted_user int, fa_deleted_timestamp varchar(14) default '', - fa_deleted_reason nvarchar(max), + fa_deleted_reason nvarchar(max) CONSTRAINT DF_fa_deleted_reason DEFAULT '', + fa_deleted_reason_id bigint unsigned NOT NULL CONSTRAINT DF_fa_deleted_reason_id DEFAULT 0 CONSTRAINT FK_fa_deleted_reason_id FOREIGN KEY REFERENCES /*_*/comment(comment_id), -- Duped fields from image fa_size int default 0, @@ -703,9 +860,11 @@ CREATE TABLE /*_*/filearchive ( fa_media_type varchar(16) default null, fa_major_mime varchar(16) not null default 'unknown', fa_minor_mime nvarchar(100) default 'unknown', - fa_description nvarchar(255), + fa_description nvarchar(255) CONSTRAINT DF_fa_description DEFAULT '', + fa_description_id bigint unsigned NOT NULL CONSTRAINT DF_fa_description DEFAULT 0 CONSTRAINT FK_fa_description FOREIGN KEY REFERENCES /*_*/comment(comment_id), fa_user int default 0 REFERENCES /*_*/mwuser(user_id) ON DELETE SET NULL, - fa_user_text nvarchar(255), + fa_user_text nvarchar(255) CONSTRAINT DF_fa_user_text DEFAULT '', + fa_actor bigint unsigned NOT NULL CONSTRAINT DF_fa_actor DEFAULT 0, fa_timestamp varchar(14) default '', -- Visibility of deleted revisions, bitfield @@ -726,6 +885,7 @@ CREATE INDEX /*i*/fa_storage_group ON /*_*/filearchive (fa_storage_group, fa_sto CREATE INDEX /*i*/fa_deleted_timestamp ON /*_*/filearchive (fa_deleted_timestamp); -- sort by uploader CREATE INDEX /*i*/fa_user_timestamp ON /*_*/filearchive (fa_user_text,fa_timestamp); +CREATE INDEX /*i*/fa_actor_timestamp ON /*_*/filearchive (fa_actor,fa_timestamp); -- find file by sha1, 10 bytes will be enough for hashes to be indexed CREATE INDEX /*i*/fa_sha1 ON /*_*/filearchive (fa_sha1); @@ -798,7 +958,8 @@ CREATE TABLE /*_*/recentchanges ( -- As in revision rc_user int NOT NULL default 0 CONSTRAINT rc_user__user_id__fk FOREIGN KEY REFERENCES /*_*/mwuser(user_id), - rc_user_text nvarchar(255) NOT NULL, + rc_user_text nvarchar(255) NOT NULL CONSTRAINT DF_rc_user_text DEFAULT '', + rc_actor bigint unsigned NOT NULL CONSTRAINT DF_rc_actor DEFAULT 0, -- When pages are renamed, their RC entries do _not_ change. rc_namespace int NOT NULL default 0, @@ -806,6 +967,7 @@ CREATE TABLE /*_*/recentchanges ( -- as in revision... rc_comment nvarchar(255) NOT NULL default '', + rc_comment_id bigint unsigned NOT NULL CONSTRAINT DF_rc_comment_id DEFAULT 0 CONSTRAINT FK_rc_comment_id FOREIGN KEY REFERENCES /*_*/comment(comment_id), rc_minor bit NOT NULL default 0, -- Edits by user accounts with the 'bot' rights key are @@ -837,8 +999,9 @@ CREATE TABLE /*_*/recentchanges ( -- 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 bit NOT NULL default 0, + -- A value of 1 indicates the page has been reviewed manually. + -- A value of 2 indicates the page has been automatically reviewed. + rc_patrolled tinyint NOT NULL CONSTRAINT DF_rc_patrolled DEFAULT 0 -- Recorded IP address the edit was made from, if the -- $wgPutIPinRC option is enabled. @@ -863,12 +1026,14 @@ CREATE TABLE /*_*/recentchanges ( ); CREATE INDEX /*i*/rc_timestamp ON /*_*/recentchanges (rc_timestamp); -CREATE INDEX /*i*/rc_namespace_title ON /*_*/recentchanges (rc_namespace, rc_title); +CREATE INDEX /*i*/rc_namespace_title_timestamp ON /*_*/recentchanges (rc_namespace, rc_title, rc_timestamp); CREATE INDEX /*i*/rc_cur_id ON /*_*/recentchanges (rc_cur_id); CREATE INDEX /*i*/new_name_timestamp ON /*_*/recentchanges (rc_new,rc_namespace,rc_timestamp); CREATE INDEX /*i*/rc_ip ON /*_*/recentchanges (rc_ip); CREATE INDEX /*i*/rc_ns_usertext ON /*_*/recentchanges (rc_namespace, rc_user_text); CREATE INDEX /*i*/rc_user_text ON /*_*/recentchanges (rc_user_text, rc_timestamp); +CREATE INDEX /*i*/rc_ns_actor ON /*_*/recentchanges (rc_namespace, rc_actor); +CREATE INDEX /*i*/rc_actor ON /*_*/recentchanges (rc_actor, rc_timestamp); CREATE INDEX /*i*/rc_name_type_patrolled_timestamp ON /*_*/recentchanges (rc_namespace, rc_type, rc_patrolled, rc_timestamp); @@ -1000,6 +1165,9 @@ CREATE TABLE /*_*/logging ( -- Name of the user who performed this action log_user_text nvarchar(255) NOT NULL default '', + -- The actor who performed this action + log_actor bigint unsigned NOT NULL CONSTRAINT DF_log_actor DEFAULT 0, + -- 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, @@ -1009,6 +1177,10 @@ CREATE TABLE /*_*/logging ( -- Freeform text. Interpreted as edit history comments. log_comment nvarchar(255) 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 CONSTRAINT DF_log_comment_id DEFAULT 0 CONSTRAINT FK_log_comment_id FOREIGN KEY REFERENCES /*_*/comment(comment_id), + -- miscellaneous parameters: -- LF separated list (old system) or serialized PHP array (new system) log_params nvarchar(max) NOT NULL, @@ -1026,6 +1198,8 @@ 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 INDEX /*i*/log_user_text_type_time ON /*_*/logging (log_user_text, log_type, log_timestamp); CREATE INDEX /*i*/log_user_text_time ON /*_*/logging (log_user_text, log_timestamp); +CREATE INDEX /*i*/actor_time ON /*_*/logging (log_actor, log_timestamp); +CREATE INDEX /*i*/log_actor_type_time ON /*_*/logging (log_actor, log_type, log_timestamp); INSERT INTO /*_*/logging (log_user,log_page,log_params) VALUES(0,0,''); @@ -1169,7 +1343,8 @@ CREATE TABLE /*_*/protected_titles ( pt_namespace int NOT NULL, pt_title nvarchar(255) NOT NULL, pt_user int REFERENCES /*_*/mwuser(user_id) ON DELETE SET NULL, - pt_reason nvarchar(255), + pt_reason nvarchar(255) CONSTRAINT DF_pt_reason DEFAULT '', + pt_reason_id bigint unsigned NOT NULL CONSTRAINT DF_pt_reason_id DEFAULT 0 CONSTRAINT FK_pt_reason_id FOREIGN KEY REFERENCES /*_*/comment(comment_id), pt_timestamp varchar(14) NOT NULL, pt_expiry varchar(14) NOT NULL, pt_create_perm nvarchar(60) NOT NULL