· 4 years ago · Mar 19, 2021, 05:56 PM
1const Database = require("better-sqlite3");
2const db = new Database(__basedir + "/data/db.sqlite");
3
4// Set pragmas
5db.pragma("synchronous = 1");
6
7/**
8 * Enabling WAL mode causes issues with file locking within WSL, works fine on a normal Unix system
9 * Issue documented here: https://github.com/microsoft/WSL/issues/2395
10 */
11db.pragma("journal_mode = wal");
12
13/** ------------------------------------------------------------------------------------------------
14 * TABLES
15 * ------------------------------------------------------------------------------------------------ */
16// BOT SETTINGS TABLE
17db.prepare(
18 `
19 CREATE TABLE IF NOT EXISTS settings (
20 guild_id TEXT PRIMARY KEY,
21 guild_name TEXT,
22 prefix TEXT DEFAULT "c!" NOT NULL,
23 system_channel_id TEXT,
24 starboard_channel_id TEXT,
25 admin_role_id TEXT,
26 mod_role_id TEXT,
27 mute_role_id TEXT,
28 auto_role_id TEXT,
29 auto_kick INTEGER,
30 mod_channel_ids TEXT,
31 disabled_commands TEXT,
32 scrim_channel_id TEXT,
33 mod_log_id TEXT,
34 member_log_id TEXT,
35 nickname_log_id TEXT,
36 role_log_id TEXT,
37 message_edit_log_id TEXT,
38 message_delete_log_id TEXT,
39 verification_role_id TEXT,
40 verification_channel_id TEXT,
41 verification_message TEXT,
42 verification_message_id TEXT,
43 welcome_channel_id TEXT,
44 welcome_message TEXT,
45 farewell_channel_id TEXT,
46 farewell_message TEXT,
47 point_tracking INTEGER DEFAULT 1 NOT NULL,
48 message_points INTEGER DEFAULT 1 NOT NULL,
49 command_points INTEGER DEFAULT 1 NOT NULL,
50 voice_points INTEGER DEFAULT 1 NOT NULL,
51 crown_role_id TEXT,
52 crown_channel_id TEXT,
53 crown_message TEXT DEFAULT "?member has won ?role this week! Points have been reset, better luck next time!",
54 crown_schedule TEXT DEFAULT "0 21 * * 5"
55 );
56`,
57).run();
58
59// USERS TABLE
60db.prepare(
61 `
62 CREATE TABLE IF NOT EXISTS users (
63 user_id TEXT,
64 user_name TEXT,
65 user_discriminator TEXT,
66 guild_id TEXT,
67 guild_name TEXT,
68 date_joined TEXT,
69 bot INTEGER,
70 points INTEGER NOT NULL,
71 total_points INTEGER NOT NULL,
72 warns TEXT,
73 current_member INTEGER NOT NULL,
74 PRIMARY KEY (user_id, guild_id)
75 );
76`,
77).run();
78
79// TEAMS TABLE
80db.prepare(
81 `
82 CREATE TABLE IF NOT EXISTS teams (
83 guild_id TEXT,
84 teamName TEXT,
85 user_id TEXT,
86 steamID TEXT,
87 mmr BigInt,
88 PRIMARY KEY (user_id, guild_id)
89 );
90 `,
91).run();
92
93// STAFF TABLE
94db.prepare(
95 `
96 CREATE TABLE IF NOT EXISTS staff (
97 user_id TEXT,
98 user_name TEXT,
99 guild_id TEXT,
100 department TEXT,
101 PRIMARY KEY (user_id, guild_id)
102 );
103 `,
104).run();
105
106/** ------------------------------------------------------------------------------------------------
107 * PREPARED STATEMENTS
108 * ------------------------------------------------------------------------------------------------ */
109// BOT SETTINGS TABLE
110const settings = {
111 insertRow: db.prepare(`
112 INSERT OR IGNORE INTO settings (
113 guild_id,
114 guild_name,
115 system_channel_id,
116 welcome_channel_id,
117 farewell_channel_id,
118 crown_channel_id,
119 scrim_channel_id,
120 mod_log_id,
121 admin_role_id,
122 mod_role_id,
123 mute_role_id,
124 crown_role_id
125 ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);
126 `),
127
128 // Selects
129 selectRow: db.prepare("SELECT * FROM settings WHERE guild_id = ?;"),
130 selectGuilds: db.prepare("SELECT guild_name, guild_id FROM settings"),
131 selectPrefix: db.prepare("SELECT prefix FROM settings WHERE guild_id = ?;"),
132 selectSystemChannelId: db.prepare(
133 "SELECT system_channel_id FROM settings WHERE guild_id = ?;",
134 ),
135 selectStarboardChannelId: db.prepare(
136 "SELECT starboard_channel_id FROM settings WHERE guild_id = ?;",
137 ),
138 selectAdminRoleId: db.prepare(
139 "SELECT admin_role_id FROM settings WHERE guild_id = ?;",
140 ),
141 selectModRoleId: db.prepare(
142 "SELECT mod_role_id FROM settings WHERE guild_id = ?;",
143 ),
144 selectMuteRoleId: db.prepare(
145 "SELECT mute_role_id FROM settings WHERE guild_id = ?;",
146 ),
147 selectAutoRoleId: db.prepare(
148 "SELECT auto_role_id FROM settings WHERE guild_id = ?;",
149 ),
150 selectAutoKick: db.prepare(
151 "SELECT auto_kick FROM settings WHERE guild_id = ?;",
152 ),
153 selectModChannelIds: db.prepare(
154 "SELECT mod_channel_ids FROM settings WHERE guild_id = ?;",
155 ),
156 selectDisabledCommands: db.prepare(
157 "SELECT disabled_commands FROM settings WHERE guild_id = ?;",
158 ),
159 selectScrimChannelId: db.prepare(
160 "SELECT scrim_channel_id FROM settings WHERE guild_id = ?;",
161 ),
162 selectModLogId: db.prepare(
163 "SELECT mod_log_id FROM settings WHERE guild_id = ?;",
164 ),
165 selectMemberLogId: db.prepare(
166 "SELECT member_log_id FROM settings WHERE guild_id = ?;",
167 ),
168 selectNicknameLogId: db.prepare(
169 "SELECT nickname_log_id FROM settings WHERE guild_id = ?;",
170 ),
171 selectRoleLogId: db.prepare(
172 "SELECT role_log_id FROM settings WHERE guild_id = ?;",
173 ),
174 selectMessageEditLogId: db.prepare(
175 "SELECT message_edit_log_id FROM settings WHERE guild_id = ?;",
176 ),
177 selectMessageDeleteLogId: db.prepare(
178 "SELECT message_delete_log_id FROM settings WHERE guild_id = ?;",
179 ),
180 selectVerification: db.prepare(`
181 SELECT verification_role_id, verification_channel_id, verification_message, verification_message_id
182 FROM settings
183 WHERE guild_id = ?;
184 `),
185 selectWelcomes: db.prepare(
186 "SELECT welcome_channel_id, welcome_message FROM settings WHERE guild_id = ?;",
187 ),
188 selectFarewells: db.prepare(
189 "SELECT farewell_channel_id, farewell_message FROM settings WHERE guild_id = ?;",
190 ),
191 selectPoints: db.prepare(`
192 SELECT point_tracking, message_points, command_points, voice_points
193 FROM settings
194 WHERE guild_id = ?;
195 `),
196 selectCrown: db.prepare(`
197 SELECT crown_role_id, crown_channel_id, crown_message, crown_schedule
198 FROM settings
199 WHERE guild_id = ?;
200 `),
201
202 // Updates
203 updatePrefix: db.prepare(
204 "UPDATE settings SET prefix = ? WHERE guild_id = ?;",
205 ),
206 updateGuildName: db.prepare(
207 "UPDATE settings SET guild_name = ? WHERE guild_id = ?;",
208 ),
209 updateSystemChannelId: db.prepare(
210 "UPDATE settings SET system_channel_id = ? WHERE guild_id = ?;",
211 ),
212 updateStarboardChannelId: db.prepare(
213 "UPDATE settings SET starboard_channel_id = ? WHERE guild_id = ?;",
214 ),
215 updateAdminRoleId: db.prepare(
216 "UPDATE settings SET admin_role_id = ? WHERE guild_id = ?;",
217 ),
218 updateModRoleId: db.prepare(
219 "UPDATE settings SET mod_role_id = ? WHERE guild_id = ?;",
220 ),
221 updateMuteRoleId: db.prepare(
222 "UPDATE settings SET mute_role_id = ? WHERE guild_id = ?;",
223 ),
224 updateAutoRoleId: db.prepare(
225 "UPDATE settings SET auto_role_id = ? WHERE guild_id = ?;",
226 ),
227 updateAutoKick: db.prepare(
228 "UPDATE settings SET auto_kick = ? WHERE guild_id = ?;",
229 ),
230 updateModChannelIds: db.prepare(
231 "UPDATE settings SET mod_channel_ids = ? WHERE guild_id = ?;",
232 ),
233 updateDisabledCommands: db.prepare(
234 "UPDATE settings SET disabled_commands = ? WHERE guild_id = ?;",
235 ),
236 updateScrimChannelId: db.prepare(
237 "UPDATE settings SET scrim_channel_id = ? WHERE guild_id = ?;",
238 ),
239 updateModLogId: db.prepare(
240 "UPDATE settings SET mod_log_id = ? WHERE guild_id = ?;",
241 ),
242 updateMemberLogId: db.prepare(
243 "UPDATE settings SET member_log_id = ? WHERE guild_id = ?;",
244 ),
245 updateNicknameLogId: db.prepare(
246 "UPDATE settings SET nickname_log_id = ? WHERE guild_id = ?;",
247 ),
248 updateRoleLogId: db.prepare(
249 "UPDATE settings SET role_log_id = ? WHERE guild_id = ?;",
250 ),
251 updateMessageEditLogId: db.prepare(
252 "UPDATE settings SET message_edit_log_id = ? WHERE guild_id = ?;",
253 ),
254 updateMessageDeleteLogId: db.prepare(
255 "UPDATE settings SET message_delete_log_id = ? WHERE guild_id = ?;",
256 ),
257 updateVerificationRoleId: db.prepare(
258 "UPDATE settings SET verification_role_id = ? WHERE guild_id = ?;",
259 ),
260 updateVerificationChannelId: db.prepare(
261 "UPDATE settings SET verification_channel_id = ? WHERE guild_id = ?;",
262 ),
263 updateVerificationMessage: db.prepare(
264 "UPDATE settings SET verification_message = ? WHERE guild_id = ?;",
265 ),
266 updateVerificationMessageId: db.prepare(
267 "UPDATE settings SET verification_message_id = ? WHERE guild_id = ?;",
268 ),
269 updateWelcomeChannelId: db.prepare(
270 "UPDATE settings SET welcome_channel_id = ? WHERE guild_id = ?;",
271 ),
272 updateWelcomeMessage: db.prepare(
273 "UPDATE settings SET welcome_message = ? WHERE guild_id = ?;",
274 ),
275 updateFarewellChannelId: db.prepare(
276 "UPDATE settings SET farewell_channel_id = ? WHERE guild_id = ?;",
277 ),
278 updateFarewellMessage: db.prepare(
279 "UPDATE settings SET farewell_message = ? WHERE guild_id = ?;",
280 ),
281 updatePointTracking: db.prepare(
282 "UPDATE settings SET point_tracking = ? WHERE guild_id = ?;",
283 ),
284 updateMessagePoints: db.prepare(
285 "UPDATE settings SET message_points = ? WHERE guild_id = ?;",
286 ),
287 updateCommandPoints: db.prepare(
288 "UPDATE settings SET command_points = ? WHERE guild_id = ?;",
289 ),
290 updateVoicePoints: db.prepare(
291 "UPDATE settings SET voice_points = ? WHERE guild_id = ?;",
292 ),
293 updateCrownRoleId: db.prepare(
294 "UPDATE settings SET crown_role_id = ? WHERE guild_id = ?;",
295 ),
296 updateCrownChannelId: db.prepare(
297 "UPDATE settings SET crown_channel_id = ? WHERE guild_id = ?;",
298 ),
299 updateCrownMessage: db.prepare(
300 "UPDATE settings SET crown_message = ? WHERE guild_id = ?;",
301 ),
302 updateCrownSchedule: db.prepare(
303 "UPDATE settings SET crown_schedule = ? WHERE guild_id = ?;",
304 ),
305 deleteGuild: db.prepare("DELETE FROM settings WHERE guild_id = ?;"),
306};
307
308// USERS TABLE
309const users = {
310 insertRow: db.prepare(`
311 INSERT OR IGNORE INTO users (
312 user_id,
313 user_name,
314 user_discriminator,
315 guild_id,
316 guild_name,
317 date_joined,
318 bot,
319 points,
320 total_points,
321 current_member
322 ) VALUES (?, ?, ?, ?, ?, ?, ?, 0, 0, 1);
323 `),
324
325 // Selects
326 selectRow: db.prepare(
327 "SELECT * FROM users WHERE user_id = ? AND guild_id = ?;",
328 ),
329 selectLeaderboard: db.prepare(
330 "SELECT * FROM users WHERE guild_id = ? AND current_member = 1 ORDER BY points DESC;",
331 ),
332 selectPoints: db.prepare(
333 "SELECT points FROM users WHERE user_id = ? AND guild_id = ?;",
334 ),
335 selectTotalPoints: db.prepare(
336 "SELECT total_points FROM users WHERE user_id = ? AND guild_id = ?;",
337 ),
338 selectWarns: db.prepare(
339 "SELECT warns FROM users WHERE user_id = ? AND guild_id = ?;",
340 ),
341 selectCurrentMembers: db.prepare(
342 "SELECT * FROM users WHERE guild_id = ? AND current_member = 1;",
343 ),
344 selectMissingMembers: db.prepare(
345 "SELECT * FROM users WHERE guild_id = ? AND current_member = 0;",
346 ),
347
348 // Updates
349 updateGuildName: db.prepare(
350 "UPDATE users SET guild_name = ? WHERE guild_id = ?;",
351 ),
352 updateUser: db.prepare(
353 "UPDATE users SET user_name = ?, user_discriminator = ? WHERE user_id = ?;",
354 ),
355 updatePoints: db.prepare(`
356 UPDATE users
357 SET points = points + @points, total_points = total_points + @points
358 WHERE user_id = ? AND guild_id = ?;
359 `),
360 wipePoints: db.prepare(
361 "UPDATE users SET points = 0 WHERE user_id = ? AND guild_id = ?;",
362 ),
363 wipeTotalPoints: db.prepare(
364 "UPDATE users SET points = 0, total_points = 0 WHERE user_id = ? AND guild_id = ?;",
365 ),
366 wipeAllPoints: db.prepare("UPDATE users SET points = 0 WHERE guild_id = ?;"),
367 wipeAllTotalPoints: db.prepare(
368 "UPDATE users SET points = 0, total_points = 0 WHERE guild_id = ?;",
369 ),
370 updateWarns: db.prepare(
371 "UPDATE users SET warns = ? WHERE user_id = ? AND guild_id = ?;",
372 ),
373 updateCurrentMember: db.prepare(
374 "UPDATE users SET current_member = ? WHERE user_id = ? AND guild_id = ?;",
375 ),
376 deleteGuild: db.prepare("DELETE FROM users WHERE guild_id = ?;"),
377};
378
379// TEAMS TABLE
380const teams = {
381 insertRow: db.prepare(`
382 INSERT OR IGNORE INTO (
383 guild_id,
384 teamName,
385 user_id,
386 steamId,
387 mmr,
388 ) VALUES (?, ?, ?, ?, 1500);
389 `),
390
391 // Selects
392 selectRow: db.prepare("SELECT * FROM teams WHERE guild_id = ?;"),
393 selectTeamName: db.prepare(
394 "SELECT teamName FROM teams WHERE user_id = ? AND guild_id = ?;",
395 ),
396 selectSteamId: db.prepare(
397 "SELECT steamId FROM teams WHERE user_id = ? AND guild_id = ?;",
398 ),
399 selectMMR: db.prepare(
400 "SELECT mmr FROM teams WHERE user_id = ? AND guild_id = ?;",
401 ),
402
403 // Updates
404 updateTeamName: db.prepare(
405 "UPDATE teams SET teamName = ? WHERE user_id = ? AND guild_id = ?;",
406 ),
407 updatePlayerId: db.prepare(
408 "UPDATE teams SET player_id = ? WHERE user_id = ? AND guild_id = ?;",
409 ),
410 updateMMR: db.prepare(
411 "UPDATE teams SET mmr = ? WHERE user_id = ? AND guild_id = ?;",
412 ),
413};
414
415// STAFF TABLE
416const staff = {
417 insertRow: db.prepare(`
418 INSERT OR IGNORE INTO staff (
419 user_id,
420 user_name,
421 guild_id,
422 department,
423 ) VALUES (?, ?, ?, ?);
424 `),
425
426 // Selects
427 selectRow: db.prepare("SELECT * FROM staff WHERE guild_id = ?;"),
428 selectDepartment: db.prepare(
429 "SELECT department FROM staff WHERE user_id = ? AND guild_id = ?;",
430 ),
431 selectUserName: db.prepare(
432 "SELECT user_name FROM staff WHERE user_id = ? AND guild_id = ?;",
433 ),
434
435 // Updates
436 updateUser: db.prepare(
437 "UPDATE staff SET user_name = ? WHERE user_id = ?;",
438 ),
439 updateDepartment: db.prepare(
440 "UPDATE staff SET department = ? WHERE guild_id = ?;",
441 ),
442};
443
444module.exports = {
445 settings,
446 users,
447 teams,
448 staff,
449};
450