demmings / gsSQL / 8678234616

// @author Chris Demmings - https://demmings.github.io/

/*  *** DEBUG START ***

//  Remove comments for testing in NODE

export { Sql, gsSQL, GasSql, BindData, TableExtract };

import { Table } from './Table.js';

import { TableData } from './TableData.js';

import { SqlParse } from './SimpleParser.js';

import { SelectTables } from './Views.js';

class Logger {

    static log(msg) {

        console.log(msg);

    }

}

//  *** DEBUG END ***/

/**

 * Query any sheet range using standard SQL SELECT syntax.

 * @example

 * gsSQL("select * from expenses where type = ?1", "expenses", A1:B, true, "travel")

 * 

 * @param {String} statement - SQL string 

 * @param {...any} parms - "table name",  SheetRange, [..."table name", SheetRange], OutputTitles (true/false), [...Bind Variable] 

 * @returns {any[][]} - Double array of selected data.  First index ROW, Second index COLUMN.

 * @customfunction

 */

function gsSQL(statement, ...parms) {     //  skipcq: JS-0128

    return GasSql.execute(statement, parms);

}

/**

 * @classdesc 

 * Top level class used by Google Sheets custom function to process SELECT and return table data. 

 */

class GasSql {

    /**

     * Run SELECT command statement and returns data in a table format (double array).

     * The appropriate functions are selected to be run based on the format of the command line parameters.

     * @param {String} statement 

     * @param {any[]} parms 

     * @returns {any[][]}

     */

    static execute(statement, parms) {

        if (parms.length === 0 || (parms.length > 0 && (Array.isArray(parms[0]) || parms[0] === ''))) {

            return GasSql.executeSqlv1(statement, parms);

        }

        else if (parms.length > 0 && typeof parms[0] === 'string') {

            return GasSql.executeSqlv2(statement, parms);

        }

    }

    /**

     * Processes SQL SELECT using original command line syntax.  This syntax does not update automatically if the

     * data changes, so is not recommended anymore.

     * @param {String} statement 

     * @param {any[]} parms 

     * @returns {any[][]}

     */

    static executeSqlv1(statement, parms) {

        const sqlCmd = new Sql();

        let columnTitle = true;

        const bindings = [];

        //  If first item of parms is an array, the parms are assumed to be:

        // @param {any[][]} tableArr - {{"tableName", "sheetRange", cacheSeconds, hasColumnTitle}; {"name","range",cache,true};...}"

        // @param {Boolean} columnTitle - TRUE will add column title to output (default=TRUE)

        // @param {...any} bindings - Bind variables to match '?' in SQL statement.

        const tableList = GasSql.parseTableSettings(tableArr, statement);

        Logger.log(`gsSQL: tableList=${tableList}.  Statement=${statement}. List Len=${tableList.length}`);

        for (const tableDef of tableList) {

            sqlCmd.addTableData(tableDef[0], tableDef[1], tableDef[2], tableDef[3]);

        }

        sqlCmd.enableColumnTitle(columnTitle);

        return sqlCmd.execute(statement);

    }

    /**

     * Process SQL SELECT using new command line syntax.  Using this syntax ensures that the select data is refreshed

     * if any of the selected table data changes - and is therefore the recommended usage.

     * @param {String} statement 

     * @param {any[]} parms 

     * @returns {any[][]}

     */

    static executeSqlv2(statement, parms) {

        const sqlCmd = new Sql();

        let columnTitle = true;

        const bindings = [];

        //  We expect:  "tableName", tableData[], ...["tableName", tableData[]], includeColumnOutput, ...bindings

        let i = 0;

        while (i + 1 < parms.length && typeof parms[i] !== 'boolean') {

            Logger.log(`Add Table: ${parms[i]}. Items=${parms[i + 1].length}`);

            sqlCmd.addTableData(parms[i], parms[i + 1], 0, true);

            i += 2;

        }

        if (i < parms.length && typeof parms[i] === 'boolean') {

            columnTitle = parms[i];

            i++

        }

        Logger.log(`Column Titles: ${columnTitle}`);

        while (i < parms.length) {

            Logger.log(`Add BIND Variable: ${parms[i]}`);

            bindings.push(parms[i]);

            i++

        }

        sqlCmd.enableColumnTitle(columnTitle);

        for (const bind of bindings) {

            sqlCmd.addBindParameter(bind);

        }

        return sqlCmd.execute(statement);

    }

