diff options
author | morkt <> | 2014-12-22 11:14:18 +0100 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2014-12-22 11:20:39 +0100 |
commit | ac784a64d0f74f9e04ac41890075c411e50ca823 (patch) | |
tree | f527926f531975751563e3d9286ce02212152850 /util | |
parent | df383d117908160d1a84ce4519edc0c9bcf08c3b (diff) |
Initial implementation of a staff/seiyuu database
Diffstat (limited to 'util')
-rwxr-xr-x | util/jsgen.pl | 1 | ||||
-rw-r--r-- | util/sql/func.sql | 54 | ||||
-rw-r--r-- | util/sql/staff.sql | 59 |
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); |