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)
9 -- It also adds hot_rank_active to post_view
13 add column banner text;
17 add column banner text;
19 alter table user_ add column banner text;
22 create view site_view as
24 u.name as creator_name,
25 u.preferred_username as creator_preferred_username,
26 u.avatar as creator_avatar,
27 (select count(*) from user_) as number_of_users,
28 (select count(*) from post) as number_of_posts,
29 (select count(*) from comment) as number_of_comments,
30 (select count(*) from community) as number_of_communities
32 left join user_ u on s.creator_id = u.id;
37 create view user_view as
52 u.send_notifications_to_email,
54 coalesce(pd.posts, 0) as number_of_posts,
55 coalesce(pd.score, 0) as post_score,
56 coalesce(cd.comments, 0) as number_of_comments,
57 coalesce(cd.score, 0) as comment_score
61 p.creator_id as creator_id,
62 count(distinct p.id) as posts,
63 sum(pl.score) as score
65 join post_like pl on p.id = pl.post_id
67 ) pd on u.id = pd.creator_id
71 count(distinct c.id) as comments,
72 sum(cl.score) as score
74 join comment_like cl on c.id = cl.comment_id
76 ) cd on u.id = cd.creator_id;
78 create table user_fast as select * from user_view;
79 alter table user_fast add primary key (id);
82 drop view private_message_view;
83 create view private_message_view as
86 u.name as creator_name,
87 u.preferred_username as creator_preferred_username,
88 u.avatar as creator_avatar,
89 u.actor_id as creator_actor_id,
90 u.local as creator_local,
91 u2.name as recipient_name,
92 u2.preferred_username as recipient_preferred_username,
93 u2.avatar as recipient_avatar,
94 u2.actor_id as recipient_actor_id,
95 u2.local as recipient_local
96 from private_message pm
97 inner join user_ u on u.id = pm.creator_id
98 inner join user_ u2 on u2.id = pm.recipient_id;
101 drop view post_fast_view;
102 drop table post_aggregates_fast;
104 drop view post_aggregates_view;
106 create view post_aggregates_view as
110 u.actor_id as creator_actor_id,
111 u."local" as creator_local,
112 u."name" as creator_name,
113 u."preferred_username" as creator_preferred_username,
114 u.published as creator_published,
115 u.avatar as creator_avatar,
117 cb.id::bool as banned_from_community,
119 c.actor_id as community_actor_id,
120 c."local" as community_local,
121 c."name" as community_name,
122 c.icon as community_icon,
123 c.removed as community_removed,
124 c.deleted as community_deleted,
125 c.nsfw as community_nsfw,
126 -- post score data/comment count
127 coalesce(ct.comments, 0) as number_of_comments,
128 coalesce(pl.score, 0) as score,
129 coalesce(pl.upvotes, 0) as upvotes,
130 coalesce(pl.downvotes, 0) as downvotes,
131 hot_rank(coalesce(pl.score, 1), p.published) as hot_rank,
132 hot_rank(coalesce(pl.score, 1), greatest(ct.recent_comment_time, p.published)) as hot_rank_active,
133 greatest(ct.recent_comment_time, p.published) as newest_activity_time
135 left join user_ u on p.creator_id = u.id
136 left join community_user_ban cb on p.creator_id = cb.user_id and p.community_id = cb.community_id
137 left join community c on p.community_id = c.id
141 count(*) as comments,
142 max(published) as recent_comment_time
145 ) ct on ct.post_id = p.id
150 sum(score) filter (where score = 1) as upvotes,
151 -sum(score) filter (where score = -1) as downvotes
154 ) pl on pl.post_id = p.id
157 create view post_view as
161 us.user_vote as my_vote,
162 us.is_subbed::bool as subscribed,
163 us.is_read::bool as read,
164 us.is_saved::bool as saved
165 from post_aggregates_view pav
169 coalesce(cf.community_id, 0) as is_subbed,
170 coalesce(pr.post_id, 0) as is_read,
171 coalesce(ps.post_id, 0) as is_saved,
172 coalesce(pl.score, 0) as user_vote
174 left join community_user_ban cb on u.id = cb.user_id and cb.community_id = pav.community_id
175 left join community_follower cf on u.id = cf.user_id and cf.community_id = pav.community_id
176 left join post_read pr on u.id = pr.user_id and pr.post_id = pav.id
177 left join post_saved ps on u.id = ps.user_id and ps.post_id = pav.id
178 left join post_like pl on u.id = pl.user_id and pav.id = pl.post_id
190 from post_aggregates_view pav;
192 create table post_aggregates_fast as select * from post_aggregates_view;
193 alter table post_aggregates_fast add primary key (id);
195 -- For the hot rank resorting
196 create index idx_post_aggregates_fast_hot_rank_published on post_aggregates_fast (hot_rank desc, published desc);
197 create index idx_post_aggregates_fast_hot_rank_active_published on post_aggregates_fast (hot_rank_active desc, published desc);
199 create view post_fast_view as
203 us.user_vote as my_vote,
204 us.is_subbed::bool as subscribed,
205 us.is_read::bool as read,
206 us.is_saved::bool as saved
207 from post_aggregates_fast pav
211 coalesce(cf.community_id, 0) as is_subbed,
212 coalesce(pr.post_id, 0) as is_read,
213 coalesce(ps.post_id, 0) as is_saved,
214 coalesce(pl.score, 0) as user_vote
216 left join community_user_ban cb on u.id = cb.user_id and cb.community_id = pav.community_id
217 left join community_follower cf on u.id = cf.user_id and cf.community_id = pav.community_id
218 left join post_read pr on u.id = pr.user_id and pr.post_id = pav.id
219 left join post_saved ps on u.id = ps.user_id and ps.post_id = pav.id
220 left join post_like pl on u.id = pl.user_id and pav.id = pl.post_id
232 from post_aggregates_fast pav;
235 drop view community_moderator_view;
236 drop view community_follower_view;
237 drop view community_user_ban_view;
238 drop view community_view;
239 drop view community_aggregates_view;
240 drop view community_fast_view;
241 drop table community_aggregates_fast;
243 create view community_aggregates_view as
261 u.actor_id as creator_actor_id,
262 u.local as creator_local,
263 u.name as creator_name,
264 u.preferred_username as creator_preferred_username,
265 u.avatar as creator_avatar,
266 cat.name as category_name,
267 coalesce(cf.subs, 0) as number_of_subscribers,
268 coalesce(cd.posts, 0) as number_of_posts,
269 coalesce(cd.comments, 0) as number_of_comments,
270 hot_rank(cf.subs, c.published) as hot_rank
272 left join user_ u on c.creator_id = u.id
273 left join category cat on c.category_id = cat.id
277 count(distinct p.id) as posts,
278 count(distinct ct.id) as comments
280 join comment ct on p.id = ct.post_id
281 group by p.community_id
282 ) cd on cd.community_id = c.id
287 from community_follower
288 group by community_id
289 ) cf on cf.community_id = c.id;
291 create view community_view as
295 us.is_subbed::bool as subscribed
296 from community_aggregates_view cv
300 coalesce(cf.community_id, 0) as is_subbed
302 left join community_follower cf on u.id = cf.user_id and cf.community_id = cv.id
311 from community_aggregates_view cv;
313 create view community_moderator_view as
316 u.actor_id as user_actor_id,
317 u.local as user_local,
319 u.preferred_username as user_preferred_username,
321 c.actor_id as community_actor_id,
322 c.local as community_local,
323 c.name as community_name,
324 c.icon as community_icon
325 from community_moderator cm
326 left join user_ u on cm.user_id = u.id
327 left join community c on cm.community_id = c.id;
329 create view community_follower_view as
332 u.actor_id as user_actor_id,
333 u.local as user_local,
335 u.preferred_username as user_preferred_username,
337 c.actor_id as community_actor_id,
338 c.local as community_local,
339 c.name as community_name,
340 c.icon as community_icon
341 from community_follower cf
342 left join user_ u on cf.user_id = u.id
343 left join community c on cf.community_id = c.id;
345 create view community_user_ban_view as
348 u.actor_id as user_actor_id,
349 u.local as user_local,
351 u.preferred_username as user_preferred_username,
353 c.actor_id as community_actor_id,
354 c.local as community_local,
355 c.name as community_name,
356 c.icon as community_icon
357 from community_user_ban cb
358 left join user_ u on cb.user_id = u.id
359 left join community c on cb.community_id = c.id;
361 -- The community fast table
363 create table community_aggregates_fast as select * from community_aggregates_view;
364 alter table community_aggregates_fast add primary key (id);
366 create view community_fast_view as
370 (select cf.id::boolean from community_follower cf where u.id = cf.user_id and ac.id = cf.community_id) as subscribed
375 from community_aggregates_fast ca
384 from community_aggregates_fast caf;
386 -- Comments, mentions, replies
387 drop view user_mention_view;
388 drop view reply_fast_view;
389 drop view comment_fast_view;
390 drop view comment_view;
391 drop view user_mention_fast_view;
392 drop table comment_aggregates_fast;
393 drop view comment_aggregates_view;
395 create view comment_aggregates_view as
399 p."name" as post_name,
402 c.actor_id as community_actor_id,
403 c."local" as community_local,
404 c."name" as community_name,
405 c.icon as community_icon,
408 coalesce(cb.id, 0)::bool as banned_from_community,
409 u.actor_id as creator_actor_id,
410 u.local as creator_local,
411 u.name as creator_name,
412 u.preferred_username as creator_preferred_username,
413 u.published as creator_published,
414 u.avatar as creator_avatar,
416 coalesce(cl.total, 0) as score,
417 coalesce(cl.up, 0) as upvotes,
418 coalesce(cl.down, 0) as downvotes,
419 hot_rank(coalesce(cl.total, 1), p.published) as hot_rank,
420 hot_rank(coalesce(cl.total, 1), ct.published) as hot_rank_active
422 left join post p on ct.post_id = p.id
423 left join community c on p.community_id = c.id
424 left join user_ u on ct.creator_id = u.id
425 left join community_user_ban cb on ct.creator_id = cb.user_id and p.id = ct.post_id and p.community_id = cb.community_id
429 sum(l.score) as total,
430 count(case when l.score = 1 then 1 else null end) as up,
431 count(case when l.score = -1 then 1 else null end) as down
434 ) as cl on cl.id = ct.id;
436 create or replace view comment_view as (
439 us.user_id as user_id,
440 us.my_vote as my_vote,
441 us.is_subbed::bool as subscribed,
442 us.is_saved::bool as saved
443 from comment_aggregates_view cav
447 coalesce(cl.score, 0) as my_vote,
448 coalesce(cf.id, 0) as is_subbed,
449 coalesce(cs.id, 0) as is_saved
451 left join comment_like cl on u.id = cl.user_id and cav.id = cl.comment_id
452 left join comment_saved cs on u.id = cs.user_id and cs.comment_id = cav.id
453 left join community_follower cf on u.id = cf.user_id and cav.community_id = cf.community_id
464 from comment_aggregates_view cav
467 create table comment_aggregates_fast as select * from comment_aggregates_view;
468 alter table comment_aggregates_fast add primary key (id);
470 create view comment_fast_view as
473 us.user_id as user_id,
474 us.my_vote as my_vote,
475 us.is_subbed::bool as subscribed,
476 us.is_saved::bool as saved
477 from comment_aggregates_fast cav
481 coalesce(cl.score, 0) as my_vote,
482 coalesce(cf.id, 0) as is_subbed,
483 coalesce(cs.id, 0) as is_saved
485 left join comment_like cl on u.id = cl.user_id and cav.id = cl.comment_id
486 left join comment_saved cs on u.id = cs.user_id and cs.comment_id = cav.id
487 left join community_follower cf on u.id = cf.user_id and cav.community_id = cf.community_id
498 from comment_aggregates_fast cav;
500 create view user_mention_view as
503 um.id as user_mention_id,
517 c.community_actor_id,
522 c.banned_from_community,
524 c.creator_preferred_username,
535 (select actor_id from user_ u where u.id = um.recipient_id) as recipient_actor_id,
536 (select local from user_ u where u.id = um.recipient_id) as recipient_local
537 from user_mention um, comment_view c
538 where um.comment_id = c.id;
540 create view user_mention_fast_view as
543 um.id as user_mention_id,
557 ac.community_actor_id,
562 ac.banned_from_community,
564 ac.creator_preferred_username,
572 coalesce(cl.score, 0) as my_vote,
573 (select cs.id::bool from comment_saved cs where u.id = cs.user_id and cs.comment_id = ac.id) as saved,
575 (select actor_id from user_ u where u.id = um.recipient_id) as recipient_actor_id,
576 (select local from user_ u where u.id = um.recipient_id) as recipient_local
581 from comment_aggregates_fast ca
583 left join comment_like cl on u.id = cl.user_id and ac.id = cl.comment_id
584 left join user_mention um on um.comment_id = ac.id
590 um.id as user_mention_id,
604 ac.community_actor_id,
609 ac.banned_from_community,
611 ac.creator_preferred_username,
622 (select actor_id from user_ u where u.id = um.recipient_id) as recipient_actor_id,
623 (select local from user_ u where u.id = um.recipient_id) as recipient_local
624 from comment_aggregates_fast ac
625 left join user_mention um on um.comment_id = ac.id
628 -- Do the reply_view referencing the comment_fast_view
629 create view reply_fast_view as
633 c2.creator_id as sender_id,
634 c.creator_id as recipient_id
636 inner join comment c2 on c.id = c2.parent_id
637 where c2.creator_id != c.creator_id
638 -- Do union where post is null
642 c.creator_id as sender_id,
643 p.creator_id as recipient_id
644 from comment c, post p
645 where c.post_id = p.id and c.parent_id is null and c.creator_id != p.creator_id
648 closereply.recipient_id
649 from comment_fast_view cv, closereply
650 where closereply.id = cv.id
653 -- Adding hot rank active to the triggers
654 create or replace function refresh_post()
655 returns trigger language plpgsql
658 IF (TG_OP = 'DELETE') THEN
659 delete from post_aggregates_fast where id = OLD.id;
661 -- Update community number of posts
662 update community_aggregates_fast set number_of_posts = number_of_posts - 1 where id = OLD.community_id;
663 ELSIF (TG_OP = 'UPDATE') THEN
664 delete from post_aggregates_fast where id = OLD.id;
665 insert into post_aggregates_fast select * from post_aggregates_view where id = NEW.id;
666 ELSIF (TG_OP = 'INSERT') THEN
667 insert into post_aggregates_fast select * from post_aggregates_view where id = NEW.id;
669 -- Update that users number of posts, post score
670 delete from user_fast where id = NEW.creator_id;
671 insert into user_fast select * from user_view where id = NEW.creator_id;
673 -- Update community number of posts
674 update community_aggregates_fast set number_of_posts = number_of_posts + 1 where id = NEW.community_id;
676 -- Update the hot rank on the post table
677 -- TODO this might not correctly update it, using a 1 week interval
678 update post_aggregates_fast as paf
680 hot_rank = pav.hot_rank,
681 hot_rank_active = pav.hot_rank_active
682 from post_aggregates_view as pav
683 where paf.id = pav.id and (pav.published > ('now'::timestamp - '1 week'::interval));
689 create or replace function refresh_comment()
690 returns trigger language plpgsql
693 IF (TG_OP = 'DELETE') THEN
694 delete from comment_aggregates_fast where id = OLD.id;
696 -- Update community number of comments
697 update community_aggregates_fast as caf
698 set number_of_comments = number_of_comments - 1
700 where caf.id = p.community_id and p.id = OLD.post_id;
702 ELSIF (TG_OP = 'UPDATE') THEN
703 delete from comment_aggregates_fast where id = OLD.id;
704 insert into comment_aggregates_fast select * from comment_aggregates_view where id = NEW.id;
705 ELSIF (TG_OP = 'INSERT') THEN
706 insert into comment_aggregates_fast select * from comment_aggregates_view where id = NEW.id;
708 -- Update user view due to comment count
710 set number_of_comments = number_of_comments + 1
711 where id = NEW.creator_id;
713 -- Update post view due to comment count, new comment activity time, but only on new posts
714 -- TODO this could be done more efficiently
715 delete from post_aggregates_fast where id = NEW.post_id;
716 insert into post_aggregates_fast select * from post_aggregates_view where id = NEW.post_id;
718 -- Update the comment hot_ranks as of last week
719 update comment_aggregates_fast as caf
721 hot_rank = cav.hot_rank,
722 hot_rank_active = cav.hot_rank_active
723 from comment_aggregates_view as cav
724 where caf.id = cav.id and (cav.published > ('now'::timestamp - '1 week'::interval));
726 -- Update the post ranks
727 update post_aggregates_fast as paf
729 hot_rank = pav.hot_rank,
730 hot_rank_active = pav.hot_rank_active
731 from post_aggregates_view as pav
732 where paf.id = pav.id and (pav.published > ('now'::timestamp - '1 week'::interval));
734 -- Force the hot rank active as zero on 2 day-older posts (necro-bump)
735 update post_aggregates_fast as paf
736 set hot_rank_active = 0
737 where paf.id = NEW.post_id and (paf.published < ('now'::timestamp - '2 days'::interval));
739 -- Update community number of comments
740 update community_aggregates_fast as caf
741 set number_of_comments = number_of_comments + 1
743 where caf.id = p.community_id and p.id = NEW.post_id;