migrations/2020-02-06-165953_change_post_title_length/up.sql

   1 -- Drop the dependent views
   2 DROP VIEW post_view;
   3
   4 DROP VIEW post_mview;
   5
   6 DROP MATERIALIZED VIEW post_aggregates_mview;
   7
   8 DROP VIEW post_aggregates_view;
   9
  10 DROP VIEW mod_remove_post_view;
  11
  12 DROP VIEW mod_sticky_post_view;
  13
  14 DROP VIEW mod_lock_post_view;
  15
  16 DROP VIEW mod_remove_comment_view;
  17
  18 -- Add the extra post limit
  19 ALTER TABLE post
  20     ALTER COLUMN name TYPE varchar(200);
  21
  22 -- regen post view
  23 CREATE VIEW post_aggregates_view AS
  24 SELECT
  25     p.*,
  26     (
  27         SELECT
  28             u.banned
  29         FROM
  30             user_ u
  31         WHERE
  32             p.creator_id = u.id) AS banned,
  33     (
  34         SELECT
  35             cb.id::bool
  36         FROM
  37             community_user_ban cb
  38         WHERE
  39             p.creator_id = cb.user_id
  40             AND p.community_id = cb.community_id) AS banned_from_community,
  41     (
  42         SELECT
  43             name
  44         FROM
  45             user_
  46         WHERE
  47             p.creator_id = user_.id) AS creator_name,
  48     (
  49         SELECT
  50             avatar
  51         FROM
  52             user_
  53         WHERE
  54             p.creator_id = user_.id) AS creator_avatar,
  55     (
  56         SELECT
  57             name
  58         FROM
  59             community
  60         WHERE
  61             p.community_id = community.id) AS community_name,
  62     (
  63         SELECT
  64             removed
  65         FROM
  66             community c
  67         WHERE
  68             p.community_id = c.id) AS community_removed,
  69     (
  70         SELECT
  71             deleted
  72         FROM
  73             community c
  74         WHERE
  75             p.community_id = c.id) AS community_deleted,
  76     (
  77         SELECT
  78             nsfw
  79         FROM
  80             community c
  81         WHERE
  82             p.community_id = c.id) AS community_nsfw,
  83     (
  84         SELECT
  85             count(*)
  86         FROM
  87             comment
  88         WHERE
  89             comment.post_id = p.id) AS number_of_comments,
  90     coalesce(sum(pl.score), 0) AS score,
  91     count(
  92         CASE WHEN pl.score = 1 THEN
  93             1
  94         ELSE
  95             NULL
  96         END) AS upvotes,
  97     count(
  98         CASE WHEN pl.score = - 1 THEN
  99             1
 100         ELSE
 101             NULL
 102         END) AS downvotes,
 103     hot_rank (coalesce(sum(pl.score), 0), p.published) AS hot_rank
 104 FROM
 105     post p
 106     LEFT JOIN post_like pl ON p.id = pl.post_id
 107 GROUP BY
 108     p.id;
 109
 110 CREATE MATERIALIZED VIEW post_aggregates_mview AS
 111 SELECT
 112     *
 113 FROM
 114     post_aggregates_view;
 115
 116 CREATE UNIQUE INDEX idx_post_aggregates_mview_id ON post_aggregates_mview (id);
 117
 118 CREATE VIEW post_view AS
 119 with all_post AS (
 120     SELECT
 121         pa.*
 122     FROM
 123         post_aggregates_view pa
 124 )
 125 SELECT
 126     ap.*,
 127     u.id AS user_id,
 128     coalesce(pl.score, 0) AS my_vote,
 129     (
 130         SELECT
 131             cf.id::bool
 132         FROM
 133             community_follower cf
 134         WHERE
 135             u.id = cf.user_id
 136             AND cf.community_id = ap.community_id) AS subscribed,
 137     (
 138         SELECT
 139             pr.id::bool
 140         FROM
 141             post_read pr
 142         WHERE
 143             u.id = pr.user_id
 144             AND pr.post_id = ap.id) AS read,
 145     (
 146         SELECT
 147             ps.id::bool
 148         FROM
 149             post_saved ps
 150         WHERE
 151             u.id = ps.user_id
 152             AND ps.post_id = ap.id) AS saved
 153 FROM
 154     user_ u
 155     CROSS JOIN all_post ap
 156     LEFT JOIN post_like pl ON u.id = pl.user_id
 157         AND ap.id = pl.post_id
 158     UNION ALL
 159     SELECT
 160         ap.*,
 161         NULL AS user_id,
 162         NULL AS my_vote,
 163         NULL AS subscribed,
 164         NULL AS read,
 165         NULL AS saved
 166     FROM
 167         all_post ap;
 168
 169 CREATE VIEW post_mview AS
 170 with all_post AS (
 171     SELECT
 172         pa.*
 173     FROM
 174         post_aggregates_mview pa
 175 )
 176 SELECT
 177     ap.*,
 178     u.id AS user_id,
 179     coalesce(pl.score, 0) AS my_vote,
 180     (
 181         SELECT
 182             cf.id::bool
 183         FROM
 184             community_follower cf
 185         WHERE
 186             u.id = cf.user_id
 187             AND cf.community_id = ap.community_id) AS subscribed,
 188     (
 189         SELECT
 190             pr.id::bool
 191         FROM
 192             post_read pr
 193         WHERE
 194             u.id = pr.user_id
 195             AND pr.post_id = ap.id) AS read,
 196     (
 197         SELECT
 198             ps.id::bool
 199         FROM
 200             post_saved ps
 201         WHERE
 202             u.id = ps.user_id
 203             AND ps.post_id = ap.id) AS saved
 204 FROM
 205     user_ u
 206     CROSS JOIN all_post ap
 207     LEFT JOIN post_like pl ON u.id = pl.user_id
 208         AND ap.id = pl.post_id
 209     UNION ALL
 210     SELECT
 211         ap.*,
 212         NULL AS user_id,
 213         NULL AS my_vote,
 214         NULL AS subscribed,
 215         NULL AS read,
 216         NULL AS saved
 217     FROM
 218         all_post ap;
 219
 220 -- The mod views
 221 CREATE VIEW mod_remove_post_view AS
 222 SELECT
 223     mrp.*,
 224     (
 225         SELECT
 226             name
 227         FROM
 228             user_ u
 229         WHERE
 230             mrp.mod_user_id = u.id) AS mod_user_name,
 231     (
 232         SELECT
 233             name
 234         FROM
 235             post p
 236         WHERE
 237             mrp.post_id = p.id) AS post_name,
 238     (
 239         SELECT
 240             c.id
 241         FROM
 242             post p,
 243             community c
 244         WHERE
 245             mrp.post_id = p.id
 246             AND p.community_id = c.id) AS community_id,
 247     (
 248         SELECT
 249             c.name
 250         FROM
 251             post p,
 252             community c
 253         WHERE
 254             mrp.post_id = p.id
 255             AND p.community_id = c.id) AS community_name
 256 FROM
 257     mod_remove_post mrp;
 258
 259 CREATE VIEW mod_lock_post_view AS
 260 SELECT
 261     mlp.*,
 262     (
 263         SELECT
 264             name
 265         FROM
 266             user_ u
 267         WHERE
 268             mlp.mod_user_id = u.id) AS mod_user_name,
 269     (
 270         SELECT
 271             name
 272         FROM
 273             post p
 274         WHERE
 275             mlp.post_id = p.id) AS post_name,
 276     (
 277         SELECT
 278             c.id
 279         FROM
 280             post p,
 281             community c
 282         WHERE
 283             mlp.post_id = p.id
 284             AND p.community_id = c.id) AS community_id,
 285     (
 286         SELECT
 287             c.name
 288         FROM
 289             post p,
 290             community c
 291         WHERE
 292             mlp.post_id = p.id
 293             AND p.community_id = c.id) AS community_name
 294 FROM
 295     mod_lock_post mlp;
 296
 297 CREATE VIEW mod_remove_comment_view AS
 298 SELECT
 299     mrc.*,
 300     (
 301         SELECT
 302             name
 303         FROM
 304             user_ u
 305         WHERE
 306             mrc.mod_user_id = u.id) AS mod_user_name,
 307     (
 308         SELECT
 309             c.id
 310         FROM
 311             comment c
 312         WHERE
 313             mrc.comment_id = c.id) AS comment_user_id,
 314     (
 315         SELECT
 316             name
 317         FROM
 318             user_ u,
 319             comment c
 320         WHERE
 321             mrc.comment_id = c.id
 322             AND u.id = c.creator_id) AS comment_user_name,
 323     (
 324         SELECT
 325             content
 326         FROM
 327             comment c
 328         WHERE
 329             mrc.comment_id = c.id) AS comment_content,
 330     (
 331         SELECT
 332             p.id
 333         FROM
 334             post p,
 335             comment c
 336         WHERE
 337             mrc.comment_id = c.id
 338             AND c.post_id = p.id) AS post_id,
 339     (
 340         SELECT
 341             p.name
 342         FROM
 343             post p,
 344             comment c
 345         WHERE
 346             mrc.comment_id = c.id
 347             AND c.post_id = p.id) AS post_name,
 348     (
 349         SELECT
 350             co.id
 351         FROM
 352             comment c,
 353             post p,
 354             community co
 355         WHERE
 356             mrc.comment_id = c.id
 357             AND c.post_id = p.id
 358             AND p.community_id = co.id) AS community_id,
 359     (
 360         SELECT
 361             co.name
 362         FROM
 363             comment c,
 364             post p,
 365             community co
 366         WHERE
 367             mrc.comment_id = c.id
 368             AND c.post_id = p.id
 369             AND p.community_id = co.id) AS community_name
 370 FROM
 371     mod_remove_comment mrc;
 372
 373 CREATE VIEW mod_sticky_post_view AS
 374 SELECT
 375     msp.*,
 376     (
 377         SELECT
 378             name
 379         FROM
 380             user_ u
 381         WHERE
 382             msp.mod_user_id = u.id) AS mod_user_name,
 383     (
 384         SELECT
 385             name
 386         FROM
 387             post p
 388         WHERE
 389             msp.post_id = p.id) AS post_name,
 390     (
 391         SELECT
 392             c.id
 393         FROM
 394             post p,
 395             community c
 396         WHERE
 397             msp.post_id = p.id
 398             AND p.community_id = c.id) AS community_id,
 399     (
 400         SELECT
 401             c.name
 402         FROM
 403             post p,
 404             community c
 405         WHERE
 406             msp.post_id = p.id
 407             AND p.community_id = c.id) AS community_name
 408 FROM
 409     mod_sticky_post msp;
 410