diff options
Diffstat (limited to 'server/migrations')
3 files changed, 13 insertions, 3 deletions
diff --git a/server/migrations/2019-02-27-170003_create_community/up.sql b/server/migrations/2019-02-27-170003_create_community/up.sql index f78486d5..46b4df52 100644 --- a/server/migrations/2019-02-27-170003_create_community/up.sql +++ b/server/migrations/2019-02-27-170003_create_community/up.sql @@ -31,8 +31,6 @@ insert into category (name) values ('Meta'), ('Other'); - - create table community ( id serial primary key, name varchar(20) not null unique, @@ -58,4 +56,4 @@ create table community_follower ( published timestamp not null default now() ); -insert into community (name, title, category_id, creator_id) values ('main', 'The default Community', 1, 1); +insert into community (name, title, category_id, creator_id) values ('main', 'The Default Community', 1, 1); diff --git a/server/migrations/2019-04-08-015947_create_user_view/down.sql b/server/migrations/2019-04-08-015947_create_user_view/down.sql new file mode 100644 index 00000000..c94d94c4 --- /dev/null +++ b/server/migrations/2019-04-08-015947_create_user_view/down.sql @@ -0,0 +1 @@ +drop view user_view; diff --git a/server/migrations/2019-04-08-015947_create_user_view/up.sql b/server/migrations/2019-04-08-015947_create_user_view/up.sql new file mode 100644 index 00000000..69d052de --- /dev/null +++ b/server/migrations/2019-04-08-015947_create_user_view/up.sql @@ -0,0 +1,11 @@ +create view user_view as +select id, +name, +fedi_name, +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; + |