diff options
author | Dessalines <tyhou13@gmx.com> | 2019-04-15 16:12:06 -0700 |
---|---|---|
committer | Dessalines <tyhou13@gmx.com> | 2019-04-15 16:12:06 -0700 |
commit | e94885eb97b3240ed9cec7f97d0f405b2819e922 (patch) | |
tree | b407f0b6ed9be27682e3767271e34933c947cb2a /server/migrations | |
parent | 8590a612f633fe6ba8f8b18379a8a822a3b3019b (diff) |
Commiting before I lose everything. I'll do this properly in a merge
Diffstat (limited to 'server/migrations')
15 files changed, 150 insertions, 43 deletions
diff --git a/server/migrations/2019-02-26-002946_create_user/down.sql b/server/migrations/2019-02-26-002946_create_user/down.sql index 606be6e1..67a280d6 100644 --- a/server/migrations/2019-02-26-002946_create_user/down.sql +++ b/server/migrations/2019-02-26-002946_create_user/down.sql @@ -1 +1,2 @@ -drop table user_ +drop table user_ban; +drop table user_; diff --git a/server/migrations/2019-02-26-002946_create_user/up.sql b/server/migrations/2019-02-26-002946_create_user/up.sql index 80e6e92a..83112e9b 100644 --- a/server/migrations/2019-02-26-002946_create_user/up.sql +++ b/server/migrations/2019-02-26-002946_create_user/up.sql @@ -6,9 +6,18 @@ create table user_ ( password_encrypted text not null, email text unique, icon bytea, + admin boolean default false, + banned boolean default false, published timestamp not null default now(), updated timestamp, unique(name, fedi_name) ); +create table user_ban ( + id serial primary key, + user_id int references user_ on update cascade on delete cascade not null, + published timestamp not null default now(), + unique (user_id) +); + insert into user_ (name, fedi_name, password_encrypted) values ('admin', 'TBD', 'TBD'); diff --git a/server/migrations/2019-02-27-170003_create_community/down.sql b/server/migrations/2019-02-27-170003_create_community/down.sql index f293dfad..d5bd3994 100644 --- a/server/migrations/2019-02-27-170003_create_community/down.sql +++ b/server/migrations/2019-02-27-170003_create_community/down.sql @@ -1,3 +1,4 @@ +drop table community_user_ban;; drop table community_moderator; drop table community_follower; drop table community; 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 46b4df52..ad47adfe 100644 --- a/server/migrations/2019-02-27-170003_create_community/up.sql +++ b/server/migrations/2019-02-27-170003_create_community/up.sql @@ -38,6 +38,7 @@ create table community ( description text, category_id int references category on update cascade on delete cascade not null, creator_id int references user_ on update cascade on delete cascade not null, + removed boolean default false, published timestamp not null default now(), updated timestamp ); @@ -46,14 +47,24 @@ create table community_moderator ( id serial primary key, community_id int references community on update cascade on delete cascade not null, user_id int references user_ on update cascade on delete cascade not null, - published timestamp not null default now() + published timestamp not null default now(), + unique (community_id, user_id) ); create table community_follower ( id serial primary key, community_id int references community on update cascade on delete cascade not null, user_id int references user_ on update cascade on delete cascade not null, - published timestamp not null default now() + published timestamp not null default now(), + unique (community_id, user_id) +); + +create table community_user_ban ( + id serial primary key, + community_id int references community on update cascade on delete cascade not null, + user_id int references user_ on update cascade on delete cascade not null, + published timestamp not null default now(), + unique (community_id, user_id) ); insert into community (name, title, category_id, creator_id) values ('main', 'The Default Community', 1, 1); diff --git a/server/migrations/2019-03-03-163336_create_post/up.sql b/server/migrations/2019-03-03-163336_create_post/up.sql index aaa6911e..c3b7c0b8 100644 --- a/server/migrations/2019-03-03-163336_create_post/up.sql +++ b/server/migrations/2019-03-03-163336_create_post/up.sql @@ -5,6 +5,8 @@ create table post ( body text, creator_id int references user_ on update cascade on delete cascade not null, community_id int references community on update cascade on delete cascade not null, + removed boolean default false, + locked boolean default false, published timestamp not null default now(), updated timestamp ); diff --git a/server/migrations/2019-03-05-233828_create_comment/up.sql b/server/migrations/2019-03-05-233828_create_comment/up.sql index aa20d358..214d50a6 100644 --- a/server/migrations/2019-03-05-233828_create_comment/up.sql +++ b/server/migrations/2019-03-05-233828_create_comment/up.sql @@ -4,6 +4,7 @@ create table comment ( post_id int references post on update cascade on delete cascade not null, parent_id int references comment on update cascade on delete cascade, content text not null, + removed boolean default false, published timestamp not null default now(), updated timestamp ); diff --git a/server/migrations/2019-03-30-212058_create_post_view/up.sql b/server/migrations/2019-03-30-212058_create_post_view/up.sql index 95789c73..ecf3280a 100644 --- a/server/migrations/2019-03-30-212058_create_post_view/up.sql +++ b/server/migrations/2019-03-30-212058_create_post_view/up.sql @@ -30,7 +30,8 @@ select ap.*, u.id as user_id, coalesce(pl.score, 0) as my_vote, -(select cf.id::bool from community_follower cf where u.id = cf.user_id and cf.community_id = ap.community_id) as subscribed +(select cf.id::bool from community_follower cf where u.id = cf.user_id and cf.community_id = ap.community_id) as subscribed, +u.admin or (select cm.id::bool from community_moderator cm where u.id = cm.user_id and cm.community_id = ap.community_id) as am_mod from user_ u cross join all_post ap left join post_like pl on u.id = pl.user_id and ap.id = pl.post_id @@ -41,33 +42,7 @@ select ap.*, null as user_id, null as my_vote, -null as subscribed +null as subscribed, +null as am_mod from all_post ap ; - -/* The old post view */ -/* create view post_view as */ -/* select */ -/* u.id as user_id, */ -/* pl.score as my_vote, */ -/* p.id as id, */ -/* p.name as name, */ -/* p.url, */ -/* p.body, */ -/* p.creator_id, */ -/* (select name from user_ where p.creator_id = user_.id) creator_name, */ -/* p.community_id, */ -/* (select name from community where p.community_id = community.id) as community_name, */ -/* (select count(*) from comment where comment.post_id = p.id) as number_of_comments, */ -/* coalesce(sum(pl.score) over (partition by p.id), 0) as score, */ -/* count (case when pl.score = 1 then 1 else null end) over (partition by p.id) as upvotes, */ -/* count (case when pl.score = -1 then 1 else null end) over (partition by p.id) as downvotes, */ -/* hot_rank(coalesce(sum(pl.score) over (partition by p.id) , 0), p.published) as hot_rank, */ -/* p.published, */ -/* p.updated */ -/* from user_ u */ -/* cross join post p */ -/* left join post_like pl on u.id = pl.user_id and p.id = pl.post_id; */ - - - diff --git a/server/migrations/2019-04-03-155205_create_community_view/down.sql b/server/migrations/2019-04-03-155205_create_community_view/down.sql index 6c7e8708..0c7a33c8 100644 --- a/server/migrations/2019-04-03-155205_create_community_view/down.sql +++ b/server/migrations/2019-04-03-155205_create_community_view/down.sql @@ -1,3 +1,4 @@ drop view community_view; drop view community_moderator_view; drop view community_follower_view; +drop view community_user_ban_view; diff --git a/server/migrations/2019-04-03-155205_create_community_view/up.sql b/server/migrations/2019-04-03-155205_create_community_view/up.sql index 7c608742..510fd0f2 100644 --- a/server/migrations/2019-04-03-155205_create_community_view/up.sql +++ b/server/migrations/2019-04-03-155205_create_community_view/up.sql @@ -13,7 +13,8 @@ with all_community as select ac.*, u.id as user_id, -cf.id::boolean as subscribed +cf.id::boolean as subscribed, +u.admin or (select cm.id::bool from community_moderator cm where u.id = cm.user_id and cm.community_id = ac.id) as am_mod from user_ u cross join all_community ac left join community_follower cf on u.id = cf.user_id and ac.id = cf.community_id @@ -23,7 +24,8 @@ union all select ac.*, null as user_id, -null as subscribed +null as subscribed, +null as am_mod from all_community ac ; @@ -38,3 +40,9 @@ select *, (select name from user_ u where cf.user_id = u.id) as user_name, (select name from community c where cf.community_id = c.id) as community_name from community_follower cf; + +create view community_user_ban_view as +select *, +(select name from user_ u where cm.user_id = u.id) as user_name, +(select name from community c where cm.community_id = c.id) as community_name +from community_user_ban cm; diff --git a/server/migrations/2019-04-03-155309_create_comment_view/up.sql b/server/migrations/2019-04-03-155309_create_comment_view/up.sql index a4d2be9f..a73b6182 100644 --- a/server/migrations/2019-04-03-155309_create_comment_view/up.sql +++ b/server/migrations/2019-04-03-155309_create_comment_view/up.sql @@ -3,7 +3,9 @@ with all_comment as ( select c.*, - (select name from user_ where c.creator_id = user_.id) creator_name, + (select community_id from post p where p.id = c.post_id), + (select cb.id::bool from community_user_ban cb where c.creator_id = cb.user_id) as banned, + (select name from user_ where c.creator_id = user_.id) as creator_name, coalesce(sum(cl.score), 0) as score, count (case when cl.score = 1 then 1 else null end) as upvotes, count (case when cl.score = -1 then 1 else null end) as downvotes @@ -15,7 +17,8 @@ with all_comment as select ac.*, u.id as user_id, -coalesce(cl.score, 0) as my_vote +coalesce(cl.score, 0) as my_vote, +u.admin or (select cm.id::bool from community_moderator cm, post p where u.id = cm.user_id and ac.post_id = p.id and p.community_id = cm.community_id) as am_mod from user_ u cross join all_comment ac left join comment_like cl on u.id = cl.user_id and ac.id = cl.comment_id @@ -25,6 +28,7 @@ union all select ac.*, null as user_id, - null as my_vote + null as my_vote, + null as am_mod from all_comment ac ; diff --git a/server/migrations/2019-04-07-003142_create_moderation_logs/down.sql b/server/migrations/2019-04-07-003142_create_moderation_logs/down.sql index 15718917..888a87fe 100644 --- a/server/migrations/2019-04-07-003142_create_moderation_logs/down.sql +++ b/server/migrations/2019-04-07-003142_create_moderation_logs/down.sql @@ -3,4 +3,6 @@ drop table mod_lock_post; drop table mod_remove_comment; drop table mod_remove_community; drop table mod_ban; -drop table mod_add_mod; +drop table mod_ban_from_community; +drop table mod_add; +drop table mod_add_community; diff --git a/server/migrations/2019-04-07-003142_create_moderation_logs/up.sql b/server/migrations/2019-04-07-003142_create_moderation_logs/up.sql index 41929e50..3b320d81 100644 --- a/server/migrations/2019-04-07-003142_create_moderation_logs/up.sql +++ b/server/migrations/2019-04-07-003142_create_moderation_logs/up.sql @@ -1,4 +1,3 @@ - create table mod_remove_post ( id serial primary key, mod_user_id int references user_ on update cascade on delete cascade not null, @@ -12,6 +11,7 @@ create table mod_lock_post ( id serial primary key, mod_user_id int references user_ on update cascade on delete cascade not null, post_id int references post on update cascade on delete cascade not null, + locked boolean default true, when_ timestamp not null default now() ); @@ -30,25 +30,47 @@ create table mod_remove_community ( community_id int references community on update cascade on delete cascade not null, reason text, removed boolean default true, + expires timestamp, when_ timestamp not null default now() ); -- TODO make sure you can't ban other mods +create table mod_ban_from_community ( + id serial primary key, + mod_user_id int references user_ on update cascade on delete cascade not null, + other_user_id int references user_ on update cascade on delete cascade not null, + community_id int references community on update cascade on delete cascade not null, + reason text, + banned boolean default true, + expires timestamp, + when_ timestamp not null default now() +); + create table mod_ban ( id serial primary key, mod_user_id int references user_ on update cascade on delete cascade not null, other_user_id int references user_ on update cascade on delete cascade not null, reason text, - removed boolean default true, + banned boolean default true, expires timestamp, when_ timestamp not null default now() ); +create table mod_add_community ( + id serial primary key, + mod_user_id int references user_ on update cascade on delete cascade not null, + other_user_id int references user_ on update cascade on delete cascade not null, + community_id int references community on update cascade on delete cascade not null, + removed boolean default false, + when_ timestamp not null default now() +); + -- When removed is false that means kicked -create table mod_add_mod ( +create table mod_add ( id serial primary key, mod_user_id int references user_ on update cascade on delete cascade not null, other_user_id int references user_ on update cascade on delete cascade not null, removed boolean default false, when_ timestamp not null default now() -) +); + 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 index 69d052de..08eb56ca 100644 --- a/server/migrations/2019-04-08-015947_create_user_view/up.sql +++ b/server/migrations/2019-04-08-015947_create_user_view/up.sql @@ -2,10 +2,11 @@ create view user_view as select id, name, fedi_name, +admin, +banned, 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; - diff --git a/server/migrations/2019-04-11-144915_create_mod_views/down.sql b/server/migrations/2019-04-11-144915_create_mod_views/down.sql new file mode 100644 index 00000000..95018f35 --- /dev/null +++ b/server/migrations/2019-04-11-144915_create_mod_views/down.sql @@ -0,0 +1,8 @@ +drop view mod_remove_post_view; +drop view mod_lock_post_view; +drop view mod_remove_comment_view; +drop view mod_remove_community_view; +drop view mod_ban_from_community_view; +drop view mod_ban_view; +drop view mod_add_community_view; +drop view mod_add_view; diff --git a/server/migrations/2019-04-11-144915_create_mod_views/up.sql b/server/migrations/2019-04-11-144915_create_mod_views/up.sql new file mode 100644 index 00000000..908028d0 --- /dev/null +++ b/server/migrations/2019-04-11-144915_create_mod_views/up.sql @@ -0,0 +1,61 @@ +create view mod_remove_post_view as +select mrp.*, +(select name from user_ u where mrp.mod_user_id = u.id) as mod_user_name, +(select name from post p where mrp.post_id = p.id) as post_name, +(select c.id from post p, community c where mrp.post_id = p.id and p.community_id = c.id) as community_id, +(select c.name from post p, community c where mrp.post_id = p.id and p.community_id = c.id) as community_name +from mod_remove_post mrp; + +create view mod_lock_post_view as +select mlp.*, +(select name from user_ u where mlp.mod_user_id = u.id) as mod_user_name, +(select name from post p where mlp.post_id = p.id) as post_name, +(select c.id from post p, community c where mlp.post_id = p.id and p.community_id = c.id) as community_id, +(select c.name from post p, community c where mlp.post_id = p.id and p.community_id = c.id) as community_name +from mod_lock_post mlp; + +create view mod_remove_comment_view as +select mrc.*, +(select name from user_ u where mrc.mod_user_id = u.id) as mod_user_name, +(select c.id from comment c where mrc.comment_id = c.id) as comment_user_id, +(select name from user_ u, comment c where mrc.comment_id = c.id and u.id = c.creator_id) as comment_user_name, +(select content from comment c where mrc.comment_id = c.id) as comment_content, +(select p.id from post p, comment c where mrc.comment_id = c.id and c.post_id = p.id) as post_id, +(select p.name from post p, comment c where mrc.comment_id = c.id and c.post_id = p.id) as post_name, +(select co.id from comment c, post p, community co where mrc.comment_id = c.id and c.post_id = p.id and p.community_id = co.id) as community_id, +(select co.name from comment c, post p, community co where mrc.comment_id = c.id and c.post_id = p.id and p.community_id = co.id) as community_name +from mod_remove_comment mrc; + +create view mod_remove_community_view as +select mrc.*, +(select name from user_ u where mrc.mod_user_id = u.id) as mod_user_name, +(select c.name from community c where mrc.community_id = c.id) as community_name +from mod_remove_community mrc; + +create view mod_ban_from_community_view as +select mb.*, +(select name from user_ u where mb.mod_user_id = u.id) as mod_user_name, +(select name from user_ u where mb.other_user_id = u.id) as other_user_name, +(select name from community c where mb.community_id = c.id) as community_name +from mod_ban_from_community mb; + +create view mod_ban_view as +select mb.*, +(select name from user_ u where mb.mod_user_id = u.id) as mod_user_name, +(select name from user_ u where mb.other_user_id = u.id) as other_user_name +from mod_ban_from_community mb; + + +create view mod_add_community_view as +select ma.*, +(select name from user_ u where ma.mod_user_id = u.id) as mod_user_name, +(select name from user_ u where ma.other_user_id = u.id) as other_user_name, +(select name from community c where ma.community_id = c.id) as community_name +from mod_add_community ma; + + +create view mod_add_view as +select ma.*, +(select name from user_ u where ma.mod_user_id = u.id) as mod_user_name, +(select name from user_ u where ma.other_user_id = u.id) as other_user_name +from mod_add ma; |