1 -- Dropping all the fast tables
4 DROP VIEW post_fast_view;
6 DROP TABLE post_aggregates_fast;
8 DROP VIEW community_fast_view;
10 DROP TABLE community_aggregates_fast;
12 DROP VIEW reply_fast_view;
14 DROP VIEW user_mention_fast_view;
16 DROP VIEW comment_fast_view;
18 DROP TABLE comment_aggregates_fast;
20 -- Re-adding all the triggers, functions, and mviews
22 CREATE MATERIALIZED VIEW private_message_mview AS
28 CREATE UNIQUE INDEX idx_private_message_mview_id ON private_message_mview (id);
30 -- Create the triggers
31 CREATE OR REPLACE FUNCTION refresh_private_message ()
36 REFRESH MATERIALIZED VIEW CONCURRENTLY private_message_mview;
41 CREATE TRIGGER refresh_private_message
42 AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE ON private_message
44 EXECUTE PROCEDURE refresh_private_message ();
47 CREATE OR REPLACE FUNCTION refresh_user ()
52 REFRESH MATERIALIZED VIEW CONCURRENTLY user_mview;
53 REFRESH MATERIALIZED VIEW CONCURRENTLY comment_aggregates_mview;
55 REFRESH MATERIALIZED VIEW CONCURRENTLY post_aggregates_mview;
60 DROP TRIGGER refresh_user ON user_;
62 CREATE TRIGGER refresh_user
63 AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE ON user_
65 EXECUTE PROCEDURE refresh_user ();
67 DROP VIEW user_view CASCADE;
69 CREATE VIEW user_view AS
82 u.send_notifications_to_email,
90 p.creator_id = u.id) AS number_of_posts,
93 coalesce(sum(score), 0)
99 AND p.id = pl.post_id) AS post_score,
106 c.creator_id = u.id) AS number_of_comments,
109 coalesce(sum(score), 0)
115 AND c.id = cl.comment_id) AS comment_score
119 CREATE MATERIALIZED VIEW user_mview AS
125 CREATE UNIQUE INDEX idx_user_mview_id ON user_mview (id);
128 DROP TRIGGER refresh_community ON community;
130 CREATE TRIGGER refresh_community
131 AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE ON community
133 EXECUTE PROCEDURE refresh_community ();
135 CREATE OR REPLACE FUNCTION refresh_community ()
140 REFRESH MATERIALIZED VIEW CONCURRENTLY post_aggregates_mview;
141 REFRESH MATERIALIZED VIEW CONCURRENTLY community_aggregates_mview;
142 REFRESH MATERIALIZED VIEW CONCURRENTLY user_mview;
147 DROP VIEW community_aggregates_view CASCADE;
149 CREATE VIEW community_aggregates_view AS
150 -- Now that there's public and private keys, you have to be explicit here
172 c.creator_id = u.id) AS creator_actor_id,
179 c.creator_id = u.id) AS creator_local,
186 c.creator_id = u.id) AS creator_name,
193 c.creator_id = u.id) AS creator_avatar,
200 c.category_id = ct.id) AS category_name,
205 community_follower cf
207 cf.community_id = c.id) AS number_of_subscribers,
214 p.community_id = c.id) AS number_of_posts,
222 c.id = p.community_id
223 AND p.id = co.post_id) AS number_of_comments,
227 FROM community_follower cf
229 cf.community_id = c.id), c.published) AS hot_rank
233 CREATE MATERIALIZED VIEW community_aggregates_mview AS
237 community_aggregates_view;
239 CREATE UNIQUE INDEX idx_community_aggregates_mview_id ON community_aggregates_mview (id);
241 CREATE VIEW community_view AS
242 with all_community AS (
246 community_aggregates_view ca
255 community_follower cf
258 AND ac.id = cf.community_id) AS subscribed
261 CROSS JOIN all_community ac
270 CREATE VIEW community_mview AS
271 with all_community AS (
275 community_aggregates_mview ca
284 community_follower cf
287 AND ac.id = cf.community_id) AS subscribed
290 CROSS JOIN all_community ac
302 DROP VIEW post_aggregates_view;
305 CREATE VIEW post_aggregates_view AS
314 p.creator_id = u.id) AS banned,
319 community_user_ban cb
321 p.creator_id = cb.user_id
322 AND p.community_id = cb.community_id) AS banned_from_community,
329 p.creator_id = user_.id) AS creator_actor_id,
336 p.creator_id = user_.id) AS creator_local,
343 p.creator_id = user_.id) AS creator_name,
350 p.creator_id = user_.id) AS creator_avatar,
357 p.community_id = community.id) AS community_actor_id,
364 p.community_id = community.id) AS community_local,
371 p.community_id = community.id) AS community_name,
378 p.community_id = c.id) AS community_removed,
385 p.community_id = c.id) AS community_deleted,
392 p.community_id = c.id) AS community_nsfw,
399 comment.post_id = p.id) AS number_of_comments,
400 coalesce(sum(pl.score), 0) AS score,
402 CASE WHEN pl.score = 1 THEN
408 CASE WHEN pl.score = - 1 THEN
413 hot_rank (coalesce(sum(pl.score), 0), (
414 CASE WHEN (p.published < ('now'::timestamp - '1 month'::interval)) THEN
415 p.published -- Prevents necro-bumps
417 greatest (c.recent_comment_time, p.published)
420 CASE WHEN (p.published < ('now'::timestamp - '1 month'::interval)) THEN
421 p.published -- Prevents necro-bumps
423 greatest (c.recent_comment_time, p.published)
424 END) AS newest_activity_time
427 LEFT JOIN post_like pl ON p.id = pl.post_id
431 max(published) AS recent_comment_time
435 1) c ON p.id = c.post_id
438 c.recent_comment_time;
440 CREATE MATERIALIZED VIEW post_aggregates_mview AS
444 post_aggregates_view;
446 CREATE UNIQUE INDEX idx_post_aggregates_mview_id ON post_aggregates_mview (id);
448 CREATE VIEW post_view AS
453 post_aggregates_view pa
458 coalesce(pl.score, 0) AS my_vote,
463 community_follower cf
466 AND cf.community_id = ap.community_id) AS subscribed,
474 AND pr.post_id = ap.id) AS read,
482 AND ps.post_id = ap.id) AS saved
485 CROSS JOIN all_post ap
486 LEFT JOIN post_like pl ON u.id = pl.user_id
487 AND ap.id = pl.post_id
499 CREATE VIEW post_mview AS
504 post_aggregates_mview pa
509 coalesce(pl.score, 0) AS my_vote,
514 community_follower cf
517 AND cf.community_id = ap.community_id) AS subscribed,
525 AND pr.post_id = ap.id) AS read,
533 AND ps.post_id = ap.id) AS saved
536 CROSS JOIN all_post ap
537 LEFT JOIN post_like pl ON u.id = pl.user_id
538 AND ap.id = pl.post_id
550 DROP TRIGGER refresh_post ON post;
552 CREATE TRIGGER refresh_post
553 AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE ON post
555 EXECUTE PROCEDURE refresh_post ();
557 CREATE OR REPLACE FUNCTION refresh_post ()
562 REFRESH MATERIALIZED VIEW CONCURRENTLY post_aggregates_mview;
563 REFRESH MATERIALIZED VIEW CONCURRENTLY user_mview;
568 -- User mention, comment, reply
569 DROP VIEW user_mention_view;
571 DROP VIEW comment_view;
573 DROP VIEW comment_aggregates_view;
575 -- reply and comment view
576 CREATE VIEW comment_aggregates_view AS
593 AND p.community_id = co.id) AS community_actor_id,
602 AND p.community_id = co.id) AS community_local,
611 AND p.community_id = co.id) AS community_name,
618 c.creator_id = u.id) AS banned,
623 community_user_ban cb,
626 c.creator_id = cb.user_id
628 AND p.community_id = cb.community_id) AS banned_from_community,
635 c.creator_id = user_.id) AS creator_actor_id,
642 c.creator_id = user_.id) AS creator_local,
649 c.creator_id = user_.id) AS creator_name,
656 c.creator_id = user_.id) AS creator_avatar,
657 coalesce(sum(cl.score), 0) AS score,
659 CASE WHEN cl.score = 1 THEN
665 CASE WHEN cl.score = - 1 THEN
670 hot_rank (coalesce(sum(cl.score), 0), c.published) AS hot_rank
673 LEFT JOIN comment_like cl ON c.id = cl.comment_id
677 CREATE MATERIALIZED VIEW comment_aggregates_mview AS
681 comment_aggregates_view;
683 CREATE UNIQUE INDEX idx_comment_aggregates_mview_id ON comment_aggregates_mview (id);
685 CREATE VIEW comment_view AS
686 with all_comment AS (
690 comment_aggregates_view ca
695 coalesce(cl.score, 0) AS my_vote,
700 community_follower cf
703 AND ac.community_id = cf.community_id) AS subscribed,
711 AND cs.comment_id = ac.id) AS saved
714 CROSS JOIN all_comment ac
715 LEFT JOIN comment_like cl ON u.id = cl.user_id
716 AND ac.id = cl.comment_id
727 CREATE VIEW comment_mview AS
728 with all_comment AS (
732 comment_aggregates_mview ca
737 coalesce(cl.score, 0) AS my_vote,
742 community_follower cf
745 AND ac.community_id = cf.community_id) AS subscribed,
753 AND cs.comment_id = ac.id) AS saved
756 CROSS JOIN all_comment ac
757 LEFT JOIN comment_like cl ON u.id = cl.user_id
758 AND ac.id = cl.comment_id
769 -- Do the reply_view referencing the comment_mview
770 CREATE VIEW reply_view AS
774 c2.creator_id AS sender_id,
775 c.creator_id AS recipient_id
778 INNER JOIN comment c2 ON c.id = c2.parent_id
780 c2.creator_id != c.creator_id
781 -- Do union where post is null
785 c.creator_id AS sender_id,
786 p.creator_id AS recipient_id
792 AND c.parent_id IS NULL
793 AND c.creator_id != p.creator_id
797 closereply.recipient_id
802 closereply.id = cv.id;
805 CREATE VIEW user_mention_view AS
808 um.id AS user_mention_id,
821 c.community_actor_id,
825 c.banned_from_community,
842 u.id = um.recipient_id) AS recipient_actor_id,
849 u.id = um.recipient_id) AS recipient_local
854 um.comment_id = c.id;
856 CREATE VIEW user_mention_mview AS
857 with all_comment AS (
861 comment_aggregates_mview ca
865 um.id AS user_mention_id,
878 ac.community_actor_id,
882 ac.banned_from_community,
890 coalesce(cl.score, 0) AS my_vote,
898 AND cs.comment_id = ac.id) AS saved,
906 u.id = um.recipient_id) AS recipient_actor_id,
913 u.id = um.recipient_id) AS recipient_local
916 CROSS JOIN all_comment ac
917 LEFT JOIN comment_like cl ON u.id = cl.user_id
918 AND ac.id = cl.comment_id
919 LEFT JOIN user_mention um ON um.comment_id = ac.id
923 um.id AS user_mention_id,
936 ac.community_actor_id,
940 ac.banned_from_community,
957 u.id = um.recipient_id) AS recipient_actor_id,
964 u.id = um.recipient_id) AS recipient_local
967 LEFT JOIN user_mention um ON um.comment_id = ac.id;