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

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