1 -- This adds the following columns, as well as updates the views:
6 -- User Banner (User avatar is already there)
7 -- User preferred name (already in table, needs to be added to view)
8 -- It also adds hot_rank_active to post_view
11 ADD COLUMN banner text;
15 ADD COLUMN banner text;
18 ADD COLUMN banner text;
22 CREATE VIEW site_view AS
25 u.name AS creator_name,
26 u.preferred_username AS creator_preferred_username,
27 u.avatar AS creator_avatar,
32 user_) AS number_of_users,
37 post) AS number_of_posts,
42 comment) AS number_of_comments,
47 community) AS number_of_communities
50 LEFT JOIN user_ u ON s.creator_id = u.id;
57 CREATE VIEW user_view AS
72 u.send_notifications_to_email,
74 coalesce(pd.posts, 0) AS number_of_posts,
75 coalesce(pd.score, 0) AS post_score,
76 coalesce(cd.comments, 0) AS number_of_comments,
77 coalesce(cd.score, 0) AS comment_score
82 p.creator_id AS creator_id,
83 count(DISTINCT p.id) AS posts,
84 sum(pl.score) AS score
87 JOIN post_like pl ON p.id = pl.post_id
89 p.creator_id) pd ON u.id = pd.creator_id
93 count(DISTINCT c.id) AS comments,
94 sum(cl.score) AS score
97 JOIN comment_like cl ON c.id = cl.comment_id
99 c.creator_id) cd ON u.id = cd.creator_id;
101 CREATE TABLE user_fast AS
107 ALTER TABLE user_fast
108 ADD PRIMARY KEY (id);
111 DROP VIEW private_message_view;
113 CREATE VIEW private_message_view AS
116 u.name AS creator_name,
117 u.preferred_username AS creator_preferred_username,
118 u.avatar AS creator_avatar,
119 u.actor_id AS creator_actor_id,
120 u.local AS creator_local,
121 u2.name AS recipient_name,
122 u2.preferred_username AS recipient_preferred_username,
123 u2.avatar AS recipient_avatar,
124 u2.actor_id AS recipient_actor_id,
125 u2.local AS recipient_local
128 INNER JOIN user_ u ON u.id = pm.creator_id
129 INNER JOIN user_ u2 ON u2.id = pm.recipient_id;
132 DROP VIEW post_fast_view;
134 DROP TABLE post_aggregates_fast;
138 DROP VIEW post_aggregates_view;
140 CREATE VIEW post_aggregates_view AS
144 u.actor_id AS creator_actor_id,
145 u."local" AS creator_local,
146 u."name" AS creator_name,
147 u."preferred_username" AS creator_preferred_username,
148 u.published AS creator_published,
149 u.avatar AS creator_avatar,
151 cb.id::bool AS banned_from_community,
153 c.actor_id AS community_actor_id,
154 c."local" AS community_local,
155 c."name" AS community_name,
156 c.icon AS community_icon,
157 c.removed AS community_removed,
158 c.deleted AS community_deleted,
159 c.nsfw AS community_nsfw,
160 -- post score data/comment count
161 coalesce(ct.comments, 0) AS number_of_comments,
162 coalesce(pl.score, 0) AS score,
163 coalesce(pl.upvotes, 0) AS upvotes,
164 coalesce(pl.downvotes, 0) AS downvotes,
165 hot_rank (coalesce(pl.score, 1), p.published) AS hot_rank,
166 hot_rank (coalesce(pl.score, 1), greatest (ct.recent_comment_time, p.published)) AS hot_rank_active,
167 greatest (ct.recent_comment_time, p.published) AS newest_activity_time
170 LEFT JOIN user_ u ON p.creator_id = u.id
171 LEFT JOIN community_user_ban cb ON p.creator_id = cb.user_id
172 AND p.community_id = cb.community_id
173 LEFT JOIN community c ON p.community_id = c.id
177 count(*) AS comments,
178 max(published) AS recent_comment_time
182 post_id) ct ON ct.post_id = p.id
187 sum(score) FILTER (WHERE score = 1) AS upvotes,
188 - sum(score) FILTER (WHERE score = - 1) AS downvotes
192 post_id) pl ON pl.post_id = p.id
196 CREATE VIEW post_view AS
200 us.user_vote AS my_vote,
201 us.is_subbed::bool AS subscribed,
202 us.is_read::bool AS read,
203 us.is_saved::bool AS saved
205 post_aggregates_view pav
209 coalesce(cf.community_id, 0) AS is_subbed,
210 coalesce(pr.post_id, 0) AS is_read,
211 coalesce(ps.post_id, 0) AS is_saved,
212 coalesce(pl.score, 0) AS user_vote
215 LEFT JOIN community_user_ban cb ON u.id = cb.user_id
216 AND cb.community_id = pav.community_id
217 LEFT JOIN community_follower cf ON u.id = cf.user_id
218 AND cf.community_id = pav.community_id
219 LEFT JOIN post_read pr ON u.id = pr.user_id
220 AND pr.post_id = pav.id
221 LEFT JOIN post_saved ps ON u.id = ps.user_id
222 AND ps.post_id = pav.id
223 LEFT JOIN post_like pl ON u.id = pl.user_id
224 AND pav.id = pl.post_id) AS us
234 post_aggregates_view pav;
236 CREATE TABLE post_aggregates_fast AS
240 post_aggregates_view;
242 ALTER TABLE post_aggregates_fast
243 ADD PRIMARY KEY (id);
245 -- For the hot rank resorting
246 CREATE INDEX idx_post_aggregates_fast_hot_rank_published ON post_aggregates_fast (hot_rank DESC, published DESC);
248 CREATE INDEX idx_post_aggregates_fast_hot_rank_active_published ON post_aggregates_fast (hot_rank_active DESC, published DESC);
250 CREATE VIEW post_fast_view AS
254 us.user_vote AS my_vote,
255 us.is_subbed::bool AS subscribed,
256 us.is_read::bool AS read,
257 us.is_saved::bool AS saved
259 post_aggregates_fast pav
263 coalesce(cf.community_id, 0) AS is_subbed,
264 coalesce(pr.post_id, 0) AS is_read,
265 coalesce(ps.post_id, 0) AS is_saved,
266 coalesce(pl.score, 0) AS user_vote
269 LEFT JOIN community_user_ban cb ON u.id = cb.user_id
270 AND cb.community_id = pav.community_id
271 LEFT JOIN community_follower cf ON u.id = cf.user_id
272 AND cf.community_id = pav.community_id
273 LEFT JOIN post_read pr ON u.id = pr.user_id
274 AND pr.post_id = pav.id
275 LEFT JOIN post_saved ps ON u.id = ps.user_id
276 AND ps.post_id = pav.id
277 LEFT JOIN post_like pl ON u.id = pl.user_id
278 AND pav.id = pl.post_id) AS us
288 post_aggregates_fast pav;
291 DROP VIEW community_moderator_view;
293 DROP VIEW community_follower_view;
295 DROP VIEW community_user_ban_view;
297 DROP VIEW community_view;
299 DROP VIEW community_aggregates_view;
301 DROP VIEW community_fast_view;
303 DROP TABLE community_aggregates_fast;
305 CREATE VIEW community_aggregates_view AS
323 u.actor_id AS creator_actor_id,
324 u.local AS creator_local,
325 u.name AS creator_name,
326 u.preferred_username AS creator_preferred_username,
327 u.avatar AS creator_avatar,
328 cat.name AS category_name,
329 coalesce(cf.subs, 0) AS number_of_subscribers,
330 coalesce(cd.posts, 0) AS number_of_posts,
331 coalesce(cd.comments, 0) AS number_of_comments,
332 hot_rank (cf.subs, c.published) AS hot_rank
335 LEFT JOIN user_ u ON c.creator_id = u.id
336 LEFT JOIN category cat ON c.category_id = cat.id
340 count(DISTINCT p.id) AS posts,
341 count(DISTINCT ct.id) AS comments
344 JOIN comment ct ON p.id = ct.post_id
346 p.community_id) cd ON cd.community_id = c.id
354 community_id) cf ON cf.community_id = c.id;
356 CREATE VIEW community_view AS
360 us.is_subbed::bool AS subscribed
362 community_aggregates_view cv
366 coalesce(cf.community_id, 0) AS is_subbed
369 LEFT JOIN community_follower cf ON u.id = cf.user_id
370 AND cf.community_id = cv.id) AS us
377 community_aggregates_view cv;
379 CREATE VIEW community_moderator_view AS
382 u.actor_id AS user_actor_id,
383 u.local AS user_local,
385 u.preferred_username AS user_preferred_username,
387 c.actor_id AS community_actor_id,
388 c.local AS community_local,
389 c.name AS community_name,
390 c.icon AS community_icon
392 community_moderator cm
393 LEFT JOIN user_ u ON cm.user_id = u.id
394 LEFT JOIN community c ON cm.community_id = c.id;
396 CREATE VIEW community_follower_view AS
399 u.actor_id AS user_actor_id,
400 u.local AS user_local,
402 u.preferred_username AS user_preferred_username,
404 c.actor_id AS community_actor_id,
405 c.local AS community_local,
406 c.name AS community_name,
407 c.icon AS community_icon
409 community_follower cf
410 LEFT JOIN user_ u ON cf.user_id = u.id
411 LEFT JOIN community c ON cf.community_id = c.id;
413 CREATE VIEW community_user_ban_view AS
416 u.actor_id AS user_actor_id,
417 u.local AS user_local,
419 u.preferred_username AS user_preferred_username,
421 c.actor_id AS community_actor_id,
422 c.local AS community_local,
423 c.name AS community_name,
424 c.icon AS community_icon
426 community_user_ban cb
427 LEFT JOIN user_ u ON cb.user_id = u.id
428 LEFT JOIN community c ON cb.community_id = c.id;
430 -- The community fast table
431 CREATE TABLE community_aggregates_fast AS
435 community_aggregates_view;
437 ALTER TABLE community_aggregates_fast
438 ADD PRIMARY KEY (id);
440 CREATE VIEW community_fast_view AS
448 community_follower cf
451 AND ac.id = cf.community_id) AS subscribed
458 community_aggregates_fast ca) ac
465 community_aggregates_fast caf;
467 -- Comments, mentions, replies
468 DROP VIEW user_mention_view;
470 DROP VIEW reply_fast_view;
472 DROP VIEW comment_fast_view;
474 DROP VIEW comment_view;
476 DROP VIEW user_mention_fast_view;
478 DROP TABLE comment_aggregates_fast;
480 DROP VIEW comment_aggregates_view;
482 CREATE VIEW comment_aggregates_view AS
486 p."name" AS post_name,
489 c.actor_id AS community_actor_id,
490 c."local" AS community_local,
491 c."name" AS community_name,
492 c.icon AS community_icon,
495 coalesce(cb.id, 0)::bool AS banned_from_community,
496 u.actor_id AS creator_actor_id,
497 u.local AS creator_local,
498 u.name AS creator_name,
499 u.preferred_username AS creator_preferred_username,
500 u.published AS creator_published,
501 u.avatar AS creator_avatar,
503 coalesce(cl.total, 0) AS score,
504 coalesce(cl.up, 0) AS upvotes,
505 coalesce(cl.down, 0) AS downvotes,
506 hot_rank (coalesce(cl.total, 1), p.published) AS hot_rank,
507 hot_rank (coalesce(cl.total, 1), ct.published) AS hot_rank_active
510 LEFT JOIN post p ON ct.post_id = p.id
511 LEFT JOIN community c ON p.community_id = c.id
512 LEFT JOIN user_ u ON ct.creator_id = u.id
513 LEFT JOIN community_user_ban cb ON ct.creator_id = cb.user_id
514 AND p.id = ct.post_id
515 AND p.community_id = cb.community_id
519 sum(l.score) AS total,
521 CASE WHEN l.score = 1 THEN
527 CASE WHEN l.score = - 1 THEN
535 comment_id) AS cl ON cl.id = ct.id;
537 CREATE OR REPLACE VIEW comment_view AS (
540 us.user_id AS user_id,
541 us.my_vote AS my_vote,
542 us.is_subbed::bool AS subscribed,
543 us.is_saved::bool AS saved
545 comment_aggregates_view cav
549 coalesce(cl.score, 0) AS my_vote,
550 coalesce(cf.id, 0) AS is_subbed,
551 coalesce(cs.id, 0) AS is_saved
554 LEFT JOIN comment_like cl ON u.id = cl.user_id
555 AND cav.id = cl.comment_id
556 LEFT JOIN comment_saved cs ON u.id = cs.user_id
557 AND cs.comment_id = cav.id
558 LEFT JOIN community_follower cf ON u.id = cf.user_id
559 AND cav.community_id = cf.community_id) AS us
568 comment_aggregates_view cav);
570 CREATE TABLE comment_aggregates_fast AS
574 comment_aggregates_view;
576 ALTER TABLE comment_aggregates_fast
577 ADD PRIMARY KEY (id);
579 CREATE VIEW comment_fast_view AS
582 us.user_id AS user_id,
583 us.my_vote AS my_vote,
584 us.is_subbed::bool AS subscribed,
585 us.is_saved::bool AS saved
587 comment_aggregates_fast cav
591 coalesce(cl.score, 0) AS my_vote,
592 coalesce(cf.id, 0) AS is_subbed,
593 coalesce(cs.id, 0) AS is_saved
596 LEFT JOIN comment_like cl ON u.id = cl.user_id
597 AND cav.id = cl.comment_id
598 LEFT JOIN comment_saved cs ON u.id = cs.user_id
599 AND cs.comment_id = cav.id
600 LEFT JOIN community_follower cf ON u.id = cf.user_id
601 AND cav.community_id = cf.community_id) AS us
610 comment_aggregates_fast cav;
612 CREATE VIEW user_mention_view AS
615 um.id AS user_mention_id,
629 c.community_actor_id,
634 c.banned_from_community,
636 c.creator_preferred_username,
653 u.id = um.recipient_id) AS recipient_actor_id,
660 u.id = um.recipient_id) AS recipient_local
665 um.comment_id = c.id;
667 CREATE VIEW user_mention_fast_view AS
670 um.id AS user_mention_id,
684 ac.community_actor_id,
689 ac.banned_from_community,
691 ac.creator_preferred_username,
699 coalesce(cl.score, 0) AS my_vote,
707 AND cs.comment_id = ac.id) AS saved,
715 u.id = um.recipient_id) AS recipient_actor_id,
722 u.id = um.recipient_id) AS recipient_local
729 comment_aggregates_fast ca) ac
730 LEFT JOIN comment_like cl ON u.id = cl.user_id
731 AND ac.id = cl.comment_id
732 LEFT JOIN user_mention um ON um.comment_id = ac.id
736 um.id AS user_mention_id,
750 ac.community_actor_id,
755 ac.banned_from_community,
757 ac.creator_preferred_username,
774 u.id = um.recipient_id) AS recipient_actor_id,
781 u.id = um.recipient_id) AS recipient_local
783 comment_aggregates_fast ac
784 LEFT JOIN user_mention um ON um.comment_id = ac.id;
786 -- Do the reply_view referencing the comment_fast_view
787 CREATE VIEW reply_fast_view AS
791 c2.creator_id AS sender_id,
792 c.creator_id AS recipient_id
795 INNER JOIN comment c2 ON c.id = c2.parent_id
797 c2.creator_id != c.creator_id
798 -- Do union where post is null
802 c.creator_id AS sender_id,
803 p.creator_id AS recipient_id
809 AND c.parent_id IS NULL
810 AND c.creator_id != p.creator_id
814 closereply.recipient_id
816 comment_fast_view cv,
819 closereply.id = cv.id;
821 -- Adding hot rank active to the triggers
822 CREATE OR REPLACE FUNCTION refresh_post ()
827 IF (TG_OP = 'DELETE') THEN
828 DELETE FROM post_aggregates_fast
830 -- Update community number of posts
832 community_aggregates_fast
834 number_of_posts = number_of_posts - 1
836 id = OLD.community_id;
837 ELSIF (TG_OP = 'UPDATE') THEN
838 DELETE FROM post_aggregates_fast
840 INSERT INTO post_aggregates_fast
847 ELSIF (TG_OP = 'INSERT') THEN
848 INSERT INTO post_aggregates_fast
855 -- Update that users number of posts, post score
856 DELETE FROM user_fast
857 WHERE id = NEW.creator_id;
858 INSERT INTO user_fast
865 -- Update community number of posts
867 community_aggregates_fast
869 number_of_posts = number_of_posts + 1
871 id = NEW.community_id;
872 -- Update the hot rank on the post table
873 -- TODO this might not correctly update it, using a 1 week interval
875 post_aggregates_fast AS paf
877 hot_rank = pav.hot_rank,
878 hot_rank_active = pav.hot_rank_active
880 post_aggregates_view AS pav
883 AND (pav.published > ('now'::timestamp - '1 week'::interval));
889 CREATE OR REPLACE FUNCTION refresh_comment ()
894 IF (TG_OP = 'DELETE') THEN
895 DELETE FROM comment_aggregates_fast
897 -- Update community number of comments
899 community_aggregates_fast AS caf
901 number_of_comments = number_of_comments - 1
905 caf.id = p.community_id
906 AND p.id = OLD.post_id;
907 ELSIF (TG_OP = 'UPDATE') THEN
908 DELETE FROM comment_aggregates_fast
910 INSERT INTO comment_aggregates_fast
914 comment_aggregates_view
917 ELSIF (TG_OP = 'INSERT') THEN
918 INSERT INTO comment_aggregates_fast
922 comment_aggregates_view
925 -- Update user view due to comment count
929 number_of_comments = number_of_comments + 1
932 -- Update post view due to comment count, new comment activity time, but only on new posts
933 -- TODO this could be done more efficiently
934 DELETE FROM post_aggregates_fast
935 WHERE id = NEW.post_id;
936 INSERT INTO post_aggregates_fast
943 -- Update the comment hot_ranks as of last week
945 comment_aggregates_fast AS caf
947 hot_rank = cav.hot_rank,
948 hot_rank_active = cav.hot_rank_active
950 comment_aggregates_view AS cav
953 AND (cav.published > ('now'::timestamp - '1 week'::interval));
954 -- Update the post ranks
956 post_aggregates_fast AS paf
958 hot_rank = pav.hot_rank,
959 hot_rank_active = pav.hot_rank_active
961 post_aggregates_view AS pav
964 AND (pav.published > ('now'::timestamp - '1 week'::interval));
965 -- Force the hot rank active as zero on 2 day-older posts (necro-bump)
967 post_aggregates_fast AS paf
972 AND (paf.published < ('now'::timestamp - '2 days'::interval));
973 -- Update community number of comments
975 community_aggregates_fast AS caf
977 number_of_comments = number_of_comments + 1
981 caf.id = p.community_id
982 AND p.id = NEW.post_id;