summaryrefslogtreecommitdiffstats
path: root/server/src/db
diff options
context:
space:
mode:
Diffstat (limited to 'server/src/db')
-rw-r--r--server/src/db/comment_view.rs1
-rw-r--r--server/src/db/mod.rs2
-rw-r--r--server/src/db/src/schema.rs345
-rw-r--r--server/src/db/user_mention.rs169
-rw-r--r--server/src/db/user_mention_view.rs117
5 files changed, 633 insertions, 1 deletions
diff --git a/server/src/db/comment_view.rs b/server/src/db/comment_view.rs
index b192e6eb..88190464 100644
--- a/server/src/db/comment_view.rs
+++ b/server/src/db/comment_view.rs
@@ -69,7 +69,6 @@ impl CommentView {
let (limit, offset) = limit_and_offset(page, limit);
- // TODO no limits here?
let mut query = comment_view.into_boxed();
// The view lets you pass a null user_id, if you're not logged in
diff --git a/server/src/db/mod.rs b/server/src/db/mod.rs
index 51a59139..ac3c3ae3 100644
--- a/server/src/db/mod.rs
+++ b/server/src/db/mod.rs
@@ -14,6 +14,8 @@ pub mod moderator_views;
pub mod post;
pub mod post_view;
pub mod user;
+pub mod user_mention;
+pub mod user_mention_view;
pub mod user_view;
pub trait Crud<T> {
diff --git a/server/src/db/src/schema.rs b/server/src/db/src/schema.rs
new file mode 100644
index 00000000..8693db25
--- /dev/null
+++ b/server/src/db/src/schema.rs
@@ -0,0 +1,345 @@
+table! {
+ category (id) {
+ id -> Int4,
+ name -> Varchar,
+ }
+}
+
+table! {
+ comment (id) {
+ id -> Int4,
+ creator_id -> Int4,
+ post_id -> Int4,
+ parent_id -> Nullable<Int4>,
+ content -> Text,
+ removed -> Bool,
+ read -> Bool,
+ published -> Timestamp,
+ updated -> Nullable<Timestamp>,
+ deleted -> Bool,
+ }
+}
+
+table! {
+ comment_like (id) {
+ id -> Int4,
+ user_id -> Int4,
+ comment_id -> Int4,
+ post_id -> Int4,
+ score -> Int2,
+ published -> Timestamp,
+ }
+}
+
+table! {
+ comment_saved (id) {
+ id -> Int4,
+ comment_id -> Int4,
+ user_id -> Int4,
+ published -> Timestamp,
+ }
+}
+
+table! {
+ community (id) {
+ id -> Int4,
+ name -> Varchar,
+ title -> Varchar,
+ description -> Nullable<Text>,
+ category_id -> Int4,
+ creator_id -> Int4,
+ removed -> Bool,
+ published -> Timestamp,
+ updated -> Nullable<Timestamp>,
+ deleted -> Bool,
+ nsfw -> Bool,
+ }
+}
+
+table! {
+ community_follower (id) {
+ id -> Int4,
+ community_id -> Int4,
+ user_id -> Int4,
+ published -> Timestamp,
+ }
+}
+
+table! {
+ community_moderator (id) {
+ id -> Int4,
+ community_id -> Int4,
+ user_id -> Int4,
+ published -> Timestamp,
+ }
+}
+
+table! {
+ community_user_ban (id) {
+ id -> Int4,
+ community_id -> Int4,
+ user_id -> Int4,
+ published -> Timestamp,
+ }
+}
+
+table! {
+ mod_add (id) {
+ id -> Int4,
+ mod_user_id -> Int4,
+ other_user_id -> Int4,
+ removed -> Nullable<Bool>,
+ when_ -> Timestamp,
+ }
+}
+
+table! {
+ mod_add_community (id) {
+ id -> Int4,
+ mod_user_id -> Int4,
+ other_user_id -> Int4,
+ community_id -> Int4,
+ removed -> Nullable<Bool>,
+ when_ -> Timestamp,
+ }
+}
+
+table! {
+ mod_ban (id) {
+ id -> Int4,
+ mod_user_id -> Int4,
+ other_user_id -> Int4,
+ reason -> Nullable<Text>,
+ banned -> Nullable<Bool>,
+ expires -> Nullable<Timestamp>,
+ when_ -> Timestamp,
+ }
+}
+
+table! {
+ mod_ban_from_community (id) {
+ id -> Int4,
+ mod_user_id -> Int4,
+ other_user_id -> Int4,
+ community_id -> Int4,
+ reason -> Nullable<Text>,
+ banned -> Nullable<Bool>,
+ expires -> Nullable<Timestamp>,
+ when_ -> Timestamp,
+ }
+}
+
+table! {
+ mod_lock_post (id) {
+ id -> Int4,
+ mod_user_id -> Int4,
+ post_id -> Int4,
+ locked -> Nullable<Bool>,
+ when_ -> Timestamp,
+ }
+}
+
+table! {
+ mod_remove_comment (id) {
+ id -> Int4,
+ mod_user_id -> Int4,
+ comment_id -> Int4,
+ reason -> Nullable<Text>,
+ removed -> Nullable<Bool>,
+ when_ -> Timestamp,
+ }
+}
+
+table! {
+ mod_remove_community (id) {
+ id -> Int4,
+ mod_user_id -> Int4,
+ community_id -> Int4,
+ reason -> Nullable<Text>,
+ removed -> Nullable<Bool>,
+ expires -> Nullable<Timestamp>,
+ when_ -> Timestamp,
+ }
+}
+
+table! {
+ mod_remove_post (id) {
+ id -> Int4,
+ mod_user_id -> Int4,
+ post_id -> Int4,
+ reason -> Nullable<Text>,
+ removed -> Nullable<Bool>,
+ when_ -> Timestamp,
+ }
+}
+
+table! {
+ mod_sticky_post (id) {
+ id -> Int4,
+ mod_user_id -> Int4,
+ post_id -> Int4,
+ stickied -> Nullable<Bool>,
+ when_ -> Timestamp,
+ }
+}
+
+table! {
+ post (id) {
+ id -> Int4,
+ name -> Varchar,
+ url -> Nullable<Text>,
+ body -> Nullable<Text>,
+ creator_id -> Int4,
+ community_id -> Int4,
+ removed -> Bool,
+ locked -> Bool,
+ published -> Timestamp,
+ updated -> Nullable<Timestamp>,
+ deleted -> Bool,
+ nsfw -> Bool,
+ stickied -> Bool,
+ }
+}
+
+table! {
+ post_like (id) {
+ id -> Int4,
+ post_id -> Int4,
+ user_id -> Int4,
+ score -> Int2,
+ published -> Timestamp,
+ }
+}
+
+table! {
+ post_read (id) {
+ id -> Int4,
+ post_id -> Int4,
+ user_id -> Int4,
+ published -> Timestamp,
+ }
+}
+
+table! {
+ post_saved (id) {
+ id -> Int4,
+ post_id -> Int4,
+ user_id -> Int4,
+ published -> Timestamp,
+ }
+}
+
+table! {
+ site (id) {
+ id -> Int4,
+ name -> Varchar,
+ description -> Nullable<Text>,
+ creator_id -> Int4,
+ published -> Timestamp,
+ updated -> Nullable<Timestamp>,
+ }
+}
+
+table! {
+ user_ (id) {
+ id -> Int4,
+ name -> Varchar,
+ fedi_name -> Varchar,
+ preferred_username -> Nullable<Varchar>,
+ password_encrypted -> Text,
+ email -> Nullable<Text>,
+ icon -> Nullable<Bytea>,
+ admin -> Bool,
+ banned -> Bool,
+ published -> Timestamp,
+ updated -> Nullable<Timestamp>,
+ show_nsfw -> Bool,
+ theme -> Varchar,
+ }
+}
+
+table! {
+ user_ban (id) {
+ id -> Int4,
+ user_id -> Int4,
+ published -> Timestamp,
+ }
+}
+
+table! {
+ user_mention (id) {
+ id -> Int4,
+ recipient_id -> Int4,
+ comment_id -> Int4,
+ read -> Bool,
+ published -> Timestamp,
+ }
+}
+
+joinable!(comment -> post (post_id));
+joinable!(comment -> user_ (creator_id));
+joinable!(comment_like -> comment (comment_id));
+joinable!(comment_like -> post (post_id));
+joinable!(comment_like -> user_ (user_id));
+joinable!(comment_saved -> comment (comment_id));
+joinable!(comment_saved -> user_ (user_id));
+joinable!(community -> category (category_id));
+joinable!(community -> user_ (creator_id));
+joinable!(community_follower -> community (community_id));
+joinable!(community_follower -> user_ (user_id));
+joinable!(community_moderator -> community (community_id));
+joinable!(community_moderator -> user_ (user_id));
+joinable!(community_user_ban -> community (community_id));
+joinable!(community_user_ban -> user_ (user_id));
+joinable!(mod_add_community -> community (community_id));
+joinable!(mod_ban_from_community -> community (community_id));
+joinable!(mod_lock_post -> post (post_id));
+joinable!(mod_lock_post -> user_ (mod_user_id));
+joinable!(mod_remove_comment -> comment (comment_id));
+joinable!(mod_remove_comment -> user_ (mod_user_id));
+joinable!(mod_remove_community -> community (community_id));
+joinable!(mod_remove_community -> user_ (mod_user_id));
+joinable!(mod_remove_post -> post (post_id));
+joinable!(mod_remove_post -> user_ (mod_user_id));
+joinable!(mod_sticky_post -> post (post_id));
+joinable!(mod_sticky_post -> user_ (mod_user_id));
+joinable!(post -> community (community_id));
+joinable!(post -> user_ (creator_id));
+joinable!(post_like -> post (post_id));
+joinable!(post_like -> user_ (user_id));
+joinable!(post_read -> post (post_id));
+joinable!(post_read -> user_ (user_id));
+joinable!(post_saved -> post (post_id));
+joinable!(post_saved -> user_ (user_id));
+joinable!(site -> user_ (creator_id));
+joinable!(user_ban -> user_ (user_id));
+joinable!(user_mention -> comment (comment_id));
+joinable!(user_mention -> user_ (recipient_id));
+
+allow_tables_to_appear_in_same_query!(
+ category,
+ comment,
+ comment_like,
+ comment_saved,
+ community,
+ community_follower,
+ community_moderator,
+ community_user_ban,
+ mod_add,
+ mod_add_community,
+ mod_ban,
+ mod_ban_from_community,
+ mod_lock_post,
+ mod_remove_comment,
+ mod_remove_community,
+ mod_remove_post,
+ mod_sticky_post,
+ post,
+ post_like,
+ post_read,
+ post_saved,
+ site,
+ user_,
+ user_ban,
+ user_mention,
+);
diff --git a/server/src/db/user_mention.rs b/server/src/db/user_mention.rs
new file mode 100644
index 00000000..d4dc0a51
--- /dev/null
+++ b/server/src/db/user_mention.rs
@@ -0,0 +1,169 @@
+use super::comment::Comment;
+use super::*;
+use crate::schema::user_mention;
+
+#[derive(Queryable, Associations, Identifiable, PartialEq, Debug, Serialize, Deserialize)]
+#[belongs_to(Comment)]
+#[table_name = "user_mention"]
+pub struct UserMention {
+ pub id: i32,
+ pub recipient_id: i32,
+ pub comment_id: i32,
+ pub read: bool,
+ pub published: chrono::NaiveDateTime,
+}
+
+#[derive(Insertable, AsChangeset, Clone)]
+#[table_name = "user_mention"]
+pub struct UserMentionForm {
+ pub recipient_id: i32,
+ pub comment_id: i32,
+ pub read: Option<bool>,
+}
+
+impl Crud<UserMentionForm> for UserMention {
+ fn read(conn: &PgConnection, user_mention_id: i32) -> Result<Self, Error> {
+ use crate::schema::user_mention::dsl::*;
+ user_mention.find(user_mention_id).first::<Self>(conn)
+ }
+
+ fn delete(conn: &PgConnection, user_mention_id: i32) -> Result<usize, Error> {
+ use crate::schema::user_mention::dsl::*;
+ diesel::delete(user_mention.find(user_mention_id)).execute(conn)
+ }
+
+ fn create(conn: &PgConnection, user_mention_form: &UserMentionForm) -> Result<Self, Error> {
+ use crate::schema::user_mention::dsl::*;
+ insert_into(user_mention)
+ .values(user_mention_form)
+ .get_result::<Self>(conn)
+ }
+
+ fn update(
+ conn: &PgConnection,
+ user_mention_id: i32,
+ user_mention_form: &UserMentionForm,
+ ) -> Result<Self, Error> {
+ use crate::schema::user_mention::dsl::*;
+ diesel::update(user_mention.find(user_mention_id))
+ .set(user_mention_form)
+ .get_result::<Self>(conn)
+ }
+}
+
+#[cfg(test)]
+mod tests {
+ use super::super::comment::*;
+ use super::super::community::*;
+ use super::super::post::*;
+ use super::super::user::*;
+ use super::*;
+ #[test]
+ fn test_crud() {
+ let conn = establish_connection();
+
+ let new_user = UserForm {
+ name: "terrylake".into(),
+ fedi_name: "rrf".into(),
+ preferred_username: None,
+ password_encrypted: "nope".into(),
+ email: None,
+ admin: false,
+ banned: false,
+ updated: None,
+ show_nsfw: false,
+ theme: "darkly".into(),
+ };
+
+ let inserted_user = User_::create(&conn, &new_user).unwrap();
+
+ let recipient_form = UserForm {
+ name: "terrylakes recipient".into(),
+ fedi_name: "rrf".into(),
+ preferred_username: None,
+ password_encrypted: "nope".into(),
+ email: None,
+ admin: false,
+ banned: false,
+ updated: None,
+ show_nsfw: false,
+ theme: "darkly".into(),
+ };
+
+ let inserted_recipient = User_::create(&conn, &recipient_form).unwrap();
+
+ let new_community = CommunityForm {
+ name: "test community lake".to_string(),
+ title: "nada".to_owned(),
+ description: None,
+ category_id: 1,
+ creator_id: inserted_user.id,
+ removed: None,
+ deleted: None,
+ updated: None,
+ nsfw: false,
+ };
+
+ let inserted_community = Community::create(&conn, &new_community).unwrap();
+
+ let new_post = PostForm {
+ name: "A test post".into(),
+ creator_id: inserted_user.id,
+ url: None,
+ body: None,
+ community_id: inserted_community.id,
+ removed: None,
+ deleted: None,
+ locked: None,
+ stickied: None,
+ updated: None,
+ nsfw: false,
+ };
+
+ let inserted_post = Post::create(&conn, &new_post).unwrap();
+
+ let comment_form = CommentForm {
+ content: "A test comment".into(),
+ creator_id: inserted_user.id,
+ post_id: inserted_post.id,
+ removed: None,
+ deleted: None,
+ read: None,
+ parent_id: None,
+ updated: None,
+ };
+
+ let inserted_comment = Comment::create(&conn, &comment_form).unwrap();
+
+ let user_mention_form = UserMentionForm {
+ recipient_id: inserted_recipient.id,
+ comment_id: inserted_comment.id,
+ read: None,
+ };
+
+ let inserted_mention = UserMention::create(&conn, &user_mention_form).unwrap();
+
+ let expected_mention = UserMention {
+ id: inserted_mention.id,
+ recipient_id: inserted_mention.recipient_id,
+ comment_id: inserted_mention.comment_id,
+ read: false,
+ published: inserted_mention.published,
+ };
+
+ let read_mention = UserMention::read(&conn, inserted_mention.id).unwrap();
+ let updated_mention =
+ UserMention::update(&conn, inserted_mention.id, &user_mention_form).unwrap();
+ let num_deleted = UserMention::delete(&conn, inserted_mention.id).unwrap();
+ Comment::delete(&conn, inserted_comment.id).unwrap();
+ Post::delete(&conn, inserted_post.id).unwrap();
+ Community::delete(&conn, inserted_community.id).unwrap();
+ User_::delete(&conn, inserted_user.id).unwrap();
+ User_::delete(&conn, inserted_recipient.id).unwrap();
+
+ assert_eq!(expected_mention, read_mention);
+ assert_eq!(expected_mention, inserted_mention);
+ assert_eq!(expected_mention, updated_mention);
+ assert_eq!(1, num_deleted);
+ }
+}
diff --git a/server/src/db/user_mention_view.rs b/server/src/db/user_mention_view.rs
new file mode 100644
index 00000000..6676ab9a
--- /dev/null
+++ b/server/src/db/user_mention_view.rs
@@ -0,0 +1,117 @@
+use super::*;
+
+// The faked schema since diesel doesn't do views
+table! {
+ user_mention_view (id) {
+ id -> Int4,
+ user_mention_id -> Int4,
+ creator_id -> Int4,
+ post_id -> Int4,
+ parent_id -> Nullable<Int4>,
+ content -> Text,
+ removed -> Bool,
+ read -> Bool,
+ published -> Timestamp,
+ updated -> Nullable<Timestamp>,
+ deleted -> Bool,
+ community_id -> Int4,
+ banned -> Bool,
+ banned_from_community -> Bool,
+ creator_name -> Varchar,
+ score -> BigInt,
+ upvotes -> BigInt,
+ downvotes -> BigInt,
+ user_id -> Nullable<Int4>,
+ my_vote -> Nullable<Int4>,
+ saved -> Nullable<Bool>,
+ recipient_id -> Int4,
+ }
+}
+
+#[derive(
+ Queryable, Identifiable, PartialEq, Debug, Serialize, Deserialize, QueryableByName, Clone,
+)]
+#[table_name = "user_mention_view"]
+pub struct UserMentionView {
+ pub id: i32,
+ pub user_mention_id: i32,
+ pub creator_id: i32,
+ pub post_id: i32,
+ pub parent_id: Option<i32>,
+ pub content: String,
+ pub removed: bool,
+ pub read: bool,
+ pub published: chrono::NaiveDateTime,
+ pub updated: Option<chrono::NaiveDateTime>,
+ pub deleted: bool,
+ pub community_id: i32,
+ pub banned: bool,
+ pub banned_from_community: bool,
+ pub creator_name: String,
+ pub score: i64,
+ pub upvotes: i64,
+ pub downvotes: i64,
+ pub user_id: Option<i32>,
+ pub my_vote: Option<i32>,
+ pub saved: Option<bool>,
+ pub recipient_id: i32,
+}
+
+impl UserMentionView {
+ pub fn get_mentions(
+ conn: &PgConnection,
+ for_user_id: i32,
+ sort: &SortType,
+ unread_only: bool,
+ page: Option<i64>,
+ limit: Option<i64>,
+ ) -> Result<Vec<Self>, Error> {
+ use super::user_mention_view::user_mention_view::dsl::*;
+
+ let (limit, offset) = limit_and_offset(page, limit);
+
+ let mut query = user_mention_view.into_boxed();
+
+ query = query
+ .filter(user_id.eq(for_user_id))
+ .filter(recipient_id.eq(for_user_id));
+
+ if unread_only {
+ query = query.filter(read.eq(false));
+ }
+
+ query = match sort {
+ // SortType::Hot => query.order_by(hot_rank.desc()),
+ SortType::New => query.order_by(published.desc()),
+ SortType::TopAll => query.order_by(score.desc()),
+ SortType::TopYear => query
+ .filter(published.gt(now - 1.years()))
+ .order_by(score.desc()),
+ SortType::TopMonth => query
+ .filter(published.gt(now - 1.months()))
+ .order_by(score.desc()),
+ SortType::TopWeek => query
+ .filter(published.gt(now - 1.weeks()))
+ .order_by(score.desc()),
+ SortType::TopDay => query
+ .filter(published.gt(now - 1.days()))
+ .order_by(score.desc()),
+ _ => query.order_by(published.desc()),
+ };
+
+ query.limit(limit).offset(offset).load::<Self>(conn)
+ }
+
+ pub fn read(
+ conn: &PgConnection,
+ from_user_mention_id: i32,
+ from_recipient_id: i32,
+ ) -> Result<Self, Error> {
+ use super::user_mention_view::user_mention_view::dsl::*;
+
+ user_mention_view
+ .filter(user_mention_id.eq(from_user_mention_id))
+ .filter(user_id.eq(from_recipient_id))
+ .first::<Self>(conn)
+ }
+}
generated by cgit v1.2.3 (git 2.25.1) at 2024-06-13 22:45:17 +0000