    /**

     * 

     * @param {any[][]} tableArr - Referenced Table list.  This is normally the second parameter in gsSQL() custom function.  

     * It is a double array with first index for TABLE, and the second index are settings in the table. 

     * The setting index for each table is as follows:

     * * 0 - Table Name.

     * * 1 - Sheet Range.

     * * 2 - Cache seconds.

     * * 3 - First row contains title (for field name)

     * @param {String} statement - SQL SELECT statement.  If no data specified in 'tableArr', the SELECT is 

     * parsed and each referenced table is assumed to be a TAB name on the sheet.

     * @param {Boolean} randomOrder - Returned table list is randomized.

     * @returns {any[][]} - Data from 'tableArr' PLUS any extracted tables referenced from SELECT statement.

     * It is a double array with first index for TABLE, and the second index are settings in the table. 

     * The setting index for each table is as follows:

     * * 0 - Table Name.

     * * 1 - Sheet Range.

     * * 2 - Cache seconds.

     * * 3 - First row contains title (for field name)

     */

    static parseTableSettings(tableArr, statement = "", randomOrder = true) {

        let tableList = [];

        let referencedTableSettings = tableArr;

        //  Get table names from the SELECT statement when no table range info is given.

        if (tableArr.length === 0 && statement !== "") {

            referencedTableSettings = TableExtract.getReferencedTableNames(statement);

        }

        Logger.log(`tableArr = ${referencedTableSettings}`);

        for (/** @type {any[]} */ const table of referencedTableSettings) {

            if (table.length === 1)

                table.push(table[0]);   // if NO RANGE, assumes table name is sheet name.

            if (table.length === 2)

                table.push(60);         //  default 0 second cache.

            if (table.length === 3)

                table.push(true);       //  default HAS column title row.

            if (table[1] === "")

            if (table.length !== 4)

                throw new Error("Invalid table definition [name,range,cache,hasTitle]");

            tableList.push(table);

        }

        //  If called at the same time, loading similar tables in similar order - all processes

        //  just wait for table - but if loaded in different order, each process could be loading something.

        if (randomOrder)

            tableList = tableList.sort(() => Math.random() - 0.5);

        return tableList;

    }

}

/** 

 * @classdesc

 * Perform SQL SELECT using this class.

 */

class Sql {

    constructor() {

        /** @property {Map<String,Table>} - Map of referenced tables.*/

        this.tables = new Map();

        /** @property {Boolean} - Are column tables to be ouptout? */

        this.columnTitle = false;

        /** @property {BindData} - List of BIND data linked to '?' in statement. */

        this.bindData = new BindData();

        /** @property {String} - derived table name to output in column title replacing source table name. */

        this.columnTableNameReplacement = null;

    }

    /**

    * Parse SQL SELECT statement, performs SQL query and returns data ready for custom function return.

    * <br>Execute() can be called multiple times for different SELECT statements, provided that all required

    * table data was loaded in the constructor.  

    * Methods that would be used PRIOR to execute are:

    * <br>**enableColumnTitle()** - turn on/off column title in output

    * <br>**addBindParameter()** - If bind data is needed in select.  e.g. "select * from table where id = ?"

    * <br>**addTableData()** - At least ONE table needs to be added prior to execute. This tells **execute** where to find the data.

    * <br>**Example SELECT and RETURN Data**

    * ```js

    *   let stmt = "SELECT books.id, books.title, books.author_id " +

    *        "FROM books " +

    *        "WHERE books.author_id IN ('11','12') " +

    *        "ORDER BY books.title";

    *

    *    let data = new Sql()

    *        .addTableData("books", this.bookTable())

    *        .enableColumnTitle(true)

    *        .execute(stmt);

    * 

    *    Logger.log(data);

    * 

    * [["books.id", "books.title", "books.author_id"],

    *    ["4", "Dream Your Life", "11"],

    *    ["8", "My Last Book", "11"],

    *    ["5", "Oranges", "12"],

    *    ["1", "Time to Grow Up!", "11"]]

    * ```

    * @param {any} statement - SELECT statement as STRING or AST of SELECT statement.

    * @returns {any[][]} - Double array where first index is ROW and second index is COLUMN.

    */

