server/migrations/2020-04-07-135912_add_user_community_apub_constraints/up.sql

   1 -- User table
   2
   3 -- Need to regenerate user_view, user_mview
   4 drop view user_view cascade;
   5
   6 -- Remove the fedi_name constraint, drop that useless column
   7 alter table user_
   8 drop constraint user__name_fedi_name_key;
   9
  10 alter table user_
  11 drop column fedi_name;
  12
  13 -- Community
  14 alter table community
  15 drop constraint community_name_key;
  16
  17 create view user_view as
  18 select
  19 u.id,
  20 u.name,
  21 u.avatar,
  22 u.email,
  23 u.matrix_user_id,
  24 u.admin,
  25 u.banned,
  26 u.show_avatars,
  27 u.send_notifications_to_email,
  28 u.published,
  29 (select count(*) from post p where p.creator_id = u.id) as number_of_posts,
  30 (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,
  31 (select count(*) from comment c where c.creator_id = u.id) as number_of_comments,
  32 (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
  33 from user_ u;
  34
  35 create materialized view user_mview as select * from user_view;
  36
  37 create unique index idx_user_mview_id on user_mview (id);
  38