Staff editions

As discussed in https://vndb.org/t13027 and from
https://vndb.org/t6138.327 onwards.

3 files changed, 33 insertions, 6 deletions

diff --git a/sql/perms.sql b/sql/perms.sql
index 816a45c6..786c0ebc 100644
--- a/sql/perms.sql
+++ b/sql/perms.sql
@@ -88,6 +88,8 @@ GRANT SELECT, INSERT, UPDATE, DELETE ON users_traits             TO vndb_site;
 GRANT SELECT, INSERT, UPDATE         ON vn                       TO vndb_site;
 GRANT SELECT, INSERT,         DELETE ON vn_anime                 TO vndb_site;
 GRANT SELECT, INSERT                 ON vn_anime_hist            TO vndb_site;
+GRANT SELECT, INSERT,         DELETE ON vn_editions              TO vndb_site;
+GRANT SELECT, INSERT                 ON vn_editions_hist         TO vndb_site;
 GRANT SELECT, INSERT                 ON vn_hist                  TO vndb_site;
 GRANT SELECT, INSERT, UPDATE, DELETE ON vn_length_votes          TO vndb_site;
 GRANT SELECT, INSERT,         DELETE ON vn_relations             TO vndb_site;
diff --git a/sql/schema.sql b/sql/schema.sql
index 155a2881..e6fc7874 100644
--- a/sql/schema.sql
+++ b/sql/schema.sql
@@ -57,7 +57,7 @@ CREATE TYPE anime_type        AS ENUM ('tv', 'ova', 'mov', 'oth', 'web', 'spe',
 CREATE TYPE blood_type        AS ENUM ('unknown', 'a', 'b', 'ab', 'o');
 CREATE TYPE board_type        AS ENUM ('an', 'db', 'ge', 'v', 'p', 'u');
 CREATE TYPE char_role         AS ENUM ('main', 'primary', 'side', 'appears');
-CREATE TYPE credit_type       AS ENUM ('scenario', 'chardesign', 'art', 'music', 'songs', 'director', 'staff');
+CREATE TYPE credit_type       AS ENUM ('scenario', 'chardesign', 'art', 'music', 'songs', 'director', 'translator', 'editor', 'qa', 'staff');
 CREATE TYPE cup_size          AS ENUM ('', 'AAA', 'AA', 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z');
 CREATE TYPE dbentry_type      AS ENUM ('v', 'r', 'p', 'c', 's', 'd');
 CREATE TYPE gender            AS ENUM ('unknown', 'm', 'f', 'b');
@@ -1091,7 +1091,10 @@ CREATE TABLE users_prefs (
   prodrelexpand       boolean NOT NULL DEFAULT true,
   vnrel_langs         language[], -- NULL meaning "show all languages"
   vnrel_olang         boolean NOT NULL DEFAULT true,
-  vnrel_mtl           boolean NOT NULL DEFAULT false
+  vnrel_mtl           boolean NOT NULL DEFAULT false,
+  staffed_langs       language[],
+  staffed_olang       boolean NOT NULL DEFAULT true,
+  staffed_unoff       boolean NOT NULL DEFAULT false
 );
 
 -- Additional fields for the 'users' table, but with some protected columns.
@@ -1190,6 +1193,26 @@ CREATE TABLE vn_anime_hist (
   PRIMARY KEY(chid, aid)
 );
 
+-- vn_editions
+CREATE TABLE vn_editions (
+  id         vndbid NOT NULL, -- [pub]
+  lang       language, -- [pub]
+  eid        smallint NOT NULL, -- [pub] (not stable across revisions)
+  official   boolean NOT NULL DEFAULT TRUE, -- [pub]
+  name       text NOT NULL, -- [pub]
+  PRIMARY KEY(id, eid)
+);
+
+-- vn_editions_hist
+CREATE TABLE vn_editions_hist (
+  chid       integer NOT NULL,
+  lang       language,
+  eid        smallint NOT NULL,
+  official   boolean NOT NULL DEFAULT TRUE,
+  name       text NOT NULL,
+  PRIMARY KEY(chid, eid)
+);
+
 -- vn_relations
 CREATE TABLE vn_relations (
   id         vndbid NOT NULL, -- [pub]
@@ -1250,7 +1273,7 @@ CREATE TABLE vn_staff (
   aid        integer NOT NULL, -- [pub] staff_alias.aid
   role       credit_type NOT NULL DEFAULT 'staff', -- [pub]
   note       varchar(250) NOT NULL DEFAULT '', -- [pub]
-  PRIMARY KEY (id, aid, role)
+  eid        smallint -- [pub]
 );
 
 -- vn_staff_hist
@@ -1259,7 +1282,7 @@ CREATE TABLE vn_staff_hist (
   aid        integer NOT NULL, -- See note at vn_seiyuu_hist.aid
   role       credit_type NOT NULL DEFAULT 'staff',
   note       varchar(250) NOT NULL DEFAULT '',
-  PRIMARY KEY (chid, aid, role)
+  eid        smallint
 );
 
 -- vn_titles
diff --git a/sql/tableattrs.sql b/sql/tableattrs.sql
index 176657e8..c4cd3462 100644
--- a/sql/tableattrs.sql
+++ b/sql/tableattrs.sql
@@ -37,6 +37,8 @@ CREATE        INDEX vn_image               ON vn (image);
 CREATE        INDEX vn_screenshots_scr     ON vn_screenshots (scr);
 CREATE        INDEX vn_seiyuu_aid          ON vn_seiyuu (aid); -- Only used on /s+?
 CREATE        INDEX vn_seiyuu_cid          ON vn_seiyuu (cid); -- Only used on /c+?
+CREATE UNIQUE INDEX vn_staff_pkey          ON vn_staff (id, COALESCE(eid,-1::smallint), aid, role);
+CREATE UNIQUE INDEX vn_staff_hist_pkey     ON vn_staff_hist (chid, COALESCE(eid,-1::smallint), aid, role);
 CREATE        INDEX vn_staff_aid           ON vn_staff (aid);
 CREATE UNIQUE INDEX vn_length_votes_vid_uid ON vn_length_votes (vid, uid);
 CREATE        INDEX vn_length_votes_uid    ON vn_length_votes (uid);
@@ -170,9 +172,9 @@ ALTER TABLE vn_seiyuu                ADD CONSTRAINT vn_seiyuu_aid_fkey
 ALTER TABLE vn_seiyuu                ADD CONSTRAINT vn_seiyuu_cid_fkey                 FOREIGN KEY (cid)       REFERENCES chars         (id);
 ALTER TABLE vn_seiyuu_hist           ADD CONSTRAINT vn_seiyuu_hist_chid_fkey           FOREIGN KEY (chid)      REFERENCES changes       (id) ON DELETE CASCADE;
 ALTER TABLE vn_seiyuu_hist           ADD CONSTRAINT vn_seiyuu_hist_cid_fkey            FOREIGN KEY (cid)       REFERENCES chars         (id);
-ALTER TABLE vn_staff                 ADD CONSTRAINT vn_staff_id_fkey                   FOREIGN KEY (id)        REFERENCES vn            (id);
+ALTER TABLE vn_staff                 ADD CONSTRAINT vn_staff_id_eid_fkey               FOREIGN KEY (id,eid)    REFERENCES vn_editions   (id,eid) DEFERRABLE INITIALLY DEFERRED;
 ALTER TABLE vn_staff                 ADD CONSTRAINT vn_staff_aid_fkey                  FOREIGN KEY (aid)       REFERENCES staff_alias   (aid) DEFERRABLE INITIALLY DEFERRED;
-ALTER TABLE vn_staff_hist            ADD CONSTRAINT vn_staff_hist_chid_fkey            FOREIGN KEY (chid)      REFERENCES changes       (id) ON DELETE CASCADE;
+ALTER TABLE vn_staff_hist            ADD CONSTRAINT vn_staff_hist_chid_eid_fkey        FOREIGN KEY (chid,eid)  REFERENCES vn_editions_hist (chid,eid) DEFERRABLE INITIALLY DEFERRED;
 ALTER TABLE vn_titles                ADD CONSTRAINT vn_titles_id_fkey                  FOREIGN KEY (id)        REFERENCES vn            (id);
 ALTER TABLE vn_titles_hist           ADD CONSTRAINT vn_titles_hist_chid_fkey           FOREIGN KEY (chid)      REFERENCES changes       (id) ON DELETE CASCADE;
 ALTER TABLE vn_length_votes          ADD CONSTRAINT vn_length_votes_vid_fkey           FOREIGN KEY (vid)       REFERENCES vn            (id);