diff options
author | Dessalines <tyhou13@gmx.com> | 2020-04-07 10:54:15 -0400 |
---|---|---|
committer | Dessalines <tyhou13@gmx.com> | 2020-04-07 10:54:15 -0400 |
commit | 56947e771046117cc58c78023ba1c8f3a2486bdd (patch) | |
tree | 600b0c0fff1c273859bbc19491dd4b5c03dda3de /server/migrations | |
parent | 4fadc4d072aa50c1ebd65672214b601240f9e8c7 (diff) |
Removing community name unique constraint. Removing useless fedi_name column from user_table.
Diffstat (limited to 'server/migrations')
-rw-r--r-- | server/migrations/2020-04-07-135912_add_user_community_apub_constraints/down.sql | 36 | ||||
-rw-r--r-- | server/migrations/2020-04-07-135912_add_user_community_apub_constraints/up.sql | 38 |
2 files changed, 74 insertions, 0 deletions
diff --git a/server/migrations/2020-04-07-135912_add_user_community_apub_constraints/down.sql b/server/migrations/2020-04-07-135912_add_user_community_apub_constraints/down.sql new file mode 100644 index 00000000..faf24fdc --- /dev/null +++ b/server/migrations/2020-04-07-135912_add_user_community_apub_constraints/down.sql @@ -0,0 +1,36 @@ +-- User table +drop view user_view cascade; + +alter table user_ +add column fedi_name varchar(40) not null default 'changeme'; + +alter table user_ +add constraint user__name_fedi_name_key unique (name, fedi_name); + +-- Community +alter table community +add constraint community_name_key unique (name); + + +create view user_view as +select +u.id, +u.name, +u.avatar, +u.email, +u.matrix_user_id, +u.fedi_name, +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); diff --git a/server/migrations/2020-04-07-135912_add_user_community_apub_constraints/up.sql b/server/migrations/2020-04-07-135912_add_user_community_apub_constraints/up.sql new file mode 100644 index 00000000..de65191d --- /dev/null +++ b/server/migrations/2020-04-07-135912_add_user_community_apub_constraints/up.sql @@ -0,0 +1,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); + |