mysql-suspend.js

"use strict";

/**
 * @class
 * <p>For use with MySQL databases.</p>
 * <p>Created by <a href="http://www.github.com/phanxgames">Phanxgames</a></p>
 *
 * <h4>Requirements:</h4>
 * - ECMAScript 2015 (ES6)<br>
 * - Node.JS 6.2.2 or later (tested on 6.2.2)
 *
 * <h4>Installation:</h4>
 * <pre>
 *      npm install mysql-suspend
 * </pre>
 * <p>Note: the dictionaryjs, mysql and suspend modules should be automatically installed as dependencies, but you may need to install these as well.</p>
 *
 * Copy the dbConfig.ex.json file into your project source folder, rename to dbConfig.json,
 * and update with your database connection information.
 *
 *
 * <h4>Important:</h4>
 * <p>You may only use one connection with each instance of this class.
 * Close any opened connection before opening a new one.
 * You may reuse an instance with a new connection if you close the previous.</p>
 *
 * <h4>Asynchronous nature:</h4>
 * <p>All methods that include a callback (cb) have been designed to be used with the
 * suspend library and may be placed behind a yield command. Be sure to leave the cb
 * parameter null to use the suspend.resume functionality automatically.</p>
 *
 * <p>IMPORTANT: You will also need to set the resume reference in the constructor or the
 * {@link MysqlSuspend#setResume} method, before the suspend.resume functionality will
 * be enabled.</p>
 *
 * <p>If you do provide a callback, the 3rd parameter, "next" (ex: cb(err,result, next))
 * will be the suspend.resume function reference so you may resume execution
 * to move past the next yield command.</p>
 *
 * <h4>Example:</h4>
 * <pre>
 *   var suspend = require("suspend");
 *   var MysqlSuspend = require("mysql-suspend");
 *
 *   //Attach your dbConfig to the mysql-suspend module
 *   MysqlSuspend.config = require('./dbConfig.json');
 *
 *   var db = new MysqlSuspend(suspend.resume);
 *
 *   suspend(function*() {
 *      yield db.start();
 *
 *      //find all users' email with a username that contains "phanx"
 *      yield db.query("select email from users where username LIKE ? ;",["%phanx%"]);
 *
 *      if (db.error()) {
 *          console.error("Error:",db.error());
 *          return;
 *      }
 *
 *      console.log(db.rowCount + " rows found!");
 *
 *      for (let row of db.rows) {
 *          console.log(row);
 *          if (row.email == "test@test.com") {
 *              //..
 *          }
 *      }
 *
 *      //..place other below examples here..
 *
 *      yield db.end();
 *
 *   })();
 * </pre>
 */
class MysqlSuspend {

    /**
     * Pass in a unique config just for this instance by using require to get
     * the JSON contents.
     * <h4>Example:</h4>
     * <pre>
     *     //..
     *     var suspend = require("suspend");
     *
     *     var myconfig = require("./dbConfig2.json");
     *     var db = new MysqlSuspend(suspend.resume,myconfig);
     *     //..
     * </pre>
     * @param {Function} [resume=null] -
     *          set to suspend.resume from generator context
     *          to use suspend library with yields. See {@link setResume}.
     * @param {Object} [config=null] - Overwrite the global config for just this instance.
     */
    constructor(resume,config) {

        this.config = null;

        this.resume = resume;
        this.resume_next = null;

        this.start_stack = null;
        this.opened = null;

        this.client = null;     //connection
        this.pool = null;

        this.guid = null;

        this.result = null;
        this.resultCount = 0;
        this.last_error = null;

        this._throwErrors = true;

        this.setLocalConfig(config);

    }

    //##########################################################################
    //  Config Methods
    //##########################################################################

    /**
     * Disabling throwErrors will stop suspend from throwing errors on callbacks.
     * This is enabled by default.
     * @param {Boolean} value
     */
    set throwErrors(value) {
        this._throwErrors = value;
    }


    /**
     * @description Required to set this before you first initalize this class.<br>
     * This should be static across your entire project and only needs to be set once.<br>
     * Set to the Contents of dbConfig.json as Object.
     * <h4>Example:</h4>
     * <pre>
     *     var Mysql = require("mysql-suspend");
     *     Mysql.config = require("./dbConfig.json");
     *     //..
     * </pre>
     */
    static set config(config) {
        dbConfig.config = config;
    }

    /**
     *
     * @param {Object} config
     */
    setLocalConfig(config) {
        this.config = config;

        if (this.config!=null) {
            updateAutoCloseInterval(config.auto_closer_enabled,config.auto_closer_minutes);
        }
    }

    /**
     * Enables automatic calling of suspend.resume on all methods with
     * optional callbacks.  See Class comment at top of file.
     * @param {Function} resume
     *      set to suspend.resume, set to null to disable
     */
    setResume(resume) {
        this.resume = resume;
    }

    //##########################################################################
    //  Connection Methods
    //##########################################################################

    usePool() {
        if (dbConfig.config.usePool==true) return true;
        return false;
    }


