server/migrations/2020-04-14-163701_update_views_for_activitypub/up.sql

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