summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--lib/Multi/API.pm14
-rw-r--r--lib/Multi/Maintenance.pm27
-rw-r--r--lib/VNWeb/User/List.pm37
-rw-r--r--lib/VNWeb/User/Lists.pm14
-rw-r--r--util/sql/func.sql30
-rw-r--r--util/sql/perms.sql10
-rw-r--r--util/sql/schema.sql4
-rw-r--r--util/sql/tableattrs.sql1
-rw-r--r--util/updates/update_wip_lists.sql15
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);