maintenance/oracle/archives/patch-comment-table.sql

   1 --
   2 -- patch-comment-table.sql
   3 --
   4 -- T166732. Add a `comment` table and various columns (and temporary tables) to reference it.
   5
   6 CREATE SEQUENCE comment_comment_id_seq;
   7 CREATE TABLE &mw_prefix."COMMENT" (
   8   comment_id NUMBER NOT NULL,
   9   comment_hash NUMBER NOT NULL,
  10   comment_text CLOB,
  11   comment_data CLOB
  12 );
  13 CREATE INDEX &mw_prefix.comment_hash ON &mw_prefix."COMMENT" (comment_hash);
  14 /*$mw$*/
  15 CREATE TRIGGER &mw_prefix.comment_seq_trg BEFORE INSERT ON &mw_prefix."COMMENT"
  16         FOR EACH ROW WHEN (new.comment_id IS NULL)
  17 BEGIN
  18         &mw_prefix.lastval_pkg.setLastval(comment_comment_id_seq.nextval, :new.comment_id);
  19 END;
  20 /*$mw$*/
  21
  22 -- dummy row for FKs. Hash is intentionally wrong so CommentStore won't match it.
  23 INSERT INTO &mw_prefix."COMMENT" (comment_hash, comment_text) VALUES (-1, '** dummy **');
  24
  25
  26 CREATE TABLE &mw_prefix.revision_comment_temp (
  27   revcomment_rev NUMBER NOT NULL,
  28   revcomment_comment_id NUMBER NOT NULL
  29 );
  30 ALTER TABLE &mw_prefix.revision_comment_temp ADD CONSTRAINT &mw_prefix.revision_comment_temp_pk PRIMARY KEY (revcomment_rev, revcomment_comment_id);
  31 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;
  32 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;
  33 CREATE UNIQUE INDEX &mw_prefix.revcomment_rev ON &mw_prefix.revision_comment_temp (revcomment_rev);
  34
  35
  36 CREATE TABLE &mw_prefix.image_comment_temp (
  37   imgcomment_name VARCHAR2(255) NOT NULL,
  38   imgcomment_description_id NUMBER NOT NULL
  39 );
  40 ALTER TABLE &mw_prefix.image_comment_temp ADD CONSTRAINT &mw_prefix.image_comment_temp_pk PRIMARY KEY (imgcomment_name, imgcomment_description_id);
  41 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;
  42 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;
  43 CREATE UNIQUE INDEX &mw_prefix.imgcomment_name ON &mw_prefix.image_comment_temp (imgcomment_name);
  44
  45
  46 ALTER TABLE &mw_prefix.archive ADD ( ar_comment_id NUMBER DEFAULT 0 NOT NULL );
  47 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;
  48
  49 ALTER TABLE &mw_prefix.ipblocks MODIFY ( ipb_reason NULL );
  50 ALTER TABLE &mw_prefix.ipblocks ADD ( ipb_reason_id NUMBER DEFAULT 0 NOT NULL );
  51 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;
  52
  53 ALTER TABLE &mw_prefix.oldimage ADD ( oi_description_id NUMBER DEFAULT 0 NOT NULL );
  54 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;
  55
  56 ALTER TABLE &mw_prefix.filearchive ADD ( fa_deleted_reason_id NUMBER DEFAULT 0 NOT NULL );
  57 ALTER TABLE &mw_prefix.filearchive ADD ( fa_description_id NUMBER DEFAULT 0 NOT NULL );
  58 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;
  59 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;
  60
  61 ALTER TABLE &mw_prefix.recentchanges ADD ( rc_comment_id NUMBER DEFAULT 0 NOT NULL );
  62 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;
  63
  64 ALTER TABLE &mw_prefix.logging ADD ( log_comment_id NUMBER DEFAULT 0 NOT NULL );
  65 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;
  66
  67 ALTER TABLE &mw_prefix.protected_titles ADD ( pt_reason_id NUMBER DEFAULT 0 NOT NULL );
  68 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;