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

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