works

1 files changed, 149 insertions, 0 deletions

diff --git a/src/database.rs b/src/database.rs
new file mode 100644
index 0000000..e7726a9
--- /dev/null
+++ b/src/database.rs
@@ -0,0 +1,149 @@
+use std::{path::Path, sync::Mutex};
+
+use rusqlite::{Connection, OptionalExtension, params};
+use twilight_http::request::guild;
+
+#[derive(Debug)]
+pub struct Database {
+	conn: Mutex<Connection>,
+}
+
+impl Database {
+	pub fn new<P: AsRef<Path>>(db_path: P) -> Self {
+		Self {
+			conn: Mutex::new(Connection::open(db_path).unwrap()),
+		}
+	}
+
+	pub fn create_tables(&self) {
+		let conn = self.conn.lock().unwrap();
+		conn.execute(CREATE_TABLE_LEADERBOARDS, ()).unwrap();
+	}
+
+	pub fn create_leaderboard(&self, guild_id: u64) -> Result<(), Error> {
+		let mut conn = self.conn.lock().unwrap();
+		let trans = conn.transaction().unwrap();
+
+		trans
+			.execute(
+				"INSERT INTO leaderboards(guild_id) VALUES(?1)",
+				params![guild_id],
+			)
+			.unwrap();
+
+		let leaderboard_id = trans.last_insert_rowid();
+		let leaderboard_create_sql =
+			CREATE_TABLE_LEADERBOARD.replace("LBID", &leaderboard_id.to_string());
+
+		trans.execute(&leaderboard_create_sql, params![]).unwrap();
+		trans.commit().unwrap();
+
+		Ok(())
+	}
+
+	pub fn leaderboard_id(&self, guild_id: u64) -> Result<u64, Error> {
+		let conn = self.conn.lock().unwrap();
+
+		let leaderboard_id = conn
+			.query_row(
+				"SELECT * FROM leaderboards WHERE guild_id=?1",
+				params![guild_id],
+				|row| row.get(0),
+			)
+			.map_err(|_| Error::TableNotExist);
+
+		leaderboard_id
+	}
+
+	pub fn get_leaderboard(&self, guild_id: u64) -> Result<Vec<BoardRow>, Error> {
+		// Don't deadlock!
+		let leaderboard_id = self.leaderboard_id(guild_id)?;
+		let conn = self.conn.lock().unwrap();
+
+		let mut query = conn
+			.prepare(&format!("SELECT * FROM leaderboard_{leaderboard_id}"))
+			.unwrap();
+		let vec = query
+			.query_map((), |row| {
+				Ok(BoardRow {
+					user_id: row.get(0)?,
+					user_handle: row.get(1)?,
+					user_nickname: row.get(2)?,
+					points: row.get(3)?,
+				})
+			})
+			.optional()
+			.unwrap()
+			.map(|iter| iter.map(|e| e.unwrap()).collect::<Vec<BoardRow>>())
+			.unwrap();
+
+		Ok(vec)
+	}
+
+	pub fn add_user_to_leaderboard(&self, guild: u64, row: BoardRow) -> Result<(), Error> {
+		// Don't deadlock!
+		let leaderboard_id = self.leaderboard_id(guild)?;
+		let table = format!("leaderboard_{leaderboard_id}");
+		let conn = self.conn.lock().unwrap();
+
+		let BoardRow {
+			user_id,
+			user_handle,
+			user_nickname,
+			points,
+		} = row;
+
+		let sql = format!(
+			"INSERT INTO {table}(user_id, user_handle, user_nickname, points) VALUES(?1, ?2, ?3, ?4)"
+		);
+		conn.execute(&sql, params![user_id, user_handle, user_nickname, points])
+			.unwrap();
+
+		Ok(())
+	}
+
+	pub fn give_user_points(&self, guild_id: u64, user: u64, points: i64) -> Result<(), Error> {
+		// Don't deadlock!
+		let leaderboard_id = self.leaderboard_id(guild_id)?;
+		let table = format!("leaderboard_{leaderboard_id}");
+		let conn = self.conn.lock().unwrap();
+
+		let sql = format!("SELECT * FROM {table} WHERE user_id=?1");
+		let user_handle: String = conn
+			.query_row(&sql, params![user], |row| row.get(1))
+			.map_err(|_| Error::UserNotExist)?;
+
+		let sql = format!("UPDATE {table} SET points = points + ?1 WHERE user_id=?2");
+		conn.execute(&sql, params![points, user]).unwrap();
+
+		Ok(())
+	}
+}
+
+const CREATE_TABLE_LEADERBOARDS: &'static str = "\
+	CREATE TABLE IF NOT EXISTS leaderboards(
+		id INTEGER PRIMARY KEY AUTOINCREMENT,
+		guild_id INTEGER
+	);";
+
+const CREATE_TABLE_LEADERBOARD: &'static str = "\
+	CREATE TABLE IF NOT EXISTS leaderboard_LBID(
+		user_id INTEGER PRIMARY KEY NOT NULL,
+		user_handle TEXT NOT NULL,
+		user_nickname TEXT,
+		points INTEGER NOT NULL DEFAULT 0
+	);";
+
+#[derive(Clone, Debug)]
+pub struct BoardRow {
+	pub user_id: u64,
+	pub user_handle: String,
+	pub user_nickname: Option<String>,
+	pub points: i64,
+}
+
+#[derive(Debug)]
+pub enum Error {
+	TableNotExist,
+	UserNotExist,
+}