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 drop trigger if exists community_aggregates_comment_count on comment;
   3 drop trigger if exists site_aggregates_comment_insert on comment;
   4 drop trigger if exists site_aggregates_comment_delete on comment;
   5 drop trigger if exists site_aggregates_post_insert on post;
   6 drop trigger if exists site_aggregates_post_delete on post;
   7 drop trigger if exists site_aggregates_community_insert on community;
   8 drop trigger if exists site_aggregates_community_delete on community;
   9 drop trigger if exists person_aggregates_post_count on post;
  10 drop trigger if exists person_aggregates_comment_count on comment;
  11
  12 drop function was_removed_or_deleted(TG_OP text, OLD record, NEW record);
  13 drop function was_restored_or_created(TG_OP text, OLD record, NEW record);
  14
  15 -- Community aggregate functions
  16
  17 create or replace function community_aggregates_post_count()
  18     returns trigger language plpgsql
  19 as $$
  20 begin
  21   IF (TG_OP = 'INSERT') THEN
  22 update community_aggregates
  23 set posts = posts + 1 where community_id = NEW.community_id;
  24 ELSIF (TG_OP = 'DELETE') THEN
  25 update community_aggregates
  26 set posts = posts - 1 where community_id = OLD.community_id;
  27
  28 -- Update the counts if the post got deleted
  29 update community_aggregates ca
  30 set posts = coalesce(cd.posts, 0),
  31     comments = coalesce(cd.comments, 0)
  32     from (
  33       select
  34       c.id,
  35       count(distinct p.id) as posts,
  36       count(distinct ct.id) as comments
  37       from community c
  38       left join post p on c.id = p.community_id
  39       left join comment ct on p.id = ct.post_id
  40       group by c.id
  41     ) cd
  42 where ca.community_id = OLD.community_id;
  43 END IF;
  44 return null;
  45 end $$;
  46
  47 create or replace function community_aggregates_comment_count()
  48     returns trigger language plpgsql
  49 as $$
  50 begin
  51   IF (TG_OP = 'INSERT') THEN
  52 update community_aggregates ca
  53 set comments = comments + 1 from comment c, post p
  54 where p.id = c.post_id
  55   and p.id = NEW.post_id
  56   and ca.community_id = p.community_id;
  57 ELSIF (TG_OP = 'DELETE') THEN
  58 update community_aggregates ca
  59 set comments = comments - 1 from comment c, post p
  60 where p.id = c.post_id
  61   and p.id = OLD.post_id
  62   and ca.community_id = p.community_id;
  63
  64 END IF;
  65 return null;
  66 end $$;
  67
  68 -- Community aggregate triggers
  69
  70 create trigger community_aggregates_post_count
  71     after insert or delete on post
  72 for each row
  73 execute procedure community_aggregates_post_count();
  74
  75 create trigger community_aggregates_comment_count
  76     after insert or delete on comment
  77 for each row
  78 execute procedure community_aggregates_comment_count();
  79
  80 -- Site aggregate functions
  81
  82 create or replace function site_aggregates_post_insert()
  83     returns trigger language plpgsql
  84 as $$
  85 begin
  86 update site_aggregates
  87 set posts = posts + 1;
  88 return null;
  89 end $$;
  90
  91 create or replace function site_aggregates_post_delete()
  92     returns trigger language plpgsql
  93 as $$
  94 begin
  95 update site_aggregates sa
  96 set posts = posts - 1
  97     from site s
  98 where sa.site_id = s.id;
  99 return null;
 100 end $$;
 101
 102 create or replace function site_aggregates_comment_insert()
 103     returns trigger language plpgsql
 104 as $$
 105 begin
 106 update site_aggregates
 107 set comments = comments + 1;
 108 return null;
 109 end $$;
 110
 111 create or replace function site_aggregates_comment_delete()
 112     returns trigger language plpgsql
 113 as $$
 114 begin
 115 update site_aggregates sa
 116 set comments = comments - 1
 117     from site s
 118 where sa.site_id = s.id;
 119 return null;
 120 end $$;
 121
 122 create or replace function site_aggregates_community_insert()
 123     returns trigger language plpgsql
 124 as $$
 125 begin
 126 update site_aggregates
 127 set communities = communities + 1;
 128 return null;
 129 end $$;
 130
 131 create or replace function site_aggregates_community_delete()
 132     returns trigger language plpgsql
 133 as $$
 134 begin
 135 update site_aggregates sa
 136 set communities = communities - 1
 137     from site s
 138 where sa.site_id = s.id;
 139 return null;
 140 end $$;
 141
 142
 143 -- Site update triggers
 144
 145 create trigger site_aggregates_post_insert
 146     after insert on post
 147     for each row
 148     when (NEW.local = true)
 149     execute procedure site_aggregates_post_insert();
 150
 151 create trigger site_aggregates_post_delete
 152     after delete on post
 153     for each row
 154     when (OLD.local = true)
 155     execute procedure site_aggregates_post_delete();
 156
 157 create trigger site_aggregates_comment_insert
 158     after insert on comment
 159     for each row
 160     when (NEW.local = true)
 161     execute procedure site_aggregates_comment_insert();
 162
 163 create trigger site_aggregates_comment_delete
 164     after delete on comment
 165     for each row
 166     when (OLD.local = true)
 167     execute procedure site_aggregates_comment_delete();
 168
 169 create trigger site_aggregates_community_insert
 170     after insert on community
 171     for each row
 172     when (NEW.local = true)
 173     execute procedure site_aggregates_community_insert();
 174
 175 create trigger site_aggregates_community_delete
 176     after delete on community
 177     for each row
 178     when (OLD.local = true)
 179     execute procedure site_aggregates_community_delete();
 180
 181 -- Person aggregate functions
 182
 183 create or replace function person_aggregates_post_count()
 184     returns trigger language plpgsql
 185 as $$
 186 begin
 187     IF (TG_OP = 'INSERT') THEN
 188 update person_aggregates
 189 set post_count = post_count + 1 where person_id = NEW.creator_id;
 190
 191 ELSIF (TG_OP = 'DELETE') THEN
 192 update person_aggregates
 193 set post_count = post_count - 1 where person_id = OLD.creator_id;
 194
 195 -- If the post gets deleted, the score calculation trigger won't fire,
 196 -- so you need to re-calculate
 197 update person_aggregates ua
 198 set post_score = pd.score
 199     from (
 200                  select u.id,
 201                         coalesce(0, sum(pl.score)) as score
 202                         -- User join because posts could be empty
 203                  from person u
 204                           left join post p on u.id = p.creator_id
 205                           left join post_like pl on p.id = pl.post_id
 206                  group by u.id
 207              ) pd
 208 where ua.person_id = OLD.creator_id;
 209
 210 END IF;
 211 return null;
 212 end $$;
 213
 214 create or replace function person_aggregates_comment_count()
 215     returns trigger language plpgsql
 216 as $$
 217 begin
 218     IF (TG_OP = 'INSERT') THEN
 219 update person_aggregates
 220 set comment_count = comment_count + 1 where person_id = NEW.creator_id;
 221 ELSIF (TG_OP = 'DELETE') THEN
 222 update person_aggregates
 223 set comment_count = comment_count - 1 where person_id = OLD.creator_id;
 224
 225 -- If the comment gets deleted, the score calculation trigger won't fire,
 226 -- so you need to re-calculate
 227 update person_aggregates ua
 228 set comment_score = cd.score
 229     from (
 230                  select u.id,
 231                         coalesce(0, sum(cl.score)) as score
 232                         -- User join because comments could be empty
 233                  from person u
 234                           left join comment c on u.id = c.creator_id
 235                           left join comment_like cl on c.id = cl.comment_id
 236                  group by u.id
 237              ) cd
 238 where ua.person_id = OLD.creator_id;
 239 END IF;
 240 return null;
 241 end $$;
 242
 243
 244 -- Person aggregate triggers
 245
 246 create trigger person_aggregates_post_count
 247     after insert or delete on post
 248     for each row
 249 execute procedure person_aggregates_post_count();
 250
 251 create trigger person_aggregates_comment_count
 252     after insert or delete on comment
 253     for each row
 254 execute procedure person_aggregates_comment_count();