X-Git-Url: http://git.cyclocoop.org/%27.%28%24current%20%3E%202?a=blobdiff_plain;f=maintenance%2Fmssql%2Ftables.sql;fp=maintenance%2Fmssql%2Ftables.sql;h=f040c1570d4d97afbc01bd8639e659c14877d1fc;hb=5f4cf303e22c522a63d08cac61b38e74a8dfd0f7;hp=8f30e8000a0548e5037b0345a992e23061b3887c;hpb=ac8d0d9bcda4cf8132e088af2f847c026b9fe1ef;p=lhc%2Fweb%2Fwiklou.git diff --git a/maintenance/mssql/tables.sql b/maintenance/mssql/tables.sql index 8f30e8000a..f040c1570d 100644 --- a/maintenance/mssql/tables.sql +++ b/maintenance/mssql/tables.sql @@ -61,8 +61,8 @@ INSERT INTO /*_*/mwuser (user_name) VALUES ('##Anonymous##'); -- 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); @@ -144,7 +144,7 @@ CREATE TABLE /*_*/bot_passwords ( -- 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) @@ -225,16 +225,16 @@ ALTER TABLE /*_*/page ADD CONSTRAINT FK_page_latest_page_id FOREIGN KEY (page_la -- 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); @@ -271,10 +271,10 @@ CREATE TABLE /*_*/archive ( 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 @@ -294,30 +294,26 @@ 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 @@ -326,42 +322,46 @@ CREATE INDEX /*i*/slot_revision_origin_role ON /*_*/slots (slot_revision_id, slo 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); -- @@ -643,7 +643,7 @@ CREATE TABLE /*_*/ipblocks ( 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 '', @@ -653,7 +653,7 @@ CREATE TABLE /*_*/ipblocks ( -- 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. @@ -756,7 +756,7 @@ CREATE TABLE /*_*/image ( -- 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 '', @@ -787,8 +787,8 @@ CREATE INDEX /*i*/img_media_mime ON /*_*/image (img_media_type,img_major_mime,im -- 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); @@ -814,10 +814,10 @@ CREATE TABLE /*_*/oldimage ( 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, @@ -866,7 +866,7 @@ CREATE TABLE /*_*/filearchive ( 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, @@ -878,10 +878,10 @@ CREATE TABLE /*_*/filearchive ( 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 @@ -976,7 +976,7 @@ 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 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, @@ -984,7 +984,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_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 @@ -1180,7 +1180,7 @@ CREATE TABLE /*_*/logging ( 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. @@ -1193,7 +1193,7 @@ CREATE TABLE /*_*/logging ( -- 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) @@ -1358,7 +1358,7 @@ CREATE TABLE /*_*/protected_titles ( 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,