I hate sql

6 files changed, 358 insertions, 181 deletions

diff --git a/.gitignore b/.gitignore
index ea8c4bf..50b38e5 100644
--- a/.gitignore
+++ b/.gitignore
@@ -1 +1,2 @@
 /target
+db.sqlite
\ No newline at end of file
diff --git a/Cargo.lock b/Cargo.lock
index 1aebdb4..421923c 100644
--- a/Cargo.lock
+++ b/Cargo.lock
@@ -1143,7 +1143,7 @@ dependencies = [
 
 [[package]]
 name = "rocket_test"
-version = "0.1.1"
+version = "0.1.2"
 dependencies = [
  "chrono",
  "rocket",
diff --git a/Cargo.toml b/Cargo.toml
index d4538e6..21aef71 100644
--- a/Cargo.toml
+++ b/Cargo.toml
@@ -1,6 +1,6 @@
 [package]
 name = "rocket_test"
-version = "0.1.1"
+version = "0.1.2"
 edition = "2021"
 
 # See more keys and their definitions at https://doc.rust-lang.org/cargo/reference/manifest.html
diff --git a/src/database.rs b/src/database.rs
new file mode 100644
index 0000000..5b41db7
--- /dev/null
+++ b/src/database.rs
@@ -0,0 +1,240 @@
+pub mod r#types;
+
+use sqlite::Connection;
+
+use types::{Message, User};
+
+
+pub struct Database {
+    db: Connection,
+}
+impl Database {
+    pub fn get_message(&self, id: usize) -> Option<Message> {
+        let query ="SELECT * FROM messages WHERE id IS :id";
+        let statement = self.db.prepare(query).unwrap().into_iter().bind((":id", id as i64)).unwrap();
+
+        for row in statement.map(|row| row.unwrap()) {
+
+            let msg = row.read::<&str, _>("message");
+            let reply_to = row.read::<Option<i64>, _>("reply_to");
+            let reply_to = match reply_to {
+                Some(n) => Some(self.i64_into_usize(n)),
+                None => None
+            };
+
+            let date = row.read::<i64, _>("date");
+            let date = types::Date::new(date);
+
+            let deleted = row.read::<i64, _>("deleted");
+            let deleted = match deleted {
+                0 => false,
+                _ => true,
+            };
+
+            let sender = row.read::<i64, _>("sender");
+            let sender = self.get_user(sender as usize).unwrap();
+
+            let id = row.read::<i64, _>("id");
+            let id = self.i64_into_usize(id);
+
+
+            let msg = Message::construct(String::from(msg), sender, id, reply_to, date, deleted);
+            return Some(msg)
+        }
+
+        None
+    }
+    pub fn get_message_id_list(&self, id_start: usize, id_end: usize) -> Vec<usize> {
+        let query = "SELECT id FROM messages WHERE deleted is false AND id BETWEEN :id_start AND :id_end";
+        let statement = self.db.prepare(query).unwrap().into_iter().bind::<&[(_, sqlite::Value)]>(&[
+            (":id_start", (id_start as i64).into()),
+            (":id_end", (id_end as i64).into()),
+            ]).unwrap();
+
+
+        let mut message_id_vec: Vec<usize> = vec![];
+        for row in statement.map(|row| row.unwrap()) {
+            let id = row.read::<i64, _>("id");
+            let id = self.i64_into_usize(id);
+
+            message_id_vec.push(id);
+        }
+
+        return message_id_vec;
+    }
+    pub fn get_next_message_id(&self) -> usize {
+        self.get_table_length("messages") as usize
+    }
+    pub fn get_message_newest_id(&self) -> Option<usize> {
+        match self.get_table_length("messages") as usize {
+            0 => None,
+            n => Some(n - 1),
+        }
+
+    }
+
+    pub fn send_message(&mut self, msg: &Message) -> Result<usize, &'static str> {
+        let id = msg.id();
+
+        let query = "INSERT INTO messages (date, sender, message, id, reply_to, deleted) VALUES (:date, :sender, :message, :id, :reply_to, :deleted)";
+        let statement = self.db.prepare(query).unwrap().into_iter()
+        .bind::<&[(_, sqlite::Value)]>(&[
+            (":date", msg.date_as_i64().into()),
+            (":sender", msg.user_id().into()),
+            (":message", msg.message().into()),
+            (":reply_to", match msg.reply_to() {
+                Some(n) => n.into(),
+                None => ().into()
+            }),
+            (":id", (id as i64).into()),
+            (":deleted", 0.into()),
+            ]);
+
+        for x in statement.unwrap() {
+            match x {
+                Ok(_) => (),
+                Err(n) => {
+                    match n.code.unwrap_or(0) {
+                        _ => return Err("Unknown error")
+                    }
+                },
+            }
+        }
+
+        Ok(id)
+    }
+
+    pub fn delete_message(&mut self, id: usize) -> Result<&'static str, &'static str> {
+        let query = "UPDATE messages SET message=:message, deleted=:deleted WHERE id IS :id AND deleted IS false";
+        let statement = self.db.prepare(query).unwrap().into_iter()
+            .bind::<&[(_, sqlite::Value)]>(&[
+            (":message", "".into()),
+            (":deleted", 1.into()),
+            (":id", (id as i64).into()),
+            ]);
+
+        let change_count = self.db.change_count();
+
+        for x in statement.unwrap() {
+            match x {
+                Ok(_) => (),
+                Err(n) => {
+                    match n.code.unwrap_or(0) {
+                        _ => return Err("Unknown error")
+                    }
+                },
+            }
+        }
+
+        if change_count > 0 {
+            Ok("Deleted")
+        } else {
+            Err("Unabled to delete")
+        }
+    }
+
+    pub fn get_user(&self, id: usize) -> Option<User> {
+        let query = format!("SELECT * FROM users WHERE id IS '{}'", id);
+        let statement = self.db.prepare(query).unwrap();
+
+        for row in statement.into_iter().map(|row| row.unwrap()) {
+
+            let username = row.read::<&str, _>("username");
+            let id = row.read::<i64, _>("id");
+
+            let id = self.i64_into_usize(id);
+
+            return Some(User::new(String::from(username), id))
+        }
+
+        None
+    }
+    pub fn get_user_by_name(&self, name: String) -> Option<User> {
+        let query = "SELECT * FROM users WHERE name IS :name";
+        let statement = self.db.prepare(query).unwrap().into_iter().bind((":name", name.as_str())).unwrap();
+
+        for row in statement.map(|row| row.unwrap()) {
+            let username = row.read::<&str, _>("username");
+            let id = row.read::<i64, _>("id");
+
+            let id = self.i64_into_usize(id);
+
+            return Some(User::new(String::from(username), id))
+        }
+
+        None
+    }
+
+    pub fn create_user(&mut self, name: String) -> Result<(), &'static str> {
+        let id = self.get_user_count();
+
+        let query = "INSERT INTO users (id, username) VALUES (:id, :name)";
+        let statement = self.db.prepare(query).unwrap().into_iter()
+        .bind::<&[(_, sqlite::Value)]>(&[(":id", (id as i64).into()),(":name", name.into())]);
+
+        for x in statement.unwrap() {
+            match x {
+                Ok(_) => (),
+                Err(n) => {
+                    match n.code.unwrap_or(0) {
+                        19 => return Err("Name taken"),
+                        _ => return Err("Unknown error")
+                    }
+                },
+            }
+        }
+
+        Ok(())
+    }
+
+    pub fn get_user_count(&self) -> usize {
+        self.get_table_length("users")
+    }
+
+    fn get_table_length(&self, table: &'static str) -> usize {
+        let query = format!("SELECT COUNT(*) FROM {}", table);
+        let statement = self.db.prepare(query).unwrap().into_iter();
+
+        for row in statement.map(|row| row.unwrap()) {
+            let c = row.read::<i64, _>("COUNT(*)");
+            
+            let c = self.i64_into_usize(c);
+            
+            return c;
+        }
+
+        0
+    }
+
+    fn i64_into_usize(&self, i: i64) -> usize {
+        if i < 0 {
+            0
+        } else {
+            i as usize
+        }
+    }
+
+    pub fn new() -> Database {
+        let db = match Connection::open("db.sqlite") {
+            Ok(n) => n,
+            Err(n) => panic!("{n}"),
+        };
+
+        // Setup the db
+        let query = "CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY NOT NULL, username UNIQUE NOT NULL) WITHOUT ROWID";
+        match db.execute(query) {
+            Ok(_) => (),
+            Err(n) => panic!("{n}"),
+        }
+
+        let query = "CREATE TABLE IF NOT EXISTS messages (id INTEGER PRIMARY KEY NOT NULL, sender INTEGER NOT NULL, date INTEGER NOT NULL, message TEXT, reply_to INTEGER, deleted BOOL NOT NULL) WITHOUT ROWID";
+        match db.execute(query) {
+            Ok(_) => (),
+            Err(n) => panic!("{n}"),
+        }
+
+        Database {
+            db: db,
+        }
+    }
+}
\ No newline at end of file
diff --git a/src/database/types.rs b/src/database/types.rs
new file mode 100644
index 0000000..4d2c220
--- /dev/null
+++ b/src/database/types.rs
@@ -0,0 +1,100 @@
+use serde::Serialize;
+use std::time::SystemTime;
+
+#[derive(Serialize, Clone, Debug)]
+pub struct Date {
+    date: u64,
+}
+impl Date {
+    pub fn now() -> Date {
+        Date{
+            date: SystemTime::now().duration_since(SystemTime::UNIX_EPOCH).unwrap().as_secs()
+        }
+    }
+    pub fn new(s: i64) -> Date {
+        Date {
+            date: s as u64,
+        }
+    }
+}
+
+#[derive(Serialize, Clone, Debug)]
+pub struct User {
+    username: String,
+    id: usize
+}
+impl User {
+    pub fn new(name: String, id: usize) -> User {
+        User{
+            username: name,
+            id: id
+        }
+    }
+}
+
+#[derive(Serialize, Clone, Debug)]
+pub struct Message {
+    date: Date,
+    message: String,
+    sender: User,
+    id: usize,
+    reply_to: Option<usize>, // Message ID
+    deleted: bool,
+}
+impl Message {
+    pub fn new(msg: String, sender: User, id: usize, reply_to: Option<usize>) -> Message {
+        Message {
+            date: Date::now(),
+            sender: sender,
+            message: msg,
+            id: id,
+            reply_to: reply_to,
+            deleted: false,
+        }
+    }
+
+    pub fn construct(msg: String, sender: User, id: usize, reply_to: Option<usize>, date: Date, deleted: bool) -> Message {
+        Message {
+            date: date,
+            sender: sender,
+            message: msg,
+            id: id,
+            reply_to: reply_to,
+            deleted: deleted,
+        }
+    }
+
+    pub fn id(&self) -> usize {
+        self.id
+    }
+
+    pub fn date_as_i64(&self) -> i64 {
+        self.date.date as i64
+    }
+    pub fn user_id(&self) -> i64 {
+        self.sender.id as i64
+    }
+    pub fn message(&self) -> String {
+        self.message.clone()
+    }
+    pub fn reply_to(&self) -> Option<i64> {
+        match self.reply_to {
+            Some(n) => Some(n as i64),
+            None => None
+        }
+    }
+}
+
+#[derive(Serialize)]
+pub struct Info {
+    name: String,
+    users: usize,
+}
+impl Info {
+    pub fn get(db: std::sync::MutexGuard<crate::database::Database>) -> Info {
+        Info{
+            name: String::from("Testing"),
+            users: db.get_user_count(),
+        }
+    }
+}
\ No newline at end of file
diff --git a/src/main.rs b/src/main.rs
index f6163ab..a9db22e 100644
--- a/src/main.rs
+++ b/src/main.rs
@@ -1,11 +1,17 @@
 #[macro_use] extern crate rocket;
