1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
|
-- User table
-- Need to regenerate user_view, user_mview
drop view user_view cascade;
-- Remove the fedi_name constraint, drop that useless column
alter table user_
drop constraint user__name_fedi_name_key;
alter table user_
drop column fedi_name;
-- Community
alter table community
drop constraint community_name_key;
create view user_view as
select
u.id,
u.name,
u.avatar,
u.email,
u.matrix_user_id,
u.admin,
u.banned,
u.show_avatars,
u.send_notifications_to_email,
u.published,
(select count(*) from post p where p.creator_id = u.id) as number_of_posts,
(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,
(select count(*) from comment c where c.creator_id = u.id) as number_of_comments,
(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
from user_ u;
create materialized view user_mview as select * from user_view;
create unique index idx_user_mview_id on user_mview (id);
|