    /**
     * Opens a database connection. Must be called before using any other method.
     * <p>Alias: open(cb)</p>
     * @param {MysqlSuspend~cbOnError} [cb=null] - Use callback or leave null to use suspend.resume.
     *                                 <br> Returns cb()
     */
    start(cb) {
        var self = this;
        self.initHandleCallback();



        //get the stack from the scope of method call
        self.start_stack = new Error().stack;

        try {

            if (self.usePool()) {

                if (self.pool==null)
                    self.pool = mysql.createPool(dbConfig.config.mysql);


                self.pool.getConnection(function(err, conn) {


                    if (err) {
                        console.error("Problem getting database connection from pool:\n"
                            + self.start_stack + "\n", err);
                        self.handleCallback(cb,err);
                        self.start_stack = null;
                        return;
                    }
                    self.guid = generateToken(6, dictTokens);
                    self.opened = getTimestamp();
                    self.client = conn;

                    openConnections.set(self.guid, self);


                    self.handleCallback(cb, null);

                });




            } else {

                if (self.opened!=null) {
                    var err = new Error("Database connection already open.");
                    console.error(err);
                    self.handleCallback(cb, err);
                    return;
                }

                let connection = mysql.createConnection(dbConfig.config.mysql);

                connection.connect(function(err) {
                    if (err) {
                        console.error("Problem getting database connection:\n" + self.start_stack + "\n", err);
                        self.handleCallback(cb,err);
                        self.start_stack = null;
                        return;
                    }
                    self.guid = generateToken(6, dictTokens);
                    self.opened = getTimestamp();
                    self.client = connection;

                    openConnections.set(self.guid, self);

                    self.handleCallback(cb, null);

                });
            }




        } catch (err) {
            console.error("Problem getting database connection:\n" + self.start_stack + "\n", err);
            self.handleCallback(cb, err);
            self.start_stack = null;
        }

    }

    /**
     * @ignore
     */
    open(cb) {
        this.start(cb);
    }

    /**
     * Closes the connection.
     * You must call this when you are done with this database connection.
     * If you do not end the connection the pool will be exhausted.
     * <p>Alias: close(cb)</p>
     * @param {MysqlSuspend~cbOnEmpty} [cb=null] - Use callback or leave null to use suspend.resume.
     *                            <br>Returns cb(err)
     */
    end(cb) {
        var self = this;
        self.initHandleCallback();

        if (self.opened!=null) {
            var elapsed = getTimeDiff(self.opened,"ms");
            console.log("Connection released after in use for " + elapsed + " ms.");
        }

        if (self.client == null) {
            self.handleCallback(cb, null);
            return;
        }

        if (self.usePool()) {

            self.client.release();

            self.handleCallback(cb, null);

        } else {

            self.client.end(function (err) {

                if (err) {
                    console.error("Error closing connection", err);
                }

                openConnections.remove(self.guid);

                self.client = null;
                self.opened = null;
                self.start_stack = null;
                self.result = null;

                self.handleCallback(cb, null);

                self.resume_next = null;
            });
        }



    }

    /**
     * @ignore
     */
    close(cb) {
        this.end(cb);
    }

    //##########################################################################
    //  Query Methods
    //##########################################################################

    /**
     * Executes SQL statement on database.
     * <p>Use question-marks (ie: ?) as unnamed parameters within the SQL statement.</p>
     * <h4>Example:</h4>
     * <pre>
     *     //..
     *     yield db.query("select username from users where email=?;",["test@test.com"]);
     *     for (let row of db.rows) {
     *         console.log(row.username);
     *     }
     *     //..
     * </pre>
     * @param {string} sql - sql statement to execute
     * @param {Array} [paras=null] - array of parameters, replacing "?" in SQL
     * @param {MysqlSuspend~cbOnQuery} [cb=null] - Use callback or leave null to use suspend.resume.
     *              <br>Returns cb(err,results) where result is an array of rows.
     */
    query(sql, paras, cb) {
        var self = this;
        self.initHandleCallback();

        self.resultCount = 0;

        //Check if database connection is open.
        if (self.client==null) {
            var err = new Error("Database Connection is not open.");
            console.error(err);
            self.handleCallback(cb,err);
            return;
        }

        //get the stack from the scope of method call
        var stack = new Error().stack;

        //Replaces question marks within SQL statement with numbered parameters flags
        //sql = replaceQMarks(sql);

        //TODO implement parametrized queries

        //Start timer to collect query execution time
        var querystart = timeStart();

        //execute the query on the mysql client
        self.client.query(sql,paras,function(err,result) {
            //calculate execution time
            var elapsed = timeEnd(querystart);

            //check if there is a problem with the result
            if (err || result==null ) {
                    var errObj = {
                        stack: stack,
                        sql: sql,
                        paras: paras
                    };

                    if (err!=null && err.hasOwnProperty("message"))
                        errObj.message = err.message;
                    else
                        errObj.message = "Unspecified Database Query Error."

                    console.error("Database Error (" + elapsed + "s): ", errObj);
                    self.handleCallback(cb,errObj);
                    return;
            }

            console.log("Query completed in " + elapsed +" seconds.");

            if (Array.isArray(result))
                self.result = result;
            else {
                self.result = [result];
            }

            self.result = result;
            self.resultCount = result.length;
            self.handleCallback(cb,null,self.result);

            stack = null;

        });

    }


