I hate sql

1 files changed, 240 insertions, 0 deletions

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