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

   1 -- Drop the mviews
   2 DROP VIEW post_mview;
   3
   4 DROP MATERIALIZED VIEW user_mview;
   5
   6 DROP VIEW community_mview;
   7
   8 DROP MATERIALIZED VIEW private_message_mview;
   9
  10 DROP VIEW user_mention_mview;
  11
  12 DROP VIEW reply_view;
  13
  14 DROP VIEW comment_mview;
  15
  16 DROP MATERIALIZED VIEW post_aggregates_mview;
  17
  18 DROP MATERIALIZED VIEW community_aggregates_mview;
  19
  20 DROP MATERIALIZED VIEW comment_aggregates_mview;
  21
  22 DROP TRIGGER refresh_private_message ON private_message;
  23
  24 -- User
  25 DROP VIEW user_view;
  26
  27 CREATE VIEW user_view AS
  28 SELECT
  29     u.id,
  30     u.actor_id,
  31     u.name,
  32     u.avatar,
  33     u.email,
  34     u.matrix_user_id,
  35     u.bio,
  36     u.local,
  37     u.admin,
  38     u.banned,
  39     u.show_avatars,
  40     u.send_notifications_to_email,
  41     u.published,
  42     coalesce(pd.posts, 0) AS number_of_posts,
  43     coalesce(pd.score, 0) AS post_score,
  44     coalesce(cd.comments, 0) AS number_of_comments,
  45     coalesce(cd.score, 0) AS comment_score
  46 FROM
  47     user_ u
  48     LEFT JOIN (
  49         SELECT
  50             p.creator_id AS creator_id,
  51             count(DISTINCT p.id) AS posts,
  52             sum(pl.score) AS score
  53         FROM
  54             post p
  55             JOIN post_like pl ON p.id = pl.post_id
  56         GROUP BY
  57             p.creator_id) pd ON u.id = pd.creator_id
  58     LEFT JOIN (
  59         SELECT
  60             c.creator_id,
  61             count(DISTINCT c.id) AS comments,
  62             sum(cl.score) AS score
  63         FROM
  64             comment c
  65             JOIN comment_like cl ON c.id = cl.comment_id
  66         GROUP BY
  67             c.creator_id) cd ON u.id = cd.creator_id;
  68
  69 CREATE TABLE user_fast AS
  70 SELECT
  71     *
  72 FROM
  73     user_view;
  74
  75 ALTER TABLE user_fast
  76     ADD PRIMARY KEY (id);
  77
  78 DROP TRIGGER refresh_user ON user_;
  79
  80 CREATE TRIGGER refresh_user
  81     AFTER INSERT OR UPDATE OR DELETE ON user_
  82     FOR EACH ROW
  83     EXECUTE PROCEDURE refresh_user ();
  84
  85 -- Sample insert
  86 -- insert into user_(name, password_encrypted) values ('test_name', 'bleh');
  87 -- Sample delete
  88 -- delete from user_ where name like 'test_name';
  89 -- Sample update
  90 -- update user_ set avatar = 'hai'  where name like 'test_name';
  91 CREATE OR REPLACE FUNCTION refresh_user ()
  92     RETURNS TRIGGER
  93     LANGUAGE plpgsql
  94     AS $$
  95 BEGIN
  96     IF (TG_OP = 'DELETE') THEN
  97         DELETE FROM user_fast
  98         WHERE id = OLD.id;
  99     ELSIF (TG_OP = 'UPDATE') THEN
 100         DELETE FROM user_fast
 101         WHERE id = OLD.id;
 102         INSERT INTO user_fast
 103         SELECT
 104             *
 105         FROM
 106             user_view
 107         WHERE
 108             id = NEW.id;
 109         -- Refresh post_fast, cause of user info changes
 110         DELETE FROM post_aggregates_fast
 111         WHERE creator_id = NEW.id;
 112         INSERT INTO post_aggregates_fast
 113         SELECT
 114             *
 115         FROM
 116             post_aggregates_view
 117         WHERE
 118             creator_id = NEW.id;
 119         DELETE FROM comment_aggregates_fast
 120         WHERE creator_id = NEW.id;
 121         INSERT INTO comment_aggregates_fast
 122         SELECT
 123             *
 124         FROM
 125             comment_aggregates_view
 126         WHERE
 127             creator_id = NEW.id;
 128     ELSIF (TG_OP = 'INSERT') THEN
 129         INSERT INTO user_fast
 130         SELECT
 131             *
 132         FROM
 133             user_view
 134         WHERE
 135             id = NEW.id;
 136     END IF;
 137     RETURN NULL;
 138 END
 139 $$;
 140
 141 -- Post
 142 -- Redoing the views : Credit eiknat
 143 DROP VIEW post_view;
 144
 145 DROP VIEW post_aggregates_view;
 146
 147 CREATE VIEW post_aggregates_view AS
 148 SELECT
 149     p.*,
 150     -- creator details
 151     u.actor_id AS creator_actor_id,
 152     u."local" AS creator_local,
 153     u."name" AS creator_name,
 154     u.avatar AS creator_avatar,
 155     u.banned AS banned,
 156     cb.id::bool AS banned_from_community,
 157     -- community details
 158     c.actor_id AS community_actor_id,
 159     c."local" AS community_local,
 160     c."name" AS community_name,
 161     c.removed AS community_removed,
 162     c.deleted AS community_deleted,
 163     c.nsfw AS community_nsfw,
 164     -- post score data/comment count
 165     coalesce(ct.comments, 0) AS number_of_comments,
 166     coalesce(pl.score, 0) AS score,
 167     coalesce(pl.upvotes, 0) AS upvotes,
 168     coalesce(pl.downvotes, 0) AS downvotes,
 169     hot_rank (coalesce(pl.score, 0), (
 170             CASE WHEN (p.published < ('now'::timestamp - '1 month'::interval)) THEN
 171                 p.published
 172             ELSE
 173                 greatest (ct.recent_comment_time, p.published)
 174             END)) AS hot_rank,
 175     (
 176         CASE WHEN (p.published < ('now'::timestamp - '1 month'::interval)) THEN
 177             p.published
 178         ELSE
 179             greatest (ct.recent_comment_time, p.published)
 180         END) AS newest_activity_time
 181 FROM
 182     post p
 183     LEFT JOIN user_ u ON p.creator_id = u.id
 184     LEFT JOIN community_user_ban cb ON p.creator_id = cb.user_id
 185         AND p.community_id = cb.community_id
 186     LEFT JOIN community c ON p.community_id = c.id
 187     LEFT JOIN (
 188         SELECT
 189             post_id,
 190             count(*) AS comments,
 191             max(published) AS recent_comment_time
 192         FROM
 193             comment
 194         GROUP BY
 195             post_id) ct ON ct.post_id = p.id
 196     LEFT JOIN (
 197         SELECT
 198             post_id,
 199             sum(score) AS score,
 200             sum(score) FILTER (WHERE score = 1) AS upvotes,
 201             - sum(score) FILTER (WHERE score = - 1) AS downvotes
 202         FROM
 203             post_like
 204         GROUP BY
 205             post_id) pl ON pl.post_id = p.id
 206 ORDER BY
 207     p.id;
 208
 209 CREATE VIEW post_view AS
 210 SELECT
 211     pav.*,
 212     us.id AS user_id,
 213     us.user_vote AS my_vote,
 214     us.is_subbed::bool AS subscribed,
 215     us.is_read::bool AS read,
 216     us.is_saved::bool AS saved
 217 FROM
 218     post_aggregates_view pav
 219     CROSS JOIN LATERAL (
 220         SELECT
 221             u.id,
 222             coalesce(cf.community_id, 0) AS is_subbed,
 223             coalesce(pr.post_id, 0) AS is_read,
 224             coalesce(ps.post_id, 0) AS is_saved,
 225             coalesce(pl.score, 0) AS user_vote
 226         FROM
 227             user_ u
 228             LEFT JOIN community_user_ban cb ON u.id = cb.user_id
 229                 AND cb.community_id = pav.community_id
 230         LEFT JOIN community_follower cf ON u.id = cf.user_id
 231             AND cf.community_id = pav.community_id
 232     LEFT JOIN post_read pr ON u.id = pr.user_id
 233         AND pr.post_id = pav.id
 234     LEFT JOIN post_saved ps ON u.id = ps.user_id
 235         AND ps.post_id = pav.id
 236     LEFT JOIN post_like pl ON u.id = pl.user_id
 237         AND pav.id = pl.post_id) AS us
 238 UNION ALL
 239 SELECT
 240     pav.*,
 241     NULL AS user_id,
 242     NULL AS my_vote,
 243     NULL AS subscribed,
 244     NULL AS read,
 245     NULL AS saved
 246 FROM
 247     post_aggregates_view pav;
 248
 249 -- The post fast table
 250 CREATE TABLE post_aggregates_fast AS
 251 SELECT
 252     *
 253 FROM
 254     post_aggregates_view;
 255
 256 ALTER TABLE post_aggregates_fast
 257     ADD PRIMARY KEY (id);
 258
 259 -- For the hot rank resorting
 260 CREATE INDEX idx_post_aggregates_fast_hot_rank_published ON post_aggregates_fast (hot_rank DESC, published DESC);
 261
 262 CREATE VIEW post_fast_view AS
 263 SELECT
 264     pav.*,
 265     us.id AS user_id,
 266     us.user_vote AS my_vote,
 267     us.is_subbed::bool AS subscribed,
 268     us.is_read::bool AS read,
 269     us.is_saved::bool AS saved
 270 FROM
 271     post_aggregates_fast pav
 272     CROSS JOIN LATERAL (
 273         SELECT
 274             u.id,
 275             coalesce(cf.community_id, 0) AS is_subbed,
 276             coalesce(pr.post_id, 0) AS is_read,
 277             coalesce(ps.post_id, 0) AS is_saved,
 278             coalesce(pl.score, 0) AS user_vote
 279         FROM
 280             user_ u
 281             LEFT JOIN community_user_ban cb ON u.id = cb.user_id
 282                 AND cb.community_id = pav.community_id
 283         LEFT JOIN community_follower cf ON u.id = cf.user_id
 284             AND cf.community_id = pav.community_id
 285     LEFT JOIN post_read pr ON u.id = pr.user_id
 286         AND pr.post_id = pav.id
 287     LEFT JOIN post_saved ps ON u.id = ps.user_id
 288         AND ps.post_id = pav.id
 289     LEFT JOIN post_like pl ON u.id = pl.user_id
 290         AND pav.id = pl.post_id) AS us
 291 UNION ALL
 292 SELECT
 293     pav.*,
 294     NULL AS user_id,
 295     NULL AS my_vote,
 296     NULL AS subscribed,
 297     NULL AS read,
 298     NULL AS saved
 299 FROM
 300     post_aggregates_fast pav;
 301
 302 DROP TRIGGER refresh_post ON post;
 303
 304 CREATE TRIGGER refresh_post
 305     AFTER INSERT OR UPDATE OR DELETE ON post
 306     FOR EACH ROW
 307     EXECUTE PROCEDURE refresh_post ();
 308
 309 -- Sample select
 310 -- select id, name from post_fast_view where name like 'test_post' and user_id is null;
 311 -- Sample insert
 312 -- insert into post(name, creator_id, community_id) values ('test_post', 2, 2);
 313 -- Sample delete
 314 -- delete from post where name like 'test_post';
 315 -- Sample update
 316 -- update post set community_id = 4  where name like 'test_post';
 317 CREATE OR REPLACE FUNCTION refresh_post ()
 318     RETURNS TRIGGER
 319     LANGUAGE plpgsql
 320     AS $$
 321 BEGIN
 322     IF (TG_OP = 'DELETE') THEN
 323         DELETE FROM post_aggregates_fast
 324         WHERE id = OLD.id;
 325         -- Update community number of posts
 326         UPDATE
 327             community_aggregates_fast
 328         SET
 329             number_of_posts = number_of_posts - 1
 330         WHERE
 331             id = OLD.community_id;
 332     ELSIF (TG_OP = 'UPDATE') THEN
 333         DELETE FROM post_aggregates_fast
 334         WHERE id = OLD.id;
 335         INSERT INTO post_aggregates_fast
 336         SELECT
 337             *
 338         FROM
 339             post_aggregates_view
 340         WHERE
 341             id = NEW.id;
 342     ELSIF (TG_OP = 'INSERT') THEN
 343         INSERT INTO post_aggregates_fast
 344         SELECT
 345             *
 346         FROM
 347             post_aggregates_view
 348         WHERE
 349             id = NEW.id;
 350         -- Update that users number of posts, post score
 351         DELETE FROM user_fast
 352         WHERE id = NEW.creator_id;
 353         INSERT INTO user_fast
 354         SELECT
 355             *
 356         FROM
 357             user_view
 358         WHERE
 359             id = NEW.creator_id;
 360         -- Update community number of posts
 361         UPDATE
 362             community_aggregates_fast
 363         SET
 364             number_of_posts = number_of_posts + 1
 365         WHERE
 366             id = NEW.community_id;
 367         -- Update the hot rank on the post table
 368         -- TODO this might not correctly update it, using a 1 week interval
 369         UPDATE
 370             post_aggregates_fast AS paf
 371         SET
 372             hot_rank = pav.hot_rank
 373         FROM
 374             post_aggregates_view AS pav
 375         WHERE
 376             paf.id = pav.id
 377             AND (pav.published > ('now'::timestamp - '1 week'::interval));
 378     END IF;
 379     RETURN NULL;
 380 END
 381 $$;
 382
 383 -- Community
 384 -- Redoing the views : Credit eiknat
 385 DROP VIEW community_moderator_view;
 386
 387 DROP VIEW community_follower_view;
 388
 389 DROP VIEW community_user_ban_view;
 390
 391 DROP VIEW community_view;
 392
 393 DROP VIEW community_aggregates_view;
 394
 395 CREATE VIEW community_aggregates_view AS
 396 SELECT
 397     c.id,
 398     c.name,
 399     c.title,
 400     c.description,
 401     c.category_id,
 402     c.creator_id,
 403     c.removed,
 404     c.published,
 405     c.updated,
 406     c.deleted,
 407     c.nsfw,
 408     c.actor_id,
 409     c.local,
 410     c.last_refreshed_at,
 411     u.actor_id AS creator_actor_id,
 412     u.local AS creator_local,
 413     u.name AS creator_name,
 414     u.avatar AS creator_avatar,
 415     cat.name AS category_name,
 416     coalesce(cf.subs, 0) AS number_of_subscribers,
 417     coalesce(cd.posts, 0) AS number_of_posts,
 418     coalesce(cd.comments, 0) AS number_of_comments,
 419     hot_rank (cf.subs, c.published) AS hot_rank
 420 FROM
 421     community c
 422     LEFT JOIN user_ u ON c.creator_id = u.id
 423     LEFT JOIN category cat ON c.category_id = cat.id
 424     LEFT JOIN (
 425         SELECT
 426             p.community_id,
 427             count(DISTINCT p.id) AS posts,
 428             count(DISTINCT ct.id) AS comments
 429         FROM
 430             post p
 431             JOIN comment ct ON p.id = ct.post_id
 432         GROUP BY
 433             p.community_id) cd ON cd.community_id = c.id
 434     LEFT JOIN (
 435         SELECT
 436             community_id,
 437             count(*) AS subs
 438         FROM
 439             community_follower
 440         GROUP BY
 441             community_id) cf ON cf.community_id = c.id;
 442
 443 CREATE VIEW community_view AS
 444 SELECT
 445     cv.*,
 446     us.user AS user_id,
 447     us.is_subbed::bool AS subscribed
 448 FROM
 449     community_aggregates_view cv
 450     CROSS JOIN LATERAL (
 451         SELECT
 452             u.id AS user,
 453             coalesce(cf.community_id, 0) AS is_subbed
 454         FROM
 455             user_ u
 456             LEFT JOIN community_follower cf ON u.id = cf.user_id
 457                 AND cf.community_id = cv.id) AS us
 458 UNION ALL
 459 SELECT
 460     cv.*,
 461     NULL AS user_id,
 462     NULL AS subscribed
 463 FROM
 464     community_aggregates_view cv;
 465
 466 CREATE VIEW community_moderator_view AS
 467 SELECT
 468     cm.*,
 469     u.actor_id AS user_actor_id,
 470     u.local AS user_local,
 471     u.name AS user_name,
 472     u.avatar AS avatar,
 473     c.actor_id AS community_actor_id,
 474     c.local AS community_local,
 475     c.name AS community_name
 476 FROM
 477     community_moderator cm
 478     LEFT JOIN user_ u ON cm.user_id = u.id
 479     LEFT JOIN community c ON cm.community_id = c.id;
 480
 481 CREATE VIEW community_follower_view AS
 482 SELECT
 483     cf.*,
 484     u.actor_id AS user_actor_id,
 485     u.local AS user_local,
 486     u.name AS user_name,
 487     u.avatar AS avatar,
 488     c.actor_id AS community_actor_id,
 489     c.local AS community_local,
 490     c.name AS community_name
 491 FROM
 492     community_follower cf
 493     LEFT JOIN user_ u ON cf.user_id = u.id
 494     LEFT JOIN community c ON cf.community_id = c.id;
 495
 496 CREATE VIEW community_user_ban_view AS
 497 SELECT
 498     cb.*,
 499     u.actor_id AS user_actor_id,
 500     u.local AS user_local,
 501     u.name AS user_name,
 502     u.avatar AS avatar,
 503     c.actor_id AS community_actor_id,
 504     c.local AS community_local,
 505     c.name AS community_name
 506 FROM
 507     community_user_ban cb
 508     LEFT JOIN user_ u ON cb.user_id = u.id
 509     LEFT JOIN community c ON cb.community_id = c.id;
 510
 511 -- The community fast table
 512 CREATE TABLE community_aggregates_fast AS
 513 SELECT
 514     *
 515 FROM
 516     community_aggregates_view;
 517
 518 ALTER TABLE community_aggregates_fast
 519     ADD PRIMARY KEY (id);
 520
 521 CREATE VIEW community_fast_view AS
 522 SELECT
 523     ac.*,
 524     u.id AS user_id,
 525     (
 526         SELECT
 527             cf.id::boolean
 528         FROM
 529             community_follower cf
 530         WHERE
 531             u.id = cf.user_id
 532             AND ac.id = cf.community_id) AS subscribed
 533 FROM
 534     user_ u
 535     CROSS JOIN (
 536         SELECT
 537             ca.*
 538         FROM
 539             community_aggregates_fast ca) ac
 540 UNION ALL
 541 SELECT
 542     caf.*,
 543     NULL AS user_id,
 544     NULL AS subscribed
 545 FROM
 546     community_aggregates_fast caf;
 547
 548 DROP TRIGGER refresh_community ON community;
 549
 550 CREATE TRIGGER refresh_community
 551     AFTER INSERT OR UPDATE OR DELETE ON community
 552     FOR EACH ROW
 553     EXECUTE PROCEDURE refresh_community ();
 554
 555 -- Sample select
 556 -- select * from community_fast_view where name like 'test_community_name' and user_id is null;
 557 -- Sample insert
 558 -- insert into community(name, title, category_id, creator_id) values ('test_community_name', 'test_community_title', 1, 2);
 559 -- Sample delete
 560 -- delete from community where name like 'test_community_name';
 561 -- Sample update
 562 -- update community set title = 'test_community_title_2'  where name like 'test_community_name';
 563 CREATE OR REPLACE FUNCTION refresh_community ()
 564     RETURNS TRIGGER
 565     LANGUAGE plpgsql
 566     AS $$
 567 BEGIN
 568     IF (TG_OP = 'DELETE') THEN
 569         DELETE FROM community_aggregates_fast
 570         WHERE id = OLD.id;
 571     ELSIF (TG_OP = 'UPDATE') THEN
 572         DELETE FROM community_aggregates_fast
 573         WHERE id = OLD.id;
 574         INSERT INTO community_aggregates_fast
 575         SELECT
 576             *
 577         FROM
 578             community_aggregates_view
 579         WHERE
 580             id = NEW.id;
 581         -- Update user view due to owner changes
 582         DELETE FROM user_fast
 583         WHERE id = NEW.creator_id;
 584         INSERT INTO user_fast
 585         SELECT
 586             *
 587         FROM
 588             user_view
 589         WHERE
 590             id = NEW.creator_id;
 591         -- Update post view due to community changes
 592         DELETE FROM post_aggregates_fast
 593         WHERE community_id = NEW.id;
 594         INSERT INTO post_aggregates_fast
 595         SELECT
 596             *
 597         FROM
 598             post_aggregates_view
 599         WHERE
 600             community_id = NEW.id;
 601         -- TODO make sure this shows up in the users page ?
 602     ELSIF (TG_OP = 'INSERT') THEN
 603         INSERT INTO community_aggregates_fast
 604         SELECT
 605             *
 606         FROM
 607             community_aggregates_view
 608         WHERE
 609             id = NEW.id;
 610     END IF;
 611     RETURN NULL;
 612 END
 613 $$;
 614
 615 -- Comment
 616 DROP VIEW user_mention_view;
 617
 618 DROP VIEW comment_view;
 619
 620 DROP VIEW comment_aggregates_view;
 621
 622 CREATE VIEW comment_aggregates_view AS
 623 SELECT
 624     ct.*,
 625     -- community details
 626     p.community_id,
 627     c.actor_id AS community_actor_id,
 628     c."local" AS community_local,
 629     c."name" AS community_name,
 630     -- creator details
 631     u.banned AS banned,
 632     coalesce(cb.id, 0)::bool AS banned_from_community,
 633     u.actor_id AS creator_actor_id,
 634     u.local AS creator_local,
 635     u.name AS creator_name,
 636     u.avatar AS creator_avatar,
 637     -- score details
 638     coalesce(cl.total, 0) AS score,
 639     coalesce(cl.up, 0) AS upvotes,
 640     coalesce(cl.down, 0) AS downvotes,
 641     hot_rank (coalesce(cl.total, 0), ct.published) AS hot_rank
 642 FROM
 643     comment ct
 644     LEFT JOIN post p ON ct.post_id = p.id
 645     LEFT JOIN community c ON p.community_id = c.id
 646     LEFT JOIN user_ u ON ct.creator_id = u.id
 647     LEFT JOIN community_user_ban cb ON ct.creator_id = cb.user_id
 648         AND p.id = ct.post_id
 649         AND p.community_id = cb.community_id
 650     LEFT JOIN (
 651         SELECT
 652             l.comment_id AS id,
 653             sum(l.score) AS total,
 654             count(
 655                 CASE WHEN l.score = 1 THEN
 656                     1
 657                 ELSE
 658                     NULL
 659                 END) AS up,
 660             count(
 661                 CASE WHEN l.score = - 1 THEN
 662                     1
 663                 ELSE
 664                     NULL
 665                 END) AS down
 666         FROM
 667             comment_like l
 668         GROUP BY
 669             comment_id) AS cl ON cl.id = ct.id;
 670
 671 CREATE OR REPLACE VIEW comment_view AS (
 672     SELECT
 673         cav.*,
 674         us.user_id AS user_id,
 675         us.my_vote AS my_vote,
 676         us.is_subbed::bool AS subscribed,
 677         us.is_saved::bool AS saved
 678     FROM
 679         comment_aggregates_view cav
 680     CROSS JOIN LATERAL (
 681         SELECT
 682             u.id AS user_id,
 683             coalesce(cl.score, 0) AS my_vote,
 684             coalesce(cf.id, 0) AS is_subbed,
 685             coalesce(cs.id, 0) AS is_saved
 686         FROM
 687             user_ u
 688             LEFT JOIN comment_like cl ON u.id = cl.user_id
 689                 AND cav.id = cl.comment_id
 690         LEFT JOIN comment_saved cs ON u.id = cs.user_id
 691             AND cs.comment_id = cav.id
 692     LEFT JOIN community_follower cf ON u.id = cf.user_id
 693         AND cav.community_id = cf.community_id) AS us
 694 UNION ALL
 695 SELECT
 696     cav.*,
 697     NULL AS user_id,
 698     NULL AS my_vote,
 699     NULL AS subscribed,
 700     NULL AS saved
 701 FROM
 702     comment_aggregates_view cav);
 703
 704 -- The fast view
 705 CREATE TABLE comment_aggregates_fast AS
 706 SELECT
 707     *
 708 FROM
 709     comment_aggregates_view;
 710
 711 ALTER TABLE comment_aggregates_fast
 712     ADD PRIMARY KEY (id);
 713
 714 CREATE VIEW comment_fast_view AS
 715 SELECT
 716     cav.*,
 717     us.user_id AS user_id,
 718     us.my_vote AS my_vote,
 719     us.is_subbed::bool AS subscribed,
 720     us.is_saved::bool AS saved
 721 FROM
 722     comment_aggregates_fast cav
 723     CROSS JOIN LATERAL (
 724         SELECT
 725             u.id AS user_id,
 726             coalesce(cl.score, 0) AS my_vote,
 727             coalesce(cf.id, 0) AS is_subbed,
 728             coalesce(cs.id, 0) AS is_saved
 729         FROM
 730             user_ u
 731             LEFT JOIN comment_like cl ON u.id = cl.user_id
 732                 AND cav.id = cl.comment_id
 733         LEFT JOIN comment_saved cs ON u.id = cs.user_id
 734             AND cs.comment_id = cav.id
 735     LEFT JOIN community_follower cf ON u.id = cf.user_id
 736         AND cav.community_id = cf.community_id) AS us
 737 UNION ALL
 738 SELECT
 739     cav.*,
 740     NULL AS user_id,
 741     NULL AS my_vote,
 742     NULL AS subscribed,
 743     NULL AS saved
 744 FROM
 745     comment_aggregates_fast cav;
 746
 747 -- Do the reply_view referencing the comment_fast_view
 748 CREATE VIEW reply_fast_view AS
 749 with closereply AS (
 750     SELECT
 751         c2.id,
 752         c2.creator_id AS sender_id,
 753         c.creator_id AS recipient_id
 754     FROM
 755         comment c
 756         INNER JOIN comment c2 ON c.id = c2.parent_id
 757     WHERE
 758         c2.creator_id != c.creator_id
 759         -- Do union where post is null
 760     UNION
 761     SELECT
 762         c.id,
 763         c.creator_id AS sender_id,
 764         p.creator_id AS recipient_id
 765     FROM
 766         comment c,
 767         post p
 768     WHERE
 769         c.post_id = p.id
 770         AND c.parent_id IS NULL
 771         AND c.creator_id != p.creator_id
 772 )
 773 SELECT
 774     cv.*,
 775     closereply.recipient_id
 776 FROM
 777     comment_fast_view cv,
 778     closereply
 779 WHERE
 780     closereply.id = cv.id;
 781
 782 -- user mention
 783 CREATE VIEW user_mention_view AS
 784 SELECT
 785     c.id,
 786     um.id AS user_mention_id,
 787     c.creator_id,
 788     c.creator_actor_id,
 789     c.creator_local,
 790     c.post_id,
 791     c.parent_id,
 792     c.content,
 793     c.removed,
 794     um.read,
 795     c.published,
 796     c.updated,
 797     c.deleted,
 798     c.community_id,
 799     c.community_actor_id,
 800     c.community_local,
 801     c.community_name,
 802     c.banned,
 803     c.banned_from_community,
 804     c.creator_name,
 805     c.creator_avatar,
 806     c.score,
 807     c.upvotes,
 808     c.downvotes,
 809     c.hot_rank,
 810     c.user_id,
 811     c.my_vote,
 812     c.saved,
 813     um.recipient_id,
 814     (
 815         SELECT
 816             actor_id
 817         FROM
 818             user_ u
 819         WHERE
 820             u.id = um.recipient_id) AS recipient_actor_id,
 821     (
 822         SELECT
 823             local
 824         FROM
 825             user_ u
 826         WHERE
 827             u.id = um.recipient_id) AS recipient_local
 828 FROM
 829     user_mention um,
 830     comment_view c
 831 WHERE
 832     um.comment_id = c.id;
 833
 834 CREATE VIEW user_mention_fast_view AS
 835 SELECT
 836     ac.id,
 837     um.id AS user_mention_id,
 838     ac.creator_id,
 839     ac.creator_actor_id,
 840     ac.creator_local,
 841     ac.post_id,
 842     ac.parent_id,
 843     ac.content,
 844     ac.removed,
 845     um.read,
 846     ac.published,
 847     ac.updated,
 848     ac.deleted,
 849     ac.community_id,
 850     ac.community_actor_id,
 851     ac.community_local,
 852     ac.community_name,
 853     ac.banned,
 854     ac.banned_from_community,
 855     ac.creator_name,
 856     ac.creator_avatar,
 857     ac.score,
 858     ac.upvotes,
 859     ac.downvotes,
 860     ac.hot_rank,
 861     u.id AS user_id,
 862     coalesce(cl.score, 0) AS my_vote,
 863     (
 864         SELECT
 865             cs.id::bool
 866         FROM
 867             comment_saved cs
 868         WHERE
 869             u.id = cs.user_id
 870             AND cs.comment_id = ac.id) AS saved,
 871     um.recipient_id,
 872     (
 873         SELECT
 874             actor_id
 875         FROM
 876             user_ u
 877         WHERE
 878             u.id = um.recipient_id) AS recipient_actor_id,
 879     (
 880         SELECT
 881             local
 882         FROM
 883             user_ u
 884         WHERE
 885             u.id = um.recipient_id) AS recipient_local
 886 FROM
 887     user_ u
 888     CROSS JOIN (
 889         SELECT
 890             ca.*
 891         FROM
 892             comment_aggregates_fast ca) ac
 893     LEFT JOIN comment_like cl ON u.id = cl.user_id
 894         AND ac.id = cl.comment_id
 895     LEFT JOIN user_mention um ON um.comment_id = ac.id
 896 UNION ALL
 897 SELECT
 898     ac.id,
 899     um.id AS user_mention_id,
 900     ac.creator_id,
 901     ac.creator_actor_id,
 902     ac.creator_local,
 903     ac.post_id,
 904     ac.parent_id,
 905     ac.content,
 906     ac.removed,
 907     um.read,
 908     ac.published,
 909     ac.updated,
 910     ac.deleted,
 911     ac.community_id,
 912     ac.community_actor_id,
 913     ac.community_local,
 914     ac.community_name,
 915     ac.banned,
 916     ac.banned_from_community,
 917     ac.creator_name,
 918     ac.creator_avatar,
 919     ac.score,
 920     ac.upvotes,
 921     ac.downvotes,
 922     ac.hot_rank,
 923     NULL AS user_id,
 924     NULL AS my_vote,
 925     NULL AS saved,
 926     um.recipient_id,
 927     (
 928         SELECT
 929             actor_id
 930         FROM
 931             user_ u
 932         WHERE
 933             u.id = um.recipient_id) AS recipient_actor_id,
 934     (
 935         SELECT
 936             local
 937         FROM
 938             user_ u
 939         WHERE
 940             u.id = um.recipient_id) AS recipient_local
 941 FROM
 942     comment_aggregates_fast ac
 943     LEFT JOIN user_mention um ON um.comment_id = ac.id;
 944
 945 DROP TRIGGER refresh_comment ON comment;
 946
 947 CREATE TRIGGER refresh_comment
 948     AFTER INSERT OR UPDATE OR DELETE ON comment
 949     FOR EACH ROW
 950     EXECUTE PROCEDURE refresh_comment ();
 951
 952 -- Sample select
 953 -- select * from comment_fast_view where content = 'test_comment' and user_id is null;
 954 -- Sample insert
 955 -- insert into comment(creator_id, post_id, content) values (2, 2, 'test_comment');
 956 -- Sample delete
 957 -- delete from comment where content like 'test_comment';
 958 -- Sample update
 959 -- update comment set removed = true where content like 'test_comment';
 960 CREATE OR REPLACE FUNCTION refresh_comment ()
 961     RETURNS TRIGGER
 962     LANGUAGE plpgsql
 963     AS $$
 964 BEGIN
 965     IF (TG_OP = 'DELETE') THEN
 966         DELETE FROM comment_aggregates_fast
 967         WHERE id = OLD.id;
 968         -- Update community number of comments
 969         UPDATE
 970             community_aggregates_fast AS caf
 971         SET
 972             number_of_comments = number_of_comments - 1
 973         FROM
 974             post AS p
 975         WHERE
 976             caf.id = p.community_id
 977             AND p.id = OLD.post_id;
 978     ELSIF (TG_OP = 'UPDATE') THEN
 979         DELETE FROM comment_aggregates_fast
 980         WHERE id = OLD.id;
 981         INSERT INTO comment_aggregates_fast
 982         SELECT
 983             *
 984         FROM
 985             comment_aggregates_view
 986         WHERE
 987             id = NEW.id;
 988     ELSIF (TG_OP = 'INSERT') THEN
 989         INSERT INTO comment_aggregates_fast
 990         SELECT
 991             *
 992         FROM
 993             comment_aggregates_view
 994         WHERE
 995             id = NEW.id;
 996         -- Update user view due to comment count
 997         UPDATE
 998             user_fast
 999         SET
