server/migrations/2020-06-30-135809_remove_mat_views/down.sql

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