summaryrefslogtreecommitdiff
path: root/util
diff options
context:
space:
mode:
Diffstat (limited to 'util')
-rwxr-xr-xutil/jsgen.pl1
-rw-r--r--util/sql/func.sql69
-rw-r--r--util/sql/staff.sql67
3 files changed, 133 insertions, 4 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..f7125607 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;
@@ -253,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
@@ -266,8 +270,12 @@ 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;
+ 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;
+ 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
@@ -279,6 +287,8 @@ 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;
+ INSERT INTO edit_vn_seiyuu SELECT aid, vs.cid, note FROM vn_seiyuu vs WHERE vid = cid;
END IF;
END;
$$ LANGUAGE plpgsql;
@@ -297,6 +307,8 @@ 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;
+ 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;
@@ -454,6 +466,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, gender, lang, "desc", l_wp, l_site, l_twitter, l_anidb 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, gender, lang, "desc", l_wp, l_site, l_twitter, l_anidb)
+ SELECT r.cid, r.iid, aid, gender, lang, "desc", l_wp, l_site, l_twitter, l_anidb FROM edit_staff;
+ UPDATE staff SET latest = r.cid WHERE id = r.iid;
+ RETURN r;
+END;
+$$ LANGUAGE plpgsql;
+
+
+
----------------------------------------------------------
@@ -793,7 +848,7 @@ CREATE OR REPLACE FUNCTION notify_dbdel() RETURNS trigger AS $$
BEGIN
INSERT INTO notifications (ntype, ltype, uid, iid, subid, c_title, c_byuser)
SELECT DISTINCT 'dbdel'::notification_ntype,
- (CASE TG_TABLE_NAME WHEN 'vn' THEN 'v' WHEN 'releases' THEN 'r' WHEN 'producers' THEN 'p' ELSE 'c' END)::notification_ltype,
+ (CASE TG_TABLE_NAME WHEN 'vn' THEN 'v' WHEN 'releases' THEN 'r' WHEN 'producers' THEN 'p' WHEN 'staff' THEN 's' ELSE 'c' END)::notification_ltype,
h.requester, NEW.id, h2.rev, x.title, h2.requester
-- look for changes of the deleted entry
-- this method may look a bit unintuitive, but it's way faster than doing LEFT JOINs
@@ -810,6 +865,9 @@ BEGIN
UNION SELECT cr.id, cr2.name FROM chars_rev cr
JOIN chars c ON c.id = cr.cid JOIN chars_rev cr2 ON cr2.id = c.latest
WHERE TG_TABLE_NAME = 'chars' AND cr.cid = NEW.id
+ UNION SELECT sr.id, sa.name FROM staff_rev sr
+ JOIN staff s ON s.id = sr.sid JOIN staff_rev sr2 ON sr2.id = s.latest JOIN staff_alias sa ON sa.id = sr2.aid AND sa.rid = sr2.id
+ WHERE TG_TABLE_NAME = 'staff' AND sr.sid = NEW.id
) x(id, title) ON h.id = x.id
-- join info about the deletion itself
JOIN changes h2 ON h2.id = NEW.latest
@@ -857,7 +915,7 @@ CREATE OR REPLACE FUNCTION notify_dbedit() RETURNS trigger AS $$
BEGIN
INSERT INTO notifications (ntype, ltype, uid, iid, subid, c_title, c_byuser)
SELECT DISTINCT 'dbedit'::notification_ntype,
- (CASE TG_TABLE_NAME WHEN 'vn' THEN 'v' WHEN 'releases' THEN 'r' WHEN 'producers' THEN 'p' ELSE 'c' END)::notification_ltype,
+ (CASE TG_TABLE_NAME WHEN 'vn' THEN 'v' WHEN 'releases' THEN 'r' WHEN 'producers' THEN 'p' WHEN 'staff' THEN 's' ELSE 'c' END)::notification_ltype,
h.requester, NEW.id, h2.rev, x.title, h2.requester
-- look for changes of the edited entry
FROM changes h
@@ -873,6 +931,9 @@ BEGIN
UNION SELECT cr.id, cr2.name FROM chars_rev cr
JOIN chars c ON c.id = cr.cid JOIN chars_rev cr2 ON cr2.id = c.latest
WHERE TG_TABLE_NAME = 'chars' AND cr.cid = NEW.id
+ UNION SELECT sr.id, sa.name FROM staff_rev sr
+ JOIN staff s ON s.id = sr.sid JOIN staff_rev sr2 ON sr2.id = s.latest JOIN staff_alias sa ON sa.id = sr2.aid AND sa.rid = sr2.id
+ WHERE TG_TABLE_NAME = 'staff' AND sr.sid = NEW.id
) x(id, title) ON h.id = x.id
-- join info about the deletion itself
JOIN changes h2 ON h2.id = NEW.latest
diff --git a/util/sql/staff.sql b/util/sql/staff.sql
new file mode 100644
index 00000000..0c91deb4
--- /dev/null
+++ b/util/sql/staff.sql
@@ -0,0 +1,67 @@
+-- database schema for staff/seiyuu
+
+ALTER TYPE dbentry_type ADD VALUE 's';
+ALTER TYPE notification_ltype 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
+ gender gender NOT NULL DEFAULT 'unknown',
+ lang language NOT NULL DEFAULT 'ja',
+ "desc" text NOT NULL DEFAULT '',
+ l_wp varchar(150) NOT NULL DEFAULT '',
+ l_site varchar(250) NOT NULL DEFAULT '',
+ l_twitter varchar(16) NOT NULL DEFAULT '',
+ l_anidb integer
+);
+
+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 '',
+ PRIMARY KEY (vid, aid, role)
+);
+
+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 (vid, aid, cid)
+);
+
+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 vn_seiyuu ADD FOREIGN KEY (cid) REFERENCES chars (id);
+ALTER TABLE vn_seiyuu ADD FOREIGN KEY (vid) REFERENCES vn_rev (id);
+
+CREATE INDEX vn_staff_vid ON vn_staff (vid);
+CREATE INDEX vn_staff_aid ON vn_staff (aid);
+--CREATE INDEX staff_alias_orig ON staff_alias (translate(original,' ',''));
+
+CREATE TRIGGER hidlock_update BEFORE UPDATE ON staff FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest) EXECUTE PROCEDURE update_hidlock();
+
+CREATE TRIGGER notify_dbdel AFTER UPDATE ON staff FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_dbdel();
+CREATE TRIGGER notify_dbedit AFTER UPDATE ON staff FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest AND NOT NEW.hidden) EXECUTE PROCEDURE notify_dbedit();
generated by cgit v1.2.3 (git 2.25.1) at 2024-04-16 11:40:29 +0000