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