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.

1 files changed, 98 insertions, 79 deletions

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));