migrations/2020-12-02-152437_create_site_aggregates/up.sql

   1 -- Add site aggregates
   2 create table site_aggregates (
   3   id serial primary key,
   4   site_id int references site on update cascade on delete cascade not null,
   5   users bigint not null default 1,
   6   posts bigint not null default 0,
   7   comments bigint not null default 0,
   8   communities bigint not null default 0
   9 );
  10
  11 insert into site_aggregates (site_id, users, posts, comments, communities)
  12   select id as site_id,
  13   ( select coalesce(count(*), 0) from user_ where local = true) as users,
  14   ( select coalesce(count(*), 0) from post where local = true) as posts,
  15   ( select coalesce(count(*), 0) from comment where local = true) as comments,
  16   ( select coalesce(count(*), 0) from community where local = true) as communities
  17   from site;
  18
  19 -- initial site add
  20 create function site_aggregates_site()
  21 returns trigger language plpgsql
  22 as $$
  23 begin
  24   IF (TG_OP = 'INSERT') THEN
  25     insert into site_aggregates (site_id) values (NEW.id);
  26   ELSIF (TG_OP = 'DELETE') THEN
  27     delete from site_aggregates where site_id = OLD.id;
  28   END IF;
  29   return null;
  30 end $$;
  31
  32 create trigger site_aggregates_site
  33 after insert or delete on site
  34 for each row
  35 execute procedure site_aggregates_site();
  36
  37 -- Add site aggregate triggers
  38 -- user
  39 create function site_aggregates_user_insert()
  40 returns trigger language plpgsql
  41 as $$
  42 begin
  43   update site_aggregates
  44   set users = users + 1;
  45   return null;
  46 end $$;
  47
  48 create function site_aggregates_user_delete()
  49 returns trigger language plpgsql
  50 as $$
  51 begin
  52   -- Join to site since the creator might not be there anymore
  53   update site_aggregates sa
  54   set users = users - 1
  55   from site s
  56   where sa.site_id = s.id;
  57   return null;
  58 end $$;
  59
  60 create trigger site_aggregates_user_insert
  61 after insert on user_
  62 for each row
  63 when (NEW.local = true)
  64 execute procedure site_aggregates_user_insert();
  65
  66 create trigger site_aggregates_user_delete
  67 after delete on user_
  68 for each row
  69 when (OLD.local = true)
  70 execute procedure site_aggregates_user_delete();
  71
  72 -- post
  73 create function site_aggregates_post_insert()
  74 returns trigger language plpgsql
  75 as $$
  76 begin
  77   update site_aggregates
  78   set posts = posts + 1;
  79   return null;
  80 end $$;
  81
  82 create function site_aggregates_post_delete()
  83 returns trigger language plpgsql
  84 as $$
  85 begin
  86   update site_aggregates sa
  87   set posts = posts - 1
  88   from site s
  89   where sa.site_id = s.id;
  90   return null;
  91 end $$;
  92
  93 create trigger site_aggregates_post_insert
  94 after insert on post
  95 for each row
  96 when (NEW.local = true)
  97 execute procedure site_aggregates_post_insert();
  98
  99 create trigger site_aggregates_post_delete
 100 after delete on post
 101 for each row
 102 when (OLD.local = true)
 103 execute procedure site_aggregates_post_delete();
 104
 105 -- comment
 106 create function site_aggregates_comment_insert()
 107 returns trigger language plpgsql
 108 as $$
 109 begin
 110   update site_aggregates
 111   set comments = comments + 1;
 112   return null;
 113 end $$;
 114
 115 create function site_aggregates_comment_delete()
 116 returns trigger language plpgsql
 117 as $$
 118 begin
 119   update site_aggregates sa
 120   set comments = comments - 1
 121   from site s
 122   where sa.site_id = s.id;
 123   return null;
 124 end $$;
 125
 126 create trigger site_aggregates_comment_insert
 127 after insert on comment
 128 for each row
 129 when (NEW.local = true)
 130 execute procedure site_aggregates_comment_insert();
 131
 132 create trigger site_aggregates_comment_delete
 133 after delete on comment
 134 for each row
 135 when (OLD.local = true)
 136 execute procedure site_aggregates_comment_delete();
 137
 138 -- community
 139 create function site_aggregates_community_insert()
 140 returns trigger language plpgsql
 141 as $$
 142 begin
 143   update site_aggregates
 144   set communities = communities + 1;
 145   return null;
 146 end $$;
 147
 148 create function site_aggregates_community_delete()
 149 returns trigger language plpgsql
 150 as $$
 151 begin
 152   update site_aggregates sa
 153   set communities = communities - 1
 154   from site s
 155   where sa.site_id = s.id;
 156   return null;
 157 end $$;
 158
 159 create trigger site_aggregates_community_insert
 160 after insert on community
 161 for each row
 162 when (NEW.local = true)
 163 execute procedure site_aggregates_community_insert();
 164
 165 create trigger site_aggregates_community_delete
 166 after delete on community
 167 for each row
 168 when (OLD.local = true)
 169 execute procedure site_aggregates_community_delete();