Select2Object.js

//  *** DEBUG START ***

//  Remove comments for testing in NODE
import { GasSql } from "./Sql.js";
export { Select2Object };

//  *** DEBUG END ***/

/**
 * @classdesc - Executes a SELECT statement on sheet data.  Returned data will be any array of objects,
 * where each item is one row of data.  The property values in the object are the column names.
 * The column names will be in lower case.  If more than one table is referenced, the column name will be:
 * "table.column", otherwise it will just be the column name.  Spaces in the column name use the underscore, so
 * something like "Transaction Date" would be referenced as "transaction_date".
 */
class Select2Object {           // skipcq: JS-0128
    constructor() {
        this.tables = [];
        this.bindVariables = [];
    }

    /**
     * 
     * @param {String} tableName - table name referenced in SELECT statement.
     * @param {*} data - double array or string.  If string it must reference A1 notation, named range or sheet name.
     * @returns {Select2Object}
     */
    addTableData(tableName, data) {
        const table = { tableName, data };
        this.tables.push(table);

        return this;
    }

    /**
     * If bind variables are used in SELECT statement, this are added here.
     * Ordering is important.  The first one added will be '?1' in the select, second is '?2' in select...
     * @param {any} bindVar 
     * @returns {Select2Object}
     */
    addBindVariable(bindVar) {
        this.bindVariables.push(bindVar);

        return this;
    }

    /**
     * Query any sheet range using standard SQL SELECT syntax and return array of table info with column names as properties.
     * @example
     * gsSQL("select * from expenses where type = ?1")
     * 
     * @param {String} statement - SQL string 
     * @returns {Object[]} - array of object data.  
     */
    execute(statement) {     //  skipcq: JS-0128
        const parms = [];

        //  Add the table name and range.
        for (const tab of this.tables) {
            parms.push(tab.tableName);
            parms.push(tab.data);
        }

        //  Add column output indicator.
        parms.push(true);   //  We want column names returned.

        //  Add bind data.
        for (const bind of this.bindVariables) {
            parms.push(bind);
        }

        const tableDataArray = GasSql.execute(statement, parms);

        if (tableDataArray === null || tableDataArray.length === 0) {
            return null;
        }

        //  First item in return array is an array of column names.
        const columnNames = Select2Object.cleanupColumnNames(tableDataArray[0]);

        return Select2Object.createTableObjectArray(columnNames, tableDataArray);
    }

    /**
     * Return column names in lower case and remove table name when only one table.
     * @param {String[]} cols 
     * @returns {String[]}
     */
    static cleanupColumnNames(cols) {
        const newColumns = cols.map(v => v.toLowerCase());
        const noTableColumns = [];

        const uniqueTables = new Set();
        for (const col of newColumns) {
            const splitColumn = col.split(".");

            if (splitColumn.length > 1) {
                uniqueTables.add(splitColumn[0]);
                noTableColumns.push(splitColumn[1]);
            }
            else {
                noTableColumns.push(splitColumn[0]);
            }
        }

        //  Leave the table name in the column since we have two or more tables.
        if (uniqueTables.size > 1)
            return newColumns;

        return noTableColumns;
    }

    /**
     * 
     * @param {String[]} columnNames 
     * @param {any[]} tableDataArray 
     * @returns {Object[]}
     */
    static createTableObjectArray(columnNames, tableDataArray) {
        //  Create empty table record object.
        const emptyTableRecord = Select2Object.createEmptyRecordObject(columnNames);

        //  Create table array with record data stored in an object.
        const tableData = [];
        for (let i = 1; i < tableDataArray.length; i++) {
            const newRecord = {};
            Object.assign(newRecord, emptyTableRecord);

            for (let j = 0; j < columnNames.length; j++) {
                newRecord[columnNames[j]] = tableDataArray[i][j];
            }

            tableData.push(newRecord);
        }

        return tableData;
    }

    /**
     * Creates an empty object where each column name is a property in the object.
     * @param {String[]} columnNames 
     * @returns {Object}
     */
    static createEmptyRecordObject(columnNames) {
        //  Create empty table record object.
        const dataObject = {};
        for (const col of columnNames) {
            dataObject[col] = '';
        }

        return dataObject;
    }
}