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
  26     post p
  27     LEFT JOIN (
  28         SELECT
  29             comment.post_id,
  30             count(*) AS comments,
  31             max(comment.published) AS recent_comment_time
  32         FROM
  33             comment
  34         GROUP BY
  35             comment.post_id) ct ON ct.post_id = p.id
  36     LEFT JOIN (
  37         SELECT
  38             post_like.post_id,
  39             sum(post_like.score) AS score,
  40             sum(post_like.score) FILTER (WHERE post_like.score = 1) AS upvotes,
  41             - sum(post_like.score) FILTER (WHERE post_like.score = '-1'::integer) AS downvotes
  42         FROM
  43             post_like
  44         GROUP BY
  45             post_like.post_id) pl ON pl.post_id = p.id;
  46
  47 -- Add community aggregate triggers
  48 -- initial post add
  49 CREATE FUNCTION post_aggregates_post ()
  50     RETURNS TRIGGER
  51     LANGUAGE plpgsql
  52     AS $$
  53 BEGIN
  54     IF (TG_OP = 'INSERT') THEN
  55         INSERT INTO post_aggregates (post_id)
  56             VALUES (NEW.id);
  57     ELSIF (TG_OP = 'DELETE') THEN
  58         DELETE FROM post_aggregates
  59         WHERE post_id = OLD.id;
  60     END IF;
  61     RETURN NULL;
  62 END
  63 $$;
  64
  65 CREATE TRIGGER post_aggregates_post
  66     AFTER INSERT OR DELETE ON post
  67     FOR EACH ROW
  68     EXECUTE PROCEDURE post_aggregates_post ();
  69
  70 -- comment count
  71 CREATE FUNCTION post_aggregates_comment_count ()
  72     RETURNS TRIGGER
  73     LANGUAGE plpgsql
  74     AS $$
  75 BEGIN
  76     IF (TG_OP = 'INSERT') THEN
  77         UPDATE
  78             post_aggregates pa
  79         SET
  80             comments = comments + 1
  81         WHERE
  82             pa.post_id = NEW.post_id;
  83         -- A 2 day necro-bump limit
  84         UPDATE
  85             post_aggregates pa
  86         SET
  87             newest_comment_time = NEW.published
  88         WHERE
  89             pa.post_id = NEW.post_id
  90             AND published > ('now'::timestamp - '2 days'::interval);
  91     ELSIF (TG_OP = 'DELETE') THEN
  92         -- Join to post because that post may not exist anymore
  93         UPDATE
  94             post_aggregates pa
  95         SET
  96             comments = comments - 1
  97         FROM
  98             post p
  99         WHERE
 100             pa.post_id = p.id
 101             AND pa.post_id = OLD.post_id;
 102     END IF;
 103     RETURN NULL;
 104 END
 105 $$;
 106
 107 CREATE TRIGGER post_aggregates_comment_count
 108     AFTER INSERT OR DELETE ON comment
 109     FOR EACH ROW
 110     EXECUTE PROCEDURE post_aggregates_comment_count ();
 111
 112 -- post score
 113 CREATE FUNCTION post_aggregates_score ()
 114     RETURNS TRIGGER
 115     LANGUAGE plpgsql
 116     AS $$
 117 BEGIN
 118     IF (TG_OP = 'INSERT') THEN
 119         UPDATE
 120             post_aggregates pa
 121         SET
 122             score = score + NEW.score,
 123             upvotes = CASE WHEN NEW.score = 1 THEN
 124                 upvotes + 1
 125             ELSE
 126                 upvotes
 127             END,
 128             downvotes = CASE WHEN NEW.score = - 1 THEN
 129                 downvotes + 1
 130             ELSE
 131                 downvotes
 132             END
 133         WHERE
 134             pa.post_id = NEW.post_id;
 135     ELSIF (TG_OP = 'DELETE') THEN
 136         -- Join to post because that post may not exist anymore
 137         UPDATE
 138             post_aggregates pa
 139         SET
 140             score = score - OLD.score,
 141             upvotes = CASE WHEN OLD.score = 1 THEN
 142                 upvotes - 1
 143             ELSE
 144                 upvotes
 145             END,
 146             downvotes = CASE WHEN OLD.score = - 1 THEN
 147                 downvotes - 1
 148             ELSE
 149                 downvotes
 150             END
 151         FROM
 152             post p
 153         WHERE
 154             pa.post_id = p.id
 155             AND pa.post_id = OLD.post_id;
 156     END IF;
 157     RETURN NULL;
 158 END
 159 $$;
 160
 161 CREATE TRIGGER post_aggregates_score
 162     AFTER INSERT OR DELETE ON post_like
 163     FOR EACH ROW
 164     EXECUTE PROCEDURE post_aggregates_score ();
 165
 166 -- post stickied
 167 CREATE FUNCTION post_aggregates_stickied ()
 168     RETURNS TRIGGER
 169     LANGUAGE plpgsql
 170     AS $$
 171 BEGIN
 172     UPDATE
 173         post_aggregates pa
 174     SET
 175         stickied = NEW.stickied
 176     WHERE
 177         pa.post_id = NEW.id;
 178     RETURN NULL;
 179 END
 180 $$;
 181
 182 CREATE TRIGGER post_aggregates_stickied
 183     AFTER UPDATE ON post
 184     FOR EACH ROW
 185     WHEN (OLD.stickied IS DISTINCT FROM NEW.stickied)
 186     EXECUTE PROCEDURE post_aggregates_stickied ();
 187