migrations/2022-04-04-183652_update_community_aggregates_on_soft_delete/up.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 CREATE OR REPLACE FUNCTION was_removed_or_deleted (TG_OP text, OLD record, NEW record)
  22     RETURNS boolean
  23     LANGUAGE plpgsql
  24     AS $$
  25 BEGIN
  26     IF (TG_OP = 'INSERT') THEN
  27         RETURN FALSE;
  28     END IF;
  29     IF (TG_OP = 'DELETE') THEN
  30         RETURN TRUE;
  31     END IF;
  32     RETURN TG_OP = 'UPDATE'
  33         AND ((OLD.deleted = 'f'
  34                 AND NEW.deleted = 't')
  35             OR (OLD.removed = 'f'
  36                 AND NEW.removed = 't'));
  37 END
  38 $$;
  39
  40 CREATE OR REPLACE FUNCTION was_restored_or_created (TG_OP text, OLD record, NEW record)
  41     RETURNS boolean
  42     LANGUAGE plpgsql
  43     AS $$
  44 BEGIN
  45     IF (TG_OP = 'DELETE') THEN
  46         RETURN FALSE;
  47     END IF;
  48     IF (TG_OP = 'INSERT') THEN
  49         RETURN TRUE;
  50     END IF;
  51     RETURN TG_OP = 'UPDATE'
  52         AND ((OLD.deleted = 't'
  53                 AND NEW.deleted = 'f')
  54             OR (OLD.removed = 't'
  55                 AND NEW.removed = 'f'));
  56 END
  57 $$;
  58
  59 -- Community aggregate functions
  60 CREATE OR REPLACE FUNCTION community_aggregates_post_count ()
  61     RETURNS TRIGGER
  62     LANGUAGE plpgsql
  63     AS $$
  64 BEGIN
  65     IF (was_restored_or_created (TG_OP, OLD, NEW)) THEN
  66         UPDATE
  67             community_aggregates
  68         SET
  69             posts = posts + 1
  70         WHERE
  71             community_id = NEW.community_id;
  72         IF (TG_OP = 'UPDATE') THEN
  73             -- Post was restored, so restore comment counts as well
  74             UPDATE
  75                 community_aggregates ca
  76             SET
  77                 posts = coalesce(cd.posts, 0),
  78                 comments = coalesce(cd.comments, 0)
  79             FROM (
  80                 SELECT
  81                     c.id,
  82                     count(DISTINCT p.id) AS posts,
  83                     count(DISTINCT ct.id) AS comments
  84                 FROM
  85                     community c
  86                 LEFT JOIN post p ON c.id = p.community_id
  87                     AND p.deleted = 'f'
  88                     AND p.removed = 'f'
  89             LEFT JOIN comment ct ON p.id = ct.post_id
  90                 AND ct.deleted = 'f'
  91                 AND ct.removed = 'f'
  92         WHERE
  93             c.id = NEW.community_id
  94         GROUP BY
  95             c.id) cd
  96         WHERE
  97             ca.community_id = NEW.community_id;
  98         END IF;
  99     ELSIF (was_removed_or_deleted (TG_OP, OLD, NEW)) THEN
 100         UPDATE
 101             community_aggregates
 102         SET
 103             posts = posts - 1
 104         WHERE
 105             community_id = OLD.community_id;
 106         -- Update the counts if the post got deleted
 107         UPDATE
 108             community_aggregates ca
 109         SET
 110             posts = coalesce(cd.posts, 0),
 111             comments = coalesce(cd.comments, 0)
 112         FROM (
 113             SELECT
 114                 c.id,
 115                 count(DISTINCT p.id) AS posts,
 116                 count(DISTINCT ct.id) AS comments
 117             FROM
 118                 community c
 119             LEFT JOIN post p ON c.id = p.community_id
 120                 AND p.deleted = 'f'
 121                 AND p.removed = 'f'
 122         LEFT JOIN comment ct ON p.id = ct.post_id
 123             AND ct.deleted = 'f'
 124             AND ct.removed = 'f'
 125     WHERE
 126         c.id = OLD.community_id
 127     GROUP BY
 128         c.id) cd
 129     WHERE
 130         ca.community_id = OLD.community_id;
 131     END IF;
 132     RETURN NULL;
 133 END
 134 $$;
 135
 136 -- comment count
 137 CREATE OR REPLACE FUNCTION community_aggregates_comment_count ()
 138     RETURNS TRIGGER
 139     LANGUAGE plpgsql
 140     AS $$
 141 BEGIN
 142     IF (was_restored_or_created (TG_OP, OLD, NEW)) THEN
 143         UPDATE
 144             community_aggregates ca
 145         SET
 146             comments = comments + 1
 147         FROM
 148             comment c,
 149             post p
 150         WHERE
 151             p.id = c.post_id
 152             AND p.id = NEW.post_id
 153             AND ca.community_id = p.community_id;
 154     ELSIF (was_removed_or_deleted (TG_OP, OLD, NEW)) THEN
 155         UPDATE
 156             community_aggregates ca
 157         SET
 158             comments = comments - 1
 159         FROM
 160             comment c,
 161             post p
 162         WHERE
 163             p.id = c.post_id
 164             AND p.id = OLD.post_id
 165             AND ca.community_id = p.community_id;
 166     END IF;
 167     RETURN NULL;
 168 END
 169 $$;
 170
 171 -- Community aggregate triggers
 172 CREATE TRIGGER community_aggregates_post_count
 173     AFTER INSERT OR DELETE OR UPDATE OF removed,
 174     deleted ON post
 175     FOR EACH ROW
 176     EXECUTE PROCEDURE community_aggregates_post_count ();
 177
 178 CREATE TRIGGER community_aggregates_comment_count
 179     AFTER INSERT OR DELETE OR UPDATE OF removed,
 180     deleted ON comment
 181     FOR EACH ROW
 182     EXECUTE PROCEDURE community_aggregates_comment_count ();
 183
 184 -- Site aggregate functions
 185 CREATE OR REPLACE FUNCTION site_aggregates_post_insert ()
 186     RETURNS TRIGGER
 187     LANGUAGE plpgsql
 188     AS $$
 189 BEGIN
 190     IF (was_restored_or_created (TG_OP, OLD, NEW)) THEN
 191         UPDATE
 192             site_aggregates sa
 193         SET
 194             posts = posts + 1
 195         FROM
 196             site s
 197         WHERE
 198             sa.site_id = s.id;
 199     END IF;
 200     RETURN NULL;
 201 END
 202 $$;
 203
 204 CREATE OR REPLACE FUNCTION site_aggregates_post_delete ()
 205     RETURNS TRIGGER
 206     LANGUAGE plpgsql
 207     AS $$
 208 BEGIN
 209     IF (was_removed_or_deleted (TG_OP, OLD, NEW)) THEN
 210         UPDATE
 211             site_aggregates sa
 212         SET
 213             posts = posts - 1
 214         FROM
 215             site s
 216         WHERE
 217             sa.site_id = s.id;
 218     END IF;
 219     RETURN NULL;
 220 END
 221 $$;
 222
 223 CREATE OR REPLACE FUNCTION site_aggregates_comment_insert ()
 224     RETURNS TRIGGER
 225     LANGUAGE plpgsql
 226     AS $$
 227 BEGIN
 228     IF (was_restored_or_created (TG_OP, OLD, NEW)) THEN
 229         UPDATE
 230             site_aggregates sa
 231         SET
 232             comments = comments + 1
 233         FROM
 234             site s
 235         WHERE
 236             sa.site_id = s.id;
 237     END IF;
 238     RETURN NULL;
 239 END
 240 $$;
 241
 242 CREATE OR REPLACE FUNCTION site_aggregates_comment_delete ()
 243     RETURNS TRIGGER
 244     LANGUAGE plpgsql
 245     AS $$
 246 BEGIN
 247     IF (was_removed_or_deleted (TG_OP, OLD, NEW)) THEN
 248         UPDATE
 249             site_aggregates sa
 250         SET
 251             comments = comments - 1
 252         FROM
 253             site s
 254         WHERE
 255             sa.site_id = s.id;
 256     END IF;
 257     RETURN NULL;
 258 END
 259 $$;
 260
 261 CREATE OR REPLACE FUNCTION site_aggregates_community_insert ()
 262     RETURNS TRIGGER
 263     LANGUAGE plpgsql
 264     AS $$
 265 BEGIN
 266     IF (was_restored_or_created (TG_OP, OLD, NEW)) THEN
 267         UPDATE
 268             site_aggregates sa
 269         SET
 270             communities = communities + 1
 271         FROM
 272             site s
 273         WHERE
 274             sa.site_id = s.id;
 275     END IF;
 276     RETURN NULL;
 277 END
 278 $$;
 279
 280 CREATE OR REPLACE FUNCTION site_aggregates_community_delete ()
 281     RETURNS TRIGGER
 282     LANGUAGE plpgsql
 283     AS $$
 284 BEGIN
 285     IF (was_removed_or_deleted (TG_OP, OLD, NEW)) THEN
 286         UPDATE
 287             site_aggregates sa
 288         SET
 289             communities = communities - 1
 290         FROM
 291             site s
 292         WHERE
 293             sa.site_id = s.id;
 294     END IF;
 295     RETURN NULL;
 296 END
 297 $$;
 298
 299 -- Site aggregate triggers
 300 CREATE TRIGGER site_aggregates_post_insert
 301     AFTER INSERT OR UPDATE OF removed,
 302     deleted ON post
 303     FOR EACH ROW
 304     WHEN (NEW.local = TRUE)
 305     EXECUTE PROCEDURE site_aggregates_post_insert ();
 306
 307 CREATE TRIGGER site_aggregates_post_delete
 308     AFTER DELETE OR UPDATE OF removed,
 309     deleted ON post
 310     FOR EACH ROW
 311     WHEN (OLD.local = TRUE)
 312     EXECUTE PROCEDURE site_aggregates_post_delete ();
 313
 314 CREATE TRIGGER site_aggregates_comment_insert
 315     AFTER INSERT OR UPDATE OF removed,
 316     deleted ON comment
 317     FOR EACH ROW
 318     WHEN (NEW.local = TRUE)
 319     EXECUTE PROCEDURE site_aggregates_comment_insert ();
 320
 321 CREATE TRIGGER site_aggregates_comment_delete
 322     AFTER DELETE OR UPDATE OF removed,
 323     deleted ON comment
 324     FOR EACH ROW
 325     WHEN (OLD.local = TRUE)
 326     EXECUTE PROCEDURE site_aggregates_comment_delete ();
 327
 328 CREATE TRIGGER site_aggregates_community_insert
 329     AFTER INSERT OR UPDATE OF removed,
 330     deleted ON community
 331     FOR EACH ROW
 332     WHEN (NEW.local = TRUE)
 333     EXECUTE PROCEDURE site_aggregates_community_insert ();
 334
 335 CREATE TRIGGER site_aggregates_community_delete
 336     AFTER DELETE OR UPDATE OF removed,
 337     deleted ON community
 338     FOR EACH ROW
 339     WHEN (OLD.local = TRUE)
 340     EXECUTE PROCEDURE site_aggregates_community_delete ();
 341
 342 -- Person aggregate functions
 343 CREATE OR REPLACE FUNCTION person_aggregates_post_count ()
 344     RETURNS TRIGGER
 345     LANGUAGE plpgsql
 346     AS $$
 347 BEGIN
 348     IF (was_restored_or_created (TG_OP, OLD, NEW)) THEN
 349         UPDATE
 350             person_aggregates
 351         SET
 352             post_count = post_count + 1
 353         WHERE
 354             person_id = NEW.creator_id;
 355     ELSIF (was_removed_or_deleted (TG_OP, OLD, NEW)) THEN
 356         UPDATE
 357             person_aggregates
 358         SET
 359             post_count = post_count - 1
 360         WHERE
 361             person_id = OLD.creator_id;
 362         -- If the post gets deleted, the score calculation trigger won't fire,
 363         -- so you need to re-calculate
 364         UPDATE
 365             person_aggregates ua
 366         SET
 367             post_score = pd.score
 368         FROM (
 369             SELECT
 370                 u.id,
 371                 coalesce(0, sum(pl.score)) AS score
 372                 -- User join because posts could be empty
 373             FROM
 374                 person u
 375             LEFT JOIN post p ON u.id = p.creator_id
 376                 AND p.deleted = 'f'
 377                 AND p.removed = 'f'
 378         LEFT JOIN post_like pl ON p.id = pl.post_id
 379     GROUP BY
 380         u.id) pd
 381     WHERE
 382         ua.person_id = OLD.creator_id;
 383     END IF;
 384     RETURN NULL;
 385 END
 386 $$;
 387
 388 CREATE OR REPLACE FUNCTION person_aggregates_comment_count ()
 389     RETURNS TRIGGER
 390     LANGUAGE plpgsql
 391     AS $$
 392 BEGIN
 393     IF (was_restored_or_created (TG_OP, OLD, NEW)) THEN
 394         UPDATE
 395             person_aggregates
 396         SET
 397             comment_count = comment_count + 1
 398         WHERE
 399             person_id = NEW.creator_id;
 400     ELSIF (was_removed_or_deleted (TG_OP, OLD, NEW)) THEN
 401         UPDATE
 402             person_aggregates
 403         SET
 404             comment_count = comment_count - 1
 405         WHERE
 406             person_id = OLD.creator_id;
 407         -- If the comment gets deleted, the score calculation trigger won't fire,
 408         -- so you need to re-calculate
 409         UPDATE
 410             person_aggregates ua
 411         SET
 412             comment_score = cd.score
 413         FROM (
 414             SELECT
 415                 u.id,
 416                 coalesce(0, sum(cl.score)) AS score
 417                 -- User join because comments could be empty
 418             FROM
 419                 person u
 420             LEFT JOIN comment c ON u.id = c.creator_id
 421                 AND c.deleted = 'f'
 422                 AND c.removed = 'f'
 423         LEFT JOIN comment_like cl ON c.id = cl.comment_id
 424     GROUP BY
 425         u.id) cd
 426     WHERE
 427         ua.person_id = OLD.creator_id;
 428     END IF;
 429     RETURN NULL;
 430 END
 431 $$;
 432
 433 -- Person aggregate triggers
 434 CREATE TRIGGER person_aggregates_post_count
 435     AFTER INSERT OR DELETE OR UPDATE OF removed,
 436     deleted ON post
 437     FOR EACH ROW
 438     EXECUTE PROCEDURE person_aggregates_post_count ();
 439
 440 CREATE TRIGGER person_aggregates_comment_count
 441     AFTER INSERT OR DELETE OR UPDATE OF removed,
 442     deleted ON comment
 443     FOR EACH ROW
 444     EXECUTE PROCEDURE person_aggregates_comment_count ();
 445