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;
12 create or replace function was_removed_or_deleted(TG_OP text, OLD record, NEW record)
17 IF (TG_OP = 'INSERT') THEN
21 IF (TG_OP = 'DELETE') THEN
25 return TG_OP = 'UPDATE' AND (
26 (OLD.deleted = 'f' AND NEW.deleted = 't') OR
27 (OLD.removed = 'f' AND NEW.removed = 't')
31 create or replace function was_restored_or_created(TG_OP text, OLD record, NEW record)
36 IF (TG_OP = 'DELETE') THEN
40 IF (TG_OP = 'INSERT') THEN
44 return TG_OP = 'UPDATE' AND (
45 (OLD.deleted = 't' AND NEW.deleted = 'f') OR
46 (OLD.removed = 't' AND NEW.removed = 'f')
50 -- Community aggregate functions
52 create or replace function community_aggregates_post_count()
53 returns trigger language plpgsql
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;
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)
68 count(distinct p.id) as posts,
69 count(distinct ct.id) as comments
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
76 where ca.community_id = NEW.community_id;
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;
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)
90 count(distinct p.id) as posts,
91 count(distinct ct.id) as comments
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
98 where ca.community_id = OLD.community_id;
104 create or replace function community_aggregates_comment_count()
105 returns trigger language plpgsql
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;
125 -- Community aggregate triggers
127 create trigger community_aggregates_post_count
128 after insert or delete or update of removed, deleted on post
130 execute procedure community_aggregates_post_count();
132 create trigger community_aggregates_comment_count
133 after insert or delete or update of removed, deleted on comment
135 execute procedure community_aggregates_comment_count();
137 -- Site aggregate functions
139 create or replace function site_aggregates_post_insert()
140 returns trigger language plpgsql
143 IF (was_restored_or_created(TG_OP, OLD, NEW)) THEN
144 update site_aggregates sa
145 set posts = posts + 1
147 where sa.site_id = s.id;
152 create or replace function site_aggregates_post_delete()
153 returns trigger language plpgsql
156 IF (was_removed_or_deleted(TG_OP, OLD, NEW)) THEN
157 update site_aggregates sa
158 set posts = posts - 1
160 where sa.site_id = s.id;
165 create or replace function site_aggregates_comment_insert()
166 returns trigger language plpgsql
169 IF (was_restored_or_created(TG_OP, OLD, NEW)) THEN
170 update site_aggregates sa
171 set comments = comments + 1
173 where sa.site_id = s.id;
178 create or replace function site_aggregates_comment_delete()
179 returns trigger language plpgsql
182 IF (was_removed_or_deleted(TG_OP, OLD, NEW)) THEN
183 update site_aggregates sa
184 set comments = comments - 1
186 where sa.site_id = s.id;
191 create or replace function site_aggregates_community_insert()
192 returns trigger language plpgsql
195 IF (was_restored_or_created(TG_OP, OLD, NEW)) THEN
196 update site_aggregates sa
197 set communities = communities + 1
199 where sa.site_id = s.id;
204 create or replace function site_aggregates_community_delete()
205 returns trigger language plpgsql
208 IF (was_removed_or_deleted(TG_OP, OLD, NEW)) THEN
209 update site_aggregates sa
210 set communities = communities - 1
212 where sa.site_id = s.id;
217 -- Site aggregate triggers
219 create trigger site_aggregates_post_insert
220 after insert or update of removed, deleted on post
222 when (NEW.local = true)
223 execute procedure site_aggregates_post_insert();
225 create trigger site_aggregates_post_delete
226 after delete or update of removed, deleted on post
228 when (OLD.local = true)
229 execute procedure site_aggregates_post_delete();
231 create trigger site_aggregates_comment_insert
232 after insert or update of removed, deleted on comment
234 when (NEW.local = true)
235 execute procedure site_aggregates_comment_insert();
237 create trigger site_aggregates_comment_delete
238 after delete or update of removed, deleted on comment
240 when (OLD.local = true)
241 execute procedure site_aggregates_comment_delete();
243 create trigger site_aggregates_community_insert
244 after insert or update of removed, deleted on community
246 when (NEW.local = true)
247 execute procedure site_aggregates_community_insert();
249 create trigger site_aggregates_community_delete
250 after delete or update of removed, deleted on community
252 when (OLD.local = true)
253 execute procedure site_aggregates_community_delete();
255 -- Person aggregate functions
257 create or replace function person_aggregates_post_count()
258 returns trigger language plpgsql
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;
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;
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
275 coalesce(0, sum(pl.score)) as score
276 -- User join because posts could be empty
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
282 where ua.person_id = OLD.creator_id;
288 create or replace function person_aggregates_comment_count()
289 returns trigger language plpgsql
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;
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
305 coalesce(0, sum(cl.score)) as score
306 -- User join because comments could be empty
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
312 where ua.person_id = OLD.creator_id;
317 -- Person aggregate triggers
319 create trigger person_aggregates_post_count
320 after insert or delete or update of removed, deleted on post
322 execute procedure person_aggregates_post_count();
324 create trigger person_aggregates_comment_count
325 after insert or delete or update of removed, deleted on comment
327 execute procedure person_aggregates_comment_count();