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

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