1000             number_of_comments = number_of_comments + 1
1001         WHERE
1002             id = NEW.creator_id;
1003         -- Update post view due to comment count, new comment activity time, but only on new posts
1004         -- TODO this could be done more efficiently
1005         DELETE FROM post_aggregates_fast
1006         WHERE id = NEW.post_id;
1007         INSERT INTO post_aggregates_fast
1008         SELECT
1009             *
1010         FROM
1011             post_aggregates_view
1012         WHERE
1013             id = NEW.post_id;
1014         -- Force the hot rank as zero on week-older posts
1015         UPDATE
1016             post_aggregates_fast AS paf
1017         SET
1018             hot_rank = 0
1019         WHERE
1020             paf.id = NEW.post_id
1021             AND (paf.published < ('now'::timestamp - '1 week'::interval));
1022         -- Update community number of comments
1023         UPDATE
1024             community_aggregates_fast AS caf
1025         SET
1026             number_of_comments = number_of_comments + 1
1027         FROM
1028             post AS p
1029         WHERE
1030             caf.id = p.community_id
1031             AND p.id = NEW.post_id;
1032     END IF;
1033     RETURN NULL;
1034 END
1035 $$;
1036
1037 -- post_like
1038 -- select id, score, my_vote from post_fast_view where id = 29 and user_id = 4;
1039 -- Sample insert
1040 -- insert into post_like(user_id, post_id, score) values (4, 29, 1);
1041 -- Sample delete
1042 -- delete from post_like where user_id = 4 and post_id = 29;
1043 -- Sample update
1044 -- update post_like set score = -1 where user_id = 4 and post_id = 29;
1045 -- TODO test this a LOT
1046 CREATE OR REPLACE FUNCTION refresh_post_like ()
1047     RETURNS TRIGGER
1048     LANGUAGE plpgsql
1049     AS $$
1050 BEGIN
1051     IF (TG_OP = 'DELETE') THEN
1052         UPDATE
1053             post_aggregates_fast
1054         SET
1055             score = CASE WHEN (OLD.score = 1) THEN
1056                 score - 1
1057             ELSE
1058                 score + 1
1059             END,
1060             upvotes = CASE WHEN (OLD.score = 1) THEN
1061                 upvotes - 1
1062             ELSE
1063                 upvotes
1064             END,
1065             downvotes = CASE WHEN (OLD.score = - 1) THEN
1066                 downvotes - 1
1067             ELSE
1068                 downvotes
1069             END
1070         WHERE
1071             id = OLD.post_id;
1072     ELSIF (TG_OP = 'INSERT') THEN
1073         UPDATE
1074             post_aggregates_fast
1075         SET
1076             score = CASE WHEN (NEW.score = 1) THEN
1077                 score + 1
1078             ELSE
1079                 score - 1
1080             END,
1081             upvotes = CASE WHEN (NEW.score = 1) THEN
1082                 upvotes + 1
1083             ELSE
1084                 upvotes
1085             END,
1086             downvotes = CASE WHEN (NEW.score = - 1) THEN
1087                 downvotes + 1
1088             ELSE
1089                 downvotes
1090             END
1091         WHERE
1092             id = NEW.post_id;
1093     END IF;
1094     RETURN NULL;
1095 END
1096 $$;
1097
1098 DROP TRIGGER refresh_post_like ON post_like;
1099
1100 CREATE TRIGGER refresh_post_like
1101     AFTER INSERT OR DELETE ON post_like
1102     FOR EACH ROW
1103     EXECUTE PROCEDURE refresh_post_like ();
1104
1105 -- comment_like
1106 -- select id, score, my_vote from comment_fast_view where id = 29 and user_id = 4;
1107 -- Sample insert
1108 -- insert into comment_like(user_id, comment_id, post_id, score) values (4, 29, 51, 1);
1109 -- Sample delete
1110 -- delete from comment_like where user_id = 4 and comment_id = 29;
1111 -- Sample update
1112 -- update comment_like set score = -1 where user_id = 4 and comment_id = 29;
1113 CREATE OR REPLACE FUNCTION refresh_comment_like ()
1114     RETURNS TRIGGER
1115     LANGUAGE plpgsql
1116     AS $$
1117 BEGIN
1118     -- TODO possibly select from comment_fast to get previous scores, instead of re-fetching the views?
1119     IF (TG_OP = 'DELETE') THEN
1120         UPDATE
1121             comment_aggregates_fast
1122         SET
1123             score = CASE WHEN (OLD.score = 1) THEN
1124                 score - 1
1125             ELSE
1126                 score + 1
1127             END,
1128             upvotes = CASE WHEN (OLD.score = 1) THEN
1129                 upvotes - 1
1130             ELSE
1131                 upvotes
1132             END,
1133             downvotes = CASE WHEN (OLD.score = - 1) THEN
1134                 downvotes - 1
1135             ELSE
1136                 downvotes
1137             END
1138         WHERE
1139             id = OLD.comment_id;
1140     ELSIF (TG_OP = 'INSERT') THEN
1141         UPDATE
1142             comment_aggregates_fast
1143         SET
1144             score = CASE WHEN (NEW.score = 1) THEN
1145                 score + 1
1146             ELSE
1147                 score - 1
1148             END,
1149             upvotes = CASE WHEN (NEW.score = 1) THEN
1150                 upvotes + 1
1151             ELSE
1152                 upvotes
1153             END,
1154             downvotes = CASE WHEN (NEW.score = - 1) THEN
1155                 downvotes + 1
1156             ELSE
1157                 downvotes
1158             END
1159         WHERE
1160             id = NEW.comment_id;
1161     END IF;
1162     RETURN NULL;
1163 END
1164 $$;
1165
1166 DROP TRIGGER refresh_comment_like ON comment_like;
1167
1168 CREATE TRIGGER refresh_comment_like
1169     AFTER INSERT OR DELETE ON comment_like
1170     FOR EACH ROW
1171     EXECUTE PROCEDURE refresh_comment_like ();
1172
1173 -- Community user ban
1174 DROP TRIGGER refresh_community_user_ban ON community_user_ban;
1175
1176 CREATE TRIGGER refresh_community_user_ban
1177     AFTER INSERT OR DELETE -- Note this is missing after update
1178     ON community_user_ban
1179     FOR EACH ROW
1180     EXECUTE PROCEDURE refresh_community_user_ban ();
1181
1182 -- select creator_name, banned_from_community from comment_fast_view where user_id = 4 and content = 'test_before_ban';
1183 -- select creator_name, banned_from_community, community_id from comment_aggregates_fast where content = 'test_before_ban';
1184 -- Sample insert
1185 -- insert into comment(creator_id, post_id, content) values (1198, 341, 'test_before_ban');
1186 -- insert into community_user_ban(community_id, user_id) values (2, 1198);
1187 -- Sample delete
1188 -- delete from community_user_ban where user_id = 1198 and community_id = 2;
1189 -- delete from comment where content = 'test_before_ban';
1190 -- update comment_aggregates_fast set banned_from_community = false where creator_id = 1198 and community_id = 2;
1191 CREATE OR REPLACE FUNCTION refresh_community_user_ban ()
1192     RETURNS TRIGGER
1193     LANGUAGE plpgsql
1194     AS $$
1195 BEGIN
1196     -- TODO possibly select from comment_fast to get previous scores, instead of re-fetching the views?
1197     IF (TG_OP = 'DELETE') THEN
1198         UPDATE
1199             comment_aggregates_fast
1200         SET
1201             banned_from_community = FALSE
1202         WHERE
1203             creator_id = OLD.user_id
1204             AND community_id = OLD.community_id;
1205         UPDATE
1206             post_aggregates_fast
1207         SET
1208             banned_from_community = FALSE
1209         WHERE
1210             creator_id = OLD.user_id
1211             AND community_id = OLD.community_id;
1212     ELSIF (TG_OP = 'INSERT') THEN
1213         UPDATE
1214             comment_aggregates_fast
1215         SET
1216             banned_from_community = TRUE
1217         WHERE
1218             creator_id = NEW.user_id
1219             AND community_id = NEW.community_id;
1220         UPDATE
1221             post_aggregates_fast
1222         SET
1223             banned_from_community = TRUE
1224         WHERE
1225             creator_id = NEW.user_id
1226             AND community_id = NEW.community_id;
1227     END IF;
1228     RETURN NULL;
1229 END
1230 $$;
1231
1232 -- Community follower
1233 DROP TRIGGER refresh_community_follower ON community_follower;
1234
1235 CREATE TRIGGER refresh_community_follower
1236     AFTER INSERT OR DELETE -- Note this is missing after update
1237     ON community_follower
1238     FOR EACH ROW
1239     EXECUTE PROCEDURE refresh_community_follower ();
1240
1241 CREATE OR REPLACE FUNCTION refresh_community_follower ()
1242     RETURNS TRIGGER
1243     LANGUAGE plpgsql
1244     AS $$
1245 BEGIN
1246     IF (TG_OP = 'DELETE') THEN
1247         UPDATE
1248             community_aggregates_fast
1249         SET
1250             number_of_subscribers = number_of_subscribers - 1
1251         WHERE
1252             id = OLD.community_id;
1253     ELSIF (TG_OP = 'INSERT') THEN
1254         UPDATE
1255             community_aggregates_fast
1256         SET
1257             number_of_subscribers = number_of_subscribers + 1
1258         WHERE
1259             id = NEW.community_id;
1260     END IF;
1261     RETURN NULL;
1262 END
1263 $$;
1264