2 drop view user_view cascade;
5 add column fedi_name varchar(40) not null default 'http://fake.com';
8 add constraint user__name_fedi_name_key unique (name, fedi_name);
12 add constraint community_name_key unique (name);
15 create view user_view as
26 u.send_notifications_to_email,
28 (select count(*) from post p where p.creator_id = u.id) as number_of_posts,
29 (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,
30 (select count(*) from comment c where c.creator_id = u.id) as number_of_comments,
31 (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
34 create materialized view user_mview as select * from user_view;
36 create unique index idx_user_mview_id on user_mview (id);