refactor

5 files changed, 190 insertions, 43 deletions

diff --git a/src/database/comments.rs b/src/database/comments.rs
new file mode 100644
index 0000000..9e0eaf9
--- /dev/null
+++ b/src/database/comments.rs
@@ -0,0 +1,91 @@
+use std::time::{Duration, SystemTime, UNIX_EPOCH};
+
+use rusqlite::Row;
+use tracing::instrument;
+
+use crate::{database, 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, ())?;
+
+    let sql2 = "CREATE INDEX IF NOT EXISTS post_ids on comments (post_id);";
+    conn.execute(sql2, ())?;
+
+    Ok(())
+}
+
+fn comment_from_row(row: &Row) -> Result<Comment, rusqlite::Error> {
+    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(Comment {
+        comment_id,
+        user_id,
+        post_id,
+        date,
+        content,
+    })
+}
+
+#[instrument()]
+pub fn get_comments_page(page: u64, post_id: u64) -> Result<Vec<Comment>, 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())
+}
+
+#[instrument()]
+pub fn get_all_comments() -> Result<Vec<Comment>, 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()]
+pub fn add_comment(user_id: u64, post_id: u64, content: &str) -> Result<Comment, rusqlite::Error> {
+    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)
+}
diff --git a/src/database/likes.rs b/src/database/likes.rs
new file mode 100644
index 0000000..6f6939e
--- /dev/null
+++ b/src/database/likes.rs
@@ -0,0 +1,77 @@
+use rusqlite::OptionalExtension;
+use tracing::instrument;
+
+use crate::{database, 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(())
+}
+
+#[instrument()]
+pub fn get_like_count(post_id: u64) -> Result<Option<u64>, 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)
+}
+
+#[instrument()]
+pub fn get_liked(user_id: u64, post_id: u64) -> Result<bool, rusqlite::Error> {
+    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()]
+pub fn add_liked(user_id: u64, post_id: u64) -> Result<bool, rusqlite::Error> {
+    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()]
+pub fn remove_liked(user_id: u64, post_id: u64) -> Result<bool, rusqlite::Error> {
+    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()]
+pub fn get_all_likes() -> Result<Vec<Like>, 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())
+}
diff --git a/src/database/mod.rs b/src/database/mod.rs
index 55cbe4f..6d4853a 100644
--- a/src/database/mod.rs
+++ b/src/database/mod.rs
@@ -1,5 +1,7 @@
 use tracing::instrument;
 
+pub mod comments;
+pub mod likes;
 pub mod posts;
 pub mod sessions;
 pub mod users;
@@ -12,6 +14,8 @@ pub fn init() -> Result<(), rusqlite::Error> {
     users::init()?;
     posts::init()?;
     sessions::init()?;
+    likes::init()?;
+    comments::init()?;
     Ok(())
 }
 
diff --git a/src/database/posts.rs b/src/database/posts.rs
index 8ca9b2d..7da3bf0 100644
--- a/src/database/posts.rs
+++ b/src/database/posts.rs
@@ -1,4 +1,3 @@
-use std::collections::HashSet;
 use std::time::{Duration, SystemTime, UNIX_EPOCH};
 
 use rusqlite::{OptionalExtension, Row};
@@ -7,14 +6,14 @@ use tracing::instrument;
 use crate::database;
 use crate::types::post::Post;
 
+use super::{comments, likes};
+
 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,
-            likes       TEXT NOT NULL,
-            comments    TEXT NOT NULL,
             date        INTEGER NOT NULL,
             FOREIGN KEY(user_id) REFERENCES users(user_id)
         );
@@ -28,25 +27,20 @@ fn post_from_row(row: &Row) -> Result<Post, rusqlite::Error> {
     let post_id = row.get(0)?;
     let user_id = row.get(1)?;
     let content = row.get(2)?;
-    let likes_json: String = row.get(3)?;
-    let comments_json: String = row.get(4)?;
-    let date = row.get(5)?;
-
-    let Ok(likes) = serde_json::from_str(&likes_json) else {
-        return Err(rusqlite::Error::InvalidQuery)
-    };
+    let date = row.get(3)?;
 
-    let Ok(comments) = serde_json::from_str(&comments_json) else {
-        return Err(rusqlite::Error::InvalidQuery)
-    };
+    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);
+    let liked = likes::get_liked(user_id, post_id).unwrap_or(false);
 
     Ok(Post {
         post_id,
         user_id,
         content,
+        date,
         likes,
+        liked,
         comments,
-        date,
     })
 }
 
@@ -106,14 +100,6 @@ pub fn get_users_post_page(user_id: u64, page: u64) -> Result<Vec<Post>, rusqlit
 #[instrument()]
 pub fn add_post(user_id: u64, content: &str) -> Result<Post, rusqlite::Error> {
     tracing::trace!("Adding post");
-    let likes: HashSet<u64> = HashSet::new();
-    let comments: Vec<(u64, String)> = Vec::new();
-    let Ok(likes_json) = serde_json::to_string(&likes) else {
-        return Err(rusqlite::Error::InvalidQuery)
-    };
-    let Ok(comments_json) = serde_json::to_string(&comments) else {
-        return Err(rusqlite::Error::InvalidQuery)
-    };
     let date = u64::try_from(
         SystemTime::now()
             .duration_since(UNIX_EPOCH)
@@ -122,29 +108,11 @@ pub fn add_post(user_id: u64, content: &str) -> Result<Post, rusqlite::Error> {
     )
     .unwrap_or(0);
     let conn = database::connect()?;
-    let mut stmt = conn.prepare("INSERT INTO posts (user_id, content, likes, comments, date) VALUES(?,?,?,?,?) RETURNING *;")?;
-    let post = stmt.query_row((user_id, content, likes_json, comments_json, date), |row| {
+    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()]
-pub fn update_post(
-    post_id: u64,
-    likes: &HashSet<u64>,
-    comments: &Vec<(u64, String)>,
-) -> Result<(), rusqlite::Error> {
-    tracing::trace!("Updating post");
-    let Ok(likes_json) = serde_json::to_string(&likes) else {
-        return Err(rusqlite::Error::InvalidQuery)
-    };
-    let Ok(comments_json) = serde_json::to_string(&comments) else {
-        return Err(rusqlite::Error::InvalidQuery)
-    };
-    let conn = database::connect()?;
-    let sql = "UPDATE posts SET likes = ?, comments = ? WHERE post_id = ?";
-    conn.execute(sql, (likes_json, comments_json, post_id))?;
-    Ok(())
-}
diff --git a/src/database/users.rs b/src/database/users.rs
index 8045bc4..15565f1 100644
--- a/src/database/users.rs
+++ b/src/database/users.rs
@@ -21,6 +21,13 @@ pub fn init() -> Result<(), rusqlite::Error> {
     ";
     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(())
 }