keep score history

1 files changed, 74 insertions, 15 deletions

diff --git a/src/database.rs b/src/database.rs
index e7726a9..3f86c5b 100644
--- a/src/database.rs
+++ b/src/database.rs
@@ -32,23 +32,34 @@ impl Database {
 			.unwrap();
 
 		let leaderboard_id = trans.last_insert_rowid();
-		let leaderboard_create_sql =
-			CREATE_TABLE_LEADERBOARD.replace("LBID", &leaderboard_id.to_string());
+		let lb = LeaderboardTable::new(leaderboard_id as usize);
+
+		let queries = &[
+			lb.sql_create_table(),
+			lb.sql_create_history_table(),
+			lb.sql_create_update_trigger_history_table(),
+			lb.sql_create_insert_trigger_history_table(),
+		];
+
+		for query in queries {
+			if let Err(e) = trans.execute(&query, params![]) {
+				panic!("SQL statement:\n{query}\nPanic Message:\n{e}")
+			}
+		}
 
-		trans.execute(&leaderboard_create_sql, params![]).unwrap();
 		trans.commit().unwrap();
 
 		Ok(())
 	}
 
-	pub fn leaderboard_id(&self, guild_id: u64) -> Result<u64, Error> {
+	pub fn leaderboard_id(&self, guild_id: u64) -> Result<LeaderboardTable, 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),
+				|row| row.get(0).map(|id| LeaderboardTable { id }),
 			)
 			.map_err(|_| Error::TableNotExist);
 
@@ -57,11 +68,11 @@ impl Database {
 
 	pub fn get_leaderboard(&self, guild_id: u64) -> Result<Vec<BoardRow>, Error> {
 		// Don't deadlock!
-		let leaderboard_id = self.leaderboard_id(guild_id)?;
+		let lb = self.leaderboard_id(guild_id)?;
 		let conn = self.conn.lock().unwrap();
 
 		let mut query = conn
-			.prepare(&format!("SELECT * FROM leaderboard_{leaderboard_id}"))
+			.prepare(&lb.sql("SELECT * FROM leaderboard_LBID"))
 			.unwrap();
 		let vec = query
 			.query_map((), |row| {
@@ -82,8 +93,7 @@ impl Database {
 
 	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 lb = self.leaderboard_id(guild)?;
 		let conn = self.conn.lock().unwrap();
 
 		let BoardRow {
@@ -93,8 +103,8 @@ impl Database {
 			points,
 		} = row;
 
-		let sql = format!(
-			"INSERT INTO {table}(user_id, user_handle, user_nickname, points) VALUES(?1, ?2, ?3, ?4)"
+		let sql = lb.sql(
+			"INSERT INTO leaderboard_LBID(user_id, user_handle, user_nickname, points) VALUES(?1, ?2, ?3, ?4)"
 		);
 		conn.execute(&sql, params![user_id, user_handle, user_nickname, points])
 			.unwrap();
@@ -104,16 +114,15 @@ impl Database {
 
 	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 lb = self.leaderboard_id(guild_id)?;
 		let conn = self.conn.lock().unwrap();
 
-		let sql = format!("SELECT * FROM {table} WHERE user_id=?1");
+		let sql = lb.sql("SELECT * FROM leaderboard_LBID 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");
+		let sql = lb.sql("UPDATE leaderboard_LBID SET points = points + ?1 WHERE user_id=?2");
 		conn.execute(&sql, params![points, user]).unwrap();
 
 		Ok(())
@@ -134,6 +143,56 @@ const CREATE_TABLE_LEADERBOARD: &'static str = "\
 		points INTEGER NOT NULL DEFAULT 0
 	);";
 
+const CREATE_TABLE_LEADERBOARD_HISTORY: &'static str = "\
+	CREATE TABLE IF NOT EXISTS leaderboard_history_LBID(
+		id INTEGER PRIMARY KEY AUTOINCREMENT,
+		user_id INTEGER NOT NULL,
+		timestamp TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
+		points INTEGER NOT NULL DEFAULT 0
+	);";
+
+const CREATE_TRIGGER_LDBHIST_UPDATE: &'static str = "\
+	CREATE TRIGGER ldbdhist_update_LBID BEFORE UPDATE ON leaderboard_LBID
+		BEGIN
+			INSERT INTO leaderboard_history_LBID(user_id, points) VALUES(old.user_id, new.points - old.points);
+		END;";
+
+const CREATE_TRIGGER_LDBHIST_INSERT: &'static str = "\
+	CREATE TRIGGER ldbdhist_insert_LBID BEFORE INSERT ON leaderboard_LBID
+		BEGIN
+			INSERT INTO leaderboard_history_LBID(user_id, points) VALUES(new.user_id, new.points);
+		END;";
+
+struct LeaderboardTable {
+	id: usize,
+}
+
+impl LeaderboardTable {
+	pub fn new(id: usize) -> Self {
+		Self { id }
+	}
+
+	pub fn sql<S: AsRef<str>>(&self, query: S) -> String {
+		query.as_ref().replace("LBID", &self.id.to_string())
+	}
+
+	pub fn sql_create_table(&self) -> String {
+		CREATE_TABLE_LEADERBOARD.replace("LBID", &self.id.to_string())
+	}
+
+	pub fn sql_create_history_table(&self) -> String {
+		CREATE_TABLE_LEADERBOARD_HISTORY.replace("LBID", &self.id.to_string())
+	}
+
+	pub fn sql_create_update_trigger_history_table(&self) -> String {
+		CREATE_TRIGGER_LDBHIST_UPDATE.replace("LBID", &self.id.to_string())
+	}
+
+	pub fn sql_create_insert_trigger_history_table(&self) -> String {
+		CREATE_TRIGGER_LDBHIST_INSERT.replace("LBID", &self.id.to_string())
+	}
+}
+
 #[derive(Clone, Debug)]
 pub struct BoardRow {
 	pub user_id: u64,