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

   1 -- User table
   2 -- Need to regenerate user_view, user_mview
   3 DROP VIEW user_view CASCADE;
   4
   5 -- Remove the fedi_name constraint, drop that useless column
   6 ALTER TABLE user_
   7     DROP CONSTRAINT user__name_fedi_name_key;
   8
   9 ALTER TABLE user_
  10     DROP COLUMN fedi_name;
  11
  12 -- Community
  13 ALTER TABLE community
  14     DROP CONSTRAINT community_name_key;
  15
  16 CREATE VIEW user_view AS
  17 SELECT
  18     u.id,
  19     u.name,
  20     u.avatar,
  21     u.email,
  22     u.matrix_user_id,
  23     u.admin,
  24     u.banned,
  25     u.show_avatars,
  26     u.send_notifications_to_email,
  27     u.published,
  28     (
  29         SELECT
  30             count(*)
  31         FROM
  32             post p
  33         WHERE
  34             p.creator_id = u.id) AS number_of_posts,
  35     (
  36         SELECT
  37             coalesce(sum(score), 0)
  38         FROM
  39             post p,
  40             post_like pl
  41         WHERE
  42             u.id = p.creator_id
  43             AND p.id = pl.post_id) AS post_score,
  44     (
  45         SELECT
  46             count(*)
  47         FROM
  48             comment c
  49         WHERE
  50             c.creator_id = u.id) AS number_of_comments,
  51     (
  52         SELECT
  53             coalesce(sum(score), 0)
  54         FROM
  55             comment c,
  56             comment_like cl
  57         WHERE
  58             u.id = c.creator_id
  59             AND c.id = cl.comment_id) AS comment_score
  60 FROM
  61     user_ u;
  62
  63 CREATE MATERIALIZED VIEW user_mview AS
  64 SELECT
  65     *
  66 FROM
  67     user_view;
  68
  69 CREATE UNIQUE INDEX idx_user_mview_id ON user_mview (id);
  70