server/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 private_message pm
  22 inner join user_ u on u.id = pm.creator_id
  23 inner join user_ u2 on u2.id = pm.recipient_id;
  24
  25 create materialized view private_message_mview as select * from private_message_view;
  26
  27 create unique index idx_private_message_mview_id on private_message_mview (id);
  28
  29 -- Create the triggers
  30 create or replace function refresh_private_message()
  31 returns trigger language plpgsql
  32 as $$
  33 begin
  34   refresh materialized view concurrently private_message_mview;
  35   return null;
  36 end $$;
  37
  38 create trigger refresh_private_message
  39 after insert or update or delete or truncate
  40 on private_message
  41 for each statement
  42 execute procedure refresh_private_message();
  43
  44 -- Update user to include matrix id
  45 alter table user_ add column matrix_user_id text unique;
  46
  47 drop view user_view cascade;
  48 create view user_view as
  49 select
  50 u.id,
  51 u.name,
  52 u.avatar,
  53 u.email,
  54 u.matrix_user_id,
  55 u.fedi_name,
  56 u.admin,
  57 u.banned,
  58 u.show_avatars,
  59 u.send_notifications_to_email,
  60 u.published,
  61 (select count(*) from post p where p.creator_id = u.id) as number_of_posts,
  62 (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,
  63 (select count(*) from comment c where c.creator_id = u.id) as number_of_comments,
  64 (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
  65 from user_ u;
  66
  67 create materialized view user_mview as select * from user_view;
  68
  69 create unique index idx_user_mview_id on user_mview (id);
  70
  71 -- This is what a group pm table would look like
  72 -- Not going to do it now because of the complications
  73 --
  74 -- create table private_message (
  75 --   id serial primary key,
  76 --   creator_id int references user_ on update cascade on delete cascade not null,
  77 --   content text not null,
  78 --   deleted boolean default false not null,
  79 --   published timestamp not null default now(),
  80 --   updated timestamp
  81 -- );
  82 --
  83 -- create table private_message_recipient (
  84 --   id serial primary key,
  85 --   private_message_id int references private_message on update cascade on delete cascade not null,
  86 --   recipient_id int references user_ on update cascade on delete cascade not null,
  87 --   read boolean default false not null,
  88 --   published timestamp not null default now(),
  89 --   unique(private_message_id, recipient_id)
  90 -- )