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_by(hot_rank.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     };
 266
 267     let inserted_user = User_::create(&conn, &new_user).unwrap();
 268
 269     let new_community = CommunityForm {
 270       name: "test community 5".to_string(),
 271       title: "nada".to_owned(),
 272       description: None,
 273       category_id: 1,
 274       creator_id: inserted_user.id,
 275       removed: None,
 276       deleted: None,
 277       updated: None
 278     };
 279
 280     let inserted_community = Community::create(&conn, &new_community).unwrap();
 281
 282     let new_post = PostForm {
 283       name: "A test post 2".into(),
 284       creator_id: inserted_user.id,
 285       url: None,
 286       body: None,
 287       community_id: inserted_community.id,
 288       removed: None,
 289       deleted: None,
 290       locked: None,
 291       updated: None
 292     };
 293
 294     let inserted_post = Post::create(&conn, &new_post).unwrap();
 295
 296     let comment_form = CommentForm {
 297       content: "A test comment 32".into(),
 298       creator_id: inserted_user.id,
 299       post_id: inserted_post.id,
 300       parent_id: None,
 301       removed: None,
 302       deleted: None,
 303       read: None,
 304       updated: None
 305     };
 306
 307     let inserted_comment = Comment::create(&conn, &comment_form).unwrap();
 308
 309     let comment_like_form = CommentLikeForm {
 310       comment_id: inserted_comment.id,
 311       post_id: inserted_post.id,
 312       user_id: inserted_user.id,
 313       score: 1
 314     };
 315
 316     let _inserted_comment_like = CommentLike::like(&conn, &comment_like_form).unwrap();
 317
 318     let expected_comment_view_no_user = CommentView {
 319       id: inserted_comment.id,
 320       content: "A test comment 32".into(),
 321       creator_id: inserted_user.id,
 322       post_id: inserted_post.id,
 323       community_id: inserted_community.id,
 324       parent_id: None,
 325       removed: false,
 326       deleted: false,
 327       read: false,
 328       banned: false,
 329       banned_from_community: false,
 330       published: inserted_comment.published,
 331       updated: None,
 332       creator_name: inserted_user.name.to_owned(),
 333       score: 1,
 334       downvotes: 0,
 335       upvotes: 1,
 336       user_id: None,
 337       my_vote: None,
 338       saved: None,
 339     };
 340
 341     let expected_comment_view_with_user = CommentView {
 342       id: inserted_comment.id,
 343       content: "A test comment 32".into(),
 344       creator_id: inserted_user.id,
 345       post_id: inserted_post.id,
 346       community_id: inserted_community.id,
 347       parent_id: None,
 348       removed: false,
 349       deleted: false,
 350       read: false,
 351       banned: false,
 352       banned_from_community: false,
 353       published: inserted_comment.published,
 354       updated: None,
 355       creator_name: inserted_user.name.to_owned(),
 356       score: 1,
 357       downvotes: 0,
 358       upvotes: 1,
 359       user_id: Some(inserted_user.id),
 360       my_vote: Some(1),
 361       saved: None,
 362     };
 363
 364     let read_comment_views_no_user = CommentView::list(
 365       &conn,
 366       &SortType::New,
 367       Some(inserted_post.id),
 368       None,
 369       None,
 370       None,
 371       false,
 372       None,
 373       None).unwrap();
 374     let read_comment_views_with_user = CommentView::list(
 375       &conn,
 376       &SortType::New,
 377       Some(inserted_post.id),
 378       None,
 379       None,
 380       Some(inserted_user.id),
 381       false,
 382       None,
 383       None).unwrap();
 384     let like_removed = CommentLike::remove(&conn, &comment_like_form).unwrap();
 385     let num_deleted = Comment::delete(&conn, inserted_comment.id).unwrap();
 386     Post::delete(&conn, inserted_post.id).unwrap();
 387     Community::delete(&conn, inserted_community.id).unwrap();
 388     User_::delete(&conn, inserted_user.id).unwrap();
 389
 390     assert_eq!(expected_comment_view_no_user, read_comment_views_no_user[0]);
 391     assert_eq!(expected_comment_view_with_user, read_comment_views_with_user[0]);
 392     assert_eq!(1, num_deleted);
 393     assert_eq!(1, like_removed);
 394   }
 395 }
 396