-- 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
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_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 '',
ALTER TABLE /*_*/page ADD CONSTRAINT FK_page_latest_page_id FOREIGN KEY (page_latest) REFERENCES /*_*/revision(rev_id);
--
--- Temporary table to avoid blocking on an alter of revision.
+-- 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
);
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.
--
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 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
);
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);
-- reference to content_id
slot_content_id bigint unsigned NOT NULL CONSTRAINT FK_slots_content_id FOREIGN KEY REFERENCES content(content_id),
- -- whether the content is inherited (1) or new in this revision (0)
- slot_inherited tinyint unsigned NOT NULL CONSTRAINT DF_slot_inherited DEFAULT 0,
+ -- 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_role_inherited ON /*_*/slots (slot_revision_id, slot_role_id, slot_inherited);
+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
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
);
-- 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 '',
-- Description field as entered by the uploader.
-- This is displayed in image upload history and logs.
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 '',
);
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
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,
);
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);
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
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);
-- 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,
-- 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.
);
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);
-- 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,
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,'');
dépôts / lhc / web / wiklou.git / blobdiff