server/src/db/comment_view.rs

   1 use super::*;
   2 use diesel::pg::Pg;
   3
   4 // The faked schema since diesel doesn't do views
   5 table! {
   6   comment_view (id) {
   7     id -> Int4,
   8     creator_id -> Int4,
   9     post_id -> Int4,
  10     parent_id -> Nullable<Int4>,
  11     content -> Text,
  12     removed -> Bool,
  13     read -> Bool,
  14     published -> Timestamp,
  15     updated -> Nullable<Timestamp>,
  16     deleted -> Bool,
  17     ap_id -> Text,
  18     local -> Bool,
  19     community_id -> Int4,
  20     community_actor_id -> Text,
  21     community_local -> Bool,
  22     community_name -> Varchar,
  23     banned -> Bool,
  24     banned_from_community -> Bool,
  25     creator_actor_id -> Text,
  26     creator_local -> Bool,
  27     creator_name -> Varchar,
  28     creator_avatar -> Nullable<Text>,
  29     score -> BigInt,
  30     upvotes -> BigInt,
  31     downvotes -> BigInt,
  32     hot_rank -> Int4,
  33     user_id -> Nullable<Int4>,
  34     my_vote -> Nullable<Int4>,
  35     subscribed -> Nullable<Bool>,
  36     saved -> Nullable<Bool>,
  37   }
  38 }
  39
  40 table! {
  41   comment_mview (id) {
  42     id -> Int4,
  43     creator_id -> Int4,
  44     post_id -> Int4,
  45     parent_id -> Nullable<Int4>,
  46     content -> Text,
  47     removed -> Bool,
  48     read -> Bool,
  49     published -> Timestamp,
  50     updated -> Nullable<Timestamp>,
  51     deleted -> Bool,
  52     ap_id -> Text,
  53     local -> Bool,
  54     community_id -> Int4,
  55     community_actor_id -> Text,
  56     community_local -> Bool,
  57     community_name -> Varchar,
  58     banned -> Bool,
  59     banned_from_community -> Bool,
  60     creator_actor_id -> Text,
  61     creator_local -> Bool,
  62     creator_name -> Varchar,
  63     creator_avatar -> Nullable<Text>,
  64     score -> BigInt,
  65     upvotes -> BigInt,
  66     downvotes -> BigInt,
  67     hot_rank -> Int4,
  68     user_id -> Nullable<Int4>,
  69     my_vote -> Nullable<Int4>,
  70     subscribed -> Nullable<Bool>,
  71     saved -> Nullable<Bool>,
  72   }
  73 }
  74
  75 #[derive(
  76   Queryable, Identifiable, PartialEq, Debug, Serialize, Deserialize, QueryableByName, Clone,
  77 )]
  78 #[table_name = "comment_view"]
  79 pub struct CommentView {
  80   pub id: i32,
  81   pub creator_id: i32,
  82   pub post_id: i32,
  83   pub parent_id: Option<i32>,
  84   pub content: String,
  85   pub removed: bool,
  86   pub read: bool,
  87   pub published: chrono::NaiveDateTime,
  88   pub updated: Option<chrono::NaiveDateTime>,
  89   pub deleted: bool,
  90   pub ap_id: String,
  91   pub local: bool,
  92   pub community_id: i32,
  93   pub community_actor_id: String,
  94   pub community_local: bool,
  95   pub community_name: String,
  96   pub banned: bool,
  97   pub banned_from_community: bool,
  98   pub creator_actor_id: String,
  99   pub creator_local: bool,
 100   pub creator_name: String,
 101   pub creator_avatar: Option<String>,
 102   pub score: i64,
 103   pub upvotes: i64,
 104   pub downvotes: i64,
 105   pub hot_rank: i32,
 106   pub user_id: Option<i32>,
 107   pub my_vote: Option<i32>,
 108   pub subscribed: Option<bool>,
 109   pub saved: Option<bool>,
 110 }
 111
 112 pub struct CommentQueryBuilder<'a> {
 113   conn: &'a PgConnection,
 114   query: super::comment_view::comment_mview::BoxedQuery<'a, Pg>,
 115   listing_type: ListingType,
 116   sort: &'a SortType,
 117   for_community_id: Option<i32>,
 118   for_post_id: Option<i32>,
 119   for_creator_id: Option<i32>,
 120   search_term: Option<String>,
 121   my_user_id: Option<i32>,
 122   saved_only: bool,
 123   page: Option<i64>,
 124   limit: Option<i64>,
 125 }
 126
 127 impl<'a> CommentQueryBuilder<'a> {
 128   pub fn create(conn: &'a PgConnection) -> Self {
 129     use super::comment_view::comment_mview::dsl::*;
 130
 131     let query = comment_mview.into_boxed();
 132
 133     CommentQueryBuilder {
 134       conn,
 135       query,
 136       listing_type: ListingType::All,
 137       sort: &SortType::New,
 138       for_community_id: None,
 139       for_post_id: None,
 140       for_creator_id: None,
 141       search_term: None,
 142       my_user_id: None,
 143       saved_only: false,
 144       page: None,
 145       limit: None,
 146     }
 147   }
 148
 149   pub fn listing_type(mut self, listing_type: ListingType) -> Self {
 150     self.listing_type = listing_type;
 151     self
 152   }
 153
 154   pub fn sort(mut self, sort: &'a SortType) -> Self {
 155     self.sort = sort;
 156     self
 157   }
 158
 159   pub fn for_post_id<T: MaybeOptional<i32>>(mut self, for_post_id: T) -> Self {
 160     self.for_post_id = for_post_id.get_optional();
 161     self
 162   }
 163
 164   pub fn for_creator_id<T: MaybeOptional<i32>>(mut self, for_creator_id: T) -> Self {
 165     self.for_creator_id = for_creator_id.get_optional();
 166     self
 167   }
 168
 169   pub fn for_community_id<T: MaybeOptional<i32>>(mut self, for_community_id: T) -> Self {
 170     self.for_community_id = for_community_id.get_optional();
 171     self
 172   }
 173
 174   pub fn search_term<T: MaybeOptional<String>>(mut self, search_term: T) -> Self {
 175     self.search_term = search_term.get_optional();
 176     self
 177   }
 178
 179   pub fn my_user_id<T: MaybeOptional<i32>>(mut self, my_user_id: T) -> Self {
 180     self.my_user_id = my_user_id.get_optional();
 181     self
 182   }
 183
 184   pub fn saved_only(mut self, saved_only: bool) -> Self {
 185     self.saved_only = saved_only;
 186     self
 187   }
 188
 189   pub fn page<T: MaybeOptional<i64>>(mut self, page: T) -> Self {
 190     self.page = page.get_optional();
 191     self
 192   }
 193
 194   pub fn limit<T: MaybeOptional<i64>>(mut self, limit: T) -> Self {
 195     self.limit = limit.get_optional();
 196     self
 197   }
 198
 199   pub fn list(self) -> Result<Vec<CommentView>, Error> {
 200     use super::comment_view::comment_mview::dsl::*;
 201
 202     let mut query = self.query;
 203
 204     // The view lets you pass a null user_id, if you're not logged in
 205     if let Some(my_user_id) = self.my_user_id {
 206       query = query.filter(user_id.eq(my_user_id));
 207     } else {
 208       query = query.filter(user_id.is_null());
 209     }
 210
 211     if let Some(for_creator_id) = self.for_creator_id {
 212       query = query.filter(creator_id.eq(for_creator_id));
 213     };
 214
 215     if let Some(for_community_id) = self.for_community_id {
 216       query = query.filter(community_id.eq(for_community_id));
 217     }
 218
 219     if let Some(for_post_id) = self.for_post_id {
 220       query = query.filter(post_id.eq(for_post_id));
 221     };
 222
 223     if let Some(search_term) = self.search_term {
 224       query = query.filter(content.ilike(fuzzy_search(&search_term)));
 225     };
 226
 227     if let ListingType::Subscribed = self.listing_type {
 228       query = query.filter(subscribed.eq(true));
 229     }
 230
 231     if self.saved_only {
 232       query = query.filter(saved.eq(true));
 233     }
 234
 235     query = match self.sort {
 236       SortType::Hot => query
 237         .order_by(hot_rank.desc())
 238         .then_order_by(published.desc()),
 239       SortType::New => query.order_by(published.desc()),
 240       SortType::TopAll => query.order_by(score.desc()),
 241       SortType::TopYear => query
 242         .filter(published.gt(now - 1.years()))
 243         .order_by(score.desc()),
 244       SortType::TopMonth => query
 245         .filter(published.gt(now - 1.months()))
 246         .order_by(score.desc()),
 247       SortType::TopWeek => query
 248         .filter(published.gt(now - 1.weeks()))
 249         .order_by(score.desc()),
 250       SortType::TopDay => query
 251         .filter(published.gt(now - 1.days()))
 252         .order_by(score.desc()),
 253       // _ => query.order_by(published.desc()),
 254     };
 255
 256     let (limit, offset) = limit_and_offset(self.page, self.limit);
 257
 258     // Note: deleted and removed comments are done on the front side
 259     query
 260       .limit(limit)
 261       .offset(offset)
 262       .load::<CommentView>(self.conn)
 263   }
 264 }
 265
 266 impl CommentView {
 267   pub fn read(
 268     conn: &PgConnection,
 269     from_comment_id: i32,
 270     my_user_id: Option<i32>,
 271   ) -> Result<Self, Error> {
 272     use super::comment_view::comment_mview::dsl::*;
 273     let mut query = comment_mview.into_boxed();
 274
 275     // The view lets you pass a null user_id, if you're not logged in
 276     if let Some(my_user_id) = my_user_id {
 277       query = query.filter(user_id.eq(my_user_id));
 278     } else {
 279       query = query.filter(user_id.is_null());
 280     }
 281
 282     query = query
 283       .filter(id.eq(from_comment_id))
 284       .order_by(published.desc());
 285
 286     query.first::<Self>(conn)
 287   }
 288 }
 289
 290 // The faked schema since diesel doesn't do views
 291 table! {
 292   reply_view (id) {
 293     id -> Int4,
 294     creator_id -> Int4,
 295     post_id -> Int4,
 296     parent_id -> Nullable<Int4>,
 297     content -> Text,
 298     removed -> Bool,
 299     read -> Bool,
 300     published -> Timestamp,
 301     updated -> Nullable<Timestamp>,
 302     deleted -> Bool,
 303     ap_id -> Text,
 304     local -> Bool,
 305     community_id -> Int4,
 306     community_actor_id -> Text,
 307     community_local -> Bool,
 308     community_name -> Varchar,
 309     banned -> Bool,
 310     banned_from_community -> Bool,
 311     creator_actor_id -> Text,
 312     creator_local -> Bool,
 313     creator_name -> Varchar,
 314     creator_avatar -> Nullable<Text>,
 315     score -> BigInt,
 316     upvotes -> BigInt,
 317     downvotes -> BigInt,
 318     hot_rank -> Int4,
 319     user_id -> Nullable<Int4>,
 320     my_vote -> Nullable<Int4>,
 321     subscribed -> Nullable<Bool>,
 322     saved -> Nullable<Bool>,
 323     recipient_id -> Int4,
 324   }
 325 }
 326
 327 #[derive(
 328   Queryable, Identifiable, PartialEq, Debug, Serialize, Deserialize, QueryableByName, Clone,
 329 )]
 330 #[table_name = "reply_view"]
 331 pub struct ReplyView {
 332   pub id: i32,
 333   pub creator_id: i32,
 334   pub post_id: i32,
 335   pub parent_id: Option<i32>,
 336   pub content: String,
 337   pub removed: bool,
 338   pub read: bool,
 339   pub published: chrono::NaiveDateTime,
 340   pub updated: Option<chrono::NaiveDateTime>,
 341   pub deleted: bool,
 342   pub ap_id: String,
 343   pub local: bool,
 344   pub community_id: i32,
 345   pub community_actor_id: String,
 346   pub community_local: bool,
 347   pub community_name: String,
 348   pub banned: bool,
 349   pub banned_from_community: bool,
 350   pub creator_actor_id: String,
 351   pub creator_local: bool,
 352   pub creator_name: String,
 353   pub creator_avatar: Option<String>,
 354   pub score: i64,
 355   pub upvotes: i64,
 356   pub downvotes: i64,
 357   pub hot_rank: i32,
 358   pub user_id: Option<i32>,
 359   pub my_vote: Option<i32>,
 360   pub subscribed: Option<bool>,
 361   pub saved: Option<bool>,
 362   pub recipient_id: i32,
 363 }
 364
 365 pub struct ReplyQueryBuilder<'a> {
 366   conn: &'a PgConnection,
 367   query: super::comment_view::reply_view::BoxedQuery<'a, Pg>,
 368   for_user_id: i32,
 369   sort: &'a SortType,
 370   unread_only: bool,
 371   page: Option<i64>,
 372   limit: Option<i64>,
 373 }
 374
 375 impl<'a> ReplyQueryBuilder<'a> {
 376   pub fn create(conn: &'a PgConnection, for_user_id: i32) -> Self {
 377     use super::comment_view::reply_view::dsl::*;
 378
 379     let query = reply_view.into_boxed();
 380
 381     ReplyQueryBuilder {
 382       conn,
 383       query,
 384       for_user_id,
 385       sort: &SortType::New,
 386       unread_only: false,
 387       page: None,
 388       limit: None,
 389     }
 390   }
 391
 392   pub fn sort(mut self, sort: &'a SortType) -> Self {
 393     self.sort = sort;
 394     self
 395   }
 396
 397   pub fn unread_only(mut self, unread_only: bool) -> Self {
 398     self.unread_only = unread_only;
 399     self
 400   }
 401
 402   pub fn page<T: MaybeOptional<i64>>(mut self, page: T) -> Self {
 403     self.page = page.get_optional();
 404     self
 405   }
 406
 407   pub fn limit<T: MaybeOptional<i64>>(mut self, limit: T) -> Self {
 408     self.limit = limit.get_optional();
 409     self
 410   }
 411
 412   pub fn list(self) -> Result<Vec<ReplyView>, Error> {
 413     use super::comment_view::reply_view::dsl::*;
 414
 415     let mut query = self.query;
 416
 417     query = query
 418       .filter(user_id.eq(self.for_user_id))
 419       .filter(recipient_id.eq(self.for_user_id))
 420       .filter(deleted.eq(false))
 421       .filter(removed.eq(false));
 422
 423     if self.unread_only {
 424       query = query.filter(read.eq(false));
 425     }
 426
 427     query = match self.sort {
 428       // SortType::Hot => query.order_by(hot_rank.desc()),
 429       SortType::New => query.order_by(published.desc()),
 430       SortType::TopAll => query.order_by(score.desc()),
 431       SortType::TopYear => query
 432         .filter(published.gt(now - 1.years()))
 433         .order_by(score.desc()),
 434       SortType::TopMonth => query
 435         .filter(published.gt(now - 1.months()))
 436         .order_by(score.desc()),
 437       SortType::TopWeek => query
 438         .filter(published.gt(now - 1.weeks()))
 439         .order_by(score.desc()),
 440       SortType::TopDay => query
 441         .filter(published.gt(now - 1.days()))
 442         .order_by(score.desc()),
 443       _ => query.order_by(published.desc()),
 444     };
 445
 446     let (limit, offset) = limit_and_offset(self.page, self.limit);
 447     query
 448       .limit(limit)
 449       .offset(offset)
 450       .load::<ReplyView>(self.conn)
 451   }
 452 }
 453
 454 #[cfg(test)]
 455 mod tests {
 456   use super::super::comment::*;
 457   use super::super::community::*;
 458   use super::super::post::*;
 459   use super::super::user::*;
 460   use super::*;
 461   #[test]
 462   fn test_crud() {
 463     let conn = establish_unpooled_connection();
 464
 465     let new_user = UserForm {
 466       name: "timmy".into(),
 467       preferred_username: None,
 468       password_encrypted: "nope".into(),
 469       email: None,
 470       matrix_user_id: None,
 471       avatar: None,
 472       admin: false,
 473       banned: false,
 474       updated: None,
 475       show_nsfw: false,
 476       theme: "darkly".into(),
 477       default_sort_type: SortType::Hot as i16,
 478       default_listing_type: ListingType::Subscribed as i16,
 479       lang: "browser".into(),
 480       show_avatars: true,
 481       send_notifications_to_email: false,
 482       actor_id: "changeme".into(),
 483       bio: None,
 484       local: true,
 485       private_key: None,
 486       public_key: None,
 487       last_refreshed_at: None,
 488     };
 489
 490     let inserted_user = User_::create(&conn, &new_user).unwrap();
 491
 492     let new_community = CommunityForm {
 493       name: "test community 5".to_string(),
 494       title: "nada".to_owned(),
 495       description: None,
 496       category_id: 1,
 497       creator_id: inserted_user.id,
 498       removed: None,
 499       deleted: None,
 500       updated: None,
 501       nsfw: false,
 502       actor_id: "changeme".into(),
 503       local: true,
 504       private_key: None,
 505       public_key: None,
 506       last_refreshed_at: None,
 507       published: None,
 508     };
 509
 510     let inserted_community = Community::create(&conn, &new_community).unwrap();
 511
 512     let new_post = PostForm {
 513       name: "A test post 2".into(),
 514       creator_id: inserted_user.id,
 515       url: None,
 516       body: None,
 517       community_id: inserted_community.id,
 518       removed: None,
 519       deleted: None,
 520       locked: None,
 521       stickied: None,
 522       updated: None,
 523       nsfw: false,
 524       embed_title: None,
 525       embed_description: None,
 526       embed_html: None,
 527       thumbnail_url: None,
 528       ap_id: "changeme".into(),
 529       local: true,
 530       published: None,
 531     };
 532
 533     let inserted_post = Post::create(&conn, &new_post).unwrap();
 534
 535     let comment_form = CommentForm {
 536       content: "A test comment 32".into(),
 537       creator_id: inserted_user.id,
 538       post_id: inserted_post.id,
 539       parent_id: None,
 540       removed: None,
 541       deleted: None,
 542       read: None,
 543       updated: None,
 544       ap_id: "changeme".into(),
 545       local: true,
 546     };
 547
 548     let inserted_comment = Comment::create(&conn, &comment_form).unwrap();
 549
 550     let comment_like_form = CommentLikeForm {
 551       comment_id: inserted_comment.id,
 552       post_id: inserted_post.id,
 553       user_id: inserted_user.id,
 554       score: 1,
 555     };
 556
 557     let _inserted_comment_like = CommentLike::like(&conn, &comment_like_form).unwrap();
 558
 559     let expected_comment_view_no_user = CommentView {
 560       id: inserted_comment.id,
 561       content: "A test comment 32".into(),
 562       creator_id: inserted_user.id,
 563       post_id: inserted_post.id,
 564       community_id: inserted_community.id,
 565       community_name: inserted_community.name.to_owned(),
 566       parent_id: None,
 567       removed: false,
 568       deleted: false,
 569       read: false,
 570       banned: false,
 571       banned_from_community: false,
 572       published: inserted_comment.published,
 573       updated: None,
 574       creator_name: inserted_user.name.to_owned(),
 575       creator_avatar: None,
 576       score: 1,
 577       downvotes: 0,
 578       hot_rank: 0,
 579       upvotes: 1,
 580       user_id: None,
 581       my_vote: None,
 582       subscribed: None,
 583       saved: None,
 584       ap_id: "changeme".to_string(),
 585       local: true,
 586       community_actor_id: inserted_community.actor_id.to_owned(),
 587       community_local: true,
 588       creator_actor_id: inserted_user.actor_id.to_owned(),
 589       creator_local: true,
 590     };
 591
 592     let expected_comment_view_with_user = CommentView {
 593       id: inserted_comment.id,
 594       content: "A test comment 32".into(),
 595       creator_id: inserted_user.id,
 596       post_id: inserted_post.id,
 597       community_id: inserted_community.id,
 598       community_name: inserted_community.name.to_owned(),
 599       parent_id: None,
 600       removed: false,
 601       deleted: false,
 602       read: false,
 603       banned: false,
 604       banned_from_community: false,
 605       published: inserted_comment.published,
 606       updated: None,
 607       creator_name: inserted_user.name.to_owned(),
 608       creator_avatar: None,
 609       score: 1,
 610       downvotes: 0,
 611       hot_rank: 0,
 612       upvotes: 1,
 613       user_id: Some(inserted_user.id),
 614       my_vote: Some(1),
 615       subscribed: None,
 616       saved: None,
 617       ap_id: "changeme".to_string(),
 618       local: true,
 619       community_actor_id: inserted_community.actor_id.to_owned(),
 620       community_local: true,
 621       creator_actor_id: inserted_user.actor_id.to_owned(),
 622       creator_local: true,
 623     };
 624
 625     let mut read_comment_views_no_user = CommentQueryBuilder::create(&conn)
 626       .for_post_id(inserted_post.id)
 627       .list()
 628       .unwrap();
 629     read_comment_views_no_user[0].hot_rank = 0;
 630
 631     let mut read_comment_views_with_user = CommentQueryBuilder::create(&conn)
 632       .for_post_id(inserted_post.id)
 633       .my_user_id(inserted_user.id)
 634       .list()
 635       .unwrap();
 636     read_comment_views_with_user[0].hot_rank = 0;
 637
 638     let like_removed = CommentLike::remove(&conn, &comment_like_form).unwrap();
 639     let num_deleted = Comment::delete(&conn, inserted_comment.id).unwrap();
 640     Post::delete(&conn, inserted_post.id).unwrap();
 641     Community::delete(&conn, inserted_community.id).unwrap();
 642     User_::delete(&conn, inserted_user.id).unwrap();
 643
 644     assert_eq!(expected_comment_view_no_user, read_comment_views_no_user[0]);
 645     assert_eq!(
 646       expected_comment_view_with_user,
 647       read_comment_views_with_user[0]
 648     );
 649     assert_eq!(1, num_deleted);
 650     assert_eq!(1, like_removed);
 651   }
 652 }