migrations/2020-08-03-000110_add_preferred_usernames_banners_and_icons/up.sql

   1 -- This adds the following columns, as well as updates the views:
   2 --  Site icon
   3 --  Site banner
   4 --  Community icon
   5 --  Community Banner
   6 --  User Banner (User avatar is already there)
   7 --  User preferred name (already in table, needs to be added to view)
   8 -- It also adds hot_rank_active to post_view
   9 ALTER TABLE site
  10     ADD COLUMN icon text,
  11     ADD COLUMN banner text;
  12
  13 ALTER TABLE community
  14     ADD COLUMN icon text,
  15     ADD COLUMN banner text;
  16
  17 ALTER TABLE user_
  18     ADD COLUMN banner text;
  19
  20 DROP VIEW site_view;
  21
  22 CREATE VIEW site_view AS
  23 SELECT
  24     s.*,
  25     u.name AS creator_name,
  26     u.preferred_username AS creator_preferred_username,
  27     u.avatar AS creator_avatar,
  28     (
  29         SELECT
  30             count(*)
  31         FROM
  32             user_) AS number_of_users,
  33     (
  34         SELECT
  35             count(*)
  36         FROM
  37             post) AS number_of_posts,
  38     (
  39         SELECT
  40             count(*)
  41         FROM
  42             comment) AS number_of_comments,
  43     (
  44         SELECT
  45             count(*)
  46         FROM
  47             community) AS number_of_communities
  48 FROM
  49     site s
  50     LEFT JOIN user_ u ON s.creator_id = u.id;
  51
  52 -- User
  53 DROP TABLE user_fast;
  54
  55 DROP VIEW user_view;
  56
  57 CREATE VIEW user_view AS
  58 SELECT
  59     u.id,
  60     u.actor_id,
  61     u.name,
  62     u.preferred_username,
  63     u.avatar,
  64     u.banner,
  65     u.email,
  66     u.matrix_user_id,
  67     u.bio,
  68     u.local,
  69     u.admin,
  70     u.banned,
  71     u.show_avatars,
  72     u.send_notifications_to_email,
  73     u.published,
  74     coalesce(pd.posts, 0) AS number_of_posts,
  75     coalesce(pd.score, 0) AS post_score,
  76     coalesce(cd.comments, 0) AS number_of_comments,
  77     coalesce(cd.score, 0) AS comment_score
  78 FROM
  79     user_ u
  80     LEFT JOIN (
  81         SELECT
  82             p.creator_id AS creator_id,
  83             count(DISTINCT p.id) AS posts,
  84             sum(pl.score) AS score
  85         FROM
  86             post p
  87             JOIN post_like pl ON p.id = pl.post_id
  88         GROUP BY
  89             p.creator_id) pd ON u.id = pd.creator_id
  90     LEFT JOIN (
  91         SELECT
  92             c.creator_id,
  93             count(DISTINCT c.id) AS comments,
  94             sum(cl.score) AS score
  95         FROM
  96             comment c
  97             JOIN comment_like cl ON c.id = cl.comment_id
  98         GROUP BY
  99             c.creator_id) cd ON u.id = cd.creator_id;
 100
 101 CREATE TABLE user_fast AS
 102 SELECT
 103     *
 104 FROM
 105     user_view;
 106
 107 ALTER TABLE user_fast
 108     ADD PRIMARY KEY (id);
 109
 110 -- private message
 111 DROP VIEW private_message_view;
 112
 113 CREATE VIEW private_message_view AS
 114 SELECT
 115     pm.*,
 116     u.name AS creator_name,
 117     u.preferred_username AS creator_preferred_username,
 118     u.avatar AS creator_avatar,
 119     u.actor_id AS creator_actor_id,
 120     u.local AS creator_local,
 121     u2.name AS recipient_name,
 122     u2.preferred_username AS recipient_preferred_username,
 123     u2.avatar AS recipient_avatar,
 124     u2.actor_id AS recipient_actor_id,
 125     u2.local AS recipient_local
 126 FROM
 127     private_message pm
 128     INNER JOIN user_ u ON u.id = pm.creator_id
 129     INNER JOIN user_ u2 ON u2.id = pm.recipient_id;
 130
 131 -- Post fast
 132 DROP VIEW post_fast_view;
 133
 134 DROP TABLE post_aggregates_fast;
 135
 136 DROP VIEW post_view;
 137
 138 DROP VIEW post_aggregates_view;
 139
 140 CREATE VIEW post_aggregates_view AS
 141 SELECT
 142     p.*,
 143     -- creator details
 144     u.actor_id AS creator_actor_id,
 145     u."local" AS creator_local,
 146     u."name" AS creator_name,
 147     u."preferred_username" AS creator_preferred_username,
 148     u.published AS creator_published,
 149     u.avatar AS creator_avatar,
 150     u.banned AS banned,
 151     cb.id::bool AS banned_from_community,
 152     -- community details
 153     c.actor_id AS community_actor_id,
 154     c."local" AS community_local,
 155     c."name" AS community_name,
 156     c.icon AS community_icon,
 157     c.removed AS community_removed,
 158     c.deleted AS community_deleted,
 159     c.nsfw AS community_nsfw,
 160     -- post score data/comment count
 161     coalesce(ct.comments, 0) AS number_of_comments,
 162     coalesce(pl.score, 0) AS score,
 163     coalesce(pl.upvotes, 0) AS upvotes,
 164     coalesce(pl.downvotes, 0) AS downvotes,
 165     hot_rank (coalesce(pl.score, 1), p.published) AS hot_rank,
 166     hot_rank (coalesce(pl.score, 1), greatest (ct.recent_comment_time, p.published)) AS hot_rank_active,
 167     greatest (ct.recent_comment_time, p.published) AS newest_activity_time
 168 FROM
 169     post p
 170     LEFT JOIN user_ u ON p.creator_id = u.id
 171     LEFT JOIN community_user_ban cb ON p.creator_id = cb.user_id
 172         AND p.community_id = cb.community_id
 173     LEFT JOIN community c ON p.community_id = c.id
 174     LEFT JOIN (
 175         SELECT
 176             post_id,
 177             count(*) AS comments,
 178             max(published) AS recent_comment_time
 179         FROM
 180             comment
 181         GROUP BY
 182             post_id) ct ON ct.post_id = p.id
 183     LEFT JOIN (
 184         SELECT
 185             post_id,
 186             sum(score) AS score,
 187             sum(score) FILTER (WHERE score = 1) AS upvotes,
 188             - sum(score) FILTER (WHERE score = - 1) AS downvotes
 189         FROM
 190             post_like
 191         GROUP BY
 192             post_id) pl ON pl.post_id = p.id
 193 ORDER BY
 194     p.id;
 195
 196 CREATE VIEW post_view AS
 197 SELECT
 198     pav.*,
 199     us.id AS user_id,
 200     us.user_vote AS my_vote,
 201     us.is_subbed::bool AS subscribed,
 202     us.is_read::bool AS read,
 203     us.is_saved::bool AS saved
 204 FROM
 205     post_aggregates_view pav
 206     CROSS JOIN LATERAL (
 207         SELECT
 208             u.id,
 209             coalesce(cf.community_id, 0) AS is_subbed,
 210             coalesce(pr.post_id, 0) AS is_read,
 211             coalesce(ps.post_id, 0) AS is_saved,
 212             coalesce(pl.score, 0) AS user_vote
 213         FROM
 214             user_ u
 215             LEFT JOIN community_user_ban cb ON u.id = cb.user_id
 216                 AND cb.community_id = pav.community_id
 217         LEFT JOIN community_follower cf ON u.id = cf.user_id
 218             AND cf.community_id = pav.community_id
 219     LEFT JOIN post_read pr ON u.id = pr.user_id
 220         AND pr.post_id = pav.id
 221     LEFT JOIN post_saved ps ON u.id = ps.user_id
 222         AND ps.post_id = pav.id
 223     LEFT JOIN post_like pl ON u.id = pl.user_id
 224         AND pav.id = pl.post_id) AS us
 225 UNION ALL
 226 SELECT
 227     pav.*,
 228     NULL AS user_id,
 229     NULL AS my_vote,
 230     NULL AS subscribed,
 231     NULL AS read,
 232     NULL AS saved
 233 FROM
 234     post_aggregates_view pav;
 235
 236 CREATE TABLE post_aggregates_fast AS
 237 SELECT
 238     *
 239 FROM
 240     post_aggregates_view;
 241
 242 ALTER TABLE post_aggregates_fast
 243     ADD PRIMARY KEY (id);
 244
 245 -- For the hot rank resorting
 246 CREATE INDEX idx_post_aggregates_fast_hot_rank_published ON post_aggregates_fast (hot_rank DESC, published DESC);
 247
 248 CREATE INDEX idx_post_aggregates_fast_hot_rank_active_published ON post_aggregates_fast (hot_rank_active DESC, published DESC);
 249
 250 CREATE VIEW post_fast_view AS
 251 SELECT
 252     pav.*,
 253     us.id AS user_id,
 254     us.user_vote AS my_vote,
 255     us.is_subbed::bool AS subscribed,
 256     us.is_read::bool AS read,
 257     us.is_saved::bool AS saved
 258 FROM
 259     post_aggregates_fast pav
 260     CROSS JOIN LATERAL (
 261         SELECT
 262             u.id,
 263             coalesce(cf.community_id, 0) AS is_subbed,
 264             coalesce(pr.post_id, 0) AS is_read,
 265             coalesce(ps.post_id, 0) AS is_saved,
 266             coalesce(pl.score, 0) AS user_vote
 267         FROM
 268             user_ u
 269             LEFT JOIN community_user_ban cb ON u.id = cb.user_id
 270                 AND cb.community_id = pav.community_id
 271         LEFT JOIN community_follower cf ON u.id = cf.user_id
 272             AND cf.community_id = pav.community_id
 273     LEFT JOIN post_read pr ON u.id = pr.user_id
 274         AND pr.post_id = pav.id
 275     LEFT JOIN post_saved ps ON u.id = ps.user_id
 276         AND ps.post_id = pav.id
 277     LEFT JOIN post_like pl ON u.id = pl.user_id
 278         AND pav.id = pl.post_id) AS us
 279 UNION ALL
 280 SELECT
 281     pav.*,
 282     NULL AS user_id,
 283     NULL AS my_vote,
 284     NULL AS subscribed,
 285     NULL AS read,
 286     NULL AS saved
 287 FROM
 288     post_aggregates_fast pav;
 289
 290 -- Community
 291 DROP VIEW community_moderator_view;
 292
 293 DROP VIEW community_follower_view;
 294
 295 DROP VIEW community_user_ban_view;
 296
 297 DROP VIEW community_view;
 298
 299 DROP VIEW community_aggregates_view;
 300
 301 DROP VIEW community_fast_view;
 302
 303 DROP TABLE community_aggregates_fast;
 304
 305 CREATE VIEW community_aggregates_view AS
 306 SELECT
 307     c.id,
 308     c.name,
 309     c.title,
 310     c.icon,
 311     c.banner,
 312     c.description,
 313     c.category_id,
 314     c.creator_id,
 315     c.removed,
 316     c.published,
 317     c.updated,
 318     c.deleted,
 319     c.nsfw,
 320     c.actor_id,
 321     c.local,
 322     c.last_refreshed_at,
 323     u.actor_id AS creator_actor_id,
 324     u.local AS creator_local,
 325     u.name AS creator_name,
 326     u.preferred_username AS creator_preferred_username,
 327     u.avatar AS creator_avatar,
 328     cat.name AS category_name,
 329     coalesce(cf.subs, 0) AS number_of_subscribers,
 330     coalesce(cd.posts, 0) AS number_of_posts,
 331     coalesce(cd.comments, 0) AS number_of_comments,
 332     hot_rank (cf.subs, c.published) AS hot_rank
 333 FROM
 334     community c
 335     LEFT JOIN user_ u ON c.creator_id = u.id
 336     LEFT JOIN category cat ON c.category_id = cat.id
 337     LEFT JOIN (
 338         SELECT
 339             p.community_id,
 340             count(DISTINCT p.id) AS posts,
 341             count(DISTINCT ct.id) AS comments
 342         FROM
 343             post p
 344             JOIN comment ct ON p.id = ct.post_id
 345         GROUP BY
 346             p.community_id) cd ON cd.community_id = c.id
 347     LEFT JOIN (
 348         SELECT
 349             community_id,
 350             count(*) AS subs
 351         FROM
 352             community_follower
 353         GROUP BY
 354             community_id) cf ON cf.community_id = c.id;
 355
 356 CREATE VIEW community_view AS
 357 SELECT
 358     cv.*,
 359     us.user AS user_id,
 360     us.is_subbed::bool AS subscribed
 361 FROM
 362     community_aggregates_view cv
 363     CROSS JOIN LATERAL (
 364         SELECT
 365             u.id AS user,
 366             coalesce(cf.community_id, 0) AS is_subbed
 367         FROM
 368             user_ u
 369             LEFT JOIN community_follower cf ON u.id = cf.user_id
 370                 AND cf.community_id = cv.id) AS us
 371 UNION ALL
 372 SELECT
 373     cv.*,
 374     NULL AS user_id,
 375     NULL AS subscribed
 376 FROM
 377     community_aggregates_view cv;
 378
 379 CREATE VIEW community_moderator_view AS
 380 SELECT
 381     cm.*,
 382     u.actor_id AS user_actor_id,
 383     u.local AS user_local,
 384     u.name AS user_name,
 385     u.preferred_username AS user_preferred_username,
 386     u.avatar AS avatar,
 387     c.actor_id AS community_actor_id,
 388     c.local AS community_local,
 389     c.name AS community_name,
 390     c.icon AS community_icon
 391 FROM
 392     community_moderator cm
 393     LEFT JOIN user_ u ON cm.user_id = u.id
 394     LEFT JOIN community c ON cm.community_id = c.id;
 395
 396 CREATE VIEW community_follower_view AS
 397 SELECT
 398     cf.*,
 399     u.actor_id AS user_actor_id,
 400     u.local AS user_local,
 401     u.name AS user_name,
 402     u.preferred_username AS user_preferred_username,
 403     u.avatar AS avatar,
 404     c.actor_id AS community_actor_id,
 405     c.local AS community_local,
 406     c.name AS community_name,
 407     c.icon AS community_icon
 408 FROM
 409     community_follower cf
 410     LEFT JOIN user_ u ON cf.user_id = u.id
 411     LEFT JOIN community c ON cf.community_id = c.id;
 412
 413 CREATE VIEW community_user_ban_view AS
 414 SELECT
 415     cb.*,
 416     u.actor_id AS user_actor_id,
 417     u.local AS user_local,
 418     u.name AS user_name,
 419     u.preferred_username AS user_preferred_username,
 420     u.avatar AS avatar,
 421     c.actor_id AS community_actor_id,
 422     c.local AS community_local,
 423     c.name AS community_name,
 424     c.icon AS community_icon
 425 FROM
 426     community_user_ban cb
 427     LEFT JOIN user_ u ON cb.user_id = u.id
 428     LEFT JOIN community c ON cb.community_id = c.id;
 429
 430 -- The community fast table
 431 CREATE TABLE community_aggregates_fast AS
 432 SELECT
 433     *
 434 FROM
 435     community_aggregates_view;
 436
 437 ALTER TABLE community_aggregates_fast
 438     ADD PRIMARY KEY (id);
 439
 440 CREATE VIEW community_fast_view AS
 441 SELECT
 442     ac.*,
 443     u.id AS user_id,
 444     (
 445         SELECT
 446             cf.id::boolean
 447         FROM
 448             community_follower cf
 449         WHERE
 450             u.id = cf.user_id
 451             AND ac.id = cf.community_id) AS subscribed
 452 FROM
 453     user_ u
 454     CROSS JOIN (
 455         SELECT
 456             ca.*
 457         FROM
 458             community_aggregates_fast ca) ac
 459 UNION ALL
 460 SELECT
 461     caf.*,
 462     NULL AS user_id,
 463     NULL AS subscribed
 464 FROM
 465     community_aggregates_fast caf;
 466
 467 -- Comments, mentions, replies
 468 DROP VIEW user_mention_view;
 469
 470 DROP VIEW reply_fast_view;
 471
 472 DROP VIEW comment_fast_view;
 473
 474 DROP VIEW comment_view;
 475
 476 DROP VIEW user_mention_fast_view;
 477
 478 DROP TABLE comment_aggregates_fast;
 479
 480 DROP VIEW comment_aggregates_view;
 481
 482 CREATE VIEW comment_aggregates_view AS
 483 SELECT
 484     ct.*,
 485     -- post details
 486     p."name" AS post_name,
 487     p.community_id,
 488     -- community details
 489     c.actor_id AS community_actor_id,
 490     c."local" AS community_local,
 491     c."name" AS community_name,
 492     c.icon AS community_icon,
 493     -- creator details
 494     u.banned AS banned,
 495     coalesce(cb.id, 0)::bool AS banned_from_community,
 496     u.actor_id AS creator_actor_id,
 497     u.local AS creator_local,
 498     u.name AS creator_name,
 499     u.preferred_username AS creator_preferred_username,
 500     u.published AS creator_published,
 501     u.avatar AS creator_avatar,
 502     -- score details
 503     coalesce(cl.total, 0) AS score,
 504     coalesce(cl.up, 0) AS upvotes,
 505     coalesce(cl.down, 0) AS downvotes,
 506     hot_rank (coalesce(cl.total, 1), p.published) AS hot_rank,
 507     hot_rank (coalesce(cl.total, 1), ct.published) AS hot_rank_active
 508 FROM
 509     comment ct
 510     LEFT JOIN post p ON ct.post_id = p.id
 511     LEFT JOIN community c ON p.community_id = c.id
 512     LEFT JOIN user_ u ON ct.creator_id = u.id
 513     LEFT JOIN community_user_ban cb ON ct.creator_id = cb.user_id
 514         AND p.id = ct.post_id
 515         AND p.community_id = cb.community_id
 516     LEFT JOIN (
 517         SELECT
 518             l.comment_id AS id,
 519             sum(l.score) AS total,
 520             count(
 521                 CASE WHEN l.score = 1 THEN
 522                     1
 523                 ELSE
 524                     NULL
 525                 END) AS up,
 526             count(
 527                 CASE WHEN l.score = - 1 THEN
 528                     1
 529                 ELSE
 530                     NULL
 531                 END) AS down
 532         FROM
 533             comment_like l
 534         GROUP BY
 535             comment_id) AS cl ON cl.id = ct.id;
 536
 537 CREATE OR REPLACE VIEW comment_view AS (
 538     SELECT
 539         cav.*,
 540         us.user_id AS user_id,
 541         us.my_vote AS my_vote,
 542         us.is_subbed::bool AS subscribed,
 543         us.is_saved::bool AS saved
 544     FROM
 545         comment_aggregates_view cav
 546     CROSS JOIN LATERAL (
 547         SELECT
 548             u.id AS user_id,
 549             coalesce(cl.score, 0) AS my_vote,
 550             coalesce(cf.id, 0) AS is_subbed,
 551             coalesce(cs.id, 0) AS is_saved
 552         FROM
 553             user_ u
 554             LEFT JOIN comment_like cl ON u.id = cl.user_id
 555                 AND cav.id = cl.comment_id
 556         LEFT JOIN comment_saved cs ON u.id = cs.user_id
 557             AND cs.comment_id = cav.id
 558     LEFT JOIN community_follower cf ON u.id = cf.user_id
 559         AND cav.community_id = cf.community_id) AS us
 560 UNION ALL
 561 SELECT
 562     cav.*,
 563     NULL AS user_id,
 564     NULL AS my_vote,
 565     NULL AS subscribed,
 566     NULL AS saved
 567 FROM
 568     comment_aggregates_view cav);
 569
 570 CREATE TABLE comment_aggregates_fast AS
 571 SELECT
 572     *
 573 FROM
 574     comment_aggregates_view;
 575
 576 ALTER TABLE comment_aggregates_fast
 577     ADD PRIMARY KEY (id);
 578
 579 CREATE VIEW comment_fast_view AS
 580 SELECT
 581     cav.*,
 582     us.user_id AS user_id,
 583     us.my_vote AS my_vote,
 584     us.is_subbed::bool AS subscribed,
 585     us.is_saved::bool AS saved
 586 FROM
 587     comment_aggregates_fast cav
 588     CROSS JOIN LATERAL (
 589         SELECT
 590             u.id AS user_id,
 591             coalesce(cl.score, 0) AS my_vote,
 592             coalesce(cf.id, 0) AS is_subbed,
 593             coalesce(cs.id, 0) AS is_saved
 594         FROM
 595             user_ u
 596             LEFT JOIN comment_like cl ON u.id = cl.user_id
 597                 AND cav.id = cl.comment_id
 598         LEFT JOIN comment_saved cs ON u.id = cs.user_id
 599             AND cs.comment_id = cav.id
 600     LEFT JOIN community_follower cf ON u.id = cf.user_id
 601         AND cav.community_id = cf.community_id) AS us
 602 UNION ALL
 603 SELECT
 604     cav.*,
 605     NULL AS user_id,
 606     NULL AS my_vote,
 607     NULL AS subscribed,
 608     NULL AS saved
 609 FROM
 610     comment_aggregates_fast cav;
 611
 612 CREATE VIEW user_mention_view AS
 613 SELECT
 614     c.id,
 615     um.id AS user_mention_id,
 616     c.creator_id,
 617     c.creator_actor_id,
 618     c.creator_local,
 619     c.post_id,
 620     c.post_name,
 621     c.parent_id,
 622     c.content,
 623     c.removed,
 624     um.read,
 625     c.published,
 626     c.updated,
 627     c.deleted,
 628     c.community_id,
 629     c.community_actor_id,
 630     c.community_local,
 631     c.community_name,
 632     c.community_icon,
 633     c.banned,
 634     c.banned_from_community,
 635     c.creator_name,
 636     c.creator_preferred_username,
 637     c.creator_avatar,
 638     c.score,
 639     c.upvotes,
 640     c.downvotes,
 641     c.hot_rank,
 642     c.hot_rank_active,
 643     c.user_id,
 644     c.my_vote,
 645     c.saved,
 646     um.recipient_id,
 647     (
 648         SELECT
 649             actor_id
 650         FROM
 651             user_ u
 652         WHERE
 653             u.id = um.recipient_id) AS recipient_actor_id,
 654     (
 655         SELECT
 656             local
 657         FROM
 658             user_ u
 659         WHERE
 660             u.id = um.recipient_id) AS recipient_local
 661 FROM
 662     user_mention um,
 663     comment_view c
 664 WHERE
 665     um.comment_id = c.id;
 666
 667 CREATE VIEW user_mention_fast_view AS
 668 SELECT
 669     ac.id,
 670     um.id AS user_mention_id,
 671     ac.creator_id,
 672     ac.creator_actor_id,
 673     ac.creator_local,
 674     ac.post_id,
 675     ac.post_name,
 676     ac.parent_id,
 677     ac.content,
 678     ac.removed,
 679     um.read,
 680     ac.published,
 681     ac.updated,
 682     ac.deleted,
 683     ac.community_id,
 684     ac.community_actor_id,
 685     ac.community_local,
 686     ac.community_name,
 687     ac.community_icon,
 688     ac.banned,
 689     ac.banned_from_community,
 690     ac.creator_name,
 691     ac.creator_preferred_username,
 692     ac.creator_avatar,
 693     ac.score,
 694     ac.upvotes,
 695     ac.downvotes,
 696     ac.hot_rank,
 697     ac.hot_rank_active,
 698     u.id AS user_id,
 699     coalesce(cl.score, 0) AS my_vote,
 700     (
 701         SELECT
 702             cs.id::bool
 703         FROM
 704             comment_saved cs
 705         WHERE
 706             u.id = cs.user_id
 707             AND cs.comment_id = ac.id) AS saved,
 708     um.recipient_id,
 709     (
 710         SELECT
 711             actor_id
 712         FROM
 713             user_ u
 714         WHERE
 715             u.id = um.recipient_id) AS recipient_actor_id,
 716     (
 717         SELECT
 718             local
 719         FROM
 720             user_ u
 721         WHERE
 722             u.id = um.recipient_id) AS recipient_local
 723 FROM
 724     user_ u
 725     CROSS JOIN (
 726         SELECT
 727             ca.*
 728         FROM
 729             comment_aggregates_fast ca) ac
 730     LEFT JOIN comment_like cl ON u.id = cl.user_id
 731         AND ac.id = cl.comment_id
 732     LEFT JOIN user_mention um ON um.comment_id = ac.id
 733 UNION ALL
 734 SELECT
 735     ac.id,
 736     um.id AS user_mention_id,
 737     ac.creator_id,
 738     ac.creator_actor_id,
 739     ac.creator_local,
 740     ac.post_id,
 741     ac.post_name,
 742     ac.parent_id,
 743     ac.content,
 744     ac.removed,
 745     um.read,
 746     ac.published,
 747     ac.updated,
 748     ac.deleted,
 749     ac.community_id,
 750     ac.community_actor_id,
 751     ac.community_local,
 752     ac.community_name,
 753     ac.community_icon,
 754     ac.banned,
 755     ac.banned_from_community,
 756     ac.creator_name,
 757     ac.creator_preferred_username,
 758     ac.creator_avatar,
 759     ac.score,
 760     ac.upvotes,
 761     ac.downvotes,
 762     ac.hot_rank,
 763     ac.hot_rank_active,
 764     NULL AS user_id,
 765     NULL AS my_vote,
 766     NULL AS saved,
 767     um.recipient_id,
 768     (
 769         SELECT
 770             actor_id
 771         FROM
 772             user_ u
 773         WHERE
 774             u.id = um.recipient_id) AS recipient_actor_id,
 775     (
 776         SELECT
 777             local
 778         FROM
 779             user_ u
 780         WHERE
 781             u.id = um.recipient_id) AS recipient_local
 782 FROM
 783     comment_aggregates_fast ac
 784     LEFT JOIN user_mention um ON um.comment_id = ac.id;
 785
 786 -- Do the reply_view referencing the comment_fast_view
 787 CREATE VIEW reply_fast_view AS
 788 with closereply AS (
 789     SELECT
 790         c2.id,
 791         c2.creator_id AS sender_id,
 792         c.creator_id AS recipient_id
 793     FROM
 794         comment c
 795         INNER JOIN comment c2 ON c.id = c2.parent_id
 796     WHERE
 797         c2.creator_id != c.creator_id
 798         -- Do union where post is null
 799     UNION
 800     SELECT
 801         c.id,
 802         c.creator_id AS sender_id,
 803         p.creator_id AS recipient_id
 804     FROM
 805         comment c,
 806         post p
 807     WHERE
 808         c.post_id = p.id
 809         AND c.parent_id IS NULL
 810         AND c.creator_id != p.creator_id
 811 )
 812 SELECT
 813     cv.*,
 814     closereply.recipient_id
 815 FROM
 816     comment_fast_view cv,
 817     closereply
 818 WHERE
 819     closereply.id = cv.id;
 820
 821 -- Adding hot rank active to the triggers
 822 CREATE OR REPLACE FUNCTION refresh_post ()
 823     RETURNS TRIGGER
 824     LANGUAGE plpgsql
 825     AS $$
 826 BEGIN
 827     IF (TG_OP = 'DELETE') THEN
 828         DELETE FROM post_aggregates_fast
 829         WHERE id = OLD.id;
 830         -- Update community number of posts
 831         UPDATE
 832             community_aggregates_fast
 833         SET
 834             number_of_posts = number_of_posts - 1
 835         WHERE
 836             id = OLD.community_id;
 837     ELSIF (TG_OP = 'UPDATE') THEN
 838         DELETE FROM post_aggregates_fast
 839         WHERE id = OLD.id;
 840         INSERT INTO post_aggregates_fast
 841         SELECT
 842             *
 843         FROM
 844             post_aggregates_view
 845         WHERE
 846             id = NEW.id;
 847     ELSIF (TG_OP = 'INSERT') THEN
 848         INSERT INTO post_aggregates_fast
 849         SELECT
 850             *
 851         FROM
 852             post_aggregates_view
 853         WHERE
 854             id = NEW.id;
 855         -- Update that users number of posts, post score
 856         DELETE FROM user_fast
 857         WHERE id = NEW.creator_id;
 858         INSERT INTO user_fast
 859         SELECT
 860             *
 861         FROM
 862             user_view
 863         WHERE
 864             id = NEW.creator_id;
 865         -- Update community number of posts
 866         UPDATE
 867             community_aggregates_fast
 868         SET
 869             number_of_posts = number_of_posts + 1
 870         WHERE
 871             id = NEW.community_id;
 872         -- Update the hot rank on the post table
 873         -- TODO this might not correctly update it, using a 1 week interval
 874         UPDATE
 875             post_aggregates_fast AS paf
 876         SET
 877             hot_rank = pav.hot_rank,
 878             hot_rank_active = pav.hot_rank_active
 879         FROM
 880             post_aggregates_view AS pav
 881         WHERE
 882             paf.id = pav.id
 883             AND (pav.published > ('now'::timestamp - '1 week'::interval));
 884     END IF;
 885     RETURN NULL;
 886 END
 887 $$;
 888
 889 CREATE OR REPLACE FUNCTION refresh_comment ()
 890     RETURNS TRIGGER
 891     LANGUAGE plpgsql
 892     AS $$
 893 BEGIN
 894     IF (TG_OP = 'DELETE') THEN
 895         DELETE FROM comment_aggregates_fast
 896         WHERE id = OLD.id;
 897         -- Update community number of comments
 898         UPDATE
 899             community_aggregates_fast AS caf
 900         SET
 901             number_of_comments = number_of_comments - 1
 902         FROM
 903             post AS p
 904         WHERE
 905             caf.id = p.community_id
 906             AND p.id = OLD.post_id;
 907     ELSIF (TG_OP = 'UPDATE') THEN
 908         DELETE FROM comment_aggregates_fast
 909         WHERE id = OLD.id;
 910         INSERT INTO comment_aggregates_fast
 911         SELECT
 912             *
 913         FROM
 914             comment_aggregates_view
 915         WHERE
 916             id = NEW.id;
 917     ELSIF (TG_OP = 'INSERT') THEN
 918         INSERT INTO comment_aggregates_fast
 919         SELECT
 920             *
 921         FROM
 922             comment_aggregates_view
 923         WHERE
 924             id = NEW.id;
 925         -- Update user view due to comment count
 926         UPDATE
 927             user_fast
 928         SET
 929             number_of_comments = number_of_comments + 1
 930         WHERE
 931             id = NEW.creator_id;
 932         -- Update post view due to comment count, new comment activity time, but only on new posts
 933         -- TODO this could be done more efficiently
 934         DELETE FROM post_aggregates_fast
 935         WHERE id = NEW.post_id;
 936         INSERT INTO post_aggregates_fast
 937         SELECT
 938             *
 939         FROM
 940             post_aggregates_view
 941         WHERE
 942             id = NEW.post_id;
 943         -- Update the comment hot_ranks as of last week
 944         UPDATE
 945             comment_aggregates_fast AS caf
 946         SET
 947             hot_rank = cav.hot_rank,
 948             hot_rank_active = cav.hot_rank_active
 949         FROM
 950             comment_aggregates_view AS cav
 951         WHERE
 952             caf.id = cav.id
 953             AND (cav.published > ('now'::timestamp - '1 week'::interval));
 954         -- Update the post ranks
 955         UPDATE
 956             post_aggregates_fast AS paf
 957         SET
 958             hot_rank = pav.hot_rank,
 959             hot_rank_active = pav.hot_rank_active
 960         FROM
 961             post_aggregates_view AS pav
 962         WHERE
 963             paf.id = pav.id
 964             AND (pav.published > ('now'::timestamp - '1 week'::interval));
 965         -- Force the hot rank active as zero on 2 day-older posts (necro-bump)
 966         UPDATE
 967             post_aggregates_fast AS paf
 968         SET
 969             hot_rank_active = 0
 970         WHERE
 971             paf.id = NEW.post_id
 972             AND (paf.published < ('now'::timestamp - '2 days'::interval));
 973         -- Update community number of comments
 974         UPDATE
 975             community_aggregates_fast AS caf
 976         SET
 977             number_of_comments = number_of_comments + 1
 978         FROM
 979             post AS p
 980         WHERE
 981             caf.id = p.community_id
 982             AND p.id = NEW.post_id;
 983     END IF;
 984     RETURN NULL;
 985 END
 986 $$;
 987