+
+mod database;
+
 use rocket::serde::json::Json;
-use std::collections::HashMap;
 use rocket::State;
 use std::sync::Mutex;
-use std::time::SystemTime;
+use database::types::{Message, User, Info};
 
 
+struct SharedDB {
+    sdb: Mutex<database::Database>
+}
+
 #[derive(serde::Serialize)]
 struct Response<T, Y> (Result<T, Y>);
 
@@ -48,8 +54,8 @@ fn send_message(user_id: usize, msg: String, db: &State<SharedDB>) -> Json<Respo
     };
     let msg = Message::new(msg, user, lock.get_next_message_id(), None);
 
-    lock.send_message(&msg);
-    Json(Response(Ok(msg.id)))
+    let send = lock.send_message(&msg);
+    Json(Response(send))
 }
 
 #[get("/send_reply/<user_id>/<message_id>/<msg>")]
@@ -62,8 +68,8 @@ fn send_reply(user_id: usize, message_id: usize, msg: String, db: &State<SharedD
     };
     let msg = Message::new(msg, user, lock.get_next_message_id(), Some(message_id));
 
-    lock.send_message(&msg);
-    Json(Response(Ok(msg.id)))
+    let send = lock.send_message(&msg);
+    Json(Response(send))
 }
 
 #[get("/delete_message/<id>")]
