migrations/2020-01-21-001001_create_private_message/down.sql

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