summaryrefslogtreecommitdiff
path: root/util
diff options
context:
space:
mode:
authormorkt <>2014-12-22 11:14:18 +0100
committerYorhel <git@yorhel.nl>2014-12-22 11:20:39 +0100
commitac784a64d0f74f9e04ac41890075c411e50ca823 (patch)
treef527926f531975751563e3d9286ce02212152850 /util
parentdf383d117908160d1a84ce4519edc0c9bcf08c3b (diff)
Initial implementation of a staff/seiyuu database
Diffstat (limited to 'util')
-rwxr-xr-xutil/jsgen.pl1
-rw-r--r--util/sql/func.sql54
-rw-r--r--util/sql/staff.sql59
3 files changed, 112 insertions, 2 deletions
diff --git a/util/jsgen.pl b/util/jsgen.pl
index 26815423..eb7d2a8a 100755
--- a/util/jsgen.pl
+++ b/util/jsgen.pl
@@ -114,6 +114,7 @@ sub jsgen {
$common .= sprintf "blood_types = [ %s ];\n", join ', ', map qq{"$_"}, @{$S{blood_types}};
$common .= sprintf "genders = [ %s ];\n", join ', ', map qq{"$_"}, @{$S{genders}};
$common .= sprintf "char_roles = [ %s ];\n", join ', ', map qq{"$_"}, @{$S{char_roles}};
+ $common .= sprintf "staff_roles = [ %s ];\n", join ', ', map qq{"$_"}, @{$S{staff_roles}};
$common .= sprintf "L10N_LANG = [ %s ];\n", join(', ', map
sprintf('["%s","%s"]', $_, $lang{$_}{"_lang_$_"}||$lang{en}{"_lang_$_"}),
VNDB::L10N::languages());
diff --git a/util/sql/func.sql b/util/sql/func.sql
index 360f52f6..787ae8b8 100644
--- a/util/sql/func.sql
+++ b/util/sql/func.sql
@@ -200,7 +200,8 @@ BEGIN
( SELECT vid FROM vn_rev WHERE id = i
UNION SELECT rid FROM releases_rev WHERE id = i
UNION SELECT cid FROM chars_rev WHERE id = i
- UNION SELECT pid FROM producers_rev WHERE id = i),
+ UNION SELECT pid FROM producers_rev WHERE id = i
+ UNION SELECT sid FROM staff_rev WHERE id = i),
COALESCE((SELECT ihid FROM changes WHERE id = i), FALSE),
COALESCE((SELECT ilock FROM changes WHERE id = i), FALSE)
);
@@ -226,6 +227,7 @@ BEGIN
UNION SELECT id FROM releases_rev WHERE t = 'r' AND rid = i
UNION SELECT id FROM producers_rev WHERE t = 'p' AND pid = i
UNION SELECT id FROM chars_rev WHERE t = 'c' AND cid = i
+ UNION SELECT id FROM staff_rev WHERE t = 's' AND sid = i
) x(id) ON x.id = c.id
ORDER BY c.id DESC
LIMIT 1;
@@ -242,6 +244,7 @@ BEGIN
WHEN 'r' THEN INSERT INTO releases (latest) VALUES (0) RETURNING id INTO r.iid;
WHEN 'p' THEN INSERT INTO producers (latest) VALUES (0) RETURNING id INTO r.iid;
WHEN 'c' THEN INSERT INTO chars (latest) VALUES (0) RETURNING id INTO r.iid;
+ WHEN 's' THEN INSERT INTO staff (latest) VALUES (0) RETURNING id INTO r.iid;
END CASE;
ELSE
r.iid := i;
@@ -266,8 +269,10 @@ BEGIN
ALTER TABLE edit_vn_relations RENAME COLUMN vid2 TO vid;
CREATE TEMPORARY TABLE edit_vn_screenshots (LIKE vn_screenshots INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
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;
EXCEPTION WHEN duplicate_table THEN
- TRUNCATE edit_vn, edit_vn_anime, edit_vn_relations, edit_vn_screenshots;
+ TRUNCATE edit_vn, edit_vn_anime, edit_vn_relations, edit_vn_screenshots, edit_vn_staff;
END;
PERFORM edit_revtable('v', cid);
-- new VN, load defaults
@@ -279,6 +284,7 @@ BEGIN
INSERT INTO edit_vn_anime SELECT aid FROM vn_anime WHERE vid = cid;
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;
END IF;
END;
$$ LANGUAGE plpgsql;
@@ -297,6 +303,7 @@ BEGIN
INSERT INTO vn_anime SELECT r.cid, aid FROM edit_vn_anime;
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;
UPDATE vn SET latest = r.cid WHERE id = r.iid;
RETURN r;
END;
@@ -454,6 +461,49 @@ $$ LANGUAGE plpgsql;
+CREATE OR REPLACE FUNCTION edit_staff_init(cid integer) RETURNS void AS $$
+BEGIN
+ BEGIN
+ CREATE TEMPORARY TABLE edit_staff (LIKE staff_rev INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
+ ALTER TABLE edit_staff DROP COLUMN id;
+ ALTER TABLE edit_staff DROP COLUMN sid;
+ CREATE TEMPORARY TABLE edit_staff_aliases (LIKE staff_alias INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
+ ALTER TABLE edit_staff_aliases DROP COLUMN rid;
+ EXCEPTION WHEN duplicate_table THEN
+ TRUNCATE edit_staff, edit_staff_aliases;
+ END;
+ PERFORM edit_revtable('s', cid);
+ -- new staff member
+ IF cid IS NULL THEN
+ INSERT INTO edit_staff (aid) VALUES (0);
+ -- load revision
+ ELSE
+ INSERT INTO edit_staff SELECT aid, image, gender, lang, "desc", l_wp FROM staff_rev WHERE id = cid;
+ INSERT INTO edit_staff_aliases SELECT id, name, original FROM staff_alias WHERE rid = cid;
+ END IF;
+END;
+$$ LANGUAGE plpgsql;
+
+
+CREATE OR REPLACE FUNCTION edit_staff_commit() RETURNS edit_rettype AS $$
+DECLARE
+ r edit_rettype;
+BEGIN
+ IF (SELECT COUNT(*) FROM edit_staff) <> 1 THEN
+ RAISE 'edit_staff must have exactly one row!';
+ END IF;
+ SELECT INTO r * FROM edit_commit();
+ INSERT INTO staff_alias (id, rid, name, original)
+ SELECT id, r.cid, name, original FROM edit_staff_aliases;
+ INSERT INTO staff_rev (id, sid, aid, image, gender, lang, "desc", l_wp)
+ SELECT r.cid, r.iid, aid, image, gender, lang, "desc", l_wp FROM edit_staff;
+ UPDATE staff SET latest = r.cid WHERE id = r.iid;
+ RETURN r;
+END;
+$$ LANGUAGE plpgsql;
+
+
+
----------------------------------------------------------
diff --git a/util/sql/staff.sql b/util/sql/staff.sql
new file mode 100644
index 00000000..074aacae
--- /dev/null
+++ b/util/sql/staff.sql
@@ -0,0 +1,59 @@
+-- database schema for staff/seiyuu
+
+ALTER TYPE dbentry_type ADD VALUE 's';
+CREATE TYPE credit_type AS ENUM ('script', 'chardesign', 'music', 'director', 'art', 'songs', 'staff');
+
+CREATE TABLE staff (
+ id SERIAL NOT NULL PRIMARY KEY,
+ latest integer NOT NULL DEFAULT 0,
+ locked boolean NOT NULL DEFAULT FALSE,
+ hidden boolean NOT NULL DEFAULT FALSE
+);
+
+CREATE TABLE staff_rev (
+ id integer NOT NULL PRIMARY KEY,
+ sid integer NOT NULL, -- references staff
+ aid integer NOT NULL, -- true name, references staff_alias
+ image integer NOT NULL DEFAULT 0,
+ gender gender NOT NULL DEFAULT 'unknown',
+ lang language NOT NULL DEFAULT 'ja',
+ "desc" text NOT NULL DEFAULT '',
+ l_wp varchar(150) NOT NULL DEFAULT ''
+);
+
+CREATE TABLE staff_alias (
+ id SERIAL NOT NULL,
+ rid integer, -- references staff_rev
+ name varchar(200) NOT NULL DEFAULT '',
+ original varchar(200) NOT NULL DEFAULT '',
+ PRIMARY KEY (id, rid)
+);
+
+CREATE TABLE vn_staff (
+ vid integer NOT NULL, -- vn_rev reference
+ aid integer NOT NULL, -- staff_alias reference
+ role credit_type NOT NULL DEFAULT 'staff',
+ note varchar(250) NOT NULL DEFAULT ''
+);
+
+CREATE TABLE chars_seiyuu (
+ cid integer NOT NULL, -- chars_rev reference
+ vid integer NOT NULL, -- vn reference
+ aid integer NOT NULL, -- staff_alias reference
+ note varchar(250) NOT NULL DEFAULT '',
+ PRIMARY KEY (cid, vid, aid)
+);
+
+ALTER TABLE staff ADD FOREIGN KEY (latest) REFERENCES staff_rev (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE staff_alias ADD FOREIGN KEY (rid) REFERENCES staff_rev (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE staff_rev ADD FOREIGN KEY (id) REFERENCES changes (id);
+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);
+
+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);