summaryrefslogtreecommitdiff
path: root/util/updates/update_2.24-staff.sql
diff options
context:
space:
mode:
Diffstat (limited to 'util/updates/update_2.24-staff.sql')
-rw-r--r--util/updates/update_2.24-staff.sql67
1 files changed, 67 insertions, 0 deletions
diff --git a/util/updates/update_2.24-staff.sql b/util/updates/update_2.24-staff.sql
new file mode 100644
index 00000000..0c91deb4
--- /dev/null
+++ b/util/updates/update_2.24-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();