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

   1 --
   2 -- patch-actor-table.sql
   3 --
   4 -- T167246. Add an `actor` table and various columns (and temporary tables) to reference it.
   5
   6 define mw_prefix='{$wgDBprefix}';
   7
   8 CREATE SEQUENCE actor_actor_id_seq;
   9 CREATE TABLE &mw_prefix.actor (
  10   actor_id NUMBER NOT NULL,
  11   actor_user NUMBER,
  12   actor_name VARCHAR2(255) NOT NULL
  13 );
  14
  15 ALTER TABLE &mw_prefix.actor ADD CONSTRAINT &mw_prefix.actor_pk PRIMARY KEY (actor_id);
  16
  17 /*$mw$*/
  18 CREATE TRIGGER &mw_prefix.actor_seq_trg BEFORE INSERT ON &mw_prefix.actor
  19         FOR EACH ROW WHEN (new.actor_id IS NULL)
  20 BEGIN
  21         &mw_prefix.lastval_pkg.setLastval(actor_actor_id_seq.nextval, :new.actor_id);
  22 END;
  23 /*$mw$*/
  24
  25 -- Create a dummy actor to satisfy fk contraints
  26 INSERT INTO &mw_prefix.actor (actor_id, actor_name) VALUES (0,'##Anonymous##');
  27
  28 CREATE TABLE &mw_prefix.revision_actor_temp (
  29   revactor_rev NUMBER NOT NULL,
  30   revactor_actor NUMBER NOT NULL,
  31   revactor_timestamp TIMESTAMP(6) WITH TIME ZONE NOT NULL,
  32   revactor_page NUMBER NOT NULL
  33 );
  34 ALTER TABLE &mw_prefix.revision_actor_temp ADD CONSTRAINT &mw_prefix.revision_actor_temp_pk PRIMARY KEY (revactor_rev, revactor_actor);
  35 CREATE UNIQUE INDEX &mw_prefix.revactor_rev ON &mw_prefix.revision_actor_temp (revactor_rev);
  36 CREATE INDEX &mw_prefix.actor_timestamp ON &mw_prefix.revision_actor_temp (revactor_actor,revactor_timestamp);
  37 CREATE INDEX &mw_prefix.page_actor_timestamp ON &mw_prefix.revision_actor_temp (revactor_page,revactor_actor,revactor_timestamp);
  38
  39 ALTER TABLE &mw_prefix.archive ALTER COLUMN ar_user_text VARCHAR2(255) NULL;
  40 ALTER TABLE &mw_prefix.archive ADD COLUMN ar_actor NUMBER DEFAULT 0 NOT NULL;
  41 CREATE INDEX &mw_prefix.ar_actor_timestamp ON &mw_prefix.archive (ar_actor,ar_timestamp);
  42
  43 ALTER TABLE &mw_prefix.ipblocks ADD COLUMN ipb_by_actor NUMBER DEFUALT 0 NOT NULL;
  44
  45 ALTER TABLE &mw_prefix.image ALTER COLUMN img_user_text VARCHAR2(255) NULL;
  46 ALTER TABLE &mw_prefix.image ADD COLUMN img_actor NUMBER DEFAULT 0 NOT NULL;
  47 CREATE INDEX &mw_prefix.img_actor_timestamp ON &mw_prefix.image (img_actor, img_timestamp);
  48
  49 ALTER TABLE &mw_prefix.oldimage ALTER COLUMN oi_user_text VARCHAR2(255) NULL;
  50 ALTER TABLE &mw_prefix.oldimage ADD COLUMN oi_actor NUMBER DEFAULT 0 NOT NULL;
  51 CREATE INDEX &mw_prefix.oi_actor_timestamp ON &mw_prefix.oldimage (oi_actor,oi_timestamp);
  52
  53 ALTER TABLE &mw_prefix.filearchive ALTER COLUMN fa_user_text VARCHAR2(255) NULL;
  54 ALTER TABLE &mw_prefix.filearchive ADD COLUMN fa_actor NUMBER DEFAULT 0 NOT NULL;
  55 CREATE INDEX &mw_prefix.fa_actor_timestamp ON &mw_prefix.filearchive (fa_actor,fa_timestamp);
  56
  57 ALTER TABLE &mw_prefix.recentchanges ALTER COLUMN rc_user_text VARCHAR2(255) NULL;
  58 ALTER TABLE &mw_prefix.recentchanges ADD COLUMN rc_actor NUMBER DEFAULT 0 NOT NULL;
  59 CREATE INDEX &mw_prefix.rc_ns_actor ON &mw_prefix.recentchanges (rc_namespace, rc_actor);
  60 CREATE INDEX &mw_prefix.rc_actor ON &mw_prefix.recentchanges (rc_actor, rc_timestamp);
  61
  62 ALTER TABLE &mw_prefix.logging ADD COLUMN log_actor NUMBER DEFAULT 0 NOT NULL;
  63 CREATE INDEX &mw_prefix.actor_time ON &mw_prefix.logging (log_actor, log_timestamp);
  64 CREATE INDEX &mw_prefix.log_actor_type_time ON &mw_prefix.logging (log_actor, log_type, log_timestamp);