migrations/2020-12-10-152350_create_post_aggregates/up.sql

   1 -- Add post aggregates
   2 create table post_aggregates (
   3   id serial primary key,
   4   post_id int references post on update cascade on delete cascade not null,
   5   comments bigint not null default 0,
   6   score bigint not null default 0,
   7   upvotes bigint not null default 0,
   8   downvotes bigint not null default 0,
   9   stickied boolean not null default false,
  10   published timestamp not null default now(),
  11   newest_comment_time timestamp not null default now(),
  12   unique (post_id)
  13 );
  14
  15 insert into post_aggregates (post_id, comments, score, upvotes, downvotes, stickied, published, newest_comment_time)
  16   select
  17     p.id,
  18     coalesce(ct.comments, 0::bigint) as comments,
  19     coalesce(pl.score, 0::bigint) as score,
  20     coalesce(pl.upvotes, 0::bigint) as upvotes,
  21     coalesce(pl.downvotes, 0::bigint) as downvotes,
  22     p.stickied,
  23     p.published,
  24     greatest(ct.recent_comment_time, p.published) as newest_activity_time
  25   from post p
  26   left join (
  27     select comment.post_id,
  28     count(*) as comments,
  29     max(comment.published) as recent_comment_time
  30     from comment
  31     group by comment.post_id
  32   ) ct on ct.post_id = p.id
  33   left join (
  34     select post_like.post_id,
  35     sum(post_like.score) as score,
  36     sum(post_like.score) filter (where post_like.score = 1) as upvotes,
  37     -sum(post_like.score) filter (where post_like.score = '-1'::integer) as downvotes
  38     from post_like
  39     group by post_like.post_id
  40   ) pl on pl.post_id = p.id;
  41
  42 -- Add community aggregate triggers
  43
  44 -- initial post add
  45 create function post_aggregates_post()
  46 returns trigger language plpgsql
  47 as $$
  48 begin
  49   IF (TG_OP = 'INSERT') THEN
  50     insert into post_aggregates (post_id) values (NEW.id);
  51   ELSIF (TG_OP = 'DELETE') THEN
  52     delete from post_aggregates where post_id = OLD.id;
  53   END IF;
  54   return null;
  55 end $$;
  56
  57 create trigger post_aggregates_post
  58 after insert or delete on post
  59 for each row
  60 execute procedure post_aggregates_post();
  61
  62 -- comment count
  63 create function post_aggregates_comment_count()
  64 returns trigger language plpgsql
  65 as $$
  66 begin
  67   IF (TG_OP = 'INSERT') THEN
  68     update post_aggregates pa
  69     set comments = comments + 1
  70     where pa.post_id = NEW.post_id;
  71
  72     -- A 2 day necro-bump limit
  73     update post_aggregates pa
  74     set newest_comment_time = NEW.published
  75     where pa.post_id = NEW.post_id
  76     and published > ('now'::timestamp - '2 days'::interval);
  77   ELSIF (TG_OP = 'DELETE') THEN
  78     -- Join to post because that post may not exist anymore
  79     update post_aggregates pa
  80     set comments = comments - 1
  81     from post p
  82     where pa.post_id = p.id
  83     and pa.post_id = OLD.post_id;
  84   END IF;
  85   return null;
  86 end $$;
  87
  88 create trigger post_aggregates_comment_count
  89 after insert or delete on comment
  90 for each row
  91 execute procedure post_aggregates_comment_count();
  92
  93 -- post score
  94 create function post_aggregates_score()
  95 returns trigger language plpgsql
  96 as $$
  97 begin
  98   IF (TG_OP = 'INSERT') THEN
  99     update post_aggregates pa
 100     set score = score + NEW.score,
 101     upvotes = case when NEW.score = 1 then upvotes + 1 else upvotes end,
 102     downvotes = case when NEW.score = -1 then downvotes + 1 else downvotes end
 103     where pa.post_id = NEW.post_id;
 104
 105   ELSIF (TG_OP = 'DELETE') THEN
 106     -- Join to post because that post may not exist anymore
 107     update post_aggregates pa
 108     set score = score - OLD.score,
 109     upvotes = case when OLD.score = 1 then upvotes - 1 else upvotes end,
 110     downvotes = case when OLD.score = -1 then downvotes - 1 else downvotes end
 111     from post p
 112     where pa.post_id = p.id
 113     and pa.post_id = OLD.post_id;
 114
 115   END IF;
 116   return null;
 117 end $$;
 118
 119 create trigger post_aggregates_score
 120 after insert or delete on post_like
 121 for each row
 122 execute procedure post_aggregates_score();
 123
 124 -- post stickied
 125 create function post_aggregates_stickied()
 126 returns trigger language plpgsql
 127 as $$
 128 begin
 129   update post_aggregates pa
 130   set stickied = NEW.stickied
 131   where pa.post_id = NEW.id;
 132
 133   return null;
 134 end $$;
 135
 136 create trigger post_aggregates_stickied
 137 after update on post
 138 for each row
 139 when (OLD.stickied is distinct from NEW.stickied)
 140 execute procedure post_aggregates_stickied();