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