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       self.query = self.query.filter(name.ilike(fuzzy_search(&search_term)));
 182     }
 183     self
 184   }
 185
 186   pub fn url_search<T: MaybeOptional<String>>(mut self, url_search: T) -> Self {
 187     use super::post_view::post_mview::dsl::*;
 188     if let Some(url_search) = url_search.get_optional() {
 189       self.query = self.query.filter(url.eq(url_search));
 190     }
 191     self
 192   }
 193
 194   pub fn my_user_id<T: MaybeOptional<i32>>(mut self, my_user_id: T) -> Self {
 195     self.my_user_id = my_user_id.get_optional();
 196     self
 197   }
 198
 199   pub fn show_nsfw(mut self, show_nsfw: bool) -> Self {
 200     self.show_nsfw = show_nsfw;
 201     self
 202   }
 203
 204   pub fn saved_only(mut self, saved_only: bool) -> Self {
 205     self.saved_only = saved_only;
 206     self
 207   }
 208
 209   pub fn unread_only(mut self, unread_only: bool) -> Self {
 210     self.unread_only = unread_only;
 211     self
 212   }
 213
 214   pub fn page<T: MaybeOptional<i64>>(mut self, page: T) -> Self {
 215     self.page = page.get_optional();
 216     self
 217   }
 218
 219   pub fn limit<T: MaybeOptional<i64>>(mut self, limit: T) -> Self {
 220     self.limit = limit.get_optional();
 221     self
 222   }
 223
 224   pub fn list(self) -> Result<Vec<PostView>, Error> {
 225     use super::post_view::post_mview::dsl::*;
 226
 227     let mut query = self.query;
 228
 229     if let ListingType::Subscribed = self.listing_type {
 230       query = query.filter(subscribed.eq(true));
 231     }
 232
 233     query = match self.sort {
 234       SortType::Hot => query
 235         .then_order_by(hot_rank.desc())
 236         .then_order_by(published.desc()),
 237       SortType::New => query.then_order_by(published.desc()),
 238       SortType::TopAll => query.then_order_by(score.desc()),
 239       SortType::TopYear => query
 240         .filter(published.gt(now - 1.years()))
 241         .then_order_by(score.desc()),
 242       SortType::TopMonth => query
 243         .filter(published.gt(now - 1.months()))
 244         .then_order_by(score.desc()),
 245       SortType::TopWeek => query
 246         .filter(published.gt(now - 1.weeks()))
 247         .then_order_by(score.desc()),
 248       SortType::TopDay => query
 249         .filter(published.gt(now - 1.days()))
 250         .then_order_by(score.desc()),
 251     };
 252
 253     // The view lets you pass a null user_id, if you're not logged in
 254     query = if let Some(my_user_id) = self.my_user_id {
 255       query.filter(user_id.eq(my_user_id))
 256     } else {
 257       query.filter(user_id.is_null())
 258     };
 259
 260     // If its for a specific user, show the removed / deleted
 261     if let Some(for_creator_id) = self.for_creator_id {
 262       query = query.filter(creator_id.eq(for_creator_id));
 263     } else {
 264       query = query
 265         .filter(removed.eq(false))
 266         .filter(deleted.eq(false))
 267         .filter(community_removed.eq(false))
 268         .filter(community_deleted.eq(false));
 269     }
 270
 271     if !self.show_nsfw {
 272       query = query
 273         .filter(nsfw.eq(false))
 274         .filter(community_nsfw.eq(false));
 275     };
 276
 277     // TODO these are wrong, bc they'll only show saved for your logged in user, not theirs
 278     if self.saved_only {
 279       query = query.filter(saved.eq(true));
 280     };
 281
 282     if self.unread_only {
 283       query = query.filter(read.eq(false));
 284     };
 285
 286     let (limit, offset) = limit_and_offset(self.page, self.limit);
 287     query = query
 288       .limit(limit)
 289       .offset(offset)
 290       .filter(removed.eq(false))
 291       .filter(deleted.eq(false))
 292       .filter(community_removed.eq(false))
 293       .filter(community_deleted.eq(false));
 294
 295     query.load::<PostView>(self.conn)
 296   }
 297 }
 298
 299 impl PostView {
 300   pub fn read(
 301     conn: &PgConnection,
 302     from_post_id: i32,
 303     my_user_id: Option<i32>,
 304   ) -> Result<Self, Error> {
 305     use super::post_view::post_view::dsl::*;
 306     use diesel::prelude::*;
 307
 308     let mut query = post_view.into_boxed();
 309
 310     query = query.filter(id.eq(from_post_id));
 311
 312     if let Some(my_user_id) = my_user_id {
 313       query = query.filter(user_id.eq(my_user_id));
 314     } else {
 315       query = query.filter(user_id.is_null());
 316     };
 317
 318     query.first::<Self>(conn)
 319   }
 320 }
 321
 322 #[cfg(test)]
 323 mod tests {
 324   use super::super::community::*;
 325   use super::super::post::*;
 326   use super::super::user::*;
 327   use super::*;
 328   #[test]
 329   fn test_crud() {
 330     let conn = establish_unpooled_connection();
 331
 332     let user_name = "tegan".to_string();
 333     let community_name = "test_community_3".to_string();
 334     let post_name = "test post 3".to_string();
 335
 336     let new_user = UserForm {
 337       name: user_name.to_owned(),
 338       fedi_name: "rrf".into(),
 339       preferred_username: None,
 340       password_encrypted: "nope".into(),
 341       email: None,
 342       matrix_user_id: None,
 343       avatar: None,
 344       updated: None,
 345       admin: false,
 346       banned: false,
 347       show_nsfw: false,
 348       theme: "darkly".into(),
 349       default_sort_type: SortType::Hot as i16,
 350       default_listing_type: ListingType::Subscribed as i16,
 351       lang: "browser".into(),
 352       show_avatars: true,
 353       send_notifications_to_email: false,
 354     };
 355
 356     let inserted_user = User_::create(&conn, &new_user).unwrap();
 357
 358     let new_community = CommunityForm {
 359       name: community_name.to_owned(),
 360       title: "nada".to_owned(),
 361       description: None,
 362       creator_id: inserted_user.id,
 363       category_id: 1,
 364       removed: None,
 365       deleted: None,
 366       updated: None,
 367       nsfw: false,
 368     };
 369
 370     let inserted_community = Community::create(&conn, &new_community).unwrap();
 371
 372     let new_post = PostForm {
 373       name: post_name.to_owned(),
 374       url: None,
 375       body: None,
 376       creator_id: inserted_user.id,
 377       community_id: inserted_community.id,
 378       removed: None,
 379       deleted: None,
 380       locked: None,
 381       stickied: None,
 382       updated: None,
 383       nsfw: false,
 384     };
 385
 386     let inserted_post = Post::create(&conn, &new_post).unwrap();
 387
 388     let post_like_form = PostLikeForm {
 389       post_id: inserted_post.id,
 390       user_id: inserted_user.id,
 391       score: 1,
 392     };
 393
 394     let inserted_post_like = PostLike::like(&conn, &post_like_form).unwrap();
 395
 396     let expected_post_like = PostLike {
 397       id: inserted_post_like.id,
 398       post_id: inserted_post.id,
 399       user_id: inserted_user.id,
 400       published: inserted_post_like.published,
 401       score: 1,
 402     };
 403
 404     let post_like_form = PostLikeForm {
 405       post_id: inserted_post.id,
 406       user_id: inserted_user.id,
 407       score: 1,
 408     };
 409
 410     // the non user version
 411     let expected_post_listing_no_user = PostView {
 412       user_id: None,
 413       my_vote: None,
 414       id: inserted_post.id,
 415       name: post_name.to_owned(),
 416       url: None,
 417       body: None,
 418       creator_id: inserted_user.id,
 419       creator_name: user_name.to_owned(),
 420       creator_avatar: None,
 421       banned: false,
 422       banned_from_community: false,
 423       community_id: inserted_community.id,
 424       removed: false,
 425       deleted: false,
 426       locked: false,
 427       stickied: false,
 428       community_name: community_name.to_owned(),
 429       community_removed: false,
 430       community_deleted: false,
 431       community_nsfw: false,
 432       number_of_comments: 0,
 433       score: 1,
 434       upvotes: 1,
 435       downvotes: 0,
 436       hot_rank: 1728,
 437       published: inserted_post.published,
 438       updated: None,
 439       subscribed: None,
 440       read: None,
 441       saved: None,
 442       nsfw: false,
 443     };
 444
 445     let expected_post_listing_with_user = PostView {
 446       user_id: Some(inserted_user.id),
 447       my_vote: Some(1),
 448       id: inserted_post.id,
 449       name: post_name,
 450       url: None,
 451       body: None,
 452       removed: false,
 453       deleted: false,
 454       locked: false,
 455       stickied: false,
 456       creator_id: inserted_user.id,
 457       creator_name: user_name,
 458       creator_avatar: None,
 459       banned: false,
 460       banned_from_community: false,
 461       community_id: inserted_community.id,
 462       community_name,
 463       community_removed: false,
 464       community_deleted: false,
 465       community_nsfw: false,
 466       number_of_comments: 0,
 467       score: 1,
 468       upvotes: 1,
 469       downvotes: 0,
 470       hot_rank: 1728,
 471       published: inserted_post.published,
 472       updated: None,
 473       subscribed: None,
 474       read: None,
 475       saved: None,
 476       nsfw: false,
 477     };
 478
 479     let read_post_listings_with_user = PostQueryBuilder::create(&conn)
 480       .listing_type(ListingType::Community)
 481       .sort(&SortType::New)
 482       .for_community_id(inserted_community.id)
 483       .my_user_id(inserted_user.id)
 484       .list()
 485       .unwrap();
 486
 487     let read_post_listings_no_user = PostQueryBuilder::create(&conn)
 488       .listing_type(ListingType::Community)
 489       .sort(&SortType::New)
 490       .for_community_id(inserted_community.id)
 491       .list()
 492       .unwrap();
 493
 494     let read_post_listing_no_user = PostView::read(&conn, inserted_post.id, None).unwrap();
 495     let read_post_listing_with_user =
 496       PostView::read(&conn, inserted_post.id, Some(inserted_user.id)).unwrap();
 497
 498     let like_removed = PostLike::remove(&conn, &post_like_form).unwrap();
 499     let num_deleted = Post::delete(&conn, inserted_post.id).unwrap();
 500     Community::delete(&conn, inserted_community.id).unwrap();
 501     User_::delete(&conn, inserted_user.id).unwrap();
 502
 503     // The with user
 504     assert_eq!(
 505       expected_post_listing_with_user,
 506       read_post_listings_with_user[0]
 507     );
 508     assert_eq!(expected_post_listing_with_user, read_post_listing_with_user);
 509     assert_eq!(1, read_post_listings_with_user.len());
 510
 511     // Without the user
 512     assert_eq!(expected_post_listing_no_user, read_post_listings_no_user[0]);
 513     assert_eq!(expected_post_listing_no_user, read_post_listing_no_user);
 514     assert_eq!(1, read_post_listings_no_user.len());
 515
 516     // assert_eq!(expected_post, inserted_post);
 517     // assert_eq!(expected_post, updated_post);
 518     assert_eq!(expected_post_like, inserted_post_like);
 519     assert_eq!(1, like_removed);
 520     assert_eq!(1, num_deleted);
 521   }
 522 }