migrations/2020-07-08-202609_add_creator_published/down.sql

   1 drop view user_mention_view;
   2 drop view reply_fast_view;
   3 drop view comment_fast_view;
   4 drop view comment_view;
   5
   6 drop view user_mention_fast_view;
   7 drop table comment_aggregates_fast;
   8 drop view comment_aggregates_view;
   9
  10 create view comment_aggregates_view as
  11 select
  12         ct.*,
  13         -- community details
  14         p.community_id,
  15         c.actor_id as community_actor_id,
  16         c."local" as community_local,
  17         c."name" as community_name,
  18         -- creator details
  19         u.banned as banned,
  20   coalesce(cb.id, 0)::bool as banned_from_community,
  21         u.actor_id as creator_actor_id,
  22         u.local as creator_local,
  23         u.name as creator_name,
  24         u.avatar as creator_avatar,
  25         -- score details
  26         coalesce(cl.total, 0) as score,
  27         coalesce(cl.up, 0) as upvotes,
  28         coalesce(cl.down, 0) as downvotes,
  29         hot_rank(coalesce(cl.total, 0), ct.published) as hot_rank
  30 from comment ct
  31 left join post p on ct.post_id = p.id
  32 left join community c on p.community_id = c.id
  33 left join user_ u on ct.creator_id = u.id
  34 left join community_user_ban cb on ct.creator_id = cb.user_id and p.id = ct.post_id and p.community_id = cb.community_id
  35 left join (
  36         select
  37                 l.comment_id as id,
  38                 sum(l.score) as total,
  39                 count(case when l.score = 1 then 1 else null end) as up,
  40                 count(case when l.score = -1 then 1 else null end) as down
  41         from comment_like l
  42         group by comment_id
  43 ) as cl on cl.id = ct.id;
  44
  45 create or replace view comment_view as (
  46 select
  47         cav.*,
  48   us.user_id as user_id,
  49   us.my_vote as my_vote,
  50   us.is_subbed::bool as subscribed,
  51   us.is_saved::bool as saved
  52 from comment_aggregates_view cav
  53 cross join lateral (
  54         select
  55                 u.id as user_id,
  56                 coalesce(cl.score, 0) as my_vote,
  57     coalesce(cf.id, 0) as is_subbed,
  58     coalesce(cs.id, 0) as is_saved
  59         from user_ u
  60         left join comment_like cl on u.id = cl.user_id and cav.id = cl.comment_id
  61         left join comment_saved cs on u.id = cs.user_id and cs.comment_id = cav.id
  62         left join community_follower cf on u.id = cf.user_id and cav.community_id = cf.community_id
  63 ) as us
  64
  65 union all
  66
  67 select
  68     cav.*,
  69     null as user_id,
  70     null as my_vote,
  71     null as subscribed,
  72     null as saved
  73 from comment_aggregates_view cav
  74 );
  75
  76 create table comment_aggregates_fast as select * from comment_aggregates_view;
  77 alter table comment_aggregates_fast add primary key (id);
  78
  79 create view comment_fast_view as
  80 select
  81         cav.*,
  82   us.user_id as user_id,
  83   us.my_vote as my_vote,
  84   us.is_subbed::bool as subscribed,
  85   us.is_saved::bool as saved
  86 from comment_aggregates_fast cav
  87 cross join lateral (
  88         select
  89                 u.id as user_id,
  90                 coalesce(cl.score, 0) as my_vote,
  91     coalesce(cf.id, 0) as is_subbed,
  92     coalesce(cs.id, 0) as is_saved
  93         from user_ u
  94         left join comment_like cl on u.id = cl.user_id and cav.id = cl.comment_id
  95         left join comment_saved cs on u.id = cs.user_id and cs.comment_id = cav.id
  96         left join community_follower cf on u.id = cf.user_id and cav.community_id = cf.community_id
  97 ) as us
  98
  99 union all
 100
 101 select
 102     cav.*,
 103     null as user_id,
 104     null as my_vote,
 105     null as subscribed,
 106     null as saved
 107 from comment_aggregates_fast cav;
 108
 109 create view user_mention_view as
 110 select
 111     c.id,
 112     um.id as user_mention_id,
 113     c.creator_id,
 114     c.creator_actor_id,
 115     c.creator_local,
 116     c.post_id,
 117     c.parent_id,
 118     c.content,
 119     c.removed,
 120     um.read,
 121     c.published,
 122     c.updated,
 123     c.deleted,
 124     c.community_id,
 125     c.community_actor_id,
 126     c.community_local,
 127     c.community_name,
 128     c.banned,
 129     c.banned_from_community,
 130     c.creator_name,
 131     c.creator_avatar,
 132     c.score,
 133     c.upvotes,
 134     c.downvotes,
 135     c.hot_rank,
 136     c.user_id,
 137     c.my_vote,
 138     c.saved,
 139     um.recipient_id,
 140     (select actor_id from user_ u where u.id = um.recipient_id) as recipient_actor_id,
 141     (select local from user_ u where u.id = um.recipient_id) as recipient_local
 142 from user_mention um, comment_view c
 143 where um.comment_id = c.id;
 144
 145 create view user_mention_fast_view as
 146 select
 147     ac.id,
 148     um.id as user_mention_id,
 149     ac.creator_id,
 150     ac.creator_actor_id,
 151     ac.creator_local,
 152     ac.post_id,
 153     ac.parent_id,
 154     ac.content,
 155     ac.removed,
 156     um.read,
 157     ac.published,
 158     ac.updated,
 159     ac.deleted,
 160     ac.community_id,
 161     ac.community_actor_id,
 162     ac.community_local,
 163     ac.community_name,
 164     ac.banned,
 165     ac.banned_from_community,
 166     ac.creator_name,
 167     ac.creator_avatar,
 168     ac.score,
 169     ac.upvotes,
 170     ac.downvotes,
 171     ac.hot_rank,
 172     u.id as user_id,
 173     coalesce(cl.score, 0) as my_vote,
 174     (select cs.id::bool from comment_saved cs where u.id = cs.user_id and cs.comment_id = ac.id) as saved,
 175     um.recipient_id,
 176     (select actor_id from user_ u where u.id = um.recipient_id) as recipient_actor_id,
 177     (select local from user_ u where u.id = um.recipient_id) as recipient_local
 178 from user_ u
 179 cross join (
 180   select
 181   ca.*
 182   from comment_aggregates_fast ca
 183 ) ac
 184 left join comment_like cl on u.id = cl.user_id and ac.id = cl.comment_id
 185 left join user_mention um on um.comment_id = ac.id
 186
 187 union all
 188
 189 select
 190     ac.id,
 191     um.id as user_mention_id,
 192     ac.creator_id,
 193     ac.creator_actor_id,
 194     ac.creator_local,
 195     ac.post_id,
 196     ac.parent_id,
 197     ac.content,
 198     ac.removed,
 199     um.read,
 200     ac.published,
 201     ac.updated,
 202     ac.deleted,
 203     ac.community_id,
 204     ac.community_actor_id,
 205     ac.community_local,
 206     ac.community_name,
 207     ac.banned,
 208     ac.banned_from_community,
 209     ac.creator_name,
 210     ac.creator_avatar,
 211     ac.score,
 212     ac.upvotes,
 213     ac.downvotes,
 214     ac.hot_rank,
 215     null as user_id,
 216     null as my_vote,
 217     null as saved,
 218     um.recipient_id,
 219     (select actor_id from user_ u where u.id = um.recipient_id) as recipient_actor_id,
 220     (select local from user_ u where u.id = um.recipient_id) as recipient_local
 221 from comment_aggregates_fast ac
 222 left join user_mention um on um.comment_id = ac.id
 223 ;
 224
 225 -- Do the reply_view referencing the comment_fast_view
 226 create view reply_fast_view as
 227 with closereply as (
 228     select
 229     c2.id,
 230     c2.creator_id as sender_id,
 231     c.creator_id as recipient_id
 232     from comment c
 233     inner join comment c2 on c.id = c2.parent_id
 234     where c2.creator_id != c.creator_id
 235     -- Do union where post is null
 236     union
 237     select
 238     c.id,
 239     c.creator_id as sender_id,
 240     p.creator_id as recipient_id
 241     from comment c, post p
 242     where c.post_id = p.id and c.parent_id is null and c.creator_id != p.creator_id
 243 )
 244 select cv.*,
 245 closereply.recipient_id
 246 from comment_fast_view cv, closereply
 247 where closereply.id = cv.id
 248 ;
 249
 250 -- add creator_published to the post view
 251 drop view post_fast_view;
 252 drop table post_aggregates_fast;
 253 drop view post_view;
 254 drop view post_aggregates_view;
 255
 256 create view post_aggregates_view as
 257 select
 258         p.*,
 259         -- creator details
 260         u.actor_id as creator_actor_id,
 261         u."local" as creator_local,
 262         u."name" as creator_name,
 263         u.avatar as creator_avatar,
 264   u.banned as banned,
 265   cb.id::bool as banned_from_community,
 266         -- community details
 267         c.actor_id as community_actor_id,
 268         c."local" as community_local,
 269         c."name" as community_name,
 270         c.removed as community_removed,
 271         c.deleted as community_deleted,
 272         c.nsfw as community_nsfw,
 273         -- post score data/comment count
 274         coalesce(ct.comments, 0) as number_of_comments,
 275         coalesce(pl.score, 0) as score,
 276         coalesce(pl.upvotes, 0) as upvotes,
 277         coalesce(pl.downvotes, 0) as downvotes,
 278         hot_rank(
 279                 coalesce(pl.score , 0), (
 280                         case
 281                                 when (p.published < ('now'::timestamp - '1 month'::interval))
 282                                 then p.published
 283                                 else greatest(ct.recent_comment_time, p.published)
 284                         end
 285                 )
 286         ) as hot_rank,
 287         (
 288                 case
 289                         when (p.published < ('now'::timestamp - '1 month'::interval))
 290                         then p.published
 291                         else greatest(ct.recent_comment_time, p.published)
 292                 end
 293         ) as newest_activity_time
 294 from post p
 295 left join user_ u on p.creator_id = u.id
 296 left join community_user_ban cb on p.creator_id = cb.user_id and p.community_id = cb.community_id
 297 left join community c on p.community_id = c.id
 298 left join (
 299         select
 300                 post_id,
 301                 count(*) as comments,
 302                 max(published) as recent_comment_time
 303         from comment
 304         group by post_id
 305 ) ct on ct.post_id = p.id
 306 left join (
 307         select
 308                 post_id,
 309                 sum(score) as score,
 310                 sum(score) filter (where score = 1) as upvotes,
 311                 -sum(score) filter (where score = -1) as downvotes
 312         from post_like
 313         group by post_id
 314 ) pl on pl.post_id = p.id
 315 order by p.id;
 316
 317 create view post_view as
 318 select
 319         pav.*,
 320         us.id as user_id,
 321         us.user_vote as my_vote,
 322         us.is_subbed::bool as subscribed,
 323         us.is_read::bool as read,
 324         us.is_saved::bool as saved
 325 from post_aggregates_view pav
 326 cross join lateral (
 327         select
 328                 u.id,
 329                 coalesce(cf.community_id, 0) as is_subbed,
 330                 coalesce(pr.post_id, 0) as is_read,
 331                 coalesce(ps.post_id, 0) as is_saved,
 332                 coalesce(pl.score, 0) as user_vote
 333         from user_ u
 334         left join community_user_ban cb on u.id = cb.user_id and cb.community_id = pav.community_id
 335         left join community_follower cf on u.id = cf.user_id and cf.community_id = pav.community_id
 336         left join post_read pr on u.id = pr.user_id and pr.post_id = pav.id
 337         left join post_saved ps on u.id = ps.user_id and ps.post_id = pav.id
 338         left join post_like pl on u.id = pl.user_id and pav.id = pl.post_id
 339 ) as us
 340
 341 union all
 342
 343 select
 344 pav.*,
 345 null as user_id,
 346 null as my_vote,
 347 null as subscribed,
 348 null as read,
 349 null as saved
 350 from post_aggregates_view pav;
 351
 352 create table post_aggregates_fast as select * from post_aggregates_view;
 353 alter table post_aggregates_fast add primary key (id);
 354
 355 create view post_fast_view as
 356 select
 357         pav.*,
 358         us.id as user_id,
 359         us.user_vote as my_vote,
 360         us.is_subbed::bool as subscribed,
 361         us.is_read::bool as read,
 362         us.is_saved::bool as saved
 363 from post_aggregates_fast pav
 364 cross join lateral (
 365         select
 366                 u.id,
 367                 coalesce(cf.community_id, 0) as is_subbed,
 368                 coalesce(pr.post_id, 0) as is_read,
 369                 coalesce(ps.post_id, 0) as is_saved,
 370                 coalesce(pl.score, 0) as user_vote
 371         from user_ u
 372         left join community_user_ban cb on u.id = cb.user_id and cb.community_id = pav.community_id
 373         left join community_follower cf on u.id = cf.user_id and cf.community_id = pav.community_id
 374         left join post_read pr on u.id = pr.user_id and pr.post_id = pav.id
 375         left join post_saved ps on u.id = ps.user_id and ps.post_id = pav.id
 376         left join post_like pl on u.id = pl.user_id and pav.id = pl.post_id
 377 ) as us
 378
 379 union all
 380
 381 select
 382 pav.*,
 383 null as user_id,
 384 null as my_vote,
 385 null as subscribed,
 386 null as read,
 387 null as saved
 388 from post_aggregates_fast pav;