migrations/2023-07-08-101154_fix_soft_delete_aggregates/down.sql

   1 -- 2023-06-19-120700_no_double_deletion/up.sql
   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     IF (TG_OP = 'DELETE' AND OLD.deleted = 'f' AND OLD.removed = 'f') THEN
  11         RETURN TRUE;
  12     END IF;
  13     RETURN TG_OP = 'UPDATE'
  14         AND ((OLD.deleted = 'f'
  15                 AND NEW.deleted = 't')
  16             OR (OLD.removed = 'f'
  17                 AND NEW.removed = 't'));
  18 END
  19 $$;
  20
  21 -- 2022-04-04-183652_update_community_aggregates_on_soft_delete/up.sql
  22 CREATE OR REPLACE FUNCTION was_restored_or_created (TG_OP text, OLD record, NEW record)
  23     RETURNS boolean
  24     LANGUAGE plpgsql
  25     AS $$
  26 BEGIN
  27     IF (TG_OP = 'DELETE') THEN
  28         RETURN FALSE;
  29     END IF;
  30     IF (TG_OP = 'INSERT') THEN
  31         RETURN TRUE;
  32     END IF;
  33     RETURN TG_OP = 'UPDATE'
  34         AND ((OLD.deleted = 't'
  35                 AND NEW.deleted = 'f')
  36             OR (OLD.removed = 't'
  37                 AND NEW.removed = 'f'));
  38 END
  39 $$;
  40
  41 -- 2021-08-02-002342_comment_count_fixes/up.sql
  42 CREATE OR REPLACE FUNCTION post_aggregates_comment_deleted ()
  43     RETURNS TRIGGER
  44     LANGUAGE plpgsql
  45     AS $$
  46 BEGIN
  47     IF NEW.deleted = TRUE THEN
  48         UPDATE
  49             post_aggregates pa
  50         SET
  51             comments = comments - 1
  52         WHERE
  53             pa.post_id = NEW.post_id;
  54     ELSE
  55         UPDATE
  56             post_aggregates pa
  57         SET
  58             comments = comments + 1
  59         WHERE
  60             pa.post_id = NEW.post_id;
  61     END IF;
  62     RETURN NULL;
  63 END
  64 $$;
  65
  66 CREATE TRIGGER post_aggregates_comment_set_deleted
  67     AFTER UPDATE OF deleted ON comment
  68     FOR EACH ROW
  69     EXECUTE PROCEDURE post_aggregates_comment_deleted ();
  70
  71 CREATE OR REPLACE 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             newest_comment_time = NEW.published
  82         WHERE
  83             pa.post_id = NEW.post_id;
  84         -- A 2 day necro-bump limit
  85         UPDATE
  86             post_aggregates pa
  87         SET
  88             newest_comment_time_necro = NEW.published
  89         FROM
  90             post p
  91         WHERE
  92             pa.post_id = p.id
  93             AND pa.post_id = NEW.post_id
  94             -- Fix issue with being able to necro-bump your own post
  95             AND NEW.creator_id != p.creator_id
  96             AND pa.published > ('now'::timestamp - '2 days'::interval);
  97     ELSIF (TG_OP = 'DELETE') THEN
  98         -- Join to post because that post may not exist anymore
  99         UPDATE
 100             post_aggregates pa
 101         SET
 102             comments = comments - 1
 103         FROM
 104             post p
 105         WHERE
 106             pa.post_id = p.id
 107             AND pa.post_id = OLD.post_id;
 108     ELSIF (TG_OP = 'UPDATE') THEN
 109         -- Join to post because that post may not exist anymore
 110         UPDATE
 111             post_aggregates pa
 112         SET
 113             comments = comments - 1
 114         FROM
 115             post p
 116         WHERE
 117             pa.post_id = p.id
 118             AND pa.post_id = OLD.post_id;
 119     END IF;
 120     RETURN NULL;
 121 END
 122 $$;
 123
 124 -- 2020-12-10-152350_create_post_aggregates/up.sql
 125 CREATE OR REPLACE TRIGGER post_aggregates_comment_count
 126     AFTER INSERT OR DELETE ON comment
 127     FOR EACH ROW
 128     EXECUTE PROCEDURE post_aggregates_comment_count ();
 129