ulist: Use new lists for VN vote stats & listing

To my surprise, I actually managed to achieve acceptable performance by
just adding two indices. I totally expected I'd have to keep a cache
column in ulist_vns whether the row is private or not. The partial index
on the users table in fact improves the performance of the vote graph
query. A covering index improves that even further, but that requires
Postgres 11+, which the Docker image doesn't have yet (and isn't all
that crucial anyway).

There's a rather annoying potential for confusion regarding the private
flag on votes. The user page & list stats only look at whether the
'Voted' label is private, whereas the VN stats use the "proper" approach
of checking for any public label. Not entirely sure which of the two is
more intuitive.

4 files changed, 90 insertions, 18 deletions

diff --git a/lib/VNDB/DB/ULists.pm b/lib/VNDB/DB/ULists.pm
index 6f061e97..37fd46d4 100644
--- a/lib/VNDB/DB/ULists.pm
+++ b/lib/VNDB/DB/ULists.pm
@@ -228,21 +228,19 @@ sub dbVoteGet {
 }
 
 
-# Arguments: (uid|vid), id, use_ignore_list
+# Arguments: 'vid', id
 # Returns an arrayref with 10 elements containing the [ count(vote), sum(vote) ]
 #   for votes in the range of ($index+0.5) .. ($index+1.4)
 sub dbVoteStats {
   my($self, $col, $id, $ign) = @_;
-  my $u = $self->authInfo->{id};
   my $r = [ map [0,0], 0..9 ];
   $r->[$_->{idx}] = [ $_->{votes}, $_->{total} ] for (@{$self->dbAll(q|
-    SELECT (vote::numeric/10)::int-1 AS idx, COUNT(vote) as votes, SUM(vote) AS total
-      FROM votes
-      !s
-      !W
-      GROUP BY (vote::numeric/10)::int|,
-    $ign ? 'JOIN users ON id = uid AND (NOT ign_votes'.($u?sprintf(' OR id = %d',$u):'').')' : '',
-    $col ? { '!s = ?' => [ $col, $id ] } : {},
+      SELECT (vote::numeric/10)::int-1 AS idx, COUNT(vote) as votes, SUM(vote) AS total
+        FROM ulist_vns uv
+       WHERE uv.vote IS NOT NULL AND NOT EXISTS(SELECT 1 FROM users u WHERE u.id = uv.uid AND u.ign_votes)
+         AND uv.vid = ?
+       GROUP BY (vote::numeric/10)::int|,
+    $id
   )});
   return $r;
 }
diff --git a/lib/VNDB/Handler/ULists.pm b/lib/VNDB/Handler/ULists.pm
index e42a41c0..c605182e 100644
--- a/lib/VNDB/Handler/ULists.pm
+++ b/lib/VNDB/Handler/ULists.pm
@@ -14,7 +14,7 @@ TUWF::register(
   qr{v([1-9]\d*)/list},  \&vnlist_e,
   qr{r([1-9]\d*)/list},  \&rlist_e,
   qr{xml/rlist.xml},     \&rlist_e,
-  qr{([uv])([1-9]\d*)/votes}, \&votelist,
+  qr{(u)([1-9]\d*)/votes}, \&votelist,
   qr{u([1-9]\d*)/wish},  \&wishlist,
   qr{u([1-9]\d*)/list},  \&vnlist,
 );
@@ -114,6 +114,7 @@ sub rlist_e {
 }
 
 
