From 8088055d38a591982a4209c0b6875618ddb67ec9 Mon Sep 17 00:00:00 2001 From: Dessalines Date: Thu, 18 Feb 2021 10:53:04 -0500 Subject: [PATCH] Fix aggregates time columns 2 (#1427) * Adding a new comment sort. Fixes #1294 * Fixing a migration comment. * Adding a comment for newest_comment_time_necro * Make sure federated items set correct aggregates fields in trigger. - Fixes #1402 --- .../down.sql | 35 +++++++++++++++++ .../up.sql | 39 +++++++++++++++++++ 2 files changed, 74 insertions(+) create mode 100644 migrations/2021-02-13-210612_set_correct_aggregates_time_columns/down.sql create mode 100644 migrations/2021-02-13-210612_set_correct_aggregates_time_columns/up.sql diff --git a/migrations/2021-02-13-210612_set_correct_aggregates_time_columns/down.sql b/migrations/2021-02-13-210612_set_correct_aggregates_time_columns/down.sql new file mode 100644 index 00000000..227c3ec2 --- /dev/null +++ b/migrations/2021-02-13-210612_set_correct_aggregates_time_columns/down.sql @@ -0,0 +1,35 @@ +create or replace function comment_aggregates_comment() +returns trigger language plpgsql +as $$ +begin + IF (TG_OP = 'INSERT') THEN + insert into comment_aggregates (comment_id) values (NEW.id); + ELSIF (TG_OP = 'DELETE') THEN + delete from comment_aggregates where comment_id = OLD.id; + END IF; + return null; +end $$; + +create or replace function post_aggregates_post() +returns trigger language plpgsql +as $$ +begin + IF (TG_OP = 'INSERT') THEN + insert into post_aggregates (post_id) values (NEW.id); + ELSIF (TG_OP = 'DELETE') THEN + delete from post_aggregates where post_id = OLD.id; + END IF; + return null; +end $$; + +create or replace function community_aggregates_community() +returns trigger language plpgsql +as $$ +begin + IF (TG_OP = 'INSERT') THEN + insert into community_aggregates (community_id) values (NEW.id); + ELSIF (TG_OP = 'DELETE') THEN + delete from community_aggregates where community_id = OLD.id; + END IF; + return null; +end $$; diff --git a/migrations/2021-02-13-210612_set_correct_aggregates_time_columns/up.sql b/migrations/2021-02-13-210612_set_correct_aggregates_time_columns/up.sql new file mode 100644 index 00000000..c195daad --- /dev/null +++ b/migrations/2021-02-13-210612_set_correct_aggregates_time_columns/up.sql @@ -0,0 +1,39 @@ +-- The published and updated columns on the aggregates tables are using now(), +-- when they should use the correct published or updated columns +-- This is mainly a problem with federated posts being fetched + +create or replace function comment_aggregates_comment() +returns trigger language plpgsql +as $$ +begin + IF (TG_OP = 'INSERT') THEN + insert into comment_aggregates (comment_id, published) values (NEW.id, NEW.published); + ELSIF (TG_OP = 'DELETE') THEN + delete from comment_aggregates where comment_id = OLD.id; + END IF; + return null; +end $$; + +create or replace function post_aggregates_post() +returns trigger language plpgsql +as $$ +begin + IF (TG_OP = 'INSERT') THEN + insert into post_aggregates (post_id, published, newest_comment_time, newest_comment_time_necro) values (NEW.id, NEW.published, NEW.published, NEW.published); + ELSIF (TG_OP = 'DELETE') THEN + delete from post_aggregates where post_id = OLD.id; + END IF; + return null; +end $$; + +create or replace function community_aggregates_community() +returns trigger language plpgsql +as $$ +begin + IF (TG_OP = 'INSERT') THEN + insert into community_aggregates (community_id, published) values (NEW.id, NEW.published); + ELSIF (TG_OP = 'DELETE') THEN + delete from community_aggregates where community_id = OLD.id; + END IF; + return null; +end $$; -- 2.44.1