diff options
| -rw-r--r-- | src/database.rs | 54 | ||||
| -rw-r--r-- | src/main.rs | 22 | ||||
| -rw-r--r-- | src/util.rs | 42 |
3 files changed, 107 insertions, 11 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, diff --git a/src/main.rs b/src/main.rs index 0b34f30..c46ce7d 100644 --- a/src/main.rs +++ b/src/main.rs @@ -30,11 +30,12 @@ use twilight_util::builder::{ mod brain; mod database; mod import; +mod util; const PROD_APP_ID: u64 = 1363055126264283136; #[allow(dead_code)] const DEV_APP_ID: u64 = 1363494986699771984; -const APP_ID: u64 = DEV_APP_ID; +const APP_ID: u64 = PROD_APP_ID; macro_rules! bail { ($msg:expr) => { @@ -279,10 +280,23 @@ fn get_leaderboard(brain: Arc<Brain>, guild: Id<GuildMarker>) -> InteractionResp } Err(DbError::UserNotExist) => unreachable!(), Ok(data) => { - let str = data + let placed = util::tiebreak_shared_positions(data); + + let str = placed .into_iter() - .enumerate() - .map(|(idx, br)| format!("{idx}. <@{}>: {}", br.user_id, br.points)) + .map(|placement| { + if placement.placement_first_of_tie { + format!( + "`{:>2}` <@{}>: {}", + placement.placement_tie, placement.row.user_id, placement.row.points + ) + } else { + format!( + "` ` <@{}>: {}", + placement.row.user_id, placement.row.points + ) + } + }) .collect::<Vec<String>>() .join("\n"); diff --git a/src/util.rs b/src/util.rs new file mode 100644 index 0000000..a05eeb9 --- /dev/null +++ b/src/util.rs @@ -0,0 +1,42 @@ +//! Uh-oh, a `util.rs` file. That can't be good. + +use crate::database::{BoardRow, Placement}; + +pub fn tiebreak_shared_positions(board: Vec<BoardRow>) -> Vec<Placement> { + let first = match board.first() { + Some(r) => r.clone(), + None => return vec![], + }; + + let mut last_score = first.points; + let mut last_placed = 1; + let mut placed = vec![Placement { + row: first, + placement: 1, + placement_tie: 1, + placement_first_of_tie: true, + }]; + + for (idx, row) in board.into_iter().enumerate().skip(1) { + if row.points == last_score { + placed.push(Placement { + row, + placement: idx + 1, + placement_tie: last_placed, + placement_first_of_tie: false, + }); + } else { + last_score = row.points; + last_placed = idx + 1; + + placed.push(Placement { + row, + placement: idx + 1, + placement_tie: last_placed, + placement_first_of_tie: true, + }); + } + } + + placed +} |