    execute(statement) {

        this.ast = (typeof statement === 'string') ? SqlParse.sql2ast(statement) : statement;

        //  "SELECT * from (select a,b,c from table) as derivedtable"

        //  Sub query data is loaded and given the name 'derivedtable' (using ALIAS from AS)

        //  The AST.FROM is updated from the sub-query to the new derived table name. 

        this.selectFromSubQuery();

        //  A JOIN table can a sub-query.  When this is the case, the sub-query SELECT is

        //  evaluated and the return data is given the ALIAS (as) name.  The AST is then

        //  updated to use the new table.

        this.selectJoinSubQuery();

        TableAlias.setTableAlias(this.tables, this.ast);

        Sql.loadSchema(this.tables);

        let selectResults = this.select(this.ast);

        //  Apply SET rules to SELECTs (UNION, UNION ALL, EXCEPT, INTERSECT)

        selectResults = this.selectSet(selectResults, this.ast);

        return selectResults;

    }

    /**

     * Modifies AST when FROM is a sub-query rather than a table name.

     */

    selectFromSubQuery() {

        if (typeof this.ast.FROM === 'undefined' || typeof this.ast.FROM.SELECT === 'undefined')

            return;

        const data = new Sql()

            .setTables(this.tables)

            .enableColumnTitle(true)

            .replaceColumnTableNameWith(this.ast.FROM.table)

            .execute(this.ast.FROM);

        if (typeof this.ast.FROM.table !== 'undefined') {

            this.addTableData(this.ast.FROM.table, data);

        }

        this.ast.FROM.as = '';

    }

    /**

     * Checks if the JOINed table is a sub-query.  

     * The sub-query is evaluated and assigned the alias name.

     * The AST is adjusted to use the new JOIN TABLE.

     * @returns {void}

     */

    selectJoinSubQuery() {

        if (typeof this.ast.JOIN === 'undefined')

            return;

        //  When joinAst.table is an OBJECT, then it is a sub-query.

        const subQueries = this.ast.JOIN.filter(joinAst => typeof joinAst.table !== 'string');

        for (const joinAst of subQueries) {

            const data = new Sql()

                .setTables(this.tables)

                .enableColumnTitle(true)

                .replaceColumnTableNameWith(joinAst.as)

                .execute(joinAst.table);

            if (typeof joinAst.as !== 'undefined') {

                this.addTableData(joinAst.as, data);

            }

            joinAst.table = joinAst.as;

            joinAst.as = '';

        }

    }

    /**

     * Apply set rules to each select result.

     * @param {any[][]} leftTableData 

     * @param {Object} unionAst 

     * @returns {any[][]}

     */

    selectSet(leftTableData, unionAst) {

        if (!SqlSets.isSqlSet(unionAst)) {

            return leftTableData;

        }

        const columnTitles = this.areColumnTitlesOutput() && leftTableData.length > 0 ? leftTableData.shift() : [];

        this.enableColumnTitle(false);

        let ast = unionAst;

        while (SqlSets.isSqlSet(ast)) {

            const setType = SqlSets.getSetType(ast);

            ast = ast[setType][0];

            const rightTableData = this.select(ast);

            leftTableData = SqlSets.applySet(setType, leftTableData, rightTableData);

        }

        if (columnTitles.length > 0) {

            leftTableData.unshift(columnTitles);

        }

        return leftTableData;

    }

    /**

     * Load SELECT data and return in double array.

     * @param {Object} selectAst - Abstract Syntax Tree of SELECT

     * @returns {any[][]} - double array useable by Google Sheet in custom function return value.

     * * First row of data will be column name if column title output was requested.

     * * First Array Index - ROW

     * * Second Array Index - COLUMN

     */

