diff options
Diffstat (limited to 'util/updates/update_2.24-staff.sql')
-rw-r--r-- | util/updates/update_2.24-staff.sql | 67 |
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(); |