1 -- functions and triggers
2 DROP TRIGGER refresh_user ON user_;
4 DROP FUNCTION refresh_user ();
6 DROP TRIGGER refresh_post ON post;
8 DROP FUNCTION refresh_post ();
10 DROP TRIGGER refresh_post_like ON post_like;
12 DROP FUNCTION refresh_post_like ();
14 DROP TRIGGER refresh_community ON community;
16 DROP FUNCTION refresh_community ();
18 DROP TRIGGER refresh_community_follower ON community_follower;
20 DROP FUNCTION refresh_community_follower ();
22 DROP TRIGGER refresh_community_user_ban ON community_user_ban;
24 DROP FUNCTION refresh_community_user_ban ();
26 DROP TRIGGER refresh_comment ON comment;
28 DROP FUNCTION refresh_comment ();
30 DROP TRIGGER refresh_comment_like ON comment_like;
32 DROP FUNCTION refresh_comment_like ();
38 CREATE VIEW post_view AS
48 p.creator_id = u.id) AS banned,
55 p.creator_id = cb.user_id
56 AND p.community_id = cb.community_id) AS banned_from_community,
63 p.creator_id = user_.id) AS creator_name,
70 p.creator_id = user_.id) AS creator_avatar,
77 p.community_id = community.id) AS community_name,
84 p.community_id = c.id) AS community_removed,
91 p.community_id = c.id) AS community_deleted,
98 p.community_id = c.id) AS community_nsfw,
105 comment.post_id = p.id) AS number_of_comments,
106 coalesce(sum(pl.score), 0) AS score,
108 CASE WHEN pl.score = 1 THEN
114 CASE WHEN pl.score = - 1 THEN
119 hot_rank (coalesce(sum(pl.score), 0), p.published) AS hot_rank
122 LEFT JOIN post_like pl ON p.id = pl.post_id
129 coalesce(pl.score, 0) AS my_vote,
134 community_follower cf
137 AND cf.community_id = ap.community_id) AS subscribed,
145 AND pr.post_id = ap.id) AS read,
153 AND ps.post_id = ap.id) AS saved
156 CROSS JOIN all_post ap
157 LEFT JOIN post_like pl ON u.id = pl.user_id
158 AND ap.id = pl.post_id
170 DROP VIEW post_mview;
172 DROP MATERIALIZED VIEW post_aggregates_mview;
174 DROP VIEW post_aggregates_view;
177 DROP MATERIALIZED VIEW user_mview;
181 CREATE VIEW user_view AS
191 send_notifications_to_email,
199 p.creator_id = u.id) AS number_of_posts,
202 coalesce(sum(score), 0)
208 AND p.id = pl.post_id) AS post_score,
215 c.creator_id = u.id) AS number_of_comments,
218 coalesce(sum(score), 0)
224 AND c.id = cl.comment_id) AS comment_score
229 DROP VIEW community_mview;
231 DROP MATERIALIZED VIEW community_aggregates_mview;
233 DROP VIEW community_view;
235 DROP VIEW community_aggregates_view;
237 CREATE VIEW community_view AS
238 with all_community AS (
247 c.creator_id = u.id) AS creator_name,
254 c.creator_id = u.id) AS creator_avatar,
261 c.category_id = ct.id) AS category_name,
266 community_follower cf
268 cf.community_id = c.id) AS number_of_subscribers,
275 p.community_id = c.id) AS number_of_posts,
283 c.id = p.community_id
284 AND p.id = co.post_id) AS number_of_comments,
288 FROM community_follower cf
290 cf.community_id = c.id), c.published) AS hot_rank
301 community_follower cf
304 AND ac.id = cf.community_id) AS subscribed
307 CROSS JOIN all_community ac
316 -- reply and comment view
317 DROP VIEW reply_view;
319 DROP VIEW user_mention_view;
321 DROP VIEW comment_view;
323 DROP VIEW comment_mview;
325 DROP MATERIALIZED VIEW comment_aggregates_mview;
327 DROP VIEW comment_aggregates_view;
329 CREATE VIEW comment_view AS
330 with all_comment AS (
346 c.creator_id = u.id) AS banned,
351 community_user_ban cb,
354 c.creator_id = cb.user_id
356 AND p.community_id = cb.community_id) AS banned_from_community,
363 c.creator_id = user_.id) AS creator_name,
370 c.creator_id = user_.id) AS creator_avatar,
371 coalesce(sum(cl.score), 0) AS score,
373 CASE WHEN cl.score = 1 THEN
379 CASE WHEN cl.score = - 1 THEN
386 LEFT JOIN comment_like cl ON c.id = cl.comment_id
393 coalesce(cl.score, 0) AS my_vote,
401 AND cs.comment_id = ac.id) AS saved
404 CROSS JOIN all_comment ac
405 LEFT JOIN comment_like cl ON u.id = cl.user_id
406 AND ac.id = cl.comment_id
416 CREATE VIEW reply_view AS
420 c2.creator_id AS sender_id,
421 c.creator_id AS recipient_id
424 INNER JOIN comment c2 ON c.id = c2.parent_id
426 c2.creator_id != c.creator_id
427 -- Do union where post is null
431 c.creator_id AS sender_id,
432 p.creator_id AS recipient_id
438 AND c.parent_id IS NULL
439 AND c.creator_id != p.creator_id
443 closereply.recipient_id
448 closereply.id = cv.id;
451 CREATE VIEW user_mention_view AS
454 um.id AS user_mention_id,
466 c.banned_from_community,
480 um.comment_id = c.id;