Follows up Ic3a434c06.
Add schema for MSSQL and Oracle, and add a missing 'migrateComments'
call for PostgreSQL.
Bug: T166732
Change-Id: I408085db17bf951ce721427e7344b4afd5706e40
[ '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' ],
[ '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' ],
[ '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' ],
--- /dev/null
+--
+-- 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);
);
+--
+-- 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.
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 '',
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.
--
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 '',
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.
-- 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,
-- 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.
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 '',
-- 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,
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 '',
-- 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
-- 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,
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
--- /dev/null
+--
+-- 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;
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,
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,
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,
);
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);
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,
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);
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,
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,
);
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);
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,
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,
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);
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,
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);
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);
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);
dépôts / lhc / web / wiklou.git / commitdiff