diff options
-rw-r--r-- | lib/Multi/API.pm | 14 | ||||
-rw-r--r-- | lib/Multi/Maintenance.pm | 27 | ||||
-rw-r--r-- | lib/VNWeb/User/List.pm | 37 | ||||
-rw-r--r-- | lib/VNWeb/User/Lists.pm | 14 | ||||
-rw-r--r-- | util/sql/func.sql | 30 | ||||
-rw-r--r-- | util/sql/perms.sql | 10 | ||||
-rw-r--r-- | util/sql/schema.sql | 4 | ||||
-rw-r--r-- | util/sql/tableattrs.sql | 1 | ||||
-rw-r--r-- | util/updates/update_wip_lists.sql | 15 |
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); |