3 drop materialized view user_mview;
4 drop view community_mview;
5 drop materialized view private_message_mview;
6 drop view user_mention_mview;
8 drop view comment_mview;
9 drop materialized view post_aggregates_mview;
10 drop materialized view community_aggregates_mview;
11 drop materialized view comment_aggregates_mview;
12 drop trigger refresh_private_message on private_message;
16 create view user_view as
29 u.send_notifications_to_email,
31 coalesce(pd.posts, 0) as number_of_posts,
32 coalesce(pd.score, 0) as post_score,
33 coalesce(cd.comments, 0) as number_of_comments,
34 coalesce(cd.score, 0) as comment_score
38 p.creator_id as creator_id,
39 count(distinct p.id) as posts,
40 sum(pl.score) as score
42 join post_like pl on p.id = pl.post_id
44 ) pd on u.id = pd.creator_id
48 count(distinct c.id) as comments,
49 sum(cl.score) as score
51 join comment_like cl on c.id = cl.comment_id
53 ) cd on u.id = cd.creator_id;
56 create table user_fast as select * from user_view;
57 alter table user_fast add primary key (id);
59 drop trigger refresh_user on user_;
61 create trigger refresh_user
62 after insert or update or delete
65 execute procedure refresh_user();
68 -- insert into user_(name, password_encrypted) values ('test_name', 'bleh');
70 -- delete from user_ where name like 'test_name';
72 -- update user_ set avatar = 'hai' where name like 'test_name';
73 create or replace function refresh_user()
74 returns trigger language plpgsql
77 IF (TG_OP = 'DELETE') THEN
78 delete from user_fast where id = OLD.id;
79 ELSIF (TG_OP = 'UPDATE') THEN
80 delete from user_fast where id = OLD.id;
81 insert into user_fast select * from user_view where id = NEW.id;
83 -- Refresh post_fast, cause of user info changes
84 delete from post_aggregates_fast where creator_id = NEW.id;
85 insert into post_aggregates_fast select * from post_aggregates_view where creator_id = NEW.id;
87 delete from comment_aggregates_fast where creator_id = NEW.id;
88 insert into comment_aggregates_fast select * from comment_aggregates_view where creator_id = NEW.id;
90 ELSIF (TG_OP = 'INSERT') THEN
91 insert into user_fast select * from user_view where id = NEW.id;
98 -- Redoing the views : Credit eiknat
100 drop view post_aggregates_view;
102 create view post_aggregates_view as
106 u.actor_id as creator_actor_id,
107 u."local" as creator_local,
108 u."name" as creator_name,
109 u.avatar as creator_avatar,
111 cb.id::bool as banned_from_community,
113 c.actor_id as community_actor_id,
114 c."local" as community_local,
115 c."name" as community_name,
116 c.removed as community_removed,
117 c.deleted as community_deleted,
118 c.nsfw as community_nsfw,
119 -- post score data/comment count
120 coalesce(ct.comments, 0) as number_of_comments,
121 coalesce(pl.score, 0) as score,
122 coalesce(pl.upvotes, 0) as upvotes,
123 coalesce(pl.downvotes, 0) as downvotes,
125 coalesce(pl.score , 0), (
127 when (p.published < ('now'::timestamp - '1 month'::interval))
129 else greatest(ct.recent_comment_time, p.published)
135 when (p.published < ('now'::timestamp - '1 month'::interval))
137 else greatest(ct.recent_comment_time, p.published)
139 ) as newest_activity_time
141 left join user_ u on p.creator_id = u.id
142 left join community_user_ban cb on p.creator_id = cb.user_id and p.community_id = cb.community_id
143 left join community c on p.community_id = c.id
147 count(*) as comments,
148 max(published) as recent_comment_time
151 ) ct on ct.post_id = p.id
156 sum(score) filter (where score = 1) as upvotes,
157 -sum(score) filter (where score = -1) as downvotes
160 ) pl on pl.post_id = p.id
163 create view post_view as
167 us.user_vote as my_vote,
168 us.is_subbed::bool as subscribed,
169 us.is_read::bool as read,
170 us.is_saved::bool as saved
171 from post_aggregates_view pav
175 coalesce(cf.community_id, 0) as is_subbed,
176 coalesce(pr.post_id, 0) as is_read,
177 coalesce(ps.post_id, 0) as is_saved,
178 coalesce(pl.score, 0) as user_vote
180 left join community_user_ban cb on u.id = cb.user_id and cb.community_id = pav.community_id
181 left join community_follower cf on u.id = cf.user_id and cf.community_id = pav.community_id
182 left join post_read pr on u.id = pr.user_id and pr.post_id = pav.id
183 left join post_saved ps on u.id = ps.user_id and ps.post_id = pav.id
184 left join post_like pl on u.id = pl.user_id and pav.id = pl.post_id
196 from post_aggregates_view pav;
198 -- The post fast table
199 create table post_aggregates_fast as select * from post_aggregates_view;
200 alter table post_aggregates_fast add primary key (id);
202 -- For the hot rank resorting
203 create index idx_post_aggregates_fast_hot_rank_published on post_aggregates_fast (hot_rank desc, published desc);
205 create view post_fast_view as
209 us.user_vote as my_vote,
210 us.is_subbed::bool as subscribed,
211 us.is_read::bool as read,
212 us.is_saved::bool as saved
213 from post_aggregates_fast pav
217 coalesce(cf.community_id, 0) as is_subbed,
218 coalesce(pr.post_id, 0) as is_read,
219 coalesce(ps.post_id, 0) as is_saved,
220 coalesce(pl.score, 0) as user_vote
222 left join community_user_ban cb on u.id = cb.user_id and cb.community_id = pav.community_id
223 left join community_follower cf on u.id = cf.user_id and cf.community_id = pav.community_id
224 left join post_read pr on u.id = pr.user_id and pr.post_id = pav.id
225 left join post_saved ps on u.id = ps.user_id and ps.post_id = pav.id
226 left join post_like pl on u.id = pl.user_id and pav.id = pl.post_id
238 from post_aggregates_fast pav;
240 drop trigger refresh_post on post;
242 create trigger refresh_post
243 after insert or update or delete
246 execute procedure refresh_post();
249 -- select id, name from post_fast_view where name like 'test_post' and user_id is null;
251 -- insert into post(name, creator_id, community_id) values ('test_post', 2, 2);
253 -- delete from post where name like 'test_post';
255 -- update post set community_id = 4 where name like 'test_post';
256 create or replace function refresh_post()
257 returns trigger language plpgsql
260 IF (TG_OP = 'DELETE') THEN
261 delete from post_aggregates_fast where id = OLD.id;
263 -- Update community number of posts
264 update community_aggregates_fast set number_of_posts = number_of_posts - 1 where id = OLD.community_id;
265 ELSIF (TG_OP = 'UPDATE') THEN
266 delete from post_aggregates_fast where id = OLD.id;
267 insert into post_aggregates_fast select * from post_aggregates_view where id = NEW.id;
268 ELSIF (TG_OP = 'INSERT') THEN
269 insert into post_aggregates_fast select * from post_aggregates_view where id = NEW.id;
271 -- Update that users number of posts, post score
272 delete from user_fast where id = NEW.creator_id;
273 insert into user_fast select * from user_view where id = NEW.creator_id;
275 -- Update community number of posts
276 update community_aggregates_fast set number_of_posts = number_of_posts + 1 where id = NEW.community_id;
278 -- Update the hot rank on the post table
279 -- TODO this might not correctly update it, using a 1 week interval
280 update post_aggregates_fast as paf
281 set hot_rank = pav.hot_rank
282 from post_aggregates_view as pav
283 where paf.id = pav.id and (pav.published > ('now'::timestamp - '1 week'::interval));
290 -- Redoing the views : Credit eiknat
291 drop view community_moderator_view;
292 drop view community_follower_view;
293 drop view community_user_ban_view;
294 drop view community_view;
295 drop view community_aggregates_view;
297 create view community_aggregates_view as
313 u.actor_id as creator_actor_id,
314 u.local as creator_local,
315 u.name as creator_name,
316 u.avatar as creator_avatar,
317 cat.name as category_name,
318 coalesce(cf.subs, 0) as number_of_subscribers,
319 coalesce(cd.posts, 0) as number_of_posts,
320 coalesce(cd.comments, 0) as number_of_comments,
321 hot_rank(cf.subs, c.published) as hot_rank
323 left join user_ u on c.creator_id = u.id
324 left join category cat on c.category_id = cat.id
328 count(distinct p.id) as posts,
329 count(distinct ct.id) as comments
331 join comment ct on p.id = ct.post_id
332 group by p.community_id
333 ) cd on cd.community_id = c.id
338 from community_follower
339 group by community_id
340 ) cf on cf.community_id = c.id;
342 create view community_view as
346 us.is_subbed::bool as subscribed
347 from community_aggregates_view cv
351 coalesce(cf.community_id, 0) as is_subbed
353 left join community_follower cf on u.id = cf.user_id and cf.community_id = cv.id
362 from community_aggregates_view cv;
364 create view community_moderator_view as
367 u.actor_id as user_actor_id,
368 u.local as user_local,
371 c.actor_id as community_actor_id,
372 c.local as community_local,
373 c.name as community_name
374 from community_moderator cm
375 left join user_ u on cm.user_id = u.id
376 left join community c on cm.community_id = c.id;
378 create view community_follower_view as
381 u.actor_id as user_actor_id,
382 u.local as user_local,
385 c.actor_id as community_actor_id,
386 c.local as community_local,
387 c.name as community_name
388 from community_follower cf
389 left join user_ u on cf.user_id = u.id
390 left join community c on cf.community_id = c.id;
392 create view community_user_ban_view as
395 u.actor_id as user_actor_id,
396 u.local as user_local,
399 c.actor_id as community_actor_id,
400 c.local as community_local,
401 c.name as community_name
402 from community_user_ban cb
403 left join user_ u on cb.user_id = u.id
404 left join community c on cb.community_id = c.id;
406 -- The community fast table
408 create table community_aggregates_fast as select * from community_aggregates_view;
409 alter table community_aggregates_fast add primary key (id);
411 create view community_fast_view as
415 (select cf.id::boolean from community_follower cf where u.id = cf.user_id and ac.id = cf.community_id) as subscribed
420 from community_aggregates_fast ca
429 from community_aggregates_fast caf;
431 drop trigger refresh_community on community;
433 create trigger refresh_community
434 after insert or update or delete
437 execute procedure refresh_community();
440 -- select * from community_fast_view where name like 'test_community_name' and user_id is null;
442 -- insert into community(name, title, category_id, creator_id) values ('test_community_name', 'test_community_title', 1, 2);
444 -- delete from community where name like 'test_community_name';
446 -- update community set title = 'test_community_title_2' where name like 'test_community_name';
447 create or replace function refresh_community()
448 returns trigger language plpgsql
451 IF (TG_OP = 'DELETE') THEN
452 delete from community_aggregates_fast where id = OLD.id;
453 ELSIF (TG_OP = 'UPDATE') THEN
454 delete from community_aggregates_fast where id = OLD.id;
455 insert into community_aggregates_fast select * from community_aggregates_view where id = NEW.id;
457 -- Update user view due to owner changes
458 delete from user_fast where id = NEW.creator_id;
459 insert into user_fast select * from user_view where id = NEW.creator_id;
461 -- Update post view due to community changes
462 delete from post_aggregates_fast where community_id = NEW.id;
463 insert into post_aggregates_fast select * from post_aggregates_view where community_id = NEW.id;
465 -- TODO make sure this shows up in the users page ?
466 ELSIF (TG_OP = 'INSERT') THEN
467 insert into community_aggregates_fast select * from community_aggregates_view where id = NEW.id;
475 drop view user_mention_view;
476 drop view comment_view;
477 drop view comment_aggregates_view;
479 create view comment_aggregates_view as
484 c.actor_id as community_actor_id,
485 c."local" as community_local,
486 c."name" as community_name,
489 coalesce(cb.id, 0)::bool as banned_from_community,
490 u.actor_id as creator_actor_id,
491 u.local as creator_local,
492 u.name as creator_name,
493 u.avatar as creator_avatar,
495 coalesce(cl.total, 0) as score,
496 coalesce(cl.up, 0) as upvotes,
497 coalesce(cl.down, 0) as downvotes,
498 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 and p.id = ct.post_id and p.community_id = cb.community_id
507 sum(l.score) as total,
508 count(case when l.score = 1 then 1 else null end) as up,
509 count(case when l.score = -1 then 1 else null end) as down
512 ) as cl on cl.id = ct.id;
514 create or replace view comment_view as (
517 us.user_id as user_id,
518 us.my_vote as my_vote,
519 us.is_subbed::bool as subscribed,
520 us.is_saved::bool as saved
521 from comment_aggregates_view cav
525 coalesce(cl.score, 0) as my_vote,
526 coalesce(cf.id, 0) as is_subbed,
527 coalesce(cs.id, 0) as is_saved
529 left join comment_like cl on u.id = cl.user_id and cav.id = cl.comment_id
530 left join comment_saved cs on u.id = cs.user_id and cs.comment_id = cav.id
531 left join community_follower cf on u.id = cf.user_id and cav.community_id = cf.community_id
542 from comment_aggregates_view cav
546 create table comment_aggregates_fast as select * from comment_aggregates_view;
547 alter table comment_aggregates_fast add primary key (id);
549 create view comment_fast_view as
552 us.user_id as user_id,
553 us.my_vote as my_vote,
554 us.is_subbed::bool as subscribed,
555 us.is_saved::bool as saved
556 from comment_aggregates_fast cav
560 coalesce(cl.score, 0) as my_vote,
561 coalesce(cf.id, 0) as is_subbed,
562 coalesce(cs.id, 0) as is_saved
564 left join comment_like cl on u.id = cl.user_id and cav.id = cl.comment_id
565 left join comment_saved cs on u.id = cs.user_id and cs.comment_id = cav.id
566 left join community_follower cf on u.id = cf.user_id and cav.community_id = cf.community_id
577 from comment_aggregates_fast cav;
579 -- Do the reply_view referencing the comment_fast_view
580 create view reply_fast_view as
584 c2.creator_id as sender_id,
585 c.creator_id as recipient_id
587 inner join comment c2 on c.id = c2.parent_id
588 where c2.creator_id != c.creator_id
589 -- Do union where post is null
593 c.creator_id as sender_id,
594 p.creator_id as recipient_id
595 from comment c, post p
596 where c.post_id = p.id and c.parent_id is null and c.creator_id != p.creator_id
599 closereply.recipient_id
600 from comment_fast_view cv, closereply
601 where closereply.id = cv.id
605 create view user_mention_view as
608 um.id as user_mention_id,
621 c.community_actor_id,
625 c.banned_from_community,
636 (select actor_id from user_ u where u.id = um.recipient_id) as recipient_actor_id,
637 (select local from user_ u where u.id = um.recipient_id) as recipient_local
638 from user_mention um, comment_view c
639 where um.comment_id = c.id;
641 create view user_mention_fast_view as
644 um.id as user_mention_id,
657 ac.community_actor_id,
661 ac.banned_from_community,
669 coalesce(cl.score, 0) as my_vote,
670 (select cs.id::bool from comment_saved cs where u.id = cs.user_id and cs.comment_id = ac.id) as saved,
672 (select actor_id from user_ u where u.id = um.recipient_id) as recipient_actor_id,
673 (select local from user_ u where u.id = um.recipient_id) as recipient_local
678 from comment_aggregates_fast ca
680 left join comment_like cl on u.id = cl.user_id and ac.id = cl.comment_id
681 left join user_mention um on um.comment_id = ac.id
687 um.id as user_mention_id,
700 ac.community_actor_id,
704 ac.banned_from_community,
715 (select actor_id from user_ u where u.id = um.recipient_id) as recipient_actor_id,
716 (select local from user_ u where u.id = um.recipient_id) as recipient_local
717 from comment_aggregates_fast ac
718 left join user_mention um on um.comment_id = ac.id
722 drop trigger refresh_comment on comment;
724 create trigger refresh_comment
725 after insert or update or delete
728 execute procedure refresh_comment();
731 -- select * from comment_fast_view where content = 'test_comment' and user_id is null;
733 -- insert into comment(creator_id, post_id, content) values (2, 2, 'test_comment');
735 -- delete from comment where content like 'test_comment';
737 -- update comment set removed = true where content like 'test_comment';
738 create or replace function refresh_comment()
739 returns trigger language plpgsql
742 IF (TG_OP = 'DELETE') THEN
743 delete from comment_aggregates_fast where id = OLD.id;
745 -- Update community number of comments
746 update community_aggregates_fast as caf
747 set number_of_comments = number_of_comments - 1
749 where caf.id = p.community_id and p.id = OLD.post_id;
751 ELSIF (TG_OP = 'UPDATE') THEN
752 delete from comment_aggregates_fast where id = OLD.id;
753 insert into comment_aggregates_fast select * from comment_aggregates_view where id = NEW.id;
754 ELSIF (TG_OP = 'INSERT') THEN
755 insert into comment_aggregates_fast select * from comment_aggregates_view where id = NEW.id;
757 -- Update user view due to comment count
759 set number_of_comments = number_of_comments + 1
760 where id = NEW.creator_id;
762 -- Update post view due to comment count, new comment activity time, but only on new posts
763 -- TODO this could be done more efficiently
764 delete from post_aggregates_fast where id = NEW.post_id;
765 insert into post_aggregates_fast select * from post_aggregates_view where id = NEW.post_id;
767 -- Force the hot rank as zero on week-older posts
768 update post_aggregates_fast as paf
770 where paf.id = NEW.post_id and (paf.published < ('now'::timestamp - '1 week'::interval));
772 -- Update community number of comments
773 update community_aggregates_fast as caf
774 set number_of_comments = number_of_comments + 1
776 where caf.id = p.community_id and p.id = NEW.post_id;
785 -- select id, score, my_vote from post_fast_view where id = 29 and user_id = 4;
787 -- insert into post_like(user_id, post_id, score) values (4, 29, 1);
789 -- delete from post_like where user_id = 4 and post_id = 29;
791 -- update post_like set score = -1 where user_id = 4 and post_id = 29;
793 -- TODO test this a LOT
794 create or replace function refresh_post_like()
795 returns trigger language plpgsql
798 IF (TG_OP = 'DELETE') THEN
799 update post_aggregates_fast
801 when (OLD.score = 1) then score - 1
804 when (OLD.score = 1) then upvotes - 1
807 when (OLD.score = -1) then downvotes - 1
809 where id = OLD.post_id;
811 ELSIF (TG_OP = 'INSERT') THEN
812 update post_aggregates_fast
814 when (NEW.score = 1) then score + 1
817 when (NEW.score = 1) then upvotes + 1
820 when (NEW.score = -1) then downvotes + 1
822 where id = NEW.post_id;
828 drop trigger refresh_post_like on post_like;
829 create trigger refresh_post_like
830 after insert or delete
833 execute procedure refresh_post_like();
836 -- select id, score, my_vote from comment_fast_view where id = 29 and user_id = 4;
838 -- insert into comment_like(user_id, comment_id, post_id, score) values (4, 29, 51, 1);
840 -- delete from comment_like where user_id = 4 and comment_id = 29;
842 -- update comment_like set score = -1 where user_id = 4 and comment_id = 29;
843 create or replace function refresh_comment_like()
844 returns trigger language plpgsql
847 -- TODO possibly select from comment_fast to get previous scores, instead of re-fetching the views?
848 IF (TG_OP = 'DELETE') THEN
849 update comment_aggregates_fast
851 when (OLD.score = 1) then score - 1
854 when (OLD.score = 1) then upvotes - 1
857 when (OLD.score = -1) then downvotes - 1
859 where id = OLD.comment_id;
861 ELSIF (TG_OP = 'INSERT') THEN
862 update comment_aggregates_fast
864 when (NEW.score = 1) then score + 1
867 when (NEW.score = 1) then upvotes + 1
870 when (NEW.score = -1) then downvotes + 1
872 where id = NEW.comment_id;
878 drop trigger refresh_comment_like on comment_like;
879 create trigger refresh_comment_like
880 after insert or delete
883 execute procedure refresh_comment_like();
885 -- Community user ban
887 drop trigger refresh_community_user_ban on community_user_ban;
888 create trigger refresh_community_user_ban
889 after insert or delete -- Note this is missing after update
890 on community_user_ban
892 execute procedure refresh_community_user_ban();
894 -- select creator_name, banned_from_community from comment_fast_view where user_id = 4 and content = 'test_before_ban';
895 -- select creator_name, banned_from_community, community_id from comment_aggregates_fast where content = 'test_before_ban';
897 -- insert into comment(creator_id, post_id, content) values (1198, 341, 'test_before_ban');
898 -- insert into community_user_ban(community_id, user_id) values (2, 1198);
900 -- delete from community_user_ban where user_id = 1198 and community_id = 2;
901 -- delete from comment where content = 'test_before_ban';
902 -- update comment_aggregates_fast set banned_from_community = false where creator_id = 1198 and community_id = 2;
903 create or replace function refresh_community_user_ban()
904 returns trigger language plpgsql
907 -- TODO possibly select from comment_fast to get previous scores, instead of re-fetching the views?
908 IF (TG_OP = 'DELETE') THEN
909 update comment_aggregates_fast set banned_from_community = false where creator_id = OLD.user_id and community_id = OLD.community_id;
910 update post_aggregates_fast set banned_from_community = false where creator_id = OLD.user_id and community_id = OLD.community_id;
911 ELSIF (TG_OP = 'INSERT') THEN
912 update comment_aggregates_fast set banned_from_community = true where creator_id = NEW.user_id and community_id = NEW.community_id;
913 update post_aggregates_fast set banned_from_community = true where creator_id = NEW.user_id and community_id = NEW.community_id;
919 -- Community follower
921 drop trigger refresh_community_follower on community_follower;
922 create trigger refresh_community_follower
923 after insert or delete -- Note this is missing after update
924 on community_follower
926 execute procedure refresh_community_follower();
928 create or replace function refresh_community_follower()
929 returns trigger language plpgsql
932 IF (TG_OP = 'DELETE') THEN
933 update community_aggregates_fast set number_of_subscribers = number_of_subscribers - 1 where id = OLD.community_id;
934 ELSIF (TG_OP = 'INSERT') THEN
935 update community_aggregates_fast set number_of_subscribers = number_of_subscribers + 1 where id = NEW.community_id;