8 DROP VIEW post_fast_view;
10 DROP TABLE post_aggregates_fast;
14 DROP VIEW post_aggregates_view;
16 DROP VIEW community_moderator_view;
18 DROP VIEW community_follower_view;
20 DROP VIEW community_user_ban_view;
22 DROP VIEW community_view;
24 DROP VIEW community_aggregates_view;
26 DROP VIEW community_fast_view;
28 DROP TABLE community_aggregates_fast;
30 DROP VIEW private_message_view;
32 DROP VIEW user_mention_view;
34 DROP VIEW reply_fast_view;
36 DROP VIEW comment_fast_view;
38 DROP VIEW comment_view;
40 DROP VIEW user_mention_fast_view;
42 DROP TABLE comment_aggregates_fast;
44 DROP VIEW comment_aggregates_view;
58 CREATE VIEW site_view AS
67 s.creator_id = u.id) AS creator_name,
74 s.creator_id = u.id) AS creator_avatar,
79 user_) AS number_of_users,
84 post) AS number_of_posts,
89 comment) AS number_of_comments,
94 community) AS number_of_communities
99 CREATE VIEW user_view AS
112 u.send_notifications_to_email,
114 coalesce(pd.posts, 0) AS number_of_posts,
115 coalesce(pd.score, 0) AS post_score,
116 coalesce(cd.comments, 0) AS number_of_comments,
117 coalesce(cd.score, 0) AS comment_score
122 p.creator_id AS creator_id,
123 count(DISTINCT p.id) AS posts,
124 sum(pl.score) AS score
127 JOIN post_like pl ON p.id = pl.post_id
129 p.creator_id) pd ON u.id = pd.creator_id
133 count(DISTINCT c.id) AS comments,
134 sum(cl.score) AS score
137 JOIN comment_like cl ON c.id = cl.comment_id
139 c.creator_id) cd ON u.id = cd.creator_id;
141 CREATE TABLE user_fast AS
147 ALTER TABLE user_fast
148 ADD PRIMARY KEY (id);
151 CREATE VIEW post_aggregates_view AS
155 u.actor_id AS creator_actor_id,
156 u."local" AS creator_local,
157 u."name" AS creator_name,
158 u.published AS creator_published,
159 u.avatar AS creator_avatar,
161 cb.id::bool AS banned_from_community,
163 c.actor_id AS community_actor_id,
164 c."local" AS community_local,
165 c."name" AS community_name,
166 c.removed AS community_removed,
167 c.deleted AS community_deleted,
168 c.nsfw AS community_nsfw,
169 -- post score data/comment count
170 coalesce(ct.comments, 0) AS number_of_comments,
171 coalesce(pl.score, 0) AS score,
172 coalesce(pl.upvotes, 0) AS upvotes,
173 coalesce(pl.downvotes, 0) AS downvotes,
174 hot_rank (coalesce(pl.score, 0), (
175 CASE WHEN (p.published < ('now'::timestamp - '1 month'::interval)) THEN
178 greatest (ct.recent_comment_time, p.published)
181 CASE WHEN (p.published < ('now'::timestamp - '1 month'::interval)) THEN
184 greatest (ct.recent_comment_time, p.published)
185 END) AS newest_activity_time
188 LEFT JOIN user_ u ON p.creator_id = u.id
189 LEFT JOIN community_user_ban cb ON p.creator_id = cb.user_id
190 AND p.community_id = cb.community_id
191 LEFT JOIN community c ON p.community_id = c.id
195 count(*) AS comments,
196 max(published) AS recent_comment_time
200 post_id) ct ON ct.post_id = p.id
205 sum(score) FILTER (WHERE score = 1) AS upvotes,
206 - sum(score) FILTER (WHERE score = - 1) AS downvotes
210 post_id) pl ON pl.post_id = p.id
214 CREATE VIEW post_view AS
218 us.user_vote AS my_vote,
219 us.is_subbed::bool AS subscribed,
220 us.is_read::bool AS read,
221 us.is_saved::bool AS saved
223 post_aggregates_view pav
227 coalesce(cf.community_id, 0) AS is_subbed,
228 coalesce(pr.post_id, 0) AS is_read,
229 coalesce(ps.post_id, 0) AS is_saved,
230 coalesce(pl.score, 0) AS user_vote
233 LEFT JOIN community_user_ban cb ON u.id = cb.user_id
234 AND cb.community_id = pav.community_id
235 LEFT JOIN community_follower cf ON u.id = cf.user_id
236 AND cf.community_id = pav.community_id
237 LEFT JOIN post_read pr ON u.id = pr.user_id
238 AND pr.post_id = pav.id
239 LEFT JOIN post_saved ps ON u.id = ps.user_id
240 AND ps.post_id = pav.id
241 LEFT JOIN post_like pl ON u.id = pl.user_id
242 AND pav.id = pl.post_id) AS us
252 post_aggregates_view pav;
254 CREATE TABLE post_aggregates_fast AS
258 post_aggregates_view;
260 ALTER TABLE post_aggregates_fast
261 ADD PRIMARY KEY (id);
263 CREATE VIEW post_fast_view AS
267 us.user_vote AS my_vote,
268 us.is_subbed::bool AS subscribed,
269 us.is_read::bool AS read,
270 us.is_saved::bool AS saved
272 post_aggregates_fast pav
276 coalesce(cf.community_id, 0) AS is_subbed,
277 coalesce(pr.post_id, 0) AS is_read,
278 coalesce(ps.post_id, 0) AS is_saved,
279 coalesce(pl.score, 0) AS user_vote
282 LEFT JOIN community_user_ban cb ON u.id = cb.user_id
283 AND cb.community_id = pav.community_id
284 LEFT JOIN community_follower cf ON u.id = cf.user_id
285 AND cf.community_id = pav.community_id
286 LEFT JOIN post_read pr ON u.id = pr.user_id
287 AND pr.post_id = pav.id
288 LEFT JOIN post_saved ps ON u.id = ps.user_id
289 AND ps.post_id = pav.id
290 LEFT JOIN post_like pl ON u.id = pl.user_id
291 AND pav.id = pl.post_id) AS us
301 post_aggregates_fast pav;
304 CREATE VIEW community_aggregates_view AS
320 u.actor_id AS creator_actor_id,
321 u.local AS creator_local,
322 u.name AS creator_name,
323 u.avatar AS creator_avatar,
324 cat.name AS category_name,
325 coalesce(cf.subs, 0) AS number_of_subscribers,
326 coalesce(cd.posts, 0) AS number_of_posts,
327 coalesce(cd.comments, 0) AS number_of_comments,
328 hot_rank (cf.subs, c.published) AS hot_rank
331 LEFT JOIN user_ u ON c.creator_id = u.id
332 LEFT JOIN category cat ON c.category_id = cat.id
336 count(DISTINCT p.id) AS posts,
337 count(DISTINCT ct.id) AS comments
340 JOIN comment ct ON p.id = ct.post_id
342 p.community_id) cd ON cd.community_id = c.id
350 community_id) cf ON cf.community_id = c.id;
352 CREATE VIEW community_view AS
356 us.is_subbed::bool AS subscribed
358 community_aggregates_view cv
362 coalesce(cf.community_id, 0) AS is_subbed
365 LEFT JOIN community_follower cf ON u.id = cf.user_id
366 AND cf.community_id = cv.id) AS us
373 community_aggregates_view cv;
375 CREATE VIEW community_moderator_view AS
378 u.actor_id AS user_actor_id,
379 u.local AS user_local,
382 c.actor_id AS community_actor_id,
383 c.local AS community_local,
384 c.name AS community_name
386 community_moderator cm
387 LEFT JOIN user_ u ON cm.user_id = u.id
388 LEFT JOIN community c ON cm.community_id = c.id;
390 CREATE VIEW community_follower_view AS
393 u.actor_id AS user_actor_id,
394 u.local AS user_local,
397 c.actor_id AS community_actor_id,
398 c.local AS community_local,
399 c.name AS community_name
401 community_follower cf
402 LEFT JOIN user_ u ON cf.user_id = u.id
403 LEFT JOIN community c ON cf.community_id = c.id;
405 CREATE VIEW community_user_ban_view AS
408 u.actor_id AS user_actor_id,
409 u.local AS user_local,
412 c.actor_id AS community_actor_id,
413 c.local AS community_local,
414 c.name AS community_name
416 community_user_ban cb
417 LEFT JOIN user_ u ON cb.user_id = u.id
418 LEFT JOIN community c ON cb.community_id = c.id;
420 -- The community fast table
421 CREATE TABLE community_aggregates_fast AS
425 community_aggregates_view;
427 ALTER TABLE community_aggregates_fast
428 ADD PRIMARY KEY (id);
430 CREATE VIEW community_fast_view AS
438 community_follower cf
441 AND ac.id = cf.community_id) AS subscribed
448 community_aggregates_fast ca) ac
455 community_aggregates_fast caf;
458 CREATE VIEW private_message_view AS
461 u.name AS creator_name,
462 u.avatar AS creator_avatar,
463 u.actor_id AS creator_actor_id,
464 u.local AS creator_local,
465 u2.name AS recipient_name,
466 u2.avatar AS recipient_avatar,
467 u2.actor_id AS recipient_actor_id,
468 u2.local AS recipient_local
471 INNER JOIN user_ u ON u.id = pm.creator_id
472 INNER JOIN user_ u2 ON u2.id = pm.recipient_id;
474 -- Comments, mentions, replies
475 CREATE VIEW comment_aggregates_view AS
479 p."name" AS post_name,
482 c.actor_id AS community_actor_id,
483 c."local" AS community_local,
484 c."name" AS community_name,
487 coalesce(cb.id, 0)::bool AS banned_from_community,
488 u.actor_id AS creator_actor_id,
489 u.local AS creator_local,
490 u.name AS creator_name,
491 u.published AS creator_published,
492 u.avatar AS creator_avatar,
494 coalesce(cl.total, 0) AS score,
495 coalesce(cl.up, 0) AS upvotes,
496 coalesce(cl.down, 0) AS downvotes,
497 hot_rank (coalesce(cl.total, 0), ct.published) AS hot_rank
500 LEFT JOIN post p ON ct.post_id = p.id
501 LEFT JOIN community c ON p.community_id = c.id
502 LEFT JOIN user_ u ON ct.creator_id = u.id
503 LEFT JOIN community_user_ban cb ON ct.creator_id = cb.user_id
504 AND p.id = ct.post_id
505 AND p.community_id = cb.community_id
509 sum(l.score) AS total,
511 CASE WHEN l.score = 1 THEN
517 CASE WHEN l.score = - 1 THEN
525 comment_id) AS cl ON cl.id = ct.id;
527 CREATE OR REPLACE VIEW comment_view AS (
530 us.user_id AS user_id,
531 us.my_vote AS my_vote,
532 us.is_subbed::bool AS subscribed,
533 us.is_saved::bool AS saved
535 comment_aggregates_view cav
539 coalesce(cl.score, 0) AS my_vote,
540 coalesce(cf.id, 0) AS is_subbed,
541 coalesce(cs.id, 0) AS is_saved
544 LEFT JOIN comment_like cl ON u.id = cl.user_id
545 AND cav.id = cl.comment_id
546 LEFT JOIN comment_saved cs ON u.id = cs.user_id
547 AND cs.comment_id = cav.id
548 LEFT JOIN community_follower cf ON u.id = cf.user_id
549 AND cav.community_id = cf.community_id) AS us
558 comment_aggregates_view cav);
560 CREATE TABLE comment_aggregates_fast AS
564 comment_aggregates_view;
566 ALTER TABLE comment_aggregates_fast
567 ADD PRIMARY KEY (id);
569 CREATE VIEW comment_fast_view AS
572 us.user_id AS user_id,
573 us.my_vote AS my_vote,
574 us.is_subbed::bool AS subscribed,
575 us.is_saved::bool AS saved
577 comment_aggregates_fast cav
581 coalesce(cl.score, 0) AS my_vote,
582 coalesce(cf.id, 0) AS is_subbed,
583 coalesce(cs.id, 0) AS is_saved
586 LEFT JOIN comment_like cl ON u.id = cl.user_id
587 AND cav.id = cl.comment_id
588 LEFT JOIN comment_saved cs ON u.id = cs.user_id
589 AND cs.comment_id = cav.id
590 LEFT JOIN community_follower cf ON u.id = cf.user_id
591 AND cav.community_id = cf.community_id) AS us
600 comment_aggregates_fast cav;
602 CREATE VIEW user_mention_view AS
605 um.id AS user_mention_id,
619 c.community_actor_id,
623 c.banned_from_community,
640 u.id = um.recipient_id) AS recipient_actor_id,
647 u.id = um.recipient_id) AS recipient_local
652 um.comment_id = c.id;
654 CREATE VIEW user_mention_fast_view AS
657 um.id AS user_mention_id,
671 ac.community_actor_id,
675 ac.banned_from_community,
683 coalesce(cl.score, 0) AS my_vote,
691 AND cs.comment_id = ac.id) AS saved,
699 u.id = um.recipient_id) AS recipient_actor_id,
706 u.id = um.recipient_id) AS recipient_local
713 comment_aggregates_fast ca) ac
714 LEFT JOIN comment_like cl ON u.id = cl.user_id
715 AND ac.id = cl.comment_id
716 LEFT JOIN user_mention um ON um.comment_id = ac.id
720 um.id AS user_mention_id,
734 ac.community_actor_id,
738 ac.banned_from_community,
755 u.id = um.recipient_id) AS recipient_actor_id,
762 u.id = um.recipient_id) AS recipient_local
764 comment_aggregates_fast ac
765 LEFT JOIN user_mention um ON um.comment_id = ac.id;
767 -- Do the reply_view referencing the comment_fast_view
768 CREATE VIEW reply_fast_view AS
772 c2.creator_id AS sender_id,
773 c.creator_id AS recipient_id
776 INNER JOIN comment c2 ON c.id = c2.parent_id
778 c2.creator_id != c.creator_id
779 -- Do union where post is null
783 c.creator_id AS sender_id,
784 p.creator_id AS recipient_id
790 AND c.parent_id IS NULL
791 AND c.creator_id != p.creator_id
795 closereply.recipient_id
797 comment_fast_view cv,
800 closereply.id = cv.id;
802 -- redoing the triggers
803 CREATE OR REPLACE FUNCTION refresh_post ()
808 IF (TG_OP = 'DELETE') THEN
809 DELETE FROM post_aggregates_fast
811 -- Update community number of posts
813 community_aggregates_fast
815 number_of_posts = number_of_posts - 1
817 id = OLD.community_id;
818 ELSIF (TG_OP = 'UPDATE') THEN
819 DELETE FROM post_aggregates_fast
821 INSERT INTO post_aggregates_fast
828 ELSIF (TG_OP = 'INSERT') THEN
829 INSERT INTO post_aggregates_fast
836 -- Update that users number of posts, post score
837 DELETE FROM user_fast
838 WHERE id = NEW.creator_id;
839 INSERT INTO user_fast
846 -- Update community number of posts
848 community_aggregates_fast
850 number_of_posts = number_of_posts + 1
852 id = NEW.community_id;
853 -- Update the hot rank on the post table
854 -- TODO this might not correctly update it, using a 1 week interval
856 post_aggregates_fast AS paf
858 hot_rank = pav.hot_rank
860 post_aggregates_view AS pav
863 AND (pav.published > ('now'::timestamp - '1 week'::interval));
869 CREATE OR REPLACE FUNCTION refresh_comment ()
874 IF (TG_OP = 'DELETE') THEN
875 DELETE FROM comment_aggregates_fast
877 -- Update community number of comments
879 community_aggregates_fast AS caf
881 number_of_comments = number_of_comments - 1
885 caf.id = p.community_id
886 AND p.id = OLD.post_id;
887 ELSIF (TG_OP = 'UPDATE') THEN
888 DELETE FROM comment_aggregates_fast
890 INSERT INTO comment_aggregates_fast
894 comment_aggregates_view
897 ELSIF (TG_OP = 'INSERT') THEN
898 INSERT INTO comment_aggregates_fast
902 comment_aggregates_view
905 -- Update user view due to comment count
909 number_of_comments = number_of_comments + 1
912 -- Update post view due to comment count, new comment activity time, but only on new posts
913 -- TODO this could be done more efficiently
914 DELETE FROM post_aggregates_fast
915 WHERE id = NEW.post_id;
916 INSERT INTO post_aggregates_fast
923 -- Force the hot rank as zero on week-older posts
925 post_aggregates_fast AS paf
930 AND (paf.published < ('now'::timestamp - '1 week'::interval));
931 -- Update community number of comments
933 community_aggregates_fast AS caf
935 number_of_comments = number_of_comments + 1
939 caf.id = p.community_id
940 AND p.id = NEW.post_id;