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.

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);