· 4 years ago · Mar 22, 2021, 12:22 AM
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
94// db.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 teams(
383 guild_id,
384 teamName,
385 user_id,
386 steamId,
387 mmr,
388 ) VALUES (?, ?, ?, ?, 1);
389 `),
390};
391
392// // TEAMS TABLE
393// const teams = {
394// insertRow: db.prepare(`
395// INSERT OR IGNORE INTO teams(
396// guild_id,
397// teamName,
398// user_id,
399// steamId,
400// mmr,
401// ) VALUES (?, ?, ?, ?, 1500);
402// `),
403
404// // Selects
405// selectRow: db.prepare("SELECT * FROM teams WHERE guild_id = ?;"),
406// selectTeamName: db.prepare(
407// "SELECT teamName FROM teams WHERE user_id = ? AND guild_id = ?;",
408// ),
409// selectSteamId: db.prepare(
410// "SELECT steamId FROM teams WHERE user_id = ? AND guild_id = ?;",
411// ),
412// selectMMR: db.prepare(
413// "SELECT mmr FROM teams WHERE user_id = ? AND guild_id = ?;",
414// ),
415
416// // Updates
417// updateTeamName: db.prepare(
418// "UPDATE teams SET teamName = ? WHERE user_id = ? AND guild_id = ?;",
419// ),
420// updatePlayerId: db.prepare(
421// "UPDATE teams SET player_id = ? WHERE user_id = ? AND guild_id = ?;",
422// ),
423// updateMMR: db.prepare(
424// "UPDATE teams SET mmr = ? WHERE user_id = ? AND guild_id = ?;",
425// ),
426// };
427
428// // STAFF TABLE
429// const staff = {
430// insertRow: db.prepare(`
431// INSERT OR IGNORE INTO staff (
432// user_id,
433// user_name,
434// guild_id,
435// department,
436// ) VALUES (?, ?, ?, ?);
437// `),
438
439// // Selects
440// selectRow: db.prepare("SELECT * FROM staff WHERE guild_id = ?;"),
441// selectDepartment: db.prepare(
442// "SELECT department FROM staff WHERE user_id = ? AND guild_id = ?;",
443// ),
444// selectUserName: db.prepare(
445// "SELECT user_name FROM staff WHERE user_id = ? AND guild_id = ?;",
446// ),
447
448// // Updates
449// updateUser: db.prepare(
450// "UPDATE staff SET user_name = ? WHERE user_id = ?;",
451// ),
452// updateDepartment: db.prepare(
453// "UPDATE staff SET department = ? WHERE guild_id = ?;",
454// ),
455// };
456
457module.exports = {
458 settings,
459 users,
460 teams,
461 // staff,
462};
463