2 CREATE TABLE site_aggregates (
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
11 INSERT INTO site_aggregates (site_id, users, posts, comments, communities)
20 local = TRUE) AS users,
27 local = TRUE) AS posts,
34 local = TRUE) AS comments,
41 local = TRUE) AS communities
46 CREATE FUNCTION site_aggregates_site ()
51 IF (TG_OP = 'INSERT') THEN
52 INSERT INTO site_aggregates (site_id)
54 ELSIF (TG_OP = 'DELETE') THEN
55 DELETE FROM site_aggregates
56 WHERE site_id = OLD.id;
62 CREATE TRIGGER site_aggregates_site
63 AFTER INSERT OR DELETE ON site
65 EXECUTE PROCEDURE site_aggregates_site ();
67 -- Add site aggregate triggers
69 CREATE FUNCTION site_aggregates_user_insert ()
82 CREATE FUNCTION site_aggregates_user_delete ()
87 -- Join to site since the creator might not be there anymore
100 CREATE TRIGGER site_aggregates_user_insert
101 AFTER INSERT ON user_
103 WHEN (NEW.local = TRUE)
104 EXECUTE PROCEDURE site_aggregates_user_insert ();
106 CREATE TRIGGER site_aggregates_user_delete
107 AFTER DELETE ON user_
109 WHEN (OLD.local = TRUE)
110 EXECUTE PROCEDURE site_aggregates_user_delete ();
113 CREATE FUNCTION site_aggregates_post_insert ()
126 CREATE FUNCTION site_aggregates_post_delete ()
143 CREATE TRIGGER site_aggregates_post_insert
146 WHEN (NEW.local = TRUE)
147 EXECUTE PROCEDURE site_aggregates_post_insert ();
149 CREATE TRIGGER site_aggregates_post_delete
152 WHEN (OLD.local = TRUE)
153 EXECUTE PROCEDURE site_aggregates_post_delete ();
156 CREATE FUNCTION site_aggregates_comment_insert ()
164 comments = comments + 1;
169 CREATE FUNCTION site_aggregates_comment_delete ()
177 comments = comments - 1
186 CREATE TRIGGER site_aggregates_comment_insert
187 AFTER INSERT ON comment
189 WHEN (NEW.local = TRUE)
190 EXECUTE PROCEDURE site_aggregates_comment_insert ();
192 CREATE TRIGGER site_aggregates_comment_delete
193 AFTER DELETE ON comment
195 WHEN (OLD.local = TRUE)
196 EXECUTE PROCEDURE site_aggregates_comment_delete ();
199 CREATE FUNCTION site_aggregates_community_insert ()
207 communities = communities + 1;
212 CREATE FUNCTION site_aggregates_community_delete ()
220 communities = communities - 1
229 CREATE TRIGGER site_aggregates_community_insert
230 AFTER INSERT ON community
232 WHEN (NEW.local = TRUE)
233 EXECUTE PROCEDURE site_aggregates_community_insert ();
235 CREATE TRIGGER site_aggregates_community_delete
236 AFTER DELETE ON community
238 WHEN (OLD.local = TRUE)
239 EXECUTE PROCEDURE site_aggregates_community_delete ();