jenkins-bot has submitted this change and it was merged. (
https://gerrit.wikimedia.org/r/404506 )
Change subject: Non-MySQL comment table updates
......................................................................
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
---
M includes/installer/MssqlUpdater.php
M includes/installer/OracleUpdater.php
M includes/installer/PostgresUpdater.php
A maintenance/mssql/archives/patch-comment-table.sql
M maintenance/mssql/tables.sql
A maintenance/oracle/archives/patch-comment-table.sql
M maintenance/oracle/tables.sql
7 files changed, 260 insertions(+), 9 deletions(-)
Approvals:
Tim Starling: Looks good to me, approved
jenkins-bot: Verified
diff --git a/includes/installer/MssqlUpdater.php
b/includes/installer/MssqlUpdater.php
index b4b34de..1fd1d9b 100644
--- a/includes/installer/MssqlUpdater.php
+++ b/includes/installer/MssqlUpdater.php
@@ -106,6 +106,10 @@
[ '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 ea68412..3ee51ea 100644
--- a/includes/installer/OracleUpdater.php
+++ b/includes/installer/OracleUpdater.php
@@ -127,6 +127,10 @@
[ '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 367d431..8d12404 100644
--- a/includes/installer/PostgresUpdater.php
+++ b/includes/installer/PostgresUpdater.php
@@ -481,6 +481,7 @@
[ '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 0000000..f4c2a90
--- /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 1d5abd0..4673264 100644
--- a/maintenance/mssql/tables.sql
+++ b/maintenance/mssql/tables.sql
@@ -118,6 +118,28 @@
--
+-- 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 @@
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 '',
@@ -176,6 +198,20 @@
INSERT INTO /*_*/revision (rev_page,rev_text_id,rev_comment,rev_user,rev_len)
VALUES (0,0,'',0,0);
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 @@
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 @@
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 @@
-- 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 @@
-- 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 @@
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 @@
-- 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 @@
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 @@
-- 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 @@
-- 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 @@
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 0000000..cfe944f
--- /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 09d1922..7195a5e 100644
--- a/maintenance/oracle/tables.sql
+++ b/maintenance/oracle/tables.sql
@@ -135,6 +135,25 @@
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 @@
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 @@
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 @@
);
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 @@
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 @@
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_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 @@
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 @@
);
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 @@
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 @@
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 @@
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 @@
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 @@
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 @@
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 @@
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);
--
To view, visit https://gerrit.wikimedia.org/r/404506
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: merged
Gerrit-Change-Id: I408085db17bf951ce721427e7344b4afd5706e40
Gerrit-PatchSet: 2
Gerrit-Project: mediawiki/core
Gerrit-Branch: master
Gerrit-Owner: Anomie <[email protected]>
Gerrit-Reviewer: Anomie <[email protected]>
Gerrit-Reviewer: Freakolowsky <[email protected]>
Gerrit-Reviewer: Jjanes <[email protected]>
Gerrit-Reviewer: Legoktm <[email protected]>
Gerrit-Reviewer: Parent5446 <[email protected]>
Gerrit-Reviewer: Skizzerz <[email protected]>
Gerrit-Reviewer: Tim Starling <[email protected]>
Gerrit-Reviewer: jenkins-bot <>
_______________________________________________
MediaWiki-commits mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
