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