· 8 years ago · Nov 18, 2017, 10:42 PM
1const { Provider } = require('klasa');
2const mysql = require('mysql2/promise');
3const config = require('../config');
4
5module.exports = class MySQL extends Provider {
6
7 constructor(...args) {
8 super(...args, {
9 enabled: true,
10 sql: true,
11 description: ''
12 });
13 this.db = null;
14 }
15
16 async init() {
17 this.db = await mysql.createConnection(config.mysql);
18 this.heartBeatInterval = setInterval(() => {
19 this.db.query('SELECT 1=1')
20 // .then(() => this.client.emit('log', 'MySQL Heartbeat sent', 'verbose'))
21 .catch(error => this.client.emit('error', error));
22 }, 10000);
23 }
24
25 /* Table methods */
26
27 /**
28 * @param {string} table Check if a table exists
29 * @returns {Promise<boolean>}
30 * @name MySQL#hasTable
31 * @since 2.0.0
32 */
33 hasTable(table) {
34 requestType('MySQL#hasTable', 'table', 'string', table);
35 return this.run(`SHOW TABLES LIKE '${table}';`)
36 .then(result => !!result)
37 .catch(() => false);
38 }
39
40 /**
41 * @param {string} table The name of the table to create
42 * @param {string} rows The rows with their respective datatypes
43 * @returns {Promise<Object[]>}
44 * @name MySQL#createTable
45 * @since 2.0.0
46 */
47 createTable(table, rows) {
48 requestType('MySQL#createTable', 'table', 'string', table);
49 requestType('MySQL#createTable', 'rows', 'string', rows);
50 return this.runAll(`CREATE TABLE ${sanitizeKeyName(table)} (${rows});`);
51 }
52
53 /**
54 * @param {string} table The name of the table to drop
55 * @returns {Promise<Object[]>}
56 * @name MySQL#deleteTable
57 * @since 2.0.0
58 */
59 deleteTable(table) {
60 requestType('MySQL#deleteTable', 'table', 'string', table);
61 return this.exec(`DROP TABLE ${sanitizeKeyName(table)};`);
62 }
63
64 /**
65 * @param {string} table The table with the rows to count
66 * @returns {Promise<number>}
67 * @name MySQL#countRows
68 * @since 2.0.0
69 */
70 countRows(table) {
71 requestType('MySQL#deleteTable', 'table', 'string', table);
72 return this.run(`SELECT COUNT(*) FROM ${sanitizeKeyName(table)};`)
73 .then(result => result['COUNT(*)']);
74 }
75
76 /* Row methods */
77
78 /**
79 * @param {string} table The name of the table to get the data from
80 * @param {string} [key] The key to filter the data from. Requires the value parameter
81 * @param {any} [value] The value to filter the data from. Requires the key parameter
82 * @param {number} [limitMin] The minimum range. Must be higher than zero
83 * @param {number} [limitMax] The maximum range. Must be higher than the limitMin parameter
84 * @returns {Promise<Object[]>}
85 * @name MySQL#getAll
86 * @since 2.0.0
87 */
88 getAll(table, key, value, limitMin, limitMax) {
89 requestType('MySQL#getAll', 'table', 'string', table);
90 if (typeof key !== 'undefined' && typeof value !== 'undefined') {
91 requestType('MySQL#getAll', 'key', 'string', key);
92 return this.runAll(`SELECT * FROM ${sanitizeKeyName(table)} WHERE ${sanitizeKeyName(key)} = ${sanitizeInput(value)} ${parseRange(limitMin, limitMax)};`);
93 }
94
95 return this.runAll(`SELECT * FROM ${sanitizeKeyName(table)} ${parseRange(limitMin, limitMax)};`);
96 }
97
98 /**
99 * @param {string} table The name of the table to get the data from
100 * @param {string} key The key to filter the data from
101 * @param {any} [value] The value of the filtered key
102 * @returns {Promise<Object>}
103 * @name MySQL#get
104 * @since 2.0.0
105 */
106 get(table, key, value) {
107 requestType('MySQL#get', 'table', 'string', table);
108
109 // If a key is given (id), swap it and search by id - value
110 if (typeof value === 'undefined') {
111 value = key;
112 key = 'id';
113 }
114 requestType('MySQL#get', 'key', 'string', key);
115 requestValue('MySQL#get', 'value', value);
116 return this.run(`SELECT * FROM ${sanitizeKeyName(table)} WHERE ${sanitizeKeyName(key)} = ${sanitizeInput(value)} LIMIT 1;`)
117 .catch(throwError);
118 }
119
120 /**
121 * @param {string} table The name of the table to get the data from
122 * @param {string} id The value of the id
123 * @returns {Promise<boolean>}
124 * @name MySQL#has
125 * @since 2.0.0
126 */
127 has(table, id) {
128 requestType('MySQL#has', 'table', 'string', table);
129 requestType('MySQL#has', 'id', 'string', id);
130 return this.run(`SELECT id FROM ${sanitizeKeyName(table)} WHERE id = ${sanitizeString(id)} LIMIT 1;`)
131 .then(row => !!row);
132 }
133
134 /**
135 * @param {string} table The name of the table to get the data from
136 * @returns {Promise<Object>}
137 * @name MySQL#getRandom
138 * @since 2.0.0
139 */
140 getRandom(table) {
141 requestType('MySQL#getRandom', 'table', 'string', table);
142 return this.run(`SELECT * FROM ${sanitizeKeyName(table)} ORDER BY RAND() LIMIT 1;`);
143 }
144
145 /**
146 * @param {string} table The name of the table to get the data from
147 * @param {string} key The key to sort by
148 * @param {('ASC'|'DESC')} [order='DESC'] Whether the order should be ascendent or descendent
149 * @param {number} [limitMin] The minimum range
150 * @param {number} [limitMax] The maximum range
151 * @returns {Promise<Object[]>}
152 * @name MySQL#getSorted
153 * @since 2.0.0
154 */
155 async getSorted(table, key, order = 'DESC', limitMin, limitMax) {
156 requestType('MySQL#getSorted', 'table', 'string', table);
157 requestType('MySQL#getSorted', 'key', 'string', key);
158 if (order !== 'DESC' && order !== 'ASC')
159 throw new TypeError(`MySQL#getSorted 'order' parameter expects either 'DESC' or 'ASC'. Got: ${order}`);
160
161 return this.runAll(`SELECT * FROM ${sanitizeKeyName(table)} ORDER BY ${sanitizeKeyName(key)} ${order} ${parseRange(limitMin, limitMax)};`);
162 }
163
164 /**
165 * @param {string} table The name of the table to insert the new data
166 * @param {string} id The id of the new row to insert
167 * @param {string[]} keys The keys to insert
168 * @param {any[]} values The values to insert
169 * @returns {Promise<any[]>}
170 * @name MySQL#insert
171 * @since 2.0.0
172 */
173 insert(table, id, keys, values) {
174 requestType('MySQL#insert', 'table', 'string', table);
175 requestType('MySQL#insert', 'id', 'string', id);
176 requestType('MySQL#insert', 'keys', 'object', keys);
177 requestType('MySQL#insert', 'values', 'object', values);
178 if (Array.isArray(keys) === false || Array.isArray(values) === false || keys.length !== values.length)
179 throw new TypeError(`MySQL#insert expects the parameters 'keys' and 'values' to be arrays with the same length`);
180
181 // Push the id to the inserts.
182 keys.push('id');
183 values.push(id);
184 return this.exec(`INSERT INTO ${sanitizeKeyName(table)} (${keys.map(sanitizeKeyName).join(', ')}) VALUES (${values.map(sanitizeInput).join(', ')});`);
185 }
186
187 /**
188 * @param {string} table The name of the table to update the data from
189 * @param {string} id The id of the row to update
190 * @param {string} key The key to update
191 * @param {any} value The new value for the key
192 * @returns {Promise<any[]>}
193 * @name MySQL#update
194 * @since 2.0.0
195 */
196 update(table, id, key, value) {
197 requestType('MySQL#update', 'table', 'string', table);
198 requestType('MySQL#update', 'id', 'string', id);
199 requestType('MySQL#update', 'key', 'string', key);
200 requestValue('MySQL#update', 'value', value);
201 return this.exec(`UPDATE ${sanitizeKeyName(table)} SET ${sanitizeKeyName(key)} = ${sanitizeInput(value)} WHERE id = ${sanitizeString(id)};`);
202 }
203
204 /**
205 * @param {string} table The name of the table to update the data from
206 * @param {string} id The id of the row to update
207 * @param {string} key The key to update
208 * @param {number} [amount=1] The value to increase
209 * @returns {Promise<any[]>}
210 * @name MySQL#incrementValue
211 * @since 2.0.0
212 */
213 incrementValue(table, id, key, amount = 1) {
214 requestType('MySQL#incrementValue', 'table', 'string', table);
215 requestType('MySQL#incrementValue', 'id', 'string', id);
216 requestType('MySQL#incrementValue', 'key', 'string', key);
217 requestType('MySQL#incrementValue', 'amount', 'number', amount);
218 if (amount < 0 || isNaN(amount) || Number.isInteger(amount) === false || Number.isSafeInteger(amount) === false)
219 throw new TypeError(`MySQL#incrementValue expects the parameter 'amount' to be an integer greater or equal than zero. Got: ${amount}`);
220
221 return this.exec(`UPDATE ${sanitizeKeyName(table)} SET ${key} = ${key} + ${amount} WHERE id = ${sanitizeString(id)};`);
222 }
223
224 /**
225 * @param {string} table The name of the table to update the data from
226 * @param {string} id The id of the row to update
227 * @param {string} key The key to update
228 * @param {number} [amount=1] The value to decrease
229 * @returns {Promise<any[]>}
230 * @name MySQL#decrementValue
231 * @since 2.0.0
232 */
233 decrementValue(table, id, key, amount = 1) {
234 requestType('MySQL#decrementValue', 'table', 'string', table);
235 requestType('MySQL#decrementValue', 'id', 'string', id);
236 requestType('MySQL#decrementValue', 'key', 'string', key);
237 requestType('MySQL#decrementValue', 'amount', 'number', amount);
238 if (amount < 0 || isNaN(amount) || Number.isInteger(amount) === false || Number.isSafeInteger(amount) === false)
239 throw new TypeError(`MySQL#incrementValue expects the parameter 'amount' to be an integer greater or equal than zero. Got: ${amount}`);
240
241 return this.exec(`UPDATE ${sanitizeKeyName(table)} SET ${key} = GREATEST(0, ${key} - ${amount}) WHERE id = ${sanitizeString(id)};`);
242 }
243
244 /**
245 * @param {string} table The name of the table to update
246 * @param {string} id The id of the row to delete
247 * @returns {Promise<any[]>}
248 * @name MySQL#delete
249 * @since 2.0.0
250 */
251 delete(table, id) {
252 requestType('MySQL#delete', 'table', 'string', table);
253 return this.exec(`DELETE FROM ${sanitizeKeyName(table)} WHERE id = ${sanitizeString(id)};`);
254 }
255
256 /**
257 * Get a row from an arbitrary SQL query.
258 * @param {string} sql The query to execute.
259 * @returns {Promise<Object>}
260 * @name MySQL#run
261 * @since 2.0.0
262 */
263 run(sql) {
264 return this.db.query(sql)
265 .then(([rows]) => rows[0])
266 .catch(throwError);
267 }
268
269 /**
270 * Get all rows from an arbitrary SQL query.
271 * @param {string} sql The query to execute.
272 * @returns {Promise<Object[]>}
273 * @name MySQL#runAll
274 * @since 2.0.0
275 */
276 runAll(sql) {
277 return this.db.query(sql)
278 .then(([rows]) => rows)
279 .catch(throwError);
280 }
281
282 /**
283 *
284 * @param {string} sql The query to execute
285 * @returns {Promise<Object[]>}
286 * @name MySQL#exec
287 * @since 2.0.0
288 */
289 exec(sql) {
290 return this.db.query(sql)
291 .catch(throwError);
292 }
293
294};
295
296/**
297 * @param {number} [min] The minimum value
298 * @param {number} [max] The maximum value
299 * @returns {string}
300 * @private
301 */
302function parseRange(min, max) {
303 // Min value validation
304 if (typeof min === 'undefined') return '';
305 if (isNaN(min) || Number.isInteger(min) === false || Number.isSafeInteger(min) === false)
306 throw new TypeError(`%MySQL.parseRange 'min' parameter expects an integer or undefined, got ${min}`);
307 if (min < 0)
308 throw new TypeError(`%MySQL.parseRange 'min' parameter expects to be equal or greater than zero, got ${min}`);
309
310 // Max value validation
311 if (typeof max !== 'undefined') {
312 if (typeof max !== 'number' || isNaN(max) || Number.isInteger(max) === false || Number.isSafeInteger(max) === false)
313 throw new TypeError(`%MySQL.parseRange 'max' parameter expects an integer or undefined, got ${max}`);
314 if (max <= min)
315 throw new TypeError(`%MySQL.parseRange 'max' parameter expects ${max} to be greater than ${min}. Got: ${max} <= ${min}`);
316 }
317
318 return `LIMIT ${min}${typeof max === 'number' ? `,${max}` : ''}`;
319}
320
321/**
322 * @param {string} method The name of the method
323 * @param {string} parameter The parameter name
324 * @param {string} type The expected primitive type of the parameter
325 * @param {any} value The value to test
326 * @private
327 */
328function requestType(method, parameter, type, value) {
329 const currentType = typeof value;
330 if (currentType !== type) throw new TypeError(`${method} '${parameter}' parameter expects type of ${type}. Got: ${currentType}`);
331}
332
333/**
334 * @param {string} method The name of the method
335 * @param {string} parameter The parameter name
336 * @param {any} value The value to test if undefined
337 * @private
338 */
339function requestValue(method, parameter, value) {
340 const currentType = typeof value;
341 if (currentType === 'undefined') throw new TypeError(`${method} '${parameter}' parameter expects a value. Got: undefined`);
342}
343
344/**
345 * @param {number} value The number to sanitize
346 * @returns {string}
347 * @private
348 */
349function sanitizeInteger(value) {
350 if (isNaN(value) || Number.isInteger(value) === false || Number.isSafeInteger(value) === false)
351 throw new TypeError(`%MySQL.sanitizeNumber expects an integer, got ${value}`);
352 if (value < 0)
353 throw new TypeError(`%MySQL.sanitizeNumber expects a positive integer, got ${value}`);
354
355 return String(value);
356}
357
358/**
359 * @param {string} value The string to sanitize
360 * @returns {string}
361 * @private
362 */
363function sanitizeString(value) {
364 if (value.length === 0)
365 throw new TypeError('%MySQL.sanitizeString expects a string with a length bigger than 0.');
366
367 return `'${value.replace(/'/g, "''")}'`;
368}
369
370/**
371 * @param {string} value The string to sanitize as a key
372 * @returns {string}
373 * @private
374 */
375function sanitizeKeyName(value) {
376 if (typeof value !== 'string')
377 throw new TypeError(`%MySQL.sanitizeString expects a string, got: ${typeof value}`);
378 if (/`/.test(value))
379 throw new TypeError(`Invalid input (${value}).`);
380
381 return `\`${value}\``;
382}
383
384/**
385 * @param {Object} value The object to sanitize
386 * @returns {string}
387 * @private
388 */
389function sanitizeObject(value) {
390 if (value === null) return 'NULL';
391 if (Array.isArray(value)) return JSON.stringify(value.map(sanitizeInput));
392 const type = Array.prototype.toString.call(value);
393 if (type === '[object Object]') return sanitizeString(JSON.stringify(value));
394 throw new TypeError(`%MySQL.sanitizeObject expects NULL, an array, or an object. Got: ${type}`);
395}
396
397/**
398 *
399 * @param {any} value The value to sanitize
400 * @returns {string}
401 * @private
402 */
403function sanitizeInput(value) {
404 const type = typeof value;
405 switch (type) {
406 case 'string': return sanitizeString(value);
407 case 'number': return sanitizeInteger(value);
408 case 'object': return sanitizeObject(value);
409 default: throw new TypeError(`%MySQL.sanitizeInput expects type of string, number, or object. Got: ${type}`);
410 }
411}
412
413// In several V8 versions, Promise errors do not bubble up, this workaround
414// forces errors to do so.
415const throwError = (err) => { throw err; };