diff options
author | Dessalines <happydooby@gmail.com> | 2019-04-29 12:14:54 -0700 |
---|---|---|
committer | Dessalines <happydooby@gmail.com> | 2019-04-29 12:14:54 -0700 |
commit | ee99deceb1181cc79e9d2f2111a47ae7989359a8 (patch) | |
tree | 1c58906673d41712a440da063e4f4856070904de /server | |
parent | 6ba2dde9f4327706ee6785c2f7968c9d62922e20 (diff) |
Adding proper deletes for Post, Community, and Comments.
- Fixes #132
- Fixes #108
Diffstat (limited to 'server')
-rw-r--r-- | server/migrations/2019-04-29-175834_add_delete_columns/down.sql | 137 | ||||
-rw-r--r-- | server/migrations/2019-04-29-175834_add_delete_columns/up.sql | 141 | ||||
-rw-r--r-- | server/src/actions/comment.rs | 11 | ||||
-rw-r--r-- | server/src/actions/comment_view.rs | 10 | ||||
-rw-r--r-- | server/src/actions/community.rs | 8 | ||||
-rw-r--r-- | server/src/actions/community_view.rs | 3 | ||||
-rw-r--r-- | server/src/actions/moderator.rs | 3 | ||||
-rw-r--r-- | server/src/actions/post.rs | 9 | ||||
-rw-r--r-- | server/src/actions/post_view.rs | 14 | ||||
-rw-r--r-- | server/src/schema.rs | 3 | ||||
-rw-r--r-- | server/src/websocket_server/server.rs | 10 |
11 files changed, 342 insertions, 7 deletions
diff --git a/server/migrations/2019-04-29-175834_add_delete_columns/down.sql b/server/migrations/2019-04-29-175834_add_delete_columns/down.sql new file mode 100644 index 00000000..5e13295b --- /dev/null +++ b/server/migrations/2019-04-29-175834_add_delete_columns/down.sql @@ -0,0 +1,137 @@ +drop view reply_view; +drop view comment_view; +drop view community_view; +drop view post_view; +alter table community drop column deleted; +alter table post drop column deleted; +alter table comment drop column deleted; + +create view community_view as +with all_community as +( + select *, + (select name from user_ u where c.creator_id = u.id) as creator_name, + (select name from category ct where c.category_id = ct.id) as category_name, + (select count(*) from community_follower cf where cf.community_id = c.id) as number_of_subscribers, + (select count(*) from post p where p.community_id = c.id) as number_of_posts, + (select count(*) from comment co, post p where c.id = p.community_id and p.id = co.post_id) as number_of_comments + from community c +) + +select +ac.*, +u.id as user_id, +(select cf.id::boolean from community_follower cf where u.id = cf.user_id and ac.id = cf.community_id) as subscribed +from user_ u +cross join all_community ac + +union all + +select +ac.*, +null as user_id, +null as subscribed +from all_community ac +; + +create or replace view post_view as +with all_post as +( + select + p.*, + (select name from user_ where p.creator_id = user_.id) as creator_name, + (select name from community where p.community_id = community.id) as community_name, + (select removed from community c where p.community_id = c.id) as community_removed, + (select count(*) from comment where comment.post_id = p.id) as number_of_comments, + coalesce(sum(pl.score), 0) as score, + count (case when pl.score = 1 then 1 else null end) as upvotes, + count (case when pl.score = -1 then 1 else null end) as downvotes, + hot_rank(coalesce(sum(pl.score) , 0), p.published) as hot_rank + from post p + left join post_like pl on p.id = pl.post_id + group by p.id +) + +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 pr.id::bool from post_read pr where u.id = pr.user_id and pr.post_id = ap.id) as read, +(select ps.id::bool from post_saved ps where u.id = ps.user_id and ps.post_id = ap.id) as saved +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 + +union all + +select +ap.*, +null as user_id, +null as my_vote, +null as subscribed, +null as read, +null as saved +from all_post ap +; + +create view comment_view as +with all_comment as +( + select + c.*, + (select community_id from post p where p.id = c.post_id), + (select u.banned from user_ u where c.creator_id = u.id) as banned, + (select cb.id::bool from community_user_ban cb, post p where c.creator_id = cb.user_id and p.id = c.post_id and p.community_id = cb.community_id) as banned_from_community, + (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 + from comment c + left join comment_like cl on c.id = cl.comment_id + group by c.id +) + +select +ac.*, +u.id as user_id, +coalesce(cl.score, 0) as my_vote, +(select cs.id::bool from comment_saved cs where u.id = cs.user_id and cs.comment_id = ac.id) as saved +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 + +union all + +select + ac.*, + null as user_id, + null as my_vote, + null as saved +from all_comment ac +; + +create view reply_view as +with closereply as ( + select + c2.id, + c2.creator_id as sender_id, + c.creator_id as recipient_id + from comment c + inner join comment c2 on c.id = c2.parent_id + where c2.creator_id != c.creator_id + -- Do union where post is null + union + select + c.id, + c.creator_id as sender_id, + p.creator_id as recipient_id + from comment c, post p + where c.post_id = p.id and c.parent_id is null and c.creator_id != p.creator_id +) +select cv.*, +closereply.recipient_id +from comment_view cv, closereply +where closereply.id = cv.id +; + diff --git a/server/migrations/2019-04-29-175834_add_delete_columns/up.sql b/server/migrations/2019-04-29-175834_add_delete_columns/up.sql new file mode 100644 index 00000000..88432dda --- /dev/null +++ b/server/migrations/2019-04-29-175834_add_delete_columns/up.sql @@ -0,0 +1,141 @@ +alter table community add column deleted boolean default false not null; +alter table post add column deleted boolean default false not null; +alter table comment add column deleted boolean default false not null; + +-- The views +drop view community_view; + +create view community_view as +with all_community as +( + select *, + (select name from user_ u where c.creator_id = u.id) as creator_name, + (select name from category ct where c.category_id = ct.id) as category_name, + (select count(*) from community_follower cf where cf.community_id = c.id) as number_of_subscribers, + (select count(*) from post p where p.community_id = c.id) as number_of_posts, + (select count(*) from comment co, post p where c.id = p.community_id and p.id = co.post_id) as number_of_comments + from community c +) + +select +ac.*, +u.id as user_id, +(select cf.id::boolean from community_follower cf where u.id = cf.user_id and ac.id = cf.community_id) as subscribed +from user_ u +cross join all_community ac + +union all + +select +ac.*, +null as user_id, +null as subscribed +from all_community ac +; + + +drop view post_view; +create view post_view as +with all_post as +( + select + p.*, + (select name from user_ where p.creator_id = user_.id) as creator_name, + (select name from community where p.community_id = community.id) as community_name, + (select removed from community c where p.community_id = c.id) as community_removed, + (select deleted from community c where p.community_id = c.id) as community_deleted, + (select count(*) from comment where comment.post_id = p.id) as number_of_comments, + coalesce(sum(pl.score), 0) as score, + count (case when pl.score = 1 then 1 else null end) as upvotes, + count (case when pl.score = -1 then 1 else null end) as downvotes, + hot_rank(coalesce(sum(pl.score) , 0), p.published) as hot_rank + from post p + left join post_like pl on p.id = pl.post_id + group by p.id +) + +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 pr.id::bool from post_read pr where u.id = pr.user_id and pr.post_id = ap.id) as read, +(select ps.id::bool from post_saved ps where u.id = ps.user_id and ps.post_id = ap.id) as saved +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 + +union all + +select +ap.*, +null as user_id, +null as my_vote, +null as subscribed, +null as read, +null as saved +from all_post ap +; + +drop view reply_view; +drop view comment_view; +create view comment_view as +with all_comment as +( + select + c.*, + (select community_id from post p where p.id = c.post_id), + (select u.banned from user_ u where c.creator_id = u.id) as banned, + (select cb.id::bool from community_user_ban cb, post p where c.creator_id = cb.user_id and p.id = c.post_id and p.community_id = cb.community_id) as banned_from_community, + (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 + from comment c + left join comment_like cl on c.id = cl.comment_id + group by c.id +) + +select +ac.*, +u.id as user_id, +coalesce(cl.score, 0) as my_vote, +(select cs.id::bool from comment_saved cs where u.id = cs.user_id and cs.comment_id = ac.id) as saved +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 + +union all + +select + ac.*, + null as user_id, + null as my_vote, + null as saved +from all_comment ac +; + +create view reply_view as +with closereply as ( + select + c2.id, + c2.creator_id as sender_id, + c.creator_id as recipient_id + from comment c + inner join comment c2 on c.id = c2.parent_id + where c2.creator_id != c.creator_id + -- Do union where post is null + union + select + c.id, + c.creator_id as sender_id, + p.creator_id as recipient_id + from comment c, post p + where c.post_id = p.id and c.parent_id is null and c.creator_id != p.creator_id +) +select cv.*, +closereply.recipient_id +from comment_view cv, closereply +where closereply.id = cv.id +; + diff --git a/server/src/actions/comment.rs b/server/src/actions/comment.rs index 9bb6d018..4bbc7c04 100644 --- a/server/src/actions/comment.rs +++ b/server/src/actions/comment.rs @@ -25,7 +25,8 @@ pub struct Comment { pub removed: bool, pub read: bool, pub published: chrono::NaiveDateTime, - pub updated: Option<chrono::NaiveDateTime> + pub updated: Option<chrono::NaiveDateTime>, + pub deleted: bool, } #[derive(Insertable, AsChangeset, Clone)] @@ -37,7 +38,8 @@ pub struct CommentForm { pub content: String, pub removed: Option<bool>, pub read: Option<bool>, - pub updated: Option<chrono::NaiveDateTime> + pub updated: Option<chrono::NaiveDateTime>, + pub deleted: Option<bool>, } impl Crud<CommentForm> for Comment { @@ -186,6 +188,7 @@ mod tests { category_id: 1, creator_id: inserted_user.id, removed: None, + deleted: None, updated: None }; @@ -198,6 +201,7 @@ mod tests { body: None, community_id: inserted_community.id, removed: None, + deleted: None, locked: None, updated: None }; @@ -209,6 +213,7 @@ mod tests { creator_id: inserted_user.id, post_id: inserted_post.id, removed: None, + deleted: None, read: None, parent_id: None, updated: None @@ -222,6 +227,7 @@ mod tests { creator_id: inserted_user.id, post_id: inserted_post.id, removed: false, + deleted: false, read: false, parent_id: None, published: inserted_comment.published, @@ -234,6 +240,7 @@ mod tests { post_id: inserted_post.id, parent_id: Some(inserted_comment.id), removed: None, + deleted: None, read: None, updated: None }; diff --git a/server/src/actions/comment_view.rs b/server/src/actions/comment_view.rs index 85ddf587..eb6276cc 100644 --- a/server/src/actions/comment_view.rs +++ b/server/src/actions/comment_view.rs @@ -17,6 +17,7 @@ table! { read -> Bool, published -> Timestamp, updated -> Nullable<Timestamp>, + deleted -> Bool, community_id -> Int4, banned -> Bool, banned_from_community -> Bool, @@ -42,6 +43,7 @@ pub struct CommentView { pub read: bool, pub published: chrono::NaiveDateTime, pub updated: Option<chrono::NaiveDateTime>, + pub deleted: bool, pub community_id: i32, pub banned: bool, pub banned_from_community: bool, @@ -115,6 +117,7 @@ impl CommentView { _ => query.order_by(published.desc()) }; + // Note: deleted and removed comments are done on the front side query .limit(limit) .offset(offset) @@ -153,6 +156,7 @@ table! { read -> Bool, published -> Timestamp, updated -> Nullable<Timestamp>, + deleted -> Bool, community_id -> Int4, banned -> Bool, banned_from_community -> Bool, @@ -179,6 +183,7 @@ pub struct ReplyView { pub read: bool, pub published: chrono::NaiveDateTime, pub updated: Option<chrono::NaiveDateTime>, + pub deleted: bool, pub community_id: i32, pub banned: bool, pub banned_from_community: bool, @@ -275,6 +280,7 @@ mod tests { category_id: 1, creator_id: inserted_user.id, removed: None, + deleted: None, updated: None }; @@ -287,6 +293,7 @@ mod tests { body: None, community_id: inserted_community.id, removed: None, + deleted: None, locked: None, updated: None }; @@ -299,6 +306,7 @@ mod tests { post_id: inserted_post.id, parent_id: None, removed: None, + deleted: None, read: None, updated: None }; @@ -322,6 +330,7 @@ mod tests { community_id: inserted_community.id, parent_id: None, removed: false, + deleted: false, read: false, banned: false, banned_from_community: false, @@ -344,6 +353,7 @@ mod tests { community_id: inserted_community.id, parent_id: None, removed: false, + deleted: false, read: false, banned: false, banned_from_community: false, diff --git a/server/src/actions/community.rs b/server/src/actions/community.rs index 42c95c7d..db53ceb9 100644 --- a/server/src/actions/community.rs +++ b/server/src/actions/community.rs @@ -16,7 +16,8 @@ pub struct Community { pub creator_id: i32, pub removed: bool, pub published: chrono::NaiveDateTime, - pub updated: Option<chrono::NaiveDateTime> + pub updated: Option<chrono::NaiveDateTime>, + pub deleted: bool, } #[derive(Insertable, AsChangeset, Clone, Serialize, Deserialize)] @@ -28,7 +29,8 @@ pub struct CommunityForm { pub category_id: i32, pub creator_id: i32, pub removed: Option<bool>, - pub updated: Option<chrono::NaiveDateTime> + pub updated: Option<chrono::NaiveDateTime>, + pub deleted: Option<bool>, } impl Crud<CommunityForm> for Community { @@ -245,6 +247,7 @@ mod tests { description: None, category_id: 1, removed: None, + deleted: None, updated: None, }; @@ -258,6 +261,7 @@ mod tests { description: None, category_id: 1, removed: false, + deleted: false, published: inserted_community.published, updated: None }; diff --git a/server/src/actions/community_view.rs b/server/src/actions/community_view.rs index 8966ee15..a52897ff 100644 --- a/server/src/actions/community_view.rs +++ b/server/src/actions/community_view.rs @@ -15,6 +15,7 @@ table! { removed -> Bool, published -> Timestamp, updated -> Nullable<Timestamp>, + deleted -> Bool, creator_name -> Varchar, category_name -> Varchar, number_of_subscribers -> BigInt, @@ -85,6 +86,7 @@ pub struct CommunityView { pub removed: bool, pub published: chrono::NaiveDateTime, pub updated: Option<chrono::NaiveDateTime>, + pub deleted: bool, pub creator_name: String, pub category_name: String, pub number_of_subscribers: i64, @@ -139,6 +141,7 @@ impl CommunityView { .limit(limit) .offset(offset) .filter(removed.eq(false)) + .filter(deleted.eq(false)) .load::<Self>(conn) } } diff --git a/server/src/actions/moderator.rs b/server/src/actions/moderator.rs index a0d7db6c..794d91e7 100644 --- a/server/src/actions/moderator.rs +++ b/server/src/actions/moderator.rs @@ -442,6 +442,7 @@ mod tests { category_id: 1, creator_id: inserted_user.id, removed: None, + deleted: None, updated: None }; @@ -454,6 +455,7 @@ mod tests { creator_id: inserted_user.id, community_id: inserted_community.id, removed: None, + deleted: None, locked: None, updated: None }; @@ -465,6 +467,7 @@ mod tests { creator_id: inserted_user.id, post_id: inserted_post.id, removed: None, + deleted: None, read: None, parent_id: None, updated: None diff --git a/server/src/actions/post.rs b/server/src/actions/post.rs index 4dd4561d..495a8b09 100644 --- a/server/src/actions/post.rs +++ b/server/src/actions/post.rs @@ -17,7 +17,8 @@ pub struct Post { pub removed: bool, pub locked: bool, pub published: chrono::NaiveDateTime, - pub updated: Option<chrono::NaiveDateTime> + pub updated: Option<chrono::NaiveDateTime>, + pub deleted: bool, } #[derive(Insertable, AsChangeset, Clone)] @@ -30,7 +31,8 @@ pub struct PostForm { pub community_id: i32, pub removed: Option<bool>, pub locked: Option<bool>, - pub updated: Option<chrono::NaiveDateTime> + pub updated: Option<chrono::NaiveDateTime>, + pub deleted: Option<bool>, } impl Crud<PostForm> for Post { @@ -199,6 +201,7 @@ mod tests { category_id: 1, creator_id: inserted_user.id, removed: None, + deleted: None, updated: None }; @@ -211,6 +214,7 @@ mod tests { creator_id: inserted_user.id, community_id: inserted_community.id, removed: None, + deleted: None, locked: None, updated: None }; @@ -227,6 +231,7 @@ mod tests { published: inserted_post.published, removed: false, locked: false, + deleted: false, updated: None }; diff --git a/server/src/actions/post_view.rs b/server/src/actions/post_view.rs index e24b0ed2..18287651 100644 --- a/server/src/actions/post_view.rs +++ b/server/src/actions/post_view.rs @@ -23,9 +23,11 @@ table! { locked -> Bool, published -> Timestamp, updated -> Nullable<Timestamp>, + deleted -> Bool, creator_name -> Varchar, community_name -> Varchar, community_removed -> Bool, + community_deleted -> Bool, number_of_comments -> BigInt, score -> BigInt, upvotes -> BigInt, @@ -53,9 +55,11 @@ pub struct PostView { pub locked: bool, pub published: chrono::NaiveDateTime, pub updated: Option<chrono::NaiveDateTime>, + pub deleted: bool, pub creator_name: String, pub community_name: String, pub community_removed: bool, + pub community_deleted: bool, pub number_of_comments: i64, pub score: i64, pub upvotes: i64, @@ -144,7 +148,9 @@ impl PostView { .limit(limit) .offset(offset) .filter(removed.eq(false)) - .filter(community_removed.eq(false)); + .filter(deleted.eq(false)) + .filter(community_removed.eq(false)) + .filter(community_deleted.eq(false)); query.load::<Self>(conn) } @@ -206,6 +212,7 @@ mod tests { creator_id: inserted_user.id, category_id: 1, removed: None, + deleted: None, updated: None }; @@ -218,6 +225,7 @@ mod tests { creator_id: inserted_user.id, community_id: inserted_community.id, removed: None, + deleted: None, locked: None, updated: None }; @@ -258,9 +266,11 @@ mod tests { creator_name: user_name.to_owned(), community_id: inserted_community.id, removed: false, + deleted: false, locked: false, community_name: community_name.to_owned(), community_removed: false, + community_deleted: false, number_of_comments: 0, score: 1, upvotes: 1, @@ -281,12 +291,14 @@ mod tests { url: None, body: None, removed: false, + deleted: false, locked: false, creator_id: inserted_user.id, creator_name: user_name.to_owned(), community_id: inserted_community.id, community_name: community_name.to_owned(), community_removed: false, + community_deleted: false, number_of_comments: 0, score: 1, upvotes: 1, diff --git a/server/src/schema.rs b/server/src/schema.rs index 65c2ae55..27bc3f94 100644 --- a/server/src/schema.rs +++ b/server/src/schema.rs @@ -16,6 +16,7 @@ table! { read -> Bool, published -> Timestamp, updated -> Nullable<Timestamp>, + deleted -> Bool, } } @@ -50,6 +51,7 @@ table! { removed -> Bool, published -> Timestamp, updated -> Nullable<Timestamp>, + deleted -> Bool, } } @@ -182,6 +184,7 @@ table! { locked -> Bool, published -> Timestamp, updated -> Nullable<Timestamp>, + deleted -> Bool, } } diff --git a/server/src/websocket_server/server.rs b/server/src/websocket_server/server.rs index 3e361f69..fb551cb3 100644 --- a/server/src/websocket_server/server.rs +++ b/server/src/websocket_server/server.rs @@ -219,6 +219,7 @@ pub struct EditComment { creator_id: i32, post_id: i32, removed: Option<bool>, + deleted: Option<bool>, reason: Option<String>, read: Option<bool>, auth: String @@ -268,6 +269,7 @@ pub struct EditPost { url: Option<String>, body: Option<String>, removed: Option<bool>, + deleted: Option<bool>, locked: Option<bool>, reason: Option<String>, auth: String @@ -288,6 +290,7 @@ pub struct EditCommunity { description: Option<String>, category_id: i32, removed: Option<bool>, + deleted: Option<bool>, reason: Option<String>, expires: Option<i64>, auth: String @@ -912,6 +915,7 @@ impl Perform for CreateCommunity { category_id: self.category_id, creator_id: user_id, removed: None, + deleted: None, updated: None, }; @@ -1060,6 +1064,7 @@ impl Perform for CreatePost { community_id: self.community_id, creator_id: user_id, removed: None, + deleted: None, locked: None, updated: None }; @@ -1264,6 +1269,7 @@ impl Perform for CreateComment { post_id: self.post_id, creator_id: user_id, removed: None, + deleted: None, read: None, updated: None }; @@ -1380,6 +1386,7 @@ impl Perform for EditComment { post_id: self.post_id, creator_id: self.creator_id, removed: self.removed.to_owned(), + deleted: self.deleted.to_owned(), read: self.read.to_owned(), updated: if self.read.is_some() { orig_comment.updated } else {Some(naive_now())} }; @@ -1743,6 +1750,7 @@ impl Perform for EditPost { creator_id: self.creator_id.to_owned(), community_id: self.community_id, removed: self.removed.to_owned(), + deleted: self.deleted.to_owned(), locked: self.locked.to_owned(), updated: Some(naive_now()) }; @@ -1908,6 +1916,7 @@ impl Perform for EditCommunity { category_id: self.category_id.to_owned(), creator_id: user_id, removed: self.removed.to_owned(), + deleted: self.deleted.to_owned(), updated: Some(naive_now()) }; @@ -2747,6 +2756,7 @@ impl Perform for MarkAllAsRead { post_id: reply.to_owned().post_id, creator_id: reply.to_owned().creator_id, removed: None, + deleted: None, read: Some(true), updated: reply.to_owned().updated }; |