1 DROP TRIGGER IF EXISTS community_aggregates_post_count ON post;
3 DROP TRIGGER IF EXISTS community_aggregates_comment_count ON comment;
5 DROP TRIGGER IF EXISTS site_aggregates_comment_insert ON comment;
7 DROP TRIGGER IF EXISTS site_aggregates_comment_delete ON comment;
9 DROP TRIGGER IF EXISTS site_aggregates_post_insert ON post;
11 DROP TRIGGER IF EXISTS site_aggregates_post_delete ON post;
13 DROP TRIGGER IF EXISTS site_aggregates_community_insert ON community;
15 DROP TRIGGER IF EXISTS site_aggregates_community_delete ON community;
17 DROP TRIGGER IF EXISTS person_aggregates_post_count ON post;
19 DROP TRIGGER IF EXISTS person_aggregates_comment_count ON comment;
21 CREATE OR REPLACE FUNCTION was_removed_or_deleted (TG_OP text, OLD record, NEW record)
26 IF (TG_OP = 'INSERT') THEN
29 IF (TG_OP = 'DELETE') THEN
32 RETURN TG_OP = 'UPDATE'
33 AND ((OLD.deleted = 'f'
34 AND NEW.deleted = 't')
36 AND NEW.removed = 't'));
40 CREATE OR REPLACE FUNCTION was_restored_or_created (TG_OP text, OLD record, NEW record)
45 IF (TG_OP = 'DELETE') THEN
48 IF (TG_OP = 'INSERT') THEN
51 RETURN TG_OP = 'UPDATE'
52 AND ((OLD.deleted = 't'
53 AND NEW.deleted = 'f')
55 AND NEW.removed = 'f'));
59 -- Community aggregate functions
60 CREATE OR REPLACE FUNCTION community_aggregates_post_count ()
65 IF (was_restored_or_created (TG_OP, OLD, NEW)) THEN
71 community_id = NEW.community_id;
72 IF (TG_OP = 'UPDATE') THEN
73 -- Post was restored, so restore comment counts as well
75 community_aggregates ca
77 posts = coalesce(cd.posts, 0),
78 comments = coalesce(cd.comments, 0)
82 count(DISTINCT p.id) AS posts,
83 count(DISTINCT ct.id) AS comments
86 LEFT JOIN post p ON c.id = p.community_id
89 LEFT JOIN comment ct ON p.id = ct.post_id
93 c.id = NEW.community_id
97 ca.community_id = NEW.community_id;
99 ELSIF (was_removed_or_deleted (TG_OP, OLD, NEW)) THEN
105 community_id = OLD.community_id;
106 -- Update the counts if the post got deleted
108 community_aggregates ca
110 posts = coalesce(cd.posts, 0),
111 comments = coalesce(cd.comments, 0)
115 count(DISTINCT p.id) AS posts,
116 count(DISTINCT ct.id) AS comments
119 LEFT JOIN post p ON c.id = p.community_id
122 LEFT JOIN comment ct ON p.id = ct.post_id
126 c.id = OLD.community_id
130 ca.community_id = OLD.community_id;
137 CREATE OR REPLACE FUNCTION community_aggregates_comment_count ()
142 IF (was_restored_or_created (TG_OP, OLD, NEW)) THEN
144 community_aggregates ca
146 comments = comments + 1
152 AND p.id = NEW.post_id
153 AND ca.community_id = p.community_id;
154 ELSIF (was_removed_or_deleted (TG_OP, OLD, NEW)) THEN
156 community_aggregates ca
158 comments = comments - 1
164 AND p.id = OLD.post_id
165 AND ca.community_id = p.community_id;
171 -- Community aggregate triggers
172 CREATE TRIGGER community_aggregates_post_count
173 AFTER INSERT OR DELETE OR UPDATE OF removed,
176 EXECUTE PROCEDURE community_aggregates_post_count ();
178 CREATE TRIGGER community_aggregates_comment_count
179 AFTER INSERT OR DELETE OR UPDATE OF removed,
182 EXECUTE PROCEDURE community_aggregates_comment_count ();
184 -- Site aggregate functions
185 CREATE OR REPLACE FUNCTION site_aggregates_post_insert ()
190 IF (was_restored_or_created (TG_OP, OLD, NEW)) THEN
204 CREATE OR REPLACE FUNCTION site_aggregates_post_delete ()
209 IF (was_removed_or_deleted (TG_OP, OLD, NEW)) THEN
223 CREATE OR REPLACE FUNCTION site_aggregates_comment_insert ()
228 IF (was_restored_or_created (TG_OP, OLD, NEW)) THEN
232 comments = comments + 1
242 CREATE OR REPLACE FUNCTION site_aggregates_comment_delete ()
247 IF (was_removed_or_deleted (TG_OP, OLD, NEW)) THEN
251 comments = comments - 1
261 CREATE OR REPLACE FUNCTION site_aggregates_community_insert ()
266 IF (was_restored_or_created (TG_OP, OLD, NEW)) THEN
270 communities = communities + 1
280 CREATE OR REPLACE FUNCTION site_aggregates_community_delete ()
285 IF (was_removed_or_deleted (TG_OP, OLD, NEW)) THEN
289 communities = communities - 1
299 -- Site aggregate triggers
300 CREATE TRIGGER site_aggregates_post_insert
301 AFTER INSERT OR UPDATE OF removed,
304 WHEN (NEW.local = TRUE)
305 EXECUTE PROCEDURE site_aggregates_post_insert ();
307 CREATE TRIGGER site_aggregates_post_delete
308 AFTER DELETE OR UPDATE OF removed,
311 WHEN (OLD.local = TRUE)
312 EXECUTE PROCEDURE site_aggregates_post_delete ();
314 CREATE TRIGGER site_aggregates_comment_insert
315 AFTER INSERT OR UPDATE OF removed,
318 WHEN (NEW.local = TRUE)
319 EXECUTE PROCEDURE site_aggregates_comment_insert ();
321 CREATE TRIGGER site_aggregates_comment_delete
322 AFTER DELETE OR UPDATE OF removed,
325 WHEN (OLD.local = TRUE)
326 EXECUTE PROCEDURE site_aggregates_comment_delete ();
328 CREATE TRIGGER site_aggregates_community_insert
329 AFTER INSERT OR UPDATE OF removed,
332 WHEN (NEW.local = TRUE)
333 EXECUTE PROCEDURE site_aggregates_community_insert ();
335 CREATE TRIGGER site_aggregates_community_delete
336 AFTER DELETE OR UPDATE OF removed,
339 WHEN (OLD.local = TRUE)
340 EXECUTE PROCEDURE site_aggregates_community_delete ();
342 -- Person aggregate functions
343 CREATE OR REPLACE FUNCTION person_aggregates_post_count ()
348 IF (was_restored_or_created (TG_OP, OLD, NEW)) THEN
352 post_count = post_count + 1
354 person_id = NEW.creator_id;
355 ELSIF (was_removed_or_deleted (TG_OP, OLD, NEW)) THEN
359 post_count = post_count - 1
361 person_id = OLD.creator_id;
362 -- If the post gets deleted, the score calculation trigger won't fire,
363 -- so you need to re-calculate
367 post_score = pd.score
371 coalesce(0, sum(pl.score)) AS score
372 -- User join because posts could be empty
375 LEFT JOIN post p ON u.id = p.creator_id
378 LEFT JOIN post_like pl ON p.id = pl.post_id
382 ua.person_id = OLD.creator_id;
388 CREATE OR REPLACE FUNCTION person_aggregates_comment_count ()
393 IF (was_restored_or_created (TG_OP, OLD, NEW)) THEN
397 comment_count = comment_count + 1
399 person_id = NEW.creator_id;
400 ELSIF (was_removed_or_deleted (TG_OP, OLD, NEW)) THEN
404 comment_count = comment_count - 1
406 person_id = OLD.creator_id;
407 -- If the comment gets deleted, the score calculation trigger won't fire,
408 -- so you need to re-calculate
412 comment_score = cd.score
416 coalesce(0, sum(cl.score)) AS score
417 -- User join because comments could be empty
420 LEFT JOIN comment c ON u.id = c.creator_id
423 LEFT JOIN comment_like cl ON c.id = cl.comment_id
427 ua.person_id = OLD.creator_id;
433 -- Person aggregate triggers
434 CREATE TRIGGER person_aggregates_post_count
435 AFTER INSERT OR DELETE OR UPDATE OF removed,
438 EXECUTE PROCEDURE person_aggregates_post_count ();
440 CREATE TRIGGER person_aggregates_comment_count
441 AFTER INSERT OR DELETE OR UPDATE OF removed,
444 EXECUTE PROCEDURE person_aggregates_comment_count ();