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.

9 files changed, 83 insertions, 69 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
 };
 
diff --git a/util/sql/func.sql b/util/sql/func.sql
index 8004b2e8..5bb10661 100644
--- a/util/sql/func.sql
+++ b/util/sql/func.sql
@@ -136,6 +136,25 @@ $$ LANGUAGE SQL;
 
 
 
+-- Update users.c_vns, c_votes and c_wish for one user (when given an id) or all users (when given NULL)
+CREATE OR REPLACE FUNCTION update_users_ulist_stats(integer) RETURNS void AS $$
+BEGIN
+  WITH cnt(uid, votes, vns, wish) AS (
+      SELECT u.id
+         , COUNT(*) FILTER (WHERE ul.id = 7) -- Voted
+         , COUNT(DISTINCT uvl.vid) FILTER (WHERE ul.id NOT IN(5,6)) -- Labelled, but not wishlish/blacklist
+         , COUNT(DISTINCT uvl.vid) FILTER (WHERE ul.id = 5) -- Wishlist
+      FROM users u
+      LEFT JOIN ulist_vns_labels uvl ON uvl.uid = u.id
+      LEFT JOIN ulist_labels ul ON ul.id = uvl.lbl AND ul.uid = u.id AND NOT ul.private
+     WHERE $1 IS NULL OR u.id = $1
+     GROUP BY u.id
+  ) UPDATE users SET c_votes = votes, c_vns = vns, c_wish = wish FROM cnt WHERE id = uid;
+END;
+$$ LANGUAGE plpgsql; -- Don't use "LANGUAGE SQL" here; Make sure to generate a new query plan at invocation time.
+
+
+
 -- Recalculate tags_vn_inherit.
 -- When a vid is given, only the tags for that vid will be updated. These
 -- incremental updates do not affect tags.c_items, so that may still get
@@ -444,16 +463,10 @@ $$ LANGUAGE plpgsql;
 ----------------------------------------------------------
 
 
--- keep the c_* columns in the users table up to date
+-- keep the c_tags and c_changes columns in the users table up to date
 CREATE OR REPLACE FUNCTION update_users_cache() RETURNS TRIGGER AS $$
 BEGIN
-  IF TG_TABLE_NAME = 'votes' THEN
-    IF TG_OP = 'INSERT' THEN
-      UPDATE users SET c_votes = c_votes + 1 WHERE id = NEW.uid;
-    ELSE
-      UPDATE users SET c_votes = c_votes - 1 WHERE id = OLD.uid;
-    END IF;
-  ELSIF TG_TABLE_NAME = 'changes' THEN
+  IF TG_TABLE_NAME = 'changes' THEN
     IF TG_OP = 'INSERT' THEN
       UPDATE users SET c_changes = c_changes + 1 WHERE id = NEW.requester;
     ELSE
@@ -573,7 +586,6 @@ END;
 $$ LANGUAGE plpgsql;
 
 
-
 -- Create ulist labels for new users.
 CREATE OR REPLACE FUNCTION ulist_labels_create() RETURNS trigger AS $$
 BEGIN
diff --git a/util/sql/perms.sql b/util/sql/perms.sql
index 34a6550e..358d8bc0 100644
--- a/util/sql/perms.sql
+++ b/util/sql/perms.sql
@@ -67,9 +67,9 @@ GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_vns_labels         TO vndb_site;
 
 -- users table is special; The 'perm', 'passwd' and 'mail' columns are
 -- protected and can only be accessed through the user_* functions.
-GRANT SELECT (id, username,       registered, perm, c_votes, c_changes, ip, c_tags, ign_votes, email_confirmed, skin, customcss, filter_vn, filter_release, show_nsfw, hide_list, notify_dbedit, notify_announce, vn_list_own, vn_list_wish, tags_all, tags_cont, tags_ero, tags_tech, spoilers, traits_sexual, nodistract_can, nodistract_noads, nodistract_nofancy, support_can, support_enabled, uniname_can, uniname, pubskin_can, pubskin_enabled),
-      INSERT (id, username, mail, registered,       c_votes, c_changes, ip, c_tags, ign_votes, email_confirmed, skin, customcss, filter_vn, filter_release, show_nsfw, hide_list, notify_dbedit, notify_announce, vn_list_own, vn_list_wish, tags_all, tags_cont, tags_ero, tags_tech, spoilers, traits_sexual, nodistract_can, nodistract_noads, nodistract_nofancy, support_can, support_enabled, uniname_can, uniname, pubskin_can, pubskin_enabled),
-      UPDATE (    username,       registered,       c_votes, c_changes, ip, c_tags, ign_votes, email_confirmed, skin, customcss, filter_vn, filter_release, show_nsfw, hide_list, notify_dbedit, notify_announce, vn_list_own, vn_list_wish, tags_all, tags_cont, tags_ero, tags_tech, spoilers, traits_sexual, nodistract_can, nodistract_noads, nodistract_nofancy, support_can, support_enabled, uniname_can, uniname, pubskin_can, pubskin_enabled) ON users TO vndb_site;
+GRANT SELECT (id, username,       registered, perm, c_votes, c_changes, ip, c_tags, ign_votes, email_confirmed, skin, customcss, filter_vn, filter_release, show_nsfw, hide_list, notify_dbedit, notify_announce, vn_list_own, vn_list_wish, tags_all, tags_cont, tags_ero, tags_tech, spoilers, traits_sexual, nodistract_can, nodistract_noads, nodistract_nofancy, support_can, support_enabled, uniname_can, uniname, pubskin_can, pubskin_enabled, c_vns, c_wish),
+      INSERT (id, username, mail, registered,       c_votes, c_changes, ip, c_tags, ign_votes, email_confirmed, skin, customcss, filter_vn, filter_release, show_nsfw, hide_list, notify_dbedit, notify_announce, vn_list_own, vn_list_wish, tags_all, tags_cont, tags_ero, tags_tech, spoilers, traits_sexual, nodistract_can, nodistract_noads, nodistract_nofancy, support_can, support_enabled, uniname_can, uniname, pubskin_can, pubskin_enabled, c_vns, c_wish),
+      UPDATE (    username,       registered,       c_votes, c_changes, ip, c_tags, ign_votes, email_confirmed, skin, customcss, filter_vn, filter_release, show_nsfw, hide_list, notify_dbedit, notify_announce, vn_list_own, vn_list_wish, tags_all, tags_cont, tags_ero, tags_tech, spoilers, traits_sexual, nodistract_can, nodistract_noads, nodistract_nofancy, support_can, support_enabled, uniname_can, uniname, pubskin_can, pubskin_enabled, c_vns, c_wish) ON users TO vndb_site;
 
 GRANT SELECT, INSERT, UPDATE         ON vn                       TO vndb_site;
 GRANT SELECT, INSERT,         DELETE ON vn_anime                 TO vndb_site;
