migrations/2022-04-04-183652_update_community_aggregates_on_soft_delete/down.sql

   1 DROP TRIGGER IF EXISTS community_aggregates_post_count ON post;
   2
   3 DROP TRIGGER IF EXISTS community_aggregates_comment_count ON comment;
   4
   5 DROP TRIGGER IF EXISTS site_aggregates_comment_insert ON comment;
   6
   7 DROP TRIGGER IF EXISTS site_aggregates_comment_delete ON comment;
   8
   9 DROP TRIGGER IF EXISTS site_aggregates_post_insert ON post;
  10
  11 DROP TRIGGER IF EXISTS site_aggregates_post_delete ON post;
  12
  13 DROP TRIGGER IF EXISTS site_aggregates_community_insert ON community;
  14
  15 DROP TRIGGER IF EXISTS site_aggregates_community_delete ON community;
  16
  17 DROP TRIGGER IF EXISTS person_aggregates_post_count ON post;
  18
  19 DROP TRIGGER IF EXISTS person_aggregates_comment_count ON comment;
  20
  21 DROP FUNCTION was_removed_or_deleted (TG_OP text, OLD record, NEW record);
  22
  23 DROP FUNCTION was_restored_or_created (TG_OP text, OLD record, NEW record);
  24
  25 -- Community aggregate functions
  26 CREATE OR REPLACE FUNCTION community_aggregates_post_count ()
  27     RETURNS TRIGGER
  28     LANGUAGE plpgsql
  29     AS $$
  30 BEGIN
  31     IF (TG_OP = 'INSERT') THEN
  32         UPDATE
  33             community_aggregates
  34         SET
  35             posts = posts + 1
  36         WHERE
  37             community_id = NEW.community_id;
  38     ELSIF (TG_OP = 'DELETE') THEN
  39         UPDATE
  40             community_aggregates
  41         SET
  42             posts = posts - 1
  43         WHERE
  44             community_id = OLD.community_id;
  45         -- Update the counts if the post got deleted
  46         UPDATE
  47             community_aggregates ca
  48         SET
  49             posts = coalesce(cd.posts, 0),
  50             comments = coalesce(cd.comments, 0)
  51         FROM (
  52             SELECT
  53                 c.id,
  54                 count(DISTINCT p.id) AS posts,
  55                 count(DISTINCT ct.id) AS comments
  56             FROM
  57                 community c
  58             LEFT JOIN post p ON c.id = p.community_id
  59             LEFT JOIN comment ct ON p.id = ct.post_id
  60         GROUP BY
  61             c.id) cd
  62     WHERE
  63         ca.community_id = OLD.community_id;
  64     END IF;
  65     RETURN NULL;
  66 END
  67 $$;
  68
  69 CREATE OR REPLACE FUNCTION community_aggregates_comment_count ()
  70     RETURNS TRIGGER
  71     LANGUAGE plpgsql
  72     AS $$
  73 BEGIN
  74     IF (TG_OP = 'INSERT') THEN
  75         UPDATE
  76             community_aggregates ca
  77         SET
  78             comments = comments + 1
  79         FROM
  80             comment c,
  81             post p
  82         WHERE
  83             p.id = c.post_id
  84             AND p.id = NEW.post_id
  85             AND ca.community_id = p.community_id;
  86     ELSIF (TG_OP = 'DELETE') THEN
  87         UPDATE
  88             community_aggregates ca
  89         SET
  90             comments = comments - 1
  91         FROM
  92             comment c,
  93             post p
  94         WHERE
  95             p.id = c.post_id
  96             AND p.id = OLD.post_id
  97             AND ca.community_id = p.community_id;
  98     END IF;
  99     RETURN NULL;
 100 END
 101 $$;
 102
 103 -- Community aggregate triggers
 104 CREATE TRIGGER community_aggregates_post_count
 105     AFTER INSERT OR DELETE ON post
 106     FOR EACH ROW
 107     EXECUTE PROCEDURE community_aggregates_post_count ();
 108
 109 CREATE TRIGGER community_aggregates_comment_count
 110     AFTER INSERT OR DELETE ON comment
 111     FOR EACH ROW
 112     EXECUTE PROCEDURE community_aggregates_comment_count ();
 113
 114 -- Site aggregate functions
 115 CREATE OR REPLACE FUNCTION site_aggregates_post_insert ()
 116     RETURNS TRIGGER
 117     LANGUAGE plpgsql
 118     AS $$
 119 BEGIN
 120     UPDATE
 121         site_aggregates
 122     SET
 123         posts = posts + 1;
 124     RETURN NULL;
 125 END
 126 $$;
 127
 128 CREATE OR REPLACE FUNCTION site_aggregates_post_delete ()
 129     RETURNS TRIGGER
 130     LANGUAGE plpgsql
 131     AS $$
 132 BEGIN
 133     UPDATE
 134         site_aggregates sa
 135     SET
 136         posts = posts - 1
 137     FROM
 138         site s
 139     WHERE
 140         sa.site_id = s.id;
 141     RETURN NULL;
 142 END
 143 $$;
 144
 145 CREATE OR REPLACE FUNCTION site_aggregates_comment_insert ()
 146     RETURNS TRIGGER
 147     LANGUAGE plpgsql
 148     AS $$
 149 BEGIN
 150     UPDATE
 151         site_aggregates
 152     SET
 153         comments = comments + 1;
 154     RETURN NULL;
 155 END
 156 $$;
 157
 158 CREATE OR REPLACE FUNCTION site_aggregates_comment_delete ()
 159     RETURNS TRIGGER
 160     LANGUAGE plpgsql
 161     AS $$
 162 BEGIN
 163     UPDATE
 164         site_aggregates sa
 165     SET
 166         comments = comments - 1
 167     FROM
 168         site s
 169     WHERE
 170         sa.site_id = s.id;
 171     RETURN NULL;
 172 END
 173 $$;
 174
 175 CREATE OR REPLACE FUNCTION site_aggregates_community_insert ()
 176     RETURNS TRIGGER
 177     LANGUAGE plpgsql
 178     AS $$
 179 BEGIN
 180     UPDATE
 181         site_aggregates
 182     SET
 183         communities = communities + 1;
 184     RETURN NULL;
 185 END
 186 $$;
 187
 188 CREATE OR REPLACE FUNCTION site_aggregates_community_delete ()
 189     RETURNS TRIGGER
 190     LANGUAGE plpgsql
 191     AS $$
 192 BEGIN
 193     UPDATE
 194         site_aggregates sa
 195     SET
 196         communities = communities - 1
 197     FROM
 198         site s
 199     WHERE
 200         sa.site_id = s.id;
 201     RETURN NULL;
 202 END
 203 $$;
 204
 205 -- Site update triggers
 206 CREATE TRIGGER site_aggregates_post_insert
 207     AFTER INSERT ON post
 208     FOR EACH ROW
 209     WHEN (NEW.local = TRUE)
 210     EXECUTE PROCEDURE site_aggregates_post_insert ();
 211
 212 CREATE TRIGGER site_aggregates_post_delete
 213     AFTER DELETE ON post
 214     FOR EACH ROW
 215     WHEN (OLD.local = TRUE)
 216     EXECUTE PROCEDURE site_aggregates_post_delete ();
 217
 218 CREATE TRIGGER site_aggregates_comment_insert
 219     AFTER INSERT ON comment
 220     FOR EACH ROW
 221     WHEN (NEW.local = TRUE)
 222     EXECUTE PROCEDURE site_aggregates_comment_insert ();
 223
 224 CREATE TRIGGER site_aggregates_comment_delete
 225     AFTER DELETE ON comment
 226     FOR EACH ROW
 227     WHEN (OLD.local = TRUE)
 228     EXECUTE PROCEDURE site_aggregates_comment_delete ();
 229
 230 CREATE TRIGGER site_aggregates_community_insert
 231     AFTER INSERT ON community
 232     FOR EACH ROW
 233     WHEN (NEW.local = TRUE)
 234     EXECUTE PROCEDURE site_aggregates_community_insert ();
 235
 236 CREATE TRIGGER site_aggregates_community_delete
 237     AFTER DELETE ON community
 238     FOR EACH ROW
 239     WHEN (OLD.local = TRUE)
 240     EXECUTE PROCEDURE site_aggregates_community_delete ();
 241
 242 -- Person aggregate functions
 243 CREATE OR REPLACE FUNCTION person_aggregates_post_count ()
 244     RETURNS TRIGGER
 245     LANGUAGE plpgsql
 246     AS $$
 247 BEGIN
 248     IF (TG_OP = 'INSERT') THEN
 249         UPDATE
 250             person_aggregates
 251         SET
 252             post_count = post_count + 1
 253         WHERE
 254             person_id = NEW.creator_id;
 255     ELSIF (TG_OP = 'DELETE') THEN
 256         UPDATE
 257             person_aggregates
 258         SET
 259             post_count = post_count - 1
 260         WHERE
 261             person_id = OLD.creator_id;
 262         -- If the post gets deleted, the score calculation trigger won't fire,
 263         -- so you need to re-calculate
 264         UPDATE
 265             person_aggregates ua
 266         SET
 267             post_score = pd.score
 268         FROM (
 269             SELECT
 270                 u.id,
 271                 coalesce(0, sum(pl.score)) AS score
 272                 -- User join because posts could be empty
 273             FROM
 274                 person u
 275             LEFT JOIN post p ON u.id = p.creator_id
 276             LEFT JOIN post_like pl ON p.id = pl.post_id
 277         GROUP BY
 278             u.id) pd
 279     WHERE
 280         ua.person_id = OLD.creator_id;
 281     END IF;
 282     RETURN NULL;
 283 END
 284 $$;
 285
 286 CREATE OR REPLACE FUNCTION person_aggregates_comment_count ()
 287     RETURNS TRIGGER
 288     LANGUAGE plpgsql
 289     AS $$
 290 BEGIN
 291     IF (TG_OP = 'INSERT') THEN
 292         UPDATE
 293             person_aggregates
 294         SET
 295             comment_count = comment_count + 1
 296         WHERE
 297             person_id = NEW.creator_id;
 298     ELSIF (TG_OP = 'DELETE') THEN
 299         UPDATE
 300             person_aggregates
 301         SET
 302             comment_count = comment_count - 1
 303         WHERE
 304             person_id = OLD.creator_id;
 305         -- If the comment gets deleted, the score calculation trigger won't fire,
 306         -- so you need to re-calculate
 307         UPDATE
 308             person_aggregates ua
 309         SET
 310             comment_score = cd.score
 311         FROM (
 312             SELECT
 313                 u.id,
 314                 coalesce(0, sum(cl.score)) AS score
 315                 -- User join because comments could be empty
 316             FROM
 317                 person u
 318             LEFT JOIN comment c ON u.id = c.creator_id
 319             LEFT JOIN comment_like cl ON c.id = cl.comment_id
 320         GROUP BY
 321             u.id) cd
 322     WHERE
 323         ua.person_id = OLD.creator_id;
 324     END IF;
 325     RETURN NULL;
 326 END
 327 $$;
 328
 329 -- Person aggregate triggers
 330 CREATE TRIGGER person_aggregates_post_count
 331     AFTER INSERT OR DELETE ON post
 332     FOR EACH ROW
 333     EXECUTE PROCEDURE person_aggregates_post_count ();
 334
 335 CREATE TRIGGER person_aggregates_comment_count
 336     AFTER INSERT OR DELETE ON comment
 337     FOR EACH ROW
 338     EXECUTE PROCEDURE person_aggregates_comment_count ();
 339