    select(selectAst) {

        let ast = selectAst;

        Sql.errorCheckSelectAST(ast);

        //  Manipulate AST to add GROUP BY if DISTINCT keyword.

        ast = Sql.distinctField(ast);

        //  Manipulate AST add pivot fields.

        ast = Pivot.pivotField(ast, this.tables, this.bindData);

        const view = new SelectTables(ast, this.tables, this.bindData);

        //  JOIN tables to create a derived table.

        view.join(ast);                 // skipcq: JS-D008

        view.updateSelectedFields(ast);

        //  Get the record ID's of all records matching WHERE condition.

        const recordIDs = view.whereCondition(ast);

        //  Get selected data records.

        let viewTableData = view.getViewData(recordIDs);

        //  Compress the data.

        viewTableData = view.groupBy(ast, viewTableData);

        //  Sort our selected data.

        viewTableData = view.orderBy(ast, viewTableData);

        //  Remove fields referenced but not included in SELECT field list.

        view.removeTempColumns(viewTableData);

        //  Limit rows returned.

        viewTableData = SelectTables.limit(ast, viewTableData);

        //  Add column titles

        viewTableData = this.addColumnTitles(viewTableData, view);

        //  Deal with empty dataset.

        viewTableData = Sql.cleanUp(viewTableData);

        return viewTableData;

    }

    /**

     * Add data for each referenced table in SELECT, before EXECUTE().

     * @param {String} tableName - Name of table referenced in SELECT.

     * @param {any} tableData - Either double array or a named range.

     * @param {Number} cacheSeconds - How long should loaded data be cached (default=0)

     * @param {Boolean} hasColumnTitle - Is first data row the column title?

     * @returns {Sql}

     */

    addTableData(tableName, tableData, cacheSeconds = 0, hasColumnTitle = true) {

        let tableInfo = null;

        if (Array.isArray(tableData)) {

            tableInfo = new Table(tableName)

                .setHasColumnTitle(hasColumnTitle)

                .loadArrayData(tableData);

        }

        else {

            tableInfo = new Table(tableName)

                .setHasColumnTitle(hasColumnTitle)

                .loadNamedRangeData(tableData, cacheSeconds);

        }

        this.tables.set(tableName.toUpperCase(), tableInfo);

        return this;

    }

    /**

     * Copies the data from an external tableMap to this instance.  

     * It copies a reference to outside array data only.  

     * The schema would need to be re-loaded.

     * @param {Map<String,Table>} tableMap 

     */

    copyTableData(tableMap) {

        // @ts-ignore

        for (const tableName of tableMap.keys()) {

            const tableInfo = tableMap.get(tableName);

            this.addTableData(tableName, tableInfo.tableData);

        }

        return this;

    }

    /**

     * Include column headers in return data.

     * @param {Boolean} value - true will return column names in first row of return data.

     * @returns {Sql}

     */

    enableColumnTitle(value) {

        this.columnTitle = value;

        return this;

    }

    /**

     * Derived table data that requires the ALIAS table name in column title.

     * @param {String} replacementTableName - derived table name to replace original table name.  To disable, set to null.

     * @returns {Sql}

     */

    replaceColumnTableNameWith(replacementTableName) {

        this.columnTableNameReplacement = replacementTableName;

        return this;

    }

    /**

     * Query if this instance of Sql() will generate column titles.

     * @returns {Boolean}

     */

    areColumnTitlesOutput() {

        return this.columnTitle;

    }

    /**

     * Add a bind data value.  Must be added in order.  If bind data is a named range, use addBindNamedRangeParameter().

     * @param {any} value - literal data. 

     * @returns {Sql}

     */

    addBindParameter(value) {

        this.bindData.add(value);

        return this;

    }

    /**

     * List of bind data added so far.

     * @returns {any[]}

     */

    getBindData() {

        return this.bindData.getBindDataList();

    }

    /**

     * The BIND data is a sheet named range that will be read and used for bind data.

     * @param {String} value - Sheets Named Range for SINGLE CELL only.

     * @returns {Sql}

     */

    addBindNamedRangeParameter(value) {

        const namedValue = TableData.getValueCached(value, 30);

        this.bindData.add(namedValue);

        Logger.log(`BIND=${value} = ${namedValue}`);

        return this;

    }

