diff options
author | Yorhel <git@yorhel.nl> | 2020-08-19 10:40:03 +0200 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2020-08-19 10:40:05 +0200 |
commit | 51e430fdfc5df05b7ee49d6955e2cfc8e4205b88 (patch) | |
tree | 6ea50f0fdfefc6ccd3c6df9aaeeb62634dc4dc26 | |
parent | 31367028391ec4ba9a8d2d9837651b5a522b3340 (diff) |
reviews: Cache vote & post stats
Not strictly necessary for the current use, but I plan to add some
browsing features and those really will need it.
-rw-r--r-- | lib/Multi/Maintenance.pm | 2 | ||||
-rw-r--r-- | lib/VNWeb/Reviews/Page.pm | 10 | ||||
-rw-r--r-- | lib/VNWeb/Reviews/VNTab.pm | 11 | ||||
-rw-r--r-- | sql/func.sql | 17 | ||||
-rw-r--r-- | sql/schema.sql | 6 | ||||
-rw-r--r-- | sql/triggers.sql | 31 | ||||
-rw-r--r-- | util/updates/2020-08-19-reviews-caches.sql | 14 |
7 files changed, 75 insertions, 16 deletions
diff --git a/lib/Multi/Maintenance.pm b/lib/Multi/Maintenance.pm index a371f29b..57684f37 100644 --- a/lib/Multi/Maintenance.pm +++ b/lib/Multi/Maintenance.pm @@ -77,6 +77,8 @@ my %dailies = ( # takes about 10 seconds, OK imagecache => 'SELECT update_images_cache(NULL)', + reviewcache => 'SELECT update_reviews_votes_cache(NULL)', + cleansessions => q|DELETE FROM sessions WHERE expires < NOW()|, cleannotifications => q|DELETE FROM notifications WHERE read < NOW()-'1 month'::interval|, cleannotifications2=> q|DELETE FROM notifications WHERE id IN ( diff --git a/lib/VNWeb/Reviews/Page.pm b/lib/VNWeb/Reviews/Page.pm index a06b20ca..3e85e5e6 100644 --- a/lib/VNWeb/Reviews/Page.pm +++ b/lib/VNWeb/Reviews/Page.pm @@ -66,7 +66,7 @@ sub review_ { td_ ''; td_ style => 'text-align: right', sub { elm_ 'Reviews.Vote' => $VNWeb::Reviews::Elm::VOTE_OUT, { %$w, can => auth && $w->{user_id} != auth->uid }, sub { - span_ sprintf '๐ %d ๐ %d', $w->{up}, $w->{down}; + span_ sprintf '๐ %d ๐ %d', $w->{c_up}, $w->{c_down}; }; } }; @@ -78,17 +78,13 @@ TUWF::get qr{/$RE{wid}(?:(?<sep>[\./])$RE{num})?}, sub { return tuwf->resNotFound if !auth->permReview; #XXX:While in beta my($id, $sep, $num) = (tuwf->capture('id'), tuwf->capture('sep')||'', tuwf->capture('num')); my $w = tuwf->dbRowi( - 'SELECT r.id, r.vid, r.rid, r.summary, r.text, r.spoiler, uv.vote - , rel.title AS rtitle, rel.original AS roriginal, rel.type AS rtype - , COALESCE(c.count,0) AS count, COALESCE(s.up,0) AS up, COALESCE(s.down,0) AS down, rv.vote AS my + 'SELECT r.id, r.vid, r.rid, r.summary, r.text, r.spoiler, c.count, r.c_up, r.c_down, uv.vote + , rel.title AS rtitle, rel.original AS roriginal, rel.type AS rtype, rv.vote AS my , ', sql_user(), ',', sql_totime('r.date'), 'AS date,', sql_totime('r.lastmod'), 'AS lastmod FROM reviews r LEFT JOIN releases rel ON rel.id = r.rid LEFT JOIN users u ON u.id = r.uid LEFT JOIN ulist_vns uv ON uv.uid = r.uid AND uv.vid = r.vid - LEFT JOIN (SELECT rv.id, COUNT(*) FILTER(WHERE rv.vote), COUNT(*) FILTER(WHERE NOT rv.vote) - FROM reviews_votes rv JOIN users u ON u.id = rv.uid WHERE NOT u.ign_votes GROUP BY rv.id - ) AS s(id,up,down) ON s.id = r.id LEFT JOIN (SELECT id, COUNT(*) FROM reviews_posts GROUP BY id) AS c(id,count) ON c.id = r.id LEFT JOIN reviews_votes rv ON rv.id = r.id AND rv.uid =', \auth->uid, ' WHERE r.id =', \$id diff --git a/lib/VNWeb/Reviews/VNTab.pm b/lib/VNWeb/Reviews/VNTab.pm index ef94bf2f..95fc18aa 100644 --- a/lib/VNWeb/Reviews/VNTab.pm +++ b/lib/VNWeb/Reviews/VNTab.pm @@ -10,16 +10,11 @@ sub reviews_ { # TODO: Order my $lst = tuwf->dbAlli( - 'SELECT r.id, r.rid, r.summary, r.text <> \'\' AS isfull, r.spoiler, uv.vote - , COALESCE(c.count,0) AS count, COALESCE(s.up,0) AS up, COALESCE(s.down,0) AS down, rv.vote AS my + 'SELECT r.id, r.rid, r.summary, r.text <> \'\' AS isfull, r.spoiler, r.c_up, r.c_down, r.c_count, uv.vote, rv.vote AS my , ', sql_totime('r.date'), 'AS date, ', sql_user(), ' FROM reviews r LEFT JOIN users u ON r.uid = u.id LEFT JOIN ulist_vns uv ON uv.uid = r.uid AND uv.vid = r.vid - LEFT JOIN (SELECT rv.id, COUNT(*) FILTER(WHERE rv.vote), COUNT(*) FILTER(WHERE NOT rv.vote) - FROM reviews_votes rv JOIN users u ON u.id = rv.uid WHERE NOT u.ign_votes GROUP BY rv.id - ) AS s(id,up,down) ON s.id = r.id - LEFT JOIN (SELECT id, COUNT(*) FROM reviews_posts GROUP BY id) AS c(id,count) ON c.id = r.id LEFT JOIN reviews_votes rv ON rv.uid =', \auth->uid, ' AND rv.id = r.id WhERE r.vid =', \$v->{id} ); @@ -57,9 +52,9 @@ sub reviews_ { }; div_ sub { a_ href => "/$r->{id}#review", 'Full review ยป' if $r->{isfull}; - a_ href => "/$r->{id}#threadstart", $r->{count} == 1 ? '1 comment' : "$r->{count} comments"; + a_ href => "/$r->{id}#threadstart", $r->{c_count} == 1 ? '1 comment' : "$r->{c_count} comments"; elm_ 'Reviews.Vote' => $VNWeb::Reviews::Elm::VOTE_OUT, { %$r, can => auth && $r->{user_id} != auth->uid }, sub { - span_ sprintf '๐ %d ๐ %d', $r->{up}, $r->{down}; + span_ sprintf '๐ %d ๐ %d', $r->{c_up}, $r->{c_down}; }; }; } for @$lst; diff --git a/sql/func.sql b/sql/func.sql index 8fc849cb..da06450f 100644 --- a/sql/func.sql +++ b/sql/func.sql @@ -179,6 +179,23 @@ END; $$ LANGUAGE plpgsql; +-- Update reviews.c_up and c_down. +CREATE OR REPLACE FUNCTION update_reviews_votes_cache(vndbid) RETURNS void AS $$ +BEGIN + WITH stats(id,up,down) AS ( + SELECT r.id, COUNT(*) FILTER(WHERE rv.vote AND NOT u.ign_votes), COUNT(*) FILTER(WHERE NOT rv.vote AND NOT u.ign_votes) + FROM reviews r + LEFT JOIN reviews_votes rv ON rv.id = r.id + LEFT JOIN users u ON u.id = rv.uid + WHERE $1 IS NULL OR r.id = $1 + GROUP BY r.id + ) + UPDATE reviews SET c_up = up, c_down = down + FROM stats WHERE reviews.id = stats.id AND (reviews.c_up,reviews.c_down) <> (stats.up,stats.down); +END; $$ LANGUAGE plpgsql; + + + -- 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 diff --git a/sql/schema.sql b/sql/schema.sql index 40d70c0a..cbcca38f 100644 --- a/sql/schema.sql +++ b/sql/schema.sql @@ -508,7 +508,11 @@ CREATE TABLE reviews ( lastmod timestamptz, summary text NOT NULL, text text, - spoiler boolean NOT NULL + spoiler boolean NOT NULL, + c_up int NOT NULL DEFAULT 0, + c_down int NOT NULL DEFAULT 0, + c_count smallint NOT NULL DEFAULT 0, + c_lastnum smallint ); -- reviews_posts diff --git a/sql/triggers.sql b/sql/triggers.sql index 5e83adef..eeb0c411 100644 --- a/sql/triggers.sql +++ b/sql/triggers.sql @@ -292,6 +292,23 @@ CREATE TRIGGER update_threads_cache AFTER INSERT OR UPDATE OR DELETE ON threads_ +-- Update reviews.c_count and c_lastnum + +CREATE OR REPLACE FUNCTION update_reviews_cache() RETURNS trigger AS $$ +BEGIN + UPDATE reviews + SET c_count = COALESCE((SELECT COUNT(*) FROM reviews_posts WHERE NOT hidden AND id = reviews.id), 0) + , c_lastnum = (SELECT MAX(num) FROM reviews_posts WHERE NOT hidden AND id = reviews.id) + WHERE id IN(OLD.tid,NEW.tid); + RETURN NULL; +END +$$ LANGUAGE plpgsql; + +CREATE TRIGGER update_reviews_cache AFTER INSERT OR UPDATE OR DELETE ON reviews_posts FOR EACH ROW EXECUTE PROCEDURE update_reviews_cache(); + + + + -- Call update_images_cache() for every change on image_votes @@ -304,3 +321,17 @@ $$ LANGUAGE plpgsql; CREATE TRIGGER image_votes_cache1 AFTER INSERT OR DELETE ON image_votes FOR EACH ROW EXECUTE PROCEDURE update_images_cache(); CREATE TRIGGER image_votes_cache2 AFTER UPDATE ON image_votes FOR EACH ROW WHEN ((OLD.id, OLD.sexual, OLD.violence, OLD.ignore) IS DISTINCT FROM (NEW.id, NEW.sexual, NEW.violence, NEW.ignore)) EXECUTE PROCEDURE update_images_cache(); + + + + +-- Call update_reviews_votes_cache() for every change on reviews_votes + +CREATE OR REPLACE FUNCTION update_reviews_votes_cache() RETURNS trigger AS $$ +BEGIN + PERFORM update_reviews_votes_cache(id) FROM (SELECT OLD.id UNION SELECT NEW.id) AS x(id) WHERE id IS NOT NULL; + RETURN NULL; +END +$$ LANGUAGE plpgsql; + +CREATE TRIGGER reviews_votes_cache AFTER INSERT OR UPDATE OR DELETE ON reviews_votes FOR EACH ROW EXECUTE PROCEDURE update_reviews_votes_cache(); diff --git a/util/updates/2020-08-19-reviews-caches.sql b/util/updates/2020-08-19-reviews-caches.sql new file mode 100644 index 00000000..9c37808d --- /dev/null +++ b/util/updates/2020-08-19-reviews-caches.sql @@ -0,0 +1,14 @@ +CREATE UNIQUE INDEX reviews_posts_uid ON reviews_posts (uid); + +ALTER TABLE reviews ADD COLUMN c_up int NOT NULL DEFAULT 0; +ALTER TABLE reviews ADD COLUMN c_down int NOT NULL DEFAULT 0; +ALTER TABLE reviews ADD COLUMN c_count smallint NOT NULL DEFAULT 0; +ALTER TABLE reviews ADD COLUMN c_lastnum smallint; + +\i sql/func.sql +\i sql/triggers.sql + +SELECT update_reviews_votes_cache(NULL); +UPDATE reviews + SET c_count = COALESCE((SELECT COUNT(*) FROM reviews_posts WHERE NOT hidden AND id = reviews.id), 0) + , c_lastnum = (SELECT MAX(num) FROM reviews_posts WHERE NOT hidden AND id = reviews.id); |