migrations/2020-01-13-025151_create_materialized_views/up.sql

   1 -- post
   2 CREATE VIEW post_aggregates_view AS
   3 SELECT
   4     p.*,
   5     (
   6         SELECT
   7             u.banned
   8         FROM
   9             user_ u
  10         WHERE
  11             p.creator_id = u.id) AS banned,
  12     (
  13         SELECT
  14             cb.id::bool
  15         FROM
  16             community_user_ban cb
  17         WHERE
  18             p.creator_id = cb.user_id
  19             AND p.community_id = cb.community_id) AS banned_from_community,
  20     (
  21         SELECT
  22             name
  23         FROM
  24             user_
  25         WHERE
  26             p.creator_id = user_.id) AS creator_name,
  27     (
  28         SELECT
  29             avatar
  30         FROM
  31             user_
  32         WHERE
  33             p.creator_id = user_.id) AS creator_avatar,
  34     (
  35         SELECT
  36             name
  37         FROM
  38             community
  39         WHERE
  40             p.community_id = community.id) AS community_name,
  41     (
  42         SELECT
  43             removed
  44         FROM
  45             community c
  46         WHERE
  47             p.community_id = c.id) AS community_removed,
  48     (
  49         SELECT
  50             deleted
  51         FROM
  52             community c
  53         WHERE
  54             p.community_id = c.id) AS community_deleted,
  55     (
  56         SELECT
  57             nsfw
  58         FROM
  59             community c
  60         WHERE
  61             p.community_id = c.id) AS community_nsfw,
  62     (
  63         SELECT
  64             count(*)
  65         FROM
  66             comment
  67         WHERE
  68             comment.post_id = p.id) AS number_of_comments,
  69     coalesce(sum(pl.score), 0) AS score,
  70     count(
  71         CASE WHEN pl.score = 1 THEN
  72             1
  73         ELSE
  74             NULL
  75         END) AS upvotes,
  76     count(
  77         CASE WHEN pl.score = - 1 THEN
  78             1
  79         ELSE
  80             NULL
  81         END) AS downvotes,
  82     hot_rank (coalesce(sum(pl.score), 0), p.published) AS hot_rank
  83 FROM
  84     post p
  85     LEFT JOIN post_like pl ON p.id = pl.post_id
  86 GROUP BY
  87     p.id;
  88
  89 CREATE MATERIALIZED VIEW post_aggregates_mview AS
  90 SELECT
  91     *
  92 FROM
  93     post_aggregates_view;
  94
  95 CREATE UNIQUE INDEX idx_post_aggregates_mview_id ON post_aggregates_mview (id);
  96
  97 DROP VIEW post_view;
  98
  99 CREATE VIEW post_view AS
 100 with all_post AS (
 101     SELECT
 102         pa.*
 103     FROM
 104         post_aggregates_view pa
 105 )
 106 SELECT
 107     ap.*,
 108     u.id AS user_id,
 109     coalesce(pl.score, 0) AS my_vote,
 110     (
 111         SELECT
 112             cf.id::bool
 113         FROM
 114             community_follower cf
 115         WHERE
 116             u.id = cf.user_id
 117             AND cf.community_id = ap.community_id) AS subscribed,
 118     (
 119         SELECT
 120             pr.id::bool
 121         FROM
 122             post_read pr
 123         WHERE
 124             u.id = pr.user_id
 125             AND pr.post_id = ap.id) AS read,
 126     (
 127         SELECT
 128             ps.id::bool
 129         FROM
 130             post_saved ps
 131         WHERE
 132             u.id = ps.user_id
 133             AND ps.post_id = ap.id) AS saved
 134 FROM
 135     user_ u
 136     CROSS JOIN all_post ap
 137     LEFT JOIN post_like pl ON u.id = pl.user_id
 138         AND ap.id = pl.post_id
 139     UNION ALL
 140     SELECT
 141         ap.*,
 142         NULL AS user_id,
 143         NULL AS my_vote,
 144         NULL AS subscribed,
 145         NULL AS read,
 146         NULL AS saved
 147     FROM
 148         all_post ap;
 149
 150 CREATE VIEW post_mview AS
 151 with all_post AS (
 152     SELECT
 153         pa.*
 154     FROM
 155         post_aggregates_mview pa
 156 )
 157 SELECT
 158     ap.*,
 159     u.id AS user_id,
 160     coalesce(pl.score, 0) AS my_vote,
 161     (
 162         SELECT
 163             cf.id::bool
 164         FROM
 165             community_follower cf
 166         WHERE
 167             u.id = cf.user_id
 168             AND cf.community_id = ap.community_id) AS subscribed,
 169     (
 170         SELECT
 171             pr.id::bool
 172         FROM
 173             post_read pr
 174         WHERE
 175             u.id = pr.user_id
 176             AND pr.post_id = ap.id) AS read,
 177     (
 178         SELECT
 179             ps.id::bool
 180         FROM
 181             post_saved ps
 182         WHERE
 183             u.id = ps.user_id
 184             AND ps.post_id = ap.id) AS saved
 185 FROM
 186     user_ u
 187     CROSS JOIN all_post ap
 188     LEFT JOIN post_like pl ON u.id = pl.user_id
 189         AND ap.id = pl.post_id
 190     UNION ALL
 191     SELECT
 192         ap.*,
 193         NULL AS user_id,
 194         NULL AS my_vote,
 195         NULL AS subscribed,
 196         NULL AS read,
 197         NULL AS saved
 198     FROM
 199         all_post ap;
 200
 201 -- user_view
 202 DROP VIEW user_view;
 203
 204 CREATE VIEW user_view AS
 205 SELECT
 206     u.id,
 207     u.name,
 208     u.avatar,
 209     u.email,
 210     u.fedi_name,
 211     u.admin,
 212     u.banned,
 213     u.show_avatars,
 214     u.send_notifications_to_email,
 215     u.published,
 216     (
 217         SELECT
 218             count(*)
 219         FROM
 220             post p
 221         WHERE
 222             p.creator_id = u.id) AS number_of_posts,
 223     (
 224         SELECT
 225             coalesce(sum(score), 0)
 226         FROM
 227             post p,
 228             post_like pl
 229         WHERE
 230             u.id = p.creator_id
 231             AND p.id = pl.post_id) AS post_score,
 232     (
 233         SELECT
 234             count(*)
 235         FROM
 236             comment c
 237         WHERE
 238             c.creator_id = u.id) AS number_of_comments,
 239     (
 240         SELECT
 241             coalesce(sum(score), 0)
 242         FROM
 243             comment c,
 244             comment_like cl
 245         WHERE
 246             u.id = c.creator_id
 247             AND c.id = cl.comment_id) AS comment_score
 248 FROM
 249     user_ u;
 250
 251 CREATE MATERIALIZED VIEW user_mview AS
 252 SELECT
 253     *
 254 FROM
 255     user_view;
 256
 257 CREATE UNIQUE INDEX idx_user_mview_id ON user_mview (id);
 258
 259 -- community
 260 CREATE VIEW community_aggregates_view AS
 261 SELECT
 262     c.*,
 263     (
 264         SELECT
 265             name
 266         FROM
 267             user_ u
 268         WHERE
 269             c.creator_id = u.id) AS creator_name,
 270     (
 271         SELECT
 272             avatar
 273         FROM
 274             user_ u
 275         WHERE
 276             c.creator_id = u.id) AS creator_avatar,
 277     (
 278         SELECT
 279             name
 280         FROM
 281             category ct
 282         WHERE
 283             c.category_id = ct.id) AS category_name,
 284     (
 285         SELECT
 286             count(*)
 287         FROM
 288             community_follower cf
 289         WHERE
 290             cf.community_id = c.id) AS number_of_subscribers,
 291     (
 292         SELECT
 293             count(*)
 294         FROM
 295             post p
 296         WHERE
 297             p.community_id = c.id) AS number_of_posts,
 298     (
 299         SELECT
 300             count(*)
 301         FROM
 302             comment co,
 303             post p
 304         WHERE
 305             c.id = p.community_id
 306             AND p.id = co.post_id) AS number_of_comments,
 307     hot_rank ((
 308         SELECT
 309             count(*)
 310         FROM community_follower cf
 311         WHERE
 312             cf.community_id = c.id), c.published) AS hot_rank
 313 FROM
 314     community c;
 315
 316 CREATE MATERIALIZED VIEW community_aggregates_mview AS
 317 SELECT
 318     *
 319 FROM
 320     community_aggregates_view;
 321
 322 CREATE UNIQUE INDEX idx_community_aggregates_mview_id ON community_aggregates_mview (id);
 323
 324 DROP VIEW community_view;
 325
 326 CREATE VIEW community_view AS
 327 with all_community AS (
 328     SELECT
 329         ca.*
 330     FROM
 331         community_aggregates_view ca
 332 )
 333 SELECT
 334     ac.*,
 335     u.id AS user_id,
 336     (
 337         SELECT
 338             cf.id::boolean
 339         FROM
 340             community_follower cf
 341         WHERE
 342             u.id = cf.user_id
 343             AND ac.id = cf.community_id) AS subscribed
 344 FROM
 345     user_ u
 346     CROSS JOIN all_community ac
 347 UNION ALL
 348 SELECT
 349     ac.*,
 350     NULL AS user_id,
 351     NULL AS subscribed
 352 FROM
 353     all_community ac;
 354
 355 CREATE VIEW community_mview AS
 356 with all_community AS (
 357     SELECT
 358         ca.*
 359     FROM
 360         community_aggregates_mview ca
 361 )
 362 SELECT
 363     ac.*,
 364     u.id AS user_id,
 365     (
 366         SELECT
 367             cf.id::boolean
 368         FROM
 369             community_follower cf
 370         WHERE
 371             u.id = cf.user_id
 372             AND ac.id = cf.community_id) AS subscribed
 373 FROM
 374     user_ u
 375     CROSS JOIN all_community ac
 376 UNION ALL
 377 SELECT
 378     ac.*,
 379     NULL AS user_id,
 380     NULL AS subscribed
 381 FROM
 382     all_community ac;
 383
 384 -- reply and comment view
 385 CREATE VIEW comment_aggregates_view AS
 386 SELECT
 387     c.*,
 388     (
 389         SELECT
 390             community_id
 391         FROM
 392             post p
 393         WHERE
 394             p.id = c.post_id), (
 395         SELECT
 396             u.banned
 397         FROM
 398             user_ u
 399         WHERE
 400             c.creator_id = u.id) AS banned,
 401     (
 402         SELECT
 403             cb.id::bool
 404         FROM
 405             community_user_ban cb,
 406             post p
 407         WHERE
 408             c.creator_id = cb.user_id
 409             AND p.id = c.post_id
 410             AND p.community_id = cb.community_id) AS banned_from_community,
 411     (
 412         SELECT
 413             name
 414         FROM
 415             user_
 416         WHERE
 417             c.creator_id = user_.id) AS creator_name,
 418     (
 419         SELECT
 420             avatar
 421         FROM
 422             user_
 423         WHERE
 424             c.creator_id = user_.id) AS creator_avatar,
 425     coalesce(sum(cl.score), 0) AS score,
 426     count(
 427         CASE WHEN cl.score = 1 THEN
 428             1
 429         ELSE
 430             NULL
 431         END) AS upvotes,
 432     count(
 433         CASE WHEN cl.score = - 1 THEN
 434             1
 435         ELSE
 436             NULL
 437         END) AS downvotes
 438 FROM
 439     comment c
 440     LEFT JOIN comment_like cl ON c.id = cl.comment_id
 441 GROUP BY
 442     c.id;
 443
 444 CREATE MATERIALIZED VIEW comment_aggregates_mview AS
 445 SELECT
 446     *
 447 FROM
 448     comment_aggregates_view;
 449
 450 CREATE UNIQUE INDEX idx_comment_aggregates_mview_id ON comment_aggregates_mview (id);
 451
 452 DROP VIEW reply_view;
 453
 454 DROP VIEW user_mention_view;
 455
 456 DROP VIEW comment_view;
 457
 458 CREATE VIEW comment_view AS
 459 with all_comment AS (
 460     SELECT
 461         ca.*
 462     FROM
 463         comment_aggregates_view ca
 464 )
 465 SELECT
 466     ac.*,
 467     u.id AS user_id,
 468     coalesce(cl.score, 0) AS my_vote,
 469     (
 470         SELECT
 471             cs.id::bool
 472         FROM
 473             comment_saved cs
 474         WHERE
 475             u.id = cs.user_id
 476             AND cs.comment_id = ac.id) AS saved
 477 FROM
 478     user_ u
 479     CROSS JOIN all_comment ac
 480     LEFT JOIN comment_like cl ON u.id = cl.user_id
 481         AND ac.id = cl.comment_id
 482     UNION ALL
 483     SELECT
 484         ac.*,
 485         NULL AS user_id,
 486         NULL AS my_vote,
 487         NULL AS saved
 488     FROM
 489         all_comment ac;
 490
 491 CREATE VIEW comment_mview AS
 492 with all_comment AS (
 493     SELECT
 494         ca.*
 495     FROM
 496         comment_aggregates_mview ca
 497 )
 498 SELECT
 499     ac.*,
 500     u.id AS user_id,
 501     coalesce(cl.score, 0) AS my_vote,
 502     (
 503         SELECT
 504             cs.id::bool
 505         FROM
 506             comment_saved cs
 507         WHERE
 508             u.id = cs.user_id
 509             AND cs.comment_id = ac.id) AS saved
 510 FROM
 511     user_ u
 512     CROSS JOIN all_comment ac
 513     LEFT JOIN comment_like cl ON u.id = cl.user_id
 514         AND ac.id = cl.comment_id
 515     UNION ALL
 516     SELECT
 517         ac.*,
 518         NULL AS user_id,
 519         NULL AS my_vote,
 520         NULL AS saved
 521     FROM
 522         all_comment ac;
 523
 524 CREATE VIEW reply_view AS
 525 with closereply AS (
 526     SELECT
 527         c2.id,
 528         c2.creator_id AS sender_id,
 529         c.creator_id AS recipient_id
 530     FROM
 531         comment c
 532         INNER JOIN comment c2 ON c.id = c2.parent_id
 533     WHERE
 534         c2.creator_id != c.creator_id
 535         -- Do union where post is null
 536     UNION
 537     SELECT
 538         c.id,
 539         c.creator_id AS sender_id,
 540         p.creator_id AS recipient_id
 541     FROM
 542         comment c,
 543         post p
 544     WHERE
 545         c.post_id = p.id
 546         AND c.parent_id IS NULL
 547         AND c.creator_id != p.creator_id
 548 )
 549 SELECT
 550     cv.*,
 551     closereply.recipient_id
 552 FROM
 553     comment_view cv,
 554     closereply
 555 WHERE
 556     closereply.id = cv.id;
 557
 558 -- user mention
 559 CREATE VIEW user_mention_view AS
 560 SELECT
 561     c.id,
 562     um.id AS user_mention_id,
 563     c.creator_id,
 564     c.post_id,
 565     c.parent_id,
 566     c.content,
 567     c.removed,
 568     um.read,
 569     c.published,
 570     c.updated,
 571     c.deleted,
 572     c.community_id,
 573     c.banned,
 574     c.banned_from_community,
 575     c.creator_name,
 576     c.creator_avatar,
 577     c.score,
 578     c.upvotes,
 579     c.downvotes,
 580     c.user_id,
 581     c.my_vote,
 582     c.saved,
 583     um.recipient_id
 584 FROM
 585     user_mention um,
 586     comment_view c
 587 WHERE
 588     um.comment_id = c.id;
 589
 590 -- user
 591 CREATE OR REPLACE FUNCTION refresh_user ()
 592     RETURNS TRIGGER
 593     LANGUAGE plpgsql
 594     AS $$
 595 BEGIN
 596     REFRESH MATERIALIZED VIEW CONCURRENTLY user_mview;
 597     REFRESH MATERIALIZED VIEW CONCURRENTLY comment_aggregates_mview;
 598     -- cause of bans
 599     REFRESH MATERIALIZED VIEW CONCURRENTLY post_aggregates_mview;
 600     RETURN NULL;
 601 END
 602 $$;
 603
 604 CREATE TRIGGER refresh_user
 605     AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE ON user_
 606     FOR EACH statement
 607     EXECUTE PROCEDURE refresh_user ();
 608
 609 -- post
 610 CREATE OR REPLACE FUNCTION refresh_post ()
 611     RETURNS TRIGGER
 612     LANGUAGE plpgsql
 613     AS $$
 614 BEGIN
 615     REFRESH MATERIALIZED VIEW CONCURRENTLY post_aggregates_mview;
 616     REFRESH MATERIALIZED VIEW CONCURRENTLY user_mview;
 617     RETURN NULL;
 618 END
 619 $$;
 620
 621 CREATE TRIGGER refresh_post
 622     AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE ON post
 623     FOR EACH statement
 624     EXECUTE PROCEDURE refresh_post ();
 625
 626 -- post_like
 627 CREATE OR REPLACE FUNCTION refresh_post_like ()
 628     RETURNS TRIGGER
 629     LANGUAGE plpgsql
 630     AS $$
 631 BEGIN
 632     REFRESH MATERIALIZED VIEW CONCURRENTLY post_aggregates_mview;
 633     REFRESH MATERIALIZED VIEW CONCURRENTLY user_mview;
 634     RETURN NULL;
 635 END
 636 $$;
 637
 638 CREATE TRIGGER refresh_post_like
 639     AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE ON post_like
 640     FOR EACH statement
 641     EXECUTE PROCEDURE refresh_post_like ();
 642
 643 -- community
 644 CREATE OR REPLACE FUNCTION refresh_community ()
 645     RETURNS TRIGGER
 646     LANGUAGE plpgsql
 647     AS $$
 648 BEGIN
 649     REFRESH MATERIALIZED VIEW CONCURRENTLY post_aggregates_mview;
 650     REFRESH MATERIALIZED VIEW CONCURRENTLY community_aggregates_mview;
 651     REFRESH MATERIALIZED VIEW CONCURRENTLY user_mview;
 652     RETURN NULL;
 653 END
 654 $$;
 655
 656 CREATE TRIGGER refresh_community
 657     AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE ON community
 658     FOR EACH statement
 659     EXECUTE PROCEDURE refresh_community ();
 660
 661 -- community_follower
 662 CREATE OR REPLACE FUNCTION refresh_community_follower ()
 663     RETURNS TRIGGER
 664     LANGUAGE plpgsql
 665     AS $$
 666 BEGIN
 667     REFRESH MATERIALIZED VIEW CONCURRENTLY community_aggregates_mview;
 668     REFRESH MATERIALIZED VIEW CONCURRENTLY post_aggregates_mview;
 669     RETURN NULL;
 670 END
 671 $$;
 672
 673 CREATE TRIGGER refresh_community_follower
 674     AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE ON community_follower
 675     FOR EACH statement
 676     EXECUTE PROCEDURE refresh_community_follower ();
 677
 678 -- community_user_ban
 679 CREATE OR REPLACE FUNCTION refresh_community_user_ban ()
 680     RETURNS TRIGGER
 681     LANGUAGE plpgsql
 682     AS $$
 683 BEGIN
 684     REFRESH MATERIALIZED VIEW CONCURRENTLY comment_aggregates_mview;
 685     REFRESH MATERIALIZED VIEW CONCURRENTLY post_aggregates_mview;
 686     RETURN NULL;
 687 END
 688 $$;
 689
 690 CREATE TRIGGER refresh_community_user_ban
 691     AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE ON community_user_ban
 692     FOR EACH statement
 693     EXECUTE PROCEDURE refresh_community_user_ban ();
 694
 695 -- comment
 696 CREATE OR REPLACE FUNCTION refresh_comment ()
 697     RETURNS TRIGGER
 698     LANGUAGE plpgsql
 699     AS $$
 700 BEGIN
 701     REFRESH MATERIALIZED VIEW CONCURRENTLY post_aggregates_mview;
 702     REFRESH MATERIALIZED VIEW CONCURRENTLY comment_aggregates_mview;
 703     REFRESH MATERIALIZED VIEW CONCURRENTLY community_aggregates_mview;
 704     REFRESH MATERIALIZED VIEW CONCURRENTLY user_mview;
 705     RETURN NULL;
 706 END
 707 $$;
 708
 709 CREATE TRIGGER refresh_comment
 710     AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE ON comment
 711     FOR EACH statement
 712     EXECUTE PROCEDURE refresh_comment ();
 713
 714 -- comment_like
 715 CREATE OR REPLACE FUNCTION refresh_comment_like ()
 716     RETURNS TRIGGER
 717     LANGUAGE plpgsql
 718     AS $$
 719 BEGIN
 720     REFRESH MATERIALIZED VIEW CONCURRENTLY comment_aggregates_mview;
 721     REFRESH MATERIALIZED VIEW CONCURRENTLY user_mview;
 722     RETURN NULL;
 723 END
 724 $$;
 725
 726 CREATE TRIGGER refresh_comment_like
 727     AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE ON comment_like
 728     FOR EACH statement
 729     EXECUTE PROCEDURE refresh_comment_like ();
 730