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