summaryrefslogtreecommitdiff
path: root/util/sql
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2015-10-12 19:25:37 +0200
committerYorhel <git@yorhel.nl>2015-10-17 17:20:41 +0200
commiteed1eced579e022135f98f3f733ecab88e3b6b6e (patch)
tree88e15c1475a9bf4f7b15c2c6ff1661fb52b77716 /util/sql
parent61251790bd1da10eebee3c56a70a9711e7680ce8 (diff)
SQL: Convert all item-tables to a different schema
This commit breaks pretty much everything. Lots of code will have to be fixed to work with this new schema. The basic idea is to separate live data from archived data, which allows for smaller and more effective indices on the live data, and the archived data doesn't need such indices and have to be accessed at all for most operations. Another goal is to eliminate table joins to fetch some necessary information, e.g. it's not necessary anymore to join the main item tables in order to fetch only the latest revision of some item data. This is very much work in progress. I might stumble upon some weird issue while fixing the code, and might have to redesign everything from scratch again. Let's just see how things go.
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));