migrations/2023-06-19-120700_no_double_deletion/up.sql

   1 -- Deleting after removing should not decrement the count twice.
   2 create or replace function was_removed_or_deleted(TG_OP text, OLD record, NEW record)
   3 RETURNS boolean
   4 LANGUAGE plpgsql
   5 as $$
   6     begin
   7         IF (TG_OP = 'INSERT') THEN
   8             return false;
   9         end if;
  10
  11         IF (TG_OP = 'DELETE' AND OLD.deleted = 'f' AND OLD.removed = 'f') THEN
  12             return true;
  13         end if;
  14
  15     return TG_OP = 'UPDATE' AND (
  16             (OLD.deleted = 'f' AND NEW.deleted = 't') OR
  17             (OLD.removed = 'f' AND NEW.removed = 't')
  18             );
  19 END $$;
  20
  21 -- Recalculate proper comment count.
  22 UPDATE person_aggregates
  23     SET comment_count = cnt.count
  24     FROM (
  25     SELECT creator_id, count(*) AS count FROM comment
  26     WHERE deleted='f' AND removed='f'
  27     GROUP BY creator_id
  28         ) cnt
  29     WHERE person_aggregates.person_id = cnt.creator_id;
  30
  31 -- Recalculate proper comment score.
  32 UPDATE person_aggregates ua
  33     SET comment_score = cd.score
  34     FROM (
  35         SELECT u.id AS creator_id,
  36             coalesce(0, sum(cl.score)) as score
  37         -- User join because comments could be empty
  38         FROM person u
  39             LEFT JOIN comment c ON u.id = c.creator_id AND c.deleted = 'f' AND c.removed = 'f'
  40             LEFT JOIN comment_like cl ON c.id = cl.comment_id
  41         GROUP BY u.id
  42     ) cd
  43     WHERE ua.person_id = cd.creator_id;
  44
  45 -- Recalculate proper post count.
  46 UPDATE person_aggregates
  47     SET post_count = cnt.count
  48     FROM (
  49     SELECT creator_id, count(*) AS count FROM post
  50     WHERE deleted='f' AND removed='f'
  51     GROUP BY creator_id
  52         ) cnt
  53     WHERE person_aggregates.person_id = cnt.creator_id;
  54
  55 -- Recalculate proper post score.
  56  UPDATE person_aggregates ua
  57     SET post_score = pd.score
  58     FROM (
  59         SELECT u.id AS creator_id,
  60             coalesce(0, sum(pl.score)) AS score
  61             -- User join because posts could be empty
  62         FROM person u
  63             LEFT JOIN post p ON u.id = p.creator_id AND p.deleted = 'f' AND p.removed = 'f'
  64             LEFT JOIN post_like pl ON p.id = pl.post_id
  65         GROUP BY u.id
  66     ) pd
  67     WHERE ua.person_id = pd.creator_id;