1 -- Creating private message
2 create table private_message (
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,
7 deleted boolean default false not null,
8 read boolean default false not null,
9 published timestamp not null default now(),
13 -- Create the view and materialized view which has the avatar and creator name
14 create view private_message_view as
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;
25 create materialized view private_message_mview as select * from private_message_view;
27 create unique index idx_private_message_mview_id on private_message_mview (id);
29 -- Create the triggers
30 create or replace function refresh_private_message()
31 returns trigger language plpgsql
34 refresh materialized view concurrently private_message_mview;
38 create trigger refresh_private_message
39 after insert or update or delete or truncate
42 execute procedure refresh_private_message();
44 -- Update user to include matrix id
45 alter table user_ add column matrix_user_id text unique;
47 drop view user_view cascade;
48 create view user_view as
59 u.send_notifications_to_email,
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
67 create materialized view user_mview as select * from user_view;
69 create unique index idx_user_mview_id on user_mview (id);
71 -- This is what a group pm table would look like
72 -- Not going to do it now because of the complications
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(),
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)