server/migrations/2020-01-13-025151_create_materialized_views/up.sql

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