migrations/2020-04-14-163701_update_views_for_activitypub/down.sql

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