1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
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,
}
|
