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

   1 -- Creating private message
   2 CREATE TABLE private_message (
   3     id serial PRIMARY KEY,
   4     creator_id int REFERENCES user_ ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
   5     recipient_id int REFERENCES user_ ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
   6     content text NOT NULL,
   7     deleted boolean DEFAULT FALSE NOT NULL,
   8     read boolean DEFAULT FALSE NOT NULL,
   9     published timestamp NOT NULL DEFAULT now(),
  10     updated timestamp
  11 );
  12
  13 -- Create the view and materialized view which has the avatar and creator name
  14 CREATE VIEW private_message_view AS
  15 SELECT
  16     pm.*,
  17     u.name AS creator_name,
  18     u.avatar AS creator_avatar,
  19     u2.name AS recipient_name,
  20     u2.avatar AS recipient_avatar
  21 FROM
  22     private_message pm
  23     INNER JOIN user_ u ON u.id = pm.creator_id
  24     INNER JOIN user_ u2 ON u2.id = pm.recipient_id;
  25
  26 CREATE MATERIALIZED VIEW private_message_mview AS
  27 SELECT
  28     *
  29 FROM
  30     private_message_view;
  31
  32 CREATE UNIQUE INDEX idx_private_message_mview_id ON private_message_mview (id);
  33
  34 -- Create the triggers
  35 CREATE OR REPLACE FUNCTION refresh_private_message ()
  36     RETURNS TRIGGER
  37     LANGUAGE plpgsql
  38     AS $$
  39 BEGIN
  40     REFRESH MATERIALIZED VIEW CONCURRENTLY private_message_mview;
  41     RETURN NULL;
  42 END
  43 $$;
  44
  45 CREATE TRIGGER refresh_private_message
  46     AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE ON private_message
  47     FOR EACH statement
  48     EXECUTE PROCEDURE refresh_private_message ();
  49
  50 -- Update user to include matrix id
  51 ALTER TABLE user_
  52     ADD COLUMN matrix_user_id text UNIQUE;
  53
  54 DROP VIEW user_view CASCADE;
  55
  56 CREATE VIEW user_view AS
  57 SELECT
  58     u.id,
  59     u.name,
  60     u.avatar,
  61     u.email,
  62     u.matrix_user_id,
  63     u.fedi_name,
  64     u.admin,
  65     u.banned,
  66     u.show_avatars,
  67     u.send_notifications_to_email,
  68     u.published,
  69     (
  70         SELECT
  71             count(*)
  72         FROM
  73             post p
  74         WHERE
  75             p.creator_id = u.id) AS number_of_posts,
  76     (
  77         SELECT
  78             coalesce(sum(score), 0)
  79         FROM
  80             post p,
  81             post_like pl
  82         WHERE
  83             u.id = p.creator_id
  84             AND p.id = pl.post_id) AS post_score,
  85     (
  86         SELECT
  87             count(*)
  88         FROM
  89             comment c
  90         WHERE
  91             c.creator_id = u.id) AS number_of_comments,
  92     (
  93         SELECT
  94             coalesce(sum(score), 0)
  95         FROM
  96             comment c,
  97             comment_like cl
  98         WHERE
  99             u.id = c.creator_id
 100             AND c.id = cl.comment_id) AS comment_score
 101 FROM
 102     user_ u;
 103
 104 CREATE MATERIALIZED VIEW user_mview AS
 105 SELECT
 106     *
 107 FROM
 108     user_view;
 109
 110 CREATE UNIQUE INDEX idx_user_mview_id ON user_mview (id);
 111
 112 -- This is what a group pm table would look like
 113 -- Not going to do it now because of the complications
 114 --
 115 -- create table private_message (
 116 --   id serial primary key,
 117 --   creator_id int references user_ on update cascade on delete cascade not null,
 118 --   content text not null,
 119 --   deleted boolean default false not null,
 120 --   published timestamp not null default now(),
 121 --   updated timestamp
 122 -- );
 123 --
 124 -- create table private_message_recipient (
 125 --   id serial primary key,
 126 --   private_message_id int references private_message on update cascade on delete cascade not null,
 127 --   recipient_id int references user_ on update cascade on delete cascade not null,
 128 --   read boolean default false not null,
 129 --   published timestamp not null default now(),
 130 --   unique(private_message_id, recipient_id)
 131 -- )