migrations/2020-07-08-202609_add_creator_published/up.sql

   1 DROP VIEW user_mention_view;
   2
   3 DROP VIEW reply_fast_view;
   4
   5 DROP VIEW comment_fast_view;
   6
   7 DROP VIEW comment_view;
   8
   9 DROP VIEW user_mention_fast_view;
  10
  11 DROP TABLE comment_aggregates_fast;
  12
  13 DROP VIEW comment_aggregates_view;
  14
  15 CREATE VIEW comment_aggregates_view AS
  16 SELECT
  17     ct.*,
  18     -- community details
  19     p.community_id,
  20     c.actor_id AS community_actor_id,
  21     c."local" AS community_local,
  22     c."name" AS community_name,
  23     -- creator details
  24     u.banned AS banned,
  25     coalesce(cb.id, 0)::bool AS banned_from_community,
  26     u.actor_id AS creator_actor_id,
  27     u.local AS creator_local,
  28     u.name AS creator_name,
  29     u.published AS creator_published,
  30     u.avatar AS creator_avatar,
  31     -- score details
  32     coalesce(cl.total, 0) AS score,
  33     coalesce(cl.up, 0) AS upvotes,
  34     coalesce(cl.down, 0) AS downvotes,
  35     hot_rank (coalesce(cl.total, 0), ct.published) AS hot_rank
  36 FROM
  37     comment ct
  38     LEFT JOIN post p ON ct.post_id = p.id
  39     LEFT JOIN community c ON p.community_id = c.id
  40     LEFT JOIN user_ u ON ct.creator_id = u.id
  41     LEFT JOIN community_user_ban cb ON ct.creator_id = cb.user_id
  42         AND p.id = ct.post_id
  43         AND p.community_id = cb.community_id
  44     LEFT JOIN (
  45         SELECT
  46             l.comment_id AS id,
  47             sum(l.score) AS total,
  48             count(
  49                 CASE WHEN l.score = 1 THEN
  50                     1
  51                 ELSE
  52                     NULL
  53                 END) AS up,
  54             count(
  55                 CASE WHEN l.score = - 1 THEN
  56                     1
  57                 ELSE
  58                     NULL
  59                 END) AS down
  60         FROM
  61             comment_like l
  62         GROUP BY
  63             comment_id) AS cl ON cl.id = ct.id;
  64
  65 CREATE OR REPLACE VIEW comment_view AS (
  66     SELECT
  67         cav.*,
  68         us.user_id AS user_id,
  69         us.my_vote AS my_vote,
  70         us.is_subbed::bool AS subscribed,
  71         us.is_saved::bool AS saved
  72     FROM
  73         comment_aggregates_view cav
  74     CROSS JOIN LATERAL (
  75         SELECT
  76             u.id AS user_id,
  77             coalesce(cl.score, 0) AS my_vote,
  78             coalesce(cf.id, 0) AS is_subbed,
  79             coalesce(cs.id, 0) AS is_saved
  80         FROM
  81             user_ u
  82             LEFT JOIN comment_like cl ON u.id = cl.user_id
  83                 AND cav.id = cl.comment_id
  84         LEFT JOIN comment_saved cs ON u.id = cs.user_id
  85             AND cs.comment_id = cav.id
  86     LEFT JOIN community_follower cf ON u.id = cf.user_id
  87         AND cav.community_id = cf.community_id) AS us
  88 UNION ALL
  89 SELECT
  90     cav.*,
  91     NULL AS user_id,
  92     NULL AS my_vote,
  93     NULL AS subscribed,
  94     NULL AS saved
  95 FROM
  96     comment_aggregates_view cav);
  97
  98 CREATE TABLE comment_aggregates_fast AS
  99 SELECT
 100     *
 101 FROM
 102     comment_aggregates_view;
 103
 104 ALTER TABLE comment_aggregates_fast
 105     ADD PRIMARY KEY (id);
 106
 107 CREATE VIEW comment_fast_view AS
 108 SELECT
 109     cav.*,
 110     us.user_id AS user_id,
 111     us.my_vote AS my_vote,
 112     us.is_subbed::bool AS subscribed,
 113     us.is_saved::bool AS saved
 114 FROM
 115     comment_aggregates_fast cav
 116     CROSS JOIN LATERAL (
 117         SELECT
 118             u.id AS user_id,
 119             coalesce(cl.score, 0) AS my_vote,
 120             coalesce(cf.id, 0) AS is_subbed,
 121             coalesce(cs.id, 0) AS is_saved
 122         FROM
 123             user_ u
 124             LEFT JOIN comment_like cl ON u.id = cl.user_id
 125                 AND cav.id = cl.comment_id
 126         LEFT JOIN comment_saved cs ON u.id = cs.user_id
 127             AND cs.comment_id = cav.id
 128     LEFT JOIN community_follower cf ON u.id = cf.user_id
 129         AND cav.community_id = cf.community_id) AS us
 130 UNION ALL
 131 SELECT
 132     cav.*,
 133     NULL AS user_id,
 134     NULL AS my_vote,
 135     NULL AS subscribed,
 136     NULL AS saved
 137 FROM
 138     comment_aggregates_fast cav;
 139
 140 CREATE VIEW user_mention_view AS
 141 SELECT
 142     c.id,
 143     um.id AS user_mention_id,
 144     c.creator_id,
 145     c.creator_actor_id,
 146     c.creator_local,
 147     c.post_id,
 148     c.parent_id,
 149     c.content,
 150     c.removed,
 151     um.read,
 152     c.published,
 153     c.updated,
 154     c.deleted,
 155     c.community_id,
 156     c.community_actor_id,
 157     c.community_local,
 158     c.community_name,
 159     c.banned,
 160     c.banned_from_community,
 161     c.creator_name,
 162     c.creator_avatar,
 163     c.score,
 164     c.upvotes,
 165     c.downvotes,
 166     c.hot_rank,
 167     c.user_id,
 168     c.my_vote,
 169     c.saved,
 170     um.recipient_id,
 171     (
 172         SELECT
 173             actor_id
 174         FROM
 175             user_ u
 176         WHERE
 177             u.id = um.recipient_id) AS recipient_actor_id,
 178     (
 179         SELECT
 180             local
 181         FROM
 182             user_ u
 183         WHERE
 184             u.id = um.recipient_id) AS recipient_local
 185 FROM
 186     user_mention um,
 187     comment_view c
 188 WHERE
 189     um.comment_id = c.id;
 190
 191 CREATE VIEW user_mention_fast_view AS
 192 SELECT
 193     ac.id,
 194     um.id AS user_mention_id,
 195     ac.creator_id,
 196     ac.creator_actor_id,
 197     ac.creator_local,
 198     ac.post_id,
 199     ac.parent_id,
 200     ac.content,
 201     ac.removed,
 202     um.read,
 203     ac.published,
 204     ac.updated,
 205     ac.deleted,
 206     ac.community_id,
 207     ac.community_actor_id,
 208     ac.community_local,
 209     ac.community_name,
 210     ac.banned,
 211     ac.banned_from_community,
 212     ac.creator_name,
 213     ac.creator_avatar,
 214     ac.score,
 215     ac.upvotes,
 216     ac.downvotes,
 217     ac.hot_rank,
 218     u.id AS user_id,
 219     coalesce(cl.score, 0) AS my_vote,
 220     (
 221         SELECT
 222             cs.id::bool
 223         FROM
 224             comment_saved cs
 225         WHERE
 226             u.id = cs.user_id
 227             AND cs.comment_id = ac.id) AS saved,
 228     um.recipient_id,
 229     (
 230         SELECT
 231             actor_id
 232         FROM
 233             user_ u
 234         WHERE
 235             u.id = um.recipient_id) AS recipient_actor_id,
 236     (
 237         SELECT
 238             local
 239         FROM
 240             user_ u
 241         WHERE
 242             u.id = um.recipient_id) AS recipient_local
 243 FROM
 244     user_ u
 245     CROSS JOIN (
 246         SELECT
 247             ca.*
 248         FROM
 249             comment_aggregates_fast ca) ac
 250     LEFT JOIN comment_like cl ON u.id = cl.user_id
 251         AND ac.id = cl.comment_id
 252     LEFT JOIN user_mention um ON um.comment_id = ac.id
 253 UNION ALL
 254 SELECT
 255     ac.id,
 256     um.id AS user_mention_id,
 257     ac.creator_id,
 258     ac.creator_actor_id,
 259     ac.creator_local,
 260     ac.post_id,
 261     ac.parent_id,
 262     ac.content,
 263     ac.removed,
 264     um.read,
 265     ac.published,
 266     ac.updated,
 267     ac.deleted,
 268     ac.community_id,
 269     ac.community_actor_id,
 270     ac.community_local,
 271     ac.community_name,
 272     ac.banned,
 273     ac.banned_from_community,
 274     ac.creator_name,
 275     ac.creator_avatar,
 276     ac.score,
 277     ac.upvotes,
 278     ac.downvotes,
 279     ac.hot_rank,
 280     NULL AS user_id,
 281     NULL AS my_vote,
 282     NULL AS saved,
 283     um.recipient_id,
 284     (
 285         SELECT
 286             actor_id
 287         FROM
 288             user_ u
 289         WHERE
 290             u.id = um.recipient_id) AS recipient_actor_id,
 291     (
 292         SELECT
 293             local
 294         FROM
 295             user_ u
 296         WHERE
 297             u.id = um.recipient_id) AS recipient_local
 298 FROM
 299     comment_aggregates_fast ac
 300     LEFT JOIN user_mention um ON um.comment_id = ac.id;
 301
 302 -- Do the reply_view referencing the comment_fast_view
 303 CREATE VIEW reply_fast_view AS
 304 with closereply AS (
 305     SELECT
 306         c2.id,
 307         c2.creator_id AS sender_id,
 308         c.creator_id AS recipient_id
 309     FROM
 310         comment c
 311         INNER JOIN comment c2 ON c.id = c2.parent_id
 312     WHERE
 313         c2.creator_id != c.creator_id
 314         -- Do union where post is null
 315     UNION
 316     SELECT
 317         c.id,
 318         c.creator_id AS sender_id,
 319         p.creator_id AS recipient_id
 320     FROM
 321         comment c,
 322         post p
 323     WHERE
 324         c.post_id = p.id
 325         AND c.parent_id IS NULL
 326         AND c.creator_id != p.creator_id
 327 )
 328 SELECT
 329     cv.*,
 330     closereply.recipient_id
 331 FROM
 332     comment_fast_view cv,
 333     closereply
 334 WHERE
 335     closereply.id = cv.id;
 336
 337 -- add creator_published to the post view
 338 DROP VIEW post_fast_view;
 339
 340 DROP TABLE post_aggregates_fast;
 341
 342 DROP VIEW post_view;
 343
 344 DROP VIEW post_aggregates_view;
 345
 346 CREATE VIEW post_aggregates_view AS
 347 SELECT
 348     p.*,
 349     -- creator details
 350     u.actor_id AS creator_actor_id,
 351     u."local" AS creator_local,
 352     u."name" AS creator_name,
 353     u.published AS creator_published,
 354     u.avatar AS creator_avatar,
 355     u.banned AS banned,
 356     cb.id::bool AS banned_from_community,
 357     -- community details
 358     c.actor_id AS community_actor_id,
 359     c."local" AS community_local,
 360     c."name" AS community_name,
 361     c.removed AS community_removed,
 362     c.deleted AS community_deleted,
 363     c.nsfw AS community_nsfw,
 364     -- post score data/comment count
 365     coalesce(ct.comments, 0) AS number_of_comments,
 366     coalesce(pl.score, 0) AS score,
 367     coalesce(pl.upvotes, 0) AS upvotes,
 368     coalesce(pl.downvotes, 0) AS downvotes,
 369     hot_rank (coalesce(pl.score, 0), (
 370             CASE WHEN (p.published < ('now'::timestamp - '1 month'::interval)) THEN
 371                 p.published
 372             ELSE
 373                 greatest (ct.recent_comment_time, p.published)
 374             END)) AS hot_rank,
 375     (
 376         CASE WHEN (p.published < ('now'::timestamp - '1 month'::interval)) THEN
 377             p.published
 378         ELSE
 379             greatest (ct.recent_comment_time, p.published)
 380         END) AS newest_activity_time
 381 FROM
 382     post p
 383     LEFT JOIN user_ u ON p.creator_id = u.id
 384     LEFT JOIN community_user_ban cb ON p.creator_id = cb.user_id
 385         AND p.community_id = cb.community_id
 386     LEFT JOIN community c ON p.community_id = c.id
 387     LEFT JOIN (
 388         SELECT
 389             post_id,
 390             count(*) AS comments,
 391             max(published) AS recent_comment_time
 392         FROM
 393             comment
 394         GROUP BY
 395             post_id) ct ON ct.post_id = p.id
 396     LEFT JOIN (
 397         SELECT
 398             post_id,
 399             sum(score) AS score,
 400             sum(score) FILTER (WHERE score = 1) AS upvotes,
 401             - sum(score) FILTER (WHERE score = - 1) AS downvotes
 402         FROM
 403             post_like
 404         GROUP BY
 405             post_id) pl ON pl.post_id = p.id
 406 ORDER BY
 407     p.id;
 408
 409 CREATE VIEW post_view AS
 410 SELECT
 411     pav.*,
 412     us.id AS user_id,
 413     us.user_vote AS my_vote,
 414     us.is_subbed::bool AS subscribed,
 415     us.is_read::bool AS read,
 416     us.is_saved::bool AS saved
 417 FROM
 418     post_aggregates_view pav
 419     CROSS JOIN LATERAL (
 420         SELECT
 421             u.id,
 422             coalesce(cf.community_id, 0) AS is_subbed,
 423             coalesce(pr.post_id, 0) AS is_read,
 424             coalesce(ps.post_id, 0) AS is_saved,
 425             coalesce(pl.score, 0) AS user_vote
 426         FROM
 427             user_ u
 428             LEFT JOIN community_user_ban cb ON u.id = cb.user_id
 429                 AND cb.community_id = pav.community_id
 430         LEFT JOIN community_follower cf ON u.id = cf.user_id
 431             AND cf.community_id = pav.community_id
 432     LEFT JOIN post_read pr ON u.id = pr.user_id
 433         AND pr.post_id = pav.id
 434     LEFT JOIN post_saved ps ON u.id = ps.user_id
 435         AND ps.post_id = pav.id
 436     LEFT JOIN post_like pl ON u.id = pl.user_id
 437         AND pav.id = pl.post_id) AS us
 438 UNION ALL
 439 SELECT
 440     pav.*,
 441     NULL AS user_id,
 442     NULL AS my_vote,
 443     NULL AS subscribed,
 444     NULL AS read,
 445     NULL AS saved
 446 FROM
 447     post_aggregates_view pav;
 448
 449 CREATE TABLE post_aggregates_fast AS
 450 SELECT
 451     *
 452 FROM
 453     post_aggregates_view;
 454
 455 ALTER TABLE post_aggregates_fast
 456     ADD PRIMARY KEY (id);
 457
 458 CREATE VIEW post_fast_view AS
 459 SELECT
 460     pav.*,
 461     us.id AS user_id,
 462     us.user_vote AS my_vote,
 463     us.is_subbed::bool AS subscribed,
 464     us.is_read::bool AS read,
 465     us.is_saved::bool AS saved
 466 FROM
 467     post_aggregates_fast pav
 468     CROSS JOIN LATERAL (
 469         SELECT
 470             u.id,
 471             coalesce(cf.community_id, 0) AS is_subbed,
 472             coalesce(pr.post_id, 0) AS is_read,
 473             coalesce(ps.post_id, 0) AS is_saved,
 474             coalesce(pl.score, 0) AS user_vote
 475         FROM
 476             user_ u
 477             LEFT JOIN community_user_ban cb ON u.id = cb.user_id
 478                 AND cb.community_id = pav.community_id
 479         LEFT JOIN community_follower cf ON u.id = cf.user_id
 480             AND cf.community_id = pav.community_id
 481     LEFT JOIN post_read pr ON u.id = pr.user_id
 482         AND pr.post_id = pav.id
 483     LEFT JOIN post_saved ps ON u.id = ps.user_id
 484         AND ps.post_id = pav.id
 485     LEFT JOIN post_like pl ON u.id = pl.user_id
 486         AND pav.id = pl.post_id) AS us
 487 UNION ALL
 488 SELECT
 489     pav.*,
 490     NULL AS user_id,
 491     NULL AS my_vote,
 492     NULL AS subscribed,
 493     NULL AS read,
 494     NULL AS saved
 495 FROM
 496     post_aggregates_fast pav;
 497