435 lines
18 KiB
JavaScript
435 lines
18 KiB
JavaScript
"use strict"
|
|
|
|
const mysql = require("mysql");
|
|
const Select = require("./lib/Select");
|
|
const Insert = require("./lib/Insert");
|
|
const Delete = require("./lib/Delete");
|
|
const Update = require("./lib/Update");
|
|
const { CreateTable, Structure, AlterTable } = require("./lib/Tables");
|
|
const { throwTypeError } = require("./lib/Errors");
|
|
|
|
/**
|
|
* @typedef {Object} InstanceOptions
|
|
* @property {String} [charset] - Charset to use
|
|
* @property {String} [defaultDatabase] - The default database
|
|
* @property {Boolean} [multipleStatements] - Whether multiple statements should be allowed in a single query
|
|
* @property {Boolean} [insecureAuth] - Whether insecure authentication methods should be allowed
|
|
* @property {String} [customIdentifier] - Sets a custom identifier for this instance
|
|
* @property {Boolean} [isDefault] - Whether this instance is returned by default via 'getInstance'
|
|
* @property {object} [ssl] - Whether to use ssl
|
|
*/
|
|
|
|
/**
|
|
* Exported as instance
|
|
*/
|
|
class awSQL {
|
|
#instances = {}; // Holds instances. Key is identifier/name.
|
|
#default; // Holds the identifier of the default instance (the one used if identifier is omitted)
|
|
|
|
/**
|
|
* Creates a new instance (database connection)
|
|
* @param {String} hostname - Hostname where the database is located
|
|
* @param {String} username - Username to login with
|
|
* @param {String} password
|
|
* @param {InstanceOptions} [options]
|
|
* @returns {Instance}
|
|
*/
|
|
createInstance(hostname="localhost", username, password, options = {
|
|
charset:"utf8mb4",
|
|
defaultDatabase: false,
|
|
multipleStatements: false,
|
|
insecureAuth: false,
|
|
customIdentifier: false,
|
|
isDefault: false,
|
|
ssl: false,
|
|
}){
|
|
if (!password) throw new Error(`Can't create instance: No password given`);
|
|
if (!username) throw new Error(`Can't create instance: No username given`);
|
|
const identifier = options.customIdentifier||`${username}@${hostname}`; // Set identifier to given identifier or [username]@[hostname]
|
|
// If an instance with that identifier exists, throw error
|
|
if (this.#instances[identifier]) throw new Error(`Can't create new instance with identifier "${identifier}": An instance with the same name already exists`);
|
|
const instance = new Instance(hostname, username, password, options.charset, options.defaultDatabase, options.multipleStatements, options.insecureAuth, options.ssl);
|
|
this.#instances[identifier] = instance; // Store instance
|
|
if (options.createAsDefault) this.#default = identifier; // If this instance was created with default option set it as the default instance
|
|
return instance;
|
|
}
|
|
|
|
/**
|
|
* Returns an instance matching the given identifier
|
|
*
|
|
* Returns default (or first) instance if no identifier is given
|
|
* @param {String} [identifier] - Identifier of the instance to get
|
|
* @returns {Instance}
|
|
*/
|
|
getInstance(identifier) {
|
|
if (Object.keys(this.#instances).length===0) return undefined; // If no instance is found at all return -> Safety return
|
|
// If no identifier is set return default or first instance
|
|
if (!identifier) {
|
|
return this.#default?
|
|
this.#instances[this.#default] // If default exists get that
|
|
:
|
|
this.#instances[Object.keys(this.#instances)[0]]; // Otherwise return first instance
|
|
}
|
|
return this.#instances[identifier]; // If identifier given return that instance
|
|
}
|
|
|
|
/**
|
|
* Returns a list of the identifiers of all instances
|
|
* @returns {Array<String>}
|
|
*/
|
|
listInstances(){
|
|
return Object.keys(this.#instances);
|
|
}
|
|
|
|
/**
|
|
* Deletes an instance (and closes any open connection)
|
|
* @param {String} identifier - Identifier of the instance to delete
|
|
* @returns {true}
|
|
*/
|
|
deleteInstance(identifier){
|
|
if (!identifier) throw new Error("Can't delete Instance: No identifier set");
|
|
if (typeof identifier !== "string") throwTypeError("string", identifier);
|
|
if (!this.#instances[identifier]) throw new Error(`Can't delete Instance '${identifier}': No Instance`);
|
|
this.#instances[identifier].destroy(); // Memory: Close connection
|
|
if (this.#default === identifier) this.#default = undefined; // If this instance was default, clear it from default
|
|
delete this.#instances[identifier];
|
|
return true;
|
|
}
|
|
}
|
|
|
|
/**
|
|
* An awSQL-Instance
|
|
*/
|
|
class Instance {
|
|
#user;
|
|
#password;
|
|
#host;
|
|
#insecureAuth;
|
|
#multipleStatements;
|
|
#charset;
|
|
#connection;
|
|
#ssl;
|
|
|
|
#selectedDatabase;
|
|
constructor(hostname="localhost", username, password, charset="utf8mb4", defaultDatabase=false, multipleStatements=false, insecureAuth=false, ssl=false){
|
|
this.#host = hostname;
|
|
this.#user = username;
|
|
this.#password = password;
|
|
this.#charset = charset;
|
|
this.#multipleStatements = multipleStatements;
|
|
this.#insecureAuth = insecureAuth;
|
|
this.#selectedDatabase = defaultDatabase||username;
|
|
this.#ssl = ssl;
|
|
}
|
|
|
|
/**
|
|
* Connects the instance
|
|
* @returns {undefined}
|
|
*/
|
|
connect(){
|
|
return new Promise((resolve, reject) => {
|
|
const connection = mysql.createConnection({ // Create a new mysql-connection
|
|
user: this.#user,
|
|
password: this.#password,
|
|
host: this.#host,
|
|
insecureAuth: this.#insecureAuth,
|
|
multipleStatements: this.#multipleStatements,
|
|
charset: this.#charset,
|
|
ssl: this.#ssl
|
|
});
|
|
this.#connection = connection; // Store the connection
|
|
connection.connect((err) =>{
|
|
if (err) throw err;
|
|
resolve(`Connected to ${this.#host} with user ${this.#user}`);
|
|
});
|
|
connection.on("error", (err) => {
|
|
if (!err.fatal){
|
|
return;
|
|
}
|
|
this.destroy(); // Memory: Destroy if connection errored
|
|
this.connect(); // And try to reconnect
|
|
})
|
|
})
|
|
}
|
|
|
|
/**
|
|
* Destroys the connection
|
|
* @returns {true}
|
|
*/
|
|
destroy(){
|
|
if (this.#connection) this.#connection.end();
|
|
this.#connection = undefined;
|
|
return true;
|
|
}
|
|
|
|
/**
|
|
* Performs a raw query
|
|
* @param {String} queryString The sql query string to perform.
|
|
* @param {Array<Any>} values - An array holding all replacable ?-values from left to right.
|
|
* @returns {Any} - The individual result of your query
|
|
*/
|
|
queryRaw(queryString, values=[]){
|
|
if (!queryString) throw new Error(`queryString must not be empty`);
|
|
if (typeof queryString !== "string") throwTypeError("string", queryString);
|
|
if (!Array.isArray(values)) throwTypeError("array", values);
|
|
return new Promise((resolve) => {
|
|
if (!this.#connection) throw new Error("Querying failed: No connection");
|
|
this.#connection.query(queryString, values, (err, result) => {
|
|
if (err) throw err;
|
|
resolve(result);
|
|
})
|
|
})
|
|
}
|
|
|
|
/**
|
|
* Returns a list of database names the user has access to
|
|
* @param {Boolean} excludeSchema - Whether to exclude the default database 'information_schema'
|
|
* @returns {Array<String>}
|
|
*/
|
|
async getDatabases (excludeSchema=false){
|
|
if (typeof excludeSchema !== "boolean") throwTypeError("boolean", excludeSchema);
|
|
let dbs = await this.queryRaw("SHOW DATABASES;");
|
|
if (excludeSchema) dbs = dbs.filter((db)=>db.Database!=="information_schema")
|
|
return dbs.map(db => db.Database);
|
|
}
|
|
|
|
/**
|
|
* Selects a default database for future queries
|
|
* @param {String} name - Name of the database
|
|
* @returns {this}
|
|
*/
|
|
selectDatabase(name){
|
|
if (!name) throw new Error("name must not be empty");
|
|
if (typeof name !== "string") throwTypeError("string", name);
|
|
this.#selectedDatabase = name;
|
|
return this;
|
|
}
|
|
|
|
/**
|
|
* Returns a list of tables for the selected database
|
|
* - 'multipleStatements' must be active for this to work
|
|
* @param {String} [database] - Database to select. Can be empty as long as a default database was set with 'selectDatabase'
|
|
* @returns {Array}
|
|
*/
|
|
async getTables(database){
|
|
if (!this.#multipleStatements) throw new Error("getTables: multipleStatements must be set to 'true' in instance options");
|
|
if (!this.#selectedDatabase && !database) throw new Error("getTables: No database selected");
|
|
const tables = (await this.queryRaw(`USE ${database||this.#selectedDatabase}; SHOW TABLES;`))[1];
|
|
return tables.map(table => table[`Tables_in_${database||this.#selectedDatabase}`]);
|
|
}
|
|
|
|
/**
|
|
* Prepares a new select query
|
|
* @param {String} from - Name of the table
|
|
* @param {...String} [columns] - Name of columns to select. Leave empty to select all
|
|
* @returns {Select}
|
|
*/
|
|
select(from, ...columns){
|
|
if (!from) throw new Error("Can't prepare select: No 'from' given");
|
|
if (typeof from !== "string") throwTypeError("string", from);
|
|
return new Select(this, this.#selectedDatabase, from, columns);
|
|
}
|
|
|
|
|
|
/**
|
|
* Prepares a new query to insert data
|
|
* @param {String} into - Name of the table to insert into
|
|
* @returns {Insert}
|
|
*/
|
|
insert(into){
|
|
if (!into) throw new Error("Can't prepare insert: No 'into' given");
|
|
if (typeof into !== "string") throwTypeError("string", into);
|
|
return new Insert(this, this.#selectedDatabase, into);
|
|
}
|
|
|
|
/**
|
|
* Prepares a new delete query
|
|
* @param {String} from - Name of the table to delete from
|
|
* @returns {Delete}
|
|
*/
|
|
delete(from){
|
|
if (!from) throw new Error("Can't prepare delete: No 'from' given");
|
|
if (typeof from !== "string") throwTypeError("string", from);
|
|
return new Delete(this, this.#selectedDatabase, from);
|
|
}
|
|
|
|
/**
|
|
* Prepares a new update query
|
|
* @param {String} table - Name of the table to update data in
|
|
* @returns {Update}
|
|
*/
|
|
update(table){
|
|
if (!table) throw new Error("Can't prepare update: No 'table' given");
|
|
if (typeof table !== "string") throwTypeError("string", table);
|
|
return new Update(this, this.#selectedDatabase, table);
|
|
}
|
|
|
|
/**
|
|
* Drops a whole database
|
|
* - Requires admin privileges
|
|
* @param {String} database - Name of the database to drop
|
|
* @returns {OkPacket}
|
|
*/
|
|
async dropDatabase (database){
|
|
if (!database) throw new Error(`Can't drop database: No database given`);
|
|
if (typeof database !== "string") throwTypeError("string", database);
|
|
return await this.queryRaw(`DROP DATABASE ${database};`);
|
|
}
|
|
|
|
/**
|
|
* Drops a whole table
|
|
* @param {String} table - Name of the table to drop
|
|
* @returns {OkPacket}
|
|
*/
|
|
async dropTable(table){
|
|
if (!table) throw new Error("Can't drop table: No table set");
|
|
if (typeof table !== "string") throwTypeError("string", table);
|
|
if (!this.#selectedDatabase) throw new Error(`Can't drop table '${table}': Database not set`);
|
|
return await this.queryRaw(`DROP TABLE ${this.#selectedDatabase}.${table}`);
|
|
}
|
|
|
|
/**
|
|
* Creates a new database
|
|
* - Requires admin privileges
|
|
* @param {String} name - Name of the database to create
|
|
* @returns {OkPacket}
|
|
*/
|
|
async createDatabase(name){
|
|
if (!name) throw new Error(`Can't create database: No name given`);
|
|
if (typeof name !== "string") throwTypeError("string", name);
|
|
return await this.queryRaw(`CREATE DATABASE ${name};`);
|
|
}
|
|
|
|
/**
|
|
* Prepares to create a new table
|
|
* @param {String} name - Name of the table to create
|
|
* @returns {CreateTable}
|
|
*/
|
|
createTable(name){
|
|
if (!name) throw new Error("Can't create table: No name given");
|
|
if (typeof name !== "string") throwTypeError("string", name);
|
|
return new CreateTable(this, this.#selectedDatabase, name);
|
|
}
|
|
|
|
/**
|
|
* Alters a table and updates to the new given structure.
|
|
*
|
|
* @param {String} name
|
|
* @returns {AlterTable}
|
|
*/
|
|
alterTable(name){
|
|
if (!name) throw new Error("Can't alter table: No name given");
|
|
if (typeof name !== "string") throwTypeError("string", name);
|
|
return new AlterTable(this, this.#selectedDatabase, name);
|
|
}
|
|
|
|
/**
|
|
* Prepares to create a new table-structure
|
|
* @returns {Structure}
|
|
*/
|
|
createStructure(){
|
|
return new Structure();
|
|
}
|
|
|
|
/**
|
|
* Returns the structure object of a table
|
|
* @param {String} table - Name of table to get structure of
|
|
* @param {String} [database] - Name of the underlying database
|
|
* @returns {Structure}
|
|
*/
|
|
async getStructure(table, database){
|
|
if (!table) throw new Error("Can't get structure: table not given");
|
|
if (typeof table !== "string") throwTypeError("string", table);
|
|
if (!this.#selectedDatabase && !database) throw new Error(`Can't get structure of table ${table}: Database not selected`);
|
|
return new Structure(await this.queryRaw(`DESCRIBE ${database||this.#selectedDatabase}.${table};`));
|
|
}
|
|
|
|
/**
|
|
* Checks the structure of a table
|
|
* @param {String} table - Name of the table
|
|
* @param {Structure} desiredStructure - Structure to check against
|
|
* @param {String} [database] - Name of the database. If omitted, uses default database
|
|
* @returns {CheckResult}
|
|
*/
|
|
async checkStructure(table, desiredStructure, database){
|
|
if (!table) throw new Error("Can't check structure: table not given");
|
|
if (typeof table !== "string") throwTypeError("string", table);
|
|
if (typeof desiredStructure !== "object") throwTypeError("object", desiredStructure);
|
|
if (!this.#selectedDatabase && !database) throw new Error(`Can't get structure of table ${table}: Database not selected`);
|
|
const dbStruc = (await this.getStructure(table, database||this.#selectedDatabase)).get(); // Get current structure -> Array<Objects>
|
|
const result = {
|
|
errors: [],
|
|
passed: []
|
|
}
|
|
for (let col of dbStruc){ // Check if current table has a field that is not defined
|
|
if (!desiredStructure.find(dsStruc => dsStruc.Field === col.Field)){
|
|
result.errors.push(`${col.Field}: Existing, but not defined`);
|
|
}
|
|
}
|
|
for (let col of desiredStructure){
|
|
const dbCol = dbStruc.find((dbCol) => col.Field === dbCol.Field); // Check if the current table has the field
|
|
if (!dbCol) {
|
|
result.errors.push(`${col.Field}: Missing completely`);
|
|
continue;
|
|
}
|
|
let breakOut = false;
|
|
for (let key in col){
|
|
if (col[key] !== dbCol[key]){
|
|
// If the current key has a different value
|
|
result.errors.push(`${dbCol.Field}.${key}: Required ${col[key]}, got ${dbCol[key]}`);
|
|
breakOut=true;
|
|
}
|
|
}
|
|
if (!breakOut){ // If no errors happened
|
|
result.passed.push(`${col.Field}`);
|
|
}
|
|
}
|
|
return result;
|
|
}
|
|
|
|
/**
|
|
* Returns total amount of rows of a table
|
|
* @param {String} table - Table name
|
|
* @returns {Number}
|
|
*/
|
|
async total(table){
|
|
if (!table) throw new Error("Can't get structure: table not given");
|
|
if (typeof table !== "string") throwTypeError("string", table);
|
|
if (!this.#selectedDatabase) throw new Error("Can't get total: No default table set");
|
|
return await new Select(this, this.#selectedDatabase, table).count(true).execute();
|
|
}
|
|
|
|
/**
|
|
* Returns if connection is established
|
|
* @returns {Boolean}
|
|
*/
|
|
isConnected(){
|
|
if (this.#connection) return true;
|
|
return false;
|
|
}
|
|
}
|
|
|
|
/**
|
|
* @typedef {Object} CheckResult
|
|
* @property {Array<String>} errors - String representation of errors found
|
|
* @property {Array<String>} passed - String representation of passed checks
|
|
*/
|
|
|
|
/**
|
|
* @typedef {Object} OkPacket
|
|
* @property {Number} fieldCount - Defaults to 0 on non-select queries
|
|
* @property {Number} affectedRows - The number of rows affected by the query
|
|
* @property {Number} insertId - The ID of the last inserted row if the table has an auto-increment column; otherwise always 0
|
|
* @property {Number} serverStatus - A status flag representing the current state of the mysql server
|
|
* @property {Number} warningCount - The number of warnings generated during query execution.
|
|
* @property {String} message - An optional message providin additional information about the query result (normally empty)
|
|
* @property {Boolean} protocol41 - Whether mysql protocol 4.1 or later is used
|
|
* @property {Number} changedRows - The number of rows actually changed by the query
|
|
*/
|
|
|
|
const awSQLInstance = new awSQL();
|
|
module.exports = {awSQL: awSQLInstance, Structure};
|
|
|
|
/**
|
|
* @exports awSQLInstance
|
|
*/ |