break ties by timestamp

1 files changed, 47 insertions, 7 deletions

diff --git a/src/database.rs b/src/database.rs
index 0fb6987..d5ccf92 100644
--- a/src/database.rs
+++ b/src/database.rs
@@ -72,18 +72,19 @@ impl Database {
 	pub fn get_leaderboard(&self, guild_id: u64) -> Result<Vec<BoardRow>, Error> {
 		// Don't deadlock!
 		let lb = self.leaderboard_id(guild_id)?;
+		let query = lb.sql_leaderboard_query();
 		let conn = self.conn.lock().unwrap();
 
-		let mut query = conn
-			.prepare(&lb.sql("SELECT * FROM leaderboard_LBID ORDER BY points DESC"))
-			.unwrap();
+		// The query that get's run sorts ties by preferring who got to that score first
+		let mut query = conn.prepare(&lb.sql(query)).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)?,
+					user_id: row.get(1)?,
+					user_handle: row.get(2)?,
+					user_nickname: row.get(3)?,
+					points: row.get(4)?,
 				})
 			})
 			.optional()
@@ -232,6 +233,15 @@ const CREATE_TABLE_LEADERBOARD_HISTORY: &'static str = "\
 		points INTEGER NOT NULL DEFAULT 0
 	);";
 
+const CREATE_TRIGGER_LDBCHANGETIME_UPDATE: &'static str = "\
+	CREATE TRIGGER ldbchangetime_update_LBID
+		AFTER UPDATE ON leaderboard_LBID
+		FOR EACH ROW
+		WHEN NEW.score_updated < OLD.score_updated OR OLD.score_updated IS NULL
+		BEGIN
+			UPDATE leaderboard_LBID SET score_updated=CURRENT_TIMESTAMP WHERE user_id=OLD.user_id;
+		END;";
+
 const CREATE_TRIGGER_LDBHIST_UPDATE: &'static str = "\
 	CREATE TRIGGER ldbdhist_update_LBID BEFORE UPDATE ON leaderboard_LBID
 		BEGIN
@@ -244,6 +254,19 @@ const CREATE_TRIGGER_LDBHIST_INSERT: &'static str = "\
 			INSERT INTO leaderboard_history_LBID(user_id, points) VALUES(new.user_id, new.points);
 		END;";
 
+const LEADERBOARD_QUERY: &'static str = "\
+	SELECT
+		history.timestamp,
+		leaderboard_LBID.*
+	FROM leaderboard_LBID
+	JOIN (
+	SELECT user_id, max(timestamp) as timestamp
+	FROM leaderboard_history_LBID
+	GROUP BY user_id
+	) history
+	ON history.user_id = leaderboard_LBID.user_id
+	ORDER BY leaderboard_LBID.points DESC, history.timestamp ASC;";
+
 struct LeaderboardTable {
 	id: usize,
 }
@@ -272,6 +295,10 @@ impl LeaderboardTable {
 	pub fn sql_create_insert_trigger_history_table(&self) -> String {
 		CREATE_TRIGGER_LDBHIST_INSERT.replace("LBID", &self.id.to_string())
 	}
+
+	pub fn sql_leaderboard_query(&self) -> String {
+		LEADERBOARD_QUERY.replace("LBID", &self.id.to_string())
+	}
 }
 
 #[derive(Clone, Debug)]
@@ -282,6 +309,19 @@ pub struct BoardRow {
 	pub points: i64,
 }
 
+#[derive(Clone, Debug)]
+pub struct Placement {
+	pub row: BoardRow,
+
+	/// Absolute placement sorting by points and then
+	/// TODO: sort by date points attained
+	pub placement: usize,
+	/// Placement allowing ties, and placing ties in the same place
+	pub placement_tie: usize,
+	/// Whether or not this placement was first of a tie. True if untied.
+	pub placement_first_of_tie: bool,
+}
+
 #[derive(Debug)]
 pub enum Error {
 	TableNotExist,