From aec4fdecc10be35cde5dc42308960f10bc452187 Mon Sep 17 00:00:00 2001 From: Tyler Murphy Date: Wed, 15 Feb 2023 00:01:44 -0500 Subject: make database calls 1 conn --- src/database/comments.rs | 165 ++++++++++++------------ src/database/friends.rs | 173 ++++++++++++------------- src/database/likes.rs | 136 ++++++++++---------- src/database/mod.rs | 37 +++--- src/database/posts.rs | 205 +++++++++++++++--------------- src/database/sessions.rs | 105 ++++++++-------- src/database/users.rs | 319 ++++++++++++++++++++++++----------------------- 7 files changed, 592 insertions(+), 548 deletions(-) (limited to 'src/database') diff --git a/src/database/comments.rs b/src/database/comments.rs index 9e0eaf9..5a1b39d 100644 --- a/src/database/comments.rs +++ b/src/database/comments.rs @@ -3,89 +3,100 @@ use std::time::{Duration, SystemTime, UNIX_EPOCH}; use rusqlite::Row; use tracing::instrument; -use crate::{database, types::comment::Comment}; +use crate::types::comment::Comment; -pub fn init() -> Result<(), rusqlite::Error> { - let sql = " - CREATE TABLE IF NOT EXISTS comments ( - comment_id INTEGER PRIMARY KEY AUTOINCREMENT, - user_id INTEGER NOT NULL, - post_id INTEGER NOT NULL, - date INTEGER NOT NULL, - content VARCHAR(255) NOT NULL, - FOREIGN KEY(user_id) REFERENCES users(user_id), - FOREIGN KEY(post_id) REFERENCES posts(post_id) - ); - "; - let conn = database::connect()?; - conn.execute(sql, ())?; +use super::Database; - let sql2 = "CREATE INDEX IF NOT EXISTS post_ids on comments (post_id);"; - conn.execute(sql2, ())?; +impl Database { + pub fn init_comments(&self) -> Result<(), rusqlite::Error> { + let sql = " + CREATE TABLE IF NOT EXISTS comments ( + comment_id INTEGER PRIMARY KEY AUTOINCREMENT, + user_id INTEGER NOT NULL, + post_id INTEGER NOT NULL, + date INTEGER NOT NULL, + content VARCHAR(255) NOT NULL, + FOREIGN KEY(user_id) REFERENCES users(user_id), + FOREIGN KEY(post_id) REFERENCES posts(post_id) + ); + "; + self.0.execute(sql, ())?; - Ok(()) -} + let sql2 = "CREATE INDEX IF NOT EXISTS post_ids on comments (post_id);"; + self.0.execute(sql2, ())?; -fn comment_from_row(row: &Row) -> Result { - let comment_id = row.get(0)?; - let user_id = row.get(1)?; - let post_id = row.get(2)?; - let date = row.get(3)?; - let content = row.get(4)?; + Ok(()) + } - Ok(Comment { - comment_id, - user_id, - post_id, - date, - content, - }) -} + fn comment_from_row(row: &Row) -> Result { + let comment_id = row.get(0)?; + let user_id = row.get(1)?; + let post_id = row.get(2)?; + let date = row.get(3)?; + let content = row.get(4)?; -#[instrument()] -pub fn get_comments_page(page: u64, post_id: u64) -> Result, rusqlite::Error> { - tracing::trace!("Retrieving comments page"); - let page_size = 5; - let conn = database::connect()?; - let mut stmt = conn.prepare( - "SELECT * FROM comments WHERE post_id = ? ORDER BY comment_id ASC LIMIT ? OFFSET ?", - )?; - let row = stmt.query_map([post_id, page_size, page_size * page], |row| { - let row = comment_from_row(row)?; - Ok(row) - })?; - Ok(row.into_iter().flatten().collect()) -} + Ok(Comment { + comment_id, + user_id, + post_id, + date, + content, + }) + } -#[instrument()] -pub fn get_all_comments() -> Result, rusqlite::Error> { - tracing::trace!("Retrieving comments page"); - let conn = database::connect()?; - let mut stmt = conn.prepare("SELECT * FROM comments ORDER BY comment_id DESC")?; - let row = stmt.query_map([], |row| { - let row = comment_from_row(row)?; - Ok(row) - })?; - Ok(row.into_iter().flatten().collect()) -} + #[instrument(skip(self))] + pub fn get_comments_page( + &self, + page: u64, + post_id: u64, + ) -> Result, rusqlite::Error> { + tracing::trace!("Retrieving comments page"); + let page_size = 5; + let mut stmt = self.0.prepare( + "SELECT * FROM comments WHERE post_id = ? ORDER BY comment_id ASC LIMIT ? OFFSET ?", + )?; + let row = stmt.query_map([post_id, page_size, page_size * page], |row| { + let row = Self::comment_from_row(row)?; + Ok(row) + })?; + Ok(row.into_iter().flatten().collect()) + } + + #[instrument(skip(self))] + pub fn get_all_comments(&self) -> Result, rusqlite::Error> { + tracing::trace!("Retrieving comments page"); + let mut stmt = self + .0 + .prepare("SELECT * FROM comments ORDER BY comment_id DESC")?; + let row = stmt.query_map([], |row| { + let row = Self::comment_from_row(row)?; + Ok(row) + })?; + Ok(row.into_iter().flatten().collect()) + } -#[instrument()] -pub fn add_comment(user_id: u64, post_id: u64, content: &str) -> Result { - tracing::trace!("Adding comment"); - let date = u64::try_from( - SystemTime::now() - .duration_since(UNIX_EPOCH) - .unwrap_or(Duration::ZERO) - .as_millis(), - ) - .unwrap_or(0); - let conn = database::connect()?; - let mut stmt = conn.prepare( - "INSERT INTO comments (user_id, post_id, date, content) VALUES(?,?,?,?) RETURNING *;", - )?; - let post = stmt.query_row((user_id, post_id, date, content), |row| { - let row = comment_from_row(row)?; - Ok(row) - })?; - Ok(post) + #[instrument(skip(self))] + pub fn add_comment( + &self, + user_id: u64, + post_id: u64, + content: &str, + ) -> Result { + tracing::trace!("Adding comment"); + let date = u64::try_from( + SystemTime::now() + .duration_since(UNIX_EPOCH) + .unwrap_or(Duration::ZERO) + .as_millis(), + ) + .unwrap_or(0); + let mut stmt = self.0.prepare( + "INSERT INTO comments (user_id, post_id, date, content) VALUES(?,?,?,?) RETURNING *;", + )?; + let post = stmt.query_row((user_id, post_id, date, content), |row| { + let row = Self::comment_from_row(row)?; + Ok(row) + })?; + Ok(post) + } } diff --git a/src/database/friends.rs b/src/database/friends.rs index 0b78488..31434d4 100644 --- a/src/database/friends.rs +++ b/src/database/friends.rs @@ -1,97 +1,100 @@ use tracing::instrument; -use crate::{ - database::{self, users::user_from_row}, - types::user::{User, FOLLOWED, FOLLOWING, NO_RELATION}, -}; +use crate::types::user::{User, FOLLOWED, FOLLOWING, NO_RELATION}; -pub fn init() -> Result<(), rusqlite::Error> { - let sql = " - CREATE TABLE IF NOT EXISTS friends ( - follower_id INTEGER NOT NULL, - followee_id INTEGER NOT NULL, - FOREIGN KEY(follower_id) REFERENCES users(user_id), - FOREIGN KEY(followee_id) REFERENCES users(user_id), - PRIMARY KEY (follower_id, followee_id) - ); - "; - let conn = database::connect()?; - conn.execute(sql, ())?; - Ok(()) -} +use super::Database; -#[instrument()] -pub fn get_friend_status(user_id_1: u64, user_id_2: u64) -> Result { - tracing::trace!("Retrieving friend status"); - let conn = database::connect()?; - let mut stmt = conn.prepare("SELECT * FROM friends WHERE (follower_id = ? AND followee_id = ?) OR (follower_id = ? AND followee_id = ?);")?; - let mut status = NO_RELATION; - let rows: Vec = stmt - .query_map([user_id_1, user_id_2, user_id_2, user_id_1], |row| { - let id: u64 = row.get(0)?; - Ok(id) - })? - .into_iter() - .flatten() - .collect(); +impl Database { + pub fn init_friends(&self) -> Result<(), rusqlite::Error> { + let sql = " + CREATE TABLE IF NOT EXISTS friends ( + follower_id INTEGER NOT NULL, + followee_id INTEGER NOT NULL, + FOREIGN KEY(follower_id) REFERENCES users(user_id), + FOREIGN KEY(followee_id) REFERENCES users(user_id), + PRIMARY KEY (follower_id, followee_id) + ); + "; + self.0.execute(sql, ())?; + Ok(()) + } - for follower in rows { - if follower == user_id_1 { - status |= FOLLOWING; - } + #[instrument(skip(self))] + pub fn get_friend_status(&self, user_id_1: u64, user_id_2: u64) -> Result { + tracing::trace!("Retrieving friend status"); + let mut stmt = self.0.prepare("SELECT * FROM friends WHERE (follower_id = ? AND followee_id = ?) OR (follower_id = ? AND followee_id = ?);")?; + let mut status = NO_RELATION; + let rows: Vec = stmt + .query_map([user_id_1, user_id_2, user_id_2, user_id_1], |row| { + let id: u64 = row.get(0)?; + Ok(id) + })? + .into_iter() + .flatten() + .collect(); - if follower == user_id_2 { - status |= FOLLOWED; + for follower in rows { + if follower == user_id_1 { + status |= FOLLOWING; + } + + if follower == user_id_2 { + status |= FOLLOWED; + } } - } - Ok(status) -} + Ok(status) + } -#[instrument()] -pub fn get_friends(user_id: u64) -> Result, rusqlite::Error> { - tracing::trace!("Retrieving friends"); - let conn = database::connect()?; - let mut stmt = conn.prepare( - " - SELECT * - FROM users u - WHERE EXISTS ( - SELECT NULL - FROM friends f - WHERE u.user_id = f.follower_id - AND f.followee_id = ? - ) - AND EXISTS ( - SELECT NULL - FROM friends f - WHERE u.user_id = f.followee_id - AND f.follower_id = ? - ) - ", - )?; - let row = stmt.query_map([user_id, user_id], |row| { - let row = user_from_row(row, true)?; - Ok(row) - })?; - Ok(row.into_iter().flatten().collect()) -} + #[instrument(skip(self))] + pub fn get_friends(&self, user_id: u64) -> Result, rusqlite::Error> { + tracing::trace!("Retrieving friends"); + let mut stmt = self.0.prepare( + " + SELECT * + FROM users u + WHERE EXISTS ( + SELECT NULL + FROM friends f + WHERE u.user_id = f.follower_id + AND f.followee_id = ? + ) + AND EXISTS ( + SELECT NULL + FROM friends f + WHERE u.user_id = f.followee_id + AND f.follower_id = ? + ) + ", + )?; + let row = stmt.query_map([user_id, user_id], |row| { + let row = Self::user_from_row(row, true)?; + Ok(row) + })?; + Ok(row.into_iter().flatten().collect()) + } -#[instrument()] -pub fn set_following(user_id_1: u64, user_id_2: u64) -> Result { - tracing::trace!("Setting following"); - let conn = database::connect()?; - let mut stmt = - conn.prepare("INSERT OR REPLACE INTO friends (follower_id, followee_id) VALUES (?,?)")?; - let changes = stmt.execute([user_id_1, user_id_2])?; - Ok(changes == 1) -} + #[instrument(skip(self))] + pub fn set_following(&self, user_id_1: u64, user_id_2: u64) -> Result { + tracing::trace!("Setting following"); + let mut stmt = self + .0 + .prepare("INSERT OR REPLACE INTO friends (follower_id, followee_id) VALUES (?,?)")?; + let changes = stmt.execute([user_id_1, user_id_2])?; + Ok(changes == 1) + } -#[instrument()] -pub fn remove_following(user_id_1: u64, user_id_2: u64) -> Result { - tracing::trace!("Removing following"); - let conn = database::connect()?; - let mut stmt = conn.prepare("DELETE FROM friends WHERE follower_id = ? AND followee_id = ?")?; - let changes = stmt.execute([user_id_1, user_id_2])?; - Ok(changes == 1) + #[instrument(skip(self))] + pub fn remove_following( + &self, + user_id_1: u64, + user_id_2: u64, + ) -> Result { + tracing::trace!("Removing following"); + let mut stmt = self + .0 + .prepare("DELETE FROM friends WHERE follower_id = ? AND followee_id = ?")?; + let changes = stmt.execute([user_id_1, user_id_2])?; + Ok(changes == 1) + } } diff --git a/src/database/likes.rs b/src/database/likes.rs index f6a130b..b313c97 100644 --- a/src/database/likes.rs +++ b/src/database/likes.rs @@ -1,75 +1,81 @@ use rusqlite::OptionalExtension; use tracing::instrument; -use crate::{database, types::like::Like}; +use crate::types::like::Like; -pub fn init() -> Result<(), rusqlite::Error> { - let sql = " - CREATE TABLE IF NOT EXISTS likes ( - user_id INTEGER NOT NULL, - post_id INTEGER NOT NULL, - FOREIGN KEY(user_id) REFERENCES users(user_id), - FOREIGN KEY(post_id) REFERENCES posts(post_id), - PRIMARY KEY (user_id, post_id) - ); - "; - let conn = database::connect()?; - conn.execute(sql, ())?; - Ok(()) -} +use super::Database; -#[instrument()] -pub fn get_like_count(post_id: u64) -> Result, rusqlite::Error> { - tracing::trace!("Retrieving like count"); - let conn = database::connect()?; - let mut stmt = conn.prepare("SELECT COUNT(post_id) FROM likes WHERE post_id = ?")?; - let row = stmt - .query_row([post_id], |row| { - let row = row.get(0)?; - Ok(row) - }) - .optional()?; - Ok(row) -} +impl Database { + pub fn init_likes(&self) -> Result<(), rusqlite::Error> { + let sql = " + CREATE TABLE IF NOT EXISTS likes ( + user_id INTEGER NOT NULL, + post_id INTEGER NOT NULL, + FOREIGN KEY(user_id) REFERENCES users(user_id), + FOREIGN KEY(post_id) REFERENCES posts(post_id), + PRIMARY KEY (user_id, post_id) + ); + "; + self.0.execute(sql, ())?; + Ok(()) + } -#[instrument()] -pub fn get_liked(user_id: u64, post_id: u64) -> Result { - tracing::trace!("Retrieving if liked"); - let conn = database::connect()?; - let mut stmt = conn.prepare("SELECT * FROM likes WHERE user_id = ? AND post_id = ?")?; - let liked = stmt.query_row([user_id, post_id], |_| Ok(())).optional()?; - Ok(liked.is_some()) -} + #[instrument(skip(self))] + pub fn get_like_count(&self, post_id: u64) -> Result, rusqlite::Error> { + tracing::trace!("Retrieving like count"); + let mut stmt = self + .0 + .prepare("SELECT COUNT(post_id) FROM likes WHERE post_id = ?")?; + let row = stmt + .query_row([post_id], |row| { + let row = row.get(0)?; + Ok(row) + }) + .optional()?; + Ok(row) + } -#[instrument()] -pub fn add_liked(user_id: u64, post_id: u64) -> Result { - tracing::trace!("Adding like"); - let conn = database::connect()?; - let mut stmt = conn.prepare("INSERT OR REPLACE INTO likes (user_id, post_id) VALUES (?,?)")?; - let changes = stmt.execute([user_id, post_id])?; - Ok(changes == 1) -} + #[instrument(skip(self))] + pub fn get_liked(&self, user_id: u64, post_id: u64) -> Result { + tracing::trace!("Retrieving if liked"); + let mut stmt = self + .0 + .prepare("SELECT * FROM likes WHERE user_id = ? AND post_id = ?")?; + let liked = stmt.query_row([user_id, post_id], |_| Ok(())).optional()?; + Ok(liked.is_some()) + } -#[instrument()] -pub fn remove_liked(user_id: u64, post_id: u64) -> Result { - tracing::trace!("Removing like"); - let conn = database::connect()?; - let mut stmt = conn.prepare("DELETE FROM likes WHERE user_id = ? AND post_id = ?;")?; - let changes = stmt.execute((user_id, post_id))?; - Ok(changes == 1) -} + #[instrument(skip(self))] + pub fn add_liked(&self, user_id: u64, post_id: u64) -> Result { + tracing::trace!("Adding like"); + let mut stmt = self + .0 + .prepare("INSERT OR REPLACE INTO likes (user_id, post_id) VALUES (?,?)")?; + let changes = stmt.execute([user_id, post_id])?; + Ok(changes == 1) + } + + #[instrument(skip(self))] + pub fn remove_liked(&self, user_id: u64, post_id: u64) -> Result { + tracing::trace!("Removing like"); + let mut stmt = self + .0 + .prepare("DELETE FROM likes WHERE user_id = ? AND post_id = ?;")?; + let changes = stmt.execute((user_id, post_id))?; + Ok(changes == 1) + } -#[instrument()] -pub fn get_all_likes() -> Result, rusqlite::Error> { - tracing::trace!("Retrieving comments page"); - let conn = database::connect()?; - let mut stmt = conn.prepare("SELECT * FROM likes")?; - let row = stmt.query_map([], |row| { - let like = Like { - user_id: row.get(0)?, - post_id: row.get(1)?, - }; - Ok(like) - })?; - Ok(row.into_iter().flatten().collect()) + #[instrument(skip(self))] + pub fn get_all_likes(&self) -> Result, rusqlite::Error> { + tracing::trace!("Retrieving comments page"); + let mut stmt = self.0.prepare("SELECT * FROM likes")?; + let row = stmt.query_map([], |row| { + let like = Like { + user_id: row.get(0)?, + post_id: row.get(1)?, + }; + Ok(like) + })?; + Ok(row.into_iter().flatten().collect()) + } } diff --git a/src/database/mod.rs b/src/database/mod.rs index d22a350..67e05c6 100644 --- a/src/database/mod.rs +++ b/src/database/mod.rs @@ -1,3 +1,4 @@ +use rusqlite::Connection; use tracing::instrument; pub mod comments; @@ -7,23 +8,29 @@ pub mod posts; pub mod sessions; pub mod users; -pub fn connect() -> Result { - rusqlite::Connection::open("xssbook.db") +#[derive(Debug)] +pub struct Database(Connection); + +impl Database { + pub fn connect() -> Result { + let conn = rusqlite::Connection::open("xssbook.db")?; + Ok(Self(conn)) + } + + #[instrument(skip(self))] + pub fn query(&self, query: String) -> Result { + tracing::trace!("Running custom query"); + self.0.execute(&query, []) + } } pub fn init() -> Result<(), rusqlite::Error> { - users::init()?; - posts::init()?; - sessions::init()?; - likes::init()?; - comments::init()?; - friends::init()?; + let db = Database::connect()?; + db.init_users()?; + db.init_posts()?; + db.init_sessions()?; + db.init_likes()?; + db.init_comments()?; + db.init_friends()?; Ok(()) } - -#[instrument()] -pub fn query(query: String) -> Result { - tracing::trace!("Running custom query"); - let conn = connect()?; - conn.execute(&query, []) -} diff --git a/src/database/posts.rs b/src/database/posts.rs index c33e7e7..fa0fd3c 100644 --- a/src/database/posts.rs +++ b/src/database/posts.rs @@ -3,115 +3,122 @@ use std::time::{Duration, SystemTime, UNIX_EPOCH}; use rusqlite::{OptionalExtension, Row}; use tracing::instrument; -use crate::database; use crate::types::post::Post; -use super::{comments, likes}; +use super::Database; -pub fn init() -> Result<(), rusqlite::Error> { - let sql = " - CREATE TABLE IF NOT EXISTS posts ( - post_id INTEGER PRIMARY KEY AUTOINCREMENT, - user_id INTEGER NOT NULL, - content VARCHAR(500) NOT NULL, - date INTEGER NOT NULL, - FOREIGN KEY(user_id) REFERENCES users(user_id) - ); - "; - let conn = database::connect()?; - conn.execute(sql, ())?; - Ok(()) -} - -fn post_from_row(row: &Row) -> Result { - let post_id = row.get(0)?; - let user_id = row.get(1)?; - let content = row.get(2)?; - let date = row.get(3)?; +impl Database { + pub fn init_posts(&self) -> Result<(), rusqlite::Error> { + let sql = " + CREATE TABLE IF NOT EXISTS posts ( + post_id INTEGER PRIMARY KEY AUTOINCREMENT, + user_id INTEGER NOT NULL, + content VARCHAR(500) NOT NULL, + date INTEGER NOT NULL, + FOREIGN KEY(user_id) REFERENCES users(user_id) + ); + "; + self.0.execute(sql, ())?; + Ok(()) + } - let comments = comments::get_comments_page(0, post_id).unwrap_or_else(|_| Vec::new()); - let likes = likes::get_like_count(post_id).unwrap_or(None).unwrap_or(0); + fn post_from_row(&self, row: &Row) -> Result { + let post_id = row.get(0)?; + let user_id = row.get(1)?; + let content = row.get(2)?; + let date = row.get(3)?; - Ok(Post { - post_id, - user_id, - content, - date, - likes, - liked: false, - comments, - }) -} + let comments = self + .get_comments_page(0, post_id) + .unwrap_or_else(|_| Vec::new()); + let likes = self.get_like_count(post_id).unwrap_or(None).unwrap_or(0); -#[instrument()] -pub fn get_post(post_id: u64) -> Result, rusqlite::Error> { - tracing::trace!("Retrieving post"); - let conn = database::connect()?; - let mut stmt = conn.prepare("SELECT * FROM posts WHERE post_id = ?")?; - let row = stmt - .query_row([post_id], |row| { - let row = post_from_row(row)?; - Ok(row) + Ok(Post { + post_id, + user_id, + content, + date, + likes, + liked: false, + comments, }) - .optional()?; - Ok(row) -} + } -#[instrument()] -pub fn get_post_page(page: u64) -> Result, rusqlite::Error> { - tracing::trace!("Retrieving posts page"); - let page_size = 10; - let conn = database::connect()?; - let mut stmt = conn.prepare("SELECT * FROM posts ORDER BY post_id DESC LIMIT ? OFFSET ?")?; - let row = stmt.query_map([page_size, page_size * page], |row| { - let row = post_from_row(row)?; + #[instrument(skip(self))] + pub fn get_post(&self, post_id: u64) -> Result, rusqlite::Error> { + tracing::trace!("Retrieving post"); + let mut stmt = self.0.prepare("SELECT * FROM posts WHERE post_id = ?")?; + let row = stmt + .query_row([post_id], |row| { + let row = self.post_from_row(row)?; + Ok(row) + }) + .optional()?; Ok(row) - })?; - Ok(row.into_iter().flatten().collect()) -} + } -#[instrument()] -pub fn get_all_posts() -> Result, rusqlite::Error> { - tracing::trace!("Retrieving posts page"); - let conn = database::connect()?; - let mut stmt = conn.prepare("SELECT * FROM posts ORDER BY post_id DESC")?; - let row = stmt.query_map([], |row| { - let row = post_from_row(row)?; - Ok(row) - })?; - Ok(row.into_iter().flatten().collect()) -} + #[instrument(skip(self))] + pub fn get_post_page(&self, page: u64) -> Result, rusqlite::Error> { + tracing::trace!("Retrieving posts page"); + let page_size = 10; + let mut stmt = self + .0 + .prepare("SELECT * FROM posts ORDER BY post_id DESC LIMIT ? OFFSET ?")?; + let row = stmt.query_map([page_size, page_size * page], |row| { + let row = self.post_from_row(row)?; + Ok(row) + })?; + Ok(row.into_iter().flatten().collect()) + } -#[instrument()] -pub fn get_users_post_page(user_id: u64, page: u64) -> Result, rusqlite::Error> { - tracing::trace!("Retrieving users posts"); - let page_size = 10; - let conn = database::connect()?; - let mut stmt = conn - .prepare("SELECT * FROM posts WHERE user_id = ? ORDER BY post_id DESC LIMIT ? OFFSET ?")?; - let row = stmt.query_map([user_id, page_size, page_size * page], |row| { - let row = post_from_row(row)?; - Ok(row) - })?; - Ok(row.into_iter().flatten().collect()) -} + #[instrument(skip(self))] + pub fn get_all_posts(&self) -> Result, rusqlite::Error> { + tracing::trace!("Retrieving posts page"); + let mut stmt = self + .0 + .prepare("SELECT * FROM posts ORDER BY post_id DESC")?; + let row = stmt.query_map([], |row| { + let row = self.post_from_row(row)?; + Ok(row) + })?; + Ok(row.into_iter().flatten().collect()) + } -#[instrument()] -pub fn add_post(user_id: u64, content: &str) -> Result { - tracing::trace!("Adding post"); - let date = u64::try_from( - SystemTime::now() - .duration_since(UNIX_EPOCH) - .unwrap_or(Duration::ZERO) - .as_millis(), - ) - .unwrap_or(0); - let conn = database::connect()?; - let mut stmt = - conn.prepare("INSERT INTO posts (user_id, content, date) VALUES(?,?,?) RETURNING *;")?; - let post = stmt.query_row((user_id, content, date), |row| { - let row = post_from_row(row)?; - Ok(row) - })?; - Ok(post) + #[instrument(skip(self))] + pub fn get_users_post_page( + &self, + user_id: u64, + page: u64, + ) -> Result, rusqlite::Error> { + tracing::trace!("Retrieving users posts"); + let page_size = 10; + let mut stmt = self.0.prepare( + "SELECT * FROM posts WHERE user_id = ? ORDER BY post_id DESC LIMIT ? OFFSET ?", + )?; + let row = stmt.query_map([user_id, page_size, page_size * page], |row| { + let row = self.post_from_row(row)?; + Ok(row) + })?; + Ok(row.into_iter().flatten().collect()) + } + + #[instrument(skip(self))] + pub fn add_post(&self, user_id: u64, content: &str) -> Result { + tracing::trace!("Adding post"); + let date = u64::try_from( + SystemTime::now() + .duration_since(UNIX_EPOCH) + .unwrap_or(Duration::ZERO) + .as_millis(), + ) + .unwrap_or(0); + let mut stmt = self + .0 + .prepare("INSERT INTO posts (user_id, content, date) VALUES(?,?,?) RETURNING *;")?; + let post = stmt.query_row((user_id, content, date), |row| { + let row = self.post_from_row(row)?; + Ok(row) + })?; + Ok(post) + } } diff --git a/src/database/sessions.rs b/src/database/sessions.rs index 9adccd4..a50bb51 100644 --- a/src/database/sessions.rs +++ b/src/database/sessions.rs @@ -1,65 +1,64 @@ use rusqlite::OptionalExtension; use tracing::instrument; -use crate::{database, types::session::Session}; +use crate::types::session::Session; -pub fn init() -> Result<(), rusqlite::Error> { - let sql = " - CREATE TABLE IF NOT EXISTS sessions ( - user_id INTEGER PRIMARY KEY NOT NULL, - token TEXT NOT NULL, - FOREIGN KEY(user_id) REFERENCES users(user_id) - ); - "; - let conn = database::connect()?; - conn.execute(sql, ())?; - Ok(()) -} +use super::Database; + +impl Database { + pub fn init_sessions(&self) -> Result<(), rusqlite::Error> { + let sql = " + CREATE TABLE IF NOT EXISTS sessions ( + user_id INTEGER PRIMARY KEY NOT NULL, + token TEXT NOT NULL, + FOREIGN KEY(user_id) REFERENCES users(user_id) + ); + "; + self.0.execute(sql, ())?; + Ok(()) + } + + #[instrument(skip(self))] + pub fn get_session(&self, token: &str) -> Result, rusqlite::Error> { + tracing::trace!("Retrieving session"); + let mut stmt = self.0.prepare("SELECT * FROM sessions WHERE token = ?")?; + let row = stmt + .query_row([token], |row| { + Ok(Session { + user_id: row.get(0)?, + token: row.get(1)?, + }) + }) + .optional()?; + Ok(row) + } -#[instrument()] -pub fn get_session(token: &str) -> Result, rusqlite::Error> { - tracing::trace!("Retrieving session"); - let conn = database::connect()?; - let mut stmt = conn.prepare("SELECT * FROM sessions WHERE token = ?")?; - let row = stmt - .query_row([token], |row| { + #[instrument(skip(self))] + pub fn get_all_sessions(&self) -> Result, rusqlite::Error> { + tracing::trace!("Retrieving session"); + let mut stmt = self.0.prepare("SELECT * FROM sessions")?; + let row = stmt.query_map([], |row| { Ok(Session { user_id: row.get(0)?, token: row.get(1)?, }) - }) - .optional()?; - Ok(row) -} - -#[instrument()] -pub fn get_all_sessions() -> Result, rusqlite::Error> { - tracing::trace!("Retrieving session"); - let conn = database::connect()?; - let mut stmt = conn.prepare("SELECT * FROM sessions")?; - let row = stmt.query_map([], |row| { - Ok(Session { - user_id: row.get(0)?, - token: row.get(1)?, - }) - })?; - Ok(row.into_iter().flatten().collect()) -} + })?; + Ok(row.into_iter().flatten().collect()) + } -#[instrument()] -pub fn set_session(user_id: u64, token: &str) -> Result<(), Box> { - tracing::trace!("Setting new session"); - let conn = database::connect()?; - let sql = "INSERT OR REPLACE INTO sessions (user_id, token) VALUES (?, ?);"; - conn.execute(sql, (user_id, token))?; - Ok(()) -} + #[instrument(skip(self))] + pub fn set_session(&self, user_id: u64, token: &str) -> Result<(), Box> { + tracing::trace!("Setting new session"); + let sql = "INSERT OR REPLACE INTO sessions (user_id, token) VALUES (?, ?);"; + self.0.execute(sql, (user_id, token))?; + Ok(()) + } -#[instrument()] -pub fn delete_session(user_id: u64) -> Result<(), Box> { - tracing::trace!("Deleting session"); - let conn = database::connect()?; - let sql = "DELETE FROM sessions WHERE user_id = ?;"; - conn.execute(sql, [user_id])?; - Ok(()) + #[instrument(skip(self))] + pub fn delete_session(&self, user_id: u64) -> Result<(), Box> { + tracing::trace!("Deleting session"); + let sql = "DELETE FROM sessions WHERE user_id = ?;"; + self.0.execute(sql, [user_id])?; + Ok(()) + } } diff --git a/src/database/users.rs b/src/database/users.rs index 6062ea8..9df69ee 100644 --- a/src/database/users.rs +++ b/src/database/users.rs @@ -2,169 +2,180 @@ use rusqlite::{OptionalExtension, Row}; use std::time::{Duration, SystemTime, UNIX_EPOCH}; use tracing::instrument; -use crate::{api::RegistrationRequet, database, types::user::User}; - -pub fn init() -> Result<(), rusqlite::Error> { - let sql = " - CREATE TABLE IF NOT EXISTS users ( - user_id INTEGER PRIMARY KEY AUTOINCREMENT, - firstname VARCHAR(20) NOT NULL, - lastname VARCHAR(20) NOT NULL, - email VARCHAR(50) NOT NULL, - password VARCHAR(50) NOT NULL, - gender VARCHAR(100) NOT NULL, - date BIGINT NOT NULL, - day TINYINT NOT NULL, - month TINYINT NOT NULL, - year INTEGER NOT NULL - ); - "; - let conn = database::connect()?; - conn.execute(sql, ())?; - - let sql2 = "CREATE UNIQUE INDEX IF NOT EXISTS emails on users (email);"; - conn.execute(sql2, ())?; - - let sql3 = "CREATE UNIQUE INDEX IF NOT EXISTS passwords on users (password);"; - conn.execute(sql3, ())?; - - Ok(()) -} +use crate::{api::RegistrationRequet, types::user::User}; -pub fn user_from_row(row: &Row, hide_password: bool) -> Result { - let user_id = row.get(0)?; - let firstname = row.get(1)?; - let lastname = row.get(2)?; - let email = row.get(3)?; - let password = row.get(4)?; - let gender = row.get(5)?; - let date = row.get(6)?; - let day = row.get(7)?; - let month = row.get(8)?; - let year = row.get(9)?; - - let password = if hide_password { - String::new() - } else { - password - }; - - Ok(User { - user_id, - firstname, - lastname, - email, - password, - gender, - date, - day, - month, - year, - }) -} +use super::Database; -#[instrument()] -pub fn get_user_by_id(user_id: u64, hide_password: bool) -> Result, rusqlite::Error> { - tracing::trace!("Retrieving user by id"); - let conn = database::connect()?; - let mut stmt = conn.prepare("SELECT * FROM users WHERE user_id = ?")?; - let row = stmt - .query_row([user_id], |row| { - let row = user_from_row(row, hide_password)?; - Ok(row) - }) - .optional()?; - Ok(row) -} +impl Database { + pub fn init_users(&self) -> Result<(), rusqlite::Error> { + let sql = " + CREATE TABLE IF NOT EXISTS users ( + user_id INTEGER PRIMARY KEY AUTOINCREMENT, + firstname VARCHAR(20) NOT NULL, + lastname VARCHAR(20) NOT NULL, + email VARCHAR(50) NOT NULL, + password VARCHAR(50) NOT NULL, + gender VARCHAR(100) NOT NULL, + date BIGINT NOT NULL, + day TINYINT NOT NULL, + month TINYINT NOT NULL, + year INTEGER NOT NULL + ); + "; + self.0.execute(sql, ())?; -#[instrument()] -pub fn get_user_by_email( - email: &str, - hide_password: bool, -) -> Result, rusqlite::Error> { - tracing::trace!("Retrieving user by email"); - let conn = database::connect()?; - let mut stmt = conn.prepare("SELECT * FROM users WHERE email = ?")?; - let row = stmt - .query_row([email], |row| { - let row = user_from_row(row, hide_password)?; - Ok(row) - }) - .optional()?; - Ok(row) -} + let sql2 = "CREATE UNIQUE INDEX IF NOT EXISTS emails on users (email);"; + self.0.execute(sql2, ())?; -#[instrument()] -pub fn get_user_by_password( - password: &str, - hide_password: bool, -) -> Result, rusqlite::Error> { - tracing::trace!("Retrieving user by password"); - let conn = database::connect()?; - let mut stmt = conn.prepare("SELECT * FROM users WHERE password = ?")?; - let row = stmt - .query_row([password], |row| { - let row = user_from_row(row, hide_password)?; - Ok(row) + let sql3 = "CREATE UNIQUE INDEX IF NOT EXISTS passwords on users (password);"; + self.0.execute(sql3, ())?; + + Ok(()) + } + + pub fn user_from_row(row: &Row, hide_password: bool) -> Result { + let user_id = row.get(0)?; + let firstname = row.get(1)?; + let lastname = row.get(2)?; + let email = row.get(3)?; + let password = row.get(4)?; + let gender = row.get(5)?; + let date = row.get(6)?; + let day = row.get(7)?; + let month = row.get(8)?; + let year = row.get(9)?; + + let password = if hide_password { + String::new() + } else { + password + }; + + Ok(User { + user_id, + firstname, + lastname, + email, + password, + gender, + date, + day, + month, + year, }) - .optional()?; - Ok(row) -} + } -#[instrument()] -pub fn get_user_page(page: u64, hide_password: bool) -> Result, rusqlite::Error> { - tracing::trace!("Retrieving user page"); - let page_size = 5; - let conn = database::connect()?; - let mut stmt = conn.prepare("SELECT * FROM users ORDER BY user_id DESC LIMIT ? OFFSET ?")?; - let row = stmt.query_map([page_size, page_size * page], |row| { - let row = user_from_row(row, hide_password)?; + #[instrument(skip(self))] + pub fn get_user_by_id( + &self, + user_id: u64, + hide_password: bool, + ) -> Result, rusqlite::Error> { + tracing::trace!("Retrieving user by id"); + let mut stmt = self.0.prepare("SELECT * FROM users WHERE user_id = ?")?; + let row = stmt + .query_row([user_id], |row| { + let row = Self::user_from_row(row, hide_password)?; + Ok(row) + }) + .optional()?; Ok(row) - })?; - Ok(row.into_iter().flatten().collect()) -} + } -#[instrument()] -pub fn get_all_users() -> Result, rusqlite::Error> { - tracing::trace!("Retrieving user page"); - let conn = database::connect()?; - let mut stmt = conn.prepare("SELECT * FROM users ORDER BY user_id DESC")?; - let row = stmt.query_map([], |row| { - let row = user_from_row(row, false)?; + #[instrument(skip(self))] + pub fn get_user_by_email( + &self, + email: &str, + hide_password: bool, + ) -> Result, rusqlite::Error> { + tracing::trace!("Retrieving user by email"); + let mut stmt = self.0.prepare("SELECT * FROM users WHERE email = ?")?; + let row = stmt + .query_row([email], |row| { + let row = Self::user_from_row(row, hide_password)?; + Ok(row) + }) + .optional()?; Ok(row) - })?; - Ok(row.into_iter().flatten().collect()) -} + } -#[instrument()] -pub fn add_user(request: RegistrationRequet) -> Result { - tracing::trace!("Adding new user"); - let date = u64::try_from( - SystemTime::now() - .duration_since(UNIX_EPOCH) - .unwrap_or(Duration::ZERO) - .as_millis(), - ) - .unwrap_or(0); - - let conn = database::connect()?; - let mut stmt = conn.prepare("INSERT INTO users (firstname, lastname, email, password, gender, date, day, month, year) VALUES(?,?,?,?,?,?,?,?,?) RETURNING *;")?; - let user = stmt.query_row( - ( - request.firstname, - request.lastname, - request.email, - request.password, - request.gender, - date, - request.day, - request.month, - request.year, - ), - |row| { - let row = user_from_row(row, false)?; + #[instrument(skip(self))] + pub fn get_user_by_password( + &self, + password: &str, + hide_password: bool, + ) -> Result, rusqlite::Error> { + tracing::trace!("Retrieving user by password"); + let mut stmt = self.0.prepare("SELECT * FROM users WHERE password = ?")?; + let row = stmt + .query_row([password], |row| { + let row = Self::user_from_row(row, hide_password)?; + Ok(row) + }) + .optional()?; + Ok(row) + } + + #[instrument(skip(self))] + pub fn get_user_page( + &self, + page: u64, + hide_password: bool, + ) -> Result, rusqlite::Error> { + tracing::trace!("Retrieving user page"); + let page_size = 5; + let mut stmt = self + .0 + .prepare("SELECT * FROM users ORDER BY user_id DESC LIMIT ? OFFSET ?")?; + let row = stmt.query_map([page_size, page_size * page], |row| { + let row = Self::user_from_row(row, hide_password)?; + Ok(row) + })?; + Ok(row.into_iter().flatten().collect()) + } + + #[instrument(skip(self))] + pub fn get_all_users(&self) -> Result, rusqlite::Error> { + tracing::trace!("Retrieving user page"); + let mut stmt = self + .0 + .prepare("SELECT * FROM users ORDER BY user_id DESC")?; + let row = stmt.query_map([], |row| { + let row = Self::user_from_row(row, false)?; Ok(row) - }, - )?; - Ok(user) + })?; + Ok(row.into_iter().flatten().collect()) + } + + #[instrument(skip(self))] + pub fn add_user(&self, request: RegistrationRequet) -> Result { + tracing::trace!("Adding new user"); + let date = u64::try_from( + SystemTime::now() + .duration_since(UNIX_EPOCH) + .unwrap_or(Duration::ZERO) + .as_millis(), + ) + .unwrap_or(0); + + let mut stmt = self.0.prepare("INSERT INTO users (firstname, lastname, email, password, gender, date, day, month, year) VALUES(?,?,?,?,?,?,?,?,?) RETURNING *;")?; + let user = stmt.query_row( + ( + request.firstname, + request.lastname, + request.email, + request.password, + request.gender, + date, + request.day, + request.month, + request.year, + ), + |row| { + let row = Self::user_from_row(row, false)?; + Ok(row) + }, + )?; + Ok(user) + } } -- cgit v1.2.3-freya