+# XXX: $type eq 'v' is not used anymore.
 sub votelist {
   my($self, $type, $id) = @_;
 
diff --git a/lib/VNDB/Util/CommonHTML.pm b/lib/VNDB/Util/CommonHTML.pm
index 9472f53d..7a3d554c 100644
--- a/lib/VNDB/Util/CommonHTML.pm
+++ b/lib/VNDB/Util/CommonHTML.pm
@@ -215,6 +215,7 @@ sub htmlItemMessage {
 
 
 # generates two tables, one with a vote graph, other with recent votes
+# Only supports $type eq 'v' now.
 sub htmlVoteStats {
   my($self, $type, $obj, $stats) = @_;
 
@@ -244,12 +245,17 @@ sub htmlVoteStats {
     }
    end 'table';
 
-   my $recent = $self->dbVoteGet(
-     $type.'id' => $obj->{id},
-     results => 8,
-     what => $type eq 'v' ? 'user hide_list' : 'vn',
-     hide_ign => $type eq 'v',
+   my $recent = $self->dbAlli('
+     SELECT uv.vote,', VNWeb::DB::sql_totime('uv.vote_date '), 'as date, ', VNWeb::DB::sql_user(), '
+          , NOT EXISTS(SELECT 1 FROM ulist_vns_labels uvl JOIN ulist_labels ul ON ul.uid = uvl.uid AND ul.id = uvl.lbl WHERE uvl.uid = uv.uid AND uvl.vid = uv.vid AND NOT ul.private) AS hide_list
+       FROM ulist_vns uv
+       JOIN users u ON u.id = uv.uid
+      WHERE uv.vid =', \$obj->{id}, 'AND uv.vote IS NOT NULL
+        AND NOT EXISTS(SELECT 1 FROM users u WHERE u.id = uv.uid AND u.ign_votes)
+      ORDER BY uv.vote_date DESC
+      LIMIT', \8
    );
+
    if(@$recent) {
      table class => 'recentvotes stripe';
       thead; Tr;
@@ -265,9 +271,7 @@ sub htmlVoteStats {
       for (@$recent) {
         Tr;
          td;
-          if($type eq 'u') {
-            a href => "/v$_->{vid}", title => $_->{original}||$_->{title}, shorten $_->{title}, 40;
-          } elsif($_->{hide_list}) {
+          if($_->{hide_list}) {
             b class => 'grayedout', 'hidden';
           } else {
             VNWeb::HTML::user_($_);
diff --git a/lib/VNWeb/VN/Votes.pm b/lib/VNWeb/VN/Votes.pm
new file mode 100644
index 00000000..1d4fe774
--- /dev/null
+++ b/lib/VNWeb/VN/Votes.pm
@@ -0,0 +1,69 @@
+package VNWeb::VN::Votes;
+
+use VNWeb::Prelude;
+
+
+sub listing_ {
+    my($opt, $count, $lst) = @_;
+
+    my sub url { '?'.query_encode %$opt, @_ }
+    paginate_ \&url, $opt->{p}, [ $count, 50 ], 't';
+    div_ class => 'mainbox browse votelist', sub {
+        table_ class => 'stripe', sub {
+            thead_ sub { tr_ sub {
+                td_ class => 'tc1', sub { txt_ 'Date'; sortable_ 'date',  $opt, \&url; debug_ $lst };
+                td_ class => 'tc2', sub { txt_ 'Vote'; sortable_ 'vote',  $opt, \&url; };
+                td_ class => 'tc3', sub { txt_ 'User'; sortable_ 'title', $opt, \&url; };
+            } };
+            tr_ sub {
+                td_ class => 'tc1', fmtdate $_->{date};
+                td_ class => 'tc2', fmtvote $_->{vote};
+                td_ class => 'tc3', sub {
+                    b_ class => 'grayedout', 'hidden' if $_->{hide_list};
+                    user_ $_ if !$_->{hide_list};
+                };
+            } for @$lst;
+        };
+    };
+    paginate_ \&url, $opt->{p}, [ $count, 50 ], 'b';
+}
+
+
+TUWF::get qr{/$RE{vid}/votes}, sub {
+    my $id = tuwf->capture('id');
+    my $v = tuwf->dbRowi('SELECT id, title, hidden AS entry_hidden, locked AS entry_locked FROM vn WHERE id =', \$id);
+    return tuwf->resNotFound if !$v->{id} || $v->{hidden};
+
+    my $opt = eval { tuwf->validate(get =>
+        p => { page => 1 },
+        o => { required => 0, default => 'd', enum => ['a','d'] },
+        s => { required => 0, default => 'date', enum => ['date', 'title', 'vote' ] }
+    )->data } || { p => 1, o => 'd', s => 'date' };
+
+    my $fromwhere = sql
+        'FROM ulist_vns uv
+         JOIN users u ON u.id = uv.uid
+        WHERE uv.vid =', \$v->{id}, 'AND uv.vote IS NOT NULL
+          AND NOT EXISTS(SELECT 1 FROM users u WHERE u.id = uv.uid AND u.ign_votes)';
+
+    my $count = tuwf->dbVali('SELECT COUNT(*)', $fromwhere);
+
+    my $lst = tuwf->dbPagei({results => 50, page => $opt->{p}},
+      'SELECT uv.vote,', sql_totime('uv.vote_date'), 'as date, ', sql_user(), '
+            , NOT EXISTS(SELECT 1 FROM ulist_vns_labels uvl JOIN ulist_labels ul ON ul.uid = uvl.uid AND ul.id = uvl.lbl WHERE uvl.uid = uv.uid AND uvl.vid = uv.vid AND NOT ul.private) AS hide_list
+        ', $fromwhere, 'ORDER BY', sprintf
+            { date => 'uv.vote_date %s', vote => 'uv.vote %s', title => '(CASE WHEN hide_list THEN NULL ELSE u.username END) %s, uv.vote_date' }->{$opt->{s}},
+            { a => 'ASC', d => 'DESC' }->{$opt->{o}}
+    );
+
+    framework_ title => "Votes for $v->{title}", type => 'v', dbobj => $v, sub {
+        div_ class => 'mainbox', sub {
+            h1_ "Votes for $v->{title}";
+            p_ 'No votes to list. :(' if !@$lst;
+        };
+        listing_ $opt, $count, $lst if @$lst;
+    };
+};
+
+
+1;