From 6399d6d4e21bd4464c7224f821d6ea071ea7a614 Mon Sep 17 00:00:00 2001
From: morkt <>
Date: Sun, 28 Dec 2014 09:39:21 +0100
Subject: More progress on the staff + cast DB

---
 util/sql/func.sql  |  7 ++++++-
 util/sql/staff.sql | 14 ++++++--------
 2 files changed, 12 insertions(+), 9 deletions(-)

(limited to 'util')

diff --git a/util/sql/func.sql b/util/sql/func.sql
index 787ae8b8..a1d776d2 100644
--- a/util/sql/func.sql
+++ b/util/sql/func.sql
@@ -256,6 +256,7 @@ $$ LANGUAGE plpgsql;
 
 
 CREATE OR REPLACE FUNCTION edit_vn_init(cid integer) RETURNS void AS $$
+#variable_conflict use_variable
 BEGIN
   -- create tables, based on existing tables (so that the column types are always synchronised)
   BEGIN
@@ -271,8 +272,10 @@ BEGIN
     ALTER TABLE edit_vn_screenshots DROP COLUMN vid;
     CREATE TEMPORARY TABLE edit_vn_staff (LIKE vn_staff INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
     ALTER TABLE edit_vn_staff DROP COLUMN vid;
+    CREATE TEMPORARY TABLE edit_vn_seiyuu (LIKE vn_seiyuu INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
+    ALTER TABLE edit_vn_seiyuu DROP COLUMN vid;
   EXCEPTION WHEN duplicate_table THEN
-    TRUNCATE edit_vn, edit_vn_anime, edit_vn_relations, edit_vn_screenshots, edit_vn_staff;
+    TRUNCATE edit_vn, edit_vn_anime, edit_vn_relations, edit_vn_screenshots, edit_vn_staff, edit_vn_seiyuu;
   END;
   PERFORM edit_revtable('v', cid);
   -- new VN, load defaults
@@ -285,6 +288,7 @@ BEGIN
     INSERT INTO edit_vn_relations SELECT vid2, relation, official FROM vn_relations WHERE vid1 = cid;
     INSERT INTO edit_vn_screenshots SELECT scr, nsfw, rid FROM vn_screenshots WHERE vid = cid;
     INSERT INTO edit_vn_staff SELECT aid, role, note FROM vn_staff WHERE vid = cid;
+    INSERT INTO edit_vn_seiyuu SELECT aid, vs.cid, note FROM vn_seiyuu vs WHERE vid = cid;
   END IF;
 END;
 $$ LANGUAGE plpgsql;
@@ -304,6 +308,7 @@ BEGIN
   INSERT INTO vn_relations SELECT r.cid, vid, relation, official FROM edit_vn_relations;
   INSERT INTO vn_screenshots SELECT r.cid, scr, nsfw, rid FROM edit_vn_screenshots;
   INSERT INTO vn_staff SELECT r.cid, aid, role, note FROM edit_vn_staff;
+  INSERT INTO vn_seiyuu SELECT r.cid, aid, cid, note FROM edit_vn_seiyuu;
   UPDATE vn SET latest = r.cid WHERE id = r.iid;
   RETURN r;
 END;
diff --git a/util/sql/staff.sql b/util/sql/staff.sql
index a53b8955..c4ff41ba 100644
--- a/util/sql/staff.sql
+++ b/util/sql/staff.sql
@@ -37,12 +37,12 @@ CREATE TABLE vn_staff (
     PRIMARY KEY (vid, aid, role)
 );
 
-CREATE TABLE chars_seiyuu (
-    cid         integer NOT NULL, -- chars_rev reference
-    vid         integer NOT NULL, -- vn reference
+CREATE TABLE vn_seiyuu (
+    vid         integer NOT NULL, -- vn_rev reference
     aid         integer NOT NULL, -- staff_alias reference
+    cid         integer NOT NULL, -- chars reference
     note        varchar(250) NOT NULL DEFAULT '',
-    PRIMARY KEY (cid, vid, aid)
+    PRIMARY KEY (vid, aid, cid)
 );
 
 ALTER TABLE staff               ADD FOREIGN KEY (latest)    REFERENCES staff_rev     (id) DEFERRABLE INITIALLY DEFERRED;
@@ -51,10 +51,8 @@ ALTER TABLE staff_rev           ADD FOREIGN KEY (id)        REFERENCES changes
 ALTER TABLE staff_rev           ADD FOREIGN KEY (sid)       REFERENCES staff         (id);
 ALTER TABLE staff_rev           ADD FOREIGN KEY (aid,id)    REFERENCES staff_alias   (id,rid);
 ALTER TABLE vn_staff            ADD FOREIGN KEY (vid)       REFERENCES vn_rev        (id);
-ALTER TABLE chars_seiyuu        ADD FOREIGN KEY (cid)       REFERENCES chars_rev     (id);
-ALTER TABLE chars_seiyuu        ADD FOREIGN KEY (vid)       REFERENCES vn            (id);
+ALTER TABLE vn_seiyuu           ADD FOREIGN KEY (cid)       REFERENCES chars         (id);
+ALTER TABLE vn_seiyuu           ADD FOREIGN KEY (vid)       REFERENCES vn_rev        (id);
 
-CREATE INDEX chars_seiyuu_pkey  ON chars_seiyuu (cid, vid);
-CREATE INDEX chars_seiyuu_aid   ON chars_seiyuu (aid);
 CREATE INDEX vn_staff_vid       ON vn_staff (vid);
 CREATE INDEX vn_staff_aid       ON vn_staff (aid);
-- 
cgit v1.2.3