migrations/2020-07-12-100442_add_post_title_to_comments_view/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.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