-- 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_id bigint NOT NULL CONSTRAINT PK_actor PRIMARY KEY IDENTITY(0,1),
+ actor_user int,
actor_name nvarchar(255) NOT NULL
);
CREATE UNIQUE INDEX /*i*/actor_user ON /*_*/actor (actor_user);
-- the same comment_text and comment_data.
--
CREATE TABLE /*_*/comment (
- comment_id bigint unsigned NOT NULL PRIMARY KEY IDENTITY(0,1),
+ comment_id bigint NOT NULL PRIMARY KEY IDENTITY(0,1),
comment_hash INT NOT NULL,
comment_text nvarchar(max) NOT NULL,
comment_data nvarchar(max)
--
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),
+ revcomment_comment_id bigint 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_rev int NOT NULL CONSTRAINT FK_revactor_rev FOREIGN KEY REFERENCES /*_*/revision(rev_id) ON DELETE CASCADE,
+ revactor_actor bigint NOT NULL,
revactor_timestamp varchar(14) NOT NULL CONSTRAINT DF_revactor_timestamp DEFAULT '',
- revactor_page int unsigned NOT NULL,
+ revactor_page int 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);
ar_namespace SMALLINT NOT NULL DEFAULT 0,
ar_title NVARCHAR(255) NOT NULL DEFAULT '',
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_comment_id bigint 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 CONSTRAINT DF_ar_user_text DEFAULT '',
- ar_actor bigint unsigned NOT NULL CONSTRAINT DF_ar_actor DEFAULT 0,
+ ar_actor bigint NOT NULL CONSTRAINT DF_ar_actor DEFAULT 0,
ar_timestamp varchar(14) NOT NULL default '',
ar_minor_edit BIT NOT NULL DEFAULT 0,
ar_rev_id INT NOT NULL, -- NOT a FK, the row gets deleted from revision 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);
+CREATE UNIQUE INDEX /*i*/ar_revid_uniq 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.
+-- Normalization table for role names
--
-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),
+CREATE TABLE /*_*/slot_roles (
+ role_id smallint NOT NULL CONSTRAINT PK_slot_roles PRIMARY KEY IDENTITY,
+ role_name nvarchar(64) NOT NULL
+);
- -- 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,
+-- Index for looking of the internal ID of for a name
+CREATE UNIQUE INDEX /*i*/role_name ON /*_*/slot_roles (role_name);
- CONSTRAINT PK_slots PRIMARY KEY (slot_revision_id, slot_role_id)
+--
+-- 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 finding revisions that modified a specific slot
-CREATE INDEX /*i*/slot_revision_origin_role ON /*_*/slots (slot_revision_id, slot_origin, slot_role_id);
+-- Index for looking of the internal ID of for a name
+CREATE UNIQUE INDEX /*i*/model_name ON /*_*/content_models (model_name);
--
-- The content table represents content objects. It's primary purpose is to provide the necessary
CREATE TABLE /*_*/content (
-- ID of the content object
- content_id bigint unsigned NOT NULL CONSTRAINT PK_content PRIMARY KEY IDENTITY,
+ content_id bigint 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,
+ content_size int 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),
+ content_model smallint 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
+-- 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 /*_*/slot_roles (
- role_id smallint NOT NULL CONSTRAINT PK_slot_roles PRIMARY KEY IDENTITY,
- role_name nvarchar(64) NOT NULL
-);
+CREATE TABLE /*_*/slots (
--- Index for looking of the internal ID of for a name
-CREATE UNIQUE INDEX /*i*/role_name ON /*_*/slot_roles (role_name);
+ -- reference to rev_id
+ slot_revision_id bigint NOT NULL,
---
--- 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
+ -- reference to role_id
+ slot_role_id smallint NOT NULL CONSTRAINT FK_slots_slot_role FOREIGN KEY REFERENCES slot_roles(role_id),
+
+ -- reference to content_id
+ slot_content_id bigint 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 looking of the internal ID of for a name
-CREATE UNIQUE INDEX /*i*/model_name ON /*_*/content_models (model_name);
+-- 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);
--
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,
+ ipb_by_actor bigint NOT NULL CONSTRAINT DF_ipb_by_actor DEFAULT 0,
-- User name of blocker
ipb_by_text nvarchar(255) 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 CONSTRAINT DF_ipb_reason_id DEFAULT 0 CONSTRAINT FK_ipb_reason_id FOREIGN KEY REFERENCES /*_*/comment(comment_id),
+ ipb_reason_id bigint 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.
-- 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 CONSTRAINT DF_img_user_text DEFAULT '',
- img_actor bigint unsigned NOT NULL CONSTRAINT DF_img_actor DEFAULT 0,
+ img_actor bigint NOT NULL CONSTRAINT DF_img_actor DEFAULT 0,
-- Time of the upload.
img_timestamp nvarchar(14) NOT NULL default '',
-- 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),
+ imgcomment_name nvarchar(255) NOT NULL CONSTRAINT FK_imgcomment_name FOREIGN KEY REFERENCES /*_*/image(img_name) ON DELETE CASCADE,
+ imgcomment_description_id bigint 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);
oi_height int NOT NULL default 0,
oi_bits int NOT NULL default 0,
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_description_id bigint 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 CONSTRAINT DF_oi_user_text DEFAULT '',
- oi_actor bigint unsigned NOT NULL CONSTRAINT DF_oi_actor DEFAULT 0,
+ oi_actor bigint NOT NULL CONSTRAINT DF_oi_actor DEFAULT 0,
oi_timestamp varchar(14) NOT NULL default '',
oi_metadata varbinary(max) NOT NULL,
fa_deleted_user int,
fa_deleted_timestamp varchar(14) default '',
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),
+ fa_deleted_reason_id bigint 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,
fa_major_mime varchar(16) not null default 'unknown',
fa_minor_mime nvarchar(100) default 'unknown',
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_description_id bigint NOT NULL CONSTRAINT DF_fa_description_id DEFAULT 0 CONSTRAINT FK_fa_description_id FOREIGN KEY REFERENCES /*_*/comment(comment_id),
fa_user int default 0 REFERENCES /*_*/mwuser(user_id) ON DELETE SET NULL,
fa_user_text nvarchar(255) CONSTRAINT DF_fa_user_text DEFAULT '',
- fa_actor bigint unsigned NOT NULL CONSTRAINT DF_fa_actor DEFAULT 0,
+ fa_actor bigint NOT NULL CONSTRAINT DF_fa_actor DEFAULT 0,
fa_timestamp varchar(14) default '',
-- Visibility of deleted revisions, bitfield
-- 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 CONSTRAINT DF_rc_user_text DEFAULT '',
- rc_actor bigint unsigned NOT NULL CONSTRAINT DF_rc_actor DEFAULT 0,
+ rc_actor bigint 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,
-- 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_comment_id bigint 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
--
CREATE TABLE /*_*/interwiki (
-- The interwiki prefix, (e.g. "Meatball", or the language prefix "de")
- iw_prefix nvarchar(32) NOT NULL,
+ iw_prefix nvarchar(32) NOT NULL CONSTRAINT PK_interwiki PRIMARY KEY,
-- 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_trans bit NOT NULL default 0
);
-CREATE UNIQUE INDEX /*i*/iw_prefix ON /*_*/interwiki (iw_prefix);
-
-
--
-- Used for caching expensive grouped queries
--
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,
+ log_actor bigint 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.
-- 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),
+ log_comment_id bigint 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)
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 INDEX /*i*/log_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);
pt_title nvarchar(255) NOT NULL,
pt_user int REFERENCES /*_*/mwuser(user_id) ON DELETE SET NULL,
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_reason_id bigint 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
+ pt_create_perm nvarchar(60) NOT NULL,
+ CONSTRAINT PK_protected_titles PRIMARY KEY(pt_namespace,pt_title)
);
-CREATE UNIQUE INDEX /*i*/pt_namespace_title ON /*_*/protected_titles (pt_namespace,pt_title);
CREATE INDEX /*i*/pt_timestamp ON /*_*/protected_titles (pt_timestamp);
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_sortkey float DEFAULT NULL
+ pp_sortkey float DEFAULT NULL,
+ CONSTRAINT PK_page_props PRIMARY KEY(pp_page,pp_propname)
);
-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);
si_type nvarchar(32) NOT NULL,
-- local key value, ie 'en' or 'wiktionary'
- si_key nvarchar(32) NOT NULL
+ si_key nvarchar(32) NOT NULL,
+
+ CONSTRAINT PK_site_identifiers PRIMARY KEY(si_type, si_key)
);
-CREATE UNIQUE INDEX /*i*/site_ids_type ON /*_*/site_identifiers (si_type, si_key);
CREATE INDEX /*i*/site_ids_site ON /*_*/site_identifiers (si_site);
CREATE INDEX /*i*/site_ids_key ON /*_*/site_identifiers (si_key);
dépôts / lhc / web / wiklou.git / blobdiff