1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
|
-- 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 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();
|