diff options
Diffstat (limited to 'util')
-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 |
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); |