    /**
     * Merge allows you to insert or update a record by proving insert
     * and update sql statements.
     * <p>Try using instead the {@link MysqlSuspend#mergeHelper} to generate the
     * two sql statements.</p>
     * @param {String} sqlInsert - sql statement with unnamed parameters (ie: ?)
     * @param {Array} [parasInsert=null] - array of parameters to replace ? in sql
     * @param {String} sqlUpdate - sql statement with unnamed parameters (ie: ?)
     * @param {Array} [parasUpdate=null] - array of parameters to replace ? in sql
     * @param {MysqlSuspend~cbOnMerge} [cb=null] - Use callback or leave null to use suspend.resume.
     *              <br>Returns cb(err,result) where result will be either
     *              "insert" or "update" depending on which action was used.
     */
    merge(sqlInsert,parasInsert,sqlUpdate,parasUpdate,cb) {
        var self = this;
        self.initHandleCallback();

        self.resultCount = 0;

        if (self.client==null) {
            var err = new Error("Database connect is not open.");
            console.error(err);
            self.handleCallback(cb,err);
            return;
        }

        var stack = new Error().stack;

        //sqlInsert = replaceQMarks(sqlInsert);
        //sqlUpdate = replaceQMarks(sqlUpdate);

        var querystart = process.hrtime();

        var loopCount = 0;
        function doLoop() {
            loopCount++;
            if (loopCount > 10) {
                var errObj = {
                    message: "Database Merge exceeded iteration limit",
                    stack: stack,
                    sqlInsert: sqlInsert,
                    sqlUpdate: sqlUpdate,
                    parasInsert: parasInsert,
                    parasUpdate: parasUpdate
                };

                var elapsed = timeEnd(querystart);

                console.error("Database Error ("+ elapsed + " s): ", errObj);

                self.handleCallback(cb,errObj);

                return;
            }

            //attempt to update the record
            self.client.query(sqlUpdate,parasUpdate,function(err,result) {
                var elapsed = timeEnd(querystart);

                if (err) {
                    var errObj = {
                        stack: stack,
                        sql: sqlUpdate,
                        paras: parasUpdate
                    };
                    if (err!=null && err.hasOwnProperty("message"))
                        errObj.message = err.message;
                    else
                        errObj.message = "Unspecified Merge:Update error.";

                    console.error("Database Error (" + elapsed + " s): ",errObj);
                    self.handleCallback(cb,errObj);

                    return;
                }

                //check if any rows were updated/affected
                if (result!=null && result.hasOwnProperty("rowCount") &&
                    result.rowCount > 0) {

                    self.resultCount = result.rowCount;

                    //Merge completed by successfully updating!
                    console.log("Query completed in " + elapsed + " seconds.");

                    self.handleCallback(cb,null,"update");

                    stack = null;

                    return;
                }

                self.client.query(sqlInsert,parasInsert,function(err,result) {
                    var elapsed = timeEnd(querystart);
                    if (err) {
                        //error with inserting? let's try that one again
                        doLoop();
                        return;
                    }

                    if (result!=null && result.hasOwnProperty("rowCount") &&
                        result.rowCount > 0) {

                        console.log("Query completed in " + elapsed + " seconds.");

                        self.resultCount = result.rowCount;

                        self.handleCallback(cb,null,"insert");

                        stack = null;

                        return;
                    }

                    //we shouldn't get to this point, so... Let's loop again!
                    doLoop();

                });
                
            });

        }
        doLoop();
    }

    //##########################################################################
    //  Select Methods
    //##########################################################################

    /**
     * Executes SQL statement on database and returns the first row.
     * <p>Use question-marks (ie: ?) as unnamed parameters within the SQL statement.</p>
     * <h4>Example:</h4>
     * <pre>
     *     //..
     *     let row = yield db.selectRow("select username from users where email=?;",["test@test.com"]);
     *     //..
     * </pre>
     * @param {string} sql - sql statement to execute
     * @param {Array} [paras=null] - array of parameters, replacing "?" in SQL
     * @param {MysqlSuspend~cbOnQuery} [cb=null] - Use callback or leave null to use suspend.resume.
     *              <br>Returns cb(err,results) where result is an array of rows.
     */
    selectRow(sql, paras, cb) {
        this.initHandleCallback();

        this.query(sql, paras, (err,result) => {
            if (err || result==null || result.length==0) {
                this.handleCallback(cb,err,result);
                return;
            }
            this.handleCallback(cb,null,result[0]);
        });
    }


    /**
     * Executes SQL statement on database and returns the array of rows.
     * <p>Use question-marks (ie: ?) as unnamed parameters within the SQL statement.</p>
     * <h4>Example:</h4>
     * <pre>
     *     //..
     *     let arr = yield db.selectArray("select username from users where email=?;",["test@test.com"]);
     *     //..
     * </pre>
     * @param {string} sql - sql statement to execute
     * @param {Array} [paras=null] - array of parameters, replacing "?" in SQL
     * @param {MysqlSuspend~cbOnQuery} [cb=null] - Use callback or leave null to use suspend.resume.
     *              <br>Returns cb(err,results) where result is an array of rows.
     */
    selectArray(sql, paras, cb) {
        this.query(sql, paras, cb);
    }



    //##########################################################################
    //  Transaction Methods
    //##########################################################################

    /**
     * Begins a transaction.
     * @param {MysqlSuspend~cbOnError} [cb=null] - Use callback or leave null to use suspend.resume.
     *              <br>Returns cb(err)
     */
    begin(cb) {
        this.query("START TRANSACTION;",null,cb);
    }

    /**
     * Commits the transaction.
     * @param {MysqlSuspend~cbOnError} [cb=null] - Use callback or leave null to use suspend.resume.
     *              <br>Returns cb(err)
     */
    commit(cb) {
        this.query("COMMIT;",null,cb);
    }

    /**
     * Rolls back the transaction.
     * @param {MysqlSuspend~cbOnError} [cb=null] - Use callback or leave null to use suspend.resume.
     *              <br>Returns cb(err)
     */
    rollback(cb) {
        this.query("ROLLBACK;",null,cb);
    };

    //##########################################################################
    //  Helper Methods
    //##########################################################################

