From: Brad Jorsch Date: Tue, 16 Jan 2018 17:30:24 +0000 (-0500) Subject: Non-MySQL comment table updates X-Git-Tag: 1.31.0-rc.0~881^2 X-Git-Url: https://git.heureux-cyclage.org/?p=lhc%2Fweb%2Fwiklou.git;a=commitdiff_plain;h=0c0f70a326d6daab3754e1202ed460ec3bad6508 Non-MySQL comment table updates Follows up Ic3a434c06. Add schema for MSSQL and Oracle, and add a missing 'migrateComments' call for PostgreSQL. Bug: T166732 Change-Id: I408085db17bf951ce721427e7344b4afd5706e40 --- diff --git a/includes/installer/MssqlUpdater.php b/includes/installer/MssqlUpdater.php index b4b34de106..1fd1d9b444 100644 --- a/includes/installer/MssqlUpdater.php +++ b/includes/installer/MssqlUpdater.php @@ -106,6 +106,10 @@ class MssqlUpdater extends DatabaseUpdater { [ 'modifyField', 'image', 'img_media_type', 'patch-add-3d.sql' ], [ 'addIndex', 'site_stats', 'PRIMARY', 'patch-site_stats-pk.sql' ], + // Should have been in 1.30 + [ 'addTable', 'comment', 'patch-comment-table.sql' ], + [ 'migrateComments' ], + // 1.31 [ 'addTable', 'slots', 'patch-slots.sql' ], [ 'addTable', 'content', 'patch-content.sql' ], diff --git a/includes/installer/OracleUpdater.php b/includes/installer/OracleUpdater.php index ea684128c3..3ee51eab9e 100644 --- a/includes/installer/OracleUpdater.php +++ b/includes/installer/OracleUpdater.php @@ -127,6 +127,10 @@ class OracleUpdater extends DatabaseUpdater { [ 'doAutoIncrementTriggers' ], [ 'addIndex', 'site_stats', 'PRIMARY', 'patch-site_stats-pk.sql' ], + // Should have been in 1.30 + [ 'addTable', 'comment', 'patch-comment-table.sql' ], + [ 'migrateComments' ], + // 1.31 [ 'addTable', 'slots', 'patch-slots.sql' ], [ 'addTable', 'content', 'patch-content.sql' ], diff --git a/includes/installer/PostgresUpdater.php b/includes/installer/PostgresUpdater.php index 367d431dec..8d1240495f 100644 --- a/includes/installer/PostgresUpdater.php +++ b/includes/installer/PostgresUpdater.php @@ -481,6 +481,7 @@ class PostgresUpdater extends DatabaseUpdater { [ 'changeNullableField', 'protected_titles', 'pt_reason', 'NOT NULL', true ], [ 'addPgField', 'protected_titles', 'pt_reason_id', 'INTEGER NOT NULL DEFAULT 0' ], [ 'addTable', 'comment', 'patch-comment-table.sql' ], + [ 'migrateComments' ], [ 'addIndex', 'site_stats', 'site_stats_pkey', 'patch-site_stats-pk.sql' ], [ 'addTable', 'ip_changes', 'patch-ip_changes.sql' ], diff --git a/maintenance/mssql/archives/patch-comment-table.sql b/maintenance/mssql/archives/patch-comment-table.sql new file mode 100644 index 0000000000..f4c2a900e0 --- /dev/null +++ b/maintenance/mssql/archives/patch-comment-table.sql @@ -0,0 +1,57 @@ +-- +-- patch-comment-table.sql +-- +-- T166732. Add a `comment` table and various columns (and temporary tables) to reference it. + +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) +); +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 **'); + + +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 /*_*/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); + + +ALTER TABLE /*_*/revision ADD CONSTRAINT DF_rev_comment DEFAULT '' FOR rev_comment; + +ALTER TABLE /*_*/archive ADD CONSTRAINT DF_ar_comment DEFAULT '' FOR ar_comment; +ALTER TABLE /*_*/archive ADD 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); + +ALTER TABLE /*_*/ipblocks ADD CONSTRAINT DF_ipb_reason DEFAULT '' FOR ipb_reason; +ALTER TABLE /*_*/ipblocks ADD 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); + +ALTER TABLE /*_*/image ADD CONSTRAINT DF_img_description DEFAULT '' FOR img_description; + +ALTER TABLE /*_*/oldimage ADD CONSTRAINT DF_oi_description DEFAULT '' FOR oi_description; +ALTER TABLE /*_*/oldimage ADD 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); + +ALTER TABLE /*_*/filearchive ADD CONSTRAINT DF_fa_deleted_reason DEFAULT '' FOR fa_deleted_reason; +ALTER TABLE /*_*/filearchive ADD 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); +ALTER TABLE /*_*/filearchive ADD CONSTRAINT DF_fa_description DEFAULT '' FOR fa_description; +ALTER TABLE /*_*/filearchive ADD fa_description_id bigint unsigned NOT NULL CONSTRAINT DF_fa_description_id DEFAULT 0 CONSTRAINT FK_fa_description_id FOREIGN KEY REFERENCES /*_*/comment(comment_id); + +ALTER TABLE /*_*/recentchanges ADD 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); + +ALTER TABLE /*_*/logging ADD 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); + +ALTER TABLE /*_*/protected_titles ADD CONSTRAINT DF_pt_reason DEFAULT '' FOR pt_reason; +ALTER TABLE /*_*/protected_titles ADD 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); diff --git a/maintenance/mssql/tables.sql b/maintenance/mssql/tables.sql index 1d5abd0b87..467326403e 100644 --- a/maintenance/mssql/tables.sql +++ b/maintenance/mssql/tables.sql @@ -117,6 +117,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. @@ -153,7 +175,7 @@ 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_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 +199,20 @@ 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 table 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); + -- -- Holds TEXT of individual page revisions. -- @@ -207,7 +243,8 @@ CREATE TABLE /*_*/archive ( 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_timestamp varchar(14) NOT NULL default '', @@ -567,7 +604,11 @@ CREATE TABLE /*_*/ipblocks ( 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. @@ -664,7 +705,7 @@ 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 '', -- user_id and user_name of uploader. img_user int REFERENCES /*_*/mwuser(user_id) ON DELETE SET NULL, @@ -690,6 +731,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. @@ -710,7 +765,8 @@ 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_timestamp varchar(14) NOT NULL default '', @@ -759,7 +815,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, @@ -770,7 +827,8 @@ 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_timestamp varchar(14) default '', @@ -873,6 +931,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 @@ -1076,6 +1135,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, @@ -1236,7 +1299,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 diff --git a/maintenance/oracle/archives/patch-comment-table.sql b/maintenance/oracle/archives/patch-comment-table.sql new file mode 100644 index 0000000000..cfe944fce7 --- /dev/null +++ b/maintenance/oracle/archives/patch-comment-table.sql @@ -0,0 +1,68 @@ +-- +-- patch-comment-table.sql +-- +-- T166732. Add a `comment` table and various columns (and temporary tables) to reference it. + +CREATE SEQUENCE comment_comment_id_seq; +CREATE TABLE &mw_prefix."COMMENT" ( + comment_id NUMBER NOT NULL, + comment_hash NUMBER NOT NULL, + comment_text CLOB, + comment_data CLOB +); +CREATE INDEX &mw_prefix.comment_hash ON &mw_prefix."COMMENT" (comment_hash); +/*$mw$*/ +CREATE TRIGGER &mw_prefix.comment_seq_trg BEFORE INSERT ON &mw_prefix."COMMENT" + FOR EACH ROW WHEN (new.comment_id IS NULL) +BEGIN + &mw_prefix.lastval_pkg.setLastval(comment_comment_id_seq.nextval, :new.comment_id); +END; +/*$mw$*/ + +-- dummy row for FKs. Hash is intentionally wrong so CommentStore won't match it. +INSERT INTO &mw_prefix."COMMENT" (comment_hash, comment_text) VALUES (-1, '** dummy **'); + + +CREATE TABLE &mw_prefix.revision_comment_temp ( + revcomment_rev NUMBER NOT NULL, + revcomment_comment_id NUMBER NOT NULL +); +ALTER TABLE &mw_prefix.revision_comment_temp ADD CONSTRAINT &mw_prefix.revision_comment_temp_pk PRIMARY KEY (revcomment_rev, revcomment_comment_id); +ALTER TABLE &mw_prefix.revision_comment_temp ADD CONSTRAINT &mw_prefix.revision_comment_temp_fk1 FOREIGN KEY (revcomment_rev) REFERENCES &mw_prefix.revision(rev_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE &mw_prefix.revision_comment_temp ADD CONSTRAINT &mw_prefix.revision_comment_temp_fk2 FOREIGN KEY (revcomment_comment_id) REFERENCES &mw_prefix."COMMENT"(comment_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; +CREATE UNIQUE INDEX &mw_prefix.revcomment_rev ON &mw_prefix.revision_comment_temp (revcomment_rev); + + +CREATE TABLE &mw_prefix.image_comment_temp ( + imgcomment_name VARCHAR2(255) NOT NULL, + imgcomment_description_id NUMBER NOT NULL +); +ALTER TABLE &mw_prefix.image_comment_temp ADD CONSTRAINT &mw_prefix.image_comment_temp_pk PRIMARY KEY (imgcomment_name, imgcomment_description_id); +ALTER TABLE &mw_prefix.image_comment_temp ADD CONSTRAINT &mw_prefix.image_comment_temp_fk1 FOREIGN KEY (imgcomment_name) REFERENCES &mw_prefix.image(img_name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE &mw_prefix.image_comment_temp ADD CONSTRAINT &mw_prefix.image_comment_temp_fk2 FOREIGN KEY (imgcomment_description_id) REFERENCES &mw_prefix."COMMENT"(comment_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; +CREATE UNIQUE INDEX &mw_prefix.imgcomment_name ON &mw_prefix.image_comment_temp (imgcomment_name); + + +ALTER TABLE &mw_prefix.archive ADD COLUMN ar_comment_id NUMBER DEFAULT 0 NOT NULL; +ALTER TABLE &mw_prefix.archive ADD CONSTRAINT &mw_prefix.archive_fk2 FOREIGN KEY (ar_comment_id) REFERENCES &mw_prefix."COMMENT"(comment_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; + +ALTER TABLE &mw_prefix.ipblocks ALTER COLUMN ipb_reason VARCHAR2(255) NULL; +ALTER TABLE &mw_prefix.ipblocks ADD COLUMN ipb_reason_id NUMBER DEFAULT 0 NOT NULL; +ALTER TABLE &mw_prefix.ipblocks ADD CONSTRAINT &mw_prefix.ipblocks_fk3 FOREIGN KEY (ipb_reason_id) REFERENCES &mw_prefix."COMMENT"(comment_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; + +ALTER TABLE &mw_prefix.oldimage ADD COLUMN oi_description_id NUMBER DEFAULT 0 NOT NULL; +ALTER TABLE &mw_prefix.oldimage ADD CONSTRAINT &mw_prefix.oldimage_fk3 FOREIGN KEY (oi_description_id) REFERENCES &mw_prefix."COMMENT"(comment_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; + +ALTER TABLE &mw_prefix.filearchive ADD COLUMN fa_deleted_reason_id NUMBER DEFAULT 0 NOT NULL; +ALTER TABLE &mw_prefix.filearchive ADD COLUMN fa_description_id NUMBER DEFAULT 0 NOT NULL; +ALTER TABLE &mw_prefix.filearchive ADD CONSTRAINT &mw_prefix.filearchive_fk3 FOREIGN KEY (fa_deleted_reason_id) REFERENCES &mw_prefix."COMMENT"(comment_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE &mw_prefix.filearchive ADD CONSTRAINT &mw_prefix.filearchive_fk4 FOREIGN KEY (fa_description_id) REFERENCES &mw_prefix."COMMENT"(comment_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; + +ALTER TABLE &mw_prefix.recentchanges ADD COLUMN rc_comment_id NUMBER DEFAULT 0 NOT NULL; +ALTER TABLE &mw_prefix.recentchanges ADD CONSTRAINT &mw_prefix.recentchanges_fk3 FOREIGN KEY (rc_comment_id) REFERENCES &mw_prefix."COMMENT"(comment_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; + +ALTER TABLE &mw_prefix.logging ADD COLUMN log_comment_id NUMBER DEFAULT 0 NOT NULL; +ALTER TABLE &mw_prefix.logging ADD CONSTRAINT &mw_prefix.logging_fk2 FOREIGN KEY (log_comment_id) REFERENCES &mw_prefix."COMMENT"(comment_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; + +ALTER TABLE &mw_prefix.protected_titles ADD COLUMN pt_reason_id NUMBER DEFAULT 0 NOT NULL; +ALTER TABLE &mw_prefix.protected_titles ADD CONSTRAINT &mw_prefix.protected_titles_fk1 FOREIGN KEY (pt_reason_id) REFERENCES &mw_prefix."COMMENT"(comment_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; diff --git a/maintenance/oracle/tables.sql b/maintenance/oracle/tables.sql index 09d19220db..7195a5e388 100644 --- a/maintenance/oracle/tables.sql +++ b/maintenance/oracle/tables.sql @@ -135,6 +135,25 @@ BEGIN END; /*$mw$*/ +CREATE SEQUENCE comment_comment_id_seq; +CREATE TABLE &mw_prefix."COMMENT" ( + comment_id NUMBER NOT NULL, + comment_hash NUMBER NOT NULL, + comment_text CLOB, + comment_data CLOB +); +CREATE INDEX &mw_prefix.comment_hash ON &mw_prefix."COMMENT" (comment_hash); +/*$mw$*/ +CREATE TRIGGER &mw_prefix.comment_seq_trg BEFORE INSERT ON &mw_prefix."COMMENT" + FOR EACH ROW WHEN (new.comment_id IS NULL) +BEGIN + &mw_prefix.lastval_pkg.setLastval(comment_comment_id_seq.nextval, :new.comment_id); +END; +/*$mw$*/ + +-- dummy row for FKs. Hash is intentionally wrong so CommentStore won't match it. +INSERT INTO &mw_prefix."COMMENT" (comment_hash, comment_text) VALUES (-1, '** dummy **'); + CREATE SEQUENCE revision_rev_id_seq; CREATE TABLE &mw_prefix.revision ( rev_id NUMBER NOT NULL, @@ -169,6 +188,15 @@ BEGIN END; /*$mw$*/ +CREATE TABLE &mw_prefix.revision_comment_temp ( + revcomment_rev NUMBER NOT NULL, + revcomment_comment_id NUMBER NOT NULL +); +ALTER TABLE &mw_prefix.revision_comment_temp ADD CONSTRAINT &mw_prefix.revision_comment_temp_pk PRIMARY KEY (revcomment_rev, revcomment_comment_id); +ALTER TABLE &mw_prefix.revision_comment_temp ADD CONSTRAINT &mw_prefix.revision_comment_temp_fk1 FOREIGN KEY (revcomment_rev) REFERENCES &mw_prefix.revision(rev_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE &mw_prefix.revision_comment_temp ADD CONSTRAINT &mw_prefix.revision_comment_temp_fk2 FOREIGN KEY (revcomment_comment_id) REFERENCES &mw_prefix."COMMENT"(comment_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; +CREATE UNIQUE INDEX &mw_prefix.revcomment_rev ON &mw_prefix.revision_comment_temp (revcomment_rev); + CREATE SEQUENCE text_old_id_seq; CREATE TABLE &mw_prefix.pagecontent ( -- replaces reserved word 'text' old_id NUMBER NOT NULL, @@ -191,6 +219,7 @@ CREATE TABLE &mw_prefix.archive ( ar_title VARCHAR2(255) NOT NULL, ar_text CLOB, ar_comment VARCHAR2(255), + ar_comment_id NUMBER DEFAULT 0 NOT NULL, ar_user NUMBER DEFAULT 0 NOT NULL, ar_user_text VARCHAR2(255) NOT NULL, ar_timestamp TIMESTAMP(6) WITH TIME ZONE NOT NULL, @@ -208,6 +237,7 @@ CREATE TABLE &mw_prefix.archive ( ); ALTER TABLE &mw_prefix.archive ADD CONSTRAINT &mw_prefix.archive_pk PRIMARY KEY (ar_id); ALTER TABLE &mw_prefix.archive ADD CONSTRAINT &mw_prefix.archive_fk1 FOREIGN KEY (ar_user) REFERENCES &mw_prefix.mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE &mw_prefix.archive ADD CONSTRAINT &mw_prefix.archive_fk2 FOREIGN KEY (ar_comment_id) REFERENCES &mw_prefix."COMMENT"(comment_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; CREATE INDEX &mw_prefix.archive_i01 ON &mw_prefix.archive (ar_namespace,ar_title,ar_timestamp); CREATE INDEX &mw_prefix.archive_i02 ON &mw_prefix.archive (ar_user_text,ar_timestamp); CREATE INDEX &mw_prefix.archive_i03 ON &mw_prefix.archive (ar_rev_id); @@ -409,7 +439,8 @@ CREATE TABLE &mw_prefix.ipblocks ( ipb_user NUMBER DEFAULT 0 NOT NULL, ipb_by NUMBER DEFAULT 0 NOT NULL, ipb_by_text VARCHAR2(255) NULL, - ipb_reason VARCHAR2(255) NOT NULL, + ipb_reason VARCHAR2(255) NULL, + ipb_reason_id NUMBER DEFAULT 0 NOT NULL, ipb_timestamp TIMESTAMP(6) WITH TIME ZONE NOT NULL, ipb_auto CHAR(1) DEFAULT '0' NOT NULL, ipb_anon_only CHAR(1) DEFAULT '0' NOT NULL, @@ -426,6 +457,7 @@ CREATE TABLE &mw_prefix.ipblocks ( ALTER TABLE &mw_prefix.ipblocks ADD CONSTRAINT &mw_prefix.ipblocks_pk PRIMARY KEY (ipb_id); ALTER TABLE &mw_prefix.ipblocks ADD CONSTRAINT &mw_prefix.ipblocks_fk1 FOREIGN KEY (ipb_user) REFERENCES &mw_prefix.mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED; ALTER TABLE &mw_prefix.ipblocks ADD CONSTRAINT &mw_prefix.ipblocks_fk2 FOREIGN KEY (ipb_by) REFERENCES &mw_prefix.mwuser(user_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE &mw_prefix.ipblocks ADD CONSTRAINT &mw_prefix.ipblocks_fk3 FOREIGN KEY (ipb_reason_id) REFERENCES &mw_prefix."COMMENT"(comment_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; CREATE UNIQUE INDEX &mw_prefix.ipblocks_u01 ON &mw_prefix.ipblocks (ipb_address, ipb_user, ipb_auto, ipb_anon_only); CREATE INDEX &mw_prefix.ipblocks_i01 ON &mw_prefix.ipblocks (ipb_user); CREATE INDEX &mw_prefix.ipblocks_i02 ON &mw_prefix.ipblocks (ipb_range_start, ipb_range_end); @@ -463,6 +495,15 @@ CREATE INDEX &mw_prefix.image_i02 ON &mw_prefix.image (img_size); CREATE INDEX &mw_prefix.image_i03 ON &mw_prefix.image (img_timestamp); CREATE INDEX &mw_prefix.image_i04 ON &mw_prefix.image (img_sha1); +CREATE TABLE &mw_prefix.image_comment_temp ( + imgcomment_name VARCHAR2(255) NOT NULL, + imgcomment_description_id NUMBER NOT NULL +); +ALTER TABLE &mw_prefix.image_comment_temp ADD CONSTRAINT &mw_prefix.image_comment_temp_pk PRIMARY KEY (imgcomment_name, imgcomment_description_id); +ALTER TABLE &mw_prefix.image_comment_temp ADD CONSTRAINT &mw_prefix.image_comment_temp_fk1 FOREIGN KEY (imgcomment_name) REFERENCES &mw_prefix.image(img_name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE &mw_prefix.image_comment_temp ADD CONSTRAINT &mw_prefix.image_comment_temp_fk2 FOREIGN KEY (imgcomment_description_id) REFERENCES &mw_prefix."COMMENT"(comment_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; +CREATE UNIQUE INDEX &mw_prefix.imgcomment_name ON &mw_prefix.image_comment_temp (imgcomment_name); + CREATE TABLE &mw_prefix.oldimage ( oi_name VARCHAR2(255) DEFAULT 0 NOT NULL, @@ -472,6 +513,7 @@ CREATE TABLE &mw_prefix.oldimage ( oi_height NUMBER DEFAULT 0 NOT NULL, oi_bits NUMBER DEFAULT 0 NOT NULL, oi_description VARCHAR2(255), + oi_description_id NUMBER DEFAULT 0 NOT NULL, oi_user NUMBER DEFAULT 0 NOT NULL, oi_user_text VARCHAR2(255) NOT NULL, oi_timestamp TIMESTAMP(6) WITH TIME ZONE NOT NULL, @@ -484,6 +526,7 @@ CREATE TABLE &mw_prefix.oldimage ( ); ALTER TABLE &mw_prefix.oldimage ADD CONSTRAINT &mw_prefix.oldimage_fk1 FOREIGN KEY (oi_name) REFERENCES &mw_prefix.image(img_name) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED; ALTER TABLE &mw_prefix.oldimage ADD CONSTRAINT &mw_prefix.oldimage_fk2 FOREIGN KEY (oi_user) REFERENCES &mw_prefix.mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE &mw_prefix.oldimage ADD CONSTRAINT &mw_prefix.oldimage_fk3 FOREIGN KEY (oi_description_id) REFERENCES &mw_prefix."COMMENT"(comment_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; CREATE INDEX &mw_prefix.oldimage_i01 ON &mw_prefix.oldimage (oi_user_text,oi_timestamp); CREATE INDEX &mw_prefix.oldimage_i02 ON &mw_prefix.oldimage (oi_name,oi_timestamp); CREATE INDEX &mw_prefix.oldimage_i03 ON &mw_prefix.oldimage (oi_name,oi_archive_name); @@ -500,6 +543,7 @@ CREATE TABLE &mw_prefix.filearchive ( fa_deleted_user NUMBER DEFAULT 0 NOT NULL, fa_deleted_timestamp TIMESTAMP(6) WITH TIME ZONE NOT NULL, fa_deleted_reason CLOB, + fa_deleted_reason_id NUMBER DEFAULT 0 NOT NULL, fa_size NUMBER DEFAULT 0 NOT NULL, fa_width NUMBER DEFAULT 0 NOT NULL, fa_height NUMBER DEFAULT 0 NOT NULL, @@ -509,6 +553,7 @@ CREATE TABLE &mw_prefix.filearchive ( fa_major_mime VARCHAR2(32) DEFAULT 'unknown', fa_minor_mime VARCHAR2(100) DEFAULT 'unknown', fa_description VARCHAR2(255), + fa_description_id NUMBER DEFAULT 0 NOT NULL, fa_user NUMBER DEFAULT 0 NOT NULL, fa_user_text VARCHAR2(255) NOT NULL, fa_timestamp TIMESTAMP(6) WITH TIME ZONE, @@ -518,6 +563,8 @@ CREATE TABLE &mw_prefix.filearchive ( ALTER TABLE &mw_prefix.filearchive ADD CONSTRAINT &mw_prefix.filearchive_pk PRIMARY KEY (fa_id); ALTER TABLE &mw_prefix.filearchive ADD CONSTRAINT &mw_prefix.filearchive_fk1 FOREIGN KEY (fa_deleted_user) REFERENCES &mw_prefix.mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED; ALTER TABLE &mw_prefix.filearchive ADD CONSTRAINT &mw_prefix.filearchive_fk2 FOREIGN KEY (fa_user) REFERENCES &mw_prefix.mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE &mw_prefix.filearchive ADD CONSTRAINT &mw_prefix.filearchive_fk3 FOREIGN KEY (fa_deleted_reason_id) REFERENCES &mw_prefix."COMMENT"(comment_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE &mw_prefix.filearchive ADD CONSTRAINT &mw_prefix.filearchive_fk4 FOREIGN KEY (fa_description_id) REFERENCES &mw_prefix."COMMENT"(comment_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; CREATE INDEX &mw_prefix.filearchive_i01 ON &mw_prefix.filearchive (fa_name, fa_timestamp); CREATE INDEX &mw_prefix.filearchive_i02 ON &mw_prefix.filearchive (fa_storage_group, fa_storage_key); CREATE INDEX &mw_prefix.filearchive_i03 ON &mw_prefix.filearchive (fa_deleted_timestamp); @@ -574,6 +621,7 @@ CREATE TABLE &mw_prefix.recentchanges ( rc_namespace NUMBER DEFAULT 0 NOT NULL, rc_title VARCHAR2(255) NOT NULL, rc_comment VARCHAR2(255), + rc_comment_id NUMBER DEFAULT 0 NOT NULL, rc_minor CHAR(1) DEFAULT '0' NOT NULL, rc_bot CHAR(1) DEFAULT '0' NOT NULL, rc_new CHAR(1) DEFAULT '0' NOT NULL, @@ -595,6 +643,7 @@ CREATE TABLE &mw_prefix.recentchanges ( ALTER TABLE &mw_prefix.recentchanges ADD CONSTRAINT &mw_prefix.recentchanges_pk PRIMARY KEY (rc_id); ALTER TABLE &mw_prefix.recentchanges ADD CONSTRAINT &mw_prefix.recentchanges_fk1 FOREIGN KEY (rc_user) REFERENCES &mw_prefix.mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED; ALTER TABLE &mw_prefix.recentchanges ADD CONSTRAINT &mw_prefix.recentchanges_fk2 FOREIGN KEY (rc_cur_id) REFERENCES &mw_prefix.page(page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE &mw_prefix.recentchanges ADD CONSTRAINT &mw_prefix.recentchanges_fk3 FOREIGN KEY (rc_comment_id) REFERENCES &mw_prefix."COMMENT"(comment_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; CREATE INDEX &mw_prefix.recentchanges_i01 ON &mw_prefix.recentchanges (rc_timestamp); CREATE INDEX &mw_prefix.recentchanges_i02 ON &mw_prefix.recentchanges (rc_namespace, rc_title); CREATE INDEX &mw_prefix.recentchanges_i03 ON &mw_prefix.recentchanges (rc_cur_id); @@ -676,11 +725,13 @@ CREATE TABLE &mw_prefix.logging ( log_title VARCHAR2(255) NOT NULL, log_page NUMBER, log_comment VARCHAR2(255), + log_comment_id NUMBER DEFAULT 0 NOT NULL, log_params CLOB, log_deleted CHAR(1) DEFAULT '0' NOT NULL ); ALTER TABLE &mw_prefix.logging ADD CONSTRAINT &mw_prefix.logging_pk PRIMARY KEY (log_id); ALTER TABLE &mw_prefix.logging ADD CONSTRAINT &mw_prefix.logging_fk1 FOREIGN KEY (log_user) REFERENCES &mw_prefix.mwuser(user_id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE &mw_prefix.logging ADD CONSTRAINT &mw_prefix.logging_fk2 FOREIGN KEY (log_comment_id) REFERENCES &mw_prefix."COMMENT"(comment_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; CREATE INDEX &mw_prefix.logging_i01 ON &mw_prefix.logging (log_type, log_timestamp); CREATE INDEX &mw_prefix.logging_i02 ON &mw_prefix.logging (log_user, log_timestamp); CREATE INDEX &mw_prefix.logging_i03 ON &mw_prefix.logging (log_namespace, log_title, log_timestamp); @@ -790,10 +841,12 @@ CREATE TABLE &mw_prefix.protected_titles ( pt_title VARCHAR2(255) NOT NULL, pt_user NUMBER NOT NULL, pt_reason VARCHAR2(255), + pt_reason_id NUMBER DEFAULT 0 NOT NULL, pt_timestamp TIMESTAMP(6) WITH TIME ZONE NOT NULL, pt_expiry VARCHAR2(14) NOT NULL, pt_create_perm VARCHAR2(60) NOT NULL ); +ALTER TABLE &mw_prefix.protected_titles ADD CONSTRAINT &mw_prefix.protected_titles_fk1 FOREIGN KEY (pt_reason_id) REFERENCES &mw_prefix."COMMENT"(comment_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; CREATE UNIQUE INDEX &mw_prefix.protected_titles_u01 ON &mw_prefix.protected_titles (pt_namespace,pt_title); CREATE INDEX &mw_prefix.protected_titles_i01 ON &mw_prefix.protected_titles (pt_timestamp);