tests/join.test.js

import { SqlParse } from '../SimpleParser.js';
import { SqlTester } from '../SqlTest.js';
import { Sql } from '../Sql';
import { TableFields, CalculatedField } from '../Views.js';
import { expect, jest, test } from '@jest/globals';
import { JoinTablesRecordIds } from '../JoinTables.js';

/**
 * @type {Sql}
 */
let sqlCmd = null;
describe("JOIN Tests", () => {
    beforeEach(() => {

        const testData = new SqlTester();

        sqlCmd = new Sql()
            .addTableData("booksales", testData.bookSalesTable())
            .addTableData("customers", testData.customerTable())
            .enableColumnTitle(true);
    })

    test('Parse SELECT', () => {
        const statement = "select * from booksales inner join customers on substr(booksales.customer_id, 2, 1) = substr(customers.id, 2, 1)";
        let ast = SqlParse.sql2ast(statement);

        /** @property {TableFields} */
        const tableFields = new TableFields();
        tableFields.loadVirtualFields("BOOKSALES", sqlCmd.getTables());

        const masterTable = sqlCmd.getTables().get("BOOKSALES");

        const calcSqlField = new CalculatedField(masterTable, masterTable, tableFields);
        const result = calcSqlField.evaluateCalculatedField("substr(booksales.customer_id, 2, 1)", 1);


        const joinTableIDs = new JoinTablesRecordIds();

        joinTableIDs
            .setJoinType("inner")
            .setLeftTableName("BOOKSALES")
            .setRightTableName("CUSTOMERS")
            .setTableFields(tableFields);

        expect(joinTableIDs.getRecordIDs(ast.JOIN[0])).toBe("1");

    })
});