    /**
     * Select Statement Helper.
     * <h4>Example:</h4>
     * Select emails from users table that have a username that starts with the letter "h":
     * <pre>
     *     //..
     *     yield db.selectHelper({
     *          table:"users",
     *          columns:["username","email"],
     *          where: db.whereHelper({
     *              "username -like":"h%"
     *          }),
     *          orderBy: db.orderByHelper({
     *              "email":"ASC"
     *          })
     *     });
     *
     *     for (let row of db.rows) {
     *          console.log(row);
     *          //Output example: {username:"Tester",email:"test@test.com"}
     *     }
     *
     *     //..
     * </pre>
     * @param {Object} options - Required. See properties:
     * @param {String} options.table - Table name
     * @param {Array} options.columns - Array of column names to select.
     * @param {String} [options.where=null] - where clause sql statement segment (ie: name=? OR id=? )
     *                      <br>You may also use {@link MysqlSuspend#whereHelper}
     * @param {Array} [options.whereParas=null] - Array of values to replace parameters in where SQL
     *                      <br>Not needed if you use whereHelper.
     * @param {String} [options.orderBy=null] - order clause sql statement segment (ie: name ASC)
     *                      <br>You may also use {@link MysqlSuspend#orderByHelper}
     * @param {int} [options.limit=null] - Number of records to return, or null for infinite.
     * @param {int} [options.start=0] - Start row index position.
     * @param {MysqlSuspend~cbOnQuery} [cb=null] - Use callback or leave null to use suspend.resume.
     *              <br>Returns cb(err,results) where result is an array of rows.
     */
    selectHelper(options,cb) {

        if (options==null) {
            let err = new Error("SelectHelper: Options parameter is required.");

            if (this._throwErrors)
                throw err;
            else
                console.error(err);

            return;
        }

        var table = options.table || null;
        var columns = options.columns || null;
        var where = options.where || null;
        var whereParas = options.whereParas || null;
        var orderBy = options.orderBy || null;
        var limit = options.limit || null;
        var start = options.start || 0;

        if (table == null) {
            let err = new Error("SelectHelper: Table option is required.");

            if (this._throwErrors)
                throw err;
            else
                console.error(err);

            return;
        }

        //table,columns,where,whereParas,orderBy

        var sql = "SELECT ";
        var finalParas = null;

        if (Array.isArray(columns))
            sql += columns.join(",");
        else if (columns!=null)
            sql += columns;
        else
            sql += " * ";

        sql += " FROM " + table;
        if (where!=null) {

            if (isObject(where) &&
                where.hasOwnProperty("sql") &&
                where.hasOwnProperty("paras")
            ) {

                sql += " WHERE " + where.sql;
                finalParas = where.paras;

            } else {
                where = where.trim();
                var lowerWhere =  where.toLowerCase();
                if (lowerWhere.substr(0,5)=="where")
                    where.substr(5);

                sql += " WHERE " + where;
                finalParas = whereParas;
            }

        }
        if (orderBy!=null) {
            orderBy = orderBy.trim();
            var lowerOrderBy = orderBy.toLowerCase();
            if (lowerOrderBy.substr(0,8)=="order by")
                orderBy.substr(8);

            sql += " ORDER BY " + orderBy;
        }

        if (limit!=null) {
            sql += " LIMIT " + limit + " OFFSET " + start ;
        }

        sql += " ;";

        if (finalParas==null || finalParas.length==0) finalParas = null;

        this.query(sql,finalParas,cb);

    }

    /**
     * Insert Statement Helper
     * <h4>Example:</h4>
     * Insert new user into users table.
     * <pre>
     *     //..
     *     yield db.insertHelper({
     *          table:"users",
     *          columns:{
     *              "username":"tester",
     *              "email":"oldemail@test.com"
     *          }
     *     });
     *     //..
     * </pre>
     * @param {Object} options - Required. See properties:
     * @param {String} options.table - Table name
     * @param {Array} [options.columns=null] - Leave null to use table order of columns.
     *                  <br>Array of column names to update, use with options.values property to specifiy values.
     * @param {Object} [options.columns=null] - Or Object where keys are column names and values are values.
     *                   Note: options.values property not needed.
     * @param {Array} [options.values=null] - Array of values used with options.columns (Array) property.
     * @param {MysqlSuspend~cbOnError} [cb=null] - Use callback or leave null to use suspend.resume.
     *                  <br>Returns cb(err)
     */
    insertHelper(options,cb) {

        if (options==null) {

            let err = new Error("InsertHelper: Options parameter required.");
            if (this._throwErrors)
                throw err;
            else
                console.error(err);

            return;
        }

        var table = options.table || null;
        var columns = options.columns || null;
        var values = options.values || null;

        if (table == null) {

            let err = new Error("InsertHelper: Table option is required.");
            if (this._throwErrors)
                throw err;
            else
                console.error(err);

            return;
        }

        var sql = "INSERT INTO " + table;


        let tempColumns;
        let tempValues;

        if (columns != null) {

            tempColumns = null;
            tempValues = null;

            if (isObject(columns)) {

                tempColumns = [];
                tempValues = [];

                for (let key in columns) {
                    if (columns.hasOwnProperty(key)) {
                        tempColumns.push(key);
                        tempValues.push(columns[key]);
                    }
                }

            } else {

                //split if columns is a string
                if (!Array.isArray(columns))
                    tempColumns = columns.split(",");
                else
                    tempColumns = columns;

                tempValues = values;
            }

            if (tempValues==null || tempColumns==null ||
                tempColumns.length != tempValues.length) {

                    let err = new Error("InsertHelper: Number of Columns and Values do not match.");
                    if (this._throwErrors)
                        throw err;
                    else
                        console.error(err);

                    return;
            }
            sql += "(" + tempColumns.join(",") + ") VALUES ";

        } else {
            tempValues = values;
        }

        sql += " (";

        for (let i=0; i<tempValues.length; i++) {
            sql += "?,";
        }
        sql = removeLastChara(sql) + ");";


        this.query(sql,tempValues,cb);

    }

