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