Merge branch 'master' into poll

10 files changed, 568 insertions, 472 deletions

diff --git a/lib/VNDB/DB/Affiliates.pm b/lib/VNDB/DB/Affiliates.pm
index 51f8c2dc..94dfd198 100644
--- a/lib/VNDB/DB/Affiliates.pm
+++ b/lib/VNDB/DB/Affiliates.pm
@@ -23,12 +23,12 @@ sub dbAffiliateGet {
     defined($o{hidden})    ? ('!s af.hidden'  => $o{hidden} ? '' : 'NOT') : (),
   );
 
-  my $join = $o{what} ? 'JOIN releases r ON r.id = af.rid JOIN releases_rev rr ON rr.id = r.latest' : '';
-  my $select = $o{what} ? ', rr.title' : '';
+  my $join = $o{what} ? 'JOIN releases r ON r.id = af.rid' : '';
+  my $select = $o{what} ? ', r.title' : '';
 
   my $order = sprintf {
     id        => 'af.id %s',
-    rel       => 'rr.title %s',
+    rel       => 'r.title %s',
     prio      => 'af.priority %s',
     url       => 'af.url %s',
     lastfetch => 'af.lastfetch %s',
diff --git a/lib/VNDB/DB/Chars.pm b/lib/VNDB/DB/Chars.pm
index 3db5f869..db9ae93b 100644
--- a/lib/VNDB/DB/Chars.pm
+++ b/lib/VNDB/DB/Chars.pm
@@ -5,10 +5,10 @@ use strict;
 use warnings;
 use Exporter 'import';
 
-our @EXPORT = qw|dbCharGet dbCharRevisionInsert dbCharImageId|;
+our @EXPORT = qw|dbCharGet dbCharGetRev dbCharRevisionInsert dbCharImageId|;
 
 
-# options: id rev instance tagspoil trait_inc trait_exc char what results page gender bloodt
+# options: id instance tagspoil trait_inc trait_exc char what results page gender bloodt
 #   bust_min bust_max waist_min waist_max hip_min hip_max height_min height_max weight_min weight_max role
 # what: extended traits vns changes
 sub dbCharGet {
@@ -24,33 +24,31 @@ sub dbCharGet {
   $o{search} =~ s/%//g if $o{search};
 
   my %where = (
-    !$o{id} && !$o{rev} ? ( 'c.hidden = FALSE' => 1 ) : (),
-    $o{id} ? (
-      'c.id IN(!l)' => [ ref $o{id} ? $o{id} : [$o{id}] ] ) : (),
-    $o{rev} ? ( 'h.rev = ?' => $o{rev} ) : (),
+    !$o{id} ? ( 'c.hidden = FALSE' => 1 ) : (),
+    $o{id}  ? ( 'c.id IN(!l)' => [ ref $o{id} ? $o{id} : [$o{id}] ] ) : (),
     $o{notid}    ? ( 'c.id <> ?'   => $o{notid} ) : (),
-    $o{instance} ? ( 'cr.main = ?' => $o{instance} ) : (),
-    $o{vid}      ? ( 'cr.id IN(SELECT cid FROM chars_vns WHERE vid = ?)' => $o{vid} ) : (),
-    defined $o{gender} ? ( 'cr.gender IN(!l)' => [ ref $o{gender} ? $o{gender} : [$o{gender}] ]) : (),
-    defined $o{bloodt} ? ( 'cr.bloodt IN(!l)' => [ ref $o{bloodt} ? $o{bloodt} : [$o{bloodt}] ]) : (),
-    defined $o{bust_min} ? ( 'cr.s_bust >= ?' => $o{bust_min} ) : (),
-    defined $o{bust_max} ? ( 'cr.s_bust <= ? AND cr.s_bust > 0' => $o{bust_max} ) : (),
-    defined $o{waist_min} ? ( 'cr.s_waist >= ?' => $o{waist_min} ) : (),
-    defined $o{waist_max} ? ( 'cr.s_waist <= ? AND cr.s_waist > 0' => $o{waist_max} ) : (),
-    defined $o{hip_min} ? ( 'cr.s_hip >= ?' => $o{hip_min} ) : (),
-    defined $o{hip_max} ? ( 'cr.s_hip <= ? AND cr.s_hip > 0' => $o{hip_max} ) : (),
-    defined $o{height_min} ? ( 'cr.height >= ?' => $o{height_min} ) : (),
-    defined $o{height_max} ? ( 'cr.height <= ? AND cr.height > 0' => $o{height_max} ) : (),
-    defined $o{weight_min} ? ( 'cr.weight >= ?' => $o{weight_min} ) : (),
-    defined $o{weight_max} ? ( 'cr.weight <= ? AND cr.weight > 0' => $o{weight_max} ) : (),
+    $o{instance} ? ( 'c.main = ?' => $o{instance} ) : (),
+    $o{vid}      ? ( 'c.id IN(SELECT id FROM chars_vns WHERE vid = ?)' => $o{vid} ) : (),
+    defined $o{gender}     ? ( 'c.gender IN(!l)' => [ ref $o{gender} ? $o{gender} : [$o{gender}] ]) : (),
+    defined $o{bloodt}     ? ( 'c.bloodt IN(!l)' => [ ref $o{bloodt} ? $o{bloodt} : [$o{bloodt}] ]) : (),
+    defined $o{bust_min}   ? ( 'c.s_bust >= ?' => $o{bust_min} ) : (),
+    defined $o{bust_max}   ? ( 'c.s_bust <= ? AND c.s_bust > 0' => $o{bust_max} ) : (),
+    defined $o{waist_min}  ? ( 'c.s_waist >= ?' => $o{waist_min} ) : (),
+    defined $o{waist_max}  ? ( 'c.s_waist <= ? AND c.s_waist > 0' => $o{waist_max} ) : (),
+    defined $o{hip_min}    ? ( 'c.s_hip >= ?' => $o{hip_min} ) : (),
+    defined $o{hip_max}    ? ( 'c.s_hip <= ? AND c.s_hip > 0' => $o{hip_max} ) : (),
+    defined $o{height_min} ? ( 'c.height >= ?' => $o{height_min} ) : (),
+    defined $o{height_max} ? ( 'c.height <= ? AND c.height > 0' => $o{height_max} ) : (),
+    defined $o{weight_min} ? ( 'c.weight >= ?' => $o{weight_min} ) : (),
+    defined $o{weight_max} ? ( 'c.weight <= ? AND c.weight > 0' => $o{weight_max} ) : (),
     $o{search} ? (
-      '(cr.name ILIKE ? OR cr.original ILIKE ? OR cr.alias ILIKE ?)', [ map '%'.$o{search}.'%', 1..3 ] ) : (),
+      '(c.name ILIKE ? OR c.original ILIKE ? OR c.alias ILIKE ?)', [ map '%'.$o{search}.'%', 1..3 ] ) : (),
     $o{char} ? (
-      'LOWER(SUBSTR(cr.name, 1, 1)) = ?' => $o{char} ) : (),
+      'LOWER(SUBSTR(c.name, 1, 1)) = ?' => $o{char} ) : (),
     defined $o{char} && !$o{char} ? (
-      '(ASCII(cr.name) < 97 OR ASCII(cr.name) > 122) AND (ASCII(cr.name) < 65 OR ASCII(cr.name) > 90)' => 1 ) : (),
+      '(ASCII(c.name) < 97 OR ASCII(c.name) > 122) AND (ASCII(c.name) < 65 OR ASCII(c.name) > 90)' => 1 ) : (),
     $o{role} ? (
-      'EXISTS(SELECT 1 FROM chars_vns cvi WHERE cvi.cid = cr.id AND cvi.role IN(!l))',
+      'EXISTS(SELECT 1 FROM chars_vns cvi WHERE cvi.id = c.id AND cvi.role IN(!l))',
       [ ref $o{role} ? $o{role} : [$o{role}] ] ) : (),
     $o{trait_inc} ? (
       'c.id IN(SELECT cid FROM traits_chars WHERE tid IN(!l) AND spoil <= ? GROUP BY cid HAVING COUNT(tid) = ?)',
@@ -59,75 +57,103 @@ sub dbCharGet {
       'c.id NOT IN(SELECT cid FROM traits_chars WHERE tid IN(!l))' => [ ref $o{trait_exc} ? $o{trait_exc} : [$o{trait_exc}] ] ) : (),
   );
 
-  my @select = (qw|c.id cr.name cr.original cr.gender|, 'cr.id AS cid');
-  push @select, qw|c.hidden c.locked cr.alias cr.desc cr.image cr.b_month cr.b_day cr.s_bust cr.s_waist cr.s_hip cr.height cr.weight cr.bloodt cr.main cr.main_spoil| if $o{what} =~ /extended/;
-  push @select, qw|h.requester h.comments c.latest u.username h.rev h.ihid h.ilock|, "extract('epoch' from h.added) as added" if $o{what} =~ /changes/;
-
-  my @join;
-  push @join, $o{rev} ? 'JOIN chars c ON c.id = cr.cid' : 'JOIN chars c ON cr.id = c.latest';
-  push @join, 'JOIN changes h ON h.id = cr.id' if $o{what} =~ /changes/ || $o{rev};
-  push @join, 'JOIN users u ON u.id = h.requester' if $o{what} =~ /changes/;
+  my @select = (qw|c.id c.name c.original c.gender|);
+  push @select, qw|c.hidden c.locked c.alias c.desc c.image c.b_month c.b_day c.s_bust c.s_waist c.s_hip c.height c.weight c.bloodt c.main c.main_spoil| if $o{what} =~ /extended/;
 
   my($r, $np) = $self->dbPage(\%o, q|
     SELECT !s
-      FROM chars_rev cr
-      !s
+      FROM chars c
       !W
-      ORDER BY cr.name|,
-    join(', ', @select), join(' ', @join), \%where
+      ORDER BY c.name|,
+    join(', ', @select), \%where
   );
 
-  if(@$r && $o{what} =~ /vns|traits|seiyuu/) {
+  return _enrich($self, $r, $np, 0, $o{what}, $o{vid});
+}
+
+
+sub dbCharGetRev {
+  my $self = shift;
+  my %o = (what => '', @_);
+
+  $o{rev} ||= $self->dbRow('SELECT MAX(rev) AS rev FROM changes WHERE type = \'c\' AND itemid = ?', $o{id})->{rev};
+
+  my $select = 'c.itemid AS id, ch.name, ch.original, ch.gender';
+  $select .= ', extract(\'epoch\' from c.added) as added, c.requester, c.comments, u.username, c.rev, c.ihid, c.ilock';
+  $select .= ', c.id AS cid, NOT EXISTS(SELECT 1 FROM changes c2 WHERE c2.type = c.type AND c2.itemid = c.itemid AND c2.rev = c.rev+1) AS lastrev';
+  $select .= ', ch.alias, ch.desc, ch.image, ch.b_month, ch.b_day, ch.s_bust, ch.s_waist, ch.s_hip, ch.height, ch.weight, ch.bloodt, ch.main, ch.main_spoil, co.hidden, co.locked' if $o{what} =~ /extended/;
+
+  my $r = $self->dbAll(q|
+    SELECT !s
+      FROM changes c
+      JOIN chars co ON co.id = c.itemid
+      JOIN chars_hist ch ON ch.chid = c.id
+      JOIN users u ON u.id = c.requester
+      WHERE c.type = 'c' AND c.itemid = ? AND c.rev = ?|,
+    $select, $o{id}, $o{rev}
+  );
+
+  return _enrich($self, $r, 0, 1, $o{what});
+}
+
+
+sub _enrich {
+  my($self, $r, $np, $rev, $what, $vid) = @_;
+
+  if(@$r && $what =~ /vns|traits/) {
+    my($col, $hist, $colname) = $rev ? ('cid', '_hist', 'chid') : ('id', '', 'id');
     my %r = map {
       $_->{traits} = [];
       $_->{vns} = [];
-      $_->{seiyuu} = [];
-      ($_->{cid}, $_)
+      ($_->{$col}, $_)
     } @$r;
 
-    if($o{what} =~ /traits/) {
-      push @{$r{ delete $_->{cid} }{traits}}, $_ for (@{$self->dbAll(q|
-        SELECT ct.cid, ct.tid, ct.spoil, t.name, t.sexual, t."group", tg.name AS groupname
-          FROM chars_traits ct
+    if($what =~ /traits/) {
+      push @{$r{ delete $_->{xid} }{traits}}, $_ for (@{$self->dbAll(qq|
+        SELECT ct.$colname AS xid, ct.tid, ct.spoil, t.name, t.sexual, t."group", tg.name AS groupname
+          FROM chars_traits$hist ct
           JOIN traits t ON t.id = ct.tid
-          LEFT JOIN traits tg ON tg.id = t."group"
-         WHERE cid IN(!l)
+          JOIN traits tg ON tg.id = t."group"
+         WHERE ct.$colname IN(!l)
          ORDER BY tg."order", t.name|, [ keys %r ]
       )});
     }
 
-    if($o{what} =~ /vns(?:\((\d+)\))?/) {
-      push @{$r{ delete $_->{cid} }{vns}}, $_ for (@{$self->dbAll(q|
-        SELECT cv.cid, cv.vid, cv.rid, cv.spoil, cv.role, vr.title AS vntitle, rr.title AS rtitle
-          FROM chars_vns cv
+    if($what =~ /vns(?:\((\d+)\))?/) {
+      push @{$r{ delete $_->{xid} }{vns}}, $_ for (@{$self->dbAll("
+        SELECT cv.$colname AS xid, cv.vid, cv.rid, cv.spoil, cv.role, v.title AS vntitle, r.title AS rtitle
+          FROM chars_vns$hist cv
           JOIN vn v ON cv.vid = v.id
-          JOIN vn_rev vr ON vr.id = v.latest
           LEFT JOIN releases r ON cv.rid = r.id
-          LEFT JOIN releases_rev rr ON rr.id = r.latest
           !W
-          ORDER BY v.c_released|,
-        { 'cv.cid IN(!l)' => [[keys %r]], $1 ? ('cv.vid = ?', $1) : () }
+          ORDER BY v.c_released",
+        { "cv.$colname IN(!l)" => [[keys %r]], $1 ? ('cv.vid = ?', $1) : () }
       )});
     }
+  }
 
-    if($o{what} =~ /seiyuu/) {
-      push @{$r{ delete $_->{cid} }{seiyuu}}, $_ for (@{$self->dbAll(q|
-        SELECT cr.id AS cid, s.id AS sid, sa.name, sa.original, vs.note, v.id AS vid, vr.title AS vntitle
-          FROM vn_seiyuu vs
-          JOIN chars_rev cr ON cr.cid = vs.cid
-          JOIN staff_alias sa ON sa.id = vs.aid
-          JOIN staff s ON sa.rid = s.latest
-          JOIN vn_rev vr ON vr.id = vs.vid
-          JOIN vn v ON v.latest = vs.vid
-          !W
-          ORDER BY v.c_released, sa.name|, {
-            's.hidden = FALSE' => 1,
-            'cr.id IN(!l)' => [[ keys %r ]],
-            $o{vid} ? ('v.id = ?' => $o{vid}) : (),
-          }
-      )});
-    }
+  # Depends on the VN revision rather than char revision
+  if(@$r && $what =~ /seiyuu/) {
+    my %r = map {
+      $_->{seiyuu} = [];
+      ($_->{id}, $_)
+    } @$r;
+
+    push @{$r{ delete $_->{cid} }{seiyuu}}, $_ for (@{$self->dbAll(q|
+      SELECT vs.cid, s.id AS sid, sa.name, sa.original, vs.note, v.id AS vid, v.title AS vntitle
+        FROM vn_seiyuu vs
+        JOIN staff_alias sa ON sa.aid = vs.aid
+        JOIN staff s ON s.id = sa.id
+        JOIN vn v ON v.id = vs.id
+        !W
+        ORDER BY v.c_released, sa.name|, {
+          's.hidden = FALSE' => 1,
+          'vs.cid IN(!l)' => [[ keys %r ]],
+          $vid ? ('v.id = ?' => $vid) : (),
+        }
+    )});
   }
+
   return wantarray ? ($r, $np) : $r;
 }
 
@@ -139,15 +165,15 @@ sub dbCharRevisionInsert {
 
   my %set = map exists($o->{$_}) ? (qq|"$_" = ?|, $o->{$_}) : (),
     qw|name original alias desc image b_month b_day s_bust s_waist s_hip height weight bloodt gender main main_spoil|;
-  $self->dbExec('UPDATE edit_char !H', \%set) if keys %set;
+  $self->dbExec('UPDATE edit_chars !H', \%set) if keys %set;
 
   if($o->{traits}) {
-    $self->dbExec('DELETE FROM edit_char_traits');
-    $self->dbExec('INSERT INTO edit_char_traits (tid, spoil) VALUES (?,?)', $_->[0],$_->[1]) for (@{$o->{traits}});
+    $self->dbExec('DELETE FROM edit_chars_traits');
+    $self->dbExec('INSERT INTO edit_chars_traits (tid, spoil) VALUES (?,?)', $_->[0],$_->[1]) for (@{$o->{traits}});
   }
   if($o->{vns}) {
-    $self->dbExec('DELETE FROM edit_char_vns');
-    $self->dbExec('INSERT INTO edit_char_vns (vid, rid, spoil, role) VALUES(!l)', $_) for (@{$o->{vns}});
+    $self->dbExec('DELETE FROM edit_chars_vns');
+    $self->dbExec('INSERT INTO edit_chars_vns (vid, rid, spoil, role) VALUES(!l)', $_) for (@{$o->{vns}});
   }
 }
 
diff --git a/lib/VNDB/DB/Discussions.pm b/lib/VNDB/DB/Discussions.pm
index 2af85c4e..c380feeb 100644
--- a/lib/VNDB/DB/Discussions.pm
+++ b/lib/VNDB/DB/Discussions.pm
@@ -23,11 +23,11 @@ sub dbThreadGet {
     !$o{id} ? (
       't.hidden = FALSE' => 0 ) : (),
     $o{type} && !$o{iid} ? (
-      't.id IN(SELECT tid FROM threads_boards WHERE type IN(!l))' => [ ref $o{type} ? $o{type} : [ $o{type} ] ] ) : (),
+      'EXISTS(SELECT 1 FROM threads_boards WHERE tid = t.id AND type IN(!l))' => [ ref $o{type} ? $o{type} : [ $o{type} ] ] ) : (),
     $o{type} && $o{iid} ? (
       'tb.type = ?' => $o{type}, 'tb.iid = ?' => $o{iid} ) : (),
     $o{notusers} ? (
-      't.id NOT IN(SELECT tid FROM threads_boards WHERE type = \'u\')' => 1) : (),
+      'NOT EXISTS(SELECT 1 FROM threads_boards WHERE type = \'u\' AND tid = t.id)' => 1) : (),
   );
 
   if($o{search}) {
@@ -39,16 +39,11 @@ sub dbThreadGet {
 
   my @select = (
     qw|t.id t.title t.count t.locked t.hidden|,
-    $o{what} =~ /firstpost/ ? ('tpf.uid AS fuid', q|EXTRACT('epoch' from tpf.date) AS fdate|, 'uf.username AS fusername') : (),
     $o{what} =~ /lastpost/  ? ('tpl.uid AS luid', q|EXTRACT('epoch' from tpl.date) AS ldate|, 'ul.username AS lusername') : (),
     'p.id AS poll',
   );
 
   my @join = (
-    $o{what} =~ /firstpost/ ? (
-      'JOIN threads_posts tpf ON tpf.tid = t.id AND tpf.num = 1',
-      'JOIN users uf ON uf.id = tpf.uid'
-    ) : (),
     $o{what} =~ /lastpost/ ? (
       'JOIN threads_posts tpl ON tpl.tid = t.id AND tpl.num = t.count',
       'JOIN users ul ON ul.id = tpl.uid'
@@ -86,14 +81,23 @@ sub dbThreadGet {
         [ keys %r ]
       )});
     }
+
+    if($o{what} =~ /firstpost/) {
+      do { my $x = $r->[$r{$_->{tid}}]; $x->{fuid} = $_->{uid}; $x->{fdate} = $_->{date}; $x->{fusername} = $_->{username} } for (@{$self->dbAll(q|
+        SELECT tpf.tid, tpf.uid, EXTRACT('epoch' from tpf.date) AS date, uf.username
+          FROM threads_posts tpf
+          JOIN users uf ON tpf.uid = uf.id
+          WHERE tpf.num = 1 AND tpf.tid IN(!l)|,
+        [ keys %r ]
+      )});
+    }
+
     if($o{what} =~ /boardtitles/) {
       push(@{$r->[$r{$_->{tid}}]{boards}}, $_) for (@{$self->dbAll(q|
-        SELECT tb.tid, tb.type, tb.iid, COALESCE(u.username, vr.title, pr.name) AS title, COALESCE(u.username, vr.original, pr.original) AS original
+        SELECT tb.tid, tb.type, tb.iid, COALESCE(u.username, v.title, p.name) AS title, COALESCE(u.username, v.original, p.original) AS original
           FROM threads_boards tb
           LEFT JOIN vn v ON tb.type = 'v' AND v.id = tb.iid
-          LEFT JOIN vn_rev vr ON vr.id = v.latest
           LEFT JOIN producers p ON tb.type = 'p' AND p.id = tb.iid
-          LEFT JOIN producers_rev pr ON pr.id = p.latest
           LEFT JOIN users u ON tb.type = 'u' AND u.id = tb.iid
           WHERE tb.tid IN(!l)|,
         [ keys %r ]
@@ -189,9 +193,9 @@ sub dbPostGet {
     $o{hide} && $o{what} =~ /thread/ ? (
       't.hidden = FALSE' => 1 ) : (),
     $o{search} ? (
-      q{to_tsvector('english', strip_bb_tags(msg)) @@ to_tsquery(?)} => $o{search}) : (),
+      'bb_tsvector(msg) @@ to_tsquery(?)' => $o{search}) : (),
     $o{type} ? (
-      'tp.tid = ANY(ARRAY(SELECT tid FROM threads_boards WHERE type IN(!l)))' => [ ref $o{type} ? $o{type} : [ $o{type} ] ] ) : (),
+      'tp.tid IN(SELECT tid FROM threads_boards WHERE type IN(!l))' => [ ref $o{type} ? $o{type} : [ $o{type} ] ] ) : (),
   );
 
   my @select = (
diff --git a/lib/VNDB/DB/Misc.pm b/lib/VNDB/DB/Misc.pm
index 8927e1ec..d6389376 100644
--- a/lib/VNDB/DB/Misc.pm
+++ b/lib/VNDB/DB/Misc.pm
@@ -21,14 +21,13 @@ sub dbStats {
 
 
 # Inserts a new revision into the database
-# Arguments: type [vrp], revision id, %options->{ editsum uid ihid ilock + db[item]RevisionInsert }
-#  revision id = changes.id of the revision this edit is based on, undef to create a new DB item
-# Returns: { iid, cid, rev }
+# Arguments: type [vrp], itemid, rev, %options->{ editsum uid ihid ilock + db[item]RevisionInsert }
+#  rev = changes.rev of the revision this edit is based on, undef to create a new DB item
+# Returns: { itemid, chid, rev }
 sub dbItemEdit {
-  my($self, $type, $oid, %o) = @_;
+  my($self, $type, $itemid, $rev, %o) = @_;
 
-  my $fun = {qw|v vn r release p producer c char s staff|}->{$type};
-  $self->dbExec('SELECT edit_!s_init(?)', $fun, $oid);
+  $self->dbExec('SELECT edit_!s_init(?, ?)', $type, $itemid, $rev);
   $self->dbExec('UPDATE edit_revision !H', {
     'requester = ?' => $o{uid}||$self->authInfo->{id},
     'ip = ?'        => $self->reqIP,
@@ -43,14 +42,11 @@ sub dbItemEdit {
   $self->dbCharRevisionInsert(    \%o) if $type eq 'c';
   $self->dbStaffRevisionInsert(   \%o) if $type eq 's';
 
-  return $self->dbRow('SELECT * FROM edit_!s_commit()', $fun);
+  return $self->dbRow('SELECT * FROM edit_!s_commit()', $type);
 }
 
 
-# Options: type, iid, uid, auto, hidden, edit, page, results, what, releases
-# what: item user
-# Not very fast in each situation. Can be further optimized by: putting indexes
-# on *_rev.?id, or by caching iid, ititle and ihidden in the changes table.
+# Options: type, itemid, uid, auto, hidden, edit, page, results, releases
 sub dbRevisionGet {
   my($self, %o) = @_;
   $o{results} ||= 10;
@@ -58,68 +54,57 @@ sub dbRevisionGet {
   $o{auto} ||= 0;   # 0:show, -1:only, 1:hide
   $o{hidden} ||= 0;
   $o{edit} ||= 0;   # 0:both, -1:new, 1:edits
-  $o{what} ||= '';
-  $o{releases} = 0 if !$o{type} || $o{type} ne 'v' || !$o{iid};
-
-  my %tables = qw|v vn r releases p producers c chars s staff|;
-  # what types should we join?
-  my @types = (
-    !$o{type} ? qw(v r p c s) :
-    ref($o{type}) ? @{$o{type}} :
-    $o{type} ne 'v' ? $o{type} :
-    $o{releases} ? ('v', 'r') : 'v'
-  );
+  $o{releases} = 0 if !$o{type} || $o{type} ne 'v' || !$o{itemid};
 
   my %where = (
     $o{releases} ? (
-      q{((h.type = 'v' AND vr.vid = ?) OR (h.type = 'r' AND h.id = ANY(ARRAY(SELECT rv.rid FROM releases_vn rv WHERE rv.vid = ?))))} => [$o{iid}, $o{iid}],
+      # This selects all changes of releases that are currently linked to the VN, not release revisions that are linked to the VN.
+      # The latter seems more useful, but is also a lot more expensive.
+      q{((c.type = 'v' AND c.itemid = ?) OR (c.type = 'r' AND c.itemid = ANY(ARRAY(SELECT rv.id FROM releases_vn rv WHERE rv.vid = ?))))} => [$o{itemid}, $o{itemid}],
     ) : (
       $o{type} ? (
-        'h.type IN(!l)' => [ ref($o{type})?$o{type}:[$o{type}] ] ) : (),
-      $o{iid} ? (
-        '!sr.!sid = ?' => [ $o{type}, $o{type}, $o{iid} ] ) : (),
+        'c.type IN(!l)' => [ ref($o{type})?$o{type}:[$o{type}] ] ) : (),
+      $o{itemid} ? (
+        'c.itemid = ?' => [ $o{itemid} ] ) : (),
     ),
     $o{uid} ? (
-      'h.requester = ?' => $o{uid} ) : (),
+      'c.requester = ?' => $o{uid} ) : (),
     $o{auto} ? (
-      'h.requester !s 1' => $o{auto} < 0 ? '=' : '<>' ) : (),
+      'c.requester !s 1' => $o{auto} < 0 ? '=' : '<>' ) : (),
     $o{hidden} ? (
-      '('.join(' OR ', map sprintf('%s.hidden IS NOT NULL AND %s %1$s.hidden', $_, $o{hidden} == 1 ? 'NOT' : ''), @types).')' => 1 ) : (),
+     '!s EXISTS(SELECT 1 FROM changes c2 WHERE c2.type = c.type AND c2.itemid = c.itemid AND c2.ihid AND'.
+        ' c2.rev = (SELECT MAX(c3.rev) FROM changes c3 WHERE c3.type = c.type AND c3.itemid = c.itemid))' => $o{hidden} == 1 ? 'NOT' : '') : (),
     $o{edit} ? (
-      'h.rev !s 1' => $o{edit} < 0 ? '=' : '>' ) : (),
-  );
-
-  my @join = (
-    $o{iid} || $o{what} =~ /item/ || $o{hidden} || $o{releases} ? (
-      map sprintf(q|LEFT JOIN %s_rev %sr ON h.type = '%2$s' AND h.id = %2$sr.id|, $tables{$_}, $_), @types
-    ) : (),
-    $o{hidden} ? (
-      map sprintf(q|LEFT JOIN %s %s ON h.type = '%2$s' AND %2$sr.%2$sid = %2$s.id|, $tables{$_}, $_), @types
-    ) : (),
-    $o{what} =~ /user/ ? 'JOIN users u ON h.requester = u.id' : (),
-  );
-  push @join, 'LEFT JOIN staff_alias sa ON sa.rid = sr.id AND sa.id = sr.aid' if grep /s/, @types;
-
-  my %tcolumns = qw(v vr.title r rr.title p pr.name c cr.name s sa.name);
-  my @select = (
-    qw|h.id h.type h.requester h.comments h.rev|,
-    q|extract('epoch' from h.added) as added|,
-    $o{what} =~ /user/ ? 'u.username' : (),
-    $o{what} =~ /item/ ? (
-      'COALESCE('.join(', ', map "${_}r.${_}id", @types).') AS iid',
-      'COALESCE('.join(', ', map $tcolumns{$_}, @types).') AS ititle',
-      'COALESCE('.join(', ', map /s/ ? 'sa.original' : "${_}r.original", @types).') AS ioriginal',
-    ) : (),
+      'c.rev !s 1' => $o{edit} < 0 ? '=' : '>' ) : (),
   );
 
   my($r, $np) = $self->dbPage(\%o, q|
-    SELECT !s
-      FROM changes h
-      !s
+    SELECT c.id, c.type, c.itemid, c.requester, c.comments, c.rev, extract('epoch' from c.added) as added, u.username
+      FROM changes c
+      JOIN users u ON c.requester = u.id
       !W
-      ORDER BY h.id DESC|,
-    join(', ', @select), join(' ', @join), \%where
+      ORDER BY c.id DESC|, \%where
   );
+
+  # I couldn't find a way to fetch the titles the main query above without slowing it down considerably, so let's just do it this way.
+  if(@$r) {
+    my %r = map +($_->{id}, $_), @$r;
+    my $w = join ' OR ', ('(type = ? AND id = ?)') x @$r;
+    my @w = map +($_->{type}, $_->{id}), @$r;
+
+    $r{ $_->{id} }{ititle} = $_->{title}, $r{ $_->{id} }{ioriginal} = $_->{original} for(@{$self->dbAll("
+        SELECT id, title, original FROM (
+                    SELECT 'v'::dbentry_type, chid, title, original FROM vn_hist
+          UNION ALL SELECT 'r'::dbentry_type, chid, title, original FROM releases_hist
+          UNION ALL SELECT 'p'::dbentry_type, chid, name,  original FROM producers_hist
+          UNION ALL SELECT 'c'::dbentry_type, chid, name,  original FROM chars_hist
+          UNION ALL SELECT 's'::dbentry_type, sh.chid, name, original FROM staff_hist sh JOIN staff_alias_hist sah ON sah.chid = sh.chid AND sah.aid = sh.aid
+        ) x(type, id, title, original)
+        WHERE $w
+      ", @w
+    )});
+  }
+
   return wantarray ? ($r, $np) : $r;
 }
 
diff --git a/lib/VNDB/DB/Producers.pm b/lib/VNDB/DB/Producers.pm
index 3ff70ae1..3b6f2a32 100644
--- a/lib/VNDB/DB/Producers.pm
+++ b/lib/VNDB/DB/Producers.pm
@@ -4,12 +4,13 @@ package VNDB::DB::Producers;
 use strict;
 use warnings;
 use Exporter 'import';
+use Encode 'decode_utf8';
 
-our @EXPORT = qw|dbProducerGet dbProducerRevisionInsert|;
+our @EXPORT = qw|dbProducerGet dbProducerGetRev dbProducerRevisionInsert|;
 
 
-# options: results, page, id, search, char, rev
-# what: extended changes relations relgraph
+# options: results, page, id, search, char
+# what: extended relations relgraph
 sub dbProducerGet {
   my $self = shift;
   my %o = (
@@ -22,52 +23,80 @@ sub dbProducerGet {
   $o{search} =~ s/%//g if $o{search};
 
   my %where = (
-    !$o{id} && !$o{rev} ? (
+    !$o{id} ? (
       'p.hidden = FALSE' => 1 ) : (),
     $o{id} ? (
       'p.id IN(!l)' => [ ref $o{id} ? $o{id} : [$o{id}] ] ) : (),
     $o{search} ? (
-      '(pr.name ILIKE ? OR pr.original ILIKE ? OR pr.alias ILIKE ?)', [ map '%'.$o{search}.'%', 1..3 ] ) : (),
+      '(p.name ILIKE ? OR p.original ILIKE ? OR p.alias ILIKE ?)', [ map '%'.$o{search}.'%', 1..3 ] ) : (),
     $o{char} ? (
-      'LOWER(SUBSTR(pr.name, 1, 1)) = ?' => $o{char} ) : (),
+      'LOWER(SUBSTR(p.name, 1, 1)) = ?' => $o{char} ) : (),
     defined $o{char} && !$o{char} ? (
-      '(ASCII(pr.name) < 97 OR ASCII(pr.name) > 122) AND (ASCII(pr.name) < 65 OR ASCII(pr.name) > 90)' => 1 ) : (),
-    $o{rev} ? (
-      'c.rev = ?' => $o{rev} ) : (),
+      '(ASCII(p.name) < 97 OR ASCII(p.name) > 122) AND (ASCII(p.name) < 65 OR ASCII(p.name) > 90)' => 1 ) : (),
   );
 
-  my @join;
-  push @join, $o{rev} ? 'JOIN producers p ON p.id = pr.pid' : 'JOIN producers p ON pr.id = p.latest';
-  push @join, 'JOIN changes c ON c.id = pr.id' if $o{what} =~ /changes/ || $o{rev};
-  push @join, 'JOIN users u ON u.id = c.requester' if $o{what} =~ /changes/;
-  push @join, 'JOIN relgraphs pg ON pg.id = p.rgraph' if $o{what} =~ /relgraph/;
+  my $join = $o{what} =~ /relgraph/ ? 'JOIN relgraphs pg ON pg.id = p.rgraph' : '';
 
-  my $select = 'p.id, pr.type, pr.name, pr.original, pr.lang, pr.id AS cid, p.rgraph';
-  $select .= ', pr.desc, pr.alias, pr.website, pr.l_wp, p.hidden, p.locked' if $o{what} =~ /extended/;
-  $select .= q|, extract('epoch' from c.added) as added, c.requester, c.comments, p.latest, pr.id AS cid, u.username, c.rev, c.ihid, c.ilock| if $o{what} =~ /changes/;
+  my $select = 'p.id, p.type, p.name, p.original, p.lang, p.rgraph';
+  $select .= ', p.desc, p.alias, p.website, p.l_wp, p.hidden, p.locked' if $o{what} =~ /extended/;
   $select .= ', pg.svg' if $o{what} =~ /relgraph/;
 
   my($r, $np) = $self->dbPage(\%o, q|
     SELECT !s
-      FROM producers_rev pr
+      FROM producers p
       !s
       !W
-      ORDER BY pr.name ASC|,
-    $select, join(' ', @join), \%where,
+      ORDER BY p.name ASC|,
+    $select, $join, \%where,
   );
 
-  if(@$r && $o{what} =~ /relations/) {
+  $_->{svg} && ($_->{svg} = decode_utf8($_->{svg})) for (@$r);
+  return _enrich($self, $r, $np, 0, $o{what});
+}
+
+
+# options: id, rev, what
+# what: extended relations
+sub dbProducerGetRev {
+  my $self = shift;
+  my %o = (what => '', @_);
+
+  $o{rev} ||= $self->dbRow('SELECT MAX(rev) AS rev FROM changes WHERE type = \'p\' AND itemid = ?', $o{id})->{rev};
+
+  my $select = 'c.itemid AS id, p.type, p.name, p.original, p.lang, po.rgraph';
+  $select .= ', extract(\'epoch\' from c.added) as added, c.requester, c.comments, u.username, c.rev, c.ihid, c.ilock';
+  $select .= ', c.id AS cid, NOT EXISTS(SELECT 1 FROM changes c2 WHERE c2.type = c.type AND c2.itemid = c.itemid AND c2.rev = c.rev+1) AS lastrev';
+  $select .= ', p.desc, p.alias, p.website, p.l_wp, po.hidden, po.locked' if $o{what} =~ /extended/;
+
+  my $r = $self->dbAll(q|
+    SELECT !s
+      FROM changes c
+      JOIN producers po ON po.id = c.itemid
+      JOIN producers_hist p ON p.chid = c.id
+      JOIN users u ON u.id = c.requester
+      WHERE c.type = 'p' AND c.itemid = ? AND c.rev = ?|,
+    $select, $o{id}, $o{rev}
+  );
+
+  return _enrich($self, $r, 0, 1, $o{what});
+}
+
+
+sub _enrich {
+  my($self, $r, $np, $rev, $what) = @_;
+
+  if(@$r && $what =~ /relations/) {
+    my($col, $hist, $colname) = $rev ? ('cid', '_hist', 'chid') : ('id', '', 'id');
     my %r = map {
       $r->[$_]{relations} = [];
-      ($r->[$_]{cid}, $_)
+      ($r->[$_]{$col}, $_)
     } 0..$#$r;
 
-    push @{$r->[$r{$_->{pid1}}]{relations}}, $_ for(@{$self->dbAll(q|
-      SELECT rel.pid1, rel.pid2 AS id, rel.relation, pr.name, pr.original
-        FROM producers_relations rel
-        JOIN producers p ON rel.pid2 = p.id
-        JOIN producers_rev pr ON p.latest = pr.id
-        WHERE rel.pid1 IN(!l)|,
+    push @{$r->[$r{$_->{xid}}]{relations}}, $_ for(@{$self->dbAll(qq|
+      SELECT rel.$colname AS xid, rel.pid AS id, rel.relation, p.name, p.original
+        FROM producers_relations$hist rel
+        JOIN producers p ON rel.pid = p.id
+        WHERE rel.$colname IN(!l)|,
       [ keys %r ]
     )});
   }
@@ -83,13 +112,13 @@ sub dbProducerRevisionInsert {
 
   my %set = map exists($o->{$_}) ? (qq|"$_" = ?|, $o->{$_}) : (),
     qw|name original website l_wp type lang desc alias|;
-  $self->dbExec('UPDATE edit_producer !H', \%set) if keys %set;
+  $self->dbExec('UPDATE edit_producers !H', \%set) if keys %set;
 
   if($o->{relations}) {
-    $self->dbExec('DELETE FROM edit_producer_relations');
+    $self->dbExec('DELETE FROM edit_producers_relations');
     my $q = join ',', map '(?,?)', @{$o->{relations}};
     my @q = map +($_->[1], $_->[0]), @{$o->{relations}};
-    $self->dbExec("INSERT INTO edit_producer_relations (pid, relation) VALUES $q", @q) if @q;
+    $self->dbExec("INSERT INTO edit_producers_relations (pid, relation) VALUES $q", @q) if @q;
   }
 }
 
diff --git a/lib/VNDB/DB/Releases.pm b/lib/VNDB/DB/Releases.pm
index 3c19cfb2..eafb84c0 100644
--- a/lib/VNDB/DB/Releases.pm
+++ b/lib/VNDB/DB/Releases.pm
@@ -7,12 +7,12 @@ use POSIX 'strftime';
 use Exporter 'import';
 use VNDB::Func 'gtintype';
 
-our @EXPORT = qw|dbReleaseGet dbReleaseRevisionInsert|;
+our @EXPORT = qw|dbReleaseGet dbReleaseGetRev dbReleaseRevisionInsert|;
 
 
-# Options: id vid pid rev released page results what med sort reverse date_before date_after
+# Options: id vid pid released page results what med sort reverse date_before date_after
 #   plat lang olang type minage search resolution freeware doujin voiced ani_story ani_ero
-# What: extended changes vn producers platforms media affiliates
+# What: extended vn producers platforms media affiliates
 # Sort: title released minage
 sub dbReleaseGet {
   my($self, %o) = @_;
@@ -23,34 +23,33 @@ sub dbReleaseGet {
   $o{med}  = [ $o{med}  ] if $o{med}  && !ref $o{med};
 
   my @where = (
-    !$o{id} && !$o{rev}     ? ( 'r.hidden = FALSE' => 0       ) : (),
+    !$o{id}                 ? ( 'r.hidden = FALSE' => 0       ) : (),
     $o{id}                  ? ( 'r.id = ?'         => $o{id}  ) : (),
-    $o{rev}                 ? ( 'c.rev = ?'        => $o{rev} ) : (),
     $o{vid}                 ? ( 'rv.vid IN(!l)'    => [ ref $o{vid} ? $o{vid} : [$o{vid}] ] ) : (),
     $o{pid}                 ? ( 'rp.pid = ?'       => $o{pid} ) : (),
-    defined $o{patch}       ? ( 'rr.patch = ?'     => $o{patch}    == 1 ? 1 : 0) : (),
-    defined $o{freeware}    ? ( 'rr.freeware = ?'  => $o{freeware} == 1 ? 1 : 0) : (),
-    defined $o{doujin}      ? ( 'rr.doujin = ?'    => $o{doujin}   == 1 ? 1 : 0) : (),
-    defined $o{type}        ? ( 'rr.type = ?'      => $o{type} ) : (),
-    defined $o{date_before} ? ( 'rr.released <= ?' => $o{date_before} ) : (),
-    defined $o{date_after}  ? ( 'rr.released >= ?' => $o{date_after} ) : (),
-    defined $o{minage}      ? ( 'rr.minage IN(!l)'     => [ ref $o{minage}     ? $o{minage}     : [$o{minage}]     ] ) : (),
-    defined $o{resolution}  ? ( 'rr.resolution IN(!l)' => [ ref $o{resolution} ? $o{resolution} : [$o{resolution}] ] ) : (),
-    defined $o{voiced}      ? ( 'rr.voiced IN(!l)'     => [ ref $o{voiced}     ? $o{voiced}     : [$o{voiced}]     ] ) : (),
-    defined $o{ani_story}   ? ( 'rr.ani_story IN(!l)'  => [ ref $o{ani_story}  ? $o{ani_story}  : [$o{ani_story}]  ] ) : (),
-    defined $o{ani_ero}     ? ( 'rr.ani_ero IN(!l)'    => [ ref $o{ani_ero}    ? $o{ani_ero}    : [$o{ani_ero}]    ] ) : (),
-    defined $o{released}    ? ( 'rr.released !s ?' => [ $o{released} ? '<=' : '>', strftime('%Y%m%d', gmtime) ] ) : (),
+    defined $o{patch}       ? ( 'r.patch = ?'      => $o{patch}    == 1 ? 1 : 0) : (),
+    defined $o{freeware}    ? ( 'r.freeware = ?'   => $o{freeware} == 1 ? 1 : 0) : (),
+    defined $o{doujin}      ? ( 'r.doujin = ?'     => $o{doujin}   == 1 ? 1 : 0) : (),
+    defined $o{type}        ? ( 'r.type = ?'       => $o{type} ) : (),
+    defined $o{date_before} ? ( 'r.released <= ?'  => $o{date_before} ) : (),
+    defined $o{date_after}  ? ( 'r.released >= ?'  => $o{date_after} ) : (),
+    defined $o{minage}      ? ( 'r.minage IN(!l)'     => [ ref $o{minage}     ? $o{minage}     : [$o{minage}]     ] ) : (),
+    defined $o{resolution}  ? ( 'r.resolution IN(!l)' => [ ref $o{resolution} ? $o{resolution} : [$o{resolution}] ] ) : (),
+    defined $o{voiced}      ? ( 'r.voiced IN(!l)'     => [ ref $o{voiced}     ? $o{voiced}     : [$o{voiced}]     ] ) : (),
+    defined $o{ani_story}   ? ( 'r.ani_story IN(!l)'  => [ ref $o{ani_story}  ? $o{ani_story}  : [$o{ani_story}]  ] ) : (),
+    defined $o{ani_ero}     ? ( 'r.ani_ero IN(!l)'    => [ ref $o{ani_ero}    ? $o{ani_ero}    : [$o{ani_ero}]    ] ) : (),
+    defined $o{released}    ? ( 'r.released !s ?'  => [ $o{released} ? '<=' : '>', strftime('%Y%m%d', gmtime) ] ) : (),
     $o{lang} ? (
-      'rr.id IN(SELECT irl.rid FROM releases_lang irl JOIN releases ir ON ir.latest = irl.rid WHERE irl.lang IN(!l))' => [ ref $o{lang} ? $o{lang} : [ $o{lang} ] ] ) : (),
+      'r.id IN(SELECT irl.id FROM releases_lang irl WHERE irl.lang IN(!l))' => [ ref $o{lang} ? $o{lang} : [ $o{lang} ] ] ) : (),
     $o{olang} ? (
-      'rr.id IN(SELECT irv.rid FROM releases_vn irv JOIN releases ir ON ir.latest = irv.rid JOIN vn v ON irv.vid = v.id WHERE v.c_olang && ARRAY[!l]::language[])' => [ ref $o{olang} ? $o{olang} : [ $o{olang} ] ] ) : (),
+      'r.id IN(SELECT irv.id FROM releases_vn irv JOIN vn v ON irv.vid = v.id WHERE v.c_olang && ARRAY[!l]::language[])' => [ ref $o{olang} ? $o{olang} : [ $o{olang} ] ] ) : (),
     $o{plat} ? ('('.join(' OR ',
-      grep(/^unk$/, @{$o{plat}}) ? 'NOT EXISTS(SELECT 1 FROM releases_platforms irp WHERE irp.rid = r.latest)' : (),
-      grep(!/^unk$/, @{$o{plat}}) ? 'rr.id IN(SELECT irp.rid FROM releases_platforms irp JOIN releases ir ON ir.latest = irp.rid WHERE irp.platform IN(!l))' : (),
+      grep(/^unk$/, @{$o{plat}}) ? 'NOT EXISTS(SELECT 1 FROM releases_platforms irp WHERE irp.id = r.id)' : (),
+      grep(!/^unk$/, @{$o{plat}}) ? 'r.id IN(SELECT irp.id FROM releases_platforms irp WHERE irp.platform IN(!l))' : (),
       ).')', [ [ grep !/^unk$/, @{$o{plat}} ] ]) : (),
     $o{med} ? ('('.join(' OR ',
-      grep(/^unk$/, @{$o{med}}) ? 'NOT EXISTS(SELECT 1 FROM releases_media irm WHERE irm.rid = r.latest)' : (),
-      grep(!/^unk$/, @{$o{med}}) ? 'rr.id IN(SELECT irm.rid FROM releases_media irm JOIN releases ir ON ir.latest = irm.rid WHERE irm.medium IN(!l))' : ()
+      grep(/^unk$/, @{$o{med}}) ? 'NOT EXISTS(SELECT 1 FROM releases_media irm WHERE irm.id = r.id)' : (),
+      grep(!/^unk$/, @{$o{med}}) ? 'r.id IN(SELECT irm.id FROM releases_media irm WHERE irm.medium IN(!l))' : ()
       ).')', [ [ grep(!/^unk$/, @{$o{med}}) ] ]) : (),
   );
 
@@ -58,110 +57,135 @@ sub dbReleaseGet {
     for (split /[ -,._]/, $o{search}) {
       s/%//g;
       if(/^\d+$/ && gtintype($_)) {
-        push @where, 'rr.gtin = ?', $_;
+        push @where, 'r.gtin = ?', $_;
       } elsif(length($_) > 0) {
         $_ = "%$_%";
-        push @where, '(rr.title ILIKE ? OR rr.original ILIKE ? OR rr.catalog = ?)',
+        push @where, '(r.title ILIKE ? OR r.original ILIKE ? OR r.catalog = ?)',
           [ $_, $_, $_ ];
       }
     }
   }
 
   my @join = (
-    $o{rev} ? 'JOIN releases r ON r.id = rr.rid' : 'JOIN releases r ON rr.id = r.latest',
-    $o{vid} ? 'JOIN releases_vn rv ON rv.rid = rr.id' : (),
-    $o{pid} ? 'JOIN releases_producers rp ON rp.rid = rr.id' : (),
-    $o{what} =~ /changes/ || $o{rev} ? (
-      'JOIN changes c ON c.id = rr.id',
-      'JOIN users u ON u.id = c.requester'
-    ) : (),
+    $o{vid} ? 'JOIN releases_vn rv ON rv.id = r.id' : (),
+    $o{pid} ? 'JOIN releases_producers rp ON rp.id = r.id' : (),
   );
 
   my @select = (
-    qw|r.id rr.title rr.original rr.website rr.released rr.minage rr.type rr.patch|,
-    'rr.id AS cid',
-    $o{what} =~ /extended/ ? qw|rr.notes rr.catalog rr.gtin rr.resolution rr.voiced rr.freeware rr.doujin rr.ani_story rr.ani_ero r.hidden r.locked| : (),
-    $o{what} =~ /changes/ ?
-      (qw|c.requester c.comments r.latest u.username c.rev c.ihid c.ilock|, q|extract('epoch' from c.added) as added|) : (),
+    qw|r.id r.title r.original r.website r.released r.minage r.type r.patch|,
+    $o{what} =~ /extended/ ? qw|r.notes r.catalog r.gtin r.resolution r.voiced r.freeware r.doujin r.ani_story r.ani_ero r.hidden r.locked| : (),
     $o{pid} ? ('rp.developer', 'rp.publisher') : (),
   );
 
   my $order = sprintf {
-    title       => 'rr.title %s,                                     rr.released %1$s',
-    type        => 'rr.patch %s, rr.type %1$s,                       rr.released %1$s, rr.title %1$s',
-    publication => 'rr.doujin %s, rr.freeware %1$s,   rr.patch %1$s, rr.released %1$s, rr.title %1$s',
-    resolution  => 'rr.resolution %s,                 rr.patch %2$s, rr.released %1$s, rr.title %1$s',
-    voiced      => 'rr.voiced %s,                     rr.patch %2$s, rr.released %1$s, rr.title %1$s',
-    ani_ero     => 'rr.ani_story %s, rr.ani_ero %1$s, rr.patch %2$s, rr.released %1$s, rr.title %1$s',
-    released    => 'rr.released %s, r.id %1$s',
-    minage      => 'rr.minage %s,                                    rr.released %1$s, rr.title %1$s',
-    notes       => 'rr.notes %s,                                     rr.released %1$s, rr.title %1$s',
+    title       => 'r.title %s,                                   r.released %1$s',
+    type        => 'r.patch %s, r.type %1$s,                      r.released %1$s, r.title %1$s',
+    publication => 'r.doujin %s, r.freeware %1$s,   r.patch %1$s, r.released %1$s, r.title %1$s',
+    resolution  => 'r.resolution %s,                r.patch %2$s, r.released %1$s, r.title %1$s',
+    voiced      => 'r.voiced %s,                    r.patch %2$s, r.released %1$s, r.title %1$s',
+    ani_ero     => 'r.ani_story %s, r.ani_ero %1$s, r.patch %2$s, r.released %1$s, r.title %1$s',
+    released    => 'r.released %s, r.id %1$s',
+    minage      => 'r.minage %s,                                  r.released %1$s, r.title %1$s',
+    notes       => 'r.notes %s,                                   r.released %1$s, r.title %1$s',
   }->{ $o{sort}||'released' }, $o{reverse} ? 'DESC' : 'ASC', !$o{reverse} ? 'DESC' : 'ASC';
 
   my($r, $np) = $self->dbPage(\%o, q|
     SELECT !s
-      FROM releases_rev rr
+      FROM releases r
       !s
       !W
       ORDER BY !s|,
     join(', ', @select), join(' ', @join), \@where, $order
   );
 
+  return _enrich($self, $r, $np, 0, $o{what});
+}
+
+
+# options: id, rev, what
+# what: extended vn producers platforms media affiliates
+sub dbReleaseGetRev {
+  my $self = shift;
+  my %o = (what => '', @_);
+
+  $o{rev} ||= $self->dbRow('SELECT MAX(rev) AS rev FROM changes WHERE type = \'r\' AND itemid = ?', $o{id})->{rev};
+
+  my $select = 'c.itemid AS id, r.title, r.original, r.website, r.released, r.minage, r.type, r.patch';
+  $select .= ', r.notes, r.catalog, r.gtin, r.resolution, r.voiced, r.freeware, r.doujin, r.ani_story, r.ani_ero, ro.hidden, ro.locked' if $o{what} =~ /extended/;
+  $select .= ', extract(\'epoch\' from c.added) as added, c.requester, c.comments, u.username, c.rev, c.ihid, c.ilock';
+  $select .= ', c.id AS cid, NOT EXISTS(SELECT 1 FROM changes c2 WHERE c2.type = c.type AND c2.itemid = c.itemid AND c2.rev = c.rev+1) AS lastrev';
+
+  my $r = $self->dbAll(q|
+    SELECT !s
+      FROM changes c
+      JOIN releases ro ON ro.id = c.itemid
+      JOIN releases_hist r ON r.chid = c.id
+      JOIN users u ON u.id = c.requester
+      WHERE c.type = 'r' AND c.itemid = ? AND c.rev = ?|,
+    $select, $o{id}, $o{rev}
+  );
+
+  return _enrich($self, $r, 0, 1, $o{what});
+}
+
+
+sub _enrich {
+  my($self, $r, $np, $rev, $what) = @_;
+
   if(@$r) {
+    my($col, $hist, $colname) = $rev ? ('cid', '_hist', 'chid') : ('id', '', 'id');
     my %r = map {
       $r->[$_]{producers} = [];
       $r->[$_]{platforms} = [];
       $r->[$_]{media} = [];
       $r->[$_]{vn} = [];
       $r->[$_]{languages} = [];
-      ($r->[$_]{cid}, $_)
+      ($r->[$_]{$col}, $_)
     } 0..$#$r;
 
-    push(@{$r->[$r{$_->{rid}}]{languages}}, $_->{lang}) for (@{$self->dbAll(q|
-      SELECT rid, lang
-        FROM releases_lang
-        WHERE rid IN(!l)|,
+    push(@{$r->[$r{$_->{xid}}]{languages}}, $_->{lang}) for (@{$self->dbAll("
+      SELECT $colname AS xid, lang
+        FROM releases_lang$hist
+        WHERE $colname IN(!l)",
       [ keys %r ]
     )});
 
-    if($o{what} =~ /vn/) {
-      push(@{$r->[$r{$_->{rid}}]{vn}}, $_) for (@{$self->dbAll(q|
-        SELECT rv.rid, vr.vid, vr.title, vr.original
-          FROM releases_vn rv
+    if($what =~ /vn/) {
+      push(@{$r->[$r{$_->{xid}}]{vn}}, $_) for (@{$self->dbAll("
+        SELECT rv.$colname AS xid, v.id AS vid, v.title, v.original
+          FROM releases_vn$hist rv
           JOIN vn v ON v.id = rv.vid
-          JOIN vn_rev vr ON vr.id = v.latest
-          WHERE rv.rid IN(!l)
-          ORDER BY vr.title|,
+          WHERE rv.$colname IN(!l)
+          ORDER BY v.title",
         [ keys %r ]
       )});
     }
 
-    if($o{what} =~ /producers/) {
-      push(@{$r->[$r{$_->{rid}}]{producers}}, $_) for (@{$self->dbAll(q|
-        SELECT rp.rid, rp.developer, rp.publisher, p.id, pr.name, pr.original, pr.type
-          FROM releases_producers rp
+    if($what =~ /producers/) {
+      push(@{$r->[$r{$_->{xid}}]{producers}}, $_) for (@{$self->dbAll("
+        SELECT rp.$colname AS xid, rp.developer, rp.publisher, p.id, p.name, p.original, p.type
+          FROM releases_producers$hist rp
           JOIN producers p ON rp.pid = p.id
-          JOIN producers_rev pr ON pr.id = p.latest
-          WHERE rp.rid IN(!l)
-          ORDER BY pr.name|,
+          WHERE rp.$colname IN(!l)
+          ORDER BY p.name",
         [ keys %r ]
       )});
     }
 
-    if($o{what} =~ /platforms/) {
-      push(@{$r->[$r{$_->{rid}}]{platforms}}, $_->{platform}) for (@{$self->dbAll(q|
-        SELECT rid, platform
-          FROM releases_platforms
-          WHERE rid IN(!l)|,
+    if($what =~ /platforms/) {
+      push(@{$r->[$r{$_->{xid}}]{platforms}}, $_->{platform}) for (@{$self->dbAll("
+        SELECT $colname AS xid, platform
+          FROM releases_platforms$hist
+          WHERE $colname IN(!l)",
         [ keys %r ]
       )});
     }
 
-    if($o{what} =~ /media/) {
-      push(@{$r->[$r{$_->{rid}}]{media}}, $_) for (@{$self->dbAll(q|
-        SELECT rid, medium, qty
-          FROM releases_media
-          WHERE rid IN(!l)|,
+    if($what =~ /media/) {
+      push(@{$r->[$r{$_->{xid}}]{media}}, $_) for (@{$self->dbAll("
+        SELECT $colname AS xid, medium, qty
+          FROM releases_media$hist
+          WHERE $colname IN(!l)",
         [ keys %r ]
       )});
     }
@@ -179,38 +203,38 @@ sub dbReleaseRevisionInsert {
   my %set = map exists($o->{$_}) ? ("$_ = ?", $o->{$_}) : (),
     qw|title original gtin catalog website released notes minage type
        patch resolution voiced freeware doujin ani_story ani_ero|;
-  $self->dbExec('UPDATE edit_release !H', \%set) if keys %set;
+  $self->dbExec('UPDATE edit_releases !H', \%set) if keys %set;
 
   if($o->{languages}) {
-    $self->dbExec('DELETE FROM edit_release_lang');
+    $self->dbExec('DELETE FROM edit_releases_lang');
     my $q = join ',', map '(?)', @{$o->{languages}};
-    $self->dbExec("INSERT INTO edit_release_lang (lang) VALUES $q", @{$o->{languages}}) if @{$o->{languages}};
+    $self->dbExec("INSERT INTO edit_releases_lang (lang) VALUES $q", @{$o->{languages}}) if @{$o->{languages}};
   }
 
   if($o->{producers}) {
-    $self->dbExec('DELETE FROM edit_release_producers');
+    $self->dbExec('DELETE FROM edit_releases_producers');
     my $q = join ',', map '(?,?,?)', @{$o->{producers}};
     my @q = map +($_->[0], $_->[1]?1:0, $_->[2]?1:0), @{$o->{producers}};
-    $self->dbExec("INSERT INTO edit_release_producers (pid, developer, publisher) VALUES $q", @q) if @q;
+    $self->dbExec("INSERT INTO edit_releases_producers (pid, developer, publisher) VALUES $q", @q) if @q;
   }
 
   if($o->{platforms}) {
-    $self->dbExec('DELETE FROM edit_release_platforms');
+    $self->dbExec('DELETE FROM edit_releases_platforms');
     my $q = join ',', map '(?)', @{$o->{platforms}};
-    $self->dbExec("INSERT INTO edit_release_platforms (platform) VALUES $q", @{$o->{platforms}}) if @{$o->{platforms}};
+    $self->dbExec("INSERT INTO edit_releases_platforms (platform) VALUES $q", @{$o->{platforms}}) if @{$o->{platforms}};
   }
 
   if($o->{vn}) {
-    $self->dbExec('DELETE FROM edit_release_vn');
+    $self->dbExec('DELETE FROM edit_releases_vn');
     my $q = join ',', map '(?)', @{$o->{vn}};
-    $self->dbExec("INSERT INTO edit_release_vn (vid) VALUES $q", @{$o->{vn}}) if @{$o->{vn}};
+    $self->dbExec("INSERT INTO edit_releases_vn (vid) VALUES $q", @{$o->{vn}}) if @{$o->{vn}};
   }
 
   if($o->{media}) {
-    $self->dbExec('DELETE FROM edit_release_media');
+    $self->dbExec('DELETE FROM edit_releases_media');
     my $q = join ',', map '(?,?)', @{$o->{media}};
     my @q = map +($_->[0], $_->[1]), @{$o->{media}};
-    $self->dbExec("INSERT INTO edit_release_media (medium, qty) VALUES $q", @q) if @q;
+    $self->dbExec("INSERT INTO edit_releases_media (medium, qty) VALUES $q", @q) if @q;
   }
 }
 
diff --git a/lib/VNDB/DB/Staff.pm b/lib/VNDB/DB/Staff.pm
index 4dfe0756..69f4d6b3 100644
--- a/lib/VNDB/DB/Staff.pm
+++ b/lib/VNDB/DB/Staff.pm
@@ -5,9 +5,9 @@ use strict;
 use warnings;
 use Exporter 'import';
 
-our @EXPORT = qw|dbStaffGet dbStaffRevisionInsert dbStaffAliasIds|;
+our @EXPORT = qw|dbStaffGet dbStaffGetRev dbStaffRevisionInsert dbStaffAliasIds|;
 
-# options: results, page, id, aid, search, exact, rev, truename, role, gender
+# options: results, page, id, aid, search, exact, truename, role, gender
 # what: extended changes roles aliases
 sub dbStaffGet {
   my $self = shift;
@@ -31,15 +31,15 @@ sub dbStaffGet {
   $o{search} =~ s/%//g if $o{search};
 
   my %where = (
-    !$o{id} && !$o{rev} ? ( 's.hidden = FALSE' => 1 ) : (),
+    !$o{id} ? ( 's.hidden = FALSE' => 1 ) : (),
     $o{id}  ? ( ref $o{id}  ? ('s.id IN(!l)'  => [$o{id}])  : ('s.id = ?' => $o{id}) ) : (),
-    $o{aid} ? ( ref $o{aid} ? ('sa.id IN(!l)' => [$o{aid}]) : ('sa.id = ?' => $o{aid}) ) : (),
-    $o{truename} ? ( 'sr.aid = sa.id' => 1 ) : (),
-    defined $o{gender} ? ( 'sr.gender IN(!l)' => [ ref $o{gender} ? $o{gender} : [$o{gender}] ]) : (),
+    $o{aid} ? ( ref $o{aid} ? ('sa.aid IN(!l)' => [$o{aid}]) : ('sa.aid = ?' => $o{aid}) ) : (),
+    $o{id} || $o{truename} ? ( 's.aid = sa.aid' => 1 ) : (),
+    defined $o{gender} ? ( 's.gender IN(!l)' => [ ref $o{gender} ? $o{gender} : [$o{gender}] ]) : (),
     defined $o{role} ? (
       '('.join(' OR ',
-        @roles ? ( 'EXISTS(SELECT 1 FROM vn_staff vs JOIN vn v ON v.latest = vs.vid WHERE vs.aid = sa.id AND vs.role IN(!l) AND NOT v.hidden)' ) : (),
-        $seiyuu ? ( 'EXISTS(SELECT 1 FROM vn_seiyuu vsy JOIN vn v ON v.latest = vsy.vid WHERE vsy.aid = sa.id AND NOT v.hidden)' ) : ()
+        @roles ? ( 'EXISTS(SELECT 1 FROM vn_staff vs JOIN vn v ON v.id = vs.id WHERE vs.aid = sa.aid AND vs.role IN(!l) AND NOT v.hidden)' ) : (),
+        $seiyuu ? ( 'EXISTS(SELECT 1 FROM vn_seiyuu vsy JOIN vn v ON v.id = vsy.id WHERE vsy.aid = sa.aid AND NOT v.hidden)' ) : ()
       ).')' => ( @roles ? [ \@roles ] : 1 ),
     ) : (),
     $o{exact} ? ( '(sa.name = ? OR sa.original = ?)' => [ ($o{exact}) x 2 ] ) : (),
@@ -53,87 +53,114 @@ sub dbStaffGet {
     $o{char} ? ( 'LOWER(SUBSTR(sa.name, 1, 1)) = ?' => $o{char} ) : (),
     defined $o{char} && !$o{char} ?
       ( '(ASCII(sa.name) < 97 OR ASCII(sa.name) > 122) AND (ASCII(sa.name) < 65 OR ASCII(sa.name) > 90)' => 1 ) : (),
-    $o{rev} ? ( 'c.rev = ?' => $o{rev} ) : (),
   );
 
-  my @join;
-  push @join, 'JOIN staff s ON '.($o{rev} ? 's.id = sr.sid' : 'sr.id = s.latest');
-  push @join, 'JOIN staff_alias sa ON sa.rid = sr.id'.($o{id}?' AND sa.id = sr.aid':'');
-  push @join, 'JOIN changes c ON c.id = sr.id' if $o{what} =~ /changes/ || $o{rev};
-  push @join, 'JOIN users u ON u.id = c.requester' if $o{what} =~ /changes/;
-
-  my $select = 's.id, sa.id AS aid, sa.name, sa.original, sr.gender, sr.lang, sr.id AS cid';
-  $select .= ', sr.desc, sr.l_wp, sr.l_site, sr.l_twitter, sr.l_anidb, s.hidden, s.locked' if $o{what} =~ /extended/;
-  $select .= q|, extract('epoch' from c.added) as added, c.requester, c.comments, s.latest, u.username, c.rev, c.ihid, c.ilock| if $o{what} =~ /changes/;
-
-  my $order = 'ORDER BY sa.name';
+  my $select = 's.id, sa.aid, sa.name, sa.original, s.gender, s.lang';
+  $select .= ', s.desc, s.l_wp, s.l_site, s.l_twitter, s.l_anidb, s.hidden, s.locked' if $o{what} =~ /extended/;
 
   my($r, $np) = $self->dbPage(\%o, q|
     SELECT !s
-      FROM staff_rev sr
-      !s
+      FROM staff s
+      JOIN staff_alias sa ON sa.id = s.id
       !W
-      !s|,
-    $select, join(' ', @join), \%where, $order
+      ORDER BY sa.name|,
+    $select, \%where
   );
 
-  if (@$r && $o{what} =~ /roles|aliases/) {
+  return _enrich($self, $r, $np, 0, $o{what});
+}
+
+
+sub dbStaffGetRev {
+  my $self = shift;
+  my %o = (what => '', @_);
+
+  $o{rev} ||= $self->dbRow('SELECT MAX(rev) AS rev FROM changes WHERE type = \'s\' AND itemid = ?', $o{id})->{rev};
+
+  my $select = 'c.itemid AS id, sa.aid, sa.name, sa.original, s.gender, s.lang';
+  $select .= ', extract(\'epoch\' from c.added) as added, c.requester, c.comments, u.username, c.rev, c.ihid, c.ilock';
+  $select .= ', c.id AS cid, NOT EXISTS(SELECT 1 FROM changes c2 WHERE c2.type = c.type AND c2.itemid = c.itemid AND c2.rev = c.rev+1) AS lastrev';
+  $select .= ', s.desc, s.l_wp, s.l_site, s.l_twitter, s.l_anidb, so.hidden, so.locked' if $o{what} =~ /extended/;
+
+  my $r = $self->dbAll(q|
+    SELECT !s
+      FROM changes c
+      JOIN staff so ON so.id = c.itemid
+      JOIN staff_hist s ON s.chid = c.id
+      JOIN staff_alias_hist sa ON sa.chid = c.id AND s.aid = sa.aid
+      JOIN users u ON u.id = c.requester
+      WHERE c.type = 's' AND c.itemid = ? AND c.rev = ?|,
+    $select, $o{id}, $o{rev}
+  );
+
+  return _enrich($self, $r, 0, 1, $o{what});
+}
+
+
+sub _enrich {
+  my($self, $r, $np, $rev, $what) = @_;
+
+  # Role info is linked to VN revisions, so is independent of the selected staff revision
+  if(@$r && $what =~ /roles/) {
     my %r = map {
       $_->{roles} = [];
       $_->{cast} = [];
+      ($_->{id}, $_);
+    } @$r;
+
+    push @{$r{ delete $_->{id} }{roles}}, $_ for (@{$self->dbAll(q|
+      SELECT sa.id, sa.aid, v.id AS vid, sa.name, sa.original, v.c_released, v.title, v.original AS t_original, vs.role, vs.note
+        FROM vn_staff vs
+        JOIN vn v ON v.id = vs.id
+        JOIN staff_alias sa ON vs.aid = sa.aid
+        WHERE sa.id IN(!l) AND NOT v.hidden
+        ORDER BY v.c_released ASC, v.title ASC, vs.role ASC|, [ keys %r ]
+    )});
+    push @{$r{ delete $_->{id} }{cast}}, $_ for (@{$self->dbAll(q|
+      SELECT sa.id, sa.aid, v.id AS vid, sa.name, sa.original, v.c_released, v.title, v.original AS t_original, c.id AS cid, c.name AS c_name, c.original AS c_original, vs.note
+        FROM vn_seiyuu vs
+        JOIN vn v ON v.id = vs.id
+        JOIN chars c ON c.id = vs.cid
+        JOIN staff_alias sa ON vs.aid = sa.aid
+        WHERE sa.id IN(!l) AND NOT v.hidden
+        ORDER BY v.c_released ASC, v.title ASC|, [ keys %r ]
+    )});
+  }
+
+  if(@$r && $what =~ /aliases/) {
+    my ($col, $hist, $colname) = $rev ? ('cid', '_hist', 'chid') : ('id', '', 'id');
+    my %r = map {
       $_->{aliases} = [];
-      ($_->{cid}, $_);
+      ($_->{$col}, $_);
     } @$r;
-    if ($o{what} =~ /roles/) {
-      push @{$r{ delete $_->{rid} }{roles}}, $_ for (@{$self->dbAll(q|
-        SELECT sa.rid, vr.vid, sa.name, sa.original, v.c_released, vr.title, vr.original AS t_original, vs.role, vs.note
-          FROM vn_staff vs
-          JOIN vn_rev vr ON vr.id = vs.vid
-          JOIN vn v ON v.latest = vr.id
-          JOIN staff_alias sa ON vs.aid = sa.id
-          WHERE sa.rid IN(!l) AND NOT v.hidden
-          ORDER BY v.c_released ASC, vr.title ASC, vs.role ASC|, [ keys %r ]
-      )});
-      push @{$r{ delete $_->{rid} }{cast}}, $_ for (@{$self->dbAll(q|
-        SELECT sa.rid, vr.vid, sa.name, sa.original, v.c_released, vr.title, vr.original AS t_original, cr.cid, cr.name AS c_name, cr.original AS c_original, vs.note
-          FROM vn_seiyuu vs
-          JOIN vn_rev vr ON vr.id = vs.vid
-          JOIN vn v ON v.latest = vr.id
-          JOIN chars_rev cr ON cr.cid = vs.cid
-          JOIN chars c ON c.latest = cr.id
-          JOIN staff_alias sa ON vs.aid = sa.id
-          WHERE sa.rid IN(!l) AND NOT v.hidden
-          ORDER BY v.c_released ASC, vr.title ASC|, [ keys %r ]
-      )});
-    }
-    if ($o{what} =~ /aliases/) {
-      push @{$r{ delete $_->{rid} }{aliases}}, $_ for (@{$self->dbAll(q|
-        SELECT sa.id, sa.rid, sa.name, sa.original
-          FROM staff_alias sa
-          JOIN staff_rev sr ON sr.id = sa.rid
-          WHERE sr.id IN(!l) AND sr.aid <> sa.id
-          ORDER BY sa.name ASC|, [ keys %r ]
-      )});
-    }
+
+    push @{$r{ delete $_->{xid} }{aliases}}, $_ for (@{$self->dbAll("
+      SELECT s.$colname AS xid, sa.aid, sa.name, sa.original
+        FROM staff_alias$hist sa
+        JOIN staff$hist s ON s.$colname = sa.$colname
+        WHERE s.$colname IN(!l) AND s.aid <> sa.aid
+        ORDER BY sa.name ASC", [ keys %r ]
+    )});
   }
 
   return wantarray ? ($r, $np) : $r;
 }
 
+
 # Updates the edit_* tables, used from dbItemEdit()
 # Arguments: { columns in staff_rev and staff_alias},
 sub dbStaffRevisionInsert {
   my($self, $o) = @_;
 
-  $self->dbExec('DELETE FROM edit_staff_aliases');
+  $self->dbExec('DELETE FROM edit_staff_alias');
   if($o->{aid}) {
     $self->dbExec(q|
-      INSERT INTO edit_staff_aliases (id, name, original) VALUES (?, ?, ?)|,
+      INSERT INTO edit_staff_alias (aid, name, original) VALUES (?, ?, ?)|,
       $o->{aid}, $o->{name}, $o->{original});
   } else {
     $o->{aid} = $self->dbRow(q|
-      INSERT INTO edit_staff_aliases (name, original) VALUES (?, ?) RETURNING id|,
-      $o->{name}, $o->{original})->{id};
+      INSERT INTO edit_staff_alias (name, original) VALUES (?, ?) RETURNING aid|,
+      $o->{name}, $o->{original})->{aid};
   }
 
   my %staff = map exists($o->{$_}) ? (qq|"$_" = ?|, $o->{$_}) : (),
@@ -141,9 +168,9 @@ sub dbStaffRevisionInsert {
   $self->dbExec('UPDATE edit_staff !H', \%staff) if %staff;
   for my $a (@{$o->{aliases}}) {
     if($a->{aid}) {
-      $self->dbExec('INSERT INTO edit_staff_aliases (id, name, original) VALUES (!l)', [ @{$a}{qw|aid name orig|} ]);
+      $self->dbExec('INSERT INTO edit_staff_alias (aid, name, original) VALUES (!l)', [ @{$a}{qw|aid name orig|} ]);
     } else {
-      $self->dbExec('INSERT INTO edit_staff_aliases (name, original) VALUES (?, ?)', $a->{name}, $a->{orig});
+      $self->dbExec('INSERT INTO edit_staff_alias (name, original) VALUES (?, ?)', $a->{name}, $a->{orig});
     }
   }
 }
@@ -153,10 +180,10 @@ sub dbStaffRevisionInsert {
 sub dbStaffAliasIds {
   my($self, $sid) = @_;
   return $self->dbAll(q|
-    SELECT DISTINCT sa.id
-      FROM staff_alias sa
-      JOIN staff_rev sr ON sr.id = sa.rid
-      WHERE sr.sid = ?|, $sid);
+    SELECT DISTINCT sa.aid
+      FROM changes c
+      JOIN staff_alias_hist sa ON sa.chid = c.id
+      WHERE c.type = 's' AND c.itemid = ?|, $sid);
 }
 
 1;
diff --git a/lib/VNDB/DB/Tags.pm b/lib/VNDB/DB/Tags.pm
index 37411a62..f3c901ab 100644
--- a/lib/VNDB/DB/Tags.pm
+++ b/lib/VNDB/DB/Tags.pm
@@ -184,11 +184,11 @@ sub dbTagLinks {
 
   my @select = (
     qw|tv.tag tv.vid tv.uid tv.vote tv.spoiler tv.ignore|, "EXTRACT('epoch' from tv.date) AS date",
-    $o{what} =~ /details/ ? (qw|vr.title u.username t.name|) : (),
+    $o{what} =~ /details/ ? (qw|v.title u.username t.name|) : (),
   );
 
   my @join = $o{what} =~ /details/ ? (
-    'JOIN vn v ON v.id = tv.vid JOIN vn_rev vr ON vr.id = v.latest',
+    'JOIN vn v ON v.id = tv.vid',
     'JOIN users u ON u.id = tv.uid',
     'JOIN tags t ON t.id = tv.tag'
   ) : ();
@@ -196,7 +196,7 @@ sub dbTagLinks {
   my $order = !$o{sort} ? '' : 'ORDER BY '.{
     username => 'u.username',
     date     => 'tv.date',
-    title    => 'vr.title',
+    title    => 'v.title',
     tag      => 't.name',
   }->{$o{sort}}.($o{reverse} ? ' DESC' : ' ASC');
 
diff --git a/lib/VNDB/DB/ULists.pm b/lib/VNDB/DB/ULists.pm
index 6831e579..23bde7cf 100644
--- a/lib/VNDB/DB/ULists.pm
+++ b/lib/VNDB/DB/ULists.pm
@@ -59,22 +59,21 @@ sub dbVNListList {
     'vl.uid = ?' => $o{uid},
     defined($o{voted}) ? ('vo.vote !s NULL' => $o{voted} ? 'IS NOT' : 'IS') : (),
     defined($o{status})? ('vl.status = ?' => $o{status}) : (),
-    $o{char}           ? ('LOWER(SUBSTR(vr.title, 1, 1)) = ?' => $o{char} ) : (),
+    $o{char}           ? ('LOWER(SUBSTR(v.title, 1, 1)) = ?' => $o{char} ) : (),
     defined $o{char} && !$o{char} ? (
-      '(ASCII(vr.title) < 97 OR ASCII(vr.title) > 122) AND (ASCII(vr.title) < 65 OR ASCII(vr.title) > 90)' => 1 ) : (),
+      '(ASCII(v.title) < 97 OR ASCII(v.title) > 122) AND (ASCII(v.title) < 65 OR ASCII(v.title) > 90)' => 1 ) : (),
   );
 
   my $order = sprintf {
-    title => 'vr.title %s',
-    vote  => 'vo.vote %s NULLS LAST, vr.title ASC',
+    title => 'v.title %s',
+    vote  => 'vo.vote %s NULLS LAST, v.title ASC',
   }->{ $o{sort}||'title' }, $o{reverse} ? 'DESC' : 'ASC';
 
   # execute query
   my($r, $np) = $self->dbPage(\%o, qq|
-    SELECT vr.vid, vr.title, vr.original, vl.status, vl.notes, COALESCE(vo.vote, 0) AS vote
+    SELECT vl.vid, v.title, v.original, vl.status, vl.notes, COALESCE(vo.vote, 0) AS vote
       FROM vnlists vl
       JOIN vn v ON v.id = vl.vid
-      JOIN vn_rev vr ON vr.id = v.latest
       LEFT JOIN votes vo ON vo.vid = vl.vid AND vo.uid = vl.uid
       !W
       ORDER BY !s|,
@@ -89,27 +88,26 @@ sub dbVNListList {
     } @$r;
 
     my $rel = $self->dbAll(q|
-      SELECT rv.vid, rr.rid, r.latest, rr.title, rr.original, rr.released, rr.type, rl.status
+      SELECT rv.vid, rl.rid, r.title, r.original, r.released, r.type, rl.status
         FROM rlists rl
         JOIN releases r ON rl.rid = r.id
-        JOIN releases_rev rr ON rr.id = r.latest
-        JOIN releases_vn rv ON rv.rid = r.latest
+        JOIN releases_vn rv ON rv.id = r.id
         WHERE rl.uid = ?
           AND rv.vid IN(!l)
-        ORDER BY rr.released ASC|,
+        ORDER BY r.released ASC|,
       $o{uid}, [ keys %vns ]
     );
 
     if(@$rel) {
-      my %rel = map { $_->{latest} => [] } @$rel;
-      push(@{$rel{$_->{rid}}}, $_->{lang}) for (@{$self->dbAll(q|
-        SELECT rid, lang
+      my %rel = map { $_->{rid} => [] } @$rel;
+      push(@{$rel{$_->{id}}}, $_->{lang}) for (@{$self->dbAll(q|
+        SELECT id, lang
           FROM releases_lang
-          WHERE rid IN(!l)|,
+          WHERE id IN(!l)|,
         [ keys %rel ]
       )});
       for(@$rel) {
-        $_->{languages} = $rel{$_->{latest}};
+        $_->{languages} = $rel{$_->{rid}};
         push @{$vns{$_->{vid}}}, $_;
       }
     }
@@ -189,9 +187,9 @@ sub dbVoteGet {
     $o{vid} ? ( 'n.vid = ?' => $o{vid} ) : (),
     $o{hide} ? ( 'NOT EXISTS(SELECT 1 FROM users_prefs WHERE uid = n.uid AND key = \'hide_list\')' => 1 ) : (),
     $o{hide_ign} ? ( '(NOT u.ign_votes OR u.id = ?)' => $self->authInfo->{id}||0 ) : (),
-    $o{vn_char}  ? ( 'LOWER(SUBSTR(vr.title, 1, 1)) = ?' => $o{vn_char} ) : (),
+    $o{vn_char}  ? ( 'LOWER(SUBSTR(v.title, 1, 1)) = ?' => $o{vn_char} ) : (),
     defined $o{vn_char} && !$o{vn_char} ? (
-      '(ASCII(vr.title) < 97 OR ASCII(vr.title) > 122) AND (ASCII(vr.title) < 65 OR ASCII(vr.title) > 90)' => 1 ) : (),
+      '(ASCII(v.title) < 97 OR ASCII(v.title) > 122) AND (ASCII(v.title) < 65 OR ASCII(v.title) > 90)' => 1 ) : (),
     $o{user_char} ? ( 'LOWER(SUBSTR(u.username, 1, 1)) = ?' => $o{user_char} ) : (),
     defined $o{user_char} && !$o{user_char} ? (
       '(ASCII(u.username) < 97 OR ASCII(u.username) > 122) AND (ASCII(u.username) < 65 OR ASCII(u.username) > 90)' => 1 ) : (),
@@ -200,13 +198,12 @@ sub dbVoteGet {
   my @select = (
     qw|n.vid n.vote n.uid|, q|extract('epoch' from n.date) as date|,
     $o{what} =~ /user/ ? ('u.username') : (),
-    $o{what} =~ /vn/ ? (qw|vr.title vr.original|) : (),
+    $o{what} =~ /vn/ ? (qw|v.title v.original|) : (),
   );
 
   my @join = (
     $o{what} =~ /vn/ ? (
       'JOIN vn v ON v.id = n.vid',
-      'JOIN vn_rev vr ON vr.id = v.latest'
     ) : (),
     $o{what} =~ /user/ || $o{hide} ? (
       'JOIN users u ON u.id = n.uid'
@@ -216,8 +213,8 @@ sub dbVoteGet {
   my $order = sprintf {
     date     => 'n.date %s',
     username => 'u.username %s',
-    title    => 'vr.title %s',
-    vote     => 'n.vote %s'.($o{what} =~ /vn/ ? ', vr.title ASC' : $o{what} =~ /user/ ? ', u.username ASC' : ''),
+    title    => 'v.title %s',
+    vote     => 'n.vote %s'.($o{what} =~ /vn/ ? ', v.title ASC' : $o{what} =~ /user/ ? ', u.username ASC' : ''),
   }->{$o{sort}}, $o{reverse} ? 'DESC' : 'ASC';
 
   my($r, $np) = $self->dbPage(\%o, q|
@@ -302,15 +299,14 @@ sub dbWishListGet {
   my $select = q|wl.vid, wl.wstat, extract('epoch' from wl.added) AS added|;
   my @join;
   if($o{what} =~ /vn/) {
-    $select .= ', vr.title, vr.original';
-    push @join, 'JOIN vn v ON v.id = wl.vid',
-    'JOIN vn_rev vr ON vr.id = v.latest';
+    $select .= ', v.title, v.original';
+    push @join, 'JOIN vn v ON v.id = wl.vid';
   }
 
   my $order = sprintf  {
-    title => 'vr.title %s',
+    title => 'v.title %s',
     added => 'wl.added %s',
-    wstat => 'wl.wstat %2$s, vr.title ASC',
+    wstat => 'wl.wstat %2$s, v.title ASC',
   }->{ $o{sort}||'added' }, $o{reverse} ? 'DESC' : 'ASC', $o{reverse} ? 'ASC' : 'DESC';
 
   my($r, $np) = $self->dbPage(\%o, q|
diff --git a/lib/VNDB/DB/VN.pm b/lib/VNDB/DB/VN.pm
index 5d404b28..8f3bf913 100644
--- a/lib/VNDB/DB/VN.pm
+++ b/lib/VNDB/DB/VN.pm
@@ -7,12 +7,12 @@ use Exporter 'import';
 use VNDB::Func 'gtintype', 'normalize_query';
 use Encode 'decode_utf8';
 
-our @EXPORT = qw|dbVNGet dbVNRevisionInsert dbVNImageId dbScreenshotAdd dbScreenshotGet dbScreenshotRandom dbVNHasChar dbVNHasStaff dbVNImportSeiyuu|;
+our @EXPORT = qw|dbVNGet dbVNGetRev dbVNRevisionInsert dbVNImageId dbScreenshotAdd dbScreenshotGet dbScreenshotRandom dbVNHasChar dbVNHasStaff dbVNImportSeiyuu|;
 
 
-# Options: id, rev, char, search, length, lang, olang, plat, tag_inc, tag_exc, tagspoil,
+# Options: id, char, search, length, lang, olang, plat, tag_inc, tag_exc, tagspoil,
 #   hasani, hasshot, ul_notblack, ul_onwish, results, page, what, sort, reverse, inc_hidden
-# What: extended anime credits relations screenshots relgraph rating ranking changes wishlist vnlist
+# What: extended anime credits relations screenshots relgraph rating ranking wishlist vnlist
 #  Note: wishlist and vnlist are ignored (no db search) unless a user is logged in
 # Sort: id rel pop rating title tagscore rand
 sub dbVNGet {
@@ -33,14 +33,12 @@ sub dbVNGet {
   my @where = (
     $o{id} ? (
       'v.id IN(!l)' => [ ref $o{id} ? $o{id} : [$o{id}] ] ) : (),
-    $o{rev} ? (
-      'c.rev = ?' => $o{rev} ) : (),
     $o{char} ? (
-      'LOWER(SUBSTR(vr.title, 1, 1)) = ?' => $o{char} ) : (),
+      'LOWER(SUBSTR(v.title, 1, 1)) = ?' => $o{char} ) : (),
     defined $o{char} && !$o{char} ? (
-      '(ASCII(vr.title) < 97 OR ASCII(vr.title) > 122) AND (ASCII(vr.title) < 65 OR ASCII(vr.title) > 90)' => 1 ) : (),
+      '(ASCII(v.title) < 97 OR ASCII(v.title) > 122) AND (ASCII(v.title) < 65 OR ASCII(v.title) > 90)' => 1 ) : (),
     defined $o{length} ? (
-      'vr.length IN(!l)' => [ ref $o{length} ? $o{length} : [$o{length}] ]) : (),
+      'v.length IN(!l)' => [ ref $o{length} ? $o{length} : [$o{length}] ]) : (),
     $o{lang} ? (
       'v.c_languages && ARRAY[!l]::language[]' => [ ref $o{lang} ? $o{lang} : [$o{lang}] ]) : (),
     $o{olang} ? (
@@ -48,9 +46,9 @@ sub dbVNGet {
     $o{plat} ? (
       'v.c_platforms && ARRAY[!l]::platform[]' => [ ref $o{plat} ? $o{plat} : [$o{plat}] ]) : (),
     defined $o{hasani} ? (
-      '!sEXISTS(SELECT 1 FROM vn_anime va WHERE va.vid = vr.id)' => [ $o{hasani} ? '' : 'NOT ' ]) : (),
+      '!sEXISTS(SELECT 1 FROM vn_anime va WHERE va.id = v.id)' => [ $o{hasani} ? '' : 'NOT ' ]) : (),
     defined $o{hasshot} ? (
-      '!sEXISTS(SELECT 1 FROM vn_screenshots vs WHERE vs.vid = vr.id)' => [ $o{hasshot} ? '' : 'NOT ' ]) : (),
+      '!sEXISTS(SELECT 1 FROM vn_screenshots vs WHERE vs.id = v.id)' => [ $o{hasshot} ? '' : 'NOT ' ]) : (),
     $o{tag_inc} ? (
       'v.id IN(SELECT vid FROM tags_vn_inherit WHERE tag IN(!l) AND spoiler <= ? GROUP BY vid HAVING COUNT(tag) = ?)',
       [ ref $o{tag_inc} ? $o{tag_inc} : [$o{tag_inc}], $o{tagspoil}, ref $o{tag_inc} ? $#{$o{tag_inc}}+1 : 1 ]) : (),
@@ -66,24 +64,14 @@ sub dbVNGet {
       'v.id !s IN(SELECT vid FROM votes WHERE uid = ?)' => [ $o{ul_voted} ? '' : 'NOT', $uid ] ) : (),
     $uid && defined $o{ul_onlist} ? (
       'v.id !s IN(SELECT vid FROM vnlists WHERE uid = ?)' => [ $o{ul_onlist} ? '' : 'NOT', $uid ] ) : (),
-   # don't fetch hidden items unless we ask for an ID
-    !$o{id} && !$o{rev} && !$o{inc_hidden} ? (
+    !$o{id} && !$o{inc_hidden} ? (
       'v.hidden = FALSE' => 0 ) : (),
-   # optimize fetching random entries (only when there are no other filters present, otherwise this won't work well)
+    # optimize fetching random entries (only when there are no other filters present, otherwise this won't work well)
     $o{sort} eq 'rand' && $o{results} <= 10 && !grep(!/^(?:results|page|what|sort|tagspoil)$/, keys %o) ? (
-      sprintf 'v.id IN(SELECT floor(random() * last_value)::integer
-           FROM generate_series(1,20), (SELECT last_value FROM vn_id_seq) s1
-          LIMIT 20)' ) : (),
+      'v.id IN(SELECT floor(random() * last_value)::integer FROM generate_series(1,20), (SELECT MAX(id) AS last_value FROM vn) s1 LIMIT 20)' ) : (),
   );
 
   my @join = (
-    $o{rev} ?
-      'JOIN vn v ON v.id = vr.vid' :
-      'JOIN vn v ON vr.id = v.latest',
-    $o{rev} || $o{what} =~ /changes/ ?
-      'JOIN changes c ON c.id = vr.id' : (),
-    $o{what} =~ /changes/ ?
-      'JOIN users u ON u.id = c.requester' : (),
     $o{what} =~ /relgraph/ ?
       'JOIN relgraphs vg ON vg.id = v.rgraph' : (),
     $uid && $o{what} =~ /wishlist/ ?
@@ -92,8 +80,7 @@ sub dbVNGet {
        SELECT irv.vid, COUNT(*) AS userlist_all,
               SUM(CASE WHEN irl.status = 2 THEN 1 ELSE 0 END) AS userlist_obtained
          FROM rlists irl
-         JOIN releases ir     ON irl.rid = ir.id
-         JOIN releases_vn irv ON irv.rid = ir.latest
+         JOIN releases_vn irv ON irv.id = irl.rid
         WHERE irl.uid = $uid
         GROUP BY irv.vid
      ) AS vnlist ON vnlist.vid = v.id") : (),
@@ -101,11 +88,9 @@ sub dbVNGet {
 
   my $tag_ids = $o{tag_inc} && join ',', ref $o{tag_inc} ? @{$o{tag_inc}} : $o{tag_inc};
   my @select = ( # see https://rt.cpan.org/Ticket/Display.html?id=54224 for the cast on c_languages and c_platforms
-    qw|v.id v.locked v.hidden v.c_released v.c_languages::text[] v.c_platforms::text[] vr.title vr.original v.rgraph|, 'vr.id AS cid',
+    qw|v.id v.locked v.hidden v.c_released v.c_languages::text[] v.c_platforms::text[] v.title v.original v.rgraph|,
     $o{what} =~ /extended/ ? (
-      qw|vr.alias vr.image vr.img_nsfw vr.length vr.desc vr.l_wp vr.l_encubed vr.l_renai| ) : (),
-    $o{what} =~ /changes/ ? (
-      qw|c.requester c.comments v.latest u.username c.rev c.ihid c.ilock|, q|extract('epoch' from c.added) as added|) : (),
+      qw|v.alias v.image v.img_nsfw v.length v.desc v.l_wp v.l_encubed v.l_renai| ) : (),
     $o{what} =~ /relgraph/ ? 'vg.svg' : (),
     $o{what} =~ /rating/ ? (qw|v.c_popularity v.c_rating v.c_votecount|) : (),
     $o{what} =~ /ranking/ ? (
@@ -120,100 +105,129 @@ sub dbVNGet {
   );
 
   my $order = sprintf {
-    id       => 'id %s',
-    rel      => 'c_released %s, title ASC',
-    pop      => 'c_popularity %s NULLS LAST',
-    rating   => 'c_rating %s NULLS LAST',
-    title    => 'title %s',
+    id       => 'v.id %s',
+    rel      => 'v.c_released %s, v.title ASC',
+    pop      => 'v.c_popularity %s NULLS LAST',
+    rating   => 'v.c_rating %s NULLS LAST',
+    title    => 'v.title %s',
     tagscore => 'tagscore %s',
     rand     => 'RANDOM()',
   }->{$o{sort}}, $o{reverse} ? 'DESC' : 'ASC';
 
   my($r, $np) = $self->dbPage(\%o, q|
     SELECT !s
-      FROM vn_rev vr
+      FROM vn v
       !s
       !W
       ORDER BY !s|,
     join(', ', @select), join(' ', @join), \@where, $order,
   );
 
-  if($o{what} =~ /relgraph/) {
-    $_->{svg} = decode_utf8($_->{svg}) for @$r;
-  }
+  $_->{svg} && ($_->{svg} = decode_utf8($_->{svg})) for (@$r);
+  return _enrich($self, $r, $np, 0, $o{what});
+}
+
+
+sub dbVNGetRev {
+  my $self = shift;
+  my %o = (what => '', @_);
+
+  $o{rev} ||= $self->dbRow('SELECT MAX(rev) AS rev FROM changes WHERE type = \'v\' AND itemid = ?', $o{id})->{rev};
 
-  if(@$r && $o{what} =~ /anime|relations|screenshots|credits/) {
+  # XXX: Too much duplication with code in dbVNGet() here. Can we combine some code here?
+  my $uid = $self->authInfo->{id};
+
+  my $select = 'c.itemid AS id, vo.c_released, vo.c_languages::text[], vo.c_platforms::text[], v.title, v.original, vo.rgraph';
+  $select .= ', extract(\'epoch\' from c.added) as added, c.requester, c.comments, u.username, c.rev, c.ihid, c.ilock';
+  $select .= ', c.id AS cid, NOT EXISTS(SELECT 1 FROM changes c2 WHERE c2.type = c.type AND c2.itemid = c.itemid AND c2.rev = c.rev+1) AS lastrev';
+  $select .= ', v.alias, v.image, v.img_nsfw, v.length, v.desc, v.l_wp, v.l_encubed, v.l_renai, vo.hidden, vo.locked' if $o{what} =~ /extended/;
+  $select .= ', vo.c_popularity, vo.c_rating, vo.c_votecount' if $o{what} =~ /rating/;
+  $select .= ', (SELECT COUNT(*)+1 FROM vn iv WHERE iv.hidden = false AND iv.c_popularity > COALESCE(vo.c_popularity, 0.0)) AS p_ranking'
+            .', (SELECT COUNT(*)+1 FROM vn iv WHERE iv.hidden = false AND iv.c_rating > COALESCE(vo.c_rating, 0.0)) AS r_ranking' if $o{what} =~ /ranking/;
+
+  my $r = $self->dbAll(q|
+    SELECT !s
+      FROM changes c
+      JOIN vn vo ON vo.id = c.itemid
+      JOIN vn_hist v ON v.chid = c.id
+      JOIN users u ON u.id = c.requester
+      WHERE c.type = 'v' AND c.itemid = ? AND c.rev = ?|,
+    $select, $o{id}, $o{rev}
+  );
+
+  return _enrich($self, $r, 0, 1, $o{what});
+}
+
+
+sub _enrich {
+  my($self, $r, $np, $rev, $what) = @_;
+
+  if(@$r && $what =~ /anime|relations|screenshots|credits/) {
+    my($col, $hist, $colname) = $rev ? ('cid', '_hist', 'chid') : ('id', '', 'id');
     my %r = map {
       $r->[$_]{anime} = [];
       $r->[$_]{credits} = [];
       $r->[$_]{seiyuu} = [];
       $r->[$_]{relations} = [];
       $r->[$_]{screenshots} = [];
-      ($r->[$_]{cid}, $_)
+      ($r->[$_]{$col}, $_)
     } 0..$#$r;
 
-    if($o{what} =~ /credits/) {
-      push(@{$r->[$r{ delete $_->{vid} }]{credits}}, $_) for (@{$self->dbAll(q|
-        SELECT vs.vid, s.id, vs.aid, sa.name, sa.original, sr.gender, sr.lang, vs.role, vs.note
-          FROM vn_staff vs
-          JOIN staff_alias sa ON vs.aid = sa.id
-          JOIN staff_rev sr ON sr.id = sa.rid
-          JOIN staff s ON sr.id = s.latest
-          WHERE s.hidden = FALSE AND vs.vid IN(!l)
-          ORDER BY vs.role ASC, sa.name ASC|,
+    if($what =~ /credits/) {
+      push(@{$r->[$r{ delete $_->{xid} }]{credits}}, $_) for (@{$self->dbAll("
+        SELECT vs.$colname AS xid, s.id, vs.aid, sa.name, sa.original, s.gender, s.lang, vs.role, vs.note
+          FROM vn_staff$hist vs
+          JOIN staff_alias sa ON vs.aid = sa.aid
+          JOIN staff s ON s.id = sa.id
+          WHERE s.hidden = FALSE AND vs.$colname IN(!l)
+          ORDER BY vs.role ASC, sa.name ASC",
         [ keys %r ]
       )});
-      push(@{$r->[$r{ delete $_->{vid} }]{seiyuu}}, $_) for (@{$self->dbAll(q|
-        SELECT vs.vid, s.id, vs.aid, sa.name, sa.original, sr.gender, sr.lang, cr.cid, cr.name AS cname, vs.note,
-            (SELECT MAX(spoil) FROM chars_vns cv WHERE cv.vid = vr.vid AND cv.cid = cr.id) AS spoil
-          FROM vn_seiyuu vs
-          JOIN vn_rev vr ON vr.id = vs.vid
-          JOIN staff_alias sa ON vs.aid = sa.id
-          JOIN staff_rev sr ON sr.id = sa.rid
-          JOIN staff s ON s.id = sr.sid AND sr.id = s.latest
+
+      # The seiyuu query needs the VN id to get the VN<->Char spoiler level.
+      # Obtaining this ID is different when using the hist table.
+      my($vid, $join) = $rev ? ('h.itemid', 'JOIN changes h ON h.id = vs.chid') : ('vs.id', '');
+      push(@{$r->[$r{ delete $_->{xid} }]{seiyuu}}, $_) for (@{$self->dbAll("
+        SELECT vs.$colname AS xid, s.id, vs.aid, sa.name, sa.original, s.gender, s.lang, c.id AS cid, c.name AS cname, vs.note,
+            (SELECT MAX(spoil) FROM chars_vns cv WHERE cv.vid = $vid AND cv.id = c.id) AS spoil
+          FROM vn_seiyuu$hist vs
+          JOIN staff_alias sa ON vs.aid = sa.aid
+          JOIN staff s ON s.id = sa.id
           JOIN chars c ON c.id = vs.cid
-          JOIN chars_rev cr ON cr.id = c.latest
-          WHERE s.hidden = FALSE AND vs.vid IN(!l)
-            AND cr.id IN(SELECT cv.cid FROM chars_vns cv WHERE cv.vid = vr.vid AND cv.cid = cr.id)
-          ORDER BY cr.name|,
+          $join
+          WHERE s.hidden = FALSE AND vs.$colname IN(!l)
+          ORDER BY c.name",
         [ keys %r ]
       )});
     }
 
-    if($o{what} =~ /anime/) {
-      push(@{$r->[$r{$_->{vid}}]{anime}}, $_) && delete $_->{vid} for (@{$self->dbAll(q|
-        SELECT va.vid, a.id, a.year, a.ann_id, a.nfo_id, a.type, a.title_romaji, a.title_kanji, extract('epoch' from a.lastfetch) AS lastfetch
-          FROM vn_anime va
+    if($what =~ /anime/) {
+      push(@{$r->[$r{ delete $_->{xid} }]{anime}}, $_) for (@{$self->dbAll("
+        SELECT va.$colname AS xid, a.id, a.year, a.ann_id, a.nfo_id, a.type, a.title_romaji, a.title_kanji, extract('epoch' from a.lastfetch) AS lastfetch
+          FROM vn_anime$hist va
           JOIN anime a ON va.aid = a.id
-          WHERE va.vid IN(!l)|,
+          WHERE va.$colname IN(!l)",
         [ keys %r ]
       )});
     }
 
-    if($o{what} =~ /relations/) {
-      push(@{$r->[$r{$_->{vid1}}]{relations}}, {
-        relation => $_->{relation},
-        official => $_->{official},
-        id => $_->{vid2},
-        title => $_->{title},
-        original => $_->{original},
-      }) for(@{$self->dbAll(q|
-        SELECT rel.vid1, rel.vid2, rel.relation, rel.official, vr.title, vr.original
-          FROM vn_relations rel
-          JOIN vn v ON rel.vid2 = v.id
-          JOIN vn_rev vr ON v.latest = vr.id
-          WHERE rel.vid1 IN(!l)|,
+    if($what =~ /relations/) {
+      push(@{$r->[$r{ delete $_->{xid} }]{relations}}, $_) for(@{$self->dbAll("
+        SELECT rel.$colname AS xid, rel.vid AS id, rel.relation, rel.official, v.title, v.original
+          FROM vn_relations$hist rel
+          JOIN vn v ON rel.vid = v.id
+          WHERE rel.$colname IN(!l)",
         [ keys %r ]
       )});
     }
 
-    if($o{what} =~ /screenshots/) {
-      push(@{$r->[$r{$_->{vid}}]{screenshots}}, $_) && delete $_->{vid} for (@{$self->dbAll(q|
-        SELECT vs.vid, s.id, vs.nsfw, vs.rid, s.width, s.height
-          FROM vn_screenshots vs
+    if($what =~ /screenshots/) {
+      push(@{$r->[$r{ delete $_->{xid} }]{screenshots}}, $_) for (@{$self->dbAll("
+        SELECT vs.$colname AS xid, s.id, vs.nsfw, vs.rid, s.width, s.height
+          FROM vn_screenshots$hist vs
           JOIN screenshots s ON vs.scr = s.id
-          WHERE vs.vid IN(!l)
-          ORDER BY vs.scr|,
+          WHERE vs.$colname IN(!l)
+          ORDER BY vs.scr",
         [ keys %r ]
       )});
     }
@@ -296,41 +310,36 @@ sub dbScreenshotGet {
 sub dbScreenshotRandom {
   my($self, @vids) = @_;
   return $self->dbAll(q|
-    SELECT s.id AS scr, s.width, s.height, vr.vid, vr.title
+    SELECT s.id AS scr, s.width, s.height, v.id AS vid, v.title
       FROM screenshots s
       JOIN vn_screenshots vs ON vs.scr = s.id
-      JOIN vn_rev vr ON vr.id = vs.vid
-      JOIN vn v ON v.id = vr.vid AND v.latest = vs.vid
+      JOIN vn v ON v.id = vs.id
      WHERE NOT v.hidden AND NOT vs.nsfw
        AND s.id IN(
          SELECT floor(random() * last_value)::integer
-           FROM generate_series(1,20), (SELECT last_value FROM screenshots_id_seq) s1
+           FROM generate_series(1,20), (SELECT MAX(id) AS last_value FROM screenshots) s1
           LIMIT 20
        )
      LIMIT 4|
   ) if !@vids;
   # this query is faster than it looks
   return $self->dbAll(join(' UNION ALL ', map
-    q|SELECT s.id AS scr, s.width, s.height, vr.vid, vr.title, RANDOM() AS position
-        FROM vn v
-        JOIN vn_rev vr ON vr.id = v.latest
-        JOIN vn_screenshots vs ON vs.vid = v.latest
+    q|SELECT s.id AS scr, s.width, s.height, v.id AS vid, v.title, RANDOM() AS position
+        FROM (
+         SELECT vs2.id, vs2.scr FROM vn_screenshots vs2
+          WHERE vs2.id = ? AND NOT vs2.nsfw
+          ORDER BY RANDOM() LIMIT 1
+        ) vs
+        JOIN vn v ON v.id = vs.id
         JOIN screenshots s ON s.id = vs.scr
-       WHERE v.id = ? AND s.id = (
-         SELECT vs2.scr
-          FROM vn_screenshots vs2
-          JOIN vn v2 ON v2.latest = vs2.vid
-         WHERE v2.id = v.id AND NOT vs2.nsfw
-         ORDER BY RANDOM()
-         LIMIT 1
-       )|, @vids).' ORDER BY position', @vids);
+     |, @vids).' ORDER BY position', @vids);
 }
 
 
 sub dbVNHasChar {
   my($self, $vid) = @_;
   return $self->dbRow(
-    'SELECT 1 AS exists FROM chars c JOIN chars_vns cv ON c.latest = cv.cid WHERE cv.vid = ? AND NOT c.hidden', $vid
+    'SELECT 1 AS exists FROM chars c JOIN chars_vns cv ON c.id = cv.id WHERE cv.vid = ? AND NOT c.hidden', $vid
   )->{exists};
 }
 
@@ -338,7 +347,7 @@ sub dbVNHasChar {
 sub dbVNHasStaff {
   my($self, $vid) = @_;
   return $self->dbRow(
-    'SELECT 1 AS exists FROM vn_staff vs FULL OUTER JOIN vn_seiyuu vsy ON vs.vid = vsy.vid JOIN vn v ON v.latest = vs.vid OR v.latest = vsy.vid WHERE v.id = ?', $vid
+    'SELECT 1 AS exists FROM vn_staff WHERE id = ? UNION ALL SELECT 1 FROM vn_seiyuu WHERE id = ? LIMIT 1', $vid, $vid
   )->{exists};
 }
 
@@ -347,16 +356,12 @@ sub dbVNHasStaff {
 sub dbVNImportSeiyuu {
   my($self, $vid, $cids) = @_;
   return $self->dbAll(q|
-    SELECT DISTINCT ON(cr.cid) cr.cid, cr.name AS c_name, s.id AS sid, sa.id AS aid, sa.name
+    SELECT DISTINCT ON(c.id) c.id AS cid, c.name AS c_name, sa.id AS sid, sa.aid, sa.name
       FROM vn_seiyuu vs
-      JOIN vn v ON v.latest = vs.vid
       JOIN chars c ON c.id = vs.cid
-      JOIN chars_rev cr ON cr.id = c.latest
-      JOIN staff_alias sa ON sa.id = vs.aid
-      JOIN staff s ON sa.rid = s.latest
-      WHERE vs.cid IN(!l) AND v.id <> ?|, $cids, $vid);
+      JOIN staff_alias sa ON sa.aid = vs.aid
+      WHERE vs.cid IN(!l) AND vs.id <> ?|, $cids, $vid);
 }
 
 
 1;
-