5 drop view post_fast_view;
6 drop table post_aggregates_fast;
8 drop view post_aggregates_view;
9 drop view community_moderator_view;
10 drop view community_follower_view;
11 drop view community_user_ban_view;
12 drop view community_view;
13 drop view community_aggregates_view;
14 drop view community_fast_view;
15 drop table community_aggregates_fast;
16 drop view private_message_view;
17 drop view user_mention_view;
18 drop view reply_fast_view;
19 drop view comment_fast_view;
20 drop view comment_view;
21 drop view user_mention_fast_view;
22 drop table comment_aggregates_fast;
23 drop view comment_aggregates_view;
33 alter table user_ drop column banner;
36 create view site_view as
38 (select name from user_ u where s.creator_id = u.id) as creator_name,
39 (select avatar from user_ u where s.creator_id = u.id) as creator_avatar,
40 (select count(*) from user_) as number_of_users,
41 (select count(*) from post) as number_of_posts,
42 (select count(*) from comment) as number_of_comments,
43 (select count(*) from community) as number_of_communities
47 create view user_view as
60 u.send_notifications_to_email,
62 coalesce(pd.posts, 0) as number_of_posts,
63 coalesce(pd.score, 0) as post_score,
64 coalesce(cd.comments, 0) as number_of_comments,
65 coalesce(cd.score, 0) as comment_score
69 p.creator_id as creator_id,
70 count(distinct p.id) as posts,
71 sum(pl.score) as score
73 join post_like pl on p.id = pl.post_id
75 ) pd on u.id = pd.creator_id
79 count(distinct c.id) as comments,
80 sum(cl.score) as score
82 join comment_like cl on c.id = cl.comment_id
84 ) cd on u.id = cd.creator_id;
86 create table user_fast as select * from user_view;
87 alter table user_fast add primary key (id);
91 create view post_aggregates_view as
95 u.actor_id as creator_actor_id,
96 u."local" as creator_local,
97 u."name" as creator_name,
98 u.published as creator_published,
99 u.avatar as creator_avatar,
101 cb.id::bool as banned_from_community,
103 c.actor_id as community_actor_id,
104 c."local" as community_local,
105 c."name" as community_name,
106 c.removed as community_removed,
107 c.deleted as community_deleted,
108 c.nsfw as community_nsfw,
109 -- post score data/comment count
110 coalesce(ct.comments, 0) as number_of_comments,
111 coalesce(pl.score, 0) as score,
112 coalesce(pl.upvotes, 0) as upvotes,
113 coalesce(pl.downvotes, 0) as downvotes,
115 coalesce(pl.score , 0), (
117 when (p.published < ('now'::timestamp - '1 month'::interval))
119 else greatest(ct.recent_comment_time, p.published)
125 when (p.published < ('now'::timestamp - '1 month'::interval))
127 else greatest(ct.recent_comment_time, p.published)
129 ) as newest_activity_time
131 left join user_ u on p.creator_id = u.id
132 left join community_user_ban cb on p.creator_id = cb.user_id and p.community_id = cb.community_id
133 left join community c on p.community_id = c.id
137 count(*) as comments,
138 max(published) as recent_comment_time
141 ) ct on ct.post_id = p.id
146 sum(score) filter (where score = 1) as upvotes,
147 -sum(score) filter (where score = -1) as downvotes
150 ) pl on pl.post_id = p.id
153 create view post_view as
157 us.user_vote as my_vote,
158 us.is_subbed::bool as subscribed,
159 us.is_read::bool as read,
160 us.is_saved::bool as saved
161 from post_aggregates_view pav
165 coalesce(cf.community_id, 0) as is_subbed,
166 coalesce(pr.post_id, 0) as is_read,
167 coalesce(ps.post_id, 0) as is_saved,
168 coalesce(pl.score, 0) as user_vote
170 left join community_user_ban cb on u.id = cb.user_id and cb.community_id = pav.community_id
171 left join community_follower cf on u.id = cf.user_id and cf.community_id = pav.community_id
172 left join post_read pr on u.id = pr.user_id and pr.post_id = pav.id
173 left join post_saved ps on u.id = ps.user_id and ps.post_id = pav.id
174 left join post_like pl on u.id = pl.user_id and pav.id = pl.post_id
186 from post_aggregates_view pav;
188 create table post_aggregates_fast as select * from post_aggregates_view;
189 alter table post_aggregates_fast add primary key (id);
191 create view post_fast_view as
195 us.user_vote as my_vote,
196 us.is_subbed::bool as subscribed,
197 us.is_read::bool as read,
198 us.is_saved::bool as saved
199 from post_aggregates_fast pav
203 coalesce(cf.community_id, 0) as is_subbed,
204 coalesce(pr.post_id, 0) as is_read,
205 coalesce(ps.post_id, 0) as is_saved,
206 coalesce(pl.score, 0) as user_vote
208 left join community_user_ban cb on u.id = cb.user_id and cb.community_id = pav.community_id
209 left join community_follower cf on u.id = cf.user_id and cf.community_id = pav.community_id
210 left join post_read pr on u.id = pr.user_id and pr.post_id = pav.id
211 left join post_saved ps on u.id = ps.user_id and ps.post_id = pav.id
212 left join post_like pl on u.id = pl.user_id and pav.id = pl.post_id
224 from post_aggregates_fast pav;
227 create view community_aggregates_view as
243 u.actor_id as creator_actor_id,
244 u.local as creator_local,
245 u.name as creator_name,
246 u.avatar as creator_avatar,
247 cat.name as category_name,
248 coalesce(cf.subs, 0) as number_of_subscribers,
249 coalesce(cd.posts, 0) as number_of_posts,
250 coalesce(cd.comments, 0) as number_of_comments,
251 hot_rank(cf.subs, c.published) as hot_rank
253 left join user_ u on c.creator_id = u.id
254 left join category cat on c.category_id = cat.id
258 count(distinct p.id) as posts,
259 count(distinct ct.id) as comments
261 join comment ct on p.id = ct.post_id
262 group by p.community_id
263 ) cd on cd.community_id = c.id
268 from community_follower
269 group by community_id
270 ) cf on cf.community_id = c.id;
272 create view community_view as
276 us.is_subbed::bool as subscribed
277 from community_aggregates_view cv
281 coalesce(cf.community_id, 0) as is_subbed
283 left join community_follower cf on u.id = cf.user_id and cf.community_id = cv.id
292 from community_aggregates_view cv;
294 create view community_moderator_view as
297 u.actor_id as user_actor_id,
298 u.local as user_local,
301 c.actor_id as community_actor_id,
302 c.local as community_local,
303 c.name as community_name
304 from community_moderator cm
305 left join user_ u on cm.user_id = u.id
306 left join community c on cm.community_id = c.id;
308 create view community_follower_view as
311 u.actor_id as user_actor_id,
312 u.local as user_local,
315 c.actor_id as community_actor_id,
316 c.local as community_local,
317 c.name as community_name
318 from community_follower cf
319 left join user_ u on cf.user_id = u.id
320 left join community c on cf.community_id = c.id;
322 create view community_user_ban_view as
325 u.actor_id as user_actor_id,
326 u.local as user_local,
329 c.actor_id as community_actor_id,
330 c.local as community_local,
331 c.name as community_name
332 from community_user_ban cb
333 left join user_ u on cb.user_id = u.id
334 left join community c on cb.community_id = c.id;
336 -- The community fast table
338 create table community_aggregates_fast as select * from community_aggregates_view;
339 alter table community_aggregates_fast add primary key (id);
341 create view community_fast_view as
345 (select cf.id::boolean from community_follower cf where u.id = cf.user_id and ac.id = cf.community_id) as subscribed
350 from community_aggregates_fast ca
359 from community_aggregates_fast caf;
363 create view private_message_view as
366 u.name as creator_name,
367 u.avatar as creator_avatar,
368 u.actor_id as creator_actor_id,
369 u.local as creator_local,
370 u2.name as recipient_name,
371 u2.avatar as recipient_avatar,
372 u2.actor_id as recipient_actor_id,
373 u2.local as recipient_local
374 from private_message pm
375 inner join user_ u on u.id = pm.creator_id
376 inner join user_ u2 on u2.id = pm.recipient_id;
379 -- Comments, mentions, replies
381 create view comment_aggregates_view as
385 p."name" as post_name,
388 c.actor_id as community_actor_id,
389 c."local" as community_local,
390 c."name" as community_name,
393 coalesce(cb.id, 0)::bool as banned_from_community,
394 u.actor_id as creator_actor_id,
395 u.local as creator_local,
396 u.name as creator_name,
397 u.published as creator_published,
398 u.avatar as creator_avatar,
400 coalesce(cl.total, 0) as score,
401 coalesce(cl.up, 0) as upvotes,
402 coalesce(cl.down, 0) as downvotes,
403 hot_rank(coalesce(cl.total, 0), ct.published) as hot_rank
405 left join post p on ct.post_id = p.id
406 left join community c on p.community_id = c.id
407 left join user_ u on ct.creator_id = u.id
408 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
412 sum(l.score) as total,
413 count(case when l.score = 1 then 1 else null end) as up,
414 count(case when l.score = -1 then 1 else null end) as down
417 ) as cl on cl.id = ct.id;
419 create or replace view comment_view as (
422 us.user_id as user_id,
423 us.my_vote as my_vote,
424 us.is_subbed::bool as subscribed,
425 us.is_saved::bool as saved
426 from comment_aggregates_view cav
430 coalesce(cl.score, 0) as my_vote,
431 coalesce(cf.id, 0) as is_subbed,
432 coalesce(cs.id, 0) as is_saved
434 left join comment_like cl on u.id = cl.user_id and cav.id = cl.comment_id
435 left join comment_saved cs on u.id = cs.user_id and cs.comment_id = cav.id
436 left join community_follower cf on u.id = cf.user_id and cav.community_id = cf.community_id
447 from comment_aggregates_view cav
450 create table comment_aggregates_fast as select * from comment_aggregates_view;
451 alter table comment_aggregates_fast add primary key (id);
453 create view comment_fast_view as
456 us.user_id as user_id,
457 us.my_vote as my_vote,
458 us.is_subbed::bool as subscribed,
459 us.is_saved::bool as saved
460 from comment_aggregates_fast cav
464 coalesce(cl.score, 0) as my_vote,
465 coalesce(cf.id, 0) as is_subbed,
466 coalesce(cs.id, 0) as is_saved
468 left join comment_like cl on u.id = cl.user_id and cav.id = cl.comment_id
469 left join comment_saved cs on u.id = cs.user_id and cs.comment_id = cav.id
470 left join community_follower cf on u.id = cf.user_id and cav.community_id = cf.community_id
481 from comment_aggregates_fast cav;
483 create view user_mention_view as
486 um.id as user_mention_id,
500 c.community_actor_id,
504 c.banned_from_community,
515 (select actor_id from user_ u where u.id = um.recipient_id) as recipient_actor_id,
516 (select local from user_ u where u.id = um.recipient_id) as recipient_local
517 from user_mention um, comment_view c
518 where um.comment_id = c.id;
520 create view user_mention_fast_view as
523 um.id as user_mention_id,
537 ac.community_actor_id,
541 ac.banned_from_community,
549 coalesce(cl.score, 0) as my_vote,
550 (select cs.id::bool from comment_saved cs where u.id = cs.user_id and cs.comment_id = ac.id) as saved,
552 (select actor_id from user_ u where u.id = um.recipient_id) as recipient_actor_id,
553 (select local from user_ u where u.id = um.recipient_id) as recipient_local
558 from comment_aggregates_fast ca
560 left join comment_like cl on u.id = cl.user_id and ac.id = cl.comment_id
561 left join user_mention um on um.comment_id = ac.id
567 um.id as user_mention_id,
581 ac.community_actor_id,
585 ac.banned_from_community,
596 (select actor_id from user_ u where u.id = um.recipient_id) as recipient_actor_id,
597 (select local from user_ u where u.id = um.recipient_id) as recipient_local
598 from comment_aggregates_fast ac
599 left join user_mention um on um.comment_id = ac.id
602 -- Do the reply_view referencing the comment_fast_view
603 create view reply_fast_view as
607 c2.creator_id as sender_id,
608 c.creator_id as recipient_id
610 inner join comment c2 on c.id = c2.parent_id
611 where c2.creator_id != c.creator_id
612 -- Do union where post is null
616 c.creator_id as sender_id,
617 p.creator_id as recipient_id
618 from comment c, post p
619 where c.post_id = p.id and c.parent_id is null and c.creator_id != p.creator_id
622 closereply.recipient_id
623 from comment_fast_view cv, closereply
624 where closereply.id = cv.id
627 -- redoing the triggers
628 create or replace function refresh_post()
629 returns trigger language plpgsql
632 IF (TG_OP = 'DELETE') THEN
633 delete from post_aggregates_fast where id = OLD.id;
635 -- Update community number of posts
636 update community_aggregates_fast set number_of_posts = number_of_posts - 1 where id = OLD.community_id;
637 ELSIF (TG_OP = 'UPDATE') THEN
638 delete from post_aggregates_fast where id = OLD.id;
639 insert into post_aggregates_fast select * from post_aggregates_view where id = NEW.id;
640 ELSIF (TG_OP = 'INSERT') THEN
641 insert into post_aggregates_fast select * from post_aggregates_view where id = NEW.id;
643 -- Update that users number of posts, post score
644 delete from user_fast where id = NEW.creator_id;
645 insert into user_fast select * from user_view where id = NEW.creator_id;
647 -- Update community number of posts
648 update community_aggregates_fast set number_of_posts = number_of_posts + 1 where id = NEW.community_id;
650 -- Update the hot rank on the post table
651 -- TODO this might not correctly update it, using a 1 week interval
652 update post_aggregates_fast as paf
653 set hot_rank = pav.hot_rank
654 from post_aggregates_view as pav
655 where paf.id = pav.id and (pav.published > ('now'::timestamp - '1 week'::interval));
661 create or replace function refresh_comment()
662 returns trigger language plpgsql
665 IF (TG_OP = 'DELETE') THEN
666 delete from comment_aggregates_fast where id = OLD.id;
668 -- Update community number of comments
669 update community_aggregates_fast as caf
670 set number_of_comments = number_of_comments - 1
672 where caf.id = p.community_id and p.id = OLD.post_id;
674 ELSIF (TG_OP = 'UPDATE') THEN
675 delete from comment_aggregates_fast where id = OLD.id;
676 insert into comment_aggregates_fast select * from comment_aggregates_view where id = NEW.id;
677 ELSIF (TG_OP = 'INSERT') THEN
678 insert into comment_aggregates_fast select * from comment_aggregates_view where id = NEW.id;
680 -- Update user view due to comment count
682 set number_of_comments = number_of_comments + 1
683 where id = NEW.creator_id;
685 -- Update post view due to comment count, new comment activity time, but only on new posts
686 -- TODO this could be done more efficiently
687 delete from post_aggregates_fast where id = NEW.post_id;
688 insert into post_aggregates_fast select * from post_aggregates_view where id = NEW.post_id;
690 -- Force the hot rank as zero on week-older posts
691 update post_aggregates_fast as paf
693 where paf.id = NEW.post_id and (paf.published < ('now'::timestamp - '1 week'::interval));
695 -- Update community number of comments
696 update community_aggregates_fast as caf
697 set number_of_comments = number_of_comments + 1
699 where caf.id = p.community_id and p.id = NEW.post_id;