diff options
Diffstat (limited to 'src/database.rs')
| -rw-r--r-- | src/database.rs | 54 |
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, |