server/src/db/post_view.rs

   1 use super::post_view::post_mview::BoxedQuery;
   2 use super::*;
   3 use diesel::pg::Pg;
   4
   5 // The faked schema since diesel doesn't do views
   6 table! {
   7   post_view (id) {
   8     id -> Int4,
   9     name -> Varchar,
  10     url -> Nullable<Text>,
  11     body -> Nullable<Text>,
  12     creator_id -> Int4,
  13     community_id -> Int4,
  14     removed -> Bool,
  15     locked -> Bool,
  16     published -> Timestamp,
  17     updated -> Nullable<Timestamp>,
  18     deleted -> Bool,
  19     nsfw -> Bool,
  20     banned -> Bool,
  21     banned_from_community -> Bool,
  22     stickied -> Bool,
  23     creator_name -> Varchar,
  24     creator_avatar -> Nullable<Text>,
  25     community_name -> Varchar,
  26     community_removed -> Bool,
  27     community_deleted -> Bool,
  28     community_nsfw -> Bool,
  29     number_of_comments -> BigInt,
  30     score -> BigInt,
  31     upvotes -> BigInt,
  32     downvotes -> BigInt,
  33     hot_rank -> Int4,
  34     newest_activity_time -> Timestamp,
  35     user_id -> Nullable<Int4>,
  36     my_vote -> Nullable<Int4>,
  37     subscribed -> Nullable<Bool>,
  38     read -> Nullable<Bool>,
  39     saved -> Nullable<Bool>,
  40   }
  41 }
  42
  43 #[derive(
  44   Queryable, Identifiable, PartialEq, Debug, Serialize, Deserialize, QueryableByName, Clone,
  45 )]
  46 #[table_name = "post_view"]
  47 pub struct PostView {
  48   pub id: i32,
  49   pub name: String,
  50   pub url: Option<String>,
  51   pub body: Option<String>,
  52   pub creator_id: i32,
  53   pub community_id: i32,
  54   pub removed: bool,
  55   pub locked: bool,
  56   pub published: chrono::NaiveDateTime,
  57   pub updated: Option<chrono::NaiveDateTime>,
  58   pub deleted: bool,
  59   pub nsfw: bool,
  60   pub banned: bool,
  61   pub banned_from_community: bool,
  62   pub stickied: bool,
  63   pub creator_name: String,
  64   pub creator_avatar: Option<String>,
  65   pub community_name: String,
  66   pub community_removed: bool,
  67   pub community_deleted: bool,
  68   pub community_nsfw: bool,
  69   pub number_of_comments: i64,
  70   pub score: i64,
  71   pub upvotes: i64,
  72   pub downvotes: i64,
  73   pub hot_rank: i32,
  74   pub newest_activity_time: chrono::NaiveDateTime,
  75   pub user_id: Option<i32>,
  76   pub my_vote: Option<i32>,
  77   pub subscribed: Option<bool>,
  78   pub read: Option<bool>,
  79   pub saved: Option<bool>,
  80 }
  81
  82 // The faked schema since diesel doesn't do views
  83 table! {
  84   post_mview (id) {
  85     id -> Int4,
  86     name -> Varchar,
  87     url -> Nullable<Text>,
  88     body -> Nullable<Text>,
  89     creator_id -> Int4,
  90     community_id -> Int4,
  91     removed -> Bool,
  92     locked -> Bool,
  93     published -> Timestamp,
  94     updated -> Nullable<Timestamp>,
  95     deleted -> Bool,
  96     nsfw -> Bool,
  97     banned -> Bool,
  98     banned_from_community -> Bool,
  99     stickied -> Bool,
 100     creator_name -> Varchar,
 101     creator_avatar -> Nullable<Text>,
 102     community_name -> Varchar,
 103     community_removed -> Bool,
 104     community_deleted -> Bool,
 105     community_nsfw -> Bool,
 106     number_of_comments -> BigInt,
 107     score -> BigInt,
 108     upvotes -> BigInt,
 109     downvotes -> BigInt,
 110     hot_rank -> Int4,
 111     newest_activity_time -> Timestamp,
 112     user_id -> Nullable<Int4>,
 113     my_vote -> Nullable<Int4>,
 114     subscribed -> Nullable<Bool>,
 115     read -> Nullable<Bool>,
 116     saved -> Nullable<Bool>,
 117   }
 118 }
 119
 120 pub struct PostQueryBuilder<'a> {
 121   conn: &'a PgConnection,
 122   query: BoxedQuery<'a, Pg>,
 123   listing_type: ListingType,
 124   sort: &'a SortType,
 125   my_user_id: Option<i32>,
 126   for_creator_id: Option<i32>,
 127   for_community_id: Option<i32>,
 128   search_term: Option<String>,
 129   url_search: Option<String>,
 130   show_nsfw: bool,
 131   saved_only: bool,
 132   unread_only: bool,
 133   page: Option<i64>,
 134   limit: Option<i64>,
 135 }
 136
 137 impl<'a> PostQueryBuilder<'a> {
 138   pub fn create(conn: &'a PgConnection) -> Self {
 139     use super::post_view::post_mview::dsl::*;
 140
 141     let query = post_mview.into_boxed();
 142
 143     PostQueryBuilder {
 144       conn,
 145       query,
 146       listing_type: ListingType::All,
 147       sort: &SortType::Hot,
 148       my_user_id: None,
 149       for_creator_id: None,
 150       for_community_id: None,
 151       search_term: None,
 152       url_search: None,
 153       show_nsfw: true,
 154       saved_only: false,
 155       unread_only: false,
 156       page: None,
 157       limit: None,
 158     }
 159   }
 160
 161   pub fn listing_type(mut self, listing_type: ListingType) -> Self {
 162     self.listing_type = listing_type;
 163     self
 164   }
 165
 166   pub fn sort(mut self, sort: &'a SortType) -> Self {
 167     self.sort = sort;
 168     self
 169   }
 170
 171   pub fn for_community_id<T: MaybeOptional<i32>>(mut self, for_community_id: T) -> Self {
 172     self.for_community_id = for_community_id.get_optional();
 173     self
 174   }
 175
 176   pub fn for_creator_id<T: MaybeOptional<i32>>(mut self, for_creator_id: T) -> Self {
 177     self.for_creator_id = for_creator_id.get_optional();
 178     self
 179   }
 180
 181   pub fn search_term<T: MaybeOptional<String>>(mut self, search_term: T) -> Self {
 182     self.search_term = search_term.get_optional();
 183     self
 184   }
 185
 186   pub fn url_search<T: MaybeOptional<String>>(mut self, url_search: T) -> Self {
 187     self.url_search = url_search.get_optional();
 188     self
 189   }
 190
 191   pub fn my_user_id<T: MaybeOptional<i32>>(mut self, my_user_id: T) -> Self {
 192     self.my_user_id = my_user_id.get_optional();
 193     self
 194   }
 195
 196   pub fn show_nsfw(mut self, show_nsfw: bool) -> Self {
 197     self.show_nsfw = show_nsfw;
 198     self
 199   }
 200
 201   pub fn saved_only(mut self, saved_only: bool) -> Self {
 202     self.saved_only = saved_only;
 203     self
 204   }
 205
 206   pub fn unread_only(mut self, unread_only: bool) -> Self {
 207     self.unread_only = unread_only;
 208     self
 209   }
 210
 211   pub fn page<T: MaybeOptional<i64>>(mut self, page: T) -> Self {
 212     self.page = page.get_optional();
 213     self
 214   }
 215
 216   pub fn limit<T: MaybeOptional<i64>>(mut self, limit: T) -> Self {
 217     self.limit = limit.get_optional();
 218     self
 219   }
 220
 221   pub fn list(self) -> Result<Vec<PostView>, Error> {
 222     use super::post_view::post_mview::dsl::*;
 223
 224     let mut query = self.query;
 225
 226     if let ListingType::Subscribed = self.listing_type {
 227       query = query.filter(subscribed.eq(true));
 228     }
 229
 230     if let Some(for_community_id) = self.for_community_id {
 231       query = query.filter(community_id.eq(for_community_id));
 232       query = query.then_order_by(stickied.desc());
 233     }
 234
 235     if let Some(url_search) = self.url_search {
 236       query = query.filter(url.eq(url_search));
 237     }
 238
 239     if let Some(search_term) = self.search_term {
 240       let searcher = fuzzy_search(&search_term);
 241       query = query
 242         .filter(name.ilike(searcher.to_owned()))
 243         .or_filter(body.ilike(searcher));
 244     }
 245
 246     query = match self.sort {
 247       SortType::Hot => query
 248         .then_order_by(hot_rank.desc())
 249         .then_order_by(published.desc()),
 250       SortType::New => query.then_order_by(published.desc()),
 251       SortType::TopAll => query.then_order_by(score.desc()),
 252       SortType::TopYear => query
 253         .filter(published.gt(now - 1.years()))
 254         .then_order_by(score.desc()),
 255       SortType::TopMonth => query
 256         .filter(published.gt(now - 1.months()))
 257         .then_order_by(score.desc()),
 258       SortType::TopWeek => query
 259         .filter(published.gt(now - 1.weeks()))
 260         .then_order_by(score.desc()),
 261       SortType::TopDay => query
 262         .filter(published.gt(now - 1.days()))
 263         .then_order_by(score.desc()),
 264     };
 265
 266     // The view lets you pass a null user_id, if you're not logged in
 267     query = if let Some(my_user_id) = self.my_user_id {
 268       query.filter(user_id.eq(my_user_id))
 269     } else {
 270       query.filter(user_id.is_null())
 271     };
 272
 273     // If its for a specific user, show the removed / deleted
 274     if let Some(for_creator_id) = self.for_creator_id {
 275       query = query.filter(creator_id.eq(for_creator_id));
 276     } else {
 277       query = query
 278         .filter(removed.eq(false))
 279         .filter(deleted.eq(false))
 280         .filter(community_removed.eq(false))
 281         .filter(community_deleted.eq(false));
 282     }
 283
 284     if !self.show_nsfw {
 285       query = query
 286         .filter(nsfw.eq(false))
 287         .filter(community_nsfw.eq(false));
 288     };
 289
 290     // TODO these are wrong, bc they'll only show saved for your logged in user, not theirs
 291     if self.saved_only {
 292       query = query.filter(saved.eq(true));
 293     };
 294
 295     if self.unread_only {
 296       query = query.filter(read.eq(false));
 297     };
 298
 299     let (limit, offset) = limit_and_offset(self.page, self.limit);
 300     query = query
 301       .limit(limit)
 302       .offset(offset)
 303       .filter(removed.eq(false))
 304       .filter(deleted.eq(false))
 305       .filter(community_removed.eq(false))
 306       .filter(community_deleted.eq(false));
 307
 308     query.load::<PostView>(self.conn)
 309   }
 310 }
 311
 312 impl PostView {
 313   pub fn read(
 314     conn: &PgConnection,
 315     from_post_id: i32,
 316     my_user_id: Option<i32>,
 317   ) -> Result<Self, Error> {
 318     use super::post_view::post_mview::dsl::*;
 319     use diesel::prelude::*;
 320
 321     let mut query = post_mview.into_boxed();
 322
 323     query = query.filter(id.eq(from_post_id));
 324
 325     if let Some(my_user_id) = my_user_id {
 326       query = query.filter(user_id.eq(my_user_id));
 327     } else {
 328       query = query.filter(user_id.is_null());
 329     };
 330
 331     query.first::<Self>(conn)
 332   }
 333 }
 334
 335 #[cfg(test)]
 336 mod tests {
 337   use super::super::community::*;
 338   use super::super::post::*;
 339   use super::super::user::*;
 340   use super::*;
 341   #[test]
 342   fn test_crud() {
 343     let conn = establish_unpooled_connection();
 344
 345     let user_name = "tegan".to_string();
 346     let community_name = "test_community_3".to_string();
 347     let post_name = "test post 3".to_string();
 348
 349     let new_user = UserForm {
 350       name: user_name.to_owned(),
 351       fedi_name: "rrf".into(),
 352       preferred_username: None,
 353       password_encrypted: "nope".into(),
 354       email: None,
 355       matrix_user_id: None,
 356       avatar: None,
 357       updated: None,
 358       admin: false,
 359       banned: false,
 360       show_nsfw: false,
 361       theme: "darkly".into(),
 362       default_sort_type: SortType::Hot as i16,
 363       default_listing_type: ListingType::Subscribed as i16,
 364       lang: "browser".into(),
 365       show_avatars: true,
 366       send_notifications_to_email: false,
 367     };
 368
 369     let inserted_user = User_::create(&conn, &new_user).unwrap();
 370
 371     let new_community = CommunityForm {
 372       name: community_name.to_owned(),
 373       title: "nada".to_owned(),
 374       description: None,
 375       creator_id: inserted_user.id,
 376       category_id: 1,
 377       removed: None,
 378       deleted: None,
 379       updated: None,
 380       nsfw: false,
 381     };
 382
 383     let inserted_community = Community::create(&conn, &new_community).unwrap();
 384
 385     let new_post = PostForm {
 386       name: post_name.to_owned(),
 387       url: None,
 388       body: None,
 389       creator_id: inserted_user.id,
 390       community_id: inserted_community.id,
 391       removed: None,
 392       deleted: None,
 393       locked: None,
 394       stickied: None,
 395       updated: None,
 396       nsfw: false,
 397     };
 398
 399     let inserted_post = Post::create(&conn, &new_post).unwrap();
 400
 401     let post_like_form = PostLikeForm {
 402       post_id: inserted_post.id,
 403       user_id: inserted_user.id,
 404       score: 1,
 405     };
 406
 407     let inserted_post_like = PostLike::like(&conn, &post_like_form).unwrap();
 408
 409     let expected_post_like = PostLike {
 410       id: inserted_post_like.id,
 411       post_id: inserted_post.id,
 412       user_id: inserted_user.id,
 413       published: inserted_post_like.published,
 414       score: 1,
 415     };
 416
 417     let post_like_form = PostLikeForm {
 418       post_id: inserted_post.id,
 419       user_id: inserted_user.id,
 420       score: 1,
 421     };
 422
 423     // the non user version
 424     let expected_post_listing_no_user = PostView {
 425       user_id: None,
 426       my_vote: None,
 427       id: inserted_post.id,
 428       name: post_name.to_owned(),
 429       url: None,
 430       body: None,
 431       creator_id: inserted_user.id,
 432       creator_name: user_name.to_owned(),
 433       creator_avatar: None,
 434       banned: false,
 435       banned_from_community: false,
 436       community_id: inserted_community.id,
 437       removed: false,
 438       deleted: false,
 439       locked: false,
 440       stickied: false,
 441       community_name: community_name.to_owned(),
 442       community_removed: false,
 443       community_deleted: false,
 444       community_nsfw: false,
 445       number_of_comments: 0,
 446       score: 1,
 447       upvotes: 1,
 448       downvotes: 0,
 449       hot_rank: 1728,
 450       published: inserted_post.published,
 451       newest_activity_time: inserted_post.published,
 452       updated: None,
 453       subscribed: None,
 454       read: None,
 455       saved: None,
 456       nsfw: false,
 457     };
 458
 459     let expected_post_listing_with_user = PostView {
 460       user_id: Some(inserted_user.id),
 461       my_vote: Some(1),
 462       id: inserted_post.id,
 463       name: post_name,
 464       url: None,
 465       body: None,
 466       removed: false,
 467       deleted: false,
 468       locked: false,
 469       stickied: false,
 470       creator_id: inserted_user.id,
 471       creator_name: user_name,
 472       creator_avatar: None,
 473       banned: false,
 474       banned_from_community: false,
 475       community_id: inserted_community.id,
 476       community_name,
 477       community_removed: false,
 478       community_deleted: false,
 479       community_nsfw: false,
 480       number_of_comments: 0,
 481       score: 1,
 482       upvotes: 1,
 483       downvotes: 0,
 484       hot_rank: 1728,
 485       published: inserted_post.published,
 486       newest_activity_time: inserted_post.published,
 487       updated: None,
 488       subscribed: None,
 489       read: None,
 490       saved: None,
 491       nsfw: false,
 492     };
 493
 494     let read_post_listings_with_user = PostQueryBuilder::create(&conn)
 495       .listing_type(ListingType::Community)
 496       .sort(&SortType::New)
 497       .for_community_id(inserted_community.id)
 498       .my_user_id(inserted_user.id)
 499       .list()
 500       .unwrap();
 501
 502     let read_post_listings_no_user = PostQueryBuilder::create(&conn)
 503       .listing_type(ListingType::Community)
 504       .sort(&SortType::New)
 505       .for_community_id(inserted_community.id)
 506       .list()
 507       .unwrap();
 508
 509     let read_post_listing_no_user = PostView::read(&conn, inserted_post.id, None).unwrap();
 510     let read_post_listing_with_user =
 511       PostView::read(&conn, inserted_post.id, Some(inserted_user.id)).unwrap();
 512
 513     let like_removed = PostLike::remove(&conn, &post_like_form).unwrap();
 514     let num_deleted = Post::delete(&conn, inserted_post.id).unwrap();
 515     Community::delete(&conn, inserted_community.id).unwrap();
 516     User_::delete(&conn, inserted_user.id).unwrap();
 517
 518     // The with user
 519     assert_eq!(
 520       expected_post_listing_with_user,
 521       read_post_listings_with_user[0]
 522     );
 523     assert_eq!(expected_post_listing_with_user, read_post_listing_with_user);
 524     assert_eq!(1, read_post_listings_with_user.len());
 525
 526     // Without the user
 527     assert_eq!(expected_post_listing_no_user, read_post_listings_no_user[0]);
 528     assert_eq!(expected_post_listing_no_user, read_post_listing_no_user);
 529     assert_eq!(1, read_post_listings_no_user.len());
 530
 531     // assert_eq!(expected_post, inserted_post);
 532     // assert_eq!(expected_post, updated_post);
 533     assert_eq!(expected_post_like, inserted_post_like);
 534     assert_eq!(1, like_removed);
 535     assert_eq!(1, num_deleted);
 536   }
 537 }