summaryrefslogtreecommitdiff
path: root/util/sql/staff.sql
blob: 0c91deb45faa7d235a5c73835fff806d6feee156 (plain)
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
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();