501 lines
21 KiB
JavaScript
501 lines
21 KiB
JavaScript
// Customizable storage module for any mode of storage
|
|
// Update the functions here to change the storage medium
|
|
|
|
// Import modules
|
|
const { DebugBuilder } = require("./utilities/debugBuilder");
|
|
const log = new DebugBuilder("server", "libStorage");
|
|
const { RSSSourceRecord, RSSPostRecord } = require("./utilities/recordHelper");
|
|
|
|
// Storage Specific Modules
|
|
// MySQL
|
|
const mysql = require("mysql");
|
|
|
|
const rssFeedsTable = process.env.DB_RSS_FEEDS_TABLE;
|
|
const rssPostsTable = process.env.DB_RSS_POSTS_TABLE;
|
|
const accountsTable = process.env.DB_ACCOUNTS_TABLE;
|
|
const transactionsTable = process.env.DB_TRANSACTIONS_TABLE;
|
|
const pricingTable = process.env.DB_PRICING_TABLE;
|
|
|
|
var Connection = mysql.createPool({
|
|
host: process.env.DB_HOST,
|
|
user: process.env.DB_USER,
|
|
password: process.env.DB_PASS,
|
|
database: process.env.DB_NAME,
|
|
connectionLimit: 10
|
|
});
|
|
|
|
// Helper Functions
|
|
/**
|
|
* Function to run and handle SQL errors
|
|
* @param {string} sqlQuery The SQL query string
|
|
* @param {*} connection The SQL connection to be used to query
|
|
* @param {function} callback The callback function to be called with an error or the results
|
|
* @param {number} _retry Set by error retry, increments the number a query has been retried to increase wait time and track a specific query
|
|
*/
|
|
function runSQL(sqlQuery, connection, callback = (err, rows) => {
|
|
log.ERROR(err);
|
|
throw err;
|
|
}, _retry = 0) {
|
|
// Start the MySQL Connection
|
|
if (!connection) connection = Connection;
|
|
connection.query(sqlQuery, (err, rows) => {
|
|
if (err) {
|
|
if (err.code === "EHOSTUNREACH") {
|
|
// DB Connection is unavailable
|
|
let retryTimeout;
|
|
switch(_retry){
|
|
case 0:
|
|
retryTimeout = 30000;
|
|
break;
|
|
case retry < 15:
|
|
retryTimeout = 30000 + retry * 15000;
|
|
break;
|
|
default:
|
|
log.ERROR("Retried Database 15 times over, please check connection status and restart the app", sqlQuery, err);
|
|
return callback(err, undefined);
|
|
}
|
|
log.WARN(`Database connection is unavailable, waiting ${ retryTimeout / 1000 } seconds...`);
|
|
_retry += 1
|
|
// Wait for the retry timeout before trying the query again
|
|
setTimeout(runSQL(sqlQuery, connection, callback, _retry));
|
|
}
|
|
else return callback(err, undefined);
|
|
}
|
|
log.VERBOSE(`SQL result for query '${sqlQuery}':`, rows);
|
|
return callback(undefined, rows);
|
|
})
|
|
}
|
|
|
|
/**
|
|
* Return a formatted date time string from now for MySQL
|
|
*
|
|
* @returns Date string for now formatted for MySQL
|
|
*/
|
|
function returnMysqlTime(){
|
|
return new Date().toISOString().slice(0, 19).replace('T', ' ');
|
|
}
|
|
|
|
class Storage {
|
|
constructor(_dbTable, _connection) {
|
|
this.dbTable = _dbTable;
|
|
this.connection = _connection;
|
|
this.validKeys = [];
|
|
|
|
var sqlQuery = `SHOW COLUMNS FROM ${this.dbTable};`;
|
|
|
|
runSQL(sqlQuery, this.connection, (err, rows) => {
|
|
if (err) return log.ERROR("Error getting column names: ", err);
|
|
if (rows){
|
|
for (const validKey of rows){
|
|
this.validKeys.push(validKey.Field);
|
|
}
|
|
log.VERBOSE(`Database rows for '${this.dbTable}': `, rows);
|
|
log.DEBUG(`Keys for '${this.dbTable}': `, this.validKeys);
|
|
}
|
|
})
|
|
}
|
|
|
|
/**
|
|
* Wrapper to delete an entry using the storage method configured
|
|
* @param {} entryID The ID of the entry to be deleted
|
|
* @param {function} callback The callback function to be called with the record when deleted
|
|
*/
|
|
destroy(entryID, callback) {
|
|
if (!entryID) return callback(Error("No entry ID given"), undefined);
|
|
|
|
this.getRecordBy('id', entryID, (err, entryRecord) => {
|
|
this.removeEntry(entryRecord.id, (err, results) => {
|
|
if (err) return callback(err, undefined);
|
|
return callback(undefined, results);
|
|
});
|
|
});
|
|
}
|
|
|
|
/**
|
|
* Get a record by a specified key
|
|
* @param {*} key The key to search for
|
|
* @param {*} keyValue The value of the key to search for
|
|
* @param {*} callback The callback function
|
|
*/
|
|
getRecordBy(key, keyValue, callback) {
|
|
if (!this.validKeys.includes(key)) return callback(new Error("Given key not valid", key), undefined);
|
|
|
|
const sqlQuery = `SELECT * FROM ${this.dbTable} WHERE ${key} = "${keyValue}"`;
|
|
|
|
runSQL(sqlQuery, this.connection, (err, rows) => {
|
|
if (err) return callback(err, undefined);
|
|
if (rows[0]?.[key]) return callback(undefined, rows[0]);
|
|
else return callback(undefined, false);
|
|
})
|
|
}
|
|
|
|
/**
|
|
* Get all records stored
|
|
* @param {function} callback
|
|
*/
|
|
getAllRecords(callback) {
|
|
log.INFO("Getting all records from: ", this.dbTable);
|
|
const sqlQuery = `SELECT * FROM ${this.dbTable}`
|
|
|
|
let records = [];
|
|
|
|
runSQL(sqlQuery, this.connection, (err, rows) => {
|
|
if (err) return callback(err, undefined);
|
|
for (const row of rows) {
|
|
if (this.dbTable == rssFeedsTable){
|
|
records.push(new RSSSourceRecord(row.id, row.title, row.link, row.category, row.guild_id, row.channel_id));
|
|
}
|
|
else {
|
|
records.push(rows);
|
|
}
|
|
}
|
|
log.VERBOSE("All records:", records);
|
|
return callback(undefined, records);
|
|
});
|
|
}
|
|
|
|
/**
|
|
* Gets all unique rows in the given key
|
|
* @param {*} key
|
|
* @param {*} callback
|
|
*/
|
|
getUniqueByKey(key, callback){
|
|
log.INFO("Getting all unique values in column: ", key);
|
|
const sqlQuery = `SELECT DISTINCT ${key} FROM ${this.dbTable}`
|
|
|
|
let records = [];
|
|
|
|
runSQL(sqlQuery, this.connection, (err, rows) => {
|
|
if (err) return callback(err, undefined);
|
|
for (const row of rows) {
|
|
if (this.dbTable == rssFeedsTable){
|
|
records.push(new RSSSourceRecord(row.id, row.title, row.link, row.category, row.guild_id, row.channel_id));
|
|
}
|
|
else {
|
|
records.push(rows);
|
|
}
|
|
}
|
|
log.VERBOSE("All records:", records);
|
|
return callback(undefined, records);
|
|
});
|
|
}
|
|
|
|
closeConnection() {
|
|
try {
|
|
this.connection.end();
|
|
}
|
|
catch (err) {
|
|
log.ERROR("Error closing connection :", this.connection, err);
|
|
throw err;
|
|
}
|
|
}
|
|
}
|
|
|
|
exports.UserStorage = class UserStorage extends Storage {
|
|
constructor(connection = undefined) {
|
|
super(accountsTable, connection);
|
|
}
|
|
|
|
/**
|
|
* Save a new account to the database
|
|
* @param {*} _discordAccountId The Discord ID the the user
|
|
* @param {*} callback The callback to be sent
|
|
* @callback Error|Array|*
|
|
*/
|
|
saveAccount(_discordAccountId, callback){
|
|
const sqlQuery = `INSERT INTO ${this.dbTable} (discord_account_id, balance) VALUES ("${_discordAccountId}", ${0});`;
|
|
|
|
log.DEBUG(`Adding new entry with SQL query: '${sqlQuery}'`)
|
|
|
|
runSQL(sqlQuery, this.connection, (err, rows) => {
|
|
if (err) return callback(err, undefined);
|
|
if (rows?.affectedRows > 0) return callback(undefined, rows);
|
|
return callback(undefined, undefined);
|
|
})
|
|
}
|
|
|
|
/**
|
|
* Check or return the balance of a given account ID
|
|
*
|
|
* @param {*} _tokensToBeUsed The amount of tokens to be used, set to 0 to return the balance
|
|
* @param {*} _account_id The account ID to check or return the balance of
|
|
* @param {*} callback
|
|
*/
|
|
checkBalance(_tokensToBeUsed, _account_id, callback) {
|
|
if (!_account_id) return callback(new Error("Account not specified when checking account balance"), undefined);
|
|
log.DEBUG("Tokens to verify against balance", _tokensToBeUsed, _account_id);
|
|
if (!_tokensToBeUsed && !_tokensToBeUsed >= 0) return callback(new Error("Specified tokens are invalid when checking account balance"), undefined);
|
|
this.getRecordBy('account_id', _account_id, (err, record) => {
|
|
if (err) return callback(err, undefined);
|
|
|
|
// Check to see if the account has a balance greater than what was given
|
|
if(_tokensToBeUsed > 0){
|
|
if (record?.balance && record.balance > _tokensToBeUsed) return callback(undefined, true);
|
|
else return callback(undefined, false);
|
|
}
|
|
|
|
return callback(undefined, record.balance)
|
|
})
|
|
}
|
|
|
|
/**
|
|
* Update a user's account Balance
|
|
*
|
|
* @param {string} _updateType The type of update to make to the account [ withdraw | deposit ]
|
|
* @param {number} _updateAmount The amount to update the account
|
|
* @param {number} _account_id The ID of the discord account to update
|
|
* @param {function} callback The callback function to call with the results
|
|
* @returns Result from the SQL query or false
|
|
*/
|
|
updateBalance(_updateType, _updateAmount, _discord_account_id, callback){
|
|
var sqlQuery = "";
|
|
switch(_updateType){
|
|
case "withdraw":
|
|
// Code here to withdraw funds
|
|
sqlQuery = `UPDATE ${this.dbTable} SET balance=balance-${_updateAmount} WHERE discord_account_id = ${_discord_account_id};`;
|
|
break;
|
|
case "deposit":
|
|
// Code here to withdraw funds
|
|
sqlQuery = `UPDATE ${this.dbTable} SET balance=balance+${_updateAmount} WHERE discord_account_id = ${_discord_account_id};`;
|
|
break;
|
|
default:
|
|
log.ERROR('Update type not valid: ', _updateType);
|
|
return callback(new Error("Update type not valid"));
|
|
}
|
|
|
|
if(!sqlQuery) return callback(new Error("SQL Query empty"), undefined);
|
|
|
|
log.DEBUG("Updating Balance with SQL Query: ", sqlQuery);
|
|
|
|
runSQL(sqlQuery, this.connection, (err, rows) => {
|
|
if (err) return callback(err, undefined);
|
|
if (!rows?.affectedRows > 0) return callback(new Error("Error updating Balance", rows), undefined);
|
|
return callback(undefined, rows);
|
|
})
|
|
}
|
|
}
|
|
|
|
exports.TransactionStorage = class TransactionStorage extends Storage {
|
|
constructor(connection = undefined) {
|
|
super(transactionsTable, connection);
|
|
}
|
|
|
|
createTransaction(transaction, callback){
|
|
var sqlQuery = `INSERT INTO ${this.dbTable} (transaction_id, account_id, discord_tokens_used, provider_tokens_used, provider_id, order_date) VALUES ("${transaction.transaction_id}", "${transaction.account_id}", "${transaction.discord_tokens_used}", "${transaction.provider_tokens_used}", "${transaction.provider_id}", "${returnMysqlTime()}");`;
|
|
|
|
log.DEBUG(`Adding new entry with SQL query: '${sqlQuery}'`)
|
|
|
|
runSQL(sqlQuery, this.connection, (err, rows) => {
|
|
if (err) return callback(err, undefined);
|
|
if (rows?.affectedRows > 0) return callback(undefined, rows);
|
|
return callback(undefined, undefined);
|
|
})
|
|
}
|
|
}
|
|
|
|
exports.FeedStorage = class FeedStorage extends Storage {
|
|
constructor(connection = undefined) {
|
|
super(rssFeedsTable, connection);
|
|
}
|
|
|
|
/**
|
|
* Wrapper to save a new entry using the storage method configured
|
|
* @param {Array} toBeSaved Entry or Entries to be added
|
|
* @param {function} callback The callback function to be called with the record when saved
|
|
*/
|
|
create(toBeSaved, callback) {
|
|
log.DEBUG("To be saved:", toBeSaved);
|
|
log.DEBUG("to be saved length:", toBeSaved.length);
|
|
// If the request was for the Feeds Table
|
|
if (!toBeSaved[0].fields?.title) return callback(Error("No title given"), undefined);
|
|
let newRecords = []
|
|
for (var entry of toBeSaved) {
|
|
entry = entry.fields;
|
|
log.DEBUG("Entry:", entry);
|
|
this.returnRecord(undefined, entry.title, entry.link, entry.category, entry.guild_id, entry.channel_id, (err, record) => {
|
|
if (err) return callback(err, undefined);
|
|
newRecords.push(record);
|
|
if (toBeSaved.length === 1) {
|
|
log.DEBUG("One record to callback with:", record);
|
|
return callback(undefined, record);
|
|
}
|
|
}, false) // Do not update the if it exists
|
|
}
|
|
if (!toBeSaved.length === 1) {
|
|
return callback(undefined, newRecords);
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Check to see if an entry exists in the storage method configured
|
|
* @param {*} title The title of the entry to check if it exists
|
|
* @returns {true|false|*}
|
|
*/
|
|
checkForTitle(title, callback) {
|
|
if (!title) return callback(new Error("No title given when checking for title"), undefined)
|
|
|
|
this.getRecordBy("title", title, callback);
|
|
}
|
|
|
|
/**
|
|
* Save the given entry to the storage medium
|
|
* @param {Object} entryObject The entry object to be saved
|
|
* @param {function} callback The callback to be called with either an error or undefined if successful
|
|
*/
|
|
saveEntry(entryObject, callback) {
|
|
log.DEBUG("Saving entry:", entryObject);
|
|
if (!entryObject?.title || !entryObject?.link || !entryObject?.category) {
|
|
return callback(new Error("Entry object malformed, check the object before saving it"), undefined)
|
|
}
|
|
|
|
const sqlQuery = `INSERT INTO ${this.dbTable} (title, link, category, guild_id, channel_id) VALUES ("${entryObject.title}", "${entryObject.link}", "${entryObject.category}", "${entryObject.guild_id}", "${entryObject.channel_id}");`;
|
|
|
|
log.DEBUG(`Adding new entry with SQL query: '${sqlQuery}'`)
|
|
|
|
runSQL(sqlQuery, this.connection, (err, rows) => {
|
|
if (err) return callback(err, undefined);
|
|
return callback(undefined, rows);
|
|
})
|
|
}
|
|
|
|
/**
|
|
* Save the given entry to the storage medium
|
|
* @param {Object} entryObject The entry object to be saved
|
|
* @param {function} callback The callback to be called with either an error or undefined if successful
|
|
*/
|
|
updateEntry(entryObject, callback) {
|
|
let queryParams = [];
|
|
if (!entryObject.title) return callback(new Error("No title given before updating"), undefined);
|
|
queryParams.push(`title = "${entryObject.title}"`);
|
|
if (!entryObject.link) return callback(new Error("No link given before updating"), undefined);
|
|
queryParams.push(`link = "${entryObject.link}"`);
|
|
if (entryObject.category) queryParams.push(`category = "${entryObject.category}"`);
|
|
if (entryObject.guild_id) queryParams.push(`guild_id = "${entryObject.guild_id}"`);
|
|
if (entryObject.channel_id) queryParams.push(`channel_id = "${entryObject.channel_id}"`);
|
|
|
|
let sqlQuery = `UPDATE ${this.dbTable} SET`;
|
|
|
|
let i = 0;
|
|
for (const param of queryParams) {
|
|
if (i === queryParams.length-1) {
|
|
sqlQuery = `${sqlQuery} ${param}`
|
|
i += 1;
|
|
}
|
|
else {
|
|
sqlQuery = `${sqlQuery} ${param},`
|
|
i += 1;
|
|
}
|
|
}
|
|
|
|
sqlQuery = `${sqlQuery} WHERE title = "${entryObject.title}";`
|
|
|
|
log.DEBUG(`Updating entry with SQL query: '${sqlQuery}'`)
|
|
|
|
runSQL(sqlQuery, this.connection, (err, rows) => {
|
|
if (err) return callback(err, undefined);
|
|
return callback(undefined, rows);
|
|
})
|
|
}
|
|
|
|
/**
|
|
* Delete the given entry from the storage medium
|
|
* @param {*} id The title of the entry to be deleted
|
|
* @param {function} callback The callback to be called with either an error or undefined if successful
|
|
*/
|
|
removeEntry(id, callback) {
|
|
if (!id) {
|
|
return callback(new Error("No entry id given before deleting"), undefined)
|
|
}
|
|
|
|
const sqlQuery = `DELETE FROM ${this.dbTable} WHERE id = "${id}";`;
|
|
|
|
runSQL(sqlQuery, this.connection, (err, rows) => {
|
|
if (err) return callback(err, undefined);
|
|
return callback(undefined, rows[0]);
|
|
})
|
|
}
|
|
|
|
/**
|
|
* Returns a record class for the given information, if there's no ID, it will create it
|
|
* @param {*} _id The ID / line number of the record in the storage medium (OPT)
|
|
* @param {*} _title The title of the record
|
|
* @param {*} _link The link to the RSS feed
|
|
* @param {*} _category The category of the record
|
|
* @param {*} callback Callback function to return an error or the record
|
|
*/
|
|
returnRecord(_id, _title, _link, _category, _guild_id, _channel_id, callback, updateEnabled = true) {
|
|
log.DEBUG(`Return record for these values: ID: '${_id}', Title: '${_title}', Category: '${_category}', Link: '${_link}', Guild: '${_guild_id}', Channel:'${_channel_id}', Update Enabled: `, updateEnabled)
|
|
if (!_link && !_title && !_guild_id && !_channel_id) return callback(new Error("No link or title given when creating a record"), undefined);
|
|
let entryObject = {
|
|
"title": _title,
|
|
"link": _link,
|
|
"guild_id": _guild_id,
|
|
"channel_id": _channel_id
|
|
}
|
|
if (_category) entryObject.category = _category;
|
|
|
|
if (_id) {
|
|
entryObject.id = _id;
|
|
if (!updateEnabled) return callback(undefined, undefined);
|
|
|
|
this.updateEntry(entryObject, (err, rows) => {
|
|
if (err) return callback(err, undefined);
|
|
this.getRecordBy('id', entryObject.id, (err, record) => {
|
|
if (err) return callback(err, undefined);
|
|
return callback(undefined, new RSSSourceRecord(record.id, record.title, record.link, record.category, record.guild_id, record.channel_id));
|
|
})
|
|
})
|
|
}
|
|
else {
|
|
this.checkForTitle(_title, (err, titleExists) => {
|
|
if (!titleExists) {
|
|
log.DEBUG("Entry doesn't exist, making one now", entryObject);
|
|
this.saveEntry(entryObject, (err, rows) => {
|
|
if (err) return callback(err, undefined);
|
|
this.getRecordBy("title", entryObject.title, (err, record) => {
|
|
if (err) return callback(err, undefined);
|
|
return callback(undefined, new RSSSourceRecord(record.id, record.title, record.link, record.category, record.guild_id, record.channel_id));
|
|
})
|
|
});
|
|
}
|
|
else{
|
|
if (!updateEnabled) return callback(undefined, undefined);
|
|
|
|
this.updateEntry(entryObject, (err, rows) => {
|
|
if (err) return callback(err, undefined);
|
|
this.getRecordBy('title', entryObject.title, (err, record) => {
|
|
if (err) return callback(err, undefined);
|
|
return callback(undefined, new RSSSourceRecord(record.id, record.title, record.link, record.category, record.guild_id, record.channel_id));
|
|
})
|
|
})
|
|
}
|
|
})
|
|
}
|
|
}
|
|
}
|
|
|
|
exports.PostStorage = class PostStorage extends Storage {
|
|
constructor(connection = undefined) {
|
|
super(rssPostsTable, connection);
|
|
}
|
|
|
|
savePost(_postObject, callback){
|
|
const tempCreationDate = returnMysqlTime();
|
|
log.DEBUG("Saving Post Object:", _postObject);
|
|
if (!_postObject?.postId || !_postObject?.link) {
|
|
return callback(new Error("Post object malformed, check the object before saving it", _postObject), undefined)
|
|
}
|
|
|
|
if (_postObject.link.length > 250) _postObject.link = _postObject.link.substring(0, 250);
|
|
|
|
const sqlQuery = `INSERT INTO ${this.dbTable} (post_guid, post_link, post_sent_date) VALUES ("${_postObject.postId}","${_postObject.link}","${tempCreationDate}");`;
|
|
|
|
log.DEBUG(`Adding new post with SQL query: '${sqlQuery}'`)
|
|
|
|
runSQL(sqlQuery, this.connection, (err, rows) => {
|
|
if (err) return callback(err, undefined);
|
|
return callback(undefined, rows);
|
|
})
|
|
}
|
|
}
|