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
23 INNER JOIN user_ u ON u.id = pm.creator_id
24 INNER JOIN user_ u2 ON u2.id = pm.recipient_id;
26 CREATE MATERIALIZED VIEW private_message_mview AS
32 CREATE UNIQUE INDEX idx_private_message_mview_id ON private_message_mview (id);
34 -- Create the triggers
35 CREATE OR REPLACE FUNCTION refresh_private_message ()
40 REFRESH MATERIALIZED VIEW CONCURRENTLY private_message_mview;
45 CREATE TRIGGER refresh_private_message
46 AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE ON private_message
48 EXECUTE PROCEDURE refresh_private_message ();
50 -- Update user to include matrix id
52 ADD COLUMN matrix_user_id text UNIQUE;
54 DROP VIEW user_view CASCADE;
56 CREATE VIEW user_view AS
67 u.send_notifications_to_email,
75 p.creator_id = u.id) AS number_of_posts,
78 coalesce(sum(score), 0)
84 AND p.id = pl.post_id) AS post_score,
91 c.creator_id = u.id) AS number_of_comments,
94 coalesce(sum(score), 0)
100 AND c.id = cl.comment_id) AS comment_score
104 CREATE MATERIALIZED VIEW user_mview AS
110 CREATE UNIQUE INDEX idx_user_mview_id ON user_mview (id);
112 -- This is what a group pm table would look like
113 -- Not going to do it now because of the complications
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(),
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)