    /**

     * Set all bind data at once using array.

     * @param {BindData} value - Bind data.

     * @returns {Sql}

     */

    setBindValues(value) {

        this.bindData = value;

        return this;

    }

    /**

     * Clears existing BIND data so Sql() instance can be used again with new bind parameters.

     * @returns {Sql}

     */

    clearBindParameters() {

        this.bindData.clear();

        return this;

    }

    /**

     * Updates 'tables' with table column information.

     * @param {Map<String,Table>} tables 

     */

    static loadSchema(tables) {

        // @ts-ignore

        for (const table of tables.keys()) {

            const tableInfo = tables.get(table.toUpperCase());

            tableInfo.loadSchema();

        }

    }

    /**

     * Sets all tables referenced SELECT.

     * @param {Map<String,Table>} mapOfTables - Map of referenced tables indexed by TABLE name.

     */

    setTables(mapOfTables) {

        this.tables = mapOfTables;

        return this;

    }

    /**

     * Returns a map of all tables configured for this SELECT.

     * @returns {Map<String,Table>} - Map of referenced tables indexed by TABLE name.

     */

    getTables() {

        return this.tables;

    }

    /**

     * Basic sanity check of AST for a SELECT statement.

     * @param {object} ast 

     */

    static errorCheckSelectAST(ast) {

        if (typeof ast.SELECT === 'undefined') {

            throw new Error("Only SELECT statements are supported.");

        }

        if (typeof ast.FROM === 'undefined') {

            throw new Error("Missing keyword FROM");

        }

    }

    /**

     * If 'GROUP BY' is not set and 'DISTINCT' column is specified, update AST to add 'GROUP BY'.

     * @param {Object} ast - Abstract Syntax Tree for SELECT.

     * @returns {Object} - Updated AST to include GROUP BY when DISTINCT field used.

     */

    static distinctField(ast) {

        const astFields = ast.SELECT;

        const firstField = astFields[0].name.toUpperCase();

        if (firstField.startsWith("DISTINCT")) {

            astFields[0].name = firstField.replace("DISTINCT", "").trim();

            if (typeof ast['GROUP BY'] === 'undefined') {

                ast["GROUP BY"] = astFields.map(astItem => ({ name: astItem.name, as: '' }));

            }

        }

        return ast;

    }

    /**

     * Add column titles to data if needed.

     * @param {any[][]} viewTableData 

     * @param {SelectTables} view 

     * @returns {any[][]}

     */

    addColumnTitles(viewTableData, view) {

        if (this.columnTitle) {

            viewTableData.unshift(view.getColumnTitles(this.columnTableNameReplacement));

        }

        return viewTableData;

    }

    /**

     * If no data and no titles, create empty double array so sheets function does not have an error.

     * @param {any[][]} viewTableData 

     * @returns {any[][]}

     */

    static cleanUp(viewTableData) {

        if (viewTableData.length === 0) {

            viewTableData.push([""]);

        }

        if (viewTableData.length === 1 && viewTableData[0].length === 0) {

            viewTableData[0] = [""];

        }

        return viewTableData;

    }

}

/**

 * @classdesc Deals with the table ALIAS inside select AST.

 */

class TableAlias {

    /**

     * Updates 'tables' with associated table ALIAS name found in ast.

     * @param {Map<String,Table>} tables 

     * @param {Object} ast 

     */

    static setTableAlias(tables, ast) {

        // @ts-ignore

        for (const table of tables.keys()) {

            const tableAlias = TableAlias.getTableAlias(table, ast);

            const tableInfo = tables.get(table.toUpperCase());

            tableInfo.setTableAlias(tableAlias);

        }

    }

    /**

    * Find table alias name (if any) for input actual table name.

    * @param {String} tableName - Actual table name.

    * @param {Object} ast - Abstract Syntax Tree for SQL.

    * @returns {String} - Table alias.  Empty string if not found.

    */

