migrations/2020-01-13-025151_create_materialized_views/down.sql

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