SQL: Fix all browsing queries to use the new schema

This basically makes VNDB browsable again, but editing entries is still
broken.

I split off the get-old-revision functionality from the db*Get() methods
into db*GetRev(). This split makes sense even with the old SQL schema:
db*Get() had to special-case some joins/filters when fetching an older
revision, and none of the other filters would work in that case. This
split does cause some code duplication in that all db*GetRev() methods
look very much alike, and that the columns they fetch is almost
identical to the db*Get() methods. Not sure yet how to avoid the
duplication elegantly.

I didn't do a whole lot of query optimization yet (most issues require
extra indices, I'll investigate later which indices will make a big
difference), but I did fix some low hanging fruit whenever I encountered
something.

I don't think I've worsened anything, performance-wise.

1 files changed, 8 insertions, 8 deletions

diff --git a/lib/VNDB/Util/CommonHTML.pm b/lib/VNDB/Util/CommonHTML.pm
index 7b3fa673..bcc2ea97 100644
--- a/lib/VNDB/Util/CommonHTML.pm
+++ b/lib/VNDB/Util/CommonHTML.pm
@@ -133,12 +133,12 @@ sub htmlHiddenMessage {
   my($self, $type, $obj) = @_;
   return 0 if !$obj->{hidden};
   my $board = $type =~ /[cs]/ ? 'db' : $type eq 'r' ? 'v'.$obj->{vn}[0]{vid} : $type.$obj->{id};
-  # fetch edit summary (not present in $obj because the changes aren't fetched)
-  my $editsum = $type eq 'v' ? $self->dbVNGet(id => $obj->{id}, what => 'changes')->[0]{comments}
-              : $type eq 'r' ? $self->dbReleaseGet(id => $obj->{id}, what => 'changes')->[0]{comments}
-              : $type eq 'c' ? $self->dbCharGet(id => $obj->{id}, what => 'changes')->[0]{comments}
-              : $type eq 's' ? $self->dbStaffGet(id => $obj->{id}, what => 'changes')->[0]{comments}
-                             : $self->dbProducerGet(id => $obj->{id}, what => 'changes')->[0]{comments};
+  # fetch edit summary (not present in $obj, requires the db*GetRev() methods)
+  my $editsum = $type eq 'v' ? $self->dbVNGetRev(id => $obj->{id})->[0]{comments}
+              : $type eq 'r' ? $self->dbReleaseGetRev(id => $obj->{id})->[0]{comments}
+              : $type eq 'c' ? $self->dbCharGetRev(id => $obj->{id})->[0]{comments}
+              : $type eq 's' ? $self->dbStaffGetRev(id => $obj->{id})->[0]{comments}
+                             : $self->dbProducerGetRev(id => $obj->{id})->[0]{comments};
   div class => 'mainbox';
    h1 $obj->{title}||$obj->{name};
    div class => 'warning';
@@ -183,7 +183,7 @@ sub htmlRevision {
    a class => 'prev', href => sprintf('/%s%d.%d', $type, $new->{id}, $new->{rev}-1), '<- '.mt '_revision_previous'
      if $new->{rev} > 1;
    a class => 'next', href => sprintf('/%s%d.%d', $type, $new->{id}, $new->{rev}+1), mt('_revision_next').' ->'
-     if $new->{cid} != $new->{latest};
+     if !$new->{lastrev};
    p class => 'center';
     a href => "/$type$new->{id}", "$type$new->{id}";
    end;
@@ -310,7 +310,7 @@ sub htmlEditMessage {
      }
     end;
    end;
-   if($obj && $obj->{latest} != $obj->{cid}) {
+   if($obj && !$obj->{lastrev}) {
      div class => 'warning';
       h2 mt '_editmsg_revert_title';
       p mt '_editmsg_revert_msg', $num;