summaryrefslogtreecommitdiff
path: root/util
diff options
context:
space:
mode:
authormorkt <>2014-12-28 09:39:21 +0100
committerYorhel <git@yorhel.nl>2014-12-28 09:39:21 +0100
commit6399d6d4e21bd4464c7224f821d6ea071ea7a614 (patch)
tree0eea1eae38e2b98d2f0a235866f6e03749a6b0fa /util
parent8719a8e69f5fc46c4ccf44e6e99c33b60c342d76 (diff)
More progress on the staff + cast DB
Diffstat (limited to 'util')
-rw-r--r--util/sql/func.sql7
-rw-r--r--util/sql/staff.sql14
2 files changed, 12 insertions, 9 deletions
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);