@@ -151,8 +151,8 @@ GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_labels             TO vndb_multi;
 GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_vns                TO vndb_multi;
 GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_vns_labels         TO vndb_multi;
 
-GRANT SELECT (id, username, registered, c_votes, c_changes, c_tags, ign_votes, email_confirmed, hide_list, notify_dbedit, notify_announce),
-      UPDATE (                          c_votes, c_changes, c_tags) ON users TO vndb_multi;
+GRANT SELECT (id, username, registered, c_votes, c_changes, c_tags, c_vns, c_wish, ign_votes, email_confirmed, hide_list, notify_dbedit, notify_announce),
+      UPDATE (                          c_votes, c_changes, c_tags, c_vns, c_wish) ON users TO vndb_multi;
 GRANT                         DELETE ON users                    TO vndb_multi;
 
 GRANT SELECT,         UPDATE         ON vn                       TO vndb_multi;
diff --git a/util/sql/schema.sql b/util/sql/schema.sql
index b2bc3d04..93c5953f 100644
--- a/util/sql/schema.sql
+++ b/util/sql/schema.sql
@@ -779,7 +779,9 @@ CREATE TABLE users (
   uniname_can     boolean NOT NULL DEFAULT FALSE,
   uniname         text NOT NULL DEFAULT '',
   pubskin_can     boolean NOT NULL DEFAULT FALSE,
-  pubskin_enabled boolean NOT NULL DEFAULT FALSE
+  pubskin_enabled boolean NOT NULL DEFAULT FALSE,
+  c_vns           integer NOT NULL DEFAULT 0,
+  c_wish          integer NOT NULL DEFAULT 0
 );
 
 -- vn
diff --git a/util/sql/tableattrs.sql b/util/sql/tableattrs.sql
index 57e0416a..a63b2fcd 100644
--- a/util/sql/tableattrs.sql
+++ b/util/sql/tableattrs.sql
@@ -140,7 +140,6 @@ CREATE UNIQUE INDEX chars_vns_hist_pkey    ON chars_vns_hist (chid, vid, COALESC
 -- Triggers
 
 CREATE TRIGGER users_changes_update       AFTER  INSERT OR DELETE ON changes       FOR EACH ROW EXECUTE PROCEDURE update_users_cache();
-CREATE TRIGGER users_votes_update         AFTER  INSERT OR DELETE ON votes         FOR EACH ROW EXECUTE PROCEDURE update_users_cache();
 CREATE TRIGGER users_tags_update          AFTER  INSERT OR DELETE ON tags_vn       FOR EACH ROW EXECUTE PROCEDURE update_users_cache();
 
 CREATE TRIGGER stats_cache_new            AFTER  INSERT           ON vn            FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache();
diff --git a/util/updates/update_wip_lists.sql b/util/updates/update_wip_lists.sql
index b472cfc9..a327f854 100644
--- a/util/updates/update_wip_lists.sql
+++ b/util/updates/update_wip_lists.sql
@@ -116,17 +116,16 @@ COMMIT;
 DROP FUNCTION update_vnpopularity();
 
 \i util/sql/func.sql
+\i util/sql/perms.sql
+
+DROP TRIGGER users_votes_update ON votes;
 
 CREATE TRIGGER ulist_labels_create AFTER INSERT ON users FOR EACH ROW EXECUTE PROCEDURE ulist_labels_create();
 CREATE TRIGGER ulist_voted_label AFTER INSERT OR UPDATE ON ulist_vns FOR EACH ROW EXECUTE PROCEDURE ulist_voted_label();
 CREATE CONSTRAINT TRIGGER update_vnlist_rlist AFTER DELETE ON ulist_vns DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE update_vnlist_rlist();
 
+ALTER TABLE users ADD COLUMN c_vns  integer NOT NULL DEFAULT 0;
+ALTER TABLE users ADD COLUMN c_wish integer NOT NULL DEFAULT 0;
 
-
-GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_vns                TO vndb_site;
-GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_labels             TO vndb_site;
-GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_vns_labels         TO vndb_site;
-
-GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_vns                TO vndb_multi;
-GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_labels             TO vndb_multi;
-GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_vns_labels         TO vndb_multi;
+\timing
+SELECT update_users_ulist_stats(NULL);