server/src/db/comment_view.rs

   1 use super::*;
   2
   3 // The faked schema since diesel doesn't do views
   4 table! {
   5   comment_view (id) {
   6     id -> Int4,
   7     creator_id -> Int4,
   8     post_id -> Int4,
   9     parent_id -> Nullable<Int4>,
  10     content -> Text,
  11     removed -> Bool,
  12     read -> Bool,
  13     published -> Timestamp,
  14     updated -> Nullable<Timestamp>,
  15     deleted -> Bool,
  16     community_id -> Int4,
  17     banned -> Bool,
  18     banned_from_community -> Bool,
  19     creator_name -> Varchar,
  20     score -> BigInt,
  21     upvotes -> BigInt,
  22     downvotes -> BigInt,
  23     user_id -> Nullable<Int4>,
  24     my_vote -> Nullable<Int4>,
  25     saved -> Nullable<Bool>,
  26   }
  27 }
  28
  29 #[derive(
  30   Queryable, Identifiable, PartialEq, Debug, Serialize, Deserialize, QueryableByName, Clone,
  31 )]
  32 #[table_name = "comment_view"]
  33 pub struct CommentView {
  34   pub id: i32,
  35   pub creator_id: i32,
  36   pub post_id: i32,
  37   pub parent_id: Option<i32>,
  38   pub content: String,
  39   pub removed: bool,
  40   pub read: bool,
  41   pub published: chrono::NaiveDateTime,
  42   pub updated: Option<chrono::NaiveDateTime>,
  43   pub deleted: bool,
  44   pub community_id: i32,
  45   pub banned: bool,
  46   pub banned_from_community: bool,
  47   pub creator_name: String,
  48   pub score: i64,
  49   pub upvotes: i64,
  50   pub downvotes: i64,
  51   pub user_id: Option<i32>,
  52   pub my_vote: Option<i32>,
  53   pub saved: Option<bool>,
  54 }
  55
  56 impl CommentView {
  57   pub fn list(
  58     conn: &PgConnection,
  59     sort: &SortType,
  60     for_post_id: Option<i32>,
  61     for_creator_id: Option<i32>,
  62     search_term: Option<String>,
  63     my_user_id: Option<i32>,
  64     saved_only: bool,
  65     page: Option<i64>,
  66     limit: Option<i64>,
  67   ) -> Result<Vec<Self>, Error> {
  68     use super::comment_view::comment_view::dsl::*;
  69
  70     let (limit, offset) = limit_and_offset(page, limit);
  71
  72     // TODO no limits here?
  73     let mut query = comment_view.into_boxed();
  74
  75     // The view lets you pass a null user_id, if you're not logged in
  76     if let Some(my_user_id) = my_user_id {
  77       query = query.filter(user_id.eq(my_user_id));
  78     } else {
  79       query = query.filter(user_id.is_null());
  80     }
  81
  82     if let Some(for_creator_id) = for_creator_id {
  83       query = query.filter(creator_id.eq(for_creator_id));
  84     };
  85
  86     if let Some(for_post_id) = for_post_id {
  87       query = query.filter(post_id.eq(for_post_id));
  88     };
  89
  90     if let Some(search_term) = search_term {
  91       query = query.filter(content.ilike(fuzzy_search(&search_term)));
  92     };
  93
  94     if saved_only {
  95       query = query.filter(saved.eq(true));
  96     }
  97
  98     query = match sort {
  99       // SortType::Hot => query.order(hot_rank.desc(), published.desc()),
 100       SortType::New => query.order_by(published.desc()),
 101       SortType::TopAll => query.order_by(score.desc()),
 102       SortType::TopYear => query
 103         .filter(published.gt(now - 1.years()))
 104         .order_by(score.desc()),
 105       SortType::TopMonth => query
 106         .filter(published.gt(now - 1.months()))
 107         .order_by(score.desc()),
 108       SortType::TopWeek => query
 109         .filter(published.gt(now - 1.weeks()))
 110         .order_by(score.desc()),
 111       SortType::TopDay => query
 112         .filter(published.gt(now - 1.days()))
 113         .order_by(score.desc()),
 114       _ => query.order_by(published.desc()),
 115     };
 116
 117     // Note: deleted and removed comments are done on the front side
 118     query.limit(limit).offset(offset).load::<Self>(conn)
 119   }
 120
 121   pub fn read(
 122     conn: &PgConnection,
 123     from_comment_id: i32,
 124     my_user_id: Option<i32>,
 125   ) -> Result<Self, Error> {
 126     use super::comment_view::comment_view::dsl::*;
 127
 128     let mut query = comment_view.into_boxed();
 129
 130     // The view lets you pass a null user_id, if you're not logged in
 131     if let Some(my_user_id) = my_user_id {
 132       query = query.filter(user_id.eq(my_user_id));
 133     } else {
 134       query = query.filter(user_id.is_null());
 135     }
 136
 137     query = query
 138       .filter(id.eq(from_comment_id))
 139       .order_by(published.desc());
 140
 141     query.first::<Self>(conn)
 142   }
 143 }
 144
 145 // The faked schema since diesel doesn't do views
 146 table! {
 147   reply_view (id) {
 148     id -> Int4,
 149     creator_id -> Int4,
 150     post_id -> Int4,
 151     parent_id -> Nullable<Int4>,
 152     content -> Text,
 153     removed -> Bool,
 154     read -> Bool,
 155     published -> Timestamp,
 156     updated -> Nullable<Timestamp>,
 157     deleted -> Bool,
 158     community_id -> Int4,
 159     banned -> Bool,
 160     banned_from_community -> Bool,
 161     creator_name -> Varchar,
 162     score -> BigInt,
 163     upvotes -> BigInt,
 164     downvotes -> BigInt,
 165     user_id -> Nullable<Int4>,
 166     my_vote -> Nullable<Int4>,
 167     saved -> Nullable<Bool>,
 168     recipient_id -> Int4,
 169   }
 170 }
 171
 172 #[derive(
 173   Queryable, Identifiable, PartialEq, Debug, Serialize, Deserialize, QueryableByName, Clone,
 174 )]
 175 #[table_name = "reply_view"]
 176 pub struct ReplyView {
 177   pub id: i32,
 178   pub creator_id: i32,
 179   pub post_id: i32,
 180   pub parent_id: Option<i32>,
 181   pub content: String,
 182   pub removed: bool,
 183   pub read: bool,
 184   pub published: chrono::NaiveDateTime,
 185   pub updated: Option<chrono::NaiveDateTime>,
 186   pub deleted: bool,
 187   pub community_id: i32,
 188   pub banned: bool,
 189   pub banned_from_community: bool,
 190   pub creator_name: String,
 191   pub score: i64,
 192   pub upvotes: i64,
 193   pub downvotes: i64,
 194   pub user_id: Option<i32>,
 195   pub my_vote: Option<i32>,
 196   pub saved: Option<bool>,
 197   pub recipient_id: i32,
 198 }
 199
 200 impl ReplyView {
 201   pub fn get_replies(
 202     conn: &PgConnection,
 203     for_user_id: i32,
 204     sort: &SortType,
 205     unread_only: bool,
 206     page: Option<i64>,
 207     limit: Option<i64>,
 208   ) -> Result<Vec<Self>, Error> {
 209     use super::comment_view::reply_view::dsl::*;
 210
 211     let (limit, offset) = limit_and_offset(page, limit);
 212
 213     let mut query = reply_view.into_boxed();
 214
 215     query = query
 216       .filter(user_id.eq(for_user_id))
 217       .filter(recipient_id.eq(for_user_id));
 218
 219     if unread_only {
 220       query = query.filter(read.eq(false));
 221     }
 222
 223     query = match sort {
 224       // SortType::Hot => query.order_by(hot_rank.desc()),
 225       SortType::New => query.order_by(published.desc()),
 226       SortType::TopAll => query.order_by(score.desc()),
 227       SortType::TopYear => query
 228         .filter(published.gt(now - 1.years()))
 229         .order_by(score.desc()),
 230       SortType::TopMonth => query
 231         .filter(published.gt(now - 1.months()))
 232         .order_by(score.desc()),
 233       SortType::TopWeek => query
 234         .filter(published.gt(now - 1.weeks()))
 235         .order_by(score.desc()),
 236       SortType::TopDay => query
 237         .filter(published.gt(now - 1.days()))
 238         .order_by(score.desc()),
 239       _ => query.order_by(published.desc()),
 240     };
 241
 242     query.limit(limit).offset(offset).load::<Self>(conn)
 243   }
 244 }
 245
 246 #[cfg(test)]
 247 mod tests {
 248   use super::super::comment::*;
 249   use super::super::community::*;
 250   use super::super::post::*;
 251   use super::super::user::*;
 252   use super::*;
 253   #[test]
 254   fn test_crud() {
 255     let conn = establish_connection();
 256
 257     let new_user = UserForm {
 258       name: "timmy".into(),
 259       fedi_name: "rrf".into(),
 260       preferred_username: None,
 261       password_encrypted: "nope".into(),
 262       email: None,
 263       admin: false,
 264       banned: false,
 265       updated: None,
 266       show_nsfw: false,
 267       theme: "darkly".into(),
 268     };
 269
 270     let inserted_user = User_::create(&conn, &new_user).unwrap();
 271
 272     let new_community = CommunityForm {
 273       name: "test community 5".to_string(),
 274       title: "nada".to_owned(),
 275       description: None,
 276       category_id: 1,
 277       creator_id: inserted_user.id,
 278       removed: None,
 279       deleted: None,
 280       updated: None,
 281       nsfw: false,
 282     };
 283
 284     let inserted_community = Community::create(&conn, &new_community).unwrap();
 285
 286     let new_post = PostForm {
 287       name: "A test post 2".into(),
 288       creator_id: inserted_user.id,
 289       url: None,
 290       body: None,
 291       community_id: inserted_community.id,
 292       removed: None,
 293       deleted: None,
 294       locked: None,
 295       stickied: None,
 296       updated: None,
 297       nsfw: false,
 298     };
 299
 300     let inserted_post = Post::create(&conn, &new_post).unwrap();
 301
 302     let comment_form = CommentForm {
 303       content: "A test comment 32".into(),
 304       creator_id: inserted_user.id,
 305       post_id: inserted_post.id,
 306       parent_id: None,
 307       removed: None,
 308       deleted: None,
 309       read: None,
 310       updated: None,
 311     };
 312
 313     let inserted_comment = Comment::create(&conn, &comment_form).unwrap();
 314
 315     let comment_like_form = CommentLikeForm {
 316       comment_id: inserted_comment.id,
 317       post_id: inserted_post.id,
 318       user_id: inserted_user.id,
 319       score: 1,
 320     };
 321
 322     let _inserted_comment_like = CommentLike::like(&conn, &comment_like_form).unwrap();
 323
 324     let expected_comment_view_no_user = CommentView {
 325       id: inserted_comment.id,
 326       content: "A test comment 32".into(),
 327       creator_id: inserted_user.id,
 328       post_id: inserted_post.id,
 329       community_id: inserted_community.id,
 330       parent_id: None,
 331       removed: false,
 332       deleted: false,
 333       read: false,
 334       banned: false,
 335       banned_from_community: false,
 336       published: inserted_comment.published,
 337       updated: None,
 338       creator_name: inserted_user.name.to_owned(),
 339       score: 1,
 340       downvotes: 0,
 341       upvotes: 1,
 342       user_id: None,
 343       my_vote: None,
 344       saved: None,
 345     };
 346
 347     let expected_comment_view_with_user = CommentView {
 348       id: inserted_comment.id,
 349       content: "A test comment 32".into(),
 350       creator_id: inserted_user.id,
 351       post_id: inserted_post.id,
 352       community_id: inserted_community.id,
 353       parent_id: None,
 354       removed: false,
 355       deleted: false,
 356       read: false,
 357       banned: false,
 358       banned_from_community: false,
 359       published: inserted_comment.published,
 360       updated: None,
 361       creator_name: inserted_user.name.to_owned(),
 362       score: 1,
 363       downvotes: 0,
 364       upvotes: 1,
 365       user_id: Some(inserted_user.id),
 366       my_vote: Some(1),
 367       saved: None,
 368     };
 369
 370     let read_comment_views_no_user = CommentView::list(
 371       &conn,
 372       &SortType::New,
 373       Some(inserted_post.id),
 374       None,
 375       None,
 376       None,
 377       false,
 378       None,
 379       None,
 380     )
 381     .unwrap();
 382     let read_comment_views_with_user = CommentView::list(
 383       &conn,
 384       &SortType::New,
 385       Some(inserted_post.id),
 386       None,
 387       None,
 388       Some(inserted_user.id),
 389       false,
 390       None,
 391       None,
 392     )
 393     .unwrap();
 394     let like_removed = CommentLike::remove(&conn, &comment_like_form).unwrap();
 395     let num_deleted = Comment::delete(&conn, inserted_comment.id).unwrap();
 396     Post::delete(&conn, inserted_post.id).unwrap();
 397     Community::delete(&conn, inserted_community.id).unwrap();
 398     User_::delete(&conn, inserted_user.id).unwrap();
 399
 400     assert_eq!(expected_comment_view_no_user, read_comment_views_no_user[0]);
 401     assert_eq!(
 402       expected_comment_view_with_user,
 403       read_comment_views_with_user[0]
 404     );
 405     assert_eq!(1, num_deleted);
 406     assert_eq!(1, like_removed);
 407   }
 408 }