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.

3 files changed, 669 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));
diff --git a/util/updates/update_2.25-sqlsplit.sql b/util/updates/update_2.25-sqlsplit.sql
new file mode 100644
index 00000000..eb1ce231
--- /dev/null
+++ b/util/updates/update_2.25-sqlsplit.sql
@@ -0,0 +1,216 @@
+-- Q: Why recreate all the tables rather than modify existing ones?
+-- A: Because the production tables have been modified many times, and columns
+--    weren't always in the same order as in scheme.sql. Recreating everything
+--    also has the advantage of ensuring that all references and indices are
+--    handled and documented here. In hindsight, it also seems like the easier
+--    approach.
+
+ALTER TABLE changes             RENAME TO changes_old;
+ALTER TABLE chars               RENAME TO chars_old;
+ALTER TABLE chars_rev           RENAME TO chars_rev_old;
+ALTER TABLE chars_traits        RENAME TO chars_traits_old;
+ALTER TABLE chars_vns           RENAME TO chars_vns_old;
+ALTER TABLE producers           RENAME TO producers_old;
+ALTER TABLE producers_rev       RENAME TO producers_rev_old;
+ALTER TABLE producers_relations RENAME TO producers_relations_old;
+ALTER TABLE releases            RENAME TO releases_old;
+ALTER TABLE releases_rev        RENAME TO releases_rev_old;
+ALTER TABLE releases_lang       RENAME TO releases_lang_old;
+ALTER TABLE releases_media      RENAME TO releases_media_old;
+ALTER TABLE releases_platforms  RENAME TO releases_platforms_old;
+ALTER TABLE releases_producers  RENAME TO releases_producers_old;
+ALTER TABLE releases_vn         RENAME TO releases_vn_old;
+ALTER TABLE staff               RENAME TO staff_old;
+ALTER TABLE staff_rev           RENAME TO staff_rev_old;
+ALTER TABLE staff_alias         RENAME TO staff_alias_old;
+ALTER TABLE vn                  RENAME TO vn_old;
+ALTER TABLE vn_rev              RENAME TO vn_rev_old;
+ALTER TABLE vn_anime            RENAME TO vn_anime_old;
+ALTER TABLE vn_relations        RENAME TO vn_relations_old;
+ALTER TABLE vn_screenshots      RENAME TO vn_screenshots_old;
+ALTER TABLE vn_seiyuu           RENAME TO vn_seiyuu_old;
+ALTER TABLE vn_staff            RENAME TO vn_staff_old;
+
+
+\i util/sql/schema.sql
+
+
+-- XXX: This query uses a window function to generate changes.rev instead of
+-- copying the value from the old table. This is done because, in the old
+-- database schema, there was no uniqueness constraint on (type, itemid, rev),
+-- and due to a race condition it was possible for duplicates to appear. This
+-- is a pretty rare occurence, and easy to correct by renumbering the changes.
+-- (Changes the URL of a few revision pages, but there's no way to avoid that)
+INSERT INTO changes SELECT c.id, c.type, COALESCE(vr.vid, pr.pid, rr.rid, cr.cid, sr.sid),
+    row_number() OVER (PARTITION BY c.type, COALESCE(vr.vid, pr.pid, rr.rid, cr.cid, sr.sid) ORDER BY c.id ASC),
+    c.added, c.requester, c.ip, c.comments, c.ihid, c.ilock
+  FROM changes_old c
+  LEFT JOIN vn_rev_old vr ON vr.id = c.id
+  LEFT JOIN producers_rev_old pr ON pr.id = c.id
+  LEFT JOIN releases_rev_old rr ON rr.id = c.id
+  LEFT JOIN chars_rev_old cr ON cr.id = c.id
+  LEFT JOIN staff_rev_old sr ON sr.id = c.id;
+
+INSERT INTO chars SELECT c.id, c.locked, c.hidden,
+    cr.name, cr.original, cr.alias, cr.image, cr.desc, cr.gender, cr.s_bust, cr.s_waist, cr.s_hip,
+    cr.b_month, cr.b_day, cr.height, cr.weight, cr.bloodt, cr.main, cr.main_spoil
+  FROM chars_old c JOIN chars_rev_old cr ON cr.id = c.latest;
+
+INSERT INTO chars_hist SELECT cr.id,
+    cr.name, cr.original, cr.alias, cr.image, cr.desc, cr.gender, cr.s_bust, cr.s_waist, cr.s_hip,
+    cr.b_month, cr.b_day, cr.height, cr.weight, cr.bloodt, cr.main, cr.main_spoil
+  FROM chars_rev_old cr;
+
+INSERT INTO chars_traits SELECT c.id, ct.tid, ct.spoil
+  FROM chars_old c
+  JOIN chars_traits_old ct ON ct.cid = c.latest;
+
+INSERT INTO chars_traits_hist SELECT cid, tid, spoil
+  FROM chars_traits_old;
+
+INSERT INTO chars_vns SELECT c.id, cv.vid, cv.rid, cv.spoil, cv.role
+  FROM chars_old c
+  JOIN chars_vns_old cv ON cv.cid = c.latest;
+
+INSERT INTO chars_vns_hist SELECT cid, vid, rid, spoil, role
+  FROM chars_vns_old;
+
+INSERT INTO producers SELECT p.id, p.locked, p.hidden,
+    pr.type, pr.name, pr.original, pr.website, pr.lang, pr.desc, pr.alias, pr.l_wp, p.rgraph
+  FROM producers_old p JOIN producers_rev_old pr ON pr.id = p.latest;
+
+INSERT INTO producers_hist SELECT id, type, name, original, website, lang, "desc", alias, l_wp
+  FROM producers_rev_old;
+
+INSERT INTO producers_relations SELECT p.id, pr.pid2, pr.relation
+  FROM producers_old p
+  JOIN producers_relations_old pr ON p.latest = pr.pid1;
+
+INSERT INTO producers_relations_hist SELECT pid1, pid2, relation
+  FROM producers_relations_old;
+
+INSERT INTO releases SELECT r.id, r.locked, r.hidden,
+    rr.title, rr.original, rr.type, rr.website, rr.catalog, rr.gtin, rr.released, rr.notes, rr.minage, rr.patch,
+    rr.freeware, rr.doujin, rr.resolution, rr.voiced, rr.ani_story, rr.ani_ero
+  FROM releases_old r JOIN releases_rev_old rr ON rr.id = r.latest;
+
+INSERT INTO releases_hist SELECT rr.id,
+    rr.title, rr.original, rr.type, rr.website, rr.catalog, rr.gtin, rr.released, rr.notes, rr.minage, rr.patch,
+    rr.freeware, rr.doujin, rr.resolution, rr.voiced, rr.ani_story, rr.ani_ero
+  FROM releases_rev_old rr;
+
+INSERT INTO releases_lang SELECT r.id, rl.lang
+  FROM releases_old r JOIN releases_lang_old rl ON rl.rid = r.latest;
+
+INSERT INTO releases_lang_hist SELECT rl.rid, rl.lang
+  FROM releases_lang_old rl;
+
+INSERT INTO releases_media SELECT r.id, rm.medium, rm.qty
+  FROM releases_old r JOIN releases_media_old rm ON rm.rid = r.latest;
+
+INSERT INTO releases_media_hist SELECT rm.rid, rm.medium, rm.qty
+  FROM releases_media_old rm;
+
+INSERT INTO releases_platforms SELECT r.id, rp.platform
+  FROM releases_old r JOIN releases_platforms_old rp ON rp.rid = r.latest;
+
+INSERT INTO releases_platforms_hist SELECT rp.rid, rp.platform
+  FROM releases_platforms_old rp;
+
+INSERT INTO releases_producers SELECT r.id, rp.pid, rp.developer, rp.publisher
+  FROM releases_old r JOIN releases_producers_old rp ON rp.rid = r.latest;
+
+INSERT INTO releases_producers_hist SELECT rp.rid, rp.pid, rp.developer, rp.publisher
+  FROM releases_producers_old rp;
+
+INSERT INTO releases_vn SELECT r.id, rv.vid
+  FROM releases_old r JOIN releases_vn_old rv ON rv.rid = r.latest;
+
+INSERT INTO releases_vn_hist SELECT rv.rid, rv.vid
+  FROM releases_vn_old rv;
+
+INSERT INTO staff SELECT s.id, s.locked, s.hidden,
+    sr.aid, sr.gender, sr.lang, sr.desc, sr.l_wp, sr.l_site, sr.l_twitter, sr.l_anidb
+  FROM staff_old s JOIN staff_rev_old sr ON sr.id = s.latest;
+
+INSERT INTO staff_hist SELECT sr.id,
+    sr.aid, sr.gender, sr.lang, sr.desc, sr.l_wp, sr.l_site, sr.l_twitter, sr.l_anidb
+  FROM staff_rev_old sr;
+
+INSERT INTO staff_alias SELECT s.id, sa.id, sa.name, sa.original
+  FROM staff_old s JOIN staff_alias_old sa ON sa.rid = s.latest;
+
+INSERT INTO staff_alias_hist SELECT rid, id, name, original
+  FROM staff_alias_old;
+
+INSERT INTO vn SELECT v.id, v.locked, v.hidden,
+    vr.title, vr.original, vr.alias, vr.length, vr.img_nsfw, vr.image, vr.desc, vr.l_wp, vr.l_encubed, vr.l_renai,
+    v.rgraph, v.c_released, v.c_languages, v.c_olang, v.c_platforms, v.c_popularity, v.c_rating, v.c_votecount, v.c_search
+  FROM vn_old v JOIN vn_rev_old vr ON vr.id = v.latest;
+
+INSERT INTO vn_hist SELECT vr.id,
+    vr.title, vr.original, vr.alias, vr.length, vr.img_nsfw, vr.image, vr.desc, vr.l_wp, vr.l_encubed, vr.l_renai
+  FROM vn_rev_old vr;
+
+INSERT INTO vn_anime SELECT v.id, va.aid
+  FROM vn_old v JOIN vn_anime_old va ON va.vid = v.latest;
+
+INSERT INTO vn_anime_hist SELECT vid, aid
+  FROM vn_anime_old;
+
+INSERT INTO vn_relations SELECT v.id, vr.vid2, vr.relation, vr.official
+  FROM vn_old v JOIN vn_relations_old vr ON vr.vid1 = v.latest;
+
+INSERT INTO vn_relations_hist SELECT vid1, vid2, relation, official
+  FROM vn_relations_old;
+
+INSERT INTO vn_screenshots SELECT v.id, vs.scr, vs.rid, vs.nsfw
+  FROM vn_old v JOIN vn_screenshots_old vs ON vs.vid = v.latest;
+
+INSERT INTO vn_screenshots_hist SELECT vid, scr, rid, nsfw
+  FROM vn_screenshots_old;
+
+INSERT INTO vn_seiyuu SELECT v.id, vs.aid, vs.cid, vs.note
+  FROM vn_old v JOIN vn_seiyuu_old vs ON vs.vid = v.latest;
+
+INSERT INTO vn_seiyuu_hist SELECT vid, aid, cid, note
+  FROM vn_seiyuu_old;
+
+INSERT INTO vn_staff SELECT v.id, vs.aid, vs.role, vs.note
+  FROM vn_old v JOIN vn_staff_old vs ON vs.vid = v.latest;
+
+INSERT INTO vn_staff_hist SELECT vid, aid, role, note
+  FROM vn_staff_old;
+
+
+
+-- Dropping all tables with CASCADE causes all foreign key references to and
+-- from the tables to be dropped as well. This is exactly what we want, so we
+-- can re-add the constraints on the newly created tables.
+DROP TABLE changes_old CASCADE;
+DROP TABLE chars_old CASCADE;
+DROP TABLE chars_rev_old CASCADE;
+DROP TABLE chars_traits_old CASCADE;
+DROP TABLE chars_vns_old CASCADE;
+DROP TABLE producers_old CASCADE;
+DROP TABLE producers_rev_old CASCADE;
+DROP TABLE producers_relations_old CASCADE;
+DROP TABLE releases_old CASCADE;
+DROP TABLE releases_rev_old CASCADE;
+DROP TABLE releases_lang_old CASCADE;
+DROP TABLE releases_media_old CASCADE;
+DROP TABLE releases_platforms_old CASCADE;
+DROP TABLE releases_producers_old CASCADE;
+DROP TABLE releases_vn_old CASCADE;
+DROP TABLE staff_old CASCADE;
+DROP TABLE staff_rev_old CASCADE;
+DROP TABLE staff_alias_old CASCADE;
+DROP TABLE vn_old CASCADE;
+DROP TABLE vn_rev_old CASCADE;
+DROP TABLE vn_anime_old CASCADE;
+DROP TABLE vn_relations_old CASCADE;
+DROP TABLE vn_screenshots_old CASCADE;
+DROP TABLE vn_seiyuu_old CASCADE;
+DROP TABLE vn_staff_old CASCADE;
+
+\i util/sql/tableattrs.sql