migrations/2020-08-03-000110_add_preferred_usernames_banners_and_icons/down.sql

   1 -- Drops first
   2 drop view site_view;
   3 drop table user_fast;
   4 drop view user_view;
   5 drop view post_fast_view;
   6 drop table post_aggregates_fast;
   7 drop view post_view;
   8 drop view post_aggregates_view;
   9 drop view community_moderator_view;
  10 drop view community_follower_view;
  11 drop view community_user_ban_view;
  12 drop view community_view;
  13 drop view community_aggregates_view;
  14 drop view community_fast_view;
  15 drop table community_aggregates_fast;
  16 drop view private_message_view;
  17 drop view user_mention_view;
  18 drop view reply_fast_view;
  19 drop view comment_fast_view;
  20 drop view comment_view;
  21 drop view user_mention_fast_view;
  22 drop table comment_aggregates_fast;
  23 drop view comment_aggregates_view;
  24
  25 alter table site
  26   drop column icon,
  27   drop column banner;
  28
  29 alter table community
  30   drop column icon,
  31   drop column banner;
  32
  33 alter table user_ drop column banner;
  34
  35 -- Site
  36 create view site_view as
  37 select *,
  38 (select name from user_ u where s.creator_id = u.id) as creator_name,
  39 (select avatar from user_ u where s.creator_id = u.id) as creator_avatar,
  40 (select count(*) from user_) as number_of_users,
  41 (select count(*) from post) as number_of_posts,
  42 (select count(*) from comment) as number_of_comments,
  43 (select count(*) from community) as number_of_communities
  44 from site s;
  45
  46 -- User
  47 create view user_view as
  48 select
  49         u.id,
  50   u.actor_id,
  51         u.name,
  52         u.avatar,
  53         u.email,
  54         u.matrix_user_id,
  55   u.bio,
  56   u.local,
  57         u.admin,
  58         u.banned,
  59         u.show_avatars,
  60         u.send_notifications_to_email,
  61         u.published,
  62         coalesce(pd.posts, 0) as number_of_posts,
  63         coalesce(pd.score, 0) as post_score,
  64         coalesce(cd.comments, 0) as number_of_comments,
  65         coalesce(cd.score, 0) as comment_score
  66 from user_ u
  67 left join (
  68     select
  69         p.creator_id as creator_id,
  70         count(distinct p.id) as posts,
  71         sum(pl.score) as score
  72     from post p
  73     join post_like pl on p.id = pl.post_id
  74     group by p.creator_id
  75 ) pd on u.id = pd.creator_id
  76 left join (
  77     select
  78         c.creator_id,
  79         count(distinct c.id) as comments,
  80         sum(cl.score) as score
  81     from comment c
  82     join comment_like cl on c.id = cl.comment_id
  83     group by c.creator_id
  84 ) cd on u.id = cd.creator_id;
  85
  86 create table user_fast as select * from user_view;
  87 alter table user_fast add primary key (id);
  88
  89 -- Post fast
  90
  91 create view post_aggregates_view as
  92 select
  93         p.*,
  94         -- creator details
  95         u.actor_id as creator_actor_id,
  96         u."local" as creator_local,
  97         u."name" as creator_name,
  98   u.published as creator_published,
  99         u.avatar as creator_avatar,
 100   u.banned as banned,
 101   cb.id::bool as banned_from_community,
 102         -- community details
 103         c.actor_id as community_actor_id,
 104         c."local" as community_local,
 105         c."name" as community_name,
 106         c.removed as community_removed,
 107         c.deleted as community_deleted,
 108         c.nsfw as community_nsfw,
 109         -- post score data/comment count
 110         coalesce(ct.comments, 0) as number_of_comments,
 111         coalesce(pl.score, 0) as score,
 112         coalesce(pl.upvotes, 0) as upvotes,
 113         coalesce(pl.downvotes, 0) as downvotes,
 114         hot_rank(
 115                 coalesce(pl.score , 0), (
 116                         case
 117                                 when (p.published < ('now'::timestamp - '1 month'::interval))
 118                                 then p.published
 119                                 else greatest(ct.recent_comment_time, p.published)
 120                         end
 121                 )
 122         ) as hot_rank,
 123         (
 124                 case
 125                         when (p.published < ('now'::timestamp - '1 month'::interval))
 126                         then p.published
 127                         else greatest(ct.recent_comment_time, p.published)
 128                 end
 129         ) as newest_activity_time
 130 from post p
 131 left join user_ u on p.creator_id = u.id
 132 left join community_user_ban cb on p.creator_id = cb.user_id and p.community_id = cb.community_id
 133 left join community c on p.community_id = c.id
 134 left join (
 135         select
 136                 post_id,
 137                 count(*) as comments,
 138                 max(published) as recent_comment_time
 139         from comment
 140         group by post_id
 141 ) ct on ct.post_id = p.id
 142 left join (
 143         select
 144                 post_id,
 145                 sum(score) as score,
 146                 sum(score) filter (where score = 1) as upvotes,
 147                 -sum(score) filter (where score = -1) as downvotes
 148         from post_like
 149         group by post_id
 150 ) pl on pl.post_id = p.id
 151 order by p.id;
 152
 153 create view post_view as
 154 select
 155         pav.*,
 156         us.id as user_id,
 157         us.user_vote as my_vote,
 158         us.is_subbed::bool as subscribed,
 159         us.is_read::bool as read,
 160         us.is_saved::bool as saved
 161 from post_aggregates_view pav
 162 cross join lateral (
 163         select
 164                 u.id,
 165                 coalesce(cf.community_id, 0) as is_subbed,
 166                 coalesce(pr.post_id, 0) as is_read,
 167                 coalesce(ps.post_id, 0) as is_saved,
 168                 coalesce(pl.score, 0) as user_vote
 169         from user_ u
 170         left join community_user_ban cb on u.id = cb.user_id and cb.community_id = pav.community_id
 171         left join community_follower cf on u.id = cf.user_id and cf.community_id = pav.community_id
 172         left join post_read pr on u.id = pr.user_id and pr.post_id = pav.id
 173         left join post_saved ps on u.id = ps.user_id and ps.post_id = pav.id
 174         left join post_like pl on u.id = pl.user_id and pav.id = pl.post_id
 175 ) as us
 176
 177 union all
 178
 179 select
 180 pav.*,
 181 null as user_id,
 182 null as my_vote,
 183 null as subscribed,
 184 null as read,
 185 null as saved
 186 from post_aggregates_view pav;
 187
 188 create table post_aggregates_fast as select * from post_aggregates_view;
 189 alter table post_aggregates_fast add primary key (id);
 190
 191 create view post_fast_view as
 192 select
 193         pav.*,
 194         us.id as user_id,
 195         us.user_vote as my_vote,
 196         us.is_subbed::bool as subscribed,
 197         us.is_read::bool as read,
 198         us.is_saved::bool as saved
 199 from post_aggregates_fast pav
 200 cross join lateral (
 201         select
 202                 u.id,
 203                 coalesce(cf.community_id, 0) as is_subbed,
 204                 coalesce(pr.post_id, 0) as is_read,
 205                 coalesce(ps.post_id, 0) as is_saved,
 206                 coalesce(pl.score, 0) as user_vote
 207         from user_ u
 208         left join community_user_ban cb on u.id = cb.user_id and cb.community_id = pav.community_id
 209         left join community_follower cf on u.id = cf.user_id and cf.community_id = pav.community_id
 210         left join post_read pr on u.id = pr.user_id and pr.post_id = pav.id
 211         left join post_saved ps on u.id = ps.user_id and ps.post_id = pav.id
 212         left join post_like pl on u.id = pl.user_id and pav.id = pl.post_id
 213 ) as us
 214
 215 union all
 216
 217 select
 218 pav.*,
 219 null as user_id,
 220 null as my_vote,
 221 null as subscribed,
 222 null as read,
 223 null as saved
 224 from post_aggregates_fast pav;
 225
 226 -- Community
 227 create view community_aggregates_view as
 228 select
 229     c.id,
 230     c.name,
 231     c.title,
 232     c.description,
 233     c.category_id,
 234     c.creator_id,
 235     c.removed,
 236     c.published,
 237     c.updated,
 238     c.deleted,
 239     c.nsfw,
 240     c.actor_id,
 241     c.local,
 242     c.last_refreshed_at,
 243     u.actor_id as creator_actor_id,
 244     u.local as creator_local,
 245     u.name as creator_name,
 246     u.avatar as creator_avatar,
 247     cat.name as category_name,
 248     coalesce(cf.subs, 0) as number_of_subscribers,
 249     coalesce(cd.posts, 0) as number_of_posts,
 250     coalesce(cd.comments, 0) as number_of_comments,
 251     hot_rank(cf.subs, c.published) as hot_rank
 252 from community c
 253 left join user_ u on c.creator_id = u.id
 254 left join category cat on c.category_id = cat.id
 255 left join (
 256     select
 257         p.community_id,
 258         count(distinct p.id) as posts,
 259         count(distinct ct.id) as comments
 260     from post p
 261     join comment ct on p.id = ct.post_id
 262     group by p.community_id
 263 ) cd on cd.community_id = c.id
 264 left join (
 265     select
 266         community_id,
 267         count(*) as subs
 268     from community_follower
 269     group by community_id
 270 ) cf on cf.community_id = c.id;
 271
 272 create view community_view as
 273 select
 274     cv.*,
 275     us.user as user_id,
 276     us.is_subbed::bool as subscribed
 277 from community_aggregates_view cv
 278 cross join lateral (
 279         select
 280                 u.id as user,
 281                 coalesce(cf.community_id, 0) as is_subbed
 282         from user_ u
 283         left join community_follower cf on u.id = cf.user_id and cf.community_id = cv.id
 284 ) as us
 285
 286 union all
 287
 288 select
 289     cv.*,
 290     null as user_id,
 291     null as subscribed
 292 from community_aggregates_view cv;
 293
 294 create view community_moderator_view as
 295 select
 296     cm.*,
 297     u.actor_id as user_actor_id,
 298     u.local as user_local,
 299     u.name as user_name,
 300     u.avatar as avatar,
 301     c.actor_id as community_actor_id,
 302     c.local as community_local,
 303     c.name as community_name
 304 from community_moderator cm
 305 left join user_ u on cm.user_id = u.id
 306 left join community c on cm.community_id = c.id;
 307
 308 create view community_follower_view as
 309 select
 310     cf.*,
 311     u.actor_id as user_actor_id,
 312     u.local as user_local,
 313     u.name as user_name,
 314     u.avatar as avatar,
 315     c.actor_id as community_actor_id,
 316     c.local as community_local,
 317     c.name as community_name
 318 from community_follower cf
 319 left join user_ u on cf.user_id = u.id
 320 left join community c on cf.community_id = c.id;
 321
 322 create view community_user_ban_view as
 323 select
 324     cb.*,
 325     u.actor_id as user_actor_id,
 326     u.local as user_local,
 327     u.name as user_name,
 328     u.avatar as avatar,
 329     c.actor_id as community_actor_id,
 330     c.local as community_local,
 331     c.name as community_name
 332 from community_user_ban cb
 333 left join user_ u on cb.user_id = u.id
 334 left join community c on cb.community_id = c.id;
 335
 336 -- The community fast table
 337
 338 create table community_aggregates_fast as select * from community_aggregates_view;
 339 alter table community_aggregates_fast add primary key (id);
 340
 341 create view community_fast_view as
 342 select
 343 ac.*,
 344 u.id as user_id,
 345 (select cf.id::boolean from community_follower cf where u.id = cf.user_id and ac.id = cf.community_id) as subscribed
 346 from user_ u
 347 cross join (
 348   select
 349   ca.*
 350   from community_aggregates_fast ca
 351 ) ac
 352
 353 union all
 354
 355 select
 356 caf.*,
 357 null as user_id,
 358 null as subscribed
 359 from community_aggregates_fast caf;
 360
 361
 362 -- Private message
 363 create view private_message_view as
 364 select
 365 pm.*,
 366 u.name as creator_name,
 367 u.avatar as creator_avatar,
 368 u.actor_id as creator_actor_id,
 369 u.local as creator_local,
 370 u2.name as recipient_name,
 371 u2.avatar as recipient_avatar,
 372 u2.actor_id as recipient_actor_id,
 373 u2.local as recipient_local
 374 from private_message pm
 375 inner join user_ u on u.id = pm.creator_id
 376 inner join user_ u2 on u2.id = pm.recipient_id;
 377
 378
 379 -- Comments, mentions, replies
 380
 381 create view comment_aggregates_view as
 382 select
 383         ct.*,
 384         -- post details
 385         p."name" as post_name,
 386         p.community_id,
 387         -- community details
 388         c.actor_id as community_actor_id,
 389         c."local" as community_local,
 390         c."name" as community_name,
 391         -- creator details
 392         u.banned as banned,
 393   coalesce(cb.id, 0)::bool as banned_from_community,
 394         u.actor_id as creator_actor_id,
 395         u.local as creator_local,
 396         u.name as creator_name,
 397   u.published as creator_published,
 398         u.avatar as creator_avatar,
 399         -- score details
 400         coalesce(cl.total, 0) as score,
 401         coalesce(cl.up, 0) as upvotes,
 402         coalesce(cl.down, 0) as downvotes,
 403         hot_rank(coalesce(cl.total, 0), ct.published) as hot_rank
 404 from comment ct
 405 left join post p on ct.post_id = p.id
 406 left join community c on p.community_id = c.id
 407 left join user_ u on ct.creator_id = u.id
 408 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
 409 left join (
 410         select
 411                 l.comment_id as id,
 412                 sum(l.score) as total,
 413                 count(case when l.score = 1 then 1 else null end) as up,
 414                 count(case when l.score = -1 then 1 else null end) as down
 415         from comment_like l
 416         group by comment_id
 417 ) as cl on cl.id = ct.id;
 418
 419 create or replace view comment_view as (
 420 select
 421         cav.*,
 422   us.user_id as user_id,
 423   us.my_vote as my_vote,
 424   us.is_subbed::bool as subscribed,
 425   us.is_saved::bool as saved
 426 from comment_aggregates_view cav
 427 cross join lateral (
 428         select
 429                 u.id as user_id,
 430                 coalesce(cl.score, 0) as my_vote,
 431     coalesce(cf.id, 0) as is_subbed,
 432     coalesce(cs.id, 0) as is_saved
 433         from user_ u
 434         left join comment_like cl on u.id = cl.user_id and cav.id = cl.comment_id
 435         left join comment_saved cs on u.id = cs.user_id and cs.comment_id = cav.id
 436         left join community_follower cf on u.id = cf.user_id and cav.community_id = cf.community_id
 437 ) as us
 438
 439 union all
 440
 441 select
 442     cav.*,
 443     null as user_id,
 444     null as my_vote,
 445     null as subscribed,
 446     null as saved
 447 from comment_aggregates_view cav
 448 );
 449
 450 create table comment_aggregates_fast as select * from comment_aggregates_view;
 451 alter table comment_aggregates_fast add primary key (id);
 452
 453 create view comment_fast_view as
 454 select
 455         cav.*,
 456   us.user_id as user_id,
 457   us.my_vote as my_vote,
 458   us.is_subbed::bool as subscribed,
 459   us.is_saved::bool as saved
 460 from comment_aggregates_fast cav
 461 cross join lateral (
 462         select
 463                 u.id as user_id,
 464                 coalesce(cl.score, 0) as my_vote,
 465     coalesce(cf.id, 0) as is_subbed,
 466     coalesce(cs.id, 0) as is_saved
 467         from user_ u
 468         left join comment_like cl on u.id = cl.user_id and cav.id = cl.comment_id
 469         left join comment_saved cs on u.id = cs.user_id and cs.comment_id = cav.id
 470         left join community_follower cf on u.id = cf.user_id and cav.community_id = cf.community_id
 471 ) as us
 472
 473 union all
 474
 475 select
 476     cav.*,
 477     null as user_id,
 478     null as my_vote,
 479     null as subscribed,
 480     null as saved
 481 from comment_aggregates_fast cav;
 482
 483 create view user_mention_view as
 484 select
 485     c.id,
 486     um.id as user_mention_id,
 487     c.creator_id,
 488     c.creator_actor_id,
 489     c.creator_local,
 490     c.post_id,
 491     c.post_name,
 492     c.parent_id,
 493     c.content,
 494     c.removed,
 495     um.read,
 496     c.published,
 497     c.updated,
 498     c.deleted,
 499     c.community_id,
 500     c.community_actor_id,
 501     c.community_local,
 502     c.community_name,
 503     c.banned,
 504     c.banned_from_community,
 505     c.creator_name,
 506     c.creator_avatar,
 507     c.score,
 508     c.upvotes,
 509     c.downvotes,
 510     c.hot_rank,
 511     c.user_id,
 512     c.my_vote,
 513     c.saved,
 514     um.recipient_id,
 515     (select actor_id from user_ u where u.id = um.recipient_id) as recipient_actor_id,
 516     (select local from user_ u where u.id = um.recipient_id) as recipient_local
 517 from user_mention um, comment_view c
 518 where um.comment_id = c.id;
 519
 520 create view user_mention_fast_view as
 521 select
 522     ac.id,
 523     um.id as user_mention_id,
 524     ac.creator_id,
 525     ac.creator_actor_id,
 526     ac.creator_local,
 527     ac.post_id,
 528     ac.post_name,
 529     ac.parent_id,
 530     ac.content,
 531     ac.removed,
 532     um.read,
 533     ac.published,
 534     ac.updated,
 535     ac.deleted,
 536     ac.community_id,
 537     ac.community_actor_id,
 538     ac.community_local,
 539     ac.community_name,
 540     ac.banned,
 541     ac.banned_from_community,
 542     ac.creator_name,
 543     ac.creator_avatar,
 544     ac.score,
 545     ac.upvotes,
 546     ac.downvotes,
 547     ac.hot_rank,
 548     u.id as user_id,
 549     coalesce(cl.score, 0) as my_vote,
 550     (select cs.id::bool from comment_saved cs where u.id = cs.user_id and cs.comment_id = ac.id) as saved,
 551     um.recipient_id,
 552     (select actor_id from user_ u where u.id = um.recipient_id) as recipient_actor_id,
 553     (select local from user_ u where u.id = um.recipient_id) as recipient_local
 554 from user_ u
 555 cross join (
 556   select
 557   ca.*
 558   from comment_aggregates_fast ca
 559 ) ac
 560 left join comment_like cl on u.id = cl.user_id and ac.id = cl.comment_id
 561 left join user_mention um on um.comment_id = ac.id
 562
 563 union all
 564
 565 select
 566     ac.id,
 567     um.id as user_mention_id,
 568     ac.creator_id,
 569     ac.creator_actor_id,
 570     ac.creator_local,
 571     ac.post_id,
 572     ac.post_name,
 573     ac.parent_id,
 574     ac.content,
 575     ac.removed,
 576     um.read,
 577     ac.published,
 578     ac.updated,
 579     ac.deleted,
 580     ac.community_id,
 581     ac.community_actor_id,
 582     ac.community_local,
 583     ac.community_name,
 584     ac.banned,
 585     ac.banned_from_community,
 586     ac.creator_name,
 587     ac.creator_avatar,
 588     ac.score,
 589     ac.upvotes,
 590     ac.downvotes,
 591     ac.hot_rank,
 592     null as user_id,
 593     null as my_vote,
 594     null as saved,
 595     um.recipient_id,
 596     (select actor_id from user_ u where u.id = um.recipient_id) as recipient_actor_id,
 597     (select local from user_ u where u.id = um.recipient_id) as recipient_local
 598 from comment_aggregates_fast ac
 599 left join user_mention um on um.comment_id = ac.id
 600 ;
 601
 602 -- Do the reply_view referencing the comment_fast_view
 603 create view reply_fast_view as
 604 with closereply as (
 605     select
 606     c2.id,
 607     c2.creator_id as sender_id,
 608     c.creator_id as recipient_id
 609     from comment c
 610     inner join comment c2 on c.id = c2.parent_id
 611     where c2.creator_id != c.creator_id
 612     -- Do union where post is null
 613     union
 614     select
 615     c.id,
 616     c.creator_id as sender_id,
 617     p.creator_id as recipient_id
 618     from comment c, post p
 619     where c.post_id = p.id and c.parent_id is null and c.creator_id != p.creator_id
 620 )
 621 select cv.*,
 622 closereply.recipient_id
 623 from comment_fast_view cv, closereply
 624 where closereply.id = cv.id
 625 ;
 626
 627 -- redoing the triggers
 628 create or replace function refresh_post()
 629 returns trigger language plpgsql
 630 as $$
 631 begin
 632   IF (TG_OP = 'DELETE') THEN
 633     delete from post_aggregates_fast where id = OLD.id;
 634
 635     -- Update community number of posts
 636     update community_aggregates_fast set number_of_posts = number_of_posts - 1 where id = OLD.community_id;
 637   ELSIF (TG_OP = 'UPDATE') THEN
 638     delete from post_aggregates_fast where id = OLD.id;
 639     insert into post_aggregates_fast select * from post_aggregates_view where id = NEW.id;
 640   ELSIF (TG_OP = 'INSERT') THEN
 641     insert into post_aggregates_fast select * from post_aggregates_view where id = NEW.id;
 642
 643     -- Update that users number of posts, post score
 644     delete from user_fast where id = NEW.creator_id;
 645     insert into user_fast select * from user_view where id = NEW.creator_id;
 646
 647     -- Update community number of posts
 648     update community_aggregates_fast set number_of_posts = number_of_posts + 1 where id = NEW.community_id;
 649
 650     -- Update the hot rank on the post table
 651     -- TODO this might not correctly update it, using a 1 week interval
 652     update post_aggregates_fast as paf
 653     set hot_rank = pav.hot_rank
 654     from post_aggregates_view as pav
 655     where paf.id = pav.id  and (pav.published > ('now'::timestamp - '1 week'::interval));
 656   END IF;
 657
 658   return null;
 659 end $$;
 660
 661 create or replace function refresh_comment()
 662 returns trigger language plpgsql
 663 as $$
 664 begin
 665   IF (TG_OP = 'DELETE') THEN
 666     delete from comment_aggregates_fast where id = OLD.id;
 667
 668     -- Update community number of comments
 669     update community_aggregates_fast as caf
 670     set number_of_comments = number_of_comments - 1
 671     from post as p
 672     where caf.id = p.community_id and p.id = OLD.post_id;
 673
 674   ELSIF (TG_OP = 'UPDATE') THEN
 675     delete from comment_aggregates_fast where id = OLD.id;
 676     insert into comment_aggregates_fast select * from comment_aggregates_view where id = NEW.id;
 677   ELSIF (TG_OP = 'INSERT') THEN
 678     insert into comment_aggregates_fast select * from comment_aggregates_view where id = NEW.id;
 679
 680     -- Update user view due to comment count
 681     update user_fast
 682     set number_of_comments = number_of_comments + 1
 683     where id = NEW.creator_id;
 684
 685     -- Update post view due to comment count, new comment activity time, but only on new posts
 686     -- TODO this could be done more efficiently
 687     delete from post_aggregates_fast where id = NEW.post_id;
 688     insert into post_aggregates_fast select * from post_aggregates_view where id = NEW.post_id;
 689
 690     -- Force the hot rank as zero on week-older posts
 691     update post_aggregates_fast as paf
 692     set hot_rank = 0
 693     where paf.id = NEW.post_id and (paf.published < ('now'::timestamp - '1 week'::interval));
 694
 695     -- Update community number of comments
 696     update community_aggregates_fast as caf
 697     set number_of_comments = number_of_comments + 1
 698     from post as p
 699     where caf.id = p.community_id and p.id = NEW.post_id;
 700
 701   END IF;
 702
 703   return null;
 704 end $$;