migrations/2019-12-29-164820_add_avatar/down.sql

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