@@ -82,7 +88,7 @@ fn get_user(id: usize, db: &State<SharedDB>) -> Json<Response<User, &'static str
     }
 }
 #[get("/get_user_by_name/<name>")]
-fn get_user_by_name(name: String, db: &State<SharedDB>) -> Json<Response<Vec<User>, &'static str>> {
+fn get_user_by_name(name: String, db: &State<SharedDB>) -> Json<Response<User, &'static str>> {
     let lock = db.sdb.lock().unwrap();
     match lock.get_user_by_name(name) {
         Some(n) => Json(Response(Ok(n))),
@@ -90,182 +96,12 @@ fn get_user_by_name(name: String, db: &State<SharedDB>) -> Json<Response<Vec<Use
     }
 }
 #[get("/create_user/<name>")]
-fn create_user(name: String, db: &State<SharedDB>) -> Json<Response<User, &'static str>> {
+fn create_user(name: String, db: &State<SharedDB>) -> Json<Response<(), &'static str>> {
     let mut lock = db.sdb.lock().unwrap();
 
     Json(Response(lock.create_user(name)))
 }
 
-struct SharedDB {
-    sdb: Mutex<Database>
-}
-
-struct Database {
-    usermap: HashMap<usize, User>,
-    messagemap: HashMap<usize, Message>,
-}
-impl Database {
-    fn get_message(&self, id: usize) -> Option<Message> {
-        match self.messagemap.get(&id) {
-            Some(n) => Some(n.to_owned()),
-            None => None,
-        }
-    }
-    fn get_message_id_list(&self, id_start: usize, id_end: usize) -> Vec<usize> {
-        let mut message_id_vec: Vec<usize> = vec![];
-
-        for x in id_start..=id_end {
-            match self.messagemap.get(&x) {
-                Some(_) => message_id_vec.push(x),
-                None => (),
-            }
-        }
-
-        return message_id_vec;
-    }
-    fn get_next_message_id(&self) -> usize {
-        self.messagemap.len()
-    }
-    fn get_message_newest_id(&self) -> Option<usize> {
-        let newest = self.get_next_message_id() as isize - 1;
-        let newest = if newest < 0 {0} else {newest as usize};
-        match self.messagemap.get(&newest) {
-            Some(_) => Some(newest),
-            None => None,
-        }
-    }
-
-    fn send_message(&mut self, msg: &Message) {
-        self.messagemap.insert(msg.id, msg.clone());
-        println!("{:?}", self.messagemap.get(&0));
-    }
-
-    fn delete_message(&mut self, id: usize) -> Result<&'static str, &'static str> {
-        let  msg = match self.messagemap.get_mut(&id) {
-            Some(n) => n,
-            None => return Err("Message not found"),
-        };
-
-        msg.delete();
-
-        Ok("Deleted")
-    }
-
-    fn get_user(&self, id: usize) -> Option<User> {
-        match self.usermap.get(&id) {
-            Some(n) => Some(n.to_owned()),
-            None => None
-        }
-    }
-    fn get_user_by_name(&self, name: String) -> Option<Vec<User>> {
-        let list: Vec<Option<User>> = self.usermap.values().map(|x| if x.username == name {Some(x.clone())} else {None}).collect();
-
-        let mut users = vec![];
-        for x in list {
-            match x {
-                Some(n) => users.push(n),
-                None => ()
-            }
-        }
-
-        if users.len() == 0 {
-            None
-        } else {
-            Some(users)
-        }
-    }
-
-    fn create_user(&mut self, name: String) -> Result<User, &'static str> {
-        let id = self.usermap.len();
-        let user = User::new(name.clone(), id);
-
-        match self.get_user_by_name(name) {
-            Some(_) => return Err("ID taken"),
-            None => (),
-        }
-
-        match self.usermap.get(&id) {
-            Some(_) => Err("ID taken"),
-            None => {self.usermap.insert(id, user.clone()); Ok(user)},
-        }
-    }
-
-    pub fn new() -> Database {
-        Database {
-            usermap: HashMap::new(),
-            messagemap: HashMap::new(),
-
-        }
-    }
-}
-
-#[derive(serde::Serialize, Clone, Debug)]
-struct Date {
-    date: u64,
-}
-impl Date {
-    pub fn now() -> Date {
-        Date{
-            date: SystemTime::now().duration_since(SystemTime::UNIX_EPOCH).unwrap().as_secs()
-        }
-    }
-}
-
-#[derive(serde::Serialize, Clone, Debug)]
-struct User {
-    username: String,
-    id: usize
-}
-impl User {
-    pub fn new(name: String, id: usize) -> User {
-        User{
-            username: name,
-            id: id
-        }
-    }
-}
-
-#[derive(serde::Serialize, Clone, Debug)]
-struct Message {
-    date: Date,
-    message: String,
-    sender: User,
-    id: usize,
-    reply_to: Option<usize>, // Message ID
-    deleted: bool,
-}
-impl Message {
-    pub fn new(msg: String, sender: User, id: usize, reply_to: Option<usize>) -> Message {
-        Message {
-            date: Date::now(),
-            sender: sender,
-            message: msg,
-            id: id,
-            reply_to: reply_to,
-            deleted: false,
-        }
-    }
-
-    fn delete(&mut self) {
-        self.message.clear();
-        self.deleted = true;
-    }
-}
-
-#[derive(serde::Serialize)]
-struct Info {
-    name: String,
-    users: usize,
-}
-impl Info {
-    pub fn get(db: std::sync::MutexGuard<Database>) -> Info {
-        Info{
-            name: String::from("Testing"),
-            users: db.usermap.len(),
-        }
-    }
-}
-
 #[get("/ping")]
 fn ping() -> Json<Response<&'static str, &'static str>> {
     Json(Response(Ok("pong")))
@@ -299,5 +135,5 @@ fn rocket() -> _ {
         delete_message,
     ])
     .mount("/", routes![api_index])
-    .manage(SharedDB{sdb: Mutex::new(Database::new())})
+    .manage(SharedDB{sdb: Mutex::new(database::Database::new())})
 }