summaryrefslogtreecommitdiff
path: root/util/sql/schema.sql
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2015-01-28 13:20:30 +0100
committerYorhel <git@yorhel.nl>2015-01-28 13:20:30 +0100
commitf255e2565d48f00d5350654f728905b6670b9888 (patch)
tree55cc00efe0fab4a30a38badb672d4805e39fd132 /util/sql/schema.sql
parent4686f69cd4fd2edecaf3424b4359cea2bf325efa (diff)
sql: Merge staff changes into /util/sql/ and rename staff.sql2.24
So that the /util/sql/ files are in sync with the actual DB again.
Diffstat (limited to 'util/sql/schema.sql')
-rw-r--r--util/sql/schema.sql58
1 files changed, 58 insertions, 0 deletions
diff --git a/util/sql/schema.sql b/util/sql/schema.sql
index a3bb29b0..5c4342e2 100644
--- a/util/sql/schema.sql
+++ b/util/sql/schema.sql
@@ -244,6 +244,37 @@ CREATE TABLE sessions (
PRIMARY KEY (uid, token)
);
+-- 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
+);
+
+-- staff_alias
+CREATE TABLE staff_alias (
+ id SERIAL NOT NULL,
+ rid integer,
+ name varchar(200) NOT NULL DEFAULT '',
+ original varchar(200) NOT NULL DEFAULT '',
+ PRIMARY KEY (id, rid)
+);
+
+-- staff_rev
+CREATE TABLE staff_rev (
+ id integer NOT NULL PRIMARY KEY,
+ sid integer NOT NULL,
+ aid integer NOT NULL,
+ 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
+);
+
-- stats_cache
CREATE TABLE stats_cache (
section varchar(25) NOT NULL PRIMARY KEY,
@@ -454,6 +485,23 @@ CREATE TABLE vn_screenshots (
PRIMARY KEY(vid, scr)
);
+-- vn_seiyuu
+CREATE TABLE vn_seiyuu (
+ vid integer NOT NULL,
+ aid integer NOT NULL,
+ cid integer NOT NULL,
+ note varchar(250) NOT NULL DEFAULT '',
+ PRIMARY KEY (vid, aid, cid)
+);
+
+-- vn_staff
+CREATE TABLE vn_staff (
+ vid integer NOT NULL,
+ aid integer NOT NULL,
+ role credit_type NOT NULL DEFAULT 'staff',
+ note varchar(250) NOT NULL DEFAULT '',
+ PRIMARY KEY (vid, aid, role)
+);
-- vnlists
CREATE TABLE vnlists (
@@ -518,6 +566,11 @@ ALTER TABLE releases_vn ADD FOREIGN KEY (vid) REFERENCES vn
ALTER TABLE rlists ADD FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
ALTER TABLE rlists ADD FOREIGN KEY (rid) REFERENCES releases (id);
ALTER TABLE sessions ADD FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
+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 tags ADD FOREIGN KEY (addedby) REFERENCES users (id) ON DELETE SET DEFAULT;
ALTER TABLE tags_aliases ADD FOREIGN KEY (tag) REFERENCES tags (id);
ALTER TABLE tags_parents ADD FOREIGN KEY (tag) REFERENCES tags (id);
@@ -547,6 +600,9 @@ ALTER TABLE vn_rev ADD FOREIGN KEY (vid) REFERENCES vn
ALTER TABLE vn_screenshots ADD FOREIGN KEY (vid) REFERENCES vn_rev (id);
ALTER TABLE vn_screenshots ADD FOREIGN KEY (scr) REFERENCES screenshots (id);
ALTER TABLE vn_screenshots ADD FOREIGN KEY (rid) REFERENCES releases (id);
+ALTER TABLE vn_seiyuu ADD FOREIGN KEY (cid) REFERENCES chars (id);
+ALTER TABLE vn_seiyuu ADD FOREIGN KEY (vid) REFERENCES vn_rev (id);
+ALTER TABLE vn_staff ADD FOREIGN KEY (vid) REFERENCES vn_rev (id);
ALTER TABLE vnlists ADD FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
ALTER TABLE vnlists ADD FOREIGN KEY (vid) REFERENCES vn (id);
ALTER TABLE votes ADD FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
@@ -560,5 +616,7 @@ CREATE INDEX releases_producers_rid ON releases_producers (rid);
CREATE INDEX releases_vn_vid ON releases_vn (vid);
CREATE INDEX tags_vn_date ON tags_vn (date);
CREATE INDEX tags_vn_vid ON tags_vn (vid);
+CREATE INDEX vn_staff_vid ON vn_staff (vid);
+CREATE INDEX vn_staff_aid ON vn_staff (aid);
CREATE UNIQUE INDEX chars_vns_pkey ON chars_vns (cid, vid, COALESCE(rid, 0));