migrations/2020-04-14-163701_update_views_for_activitypub/down.sql

   1 -- user_view
   2 drop view user_view cascade;
   3
   4 create view user_view as
   5 select
   6 u.id,
   7 u.name,
   8 u.avatar,
   9 u.email,
  10 u.matrix_user_id,
  11 u.admin,
  12 u.banned,
  13 u.show_avatars,
  14 u.send_notifications_to_email,
  15 u.published,
  16 (select count(*) from post p where p.creator_id = u.id) as number_of_posts,
  17 (select coalesce(sum(score), 0) from post p, post_like pl where u.id = p.creator_id and p.id = pl.post_id) as post_score,
  18 (select count(*) from comment c where c.creator_id = u.id) as number_of_comments,
  19 (select coalesce(sum(score), 0) from comment c, comment_like cl where u.id = c.creator_id and c.id = cl.comment_id) as comment_score
  20 from user_ u;
  21
  22 create materialized view user_mview as select * from user_view;
  23
  24 create unique index idx_user_mview_id on user_mview (id);
  25
  26 -- community_view
  27 drop view community_aggregates_view cascade;
  28 create view community_aggregates_view as
  29 select c.*,
  30 (select name from user_ u where c.creator_id = u.id) as creator_name,
  31 (select avatar from user_ u where c.creator_id = u.id) as creator_avatar,
  32 (select name from category ct where c.category_id = ct.id) as category_name,
  33 (select count(*) from community_follower cf where cf.community_id = c.id) as number_of_subscribers,
  34 (select count(*) from post p where p.community_id = c.id) as number_of_posts,
  35 (select count(*) from comment co, post p where c.id = p.community_id and p.id = co.post_id) as number_of_comments,
  36 hot_rank((select count(*) from community_follower cf where cf.community_id = c.id), c.published) as hot_rank
  37 from community c;
  38
  39 create materialized view community_aggregates_mview as select * from community_aggregates_view;
  40
  41 create unique index idx_community_aggregates_mview_id on community_aggregates_mview (id);
  42
  43 create view community_view as
  44 with all_community as
  45 (
  46   select
  47   ca.*
  48   from community_aggregates_view ca
  49 )
  50
  51 select
  52 ac.*,
  53 u.id as user_id,
  54 (select cf.id::boolean from community_follower cf where u.id = cf.user_id and ac.id = cf.community_id) as subscribed
  55 from user_ u
  56 cross join all_community ac
  57
  58 union all
  59
  60 select
  61 ac.*,
  62 null as user_id,
  63 null as subscribed
  64 from all_community ac
  65 ;
  66
  67 create view community_mview as
  68 with all_community as
  69 (
  70   select
  71   ca.*
  72   from community_aggregates_mview ca
  73 )
  74
  75 select
  76 ac.*,
  77 u.id as user_id,
  78 (select cf.id::boolean from community_follower cf where u.id = cf.user_id and ac.id = cf.community_id) as subscribed
  79 from user_ u
  80 cross join all_community ac
  81
  82 union all
  83
  84 select
  85 ac.*,
  86 null as user_id,
  87 null as subscribed
  88 from all_community ac
  89 ;
  90
  91 -- community views
  92 drop view community_moderator_view;
  93 drop view community_follower_view;
  94 drop view community_user_ban_view;
  95
  96 create view community_moderator_view as
  97 select *,
  98 (select name from user_ u where cm.user_id = u.id) as user_name,
  99 (select avatar from user_ u where cm.user_id = u.id),
 100 (select name from community c where cm.community_id = c.id) as community_name
 101 from community_moderator cm;
 102
 103 create view community_follower_view as
 104 select *,
 105 (select name from user_ u where cf.user_id = u.id) as user_name,
 106 (select avatar from user_ u where cf.user_id = u.id),
 107 (select name from community c where cf.community_id = c.id) as community_name
 108 from community_follower cf;
 109
 110 create view community_user_ban_view as
 111 select *,
 112 (select name from user_ u where cm.user_id = u.id) as user_name,
 113 (select avatar from user_ u where cm.user_id = u.id),
 114 (select name from community c where cm.community_id = c.id) as community_name
 115 from community_user_ban cm;
 116
 117 -- post_view
 118 drop view post_view;
 119 drop view post_mview;
 120 drop materialized view post_aggregates_mview;
 121 drop view post_aggregates_view;
 122
 123 -- regen post view
 124 create view post_aggregates_view as
 125 select
 126 p.*,
 127 (select u.banned from user_ u where p.creator_id = u.id) as banned,
 128 (select cb.id::bool from community_user_ban cb where p.creator_id = cb.user_id and p.community_id = cb.community_id) as banned_from_community,
 129 (select name from user_ where p.creator_id = user_.id) as creator_name,
 130 (select avatar from user_ where p.creator_id = user_.id) as creator_avatar,
 131 (select name from community where p.community_id = community.id) as community_name,
 132 (select removed from community c where p.community_id = c.id) as community_removed,
 133 (select deleted from community c where p.community_id = c.id) as community_deleted,
 134 (select nsfw from community c where p.community_id = c.id) as community_nsfw,
 135 (select count(*) from comment where comment.post_id = p.id) as number_of_comments,
 136 coalesce(sum(pl.score), 0) as score,
 137 count (case when pl.score = 1 then 1 else null end) as upvotes,
 138 count (case when pl.score = -1 then 1 else null end) as downvotes,
 139 hot_rank(coalesce(sum(pl.score) , 0),
 140   (
 141     case when (p.published < ('now'::timestamp - '1 month'::interval)) then p.published -- Prevents necro-bumps
 142     else greatest(c.recent_comment_time, p.published)
 143     end
 144   )
 145 ) as hot_rank,
 146 (
 147   case when (p.published < ('now'::timestamp - '1 month'::interval)) then p.published -- Prevents necro-bumps
 148   else greatest(c.recent_comment_time, p.published)
 149   end
 150 ) as newest_activity_time
 151 from post p
 152 left join post_like pl on p.id = pl.post_id
 153 left join (
 154   select post_id,
 155   max(published) as recent_comment_time
 156   from comment
 157   group by 1
 158 ) c on p.id = c.post_id
 159 group by p.id, c.recent_comment_time;
 160
 161 create materialized view post_aggregates_mview as select * from post_aggregates_view;
 162
 163 create unique index idx_post_aggregates_mview_id on post_aggregates_mview (id);
 164
 165 create view post_view as
 166 with all_post as (
 167   select
 168   pa.*
 169   from post_aggregates_view pa
 170 )
 171 select
 172 ap.*,
 173 u.id as user_id,
 174 coalesce(pl.score, 0) as my_vote,
 175 (select cf.id::bool from community_follower cf where u.id = cf.user_id and cf.community_id = ap.community_id) as subscribed,
 176 (select pr.id::bool from post_read pr where u.id = pr.user_id and pr.post_id = ap.id) as read,
 177 (select ps.id::bool from post_saved ps where u.id = ps.user_id and ps.post_id = ap.id) as saved
 178 from user_ u
 179 cross join all_post ap
 180 left join post_like pl on u.id = pl.user_id and ap.id = pl.post_id
 181
 182 union all
 183
 184 select
 185 ap.*,
 186 null as user_id,
 187 null as my_vote,
 188 null as subscribed,
 189 null as read,
 190 null as saved
 191 from all_post ap
 192 ;
 193
 194 create view post_mview as
 195 with all_post as (
 196   select
 197   pa.*
 198   from post_aggregates_mview pa
 199 )
 200 select
 201 ap.*,
 202 u.id as user_id,
 203 coalesce(pl.score, 0) as my_vote,
 204 (select cf.id::bool from community_follower cf where u.id = cf.user_id and cf.community_id = ap.community_id) as subscribed,
 205 (select pr.id::bool from post_read pr where u.id = pr.user_id and pr.post_id = ap.id) as read,
 206 (select ps.id::bool from post_saved ps where u.id = ps.user_id and ps.post_id = ap.id) as saved
 207 from user_ u
 208 cross join all_post ap
 209 left join post_like pl on u.id = pl.user_id and ap.id = pl.post_id
 210
 211 union all
 212
 213 select
 214 ap.*,
 215 null as user_id,
 216 null as my_vote,
 217 null as subscribed,
 218 null as read,
 219 null as saved
 220 from all_post ap
 221 ;
 222
 223
 224 -- reply_view, comment_view, user_mention
 225 drop view reply_view;
 226 drop view user_mention_view;
 227 drop view user_mention_mview;
 228 drop view comment_view;
 229 drop view comment_mview;
 230 drop materialized view comment_aggregates_mview;
 231 drop view comment_aggregates_view;
 232
 233 -- reply and comment view
 234 create view comment_aggregates_view as
 235 select
 236 c.*,
 237 (select community_id from post p where p.id = c.post_id),
 238 (select co.name from post p, community co where p.id = c.post_id and p.community_id = co.id) as community_name,
 239 (select u.banned from user_ u where c.creator_id = u.id) as banned,
 240 (select cb.id::bool from community_user_ban cb, post p where c.creator_id = cb.user_id and p.id = c.post_id and p.community_id = cb.community_id) as banned_from_community,
 241 (select name from user_ where c.creator_id = user_.id) as creator_name,
 242 (select avatar from user_ where c.creator_id = user_.id) as creator_avatar,
 243 coalesce(sum(cl.score), 0) as score,
 244 count (case when cl.score = 1 then 1 else null end) as upvotes,
 245 count (case when cl.score = -1 then 1 else null end) as downvotes,
 246 hot_rank(coalesce(sum(cl.score) , 0), c.published) as hot_rank
 247 from comment c
 248 left join comment_like cl on c.id = cl.comment_id
 249 group by c.id;
 250
 251 create materialized view comment_aggregates_mview as select * from comment_aggregates_view;
 252
 253 create unique index idx_comment_aggregates_mview_id on comment_aggregates_mview (id);
 254
 255 create view comment_view as
 256 with all_comment as
 257 (
 258   select
 259   ca.*
 260   from comment_aggregates_view ca
 261 )
 262
 263 select
 264 ac.*,
 265 u.id as user_id,
 266 coalesce(cl.score, 0) as my_vote,
 267 (select cf.id::boolean from community_follower cf where u.id = cf.user_id and ac.community_id = cf.community_id) as subscribed,
 268 (select cs.id::bool from comment_saved cs where u.id = cs.user_id and cs.comment_id = ac.id) as saved
 269 from user_ u
 270 cross join all_comment ac
 271 left join comment_like cl on u.id = cl.user_id and ac.id = cl.comment_id
 272
 273 union all
 274
 275 select
 276     ac.*,
 277     null as user_id,
 278     null as my_vote,
 279     null as subscribed,
 280     null as saved
 281 from all_comment ac
 282 ;
 283
 284 create view comment_mview as
 285 with all_comment as
 286 (
 287   select
 288   ca.*
 289   from comment_aggregates_mview ca
 290 )
 291
 292 select
 293 ac.*,
 294 u.id as user_id,
 295 coalesce(cl.score, 0) as my_vote,
 296 (select cf.id::boolean from community_follower cf where u.id = cf.user_id and ac.community_id = cf.community_id) as subscribed,
 297 (select cs.id::bool from comment_saved cs where u.id = cs.user_id and cs.comment_id = ac.id) as saved
 298 from user_ u
 299 cross join all_comment ac
 300 left join comment_like cl on u.id = cl.user_id and ac.id = cl.comment_id
 301
 302 union all
 303
 304 select
 305     ac.*,
 306     null as user_id,
 307     null as my_vote,
 308     null as subscribed,
 309     null as saved
 310 from all_comment ac
 311 ;
 312
 313 -- Do the reply_view referencing the comment_mview
 314 create view reply_view as
 315 with closereply as (
 316     select
 317     c2.id,
 318     c2.creator_id as sender_id,
 319     c.creator_id as recipient_id
 320     from comment c
 321     inner join comment c2 on c.id = c2.parent_id
 322     where c2.creator_id != c.creator_id
 323     -- Do union where post is null
 324     union
 325     select
 326     c.id,
 327     c.creator_id as sender_id,
 328     p.creator_id as recipient_id
 329     from comment c, post p
 330     where c.post_id = p.id and c.parent_id is null and c.creator_id != p.creator_id
 331 )
 332 select cv.*,
 333 closereply.recipient_id
 334 from comment_mview cv, closereply
 335 where closereply.id = cv.id
 336 ;
 337
 338 -- user mention
 339 create view user_mention_view as
 340 select
 341     c.id,
 342     um.id as user_mention_id,
 343     c.creator_id,
 344     c.post_id,
 345     c.parent_id,
 346     c.content,
 347     c.removed,
 348     um.read,
 349     c.published,
 350     c.updated,
 351     c.deleted,
 352     c.community_id,
 353     c.community_name,
 354     c.banned,
 355     c.banned_from_community,
 356     c.creator_name,
 357     c.creator_avatar,
 358     c.score,
 359     c.upvotes,
 360     c.downvotes,
 361     c.hot_rank,
 362     c.user_id,
 363     c.my_vote,
 364     c.saved,
 365     um.recipient_id
 366 from user_mention um, comment_view c
 367 where um.comment_id = c.id;
 368
 369
 370 create view user_mention_mview as
 371 with all_comment as
 372 (
 373   select
 374   ca.*
 375   from comment_aggregates_mview ca
 376 )
 377
 378 select
 379     ac.id,
 380     um.id as user_mention_id,
 381     ac.creator_id,
 382     ac.post_id,
 383     ac.parent_id,
 384     ac.content,
 385     ac.removed,
 386     um.read,
 387     ac.published,
 388     ac.updated,
 389     ac.deleted,
 390     ac.community_id,
 391     ac.community_name,
 392     ac.banned,
 393     ac.banned_from_community,
 394     ac.creator_name,
 395     ac.creator_avatar,
 396     ac.score,
 397     ac.upvotes,
 398     ac.downvotes,
 399     ac.hot_rank,
 400     u.id as user_id,
 401     coalesce(cl.score, 0) as my_vote,
 402     (select cs.id::bool from comment_saved cs where u.id = cs.user_id and cs.comment_id = ac.id) as saved,
 403     um.recipient_id
 404 from user_ u
 405 cross join all_comment ac
 406 left join comment_like cl on u.id = cl.user_id and ac.id = cl.comment_id
 407 left join user_mention um on um.comment_id = ac.id
 408
 409 union all
 410
 411 select
 412     ac.id,
 413     um.id as user_mention_id,
 414     ac.creator_id,
 415     ac.post_id,
 416     ac.parent_id,
 417     ac.content,
 418     ac.removed,
 419     um.read,
 420     ac.published,
 421     ac.updated,
 422     ac.deleted,
 423     ac.community_id,
 424     ac.community_name,
 425     ac.banned,
 426     ac.banned_from_community,
 427     ac.creator_name,
 428     ac.creator_avatar,
 429     ac.score,
 430     ac.upvotes,
 431     ac.downvotes,
 432     ac.hot_rank,
 433     null as user_id,
 434     null as my_vote,
 435     null as saved,
 436     um.recipient_id
 437 from all_comment ac
 438 left join user_mention um on um.comment_id = ac.id
 439 ;
 440