ulist: Update user list with vnlist/votes/wishlist stats

This adds the users.c_vns and c_wish columns and a function to update
the cache.

Unlike my previous cache update approaches, I did not use SQL triggers
here, as that seemed more complex and less efficient than updating the
cache manually. That's not to say that I'm happy with the current
approach, but meh...

The cache update function is not automatically run for all users, but
that could be added to Multi::Maintenance if it turns out that the
cached values will not be updated properly in some cases.

4 files changed, 47 insertions, 45 deletions

diff --git a/lib/Multi/API.pm b/lib/Multi/API.pm
index 5cb68c1b..3eae8e6b 100644
--- a/lib/Multi/API.pm
+++ b/lib/Multi/API.pm
@@ -1481,11 +1481,17 @@ sub set_wishlist {
 }
 
 
+sub set_ulist_ret {
+  my($c, $obj) = @_;
+  setpg $obj, 'SELECT update_users_ulist_stats($1)', [ $c->{uid} ]; # XXX: This can be deferred, to speed up batch updates over the same connection
+}
+
 sub set_ulist {
   my($c, $obj) = @_;
 
-  return setpg $obj, 'DELETE FROM ulist_vns WHERE uid = $1 AND vid = $2',
-    [ $c->{uid}, $obj->{id} ] if !$obj->{opt};
+  return cpg $c, 'DELETE FROM ulist_vns WHERE uid = $1 AND vid = $2', [ $c->{uid}, $obj->{id} ], sub {
+    set_ulist_ret $c, $obj;
+  } if !$obj->{opt};
 
   my $opt = $obj->{opt};
   my @set;
@@ -1533,7 +1539,9 @@ sub set_ulist {
   return cerr $c, missing => 'No fields to change' if !@set;
 
   cpg $c, 'INSERT INTO ulist_vns (uid, vid) VALUES ($1, $2) ON CONFLICT (uid, vid) DO NOTHING', [ $c->{uid}, $obj->{id} ], sub {
-    setpg $obj, 'UPDATE ulist_vns SET '.join(',', @set).' WHERE uid = $1 AND vid = $2', \@bind;
+    cpg $c, 'UPDATE ulist_vns SET '.join(',', @set).' WHERE uid = $1 AND vid = $2', \@bind, sub {
+      set_ulist_ret $c, $obj;
+    }
   };
 }
 
diff --git a/lib/Multi/Maintenance.pm b/lib/Multi/Maintenance.pm
index 846abcd0..abed87a6 100644
--- a/lib/Multi/Maintenance.pm
+++ b/lib/Multi/Maintenance.pm
@@ -204,30 +204,3 @@ sub vnsearch_update { # id, res, time
 
 
 1;
-
-__END__
-
-# Shouldn't really be necessary, except c_changes could be slightly off when
-# hiding/unhiding DB items.
-# This query takes almost two hours to complete and tends to bring the entire
-# site down with it, so it's been disabled for now. Can be performed in
-# ranges though.
-UPDATE users SET
-  c_votes = COALESCE(
-    (SELECT COUNT(vid)
-    FROM votes
-    WHERE uid = users.id
-    GROUP BY uid
-  ), 0),
-  c_changes = COALESCE(
-    (SELECT COUNT(id)
-    FROM changes
-    WHERE requester = users.id
-    GROUP BY requester
-  ), 0),
-  c_tags = COALESCE(
-    (SELECT COUNT(tag)
-    FROM tags_vn
-    WHERE uid = users.id
-    GROUP BY uid
-  ), 0)
diff --git a/lib/VNWeb/User/List.pm b/lib/VNWeb/User/List.pm
index c3694ddc..a3245b4c 100644
--- a/lib/VNWeb/User/List.pm
+++ b/lib/VNWeb/User/List.pm
@@ -14,28 +14,33 @@ sub listing_ {
             thead_ sub { tr_ sub {
                 td_ class => 'tc1', sub { txt_ 'Username';   sortable_ 'username',   $opt, \&url };
                 td_ class => 'tc2', sub { txt_ 'Registered'; sortable_ 'registered', $opt, \&url };
-                td_ class => 'tc3', sub { txt_ 'Votes';      sortable_ 'votes',      $opt, \&url };
-                td_ class => 'tc4', sub { txt_ 'Edits';      sortable_ 'changes',    $opt, \&url };
-                td_ class => 'tc5', sub { txt_ 'Tags';       sortable_ 'tags',       $opt, \&url };
+                td_ class => 'tc3', sub { txt_ 'VNs';        sortable_ 'vns',        $opt, \&url };
+                td_ class => 'tc4', sub { txt_ 'Votes';      sortable_ 'votes',      $opt, \&url };
+                td_ class => 'tc5', sub { txt_ 'Wishlist';   sortable_ 'wish',       $opt, \&url };
+                td_ class => 'tc6', sub { txt_ 'Edits';      sortable_ 'changes',    $opt, \&url };
+                td_ class => 'tc7', sub { txt_ 'Tags';       sortable_ 'tags',       $opt, \&url };
             } };
             tr_ sub {
                 my $l = $_;
                 td_ class => 'tc1', sub { user_ $l };
                 td_ class => 'tc2', fmtdate $l->{registered};
-                td_ mkclass(tc3 => 1, linethrough => $l->{hide_list} && auth->permUsermod), sub {
-                    if($l->{hide_list} && !auth->permUsermod) {
-                        txt_ '-';
-                    } elsif(!$l->{c_votes}) {
-                        txt_ '0';
-                    } else {
-                        a_ href => "/u$l->{user_id}/votes", $l->{c_votes};
-                    }
+                td_ class => 'tc3', sub {
+                    txt_ '0' if !$l->{c_vns};
+                    a_ href => "/u$l->{user_id}/ulist?vnlist=1", $l->{c_vns} if $l->{c_vns};
                 };
                 td_ class => 'tc4', sub {
+                    txt_ '0' if !$l->{c_votes};
+                    a_ href => "/u$l->{user_id}/ulist?votes=1", $l->{c_votes} if $l->{c_votes};
+                };
+                td_ class => 'tc5', sub {
+                    txt_ '0' if !$l->{c_wish};
+                    a_ href => "/u$l->{user_id}/ulist?wishlist=1", $l->{c_wish} if $l->{c_wish};
+                };
+                td_ class => 'tc6', sub {
                     txt_ '-' if !$l->{c_changes};
                     a_ href => "/u$l->{user_id}/hist", $l->{c_changes} if $l->{c_changes};
                 };
-                td_ class => 'tc5', sub {
+                td_ class => 'tc7', sub {
                     txt_ '-' if !$l->{c_tags};
                     a_ href => "/g/links?u=$l->{user_id}", $l->{c_tags} if $l->{c_tags};
                 };
@@ -51,7 +56,7 @@ TUWF::get qr{/u/(?<char>[0a-z]|all)}, sub {
 
     my $opt = eval { tuwf->validate(get =>
         p => { upage => 1 },
-        s => { required => 0, default => 'registered', enum => [qw[username registered votes changes tags]] },
+        s => { required => 0, default => 'registered', enum => [qw[username registered vns votes wish changes tags]] },
         o => { required => 0, default => 'd',          enum => [qw[a d]] },
         q => { required => 0, default => '' },
     )->data } || return tuwf->resNotFound;
@@ -65,13 +70,15 @@ TUWF::get qr{/u/(?<char>[0a-z]|all)}, sub {
     );
 
     my $list = tuwf->dbPagei({ results => 50, page => $opt->{p} },
-        'SELECT', sql_user(), ',', sql_totime('registered'), 'as registered, c_votes, c_changes, c_tags, hide_list
+        'SELECT', sql_user(), ',', sql_totime('registered'), 'as registered, c_vns, c_votes, c_wish, c_changes, c_tags, hide_list
            FROM users u
           WHERE', sql_and('id > 0', @where),
          'ORDER BY', {
                   username   => 'username',
                   registered => 'id',
-                  votes      => auth->permUsermod ? 'c_votes' : 'hide_list, c_votes',
+                  vns        => 'c_vns',
+                  votes      => 'c_votes',
+                  wish       => 'c_wish',
                   changes    => 'c_changes',
                   tags       => 'c_tags'
                 }->{$opt->{s}}, $opt->{o} eq 'd' ? 'DESC' : 'ASC'
diff --git a/lib/VNWeb/User/Lists.pm b/lib/VNWeb/User/Lists.pm
index f44e4576..3146ef06 100644
--- a/lib/VNWeb/User/Lists.pm
+++ b/lib/VNWeb/User/Lists.pm
@@ -3,6 +3,12 @@ package VNWeb::User::Lists;
 use VNWeb::Prelude;
 
 
+# Should be called after any change to the ulist_* tables.
+# (Normally I'd do this with triggers, but that seemed like a more complex and less efficient solution in this case)
+sub updcache {
+    tuwf->dbExeci(SELECT => sql_func update_users_ulist_stats => \shift);
+}
+
 
 my $LABELS = form_compile any => {
     uid => { id => 1 },
@@ -63,6 +69,7 @@ json_api qr{/u/ulist/labels\.json}, $LABELS, sub {
     # (This will also delete all relevant vn<->label rows from ulist_vns_labels)
     tuwf->dbExeci('DELETE FROM ulist_labels WHERE uid =', \$uid, 'AND id IN', [ map $_->{id}, @delete ]) if @delete;
 
+    updcache $uid;
     elm_Success
 };
 
@@ -87,6 +94,8 @@ json_api qr{/u/ulist/setvote\.json}, $VNVOTE, sub {
              ', lastmod = NOW()
           WHERE uid =', \$data->{uid}, 'AND vid =', \$data->{vid}
     );
+
+    updcache $data->{uid};
     elm_Success
 };
 
@@ -122,6 +131,7 @@ json_api qr{/u/ulist/setlabel\.json}, $VNLABELS_IN, sub {
     ) if $data->{applied};
     tuwf->dbExeci('UPDATE ulist_vns SET lastmod = NOW() WHERE uid =', \$data->{uid}, 'AND vid =', \$data->{vid});
 
+    updcache $data->{uid};
     elm_Success
 };
 
@@ -144,6 +154,7 @@ json_api qr{/u/ulist/setdate\.json}, $VNDATE, sub {
         'UPDATE ulist_vns SET lastmod = NOW(), ', $data->{start} ? 'started' : 'finished', '=', \($data->{date}||undef),
          'WHERE uid =', \$data->{uid}, 'AND vid =', \$data->{vid}
     );
+    updcache $data->{uid};
     elm_Success
 };
 
@@ -186,6 +197,7 @@ json_api qr{/u/ulist/setnote\.json}, $VNNOTES, sub {
         'UPDATE ulist_vns SET lastmod = NOW(), notes = ', \$data->{notes},
          'WHERE uid =', \$data->{uid}, 'AND vid =', \$data->{vid}
     );
+    # Doesn't need `updcache()`
     elm_Success
 };
 
@@ -203,6 +215,7 @@ json_api qr{/u/ulist/del\.json}, $VNDEL, sub {
     my($data) = @_;
     return elm_Unauth if !auth || auth->uid != $data->{uid};
     tuwf->dbExeci('DELETE FROM ulist_vns WHERE uid =', \$data->{uid}, 'AND vid =', \$data->{vid});
+    updcache $data->{uid};
     elm_Success
 };
 
@@ -225,6 +238,7 @@ json_api qr{/u/ulist/rstatus\.json}, $RSTATUS, sub {
     } else {
         tuwf->dbExeci('INSERT INTO rlists', $data, 'ON CONFLICT (uid, rid) DO UPDATE SET status =', \$data->{status})
     }
+    # Doesn't need `updcache()`
     elm_Success
 };