    /**
     * Update Statement Helper
     * <h4>Example:</h4>
     * Updates user's email by username.
     * <pre>
     *     //..
     *     yield db.updateHelper({
     *          table: "users",
     *          columns: {
     *              "email":"newemail@test.com"
     *          },
     *          where: db.whereHelper({
     *              "username":"tester"
     *          })
     *     });
     *     //..
     * </pre>
     * @param {Object} options - Required. See properties:
     * @param {String} options.table - Table name
     * @param {Array} options.columns - Array of column names to update, use with options.values property to specifiy values.
     * @param {Object} options.columns - Or Object where keys are column names and values are values.
     *                   Note: options.values property not needed.
     * @param {Array} [options.values=null] - Array of values used with options.columns (Array) property.
     * @param {String} [options.where=null] - where clause sql statement segment (ie: name=? OR id=? )
     *                      <br>You may also use {@link MysqlSuspend#whereHelper}
     * @param {Array} [options.whereParas=null] - Array of values to replace parameters in where SQL
     *                      <br>Not needed if you use whereHelper.
     * @param {MysqlSuspend~cbOnError} [cb=null] - Use callback or leave null to use suspend.resume.
     *                  <br>Returns cb(err)
     */
    updateHelper(options,cb) {
        var self = this;

        if (options==null) {
            let err = new Error("UpdateHelper: Options parameter required.");
            if (this._throwErrors)
                throw err;
            else
                console.error(err);

            return;
        }


        var table = options.table;
        var columns = options.columns;
        var values = options.values;
        var where = options.where;
        var whereParas = options.whereParas;

        if (table == null) {
            let err = new Error("UpdateHelper: Table option is required.");
            if (this._throwErrors)
                throw err;
            else
                console.error(err);

            return;
        }


        var sql = "UPDATE " + table + " SET ";
        var finalParas = null;

        let tempColumns;
        let tempValues;

        if (isObject(columns)) {

            tempColumns = [];
            tempValues = [];

            for (let key in columns) {
                if (columns.hasOwnProperty(key)) {
                    tempColumns.push(key);
                    tempValues.push(columns[key]);
                }
            }

        } else {

            //split if columns is a string
            if (!Array.isArray(columns))
                tempColumns = columns.split(",");
            else
                tempColumns = columns;

            tempValues = values;
        }

        if (tempValues==null || tempColumns==null ||
            tempColumns.length != tempValues.length) {
            let err = new Error("UpdateHelper: Number of Columns and Values do not match.");
            if (this._throwErrors)
                throw err;
            else
                console.error(err);

            return;
        }

        for (let column of tempColumns) {
            sql += column+"=?,";
        }
        sql = removeLastChara(sql);
        finalParas = tempValues;

        if (finalParas==null) finalParas = [];



        if (where!=null) {

            if (isObject(where) &&
                where.hasOwnProperty("sql") &&
                where.hasOwnProperty("paras")
            ) {

                sql += " WHERE " + where.sql;
                Array.prototype.push.apply(finalParas,where.paras);

            } else {
                where = where.trim();
                var lowerWhere =  where.toLowerCase();
                if (lowerWhere.substr(0,5)=="where")
                    where.substr(5);

                sql += " WHERE " + where;
                Array.prototype.push.apply(finalParas,whereParas);

            }

        }

        sql += " ;";

        this.query(sql,finalParas,cb);

    }

    /**
     * Delete Statement Helper
     * <h4>Example:</h4>
     * Deletes user record with the username "tester".
     * <pre>
     *     //..
     *     yield db.deleteHelper({
     *          table: "users",
     *          where: db.whereHelper({
     *              "username":"tester"
     *          })
     *     });
     *     //..
     * </pre>
     * @param {Object} options - Required. See properties:
     * @param {String} options.table - Table name
     * @param {String} [options.where=null] - where clause sql statement segment (ie: name=? OR id=? )
     *                      <br>You may also use {@link MysqlSuspend#whereHelper}
     * @param {Array} [options.whereParas=null] - Array of values to replace parameters in where SQL
     *                      <br>Not needed if you use whereHelper.
     * @param {int} [options.limit=null] - Number of records to delete, or null for infinite.
     * @param {MysqlSuspend~cbOnError} [cb=null] - Use callback or leave null to use suspend.resume.
     *                  <br>Returns cb(err)
     */
    deleteHelper(options,cb) {
        var self = this;

        if (options==null) {

            let err = new Error("DeleteHelper: Options parameter required.");
            if (this._throwErrors)
                throw err;
            else
                console.error(err);

            return;
        }

        var table = options.table;
        var where = options.where;
        var whereParas = options.whereParas;
        var limit = options.limit;

        if (table == null) {
            let err = new Error("DeleteHelper: Table option is required.");
            if (this._throwErrors)
                throw err;
            else
                console.error(err);

            return;
        }

        var sql = "DELETE FROM " + table + " ";
        var finalParas = null;

        if (where!=null) {

            if (isObject(where) &&
                where.hasOwnProperty("sql") &&
                where.hasOwnProperty("paras")
            ) {

                sql += " WHERE " + where.sql;
                finalParas = where.paras;

            } else {
                where = where.trim();
                var lowerWhere =  where.toLowerCase();
                if (lowerWhere.substr(0,5)=="where")
                    where.substr(5);

                sql += " WHERE " + where;
                finalParas = whereParas;

            }

        }

        if (limit!=null) {
            sql += " LIMIT " + limit;
        }

        sql += " ;";

        this.query(sql,finalParas,cb);
    }

