From 896ba8451c0f138282b7043f4f5c559db2163b8c Mon Sep 17 00:00:00 2001 From: Dessalines Date: Sun, 1 Aug 2021 21:07:45 -0400 Subject: [PATCH] Some comment count fixes. - Make sure an update to deleted decreases the count. Fixes #1676 - Don't allow the post creator to comment necro-bump their own post. Fixes #1675 --- .../down.sql | 28 +++++++++ .../up.sql | 61 +++++++++++++++++++ 2 files changed, 89 insertions(+) create mode 100644 migrations/2021-08-02-002342_comment_count_fixes/down.sql create mode 100644 migrations/2021-08-02-002342_comment_count_fixes/up.sql diff --git a/migrations/2021-08-02-002342_comment_count_fixes/down.sql b/migrations/2021-08-02-002342_comment_count_fixes/down.sql new file mode 100644 index 00000000..3a895f70 --- /dev/null +++ b/migrations/2021-08-02-002342_comment_count_fixes/down.sql @@ -0,0 +1,28 @@ +drop trigger post_aggregates_comment_set_deleted on comment; +drop function post_aggregates_comment_deleted; + +create or replace function post_aggregates_comment_count() +returns trigger language plpgsql +as $$ +begin + IF (TG_OP = 'INSERT') THEN + update post_aggregates pa + set comments = comments + 1, + newest_comment_time = NEW.published + where pa.post_id = NEW.post_id; + + -- A 2 day necro-bump limit + update post_aggregates pa + set newest_comment_time_necro = NEW.published + where pa.post_id = NEW.post_id + and published > ('now'::timestamp - '2 days'::interval); + ELSIF (TG_OP = 'DELETE') THEN + -- Join to post because that post may not exist anymore + update post_aggregates pa + set comments = comments - 1 + from post p + where pa.post_id = p.id + and pa.post_id = OLD.post_id; + END IF; + return null; +end $$; diff --git a/migrations/2021-08-02-002342_comment_count_fixes/up.sql b/migrations/2021-08-02-002342_comment_count_fixes/up.sql new file mode 100644 index 00000000..b65f6008 --- /dev/null +++ b/migrations/2021-08-02-002342_comment_count_fixes/up.sql @@ -0,0 +1,61 @@ +-- Creating a new trigger for when comment.deleted is updated + +create or replace function post_aggregates_comment_deleted() +returns trigger language plpgsql +as $$ +begin + IF NEW.deleted = TRUE THEN + update post_aggregates pa + set comments = comments - 1 + where pa.post_id = NEW.post_id; + ELSE + update post_aggregates pa + set comments = comments + 1 + where pa.post_id = NEW.post_id; + END IF; + return null; +end $$; + +create trigger post_aggregates_comment_set_deleted +after update of deleted on comment +for each row +execute procedure post_aggregates_comment_deleted(); + +-- Fix issue with being able to necro-bump your own post +create or replace function post_aggregates_comment_count() +returns trigger language plpgsql +as $$ +begin + IF (TG_OP = 'INSERT') THEN + update post_aggregates pa + set comments = comments + 1, + newest_comment_time = NEW.published + where pa.post_id = NEW.post_id; + + -- A 2 day necro-bump limit + update post_aggregates pa + set newest_comment_time_necro = NEW.published + from post p + where pa.post_id = p.id + and pa.post_id = NEW.post_id + -- Fix issue with being able to necro-bump your own post + and NEW.creator_id != p.creator_id + and pa.published > ('now'::timestamp - '2 days'::interval); + + ELSIF (TG_OP = 'DELETE') THEN + -- Join to post because that post may not exist anymore + update post_aggregates pa + set comments = comments - 1 + from post p + where pa.post_id = p.id + and pa.post_id = OLD.post_id; + ELSIF (TG_OP = 'UPDATE') THEN + -- Join to post because that post may not exist anymore + update post_aggregates pa + set comments = comments - 1 + from post p + where pa.post_id = p.id + and pa.post_id = OLD.post_id; + END IF; + return null; +end $$; -- 2.44.1