summaryrefslogtreecommitdiff
path: root/util/sql
diff options
context:
space:
mode:
Diffstat (limited to 'util/sql')
-rw-r--r--util/sql/schema.sql511
-rw-r--r--util/sql/tableattrs.sql177
2 files changed, 453 insertions, 235 deletions
diff --git a/util/sql/schema.sql b/util/sql/schema.sql
index 59240ea9..b43079dc 100644
--- a/util/sql/schema.sql
+++ b/util/sql/schema.sql
@@ -1,3 +1,38 @@
+-- Convention for database items with version control:
+--
+-- CREATE TABLE items (
+-- id SERIAL PRIMARY KEY,
+-- locked boolean NOT NULL DEFAULT FALSE,
+-- hidden boolean NOT NULL DEFAULT FALSE,
+-- -- item-specific columns here
+-- );
+-- CREATE TABLE items_hist ( -- History of the 'items' table
+-- chid integer NOT NULL, -- references changes.id
+-- -- item-specific columns here
+-- );
+--
+-- The history of the 'locked' and 'hidden' flags is recorded in the changes
+-- table. It's possible for 'items' to have more item-specific columns than
+-- 'items_hist'. Some columns are caches or otherwise autogenerated, and do not
+-- need to be versioned.
+--
+-- item-related tables work roughly the same:
+--
+-- CREATE TABLE items_field (
+-- id integer, -- references items.id
+-- -- field-specific columns here
+-- );
+-- CREATE TABLE items_field_hist ( -- History of the 'items_field' table
+-- chid integer, -- references changes.id
+-- -- field-specific columns here
+-- );
+--
+-- The changes and *_hist tables contain all the data. In a sense, the other
+-- tables related to the item are just a cache/view into the latest versions.
+-- All modifications to the item tables has to go through the edit_* functions
+-- in func.sql, these are also responsible for keeping things synchronized.
+
+
-- affiliate_links
CREATE TABLE affiliate_links (
id SERIAL PRIMARY KEY,
@@ -26,29 +61,44 @@ CREATE TABLE anime (
-- changes
CREATE TABLE changes (
- id SERIAL NOT NULL PRIMARY KEY,
- type dbentry_type NOT NULL,
- rev integer NOT NULL DEFAULT 1,
- added timestamptz NOT NULL DEFAULT NOW(),
- requester integer NOT NULL DEFAULT 0,
- ip inet NOT NULL DEFAULT '0.0.0.0',
- comments text NOT NULL DEFAULT '',
- ihid boolean NOT NULL DEFAULT FALSE,
- ilock boolean NOT NULL DEFAULT FALSE
+ id SERIAL PRIMARY KEY,
+ type dbentry_type NOT NULL,
+ itemid integer NOT NULL,
+ rev integer NOT NULL DEFAULT 1,
+ added timestamptz NOT NULL DEFAULT NOW(),
+ requester integer NOT NULL DEFAULT 0,
+ ip inet NOT NULL DEFAULT '0.0.0.0',
+ comments text NOT NULL DEFAULT '',
+ ihid boolean NOT NULL DEFAULT FALSE,
+ ilock boolean NOT NULL DEFAULT FALSE
);
-- chars
CREATE TABLE chars (
- id SERIAL PRIMARY KEY,
- latest integer NOT NULL DEFAULT 0,
- locked boolean NOT NULL DEFAULT FALSE,
- hidden boolean NOT NULL DEFAULT FALSE
+ id SERIAL PRIMARY KEY,
+ locked boolean NOT NULL DEFAULT FALSE,
+ hidden boolean NOT NULL DEFAULT FALSE,
+ name varchar(250) NOT NULL DEFAULT '',
+ original varchar(250) NOT NULL DEFAULT '',
+ alias varchar(500) NOT NULL DEFAULT '',
+ image integer NOT NULL DEFAULT 0,
+ "desc" text NOT NULL DEFAULT '',
+ gender gender NOT NULL DEFAULT 'unknown',
+ s_bust smallint NOT NULL DEFAULT 0,
+ s_waist smallint NOT NULL DEFAULT 0,
+ s_hip smallint NOT NULL DEFAULT 0,
+ b_month smallint NOT NULL DEFAULT 0,
+ b_day smallint NOT NULL DEFAULT 0,
+ height smallint NOT NULL DEFAULT 0,
+ weight smallint NOT NULL DEFAULT 0,
+ bloodt blood_type NOT NULL DEFAULT 'unknown',
+ main integer, -- chars.id
+ main_spoil smallint NOT NULL DEFAULT 0
);
--- chars_rev
-CREATE TABLE chars_rev (
- id integer NOT NULL PRIMARY KEY,
- cid integer NOT NULL,
+-- chars_hist
+CREATE TABLE chars_hist (
+ chid integer NOT NULL PRIMARY KEY,
name varchar(250) NOT NULL DEFAULT '',
original varchar(250) NOT NULL DEFAULT '',
alias varchar(500) NOT NULL DEFAULT '',
@@ -63,25 +113,42 @@ CREATE TABLE chars_rev (
height smallint NOT NULL DEFAULT 0,
weight smallint NOT NULL DEFAULT 0,
bloodt blood_type NOT NULL DEFAULT 'unknown',
- main integer,
+ main integer, -- chars.id
main_spoil smallint NOT NULL DEFAULT 0
);
-- chars_traits
CREATE TABLE chars_traits (
- cid integer NOT NULL,
- tid integer NOT NULL,
- spoil smallint NOT NULL DEFAULT 0,
- PRIMARY KEY(cid, tid)
+ id integer NOT NULL,
+ tid integer NOT NULL, -- traits.id
+ spoil smallint NOT NULL DEFAULT 0,
+ PRIMARY KEY(id, tid)
+);
+
+-- chars_traits_hist
+CREATE TABLE chars_traits_hist (
+ chid integer NOT NULL,
+ tid integer NOT NULL, -- traits.id
+ spoil smallint NOT NULL DEFAULT 0,
+ PRIMARY KEY(chid, tid)
);
-- chars_vns
CREATE TABLE chars_vns (
- cid integer NOT NULL,
- vid integer NOT NULL,
- rid integer NULL,
- spoil smallint NOT NULL DEFAULT 0,
- role char_role NOT NULL DEFAULT 'main'
+ id integer NOT NULL,
+ vid integer NOT NULL, -- vn.id
+ rid integer NULL, -- releases.id
+ spoil smallint NOT NULL DEFAULT 0,
+ role char_role NOT NULL DEFAULT 'main'
+);
+
+-- chars_vns_hist
+CREATE TABLE chars_vns_hist (
+ chid integer NOT NULL,
+ vid integer NOT NULL, -- vn.id
+ rid integer NULL, -- releases.id
+ spoil smallint NOT NULL DEFAULT 0,
+ role char_role NOT NULL DEFAULT 'main'
);
-- login_throttle
@@ -106,33 +173,47 @@ CREATE TABLE notifications (
-- producers
CREATE TABLE producers (
- id SERIAL NOT NULL PRIMARY KEY,
- latest integer NOT NULL DEFAULT 0,
- locked boolean NOT NULL DEFAULT FALSE,
- hidden boolean NOT NULL DEFAULT FALSE,
- rgraph integer
+ id SERIAL PRIMARY KEY,
+ locked boolean NOT NULL DEFAULT FALSE,
+ hidden boolean NOT NULL DEFAULT FALSE,
+ type producer_type NOT NULL DEFAULT 'co',
+ name varchar(200) NOT NULL DEFAULT '',
+ original varchar(200) NOT NULL DEFAULT '',
+ website varchar(250) NOT NULL DEFAULT '',
+ lang language NOT NULL DEFAULT 'ja',
+ "desc" text NOT NULL DEFAULT '',
+ alias varchar(500) NOT NULL DEFAULT '',
+ l_wp varchar(150),
+ rgraph integer -- relgraphs.id
+);
+
+-- producers_hist
+CREATE TABLE producers_hist (
+ chid integer NOT NULL PRIMARY KEY,
+ type producer_type NOT NULL DEFAULT 'co',
+ name varchar(200) NOT NULL DEFAULT '',
+ original varchar(200) NOT NULL DEFAULT '',
+ website varchar(250) NOT NULL DEFAULT '',
+ lang language NOT NULL DEFAULT 'ja',
+ "desc" text NOT NULL DEFAULT '',
+ alias varchar(500) NOT NULL DEFAULT '',
+ l_wp varchar(150)
);
-- producers_relations
CREATE TABLE producers_relations (
- pid1 integer NOT NULL,
- pid2 integer NOT NULL,
- relation producer_relation NOT NULL,
- PRIMARY KEY(pid1, pid2)
+ id integer NOT NULL,
+ pid integer NOT NULL, -- producers.id
+ relation producer_relation NOT NULL,
+ PRIMARY KEY(id, pid)
);
--- producers_rev
-CREATE TABLE producers_rev (
- id integer NOT NULL PRIMARY KEY,
- pid integer NOT NULL DEFAULT 0,
- type producer_type NOT NULL DEFAULT 'co',
- name varchar(200) NOT NULL DEFAULT '',
- original varchar(200) NOT NULL DEFAULT '',
- website varchar(250) NOT NULL DEFAULT '',
- lang language NOT NULL DEFAULT 'ja',
- "desc" text NOT NULL DEFAULT '',
- alias varchar(500) NOT NULL DEFAULT '',
- l_wp varchar(150)
+-- producers_relations_hist
+CREATE TABLE producers_relations_hist (
+ chid integer NOT NULL,
+ pid integer NOT NULL, -- producers.id
+ relation producer_relation NOT NULL,
+ PRIMARY KEY(chid, pid)
);
-- quotes
@@ -144,71 +225,124 @@ CREATE TABLE quotes (
-- releases
CREATE TABLE releases (
- id SERIAL NOT NULL PRIMARY KEY,
- latest integer NOT NULL DEFAULT 0,
- locked boolean NOT NULL DEFAULT FALSE,
- hidden boolean NOT NULL DEFAULT FALSE
+ id SERIAL PRIMARY KEY,
+ locked boolean NOT NULL DEFAULT FALSE,
+ hidden boolean NOT NULL DEFAULT FALSE,
+ title varchar(250) NOT NULL DEFAULT '',
+ original varchar(250) NOT NULL DEFAULT '',
+ type release_type NOT NULL DEFAULT 'complete',
+ website varchar(250) NOT NULL DEFAULT '',
+ catalog varchar(50) NOT NULL DEFAULT '',
+ gtin bigint NOT NULL DEFAULT 0,
+ released integer NOT NULL DEFAULT 0,
+ notes text NOT NULL DEFAULT '',
+ minage smallint,
+ patch boolean NOT NULL DEFAULT FALSE,
+ freeware boolean NOT NULL DEFAULT FALSE,
+ doujin boolean NOT NULL DEFAULT FALSE,
+ resolution smallint NOT NULL DEFAULT 0,
+ voiced smallint NOT NULL DEFAULT 0,
+ ani_story smallint NOT NULL DEFAULT 0,
+ ani_ero smallint NOT NULL DEFAULT 0
+);
+
+-- releases_hist
+CREATE TABLE releases_hist (
+ chid integer NOT NULL PRIMARY KEY,
+ title varchar(250) NOT NULL DEFAULT '',
+ original varchar(250) NOT NULL DEFAULT '',
+ type release_type NOT NULL DEFAULT 'complete',
+ website varchar(250) NOT NULL DEFAULT '',
+ catalog varchar(50) NOT NULL DEFAULT '',
+ gtin bigint NOT NULL DEFAULT 0,
+ released integer NOT NULL DEFAULT 0,
+ notes text NOT NULL DEFAULT '',
+ minage smallint,
+ patch boolean NOT NULL DEFAULT FALSE,
+ freeware boolean NOT NULL DEFAULT FALSE,
+ doujin boolean NOT NULL DEFAULT FALSE,
+ resolution smallint NOT NULL DEFAULT 0,
+ voiced smallint NOT NULL DEFAULT 0,
+ ani_story smallint NOT NULL DEFAULT 0,
+ ani_ero smallint NOT NULL DEFAULT 0
);
-- releases_lang
CREATE TABLE releases_lang (
- rid integer NOT NULL,
- lang language NOT NULL,
- PRIMARY KEY(rid, lang)
+ id integer NOT NULL,
+ lang language NOT NULL,
+ PRIMARY KEY(id, lang)
+);
+
+-- releases_lang_hist
+CREATE TABLE releases_lang_hist (
+ chid integer NOT NULL,
+ lang language NOT NULL,
+ PRIMARY KEY(chid, lang)
);
-- releases_media
CREATE TABLE releases_media (
- rid integer NOT NULL DEFAULT 0,
- medium medium NOT NULL,
- qty smallint NOT NULL DEFAULT 1,
- PRIMARY KEY(rid, medium, qty)
+ id integer NOT NULL,
+ medium medium NOT NULL,
+ qty smallint NOT NULL DEFAULT 1,
+ PRIMARY KEY(id, medium, qty)
+);
+
+-- releases_media_hist
+CREATE TABLE releases_media_hist (
+ chid integer NOT NULL,
+ medium medium NOT NULL,
+ qty smallint NOT NULL DEFAULT 1,
+ PRIMARY KEY(chid, medium, qty)
);
-- releases_platforms
CREATE TABLE releases_platforms (
- rid integer NOT NULL DEFAULT 0,
- platform platform NOT NULL,
- PRIMARY KEY(rid, platform)
+ id integer NOT NULL,
+ platform platform NOT NULL,
+ PRIMARY KEY(id, platform)
+);
+
+-- releases_platforms_hist
+CREATE TABLE releases_platforms_hist (
+ chid integer NOT NULL,
+ platform platform NOT NULL,
+ PRIMARY KEY(chid, platform)
);
-- releases_producers
CREATE TABLE releases_producers (
- pid integer NOT NULL,
- rid integer NOT NULL,
- developer boolean NOT NULL DEFAULT FALSE,
- publisher boolean NOT NULL DEFAULT TRUE,
+ id integer NOT NULL,
+ pid integer NOT NULL, -- producers.id
+ developer boolean NOT NULL DEFAULT FALSE,
+ publisher boolean NOT NULL DEFAULT TRUE,
CHECK(developer OR publisher),
- PRIMARY KEY(pid, rid)
+ PRIMARY KEY(id, pid)
);
--- releases_rev
-CREATE TABLE releases_rev (
- id integer NOT NULL PRIMARY KEY,
- rid integer NOT NULL DEFAULT 0,
- title varchar(250) NOT NULL DEFAULT '',
- original varchar(250) NOT NULL DEFAULT '',
- type release_type NOT NULL DEFAULT 'complete',
- website varchar(250) NOT NULL DEFAULT '',
- released integer NOT NULL DEFAULT 0,
- notes text NOT NULL DEFAULT '',
- minage smallint,
- gtin bigint NOT NULL DEFAULT 0,
- patch boolean NOT NULL DEFAULT FALSE,
- catalog varchar(50) NOT NULL DEFAULT '',
- resolution smallint NOT NULL DEFAULT 0,
- voiced smallint NOT NULL DEFAULT 0,
- freeware boolean NOT NULL DEFAULT FALSE,
- doujin boolean NOT NULL DEFAULT FALSE,
- ani_story smallint NOT NULL DEFAULT 0,
- ani_ero smallint NOT NULL DEFAULT 0
+-- releases_producers_hist
+CREATE TABLE releases_producers_hist (
+ chid integer NOT NULL,
+ pid integer NOT NULL, -- producers.id
+ developer boolean NOT NULL DEFAULT FALSE,
+ publisher boolean NOT NULL DEFAULT TRUE,
+ CHECK(developer OR publisher),
+ PRIMARY KEY(chid, pid)
);
-- releases_vn
CREATE TABLE releases_vn (
- rid integer NOT NULL DEFAULT 0,
- vid integer NOT NULL DEFAULT 0,
- PRIMARY KEY(rid, vid)
+ id integer NOT NULL,
+ vid integer NOT NULL, -- vn.id
+ PRIMARY KEY(id, vid)
+);
+
+-- releases_vn_hist
+CREATE TABLE releases_vn_hist (
+ chid integer NOT NULL,
+ vid integer NOT NULL, -- vn.id
+ PRIMARY KEY(chid, vid)
);
-- relgraphs
@@ -244,33 +378,47 @@ CREATE TABLE sessions (
-- 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
+ id SERIAL PRIMARY KEY,
+ locked boolean NOT NULL DEFAULT FALSE,
+ hidden boolean NOT NULL DEFAULT FALSE,
+ aid integer NOT NULL, -- staff_alias.aid
+ 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
+);
+
+-- staff_hist
+CREATE TABLE staff_hist (
+ chid integer NOT NULL PRIMARY KEY,
+ aid integer NOT NULL, -- Can't refer to staff_alias.id, because the alias might have been deleted
+ 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
);
-- 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)
+ id integer NOT NULL,
+ aid SERIAL PRIMARY KEY, -- Globally unique ID of this alias
+ name varchar(200) NOT NULL DEFAULT '',
+ original varchar(200) NOT NULL DEFAULT ''
);
--- 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
+-- staff_alias_hist
+CREATE TABLE staff_alias_hist (
+ chid integer NOT NULL,
+ aid integer NOT NULL, -- staff_alias.aid, but can't reference it because the alias may have been deleted
+ name varchar(200) NOT NULL DEFAULT '',
+ original varchar(200) NOT NULL DEFAULT '',
+ PRIMARY KEY(chid, aid)
);
-- stats_cache
@@ -427,78 +575,129 @@ CREATE TABLE users_prefs (
-- vn
CREATE TABLE vn (
- id SERIAL NOT NULL PRIMARY KEY,
- latest integer NOT NULL DEFAULT 0,
- locked boolean NOT NULL DEFAULT FALSE,
- hidden boolean NOT NULL DEFAULT FALSE,
- rgraph integer,
+ id SERIAL PRIMARY KEY,
+ locked boolean NOT NULL DEFAULT FALSE,
+ hidden boolean NOT NULL DEFAULT FALSE,
+ title varchar(250) NOT NULL DEFAULT '',
+ original varchar(250) NOT NULL DEFAULT '',
+ alias varchar(500) NOT NULL DEFAULT '',
+ length smallint NOT NULL DEFAULT 0,
+ img_nsfw boolean NOT NULL DEFAULT FALSE,
+ image integer NOT NULL DEFAULT 0,
+ "desc" text NOT NULL DEFAULT '',
+ l_wp varchar(150) NOT NULL DEFAULT '',
+ l_encubed varchar(100) NOT NULL DEFAULT '',
+ l_renai varchar(100) NOT NULL DEFAULT '',
+ rgraph integer, -- relgraphs.id
c_released integer NOT NULL DEFAULT 0,
c_languages language[] NOT NULL DEFAULT '{}',
+ c_olang language[] NOT NULL DEFAULT '{}',
c_platforms platform[] NOT NULL DEFAULT '{}',
c_popularity real,
- c_rating real,
+ c_rating real,
c_votecount integer NOT NULL DEFAULT 0,
- c_search text,
- c_olang language[] NOT NULL DEFAULT '{}'
+ c_search text
+);
+
+-- vn_hist
+CREATE TABLE vn_hist (
+ chid integer NOT NULL PRIMARY KEY,
+ title varchar(250) NOT NULL DEFAULT '',
+ original varchar(250) NOT NULL DEFAULT '',
+ alias varchar(500) NOT NULL DEFAULT '',
+ length smallint NOT NULL DEFAULT 0,
+ img_nsfw boolean NOT NULL DEFAULT FALSE,
+ image integer NOT NULL DEFAULT 0,
+ "desc" text NOT NULL DEFAULT '',
+ l_wp varchar(150) NOT NULL DEFAULT '',
+ l_encubed varchar(100) NOT NULL DEFAULT '',
+ l_renai varchar(100) NOT NULL DEFAULT ''
);
-- vn_anime
CREATE TABLE vn_anime (
- vid integer NOT NULL,
- aid integer NOT NULL,
- PRIMARY KEY(vid, aid)
+ id integer NOT NULL,
+ aid integer NOT NULL, -- anime.id
+ PRIMARY KEY(id, aid)
+);
+
+-- vn_anime_hist
+CREATE TABLE vn_anime_hist (
+ chid integer NOT NULL,
+ aid integer NOT NULL, -- anime.id
+ PRIMARY KEY(chid, aid)
);
-- vn_relations
CREATE TABLE vn_relations (
- vid1 integer NOT NULL DEFAULT 0,
- vid2 integer NOT NULL DEFAULT 0,
- relation vn_relation NOT NULL,
- official boolean NOT NULL DEFAULT TRUE,
- PRIMARY KEY(vid1, vid2)
+ id integer NOT NULL,
+ vid integer NOT NULL, -- vn.id
+ relation vn_relation NOT NULL,
+ official boolean NOT NULL DEFAULT TRUE,
+ PRIMARY KEY(id, vid)
);
--- vn_rev
-CREATE TABLE vn_rev (
- id integer NOT NULL PRIMARY KEY,
- vid integer NOT NULL DEFAULT 0,
- title varchar(250) NOT NULL DEFAULT '',
- alias varchar(500) NOT NULL DEFAULT '',
- img_nsfw boolean NOT NULL DEFAULT FALSE,
- length smallint NOT NULL DEFAULT 0,
- "desc" text NOT NULL DEFAULT '',
- l_wp varchar(150) NOT NULL DEFAULT '',
- image integer NOT NULL DEFAULT 0,
- l_encubed varchar(100) NOT NULL DEFAULT '',
- l_renai varchar(100) NOT NULL DEFAULT '',
- original varchar(250) NOT NULL DEFAULT ''
+-- vn_relations_hist
+CREATE TABLE vn_relations_hist (
+ chid integer NOT NULL,
+ vid integer NOT NULL, -- vn.id
+ relation vn_relation NOT NULL,
+ official boolean NOT NULL DEFAULT TRUE,
+ PRIMARY KEY(chid, vid)
);
-- vn_screenshots
CREATE TABLE vn_screenshots (
- vid integer NOT NULL DEFAULT 0,
- scr integer NOT NULL DEFAULT 0,
- nsfw boolean NOT NULL DEFAULT FALSE,
- rid integer,
- PRIMARY KEY(vid, scr)
+ id integer NOT NULL,
+ scr integer NOT NULL, -- screenshots.id
+ rid integer, -- releases.id (only NULL for old revisions, nowadays not allowed anymore)
+ nsfw boolean NOT NULL DEFAULT FALSE,
+ PRIMARY KEY(id, scr)
+);
+
+-- vn_screenshots_hist
+CREATE TABLE vn_screenshots_hist (
+ chid integer NOT NULL,
+ scr integer NOT NULL,
+ rid integer,
+ nsfw boolean NOT NULL DEFAULT FALSE,
+ PRIMARY KEY(chid, 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)
+ id integer NOT NULL,
+ aid integer NOT NULL, -- staff_alias.aid
+ cid integer NOT NULL, -- chars.id
+ note varchar(250) NOT NULL DEFAULT '',
+ PRIMARY KEY (id, aid, cid)
+);
+
+-- vn_seiyuu_hist
+CREATE TABLE vn_seiyuu_hist (
+ chid integer NOT NULL,
+ aid integer NOT NULL, -- staff_alias.aid, but can't reference it because the alias may have been deleted
+ cid integer NOT NULL, -- chars.id
+ note varchar(250) NOT NULL DEFAULT '',
+ PRIMARY KEY (chid, 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)
+ id integer NOT NULL,
+ aid integer NOT NULL, -- staff_alias.aid
+ role credit_type NOT NULL DEFAULT 'staff',
+ note varchar(250) NOT NULL DEFAULT '',
+ PRIMARY KEY (id, aid, role)
+);
+
+-- vn_staff_hist
+CREATE TABLE vn_staff_hist (
+ chid integer NOT NULL,
+ aid integer NOT NULL, -- See note at vn_seiyuu_hist.aid
+ role credit_type NOT NULL DEFAULT 'staff',
+ note varchar(250) NOT NULL DEFAULT '',
+ PRIMARY KEY (chid, aid, role)
);
-- vnlists
diff --git a/util/sql/tableattrs.sql b/util/sql/tableattrs.sql
index 4028e98e..05770f83 100644
--- a/util/sql/tableattrs.sql
+++ b/util/sql/tableattrs.sql
@@ -1,88 +1,107 @@
-ALTER TABLE affiliate_links ADD CONSTRAINT affiliate_links_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id);
-ALTER TABLE changes ADD CONSTRAINT changes_requester_fkey FOREIGN KEY (requester) REFERENCES users (id) ON DELETE SET DEFAULT;
-ALTER TABLE chars ADD CONSTRAINT chars_latest_fkey FOREIGN KEY (latest) REFERENCES chars_rev (id) DEFERRABLE INITIALLY DEFERRED;
-ALTER TABLE chars_rev ADD CONSTRAINT chars_rev_id_fkey FOREIGN KEY (id) REFERENCES changes (id);
-ALTER TABLE chars_rev ADD CONSTRAINT chars_rev_cid_fkey FOREIGN KEY (cid) REFERENCES chars (id);
-ALTER TABLE chars_rev ADD CONSTRAINT chars_rev_main_fkey FOREIGN KEY (main) REFERENCES chars (id);
-ALTER TABLE chars_traits ADD CONSTRAINT chars_traits_cid_fkey FOREIGN KEY (cid) REFERENCES chars_rev (id);
-ALTER TABLE chars_traits ADD CONSTRAINT chars_traits_tid_fkey FOREIGN KEY (tid) REFERENCES traits (id);
-ALTER TABLE chars_vns ADD CONSTRAINT chars_vns_cid_fkey FOREIGN KEY (cid) REFERENCES chars_rev (id);
-ALTER TABLE chars_vns ADD CONSTRAINT chars_vns_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
-ALTER TABLE chars_vns ADD CONSTRAINT chars_vns_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id);
-ALTER TABLE notifications ADD CONSTRAINT notifications_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
-ALTER TABLE notifications ADD CONSTRAINT notifications_c_byuser_fkey FOREIGN KEY (c_byuser) REFERENCES users (id) ON DELETE SET DEFAULT;
-ALTER TABLE producers ADD CONSTRAINT producers_latest_fkey FOREIGN KEY (latest) REFERENCES producers_rev (id) DEFERRABLE INITIALLY DEFERRED;
-ALTER TABLE producers ADD CONSTRAINT producers_rgraph_fkey FOREIGN KEY (rgraph) REFERENCES relgraphs (id);
-ALTER TABLE producers_relations ADD CONSTRAINT producers_relations_pid1_fkey FOREIGN KEY (pid1) REFERENCES producers_rev (id);
-ALTER TABLE producers_relations ADD CONSTRAINT producers_relations_pid2_fkey FOREIGN KEY (pid2) REFERENCES producers (id);
-ALTER TABLE producers_rev ADD CONSTRAINT producers_rev_id_fkey FOREIGN KEY (id) REFERENCES changes (id);
-ALTER TABLE producers_rev ADD CONSTRAINT producers_rev_pid_fkey FOREIGN KEY (pid) REFERENCES producers (id);
-ALTER TABLE quotes ADD CONSTRAINT quotes_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
-ALTER TABLE releases ADD CONSTRAINT releases_latest_fkey FOREIGN KEY (latest) REFERENCES releases_rev (id) DEFERRABLE INITIALLY DEFERRED;
-ALTER TABLE releases_lang ADD CONSTRAINT releases_lang_rid_fkey FOREIGN KEY (rid) REFERENCES releases_rev (id);
-ALTER TABLE releases_media ADD CONSTRAINT releases_media_rid_fkey FOREIGN KEY (rid) REFERENCES releases_rev (id);
-ALTER TABLE releases_platforms ADD CONSTRAINT releases_platforms_rid_fkey FOREIGN KEY (rid) REFERENCES releases_rev (id);
-ALTER TABLE releases_producers ADD CONSTRAINT releases_producers_rid_fkey FOREIGN KEY (rid) REFERENCES releases_rev (id);
-ALTER TABLE releases_producers ADD CONSTRAINT releases_producers_pid_fkey FOREIGN KEY (pid) REFERENCES producers (id);
-ALTER TABLE releases_rev ADD CONSTRAINT releases_rev_id_fkey FOREIGN KEY (id) REFERENCES changes (id);
-ALTER TABLE releases_rev ADD CONSTRAINT releases_rev_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id);
-ALTER TABLE releases_vn ADD CONSTRAINT releases_vn_rid_fkey FOREIGN KEY (rid) REFERENCES releases_rev (id);
-ALTER TABLE releases_vn ADD CONSTRAINT releases_vn_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
-ALTER TABLE rlists ADD CONSTRAINT rlists_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
-ALTER TABLE rlists ADD CONSTRAINT rlists_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id);
-ALTER TABLE sessions ADD CONSTRAINT sessions_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
-ALTER TABLE staff ADD CONSTRAINT staff_latest_fkey FOREIGN KEY (latest) REFERENCES staff_rev (id) DEFERRABLE INITIALLY DEFERRED;
-ALTER TABLE staff_alias ADD CONSTRAINT staff_alias_rid_fkey FOREIGN KEY (rid) REFERENCES staff_rev (id) DEFERRABLE INITIALLY DEFERRED;
-ALTER TABLE staff_rev ADD CONSTRAINT staff_rev_id_fkey FOREIGN KEY (id) REFERENCES changes (id);
-ALTER TABLE staff_rev ADD CONSTRAINT staff_rev_sid_fkey FOREIGN KEY (sid) REFERENCES staff (id);
-ALTER TABLE staff_rev ADD CONSTRAINT staff_rev_aid_fkey FOREIGN KEY (aid,id) REFERENCES staff_alias (id,rid);
-ALTER TABLE tags ADD CONSTRAINT tags_addedby_fkey FOREIGN KEY (addedby) REFERENCES users (id) ON DELETE SET DEFAULT;
-ALTER TABLE tags_aliases ADD CONSTRAINT tags_aliases_tag_fkey FOREIGN KEY (tag) REFERENCES tags (id);
-ALTER TABLE tags_parents ADD CONSTRAINT tags_parents_tag_fkey FOREIGN KEY (tag) REFERENCES tags (id);
-ALTER TABLE tags_parents ADD CONSTRAINT tags_parents_parent_fkey FOREIGN KEY (parent) REFERENCES tags (id);
-ALTER TABLE tags_vn ADD CONSTRAINT tags_vn_tag_fkey FOREIGN KEY (tag) REFERENCES tags (id);
-ALTER TABLE tags_vn ADD CONSTRAINT tags_vn_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
-ALTER TABLE tags_vn ADD CONSTRAINT tags_vn_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
-ALTER TABLE threads ADD CONSTRAINT threads_id_fkey FOREIGN KEY (id, count) REFERENCES threads_posts (tid, num) DEFERRABLE INITIALLY DEFERRED;
-ALTER TABLE threads_posts ADD CONSTRAINT threads_posts_tid_fkey FOREIGN KEY (tid) REFERENCES threads (id);
-ALTER TABLE threads_posts ADD CONSTRAINT threads_posts_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE SET DEFAULT;
-ALTER TABLE threads_boards ADD CONSTRAINT threads_boards_tid_fkey FOREIGN KEY (tid) REFERENCES threads (id);
-ALTER TABLE traits ADD CONSTRAINT traits_addedby_fkey FOREIGN KEY (addedby) REFERENCES users (id);
-ALTER TABLE traits ADD CONSTRAINT traits_group_fkey FOREIGN KEY ("group") REFERENCES traits (id);
-ALTER TABLE traits_parents ADD CONSTRAINT traits_parents_trait_fkey FOREIGN KEY (trait) REFERENCES traits (id);
-ALTER TABLE traits_parents ADD CONSTRAINT traits_parents_parent_fkey FOREIGN KEY (parent) REFERENCES traits (id);
-ALTER TABLE traits_chars ADD CONSTRAINT traits_chars_cid_fkey FOREIGN KEY (cid) REFERENCES chars (id);
-ALTER TABLE traits_chars ADD CONSTRAINT traits_chars_tid_fkey FOREIGN KEY (tid) REFERENCES traits (id);
-ALTER TABLE users_prefs ADD CONSTRAINT users_prefs_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
-ALTER TABLE vn ADD CONSTRAINT vn_latest_fkey FOREIGN KEY (latest) REFERENCES vn_rev (id) DEFERRABLE INITIALLY DEFERRED;
-ALTER TABLE vn ADD CONSTRAINT vn_rgraph_fkey FOREIGN KEY (rgraph) REFERENCES relgraphs (id);
-ALTER TABLE vn_anime ADD CONSTRAINT vn_anime_aid_fkey FOREIGN KEY (aid) REFERENCES anime (id);
-ALTER TABLE vn_anime ADD CONSTRAINT vn_anime_vid_fkey FOREIGN KEY (vid) REFERENCES vn_rev (id);
-ALTER TABLE vn_relations ADD CONSTRAINT vn_relations_vid1_fkey FOREIGN KEY (vid1) REFERENCES vn_rev (id);
-ALTER TABLE vn_relations ADD CONSTRAINT vn_relations_vid2_fkey FOREIGN KEY (vid2) REFERENCES vn (id);
-ALTER TABLE vn_rev ADD CONSTRAINT vn_rev_id_fkey FOREIGN KEY (id) REFERENCES changes (id);
-ALTER TABLE vn_rev ADD CONSTRAINT vn_rev_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
-ALTER TABLE vn_screenshots ADD CONSTRAINT vn_screenshots_vid_fkey FOREIGN KEY (vid) REFERENCES vn_rev (id);
-ALTER TABLE vn_screenshots ADD CONSTRAINT vn_screenshots_scr_fkey FOREIGN KEY (scr) REFERENCES screenshots (id);
-ALTER TABLE vn_screenshots ADD CONSTRAINT vn_screenshots_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id);
-ALTER TABLE vn_seiyuu ADD CONSTRAINT vn_seiyuu_cid_fkey FOREIGN KEY (cid) REFERENCES chars (id);
-ALTER TABLE vn_seiyuu ADD CONSTRAINT vn_seiyuu_vid_fkey FOREIGN KEY (vid) REFERENCES vn_rev (id);
-ALTER TABLE vn_staff ADD CONSTRAINT vn_staff_vid_fkey FOREIGN KEY (vid) REFERENCES vn_rev (id);
-ALTER TABLE vnlists ADD CONSTRAINT vnlists_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
-ALTER TABLE vnlists ADD CONSTRAINT vnlists_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
-ALTER TABLE votes ADD CONSTRAINT votes_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
-ALTER TABLE votes ADD CONSTRAINT votes_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
-ALTER TABLE wlists ADD CONSTRAINT wlists_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
-ALTER TABLE wlists ADD CONSTRAINT wlists_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
+ALTER TABLE affiliate_links ADD CONSTRAINT affiliate_links_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id);
+ALTER TABLE changes ADD CONSTRAINT changes_requester_fkey FOREIGN KEY (requester) REFERENCES users (id) ON DELETE SET DEFAULT;
+ALTER TABLE chars ADD CONSTRAINT chars_main_fkey FOREIGN KEY (main) REFERENCES chars (id);
+ALTER TABLE chars_hist ADD CONSTRAINT chars_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id);
+ALTER TABLE chars_hist ADD CONSTRAINT chars_hist_main_fkey FOREIGN KEY (main) REFERENCES chars (id);
+ALTER TABLE chars_traits ADD CONSTRAINT chars_traits_id_fkey FOREIGN KEY (id) REFERENCES chars (id);
+ALTER TABLE chars_traits ADD CONSTRAINT chars_traits_tid_fkey FOREIGN KEY (tid) REFERENCES traits (id);
+ALTER TABLE chars_traits_hist ADD CONSTRAINT chars_traits_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id);
+ALTER TABLE chars_traits_hist ADD CONSTRAINT chars_traits_hist_tid_fkey FOREIGN KEY (tid) REFERENCES traits (id);
+ALTER TABLE chars_vns ADD CONSTRAINT chars_vns_id_fkey FOREIGN KEY (id) REFERENCES chars (id);
+ALTER TABLE chars_vns ADD CONSTRAINT chars_vns_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
+ALTER TABLE chars_vns ADD CONSTRAINT chars_vns_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id);
+ALTER TABLE chars_vns_hist ADD CONSTRAINT chars_vns_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id);
+ALTER TABLE chars_vns_hist ADD CONSTRAINT chars_vns_hist_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
+ALTER TABLE chars_vns_hist ADD CONSTRAINT chars_vns_hist_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id);
+ALTER TABLE notifications ADD CONSTRAINT notifications_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
+ALTER TABLE notifications ADD CONSTRAINT notifications_c_byuser_fkey FOREIGN KEY (c_byuser) REFERENCES users (id) ON DELETE SET DEFAULT;
+ALTER TABLE producers ADD CONSTRAINT producers_rgraph_fkey FOREIGN KEY (rgraph) REFERENCES relgraphs (id);
+ALTER TABLE producers_hist ADD CONSTRAINT producers_chid_id_fkey FOREIGN KEY (chid) REFERENCES changes (id);
+ALTER TABLE producers_relations ADD CONSTRAINT producers_relations_pid_fkey FOREIGN KEY (pid) REFERENCES producers (id);
+ALTER TABLE producers_relations_hist ADD CONSTRAINT producers_relations_hist_id_fkey FOREIGN KEY (chid) REFERENCES changes (id);
+ALTER TABLE producers_relations_hist ADD CONSTRAINT producers_relations_hist_pid_fkey FOREIGN KEY (pid) REFERENCES producers (id);
+ALTER TABLE quotes ADD CONSTRAINT quotes_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
+ALTER TABLE releases_hist ADD CONSTRAINT releases_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id);
+ALTER TABLE releases_lang ADD CONSTRAINT releases_lang_id_fkey FOREIGN KEY (id) REFERENCES releases (id);
+ALTER TABLE releases_lang_hist ADD CONSTRAINT releases_lang_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id);
+ALTER TABLE releases_media ADD CONSTRAINT releases_media_id_fkey FOREIGN KEY (id) REFERENCES releases (id);
+ALTER TABLE releases_media_hist ADD CONSTRAINT releases_media_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id);
+ALTER TABLE releases_platforms ADD CONSTRAINT releases_platforms_id_fkey FOREIGN KEY (id) REFERENCES releases (id);
+ALTER TABLE releases_platforms_hist ADD CONSTRAINT releases_platforms_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id);
+ALTER TABLE releases_producers ADD CONSTRAINT releases_producers_id_fkey FOREIGN KEY (id) REFERENCES releases (id);
+ALTER TABLE releases_producers ADD CONSTRAINT releases_producers_pid_fkey FOREIGN KEY (pid) REFERENCES producers (id);
+ALTER TABLE releases_producers_hist ADD CONSTRAINT releases_producers_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id);
+ALTER TABLE releases_producers_hist ADD CONSTRAINT releases_producers_hist_pid_fkey FOREIGN KEY (pid) REFERENCES producers (id);
+ALTER TABLE releases_vn ADD CONSTRAINT releases_vn_id_fkey FOREIGN KEY (id) REFERENCES releases (id);
+ALTER TABLE releases_vn ADD CONSTRAINT releases_vn_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
+ALTER TABLE releases_vn_hist ADD CONSTRAINT releases_vn_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id);
+ALTER TABLE releases_vn_hist ADD CONSTRAINT releases_vn_hist_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
+ALTER TABLE rlists ADD CONSTRAINT rlists_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
+ALTER TABLE rlists ADD CONSTRAINT rlists_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id);
+ALTER TABLE sessions ADD CONSTRAINT sessions_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
+ALTER TABLE staff ADD CONSTRAINT staff_aid_fkey FOREIGN KEY (aid) REFERENCES staff_alias (aid) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE staff_hist ADD CONSTRAINT staff_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id);
+ALTER TABLE staff_alias ADD CONSTRAINT staff_alias_id_fkey FOREIGN KEY (id) REFERENCES staff (id);
+ALTER TABLE staff_alias_hist ADD CONSTRAINT staff_alias_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id);
+ALTER TABLE tags ADD CONSTRAINT tags_addedby_fkey FOREIGN KEY (addedby) REFERENCES users (id) ON DELETE SET DEFAULT;
+ALTER TABLE tags_aliases ADD CONSTRAINT tags_aliases_tag_fkey FOREIGN KEY (tag) REFERENCES tags (id);
+ALTER TABLE tags_parents ADD CONSTRAINT tags_parents_tag_fkey FOREIGN KEY (tag) REFERENCES tags (id);
+ALTER TABLE tags_parents ADD CONSTRAINT tags_parents_parent_fkey FOREIGN KEY (parent) REFERENCES tags (id);
+ALTER TABLE tags_vn ADD CONSTRAINT tags_vn_tag_fkey FOREIGN KEY (tag) REFERENCES tags (id);
+ALTER TABLE tags_vn ADD CONSTRAINT tags_vn_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
+ALTER TABLE tags_vn ADD CONSTRAINT tags_vn_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
+ALTER TABLE threads ADD CONSTRAINT threads_id_fkey FOREIGN KEY (id, count) REFERENCES threads_posts (tid, num) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE threads_posts ADD CONSTRAINT threads_posts_tid_fkey FOREIGN KEY (tid) REFERENCES threads (id);
+ALTER TABLE threads_posts ADD CONSTRAINT threads_posts_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE SET DEFAULT;
+ALTER TABLE threads_boards ADD CONSTRAINT threads_boards_tid_fkey FOREIGN KEY (tid) REFERENCES threads (id);
+ALTER TABLE traits ADD CONSTRAINT traits_addedby_fkey FOREIGN KEY (addedby) REFERENCES users (id);
+ALTER TABLE traits ADD CONSTRAINT traits_group_fkey FOREIGN KEY ("group") REFERENCES traits (id);
+ALTER TABLE traits_parents ADD CONSTRAINT traits_parents_trait_fkey FOREIGN KEY (trait) REFERENCES traits (id);
+ALTER TABLE traits_parents ADD CONSTRAINT traits_parents_parent_fkey FOREIGN KEY (parent) REFERENCES traits (id);
+ALTER TABLE traits_chars ADD CONSTRAINT traits_chars_cid_fkey FOREIGN KEY (cid) REFERENCES chars (id);
+ALTER TABLE traits_chars ADD CONSTRAINT traits_chars_tid_fkey FOREIGN KEY (tid) REFERENCES traits (id);
+ALTER TABLE users_prefs ADD CONSTRAINT users_prefs_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
+ALTER TABLE vn ADD CONSTRAINT vn_rgraph_fkey FOREIGN KEY (rgraph) REFERENCES relgraphs (id);
+ALTER TABLE vn_hist ADD CONSTRAINT vn_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id);
+ALTER TABLE vn_anime ADD CONSTRAINT vn_anime_id_fkey FOREIGN KEY (id) REFERENCES vn (id);
+ALTER TABLE vn_anime ADD CONSTRAINT vn_anime_aid_fkey FOREIGN KEY (aid) REFERENCES anime (id);
+ALTER TABLE vn_anime_hist ADD CONSTRAINT vn_anime_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id);
+ALTER TABLE vn_anime_hist ADD CONSTRAINT vn_anime_hist_aid_fkey FOREIGN KEY (aid) REFERENCES anime (id);
+ALTER TABLE vn_relations ADD CONSTRAINT vn_relations_id_fkey FOREIGN KEY (id) REFERENCES vn (id);
+ALTER TABLE vn_relations ADD CONSTRAINT vn_relations_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
+ALTER TABLE vn_relations_hist ADD CONSTRAINT vn_relations_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id);
+ALTER TABLE vn_relations_hist ADD CONSTRAINT vn_relations_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
+ALTER TABLE vn_screenshots ADD CONSTRAINT vn_screenshots_id_fkey FOREIGN KEY (id) REFERENCES vn (id);
+ALTER TABLE vn_screenshots ADD CONSTRAINT vn_screenshots_scr_fkey FOREIGN KEY (scr) REFERENCES screenshots (id);
+ALTER TABLE vn_screenshots ADD CONSTRAINT vn_screenshots_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id);
+ALTER TABLE vn_screenshots_hist ADD CONSTRAINT vn_screenshots_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id);
+ALTER TABLE vn_screenshots_hist ADD CONSTRAINT vn_screenshots_hist_scr_fkey FOREIGN KEY (scr) REFERENCES screenshots (id);
+ALTER TABLE vn_screenshots_hist ADD CONSTRAINT vn_screenshots_hist_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id);
+ALTER TABLE vn_seiyuu ADD CONSTRAINT vn_seiyuu_id_fkey FOREIGN KEY (id) REFERENCES vn (id);
+ALTER TABLE vn_seiyuu ADD CONSTRAINT vn_seiyuu_aid_fkey FOREIGN KEY (aid) REFERENCES staff_alias (aid);
+ALTER TABLE vn_seiyuu ADD CONSTRAINT vn_seiyuu_cid_fkey FOREIGN KEY (cid) REFERENCES chars (id);
+ALTER TABLE vn_seiyuu_hist ADD CONSTRAINT vn_seiyuu_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id);
+ALTER TABLE vn_seiyuu_hist ADD CONSTRAINT vn_seiyuu_hist_cid_fkey FOREIGN KEY (cid) REFERENCES chars (id);
+ALTER TABLE vn_staff ADD CONSTRAINT vn_staff_id_fkey FOREIGN KEY (id) REFERENCES vn (id);
+ALTER TABLE vn_staff ADD CONSTRAINT vn_staff_aid_fkey FOREIGN KEY (aid) REFERENCES staff_alias (aid);
+ALTER TABLE vn_staff_hist ADD CONSTRAINT vn_staff_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id);
+ALTER TABLE vnlists ADD CONSTRAINT vnlists_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
+ALTER TABLE vnlists ADD CONSTRAINT vnlists_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
+ALTER TABLE votes ADD CONSTRAINT votes_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
+ALTER TABLE votes ADD CONSTRAINT votes_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
+ALTER TABLE wlists ADD CONSTRAINT wlists_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
+ALTER TABLE wlists ADD CONSTRAINT wlists_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
CREATE INDEX affiliate_links_rid ON affiliate_links (rid) WHERE NOT hidden;
CREATE INDEX notifications_uid ON notifications (uid);
-CREATE INDEX releases_producers_rid ON releases_producers (rid);
+CREATE INDEX releases_producers_pid ON releases_producers (pid);
CREATE INDEX releases_vn_vid ON releases_vn (vid);
+CREATE INDEX staff_alias_id ON staff_alias (id);
CREATE INDEX tags_vn_date ON tags_vn (date);
CREATE INDEX tags_vn_vid ON tags_vn (vid);
CREATE INDEX threads_posts_ts ON threads_posts USING gin(to_tsvector('english', strip_bb_tags(msg)));
-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));
+CREATE UNIQUE INDEX changes_itemrev ON changes (type, itemid, rev);
+CREATE UNIQUE INDEX chars_vns_pkey ON chars_vns (id, vid, COALESCE(rid, 0));
+CREATE UNIQUE INDEX chars_vns_hist_pkey ON chars_vns_hist (chid, vid, COALESCE(rid, 0));