    /**
     * Helper function of Merge method.
     * <p>Merge is a smart method that uses insert or update internally.
     * See {@link MysqlSuspend#merge} for more information.</p>
     * <h4>Example:</h4>
     * Inserts a new user in the users table or updates their email if already found.
     * <pre>
     *     //..
     *     yield db.mergeHelper({
     *          table: "users",
     *          columns: {
     *              "username":"tester",
     *              "email":"test@test.com"
     *          },
     *          where: db.whereHelper({
     *              "username":"tester"
     *          })
     *     });
     *     //..
     * </pre>
     * @param {Object} options - Required. See properties:
     * @param {String} options.table - Table name
     * @param {Array} options.columns - Array of column names to update, use with options.values property to specifiy values.
     * @param {Object} options.columns - Or Object where keys are column names and values are values.
     *                   Note: options.values property not needed.
     * @param {Array} [options.values=null] - Array of values used with options.columns (Array) property.
     * @param {String} [options.where=null] - where clause sql statement segment (ie: name=? OR id=? )
     *                      <br>You may also use {@link MysqlSuspend#whereHelper}
     * @param {Array} [options.whereParas=null] - Array of values to replace parameters in where SQL
     *                      <br>Not needed if you use whereHelper.
     * @param {MysqlSuspend~cbOnMerge} [cb=null] - Use callback or leave null to use suspend.resume.
     *                  <br>Returns cb(err,result) where result is either "update" or "insert" depending on which operation was needed.
     */
    mergeHelper(options,cb) {
        var self = this;

        if (options==null) {
            let err = new Error("MergeHelper: Options parameter required.");

            if (this._throwErrors)
                throw err;
            else
                console.error(err);

            return;
        }

        var table = options.table;
        var columns = options.columns;
        var values = options.values;
        var where = options.where;
        var whereParas = options.whereParas;

        if (table == null) {
            let err = new Error("MergeHelper: Table option is required.");

            if (this._throwErrors)
                throw err;
            else
                console.error(err);

            return;
        }


        var sqlInsert = "insert into " + table + " (";
        var sqlUpdate = "update " + table + " set ";
        var parasInsert = [];
        var parasUpdate = [];


        let tempColumns;
        let tempValues;

        if (isObject(columns)) {

            tempColumns = [];
            tempValues = [];

            for (let key in columns) {
                if (columns.hasOwnProperty(key)) {
                    tempColumns.push(key);
                    tempValues.push(columns[key]);
                }
            }

        } else {

            //split if columns is a string
            if (!Array.isArray(columns))
                tempColumns = columns.split(",");
            else
                tempColumns = columns;

            tempValues = values;
        }

        if (tempValues==null || tempColumns==null ||
            tempColumns.length != tempValues.length) {
            let err= new Error("MergeHelper: Number of Columns and Values do not match.");

            if (this._throwErrors)
                throw err;
            else
                console.error(err);

            return;
        }

        for (let column of tempColumns) {
            sqlInsert += column + ",";
            sqlUpdate += column + "=?,";
        }

        sqlInsert = removeLastChara(sqlInsert);
        sqlUpdate = removeLastChara(sqlUpdate);


        //wrapping up the end of the INSERT satement
        sqlInsert += ") VALUES (";
        for (let i=0; i<tempValues.length; i++) {
            sqlInsert += "?,";
        }

        sqlInsert = removeLastChara(sqlInsert);
        sqlInsert += ") ;";

        Array.prototype.push.apply(parasInsert,tempValues);
        Array.prototype.push.apply(parasUpdate,tempValues);


        if (where!=null) {

            if (isObject(where) &&
                where.hasOwnProperty("sql") &&
                where.hasOwnProperty("paras")
            ) {

                sqlUpdate += " WHERE " + where.sql;
                Array.prototype.push.apply(parasUpdate,where.paras);

            } else {
                where = where.trim();
                var lowerWhere =  where.toLowerCase();
                if (lowerWhere.substr(0,5)=="where")
                    where.substr(5);

                sqlUpdate += " WHERE " + where;
                Array.prototype.push.apply(parasUpdate,updateWhereParas);

            }

        }

        self.merge(sqlInsert,parasInsert,sqlUpdate,parasUpdate,cb);

    }


