summaryrefslogtreecommitdiff
path: root/util
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2020-08-19 10:40:03 +0200
committerYorhel <git@yorhel.nl>2020-08-19 10:40:05 +0200
commit51e430fdfc5df05b7ee49d6955e2cfc8e4205b88 (patch)
tree6ea50f0fdfefc6ccd3c6df9aaeeb62634dc4dc26 /util
parent31367028391ec4ba9a8d2d9837651b5a522b3340 (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.
Diffstat (limited to 'util')
-rw-r--r--util/updates/2020-08-19-reviews-caches.sql14
1 files changed, 14 insertions, 0 deletions
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);