summaryrefslogtreecommitdiff
path: root/util
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2019-12-19 15:18:41 +0100
committerYorhel <git@yorhel.nl>2019-12-19 15:18:44 +0100
commit87b1d640145d31dfb3c05ec947c8e796854c04cf (patch)
tree025c2336c0db73d07572552e61c1af28255fef84 /util
parente6f20ed931640c1b889ed0d514f4f3adad874c43 (diff)
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.
Diffstat (limited to 'util')
-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
5 files changed, 36 insertions, 24 deletions
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);