maintenance/postgres/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 CREATE SEQUENCE actor_actor_id_seq;
   7 CREATE TABLE actor (
   8   actor_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('actor_actor_id_seq'),
   9   actor_user INTEGER,
  10   actor_name TEXT NOT NULL
  11 );
  12 CREATE UNIQUE INDEX actor_user ON actor (actor_user);
  13 CREATE UNIQUE INDEX actor_name ON actor (actor_name);
  14
  15 CREATE TABLE revision_actor_temp (
  16   revactor_rev INTEGER NOT NULL,
  17   revactor_actor INTEGER NOT NULL,
  18   revactor_timestamp TIMESTAMPTZ  NOT NULL,
  19   revactor_page INTEGER          NULL  REFERENCES page (page_id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  20   PRIMARY KEY (revactor_rev, revactor_actor)
  21 );
  22 CREATE UNIQUE INDEX revactor_rev ON revision_actor_temp (revactor_rev);
  23 CREATE INDEX rev_actor_timestamp ON revision_actor_temp (revactor_actor,revactor_timestamp);
  24 CREATE INDEX rev_page_actor_timestamp ON revision_actor_temp (revactor_page,revactor_actor,revactor_timestamp);