    static getTableAlias(tableName, ast) {

        let tableAlias = "";

        const ucTableName = tableName.toUpperCase();

        tableAlias = TableAlias.getTableAliasFromJoin(tableAlias, ucTableName, ast);

        tableAlias = TableAlias.getTableAliasUnion(tableAlias, ucTableName, ast);

        tableAlias = TableAlias.getTableAliasWhereIn(tableAlias, ucTableName, ast);

        tableAlias = TableAlias.getTableAliasWhereTerms(tableAlias, ucTableName, ast);

        return tableAlias;

    }

    /**

     * Searches the FROM and JOIN components of a SELECT to find the table alias.

     * @param {String} tableAlias - Default alias name

     * @param {String} tableName - table name to search for.

     * @param {Object} ast - Abstract Syntax Tree to search

     * @returns {String} - Table alias name.

     */

    static getTableAliasFromJoin(tableAlias, tableName, ast) {

        const astTableBlocks = ['FROM', 'JOIN'];

        let aliasNameFound = tableAlias;

        let i = 0;

        while (aliasNameFound === "" && i < astTableBlocks.length) {

            aliasNameFound = TableAlias.locateAstTableAlias(tableName, ast, astTableBlocks[i]);

            i++;

        }

        return aliasNameFound;

    }

    /**

     * Search a property of AST for table alias name.

     * @param {String} tableName - Table name to find in AST.

     * @param {Object} ast - AST of SELECT.

     * @param {String} astBlock - AST property to search.

     * @returns {String} - Alias name or "" if not found.

     */

    static locateAstTableAlias(tableName, ast, astBlock) {

        if (typeof ast[astBlock] === 'undefined')

            return "";

        let block = [ast[astBlock]];

        if (TableAlias.isIterable(ast[astBlock])) {

            block = ast[astBlock];

        }

        for (const astItem of block) {

            if (typeof astItem.table === 'string' && tableName === astItem.table.toUpperCase() && astItem.as !== "") {

                return astItem.as;

            }

        }

        return "";

    }

    /**

     * Check if input is iterable.

     * @param {any} input - Check this object to see if it can be iterated. 

     * @returns {Boolean} - true - can be iterated.  false - cannot be iterated.

     */

    static isIterable(input) {

        return typeof input[Symbol.iterator] === 'function'

    }

    /**

     * Searches the UNION portion of the SELECT to locate the table alias.

     * @param {String} tableAlias - default table alias.

     * @param {String} tableName - table name to search for.

     * @param {Object} ast - Abstract Syntax Tree to search

     * @returns {String} - table alias

     */

    static getTableAliasUnion(tableAlias, tableName, ast) {

        const astRecursiveTableBlocks = ['UNION', 'UNION ALL', 'INTERSECT', 'EXCEPT'];

        let extractedAlias = tableAlias;

        let i = 0;

        while (extractedAlias === "" && i < astRecursiveTableBlocks.length) {

            if (typeof ast[astRecursiveTableBlocks[i]] !== 'undefined') {

                for (const unionAst of ast[astRecursiveTableBlocks[i]]) {

                    extractedAlias = TableAlias.getTableAlias(tableName, unionAst);

                    if (extractedAlias !== "")

                        break;

                }

            }

            i++;

        }

        return extractedAlias;

    }

    /**

     * Search WHERE IN component of SELECT to find table alias.

     * @param {String} tableAlias - default table alias

     * @param {String} tableName - table name to search for

     * @param {Object} ast - Abstract Syntax Tree to search

     * @returns {String} - table alias

     */

    static getTableAliasWhereIn(tableAlias, tableName, ast) {

        let extractedAlias = tableAlias;

        if (tableAlias === "" && typeof ast.WHERE !== 'undefined' && ast.WHERE.operator === "IN") {

            extractedAlias = TableAlias.getTableAlias(tableName, ast.WHERE.right);

        }

        if (extractedAlias === "" && ast.operator === "IN") {

            extractedAlias = TableAlias.getTableAlias(tableName, ast.right);

        }

        return extractedAlias;

    }

    /**

     * Search WHERE terms of SELECT to find table alias.

     * @param {String} tableAlias - default table alias

     * @param {String} tableName  - table name to search for.

     * @param {Object} ast - Abstract Syntax Tree to search.

     * @returns {String} - table alias

     */

