migrations/2020-06-30-135809_remove_mat_views/up.sql

   1 -- Drop the mviews
   2 drop view post_mview;
   3 drop materialized view user_mview;
   4 drop view community_mview;
   5 drop materialized view private_message_mview;
   6 drop view user_mention_mview;
   7 drop view reply_view;
   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;
  13
  14 -- User
  15 drop view user_view;
  16 create view user_view as
  17 select
  18         u.id,
  19   u.actor_id,
  20         u.name,
  21         u.avatar,
  22         u.email,
  23         u.matrix_user_id,
  24   u.bio,
  25   u.local,
  26         u.admin,
  27         u.banned,
  28         u.show_avatars,
  29         u.send_notifications_to_email,
  30         u.published,
  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
  35 from user_ u
  36 left join (
  37     select
  38         p.creator_id as creator_id,
  39         count(distinct p.id) as posts,
  40         sum(pl.score) as score
  41     from post p
  42     join post_like pl on p.id = pl.post_id
  43     group by p.creator_id
  44 ) pd on u.id = pd.creator_id
  45 left join (
  46     select
  47         c.creator_id,
  48         count(distinct c.id) as comments,
  49         sum(cl.score) as score
  50     from comment c
  51     join comment_like cl on c.id = cl.comment_id
  52     group by c.creator_id
  53 ) cd on u.id = cd.creator_id;
  54
  55
  56 create table user_fast as select * from user_view;
  57 alter table user_fast add primary key (id);
  58
  59 drop trigger refresh_user on user_;
  60
  61 create trigger refresh_user
  62 after insert or update or delete
  63 on user_
  64 for each row
  65 execute procedure refresh_user();
  66
  67 -- Sample insert
  68 -- insert into user_(name, password_encrypted) values ('test_name', 'bleh');
  69 -- Sample delete
  70 -- delete from user_ where name like 'test_name';
  71 -- Sample update
  72 -- update user_ set avatar = 'hai'  where name like 'test_name';
  73 create or replace function refresh_user()
  74 returns trigger language plpgsql
  75 as $$
  76 begin
  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;
  82
  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;
  86
  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;
  89
  90   ELSIF (TG_OP = 'INSERT') THEN
  91     insert into user_fast select * from user_view where id = NEW.id;
  92   END IF;
  93
  94   return null;
  95 end $$;
  96
  97 -- Post
  98 -- Redoing the views : Credit eiknat
  99 drop view post_view;
 100 drop view post_aggregates_view;
 101
 102 create view post_aggregates_view as
 103 select
 104         p.*,
 105         -- creator details
 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,
 110   u.banned as banned,
 111   cb.id::bool as banned_from_community,
 112         -- community details
 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,
 124         hot_rank(
 125                 coalesce(pl.score , 0), (
 126                         case
 127                                 when (p.published < ('now'::timestamp - '1 month'::interval))
 128                                 then p.published
 129                                 else greatest(ct.recent_comment_time, p.published)
 130                         end
 131                 )
 132         ) as hot_rank,
 133         (
 134                 case
 135                         when (p.published < ('now'::timestamp - '1 month'::interval))
 136                         then p.published
 137                         else greatest(ct.recent_comment_time, p.published)
 138                 end
 139         ) as newest_activity_time
 140 from post p
 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
 144 left join (
 145         select
 146                 post_id,
 147                 count(*) as comments,
 148                 max(published) as recent_comment_time
 149         from comment
 150         group by post_id
 151 ) ct on ct.post_id = p.id
 152 left join (
 153         select
 154                 post_id,
 155                 sum(score) as score,
 156                 sum(score) filter (where score = 1) as upvotes,
 157                 -sum(score) filter (where score = -1) as downvotes
 158         from post_like
 159         group by post_id
 160 ) pl on pl.post_id = p.id
 161 order by p.id;
 162
 163 create view post_view as
 164 select
 165         pav.*,
 166         us.id as user_id,
 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
 172 cross join lateral (
 173         select
 174                 u.id,
 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
 179         from user_ u
 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
 185 ) as us
 186
 187 union all
 188
 189 select
 190 pav.*,
 191 null as user_id,
 192 null as my_vote,
 193 null as subscribed,
 194 null as read,
 195 null as saved
 196 from post_aggregates_view pav;
 197
 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);
 201
 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);
 204
 205 create view post_fast_view as
 206 select
 207         pav.*,
 208         us.id as user_id,
 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
 214 cross join lateral (
 215         select
 216                 u.id,
 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
 221         from user_ u
 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
 227 ) as us
 228
 229 union all
 230
 231 select
 232 pav.*,
 233 null as user_id,
 234 null as my_vote,
 235 null as subscribed,
 236 null as read,
 237 null as saved
 238 from post_aggregates_fast pav;
 239
 240 drop trigger refresh_post on post;
 241
 242 create trigger refresh_post
 243 after insert or update or delete
 244 on post
 245 for each row
 246 execute procedure refresh_post();
 247
 248 -- Sample select
 249 -- select id, name from post_fast_view where name like 'test_post' and user_id is null;
 250 -- Sample insert
 251 -- insert into post(name, creator_id, community_id) values ('test_post', 2, 2);
 252 -- Sample delete
 253 -- delete from post where name like 'test_post';
 254 -- Sample update
 255 -- update post set community_id = 4  where name like 'test_post';
 256 create or replace function refresh_post()
 257 returns trigger language plpgsql
 258 as $$
 259 begin
 260   IF (TG_OP = 'DELETE') THEN
 261     delete from post_aggregates_fast where id = OLD.id;
 262
 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;
 270
 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;
 274
 275     -- Update community number of posts
 276     update community_aggregates_fast set number_of_posts = number_of_posts + 1 where id = NEW.community_id;
 277
 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));
 284   END IF;
 285
 286   return null;
 287 end $$;
 288
 289 -- Community
 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;
 296
 297 create view community_aggregates_view as
 298 select
 299     c.id,
 300     c.name,
 301     c.title,
 302     c.description,
 303     c.category_id,
 304     c.creator_id,
 305     c.removed,
 306     c.published,
 307     c.updated,
 308     c.deleted,
 309     c.nsfw,
 310     c.actor_id,
 311     c.local,
 312     c.last_refreshed_at,
 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
 322 from community c
 323 left join user_ u on c.creator_id = u.id
 324 left join category cat on c.category_id = cat.id
 325 left join (
 326     select
 327         p.community_id,
 328         count(distinct p.id) as posts,
 329         count(distinct ct.id) as comments
 330     from post p
 331     join comment ct on p.id = ct.post_id
 332     group by p.community_id
 333 ) cd on cd.community_id = c.id
 334 left join (
 335     select
 336         community_id,
 337         count(*) as subs
 338     from community_follower
 339     group by community_id
 340 ) cf on cf.community_id = c.id;
 341
 342 create view community_view as
 343 select
 344     cv.*,
 345     us.user as user_id,
 346     us.is_subbed::bool as subscribed
 347 from community_aggregates_view cv
 348 cross join lateral (
 349         select
 350                 u.id as user,
 351                 coalesce(cf.community_id, 0) as is_subbed
 352         from user_ u
 353         left join community_follower cf on u.id = cf.user_id and cf.community_id = cv.id
 354 ) as us
 355
 356 union all
 357
 358 select
 359     cv.*,
 360     null as user_id,
 361     null as subscribed
 362 from community_aggregates_view cv;
 363
 364 create view community_moderator_view as
 365 select
 366     cm.*,
 367     u.actor_id as user_actor_id,
 368     u.local as user_local,
 369     u.name as user_name,
 370     u.avatar as avatar,
 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;
 377
 378 create view community_follower_view as
 379 select
 380     cf.*,
 381     u.actor_id as user_actor_id,
 382     u.local as user_local,
 383     u.name as user_name,
 384     u.avatar as avatar,
 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;
 391
 392 create view community_user_ban_view as
 393 select
 394     cb.*,
 395     u.actor_id as user_actor_id,
 396     u.local as user_local,
 397     u.name as user_name,
 398     u.avatar as avatar,
 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;
 405
 406 -- The community fast table
 407
 408 create table community_aggregates_fast as select * from community_aggregates_view;
 409 alter table community_aggregates_fast add primary key (id);
 410
 411 create view community_fast_view as
 412 select
 413 ac.*,
 414 u.id as user_id,
 415 (select cf.id::boolean from community_follower cf where u.id = cf.user_id and ac.id = cf.community_id) as subscribed
 416 from user_ u
 417 cross join (
 418   select
 419   ca.*
 420   from community_aggregates_fast ca
 421 ) ac
 422
 423 union all
 424
 425 select
 426 caf.*,
 427 null as user_id,
 428 null as subscribed
 429 from community_aggregates_fast caf;
 430
 431 drop trigger refresh_community on community;
 432
 433 create trigger refresh_community
 434 after insert or update or delete
 435 on community
 436 for each row
 437 execute procedure refresh_community();
 438
 439 -- Sample select
 440 -- select * from community_fast_view where name like 'test_community_name' and user_id is null;
 441 -- Sample insert
 442 -- insert into community(name, title, category_id, creator_id) values ('test_community_name', 'test_community_title', 1, 2);
 443 -- Sample delete
 444 -- delete from community where name like 'test_community_name';
 445 -- Sample update
 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
 449 as $$
 450 begin
 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;
 456
 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;
 460
 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;
 464
 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;
 468   END IF;
 469
 470   return null;
 471 end $$;
 472
 473 -- Comment
 474
 475 drop view user_mention_view;
 476 drop view comment_view;
 477 drop view comment_aggregates_view;
 478
 479 create view comment_aggregates_view as
 480 select
 481         ct.*,
 482         -- community details
 483         p.community_id,
 484         c.actor_id as community_actor_id,
 485         c."local" as community_local,
 486         c."name" as community_name,
 487         -- creator details
 488         u.banned as banned,
 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,
 494         -- score details
 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
 499 from comment ct
 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
 504 left join (
 505         select
 506                 l.comment_id as 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
 510         from comment_like l
 511         group by comment_id
 512 ) as cl on cl.id = ct.id;
 513
 514 create or replace view comment_view as (
 515 select
 516         cav.*,
 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
 522 cross join lateral (
 523         select
 524                 u.id as user_id,
 525                 coalesce(cl.score, 0) as my_vote,
 526     coalesce(cf.id, 0) as is_subbed,
 527     coalesce(cs.id, 0) as is_saved
 528         from user_ u
 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
 532 ) as us
 533
 534 union all
 535
 536 select
 537     cav.*,
 538     null as user_id,
 539     null as my_vote,
 540     null as subscribed,
 541     null as saved
 542 from comment_aggregates_view cav
 543 );
 544
 545 -- The fast view
 546 create table comment_aggregates_fast as select * from comment_aggregates_view;
 547 alter table comment_aggregates_fast add primary key (id);
 548
 549 create view comment_fast_view as
 550 select
 551         cav.*,
 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
 557 cross join lateral (
 558         select
 559                 u.id as user_id,
 560                 coalesce(cl.score, 0) as my_vote,
 561     coalesce(cf.id, 0) as is_subbed,
 562     coalesce(cs.id, 0) as is_saved
 563         from user_ u
 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
 567 ) as us
 568
 569 union all
 570
 571 select
 572     cav.*,
 573     null as user_id,
 574     null as my_vote,
 575     null as subscribed,
 576     null as saved
 577 from comment_aggregates_fast cav;
 578
 579 -- Do the reply_view referencing the comment_fast_view
 580 create view reply_fast_view as
 581 with closereply as (
 582     select
 583     c2.id,
 584     c2.creator_id as sender_id,
 585     c.creator_id as recipient_id
 586     from comment c
 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
 590     union
 591     select
 592     c.id,
 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
 597 )
 598 select cv.*,
 599 closereply.recipient_id
 600 from comment_fast_view cv, closereply
 601 where closereply.id = cv.id
 602 ;
 603
 604 -- user mention
 605 create view user_mention_view as
 606 select
 607     c.id,
 608     um.id as user_mention_id,
 609     c.creator_id,
 610     c.creator_actor_id,
 611     c.creator_local,
 612     c.post_id,
 613     c.parent_id,
 614     c.content,
 615     c.removed,
 616     um.read,
 617     c.published,
 618     c.updated,
 619     c.deleted,
 620     c.community_id,
 621     c.community_actor_id,
 622     c.community_local,
 623     c.community_name,
 624     c.banned,
 625     c.banned_from_community,
 626     c.creator_name,
 627     c.creator_avatar,
 628     c.score,
 629     c.upvotes,
 630     c.downvotes,
 631     c.hot_rank,
 632     c.user_id,
 633     c.my_vote,
 634     c.saved,
 635     um.recipient_id,
 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;
 640
 641 create view user_mention_fast_view as
 642 select
 643     ac.id,
 644     um.id as user_mention_id,
 645     ac.creator_id,
 646     ac.creator_actor_id,
 647     ac.creator_local,
 648     ac.post_id,
 649     ac.parent_id,
 650     ac.content,
 651     ac.removed,
 652     um.read,
 653     ac.published,
 654     ac.updated,
 655     ac.deleted,
 656     ac.community_id,
 657     ac.community_actor_id,
 658     ac.community_local,
 659     ac.community_name,
 660     ac.banned,
 661     ac.banned_from_community,
 662     ac.creator_name,
 663     ac.creator_avatar,
 664     ac.score,
 665     ac.upvotes,
 666     ac.downvotes,
 667     ac.hot_rank,
 668     u.id as user_id,
 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,
 671     um.recipient_id,
 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
 674 from user_ u
 675 cross join (
 676   select
 677   ca.*
 678   from comment_aggregates_fast ca
 679 ) ac
 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
 682
 683 union all
 684
 685 select
 686     ac.id,
 687     um.id as user_mention_id,
 688     ac.creator_id,
 689     ac.creator_actor_id,
 690     ac.creator_local,
 691     ac.post_id,
 692     ac.parent_id,
 693     ac.content,
 694     ac.removed,
 695     um.read,
 696     ac.published,
 697     ac.updated,
 698     ac.deleted,
 699     ac.community_id,
 700     ac.community_actor_id,
 701     ac.community_local,
 702     ac.community_name,
 703     ac.banned,
 704     ac.banned_from_community,
 705     ac.creator_name,
 706     ac.creator_avatar,
 707     ac.score,
 708     ac.upvotes,
 709     ac.downvotes,
 710     ac.hot_rank,
 711     null as user_id,
 712     null as my_vote,
 713     null as saved,
 714     um.recipient_id,
 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
 719 ;
 720
 721
 722 drop trigger refresh_comment on comment;
 723
 724 create trigger refresh_comment
 725 after insert or update or delete
 726 on comment
 727 for each row
 728 execute procedure refresh_comment();
 729
 730 -- Sample select
 731 -- select * from comment_fast_view where content = 'test_comment' and user_id is null;
 732 -- Sample insert
 733 -- insert into comment(creator_id, post_id, content) values (2, 2, 'test_comment');
 734 -- Sample delete
 735 -- delete from comment where content like 'test_comment';
 736 -- Sample update
 737 -- update comment set removed = true where content like 'test_comment';
 738 create or replace function refresh_comment()
 739 returns trigger language plpgsql
 740 as $$
 741 begin
 742   IF (TG_OP = 'DELETE') THEN
 743     delete from comment_aggregates_fast where id = OLD.id;
 744
 745     -- Update community number of comments
 746     update community_aggregates_fast as caf
 747     set number_of_comments = number_of_comments - 1
 748     from post as p
 749     where caf.id = p.community_id and p.id = OLD.post_id;
 750
 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;
 756
 757     -- Update user view due to comment count
 758     update user_fast
 759     set number_of_comments = number_of_comments + 1
 760     where id = NEW.creator_id;
 761
 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;
 766
 767     -- Force the hot rank as zero on week-older posts
 768     update post_aggregates_fast as paf
 769     set hot_rank = 0
 770     where paf.id = NEW.post_id and (paf.published < ('now'::timestamp - '1 week'::interval));
 771
 772     -- Update community number of comments
 773     update community_aggregates_fast as caf
 774     set number_of_comments = number_of_comments + 1
 775     from post as p
 776     where caf.id = p.community_id and p.id = NEW.post_id;
 777
 778   END IF;
 779
 780   return null;
 781 end $$;
 782
 783
 784 -- post_like
 785 -- select id, score, my_vote from post_fast_view where id = 29 and user_id = 4;
 786 -- Sample insert
 787 -- insert into post_like(user_id, post_id, score) values (4, 29, 1);
 788 -- Sample delete
 789 -- delete from post_like where user_id = 4 and post_id = 29;
 790 -- Sample update
 791 -- update post_like set score = -1 where user_id = 4 and post_id = 29;
 792
 793 -- TODO test this a LOT
 794 create or replace function refresh_post_like()
 795 returns trigger language plpgsql
 796 as $$
 797 begin
 798   IF (TG_OP = 'DELETE') THEN
 799     update post_aggregates_fast
 800     set score = case
 801       when (OLD.score = 1) then score - 1
 802       else score + 1 end,
 803     upvotes = case
 804       when (OLD.score = 1) then upvotes - 1
 805       else upvotes end,
 806     downvotes = case
 807       when (OLD.score = -1) then downvotes - 1
 808       else downvotes end
 809     where id = OLD.post_id;
 810
 811   ELSIF (TG_OP = 'INSERT') THEN
 812     update post_aggregates_fast
 813     set score = case
 814       when (NEW.score = 1) then score + 1
 815       else score - 1 end,
 816     upvotes = case
 817       when (NEW.score = 1) then upvotes + 1
 818       else upvotes end,
 819     downvotes = case
 820       when (NEW.score = -1) then downvotes + 1
 821       else downvotes end
 822     where id = NEW.post_id;
 823   END IF;
 824
 825   return null;
 826 end $$;
 827
 828 drop trigger refresh_post_like on post_like;
 829 create trigger refresh_post_like
 830 after insert or delete
 831 on post_like
 832 for each row
 833 execute procedure refresh_post_like();
 834
 835 -- comment_like
 836 -- select id, score, my_vote from comment_fast_view where id = 29 and user_id = 4;
 837 -- Sample insert
 838 -- insert into comment_like(user_id, comment_id, post_id, score) values (4, 29, 51, 1);
 839 -- Sample delete
 840 -- delete from comment_like where user_id = 4 and comment_id = 29;
 841 -- Sample update
 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
 845 as $$
 846 begin
 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
 850     set score = case
 851       when (OLD.score = 1) then score - 1
 852       else score + 1 end,
 853     upvotes = case
 854       when (OLD.score = 1) then upvotes - 1
 855       else upvotes end,
 856     downvotes = case
 857       when (OLD.score = -1) then downvotes - 1
 858       else downvotes end
 859     where id = OLD.comment_id;
 860
 861   ELSIF (TG_OP = 'INSERT') THEN
 862     update comment_aggregates_fast
 863     set score = case
 864       when (NEW.score = 1) then score + 1
 865       else score - 1 end,
 866     upvotes = case
 867       when (NEW.score = 1) then upvotes + 1
 868       else upvotes end,
 869     downvotes = case
 870       when (NEW.score = -1) then downvotes + 1
 871       else downvotes end
 872     where id = NEW.comment_id;
 873   END IF;
 874
 875   return null;
 876 end $$;
 877
 878 drop trigger refresh_comment_like on comment_like;
 879 create trigger refresh_comment_like
 880 after insert or delete
 881 on comment_like
 882 for each row
 883 execute procedure refresh_comment_like();
 884
 885 -- Community user ban
 886
 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
 891 for each row
 892 execute procedure refresh_community_user_ban();
 893
 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';
 896 -- Sample insert
 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);
 899 -- Sample delete
 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
 905 as $$
 906 begin
 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;
 914   END IF;
 915
 916   return null;
 917 end $$;
 918
 919 -- Community follower
 920
 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
 925 for each row
 926 execute procedure refresh_community_follower();
 927
 928 create or replace function refresh_community_follower()
 929 returns trigger language plpgsql
 930 as $$
 931 begin
 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;
 936   END IF;
 937
 938   return null;
 939 end $$;