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