    static getTableAliasWhereTerms(tableAlias, tableName, ast) {

        let extractedTableAlias = tableAlias;

        if (tableAlias === "" && typeof ast.WHERE !== 'undefined' && typeof ast.WHERE.terms !== 'undefined') {

            for (const term of ast.WHERE.terms) {

                if (extractedTableAlias === "")

                    extractedTableAlias = TableAlias.getTableAlias(tableName, term);

            }

        }

        return extractedTableAlias;

    }

}

/**

 * @classdesc Deals with extracting all TABLE names referenece inside SELECT.

 */

class TableExtract {

    /**

     * Create table definition array from select string.

     * @param {String} statement - full sql select statement.

     * @returns {String[][]} - table definition array.

     */

    static getReferencedTableNames(statement) {

        const ast = SqlParse.sql2ast(statement);

        return TableExtract.getReferencedTableNamesFromAst(ast);

    }

    /**

     * Create table definition array from select AST.

     * @param {Object} ast - AST for SELECT. 

     * @returns {any[]} - table definition array.

     * * [0] - table name.

     * * [1] - sheet tab name

     * * [2] - cache seconds

     * * [3] - output column title flag

     */

    static getReferencedTableNamesFromAst(ast) {

        const DEFAULT_CACHE_SECONDS = 60;

        const DEFAULT_COLUMNS_OUTPUT = true;

        const tableSet = new Map();

        TableExtract.extractAstTables(ast, tableSet);

        const tableList = [];

        // @ts-ignore

        for (const key of tableSet.keys()) {

            const tableDef = [key, key, DEFAULT_CACHE_SECONDS, DEFAULT_COLUMNS_OUTPUT];

            tableList.push(tableDef);

        }

        return tableList;

    }

    /**

     * Search for all referenced tables in SELECT.

     * @param {Object} ast - AST for SELECT.

     * @param {Map<String,String>} tableSet  - Function updates this map of table names and alias name.

     */

    static extractAstTables(ast, tableSet) {

        if (typeof ast === 'undefined' || ast === null) {

            return;

        }

        TableExtract.getTableNamesFrom(ast, tableSet);

        TableExtract.getTableNamesJoin(ast, tableSet);

        TableExtract.getTableNamesUnion(ast, tableSet);

        TableExtract.getTableNamesWhereIn(ast, tableSet);

        TableExtract.getTableNamesWhereTerms(ast, tableSet);

        TableExtract.getTableNamesCorrelatedSelect(ast, tableSet);

    }

    /**

     * Search for referenced table in FROM or JOIN part of select.

     * @param {Object} ast - AST for SELECT.

     * @param {Map<String,String>} tableSet  - Function updates this map of table names and alias name.

     */

    static getTableNamesFrom(ast, tableSet) {

        let fromAst = ast.FROM;

        while (typeof fromAst !== 'undefined') {

            if (typeof fromAst.isDerived === 'undefined') {

            }

            else {

                TableExtract.extractAstTables(fromAst.FROM, tableSet);

                TableExtract.getTableNamesUnion(fromAst, tableSet);

            }

            fromAst = fromAst.FROM;

        }

    }

    /**

    * Search for referenced table in FROM or JOIN part of select.

    * @param {Object} ast - AST for SELECT.

    * @param {Map<String,String>} tableSet  - Function updates this map of table names and alias name.

    */

    static getTableNamesJoin(ast, tableSet) {

        if (typeof ast.JOIN === 'undefined')

            return;

        for (const astItem of ast.JOIN) {

            if (typeof astItem.table === 'string') {

            }

            else {

                TableExtract.extractAstTables(astItem.table, tableSet);

            }

        }

    }

    /**

     * Searches for table names within SELECT (union, intersect, except) statements.

     * @param {Object} ast - AST for SELECT

     * @param {Map<String,String>} tableSet - Function updates this map of table names and alias name.

     */

    static getTableNamesUnion(ast, tableSet) {

        const astRecursiveTableBlocks = ['UNION', 'UNION ALL', 'INTERSECT', 'EXCEPT'];

        for (const block of astRecursiveTableBlocks) {