    /**
     * Builds the where option for use with "where" option in other helpers.
     * See {@link MysqlSuspend#selectHelper}, {@link MysqlSuspend#deleteHelper},
     * {@link MysqlSuspend#updateHelper}, {@link MysqlSuspend#mergeHelper}.
     * <h4>Example:</h4>
     * <pre>
     *     //..
     *     db.whereHelper({
     *       "name -like":"%Smith",
     *       "banned":false
     *     });
     *     //..
     * </pre>
     * @param {Object} variables - Set keys as column names and values as desired value.
     *              <br>Special flags may be appended to key string:
     *              <br> -like  : uses the LIKE comparator
     *              <br> -notlike : uses the NOT LIKE comparator
     *              <br> -not : uses the != comparator
     *              <br> <i>default</i> : uses the = comparator
     * @param {String} [defaultLogic="AND"] - logic seperator (ie: OR)
     * @returns object for use with the "where" option in other helpers.
     */
    whereHelper(variables,defaultLogic) {

        if (variables==null || !isObject(variables))
            return;

        if (defaultLogic==null) defaultLogic = " AND ";

        defaultLogic = defaultLogic.trim();

        var out = {
            sql: null,
            paras: []
        }

        var tempSQL = [];

        for (let key in variables) {

            if (variables.hasOwnProperty(key) && variables[key]!=null) {

                var eqOperator = "=";

                var defaultValueInArrayOperator = " OR ";

                //split on space outside of doublequotes
                var nameParts = key.match(/(?:[^\s"]+|"[^"]*")+/g);
                var name = nameParts[0];

                for (let part of nameParts) {
                    part = part.trim().toLowerCase();
                    if (part.substr(0,2)=="--") part = part.substr(2);
                    if (part.substr(0,1)=="-") part = part.substr(1);
                    switch (part) {
                        case "like":
                            eqOperator = " LIKE ";
                            break;
                        case "notlike":
                            eqOperator = " NOT LIKE ";
                            defaultValueInArrayOperator = " AND ";
                            break;
                        case "not":
                            eqOperator = " != ";
                            defaultValueInArrayOperator = " AND ";
                            break;
                    }
                }


                var thisSQL = name + eqOperator + "?";


                var value = variables[key];
                if (Array.isArray(value)) {

                    let arrSQL = [];

                    for (let arrValue of value) {
                        arrSQL.push(thisSQL);
                        out.paras.push(arrValue);
                    }
                    tempSQL.push("(" + arrSQL.join(defaultValueInArrayOperator) + ")");

                } else {
                    tempSQL.push(thisSQL);
                    out.paras.push(value);
                }
            }
        }

        if (tempSQL.length>0) {
            out.sql = tempSQL.join(" "+defaultLogic+" ");
        }

        if (out.sql==null) out = null;

        return out;


    }

    /**
     * Returns a string to be used in the "orderBy" field in other helpers.
     * See {@link MysqlSuspend#selectHelper}.
     * <p>Sort options are either "ASC" or "DESC".</p>
     * @param {Object} options - Required. See properties:
     * @param {Array} options.columns - Array of column names, or
     *              <br>Array of Objects: [{name:"column name",sort:"ASC"}, ..], or
     *              <br>Array of Arrays: [["column name","ASC"], ..]
     * @param {String} [options.defaultSort="ASC"] - Used if sort is not specified.
     * @returns string of sql (the order by clause)
     */
    orderByHelper(options) {
        if (options!=null && isObject(options)) {

            var defaultSort = options.defaultSort || "ASC";
            var columns = options.columns || [];

            if (Array.isArray(columns)) {

                var sql = "";

                for (let column of columns) {

                    let name = null;
                    let sort = null;

                    if (isObject(column)) {

                        if (column.name) {
                            name = column.name;
                            sort = column.sort || column.sortBy || null;
                        }

                    } else if (Array.isArray(column)) {
                        if (column.length>=1)
                            name = column[0];

                        if (column.length>=2)
                            sort = column[1];

                    } else {
                        name = column;
                    }


                    if (name) {
                        sql += name + " ";

                        if (sort==null)
                            sql += defaultSort;
                        else {

                            if (sort === true)
                                sql += "ASC";
                            else if (sort === false)
                                sql += "DESC";
                            else
                                sql += sort;

                        }


                        sql += ",";
                    }


                }

                sql = removeLastChara(sql);

                console.log(sql);
                return sql;

            } else
                return columns;

        } else
            return options;

    }

    //##########################################################################
    //  Result Methods
    //##########################################################################

    /**
     * Returns an error object from the last executed command on this db conn.
     * @returns Error object or null if no error
     */
    error() {
        return this.last_error;
    };

    /**
     * @description  Returns an array of objects as rows from last query
     */
    get rows() {
        return this.result;
    }

    /**
     * @description Returns number of rows last affected by last query
     */
    get rowCount() {
        return this.resultCount;
    }


    /**
     * Loops over the rows from the last query. Non-blocking.
     * <h4>Example:</h4>
     * <pre>
     *   //..
     *   yield db.asyncForEach((index,item,next) => {
     *      console.log(index + ") ",item);
     *      //..
     *      next();
     *   });
     *  //..
     * </pre>
     * @param {MysqlSuspend~cbAsyncForEachIterator} cbIterator - Returns cb(index,item,cbNext).
     *              <br>Must call cbNext() to continue.
     * @param {MysqlSuspend~cbAsyncForEachFinal} [cbFinal=null] - Returns cb().
     * @param {Boolean} [enableCallback=true] - Set to false to disable default
     *                callback handling (used to disable suspend.resume on loop completion).
     */
    asyncForEach(cbIterator, cbFinal, enableCallback) {
        if (enableCallback==null) enableCallback = true;
        var self = this;
        if (enableCallback) self.initHandleCallback();

        var data = this.result;
        var counter = 0;
        var len = data.length;

        var next = function () {
            if (counter < len && data != null) {
                process.nextTick(step);
            } else {
                if (enableCallback)  self.handleCallback(cbFinal);
                return;
            }
        };
        var step = function () {
            if (counter < len && data != null) {
                var key = counter++;
                if (cbIterator(key, data[key], next) == false) {
                    if (enableCallback) self.handleCallback(cbFinal);
                    return;
                }

            } else {
                if (enableCallback) self.handleCallback(cbFinal);
                return;
            }
        };
        step();
    };


    //##########################################################################
    //  Callback Handlers
    //##########################################################################

    /**
     * Internal method. You should not call this directly.
     * ----------------
     * Prepares the handle callback system for suspend.resume usage.
     * @ignore
     */
    initHandleCallback() {
        if (this.resume)
            this.resume_next = this.resume();
        else
            this.resume_next = null;
    };
    /**
     * Internal method. You should not call this directly.
     * ----------------
     * Calls back the callback or if callback is null, executes
     * the suspend.resume to move forward.
     * @ignore
     */
    handleCallback(cb, err, result) {
        if (err) {
            this.last_error = err;
        } else {
            this.last_error = null;
        }
        if (cb)
            cb(err, result, this.resume_next);
        else if (this.resume_next) {

            if (this._throwErrors)
                this.resume_next(err, result);
            else
                this.resume_next(null, result);
        }
    }
};
module.exports = MysqlSuspend;

//##########################################################################
//  Private: Util Methods
//##########################################################################

function replaceQMarks(sql) {
    var insideQuote = false;
    var parameterIndex = 1;
    var currentIndex = 0;
    var rv = [];
    for (var i=0, len = sql.length; i < len; i++) {
        var c = sql[i];
        if (insideQuote) {
            if (c == "'") insideQuote = false;
        } else {
            if (c == '?') {
                rv.push(sql.substring(currentIndex, i));
                rv.push('$' + parameterIndex);
                parameterIndex++;
                currentIndex = i + 1;
            } else if (c=="'") insideQuote = true;
        }
    }
    rv.push(sql.substring(currentIndex));
    return rv.join('');
}
function getTimestamp() {
    var now = new Date();
    return formatDateTime(now);
}
function formatDateTime(str) {
    var input = new Date(str);
    return ""+(input.getFullYear())+"-"+pad(input.getMonth()+1,2,'0')+"-"+pad(input.getDate(),2,'0')+" "+pad(input.getHours(),2,'0')+":"+pad(input.getMinutes(),2,'0')+":"+pad(input.getSeconds(),2,'0');
}
function generateToken(length,dict) {
    var found = false;
    var token = "";
    while (!found) {
        token = generateRandomString(length);
        if (dict==null || (dict!=null && !dict.has(token))) {
            found = true;
        }
    }
    return token;
}
function generateRandomString(L){
    var s= '';
    var randomchar=function(){
        var n= Math.floor(Math.random()*62);
        if(n<10) return n; //1-10
        if(n<36) return String.fromCharCode(n+55); //A-Z
        return String.fromCharCode(n+61); //a-z
    }
    while(s.length< L) s+= randomchar();
    return s;
}
function pad(n, width, z) {
    z = z || '0';
    n = n + '';
    return n.length >= width ? n : new Array(width - n.length + 1).join(z) + n;
}

function timeStart() {
    return process.hrtime();
}
function timeEnd(start) {
    return (process.hrtime(start)[1] / 1000000000).toFixed(5);
}

function removeLastChara(str) {
    return str.substr(0,str.length-1);
}

function replaceAll(str,strFind, strWith) {
    var reg = new RegExp(strFind, 'ig');
    return str.replace(reg, strWith);
}
function isObject(val) {
    if (Array.isArray(val)) return false;
    if (val === null) { return false;}
    return ( (typeof val === 'function') || (typeof val === 'object') );
}


function getTimeDiff(ts,unit) {
    var now = getTimestamp();
    if (ts==null || now==null || ts=="" || now=="") return 0;
    var lastDate = new Date(ts);
    var nowDate = new Date(now);

    var diff = (nowDate-lastDate);

    switch (unit) {
        case "milliseconds":
        case "millisecond":
        case "ms":
            return diff;
            break;
        case "sec":
        case "s":
        case "second":
        case "seconds":
            return diff/1000;
            break;
        case "min":
        case "m":
        case "minutes":
        case "minute":
            return diff/1000/60;
            break;
        case "hour":
        case "h":
        case "hours":
            return diff/1000/60/60;
            break;
        case "days":
        case "day":
        case "d":
            return diff/1000/60/60/24;
            break;
    }

    return 0;
}

//##########################################################################
//  Imports
//##########################################################################

var Dictionary = require('dictionaryjs');
var mysql = require('mysql');
var suspend = require("suspend");

//global variable
var dbConfig = {config:null};
var dictTokens = new Dictionary();
var openConnections = new Dictionary();

var auto_closer_enabled = false;
var auto_closer_minutes = 3;
var auto_closer_interval;

//##########################################################################
// Auto Close
//##########################################################################

function updateAutoCloseInterval(enabled,minutes) {

    auto_closer_enabled = enabled;
    auto_closer_minutes = minutes;

    if (auto_closer_interval!=null)
        clearInterval(auto_closer_interval);

    if (enabled) {
        auto_closer_interval = setInterval(function() {

            let outlog = "";
            let counter = 0;

            openConnections.asyncForEach(function(guid,db,cbNext) {

                counter++;
                if (db.opened!=null) {
                    let minutes = getTimeDiff(db.opened,"min");
                    if (minutes > auto_closer_minutes) {
                        outlog += "\n[" + db.guid + "] Db Opened : " + minutes +
                            " minutes\n" + db.start_stack+"\n";

                        //auto closer
                        db.end();
                    }
                }
                cbNext();

            },function() {
                if (outlog!="") {
                    console.log("----------------------------------------\n" +
                        "**** " + counter + " Database Connections Open ****"+outlog +
                        "\n----------------------------------------");

                } else {
                    console.log("All database connections are closed ("+counter+").");
                }
            });

        },10000);
    }


}


//##########################################################################
// Type Definitions
//##########################################################################
/**
 * Callback returns no parameters.
 * @callback MysqlSuspend~cbOnEmpty
 */

/**
 * @callback MysqlSuspend~cbOnError
 * @param {Error} err - Returns an error if there was one, or null.
 */

/**
 * Returns the result of the query as an array of rows. Example:
 * <pre>
 *     [
 *          {id: 1, name: "Bob"},
 *          {id: 2, name: "John"}
 *     ]
 * </pre>
 * @callback MysqlSuspend~cbOnQuery
 * @param {Error} err - An error if there was one, or null.
 * @param {Array} result - An array of objects, where each object represents a row, or null.
 */

/**
 * Returns the result of the merge.
 * @callback MysqlSuspend~cbOnMerge
 * @param {Error} err - An error if there was one, or null.
 * @param {String} result - Either "insert" or "update" depending on what was used.
 */