4 DROP MATERIALIZED VIEW user_mview;
6 DROP VIEW community_mview;
8 DROP MATERIALIZED VIEW private_message_mview;
10 DROP VIEW user_mention_mview;
14 DROP VIEW comment_mview;
16 DROP MATERIALIZED VIEW post_aggregates_mview;
18 DROP MATERIALIZED VIEW community_aggregates_mview;
20 DROP MATERIALIZED VIEW comment_aggregates_mview;
22 DROP TRIGGER refresh_private_message ON private_message;
27 CREATE VIEW user_view AS
40 u.send_notifications_to_email,
42 coalesce(pd.posts, 0) AS number_of_posts,
43 coalesce(pd.score, 0) AS post_score,
44 coalesce(cd.comments, 0) AS number_of_comments,
45 coalesce(cd.score, 0) AS comment_score
50 p.creator_id AS creator_id,
51 count(DISTINCT p.id) AS posts,
52 sum(pl.score) AS score
55 JOIN post_like pl ON p.id = pl.post_id
57 p.creator_id) pd ON u.id = pd.creator_id
61 count(DISTINCT c.id) AS comments,
62 sum(cl.score) AS score
65 JOIN comment_like cl ON c.id = cl.comment_id
67 c.creator_id) cd ON u.id = cd.creator_id;
69 CREATE TABLE user_fast AS
78 DROP TRIGGER refresh_user ON user_;
80 CREATE TRIGGER refresh_user
81 AFTER INSERT OR UPDATE OR DELETE ON user_
83 EXECUTE PROCEDURE refresh_user ();
86 -- insert into user_(name, password_encrypted) values ('test_name', 'bleh');
88 -- delete from user_ where name like 'test_name';
90 -- update user_ set avatar = 'hai' where name like 'test_name';
91 CREATE OR REPLACE FUNCTION refresh_user ()
96 IF (TG_OP = 'DELETE') THEN
99 ELSIF (TG_OP = 'UPDATE') THEN
100 DELETE FROM user_fast
102 INSERT INTO user_fast
109 -- Refresh post_fast, cause of user info changes
110 DELETE FROM post_aggregates_fast
111 WHERE creator_id = NEW.id;
112 INSERT INTO post_aggregates_fast
119 DELETE FROM comment_aggregates_fast
120 WHERE creator_id = NEW.id;
121 INSERT INTO comment_aggregates_fast
125 comment_aggregates_view
128 ELSIF (TG_OP = 'INSERT') THEN
129 INSERT INTO user_fast
142 -- Redoing the views : Credit eiknat
145 DROP VIEW post_aggregates_view;
147 CREATE VIEW post_aggregates_view AS
151 u.actor_id AS creator_actor_id,
152 u."local" AS creator_local,
153 u."name" AS creator_name,
154 u.avatar AS creator_avatar,
156 cb.id::bool AS banned_from_community,
158 c.actor_id AS community_actor_id,
159 c."local" AS community_local,
160 c."name" AS community_name,
161 c.removed AS community_removed,
162 c.deleted AS community_deleted,
163 c.nsfw AS community_nsfw,
164 -- post score data/comment count
165 coalesce(ct.comments, 0) AS number_of_comments,
166 coalesce(pl.score, 0) AS score,
167 coalesce(pl.upvotes, 0) AS upvotes,
168 coalesce(pl.downvotes, 0) AS downvotes,
169 hot_rank (coalesce(pl.score, 0), (
170 CASE WHEN (p.published < ('now'::timestamp - '1 month'::interval)) THEN
173 greatest (ct.recent_comment_time, p.published)
176 CASE WHEN (p.published < ('now'::timestamp - '1 month'::interval)) THEN
179 greatest (ct.recent_comment_time, p.published)
180 END) AS newest_activity_time
183 LEFT JOIN user_ u ON p.creator_id = u.id
184 LEFT JOIN community_user_ban cb ON p.creator_id = cb.user_id
185 AND p.community_id = cb.community_id
186 LEFT JOIN community c ON p.community_id = c.id
190 count(*) AS comments,
191 max(published) AS recent_comment_time
195 post_id) ct ON ct.post_id = p.id
200 sum(score) FILTER (WHERE score = 1) AS upvotes,
201 - sum(score) FILTER (WHERE score = - 1) AS downvotes
205 post_id) pl ON pl.post_id = p.id
209 CREATE VIEW post_view AS
213 us.user_vote AS my_vote,
214 us.is_subbed::bool AS subscribed,
215 us.is_read::bool AS read,
216 us.is_saved::bool AS saved
218 post_aggregates_view pav
222 coalesce(cf.community_id, 0) AS is_subbed,
223 coalesce(pr.post_id, 0) AS is_read,
224 coalesce(ps.post_id, 0) AS is_saved,
225 coalesce(pl.score, 0) AS user_vote
228 LEFT JOIN community_user_ban cb ON u.id = cb.user_id
229 AND cb.community_id = pav.community_id
230 LEFT JOIN community_follower cf ON u.id = cf.user_id
231 AND cf.community_id = pav.community_id
232 LEFT JOIN post_read pr ON u.id = pr.user_id
233 AND pr.post_id = pav.id
234 LEFT JOIN post_saved ps ON u.id = ps.user_id
235 AND ps.post_id = pav.id
236 LEFT JOIN post_like pl ON u.id = pl.user_id
237 AND pav.id = pl.post_id) AS us
247 post_aggregates_view pav;
249 -- The post fast table
250 CREATE TABLE post_aggregates_fast AS
254 post_aggregates_view;
256 ALTER TABLE post_aggregates_fast
257 ADD PRIMARY KEY (id);
259 -- For the hot rank resorting
260 CREATE INDEX idx_post_aggregates_fast_hot_rank_published ON post_aggregates_fast (hot_rank DESC, published DESC);
262 CREATE VIEW post_fast_view AS
266 us.user_vote AS my_vote,
267 us.is_subbed::bool AS subscribed,
268 us.is_read::bool AS read,
269 us.is_saved::bool AS saved
271 post_aggregates_fast pav
275 coalesce(cf.community_id, 0) AS is_subbed,
276 coalesce(pr.post_id, 0) AS is_read,
277 coalesce(ps.post_id, 0) AS is_saved,
278 coalesce(pl.score, 0) AS user_vote
281 LEFT JOIN community_user_ban cb ON u.id = cb.user_id
282 AND cb.community_id = pav.community_id
283 LEFT JOIN community_follower cf ON u.id = cf.user_id
284 AND cf.community_id = pav.community_id
285 LEFT JOIN post_read pr ON u.id = pr.user_id
286 AND pr.post_id = pav.id
287 LEFT JOIN post_saved ps ON u.id = ps.user_id
288 AND ps.post_id = pav.id
289 LEFT JOIN post_like pl ON u.id = pl.user_id
290 AND pav.id = pl.post_id) AS us
300 post_aggregates_fast pav;
302 DROP TRIGGER refresh_post ON post;
304 CREATE TRIGGER refresh_post
305 AFTER INSERT OR UPDATE OR DELETE ON post
307 EXECUTE PROCEDURE refresh_post ();
310 -- select id, name from post_fast_view where name like 'test_post' and user_id is null;
312 -- insert into post(name, creator_id, community_id) values ('test_post', 2, 2);
314 -- delete from post where name like 'test_post';
316 -- update post set community_id = 4 where name like 'test_post';
317 CREATE OR REPLACE FUNCTION refresh_post ()
322 IF (TG_OP = 'DELETE') THEN
323 DELETE FROM post_aggregates_fast
325 -- Update community number of posts
327 community_aggregates_fast
329 number_of_posts = number_of_posts - 1
331 id = OLD.community_id;
332 ELSIF (TG_OP = 'UPDATE') THEN
333 DELETE FROM post_aggregates_fast
335 INSERT INTO post_aggregates_fast
342 ELSIF (TG_OP = 'INSERT') THEN
343 INSERT INTO post_aggregates_fast
350 -- Update that users number of posts, post score
351 DELETE FROM user_fast
352 WHERE id = NEW.creator_id;
353 INSERT INTO user_fast
360 -- Update community number of posts
362 community_aggregates_fast
364 number_of_posts = number_of_posts + 1
366 id = NEW.community_id;
367 -- Update the hot rank on the post table
368 -- TODO this might not correctly update it, using a 1 week interval
370 post_aggregates_fast AS paf
372 hot_rank = pav.hot_rank
374 post_aggregates_view AS pav
377 AND (pav.published > ('now'::timestamp - '1 week'::interval));
384 -- Redoing the views : Credit eiknat
385 DROP VIEW community_moderator_view;
387 DROP VIEW community_follower_view;
389 DROP VIEW community_user_ban_view;
391 DROP VIEW community_view;
393 DROP VIEW community_aggregates_view;
395 CREATE VIEW community_aggregates_view AS
411 u.actor_id AS creator_actor_id,
412 u.local AS creator_local,
413 u.name AS creator_name,
414 u.avatar AS creator_avatar,
415 cat.name AS category_name,
416 coalesce(cf.subs, 0) AS number_of_subscribers,
417 coalesce(cd.posts, 0) AS number_of_posts,
418 coalesce(cd.comments, 0) AS number_of_comments,
419 hot_rank (cf.subs, c.published) AS hot_rank
422 LEFT JOIN user_ u ON c.creator_id = u.id
423 LEFT JOIN category cat ON c.category_id = cat.id
427 count(DISTINCT p.id) AS posts,
428 count(DISTINCT ct.id) AS comments
431 JOIN comment ct ON p.id = ct.post_id
433 p.community_id) cd ON cd.community_id = c.id
441 community_id) cf ON cf.community_id = c.id;
443 CREATE VIEW community_view AS
447 us.is_subbed::bool AS subscribed
449 community_aggregates_view cv
453 coalesce(cf.community_id, 0) AS is_subbed
456 LEFT JOIN community_follower cf ON u.id = cf.user_id
457 AND cf.community_id = cv.id) AS us
464 community_aggregates_view cv;
466 CREATE VIEW community_moderator_view AS
469 u.actor_id AS user_actor_id,
470 u.local AS user_local,
473 c.actor_id AS community_actor_id,
474 c.local AS community_local,
475 c.name AS community_name
477 community_moderator cm
478 LEFT JOIN user_ u ON cm.user_id = u.id
479 LEFT JOIN community c ON cm.community_id = c.id;
481 CREATE VIEW community_follower_view AS
484 u.actor_id AS user_actor_id,
485 u.local AS user_local,
488 c.actor_id AS community_actor_id,
489 c.local AS community_local,
490 c.name AS community_name
492 community_follower cf
493 LEFT JOIN user_ u ON cf.user_id = u.id
494 LEFT JOIN community c ON cf.community_id = c.id;
496 CREATE VIEW community_user_ban_view AS
499 u.actor_id AS user_actor_id,
500 u.local AS user_local,
503 c.actor_id AS community_actor_id,
504 c.local AS community_local,
505 c.name AS community_name
507 community_user_ban cb
508 LEFT JOIN user_ u ON cb.user_id = u.id
509 LEFT JOIN community c ON cb.community_id = c.id;
511 -- The community fast table
512 CREATE TABLE community_aggregates_fast AS
516 community_aggregates_view;
518 ALTER TABLE community_aggregates_fast
519 ADD PRIMARY KEY (id);
521 CREATE VIEW community_fast_view AS
529 community_follower cf
532 AND ac.id = cf.community_id) AS subscribed
539 community_aggregates_fast ca) ac
546 community_aggregates_fast caf;
548 DROP TRIGGER refresh_community ON community;
550 CREATE TRIGGER refresh_community
551 AFTER INSERT OR UPDATE OR DELETE ON community
553 EXECUTE PROCEDURE refresh_community ();
556 -- select * from community_fast_view where name like 'test_community_name' and user_id is null;
558 -- insert into community(name, title, category_id, creator_id) values ('test_community_name', 'test_community_title', 1, 2);
560 -- delete from community where name like 'test_community_name';
562 -- update community set title = 'test_community_title_2' where name like 'test_community_name';
563 CREATE OR REPLACE FUNCTION refresh_community ()
568 IF (TG_OP = 'DELETE') THEN
569 DELETE FROM community_aggregates_fast
571 ELSIF (TG_OP = 'UPDATE') THEN
572 DELETE FROM community_aggregates_fast
574 INSERT INTO community_aggregates_fast
578 community_aggregates_view
581 -- Update user view due to owner changes
582 DELETE FROM user_fast
583 WHERE id = NEW.creator_id;
584 INSERT INTO user_fast
591 -- Update post view due to community changes
592 DELETE FROM post_aggregates_fast
593 WHERE community_id = NEW.id;
594 INSERT INTO post_aggregates_fast
600 community_id = NEW.id;
601 -- TODO make sure this shows up in the users page ?
602 ELSIF (TG_OP = 'INSERT') THEN
603 INSERT INTO community_aggregates_fast
607 community_aggregates_view
616 DROP VIEW user_mention_view;
618 DROP VIEW comment_view;
620 DROP VIEW comment_aggregates_view;
622 CREATE VIEW comment_aggregates_view AS
627 c.actor_id AS community_actor_id,
628 c."local" AS community_local,
629 c."name" AS community_name,
632 coalesce(cb.id, 0)::bool AS banned_from_community,
633 u.actor_id AS creator_actor_id,
634 u.local AS creator_local,
635 u.name AS creator_name,
636 u.avatar AS creator_avatar,
638 coalesce(cl.total, 0) AS score,
639 coalesce(cl.up, 0) AS upvotes,
640 coalesce(cl.down, 0) AS downvotes,
641 hot_rank (coalesce(cl.total, 0), ct.published) AS hot_rank
644 LEFT JOIN post p ON ct.post_id = p.id
645 LEFT JOIN community c ON p.community_id = c.id
646 LEFT JOIN user_ u ON ct.creator_id = u.id
647 LEFT JOIN community_user_ban cb ON ct.creator_id = cb.user_id
648 AND p.id = ct.post_id
649 AND p.community_id = cb.community_id
653 sum(l.score) AS total,
655 CASE WHEN l.score = 1 THEN
661 CASE WHEN l.score = - 1 THEN
669 comment_id) AS cl ON cl.id = ct.id;
671 CREATE OR REPLACE VIEW comment_view AS (
674 us.user_id AS user_id,
675 us.my_vote AS my_vote,
676 us.is_subbed::bool AS subscribed,
677 us.is_saved::bool AS saved
679 comment_aggregates_view cav
683 coalesce(cl.score, 0) AS my_vote,
684 coalesce(cf.id, 0) AS is_subbed,
685 coalesce(cs.id, 0) AS is_saved
688 LEFT JOIN comment_like cl ON u.id = cl.user_id
689 AND cav.id = cl.comment_id
690 LEFT JOIN comment_saved cs ON u.id = cs.user_id
691 AND cs.comment_id = cav.id
692 LEFT JOIN community_follower cf ON u.id = cf.user_id
693 AND cav.community_id = cf.community_id) AS us
702 comment_aggregates_view cav);
705 CREATE TABLE comment_aggregates_fast AS
709 comment_aggregates_view;
711 ALTER TABLE comment_aggregates_fast
712 ADD PRIMARY KEY (id);
714 CREATE VIEW comment_fast_view AS
717 us.user_id AS user_id,
718 us.my_vote AS my_vote,
719 us.is_subbed::bool AS subscribed,
720 us.is_saved::bool AS saved
722 comment_aggregates_fast cav
726 coalesce(cl.score, 0) AS my_vote,
727 coalesce(cf.id, 0) AS is_subbed,
728 coalesce(cs.id, 0) AS is_saved
731 LEFT JOIN comment_like cl ON u.id = cl.user_id
732 AND cav.id = cl.comment_id
733 LEFT JOIN comment_saved cs ON u.id = cs.user_id
734 AND cs.comment_id = cav.id
735 LEFT JOIN community_follower cf ON u.id = cf.user_id
736 AND cav.community_id = cf.community_id) AS us
745 comment_aggregates_fast cav;
747 -- Do the reply_view referencing the comment_fast_view
748 CREATE VIEW reply_fast_view AS
752 c2.creator_id AS sender_id,
753 c.creator_id AS recipient_id
756 INNER JOIN comment c2 ON c.id = c2.parent_id
758 c2.creator_id != c.creator_id
759 -- Do union where post is null
763 c.creator_id AS sender_id,
764 p.creator_id AS recipient_id
770 AND c.parent_id IS NULL
771 AND c.creator_id != p.creator_id
775 closereply.recipient_id
777 comment_fast_view cv,
780 closereply.id = cv.id;
783 CREATE VIEW user_mention_view AS
786 um.id AS user_mention_id,
799 c.community_actor_id,
803 c.banned_from_community,
820 u.id = um.recipient_id) AS recipient_actor_id,
827 u.id = um.recipient_id) AS recipient_local
832 um.comment_id = c.id;
834 CREATE VIEW user_mention_fast_view AS
837 um.id AS user_mention_id,
850 ac.community_actor_id,
854 ac.banned_from_community,
862 coalesce(cl.score, 0) AS my_vote,
870 AND cs.comment_id = ac.id) AS saved,
878 u.id = um.recipient_id) AS recipient_actor_id,
885 u.id = um.recipient_id) AS recipient_local
892 comment_aggregates_fast ca) ac
893 LEFT JOIN comment_like cl ON u.id = cl.user_id
894 AND ac.id = cl.comment_id
895 LEFT JOIN user_mention um ON um.comment_id = ac.id
899 um.id AS user_mention_id,
912 ac.community_actor_id,
916 ac.banned_from_community,
933 u.id = um.recipient_id) AS recipient_actor_id,
940 u.id = um.recipient_id) AS recipient_local
942 comment_aggregates_fast ac
943 LEFT JOIN user_mention um ON um.comment_id = ac.id;
945 DROP TRIGGER refresh_comment ON comment;
947 CREATE TRIGGER refresh_comment
948 AFTER INSERT OR UPDATE OR DELETE ON comment
950 EXECUTE PROCEDURE refresh_comment ();
953 -- select * from comment_fast_view where content = 'test_comment' and user_id is null;
955 -- insert into comment(creator_id, post_id, content) values (2, 2, 'test_comment');
957 -- delete from comment where content like 'test_comment';
959 -- update comment set removed = true where content like 'test_comment';
960 CREATE OR REPLACE FUNCTION refresh_comment ()
965 IF (TG_OP = 'DELETE') THEN
966 DELETE FROM comment_aggregates_fast
968 -- Update community number of comments
970 community_aggregates_fast AS caf
972 number_of_comments = number_of_comments - 1
976 caf.id = p.community_id
977 AND p.id = OLD.post_id;
978 ELSIF (TG_OP = 'UPDATE') THEN
979 DELETE FROM comment_aggregates_fast
981 INSERT INTO comment_aggregates_fast
985 comment_aggregates_view
988 ELSIF (TG_OP = 'INSERT') THEN
989 INSERT INTO comment_aggregates_fast
993 comment_aggregates_view
996 -- Update user view due to comment count
1000 number_of_comments = number_of_comments + 1
1002 id = NEW.creator_id;
1003 -- Update post view due to comment count, new comment activity time, but only on new posts
1004 -- TODO this could be done more efficiently
1005 DELETE FROM post_aggregates_fast
1006 WHERE id = NEW.post_id;
1007 INSERT INTO post_aggregates_fast
1011 post_aggregates_view
1014 -- Force the hot rank as zero on week-older posts
1016 post_aggregates_fast AS paf
1020 paf.id = NEW.post_id
1021 AND (paf.published < ('now'::timestamp - '1 week'::interval));
1022 -- Update community number of comments
1024 community_aggregates_fast AS caf
1026 number_of_comments = number_of_comments + 1
1030 caf.id = p.community_id
1031 AND p.id = NEW.post_id;
1038 -- select id, score, my_vote from post_fast_view where id = 29 and user_id = 4;
1040 -- insert into post_like(user_id, post_id, score) values (4, 29, 1);
1042 -- delete from post_like where user_id = 4 and post_id = 29;
1044 -- update post_like set score = -1 where user_id = 4 and post_id = 29;
1045 -- TODO test this a LOT
1046 CREATE OR REPLACE FUNCTION refresh_post_like ()
1051 IF (TG_OP = 'DELETE') THEN
1053 post_aggregates_fast
1055 score = CASE WHEN (OLD.score = 1) THEN
1060 upvotes = CASE WHEN (OLD.score = 1) THEN
1065 downvotes = CASE WHEN (OLD.score = - 1) THEN
1072 ELSIF (TG_OP = 'INSERT') THEN
1074 post_aggregates_fast
1076 score = CASE WHEN (NEW.score = 1) THEN
1081 upvotes = CASE WHEN (NEW.score = 1) THEN
1086 downvotes = CASE WHEN (NEW.score = - 1) THEN
1098 DROP TRIGGER refresh_post_like ON post_like;
1100 CREATE TRIGGER refresh_post_like
1101 AFTER INSERT OR DELETE ON post_like
1103 EXECUTE PROCEDURE refresh_post_like ();
1106 -- select id, score, my_vote from comment_fast_view where id = 29 and user_id = 4;
1108 -- insert into comment_like(user_id, comment_id, post_id, score) values (4, 29, 51, 1);
1110 -- delete from comment_like where user_id = 4 and comment_id = 29;
1112 -- update comment_like set score = -1 where user_id = 4 and comment_id = 29;
1113 CREATE OR REPLACE FUNCTION refresh_comment_like ()
1118 -- TODO possibly select from comment_fast to get previous scores, instead of re-fetching the views?
1119 IF (TG_OP = 'DELETE') THEN
1121 comment_aggregates_fast
1123 score = CASE WHEN (OLD.score = 1) THEN
1128 upvotes = CASE WHEN (OLD.score = 1) THEN
1133 downvotes = CASE WHEN (OLD.score = - 1) THEN
1139 id = OLD.comment_id;
1140 ELSIF (TG_OP = 'INSERT') THEN
1142 comment_aggregates_fast
1144 score = CASE WHEN (NEW.score = 1) THEN
1149 upvotes = CASE WHEN (NEW.score = 1) THEN
1154 downvotes = CASE WHEN (NEW.score = - 1) THEN
1160 id = NEW.comment_id;
1166 DROP TRIGGER refresh_comment_like ON comment_like;
1168 CREATE TRIGGER refresh_comment_like
1169 AFTER INSERT OR DELETE ON comment_like
1171 EXECUTE PROCEDURE refresh_comment_like ();
1173 -- Community user ban
1174 DROP TRIGGER refresh_community_user_ban ON community_user_ban;
1176 CREATE TRIGGER refresh_community_user_ban
1177 AFTER INSERT OR DELETE -- Note this is missing after update
1178 ON community_user_ban
1180 EXECUTE PROCEDURE refresh_community_user_ban ();
1182 -- select creator_name, banned_from_community from comment_fast_view where user_id = 4 and content = 'test_before_ban';
1183 -- select creator_name, banned_from_community, community_id from comment_aggregates_fast where content = 'test_before_ban';
1185 -- insert into comment(creator_id, post_id, content) values (1198, 341, 'test_before_ban');
1186 -- insert into community_user_ban(community_id, user_id) values (2, 1198);
1188 -- delete from community_user_ban where user_id = 1198 and community_id = 2;
1189 -- delete from comment where content = 'test_before_ban';
1190 -- update comment_aggregates_fast set banned_from_community = false where creator_id = 1198 and community_id = 2;
1191 CREATE OR REPLACE FUNCTION refresh_community_user_ban ()
1196 -- TODO possibly select from comment_fast to get previous scores, instead of re-fetching the views?
1197 IF (TG_OP = 'DELETE') THEN
1199 comment_aggregates_fast
1201 banned_from_community = FALSE
1203 creator_id = OLD.user_id
1204 AND community_id = OLD.community_id;
1206 post_aggregates_fast
1208 banned_from_community = FALSE
1210 creator_id = OLD.user_id
1211 AND community_id = OLD.community_id;
1212 ELSIF (TG_OP = 'INSERT') THEN
1214 comment_aggregates_fast
1216 banned_from_community = TRUE
1218 creator_id = NEW.user_id
1219 AND community_id = NEW.community_id;
1221 post_aggregates_fast
1223 banned_from_community = TRUE
1225 creator_id = NEW.user_id
1226 AND community_id = NEW.community_id;
1232 -- Community follower
1233 DROP TRIGGER refresh_community_follower ON community_follower;
1235 CREATE TRIGGER refresh_community_follower
1236 AFTER INSERT OR DELETE -- Note this is missing after update
1237 ON community_follower
1239 EXECUTE PROCEDURE refresh_community_follower ();
1241 CREATE OR REPLACE FUNCTION refresh_community_follower ()
1246 IF (TG_OP = 'DELETE') THEN
1248 community_aggregates_fast
1250 number_of_subscribers = number_of_subscribers - 1
1252 id = OLD.community_id;
1253 ELSIF (TG_OP = 'INSERT') THEN
1255 community_aggregates_fast
1257 number_of_subscribers = number_of_subscribers + 1
1259 id = NEW.community_id;