demmings/gsSQL | Build 4027440288 | src/SqlTest.js | Coveralls

1

//  Remove comments for testing in NODE

1✔

2

/*  *** DEBUG START ***

1✔

3

import { Sql, parseTableSettings, gsSQL } from './Sql.js';

1✔

4

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

1✔

5

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

1✔

6

export { CacheService };

1✔

7

export { LockService };

1✔

8

export { SpreadsheetApp };

1✔

9

export { Range };

1✔

10

export { Utilities };

1✔

11

export { Logger };

1✔

12

export { PropertiesService };

1✔

13

1✔

14

//  GAS Mock Ups.

1✔

15

class CacheService {

1✔

16

    constructor() {

1✔

17

        // @type {Map<String, cacheItem>}

1✔

18

        this.cacheMap = new Map();

1✔

19

1✔

20

    static cacheObject = null;

1✔

21

1✔

22

    static getScriptCache() {

1✔

23

        if (this.cacheObject === null)

21✔

24

            this.cacheObject = new CacheService();

21✔

25

21✔

26

        return this.cacheObject;

21✔

27

21✔

28

1✔

29

    get(tagName) {

1✔

30

        let cacheValue = this.cacheMap.get(tagName);

23✔

31

        if (typeof cacheValue === 'undefined')

23✔

32

            return null;

23✔

33

15✔

38

15✔

39

        return cacheValue.dataValue;

15✔

40

23✔

41

1✔

42

    put(namedRange, singleData, seconds) {

1✔

43

        let dataItem = new cacheItem(singleData, seconds);

10✔

44

        this.cacheMap.set(namedRange, dataItem);

10✔

45

10✔

46

1✔

47

    putAll(putObject, cacheSeconds) {

1✔

48

        for (let prop in putObject) {

2✔

49

            this.put(prop, putObject[prop], cacheSeconds);

2✔

50

2✔

51

2✔

52

1✔

53

    remove(tagName) {

1✔

54

        if (this.get(tagName) !== null) {

1✔

55

            this.cacheMap.delete(tagName);

1✔

56

1✔

57

1✔

58

1✔

59

1✔

60

class cacheItem {

1✔

61

    constructor(dataValue, seconds) {

1✔

62

        this.dataValue = dataValue;

13✔

63

        this.startTime = new Date().getTime();

13✔

64

        this.seconds = seconds;

13✔

65

13✔

66

1✔

67

    isExpired() {

1✔

68

        const endTime = new Date().getTime();

21✔

69

        let timeDiff = endTime - this.startTime; //in ms

21✔

70

        // strip the ms

21✔

71

        timeDiff /= 1000;

21✔

72

21✔

73

        return timeDiff > this.seconds;

21✔

74

21✔

75

1✔

76

1✔

77

class PropertiesService {

1✔

78

    constructor() {

1✔

79

        // @type {Map<String, cacheItem>}

1✔

80

        this.cacheMap = new Map();

1✔

81

1✔

82

    static cacheObject = null;

1✔

83

1✔

84

    static getScriptProperties() {

1✔

85

        if (this.cacheObject === null)

3✔

86

            this.cacheObject = new PropertiesService();

3✔

87

3✔

88

        return this.cacheObject;

3✔

89

3✔

90

1✔

91

    getProperty(tagName) {

1✔

92

        let cacheValue = this.cacheMap.get(tagName);

8✔

93

        if (typeof cacheValue === 'undefined')

8✔

94

            return null;

8✔

95

6✔

100

6✔

101

        return cacheValue.dataValue;

6✔

102

8✔

103

1✔

104

    deleteProperty(tagName) {

1✔

108

1✔

109

    setProperty(namedRange, singleData, seconds = 999999) {

1✔

110

        let dataItem = new cacheItem(singleData, seconds);

3✔

111

        this.cacheMap.set(namedRange, dataItem);

3✔

112

3✔

113

1✔

114

    getKeys() {

1✔

115

        return Array.from(this.cacheMap.keys());

2✔

116

2✔

117

1✔

118

1✔

119

class LockService {

1✔

120

    static isLocked = false;

1✔

121

1✔

122

    static getScriptLock() {

1✔

123

        return new LockService();

6✔

124

6✔

125

1✔

126

    waitLock(ms) {

1✔

127

        let startTime = new Date().getTime();

6✔

128

        // @ts-ignore

6✔

132

6✔

133

        // @ts-ignore

6✔

137

6✔

138

        this.isLocked = true;

6✔

139

6✔

140

1✔

141

    releaseLock() {

1✔

142

        this.isLocked = false;

6✔

143

6✔

144

1✔

145

1✔

146

class SpreadsheetApp {

1✔

147

    static getActiveSpreadsheet() {

1✔

148

        return new SpreadsheetApp();

8✔

149

8✔

150

1✔

151

    getRangeByName(tableNamedRange) {

1✔

152

        const dataRange = new Range(tableNamedRange);

6✔

153

        return dataRange.getMockData() === null ? null : dataRange;

6✔

154

6✔

155

1✔

156

    // @param {String} sheetTabName

1✔

157

    //  @returns {Sheet}

1✔

158

    getSheetByName(sheetTabName) {

1✔

159

        let sheetObj = new Sheet(sheetTabName);

2✔

160

        if (sheetObj.getSheetValues(1, 1, 1, 1) === null)

2✔

162

        return sheetObj;

2✔

163

2✔

164

1✔

165

1✔

166

class Sheet {

1✔

167

    constructor(sheetName) {

1✔

168

        this.sheetName = sheetName;

2✔

169

2✔

170

1✔

171

    getLastColumn() {

1✔

172

        let data = this.getSheetValues(-1, -1, -1, -1);

1✔

173

        if (data !== null && data.length > 0)

1✔

174

            return data[0].length;

1✔

177

1✔

178

1✔

179

    getLastRow() {

1✔

180

        let data = this.getSheetValues(-1, -1, -1, -1);

1✔

181

        if (data !== null && data.length > 0)

1✔

182

            return data.length;

1✔

185

1✔

186

1✔

187

    getSheetValues(startRow, startCol, lastRow, lastColumn) {

1✔

188

        let tester = new SqlTester();

5✔

189

5✔

190

        switch (this.sheetName.toUpperCase()) {

5✔

191

            case "MASTER TRANSACTIONS":

5✔

192

                return tester.masterTransactionsTable();

5✔

195

5✔

196

5✔

197

1✔

198

1✔

199

class Range {

1✔

200

    constructor(tableNameRange) {

1✔

201

        this.tableNameRange = tableNameRange;

6✔

202

6✔

203

1✔

204

    getValues() {

1✔

205

        return this.getMockData();

3✔

206

3✔

207

1✔

208

    getValue() {

1✔

209

        return this.getMockData()

2✔

210

2✔

211

1✔

212

    //  Set data to be returned for any named range tested.

1✔

213

    getMockData() {

1✔

214

        let tester = new SqlTester();

11✔

215

11✔

216

        switch (this.tableNameRange.toUpperCase()) {

11✔

217

            case 'STARTINCOMEDATE':

11✔

218

                return '6/7/2019';

2✔

219

            case 'ENDINCOMEDATE':

11✔

220

                return '6/20/2019';

2✔

221

            case "MASTER TRANSACTIONS!$A$1:$I":

11✔

222

            case "MASTER TRANSACTIONS!$A$1:$I30":

11✔

223

                return tester.masterTransactionsTable();

6✔

226

            default:

11✔

227

                return null;

1✔

228

11✔

229

11✔

230

1✔

231

1✔

232

class Utilities {

1✔

233

    static sleep(seconds) {

1✔

234

        const startTime = new Date().getTime();

1✔

235

1✔

236

        const waitMs = seconds * 1000;

1✔

237

        while (new Date().getTime() - startTime < waitMs) {

1✔

238

            //  waiting...

1,151,762✔

239

1,151,762✔

240

1✔

241

1✔

242

1✔

243

1✔

244

class Logger {

1✔

245

    static log(msg) {

1✔

246

        console.log(msg);

188✔

247

188✔

248

1✔

249

//  *** DEBUG END  ***/

1✔

250

1✔

251

/**

1✔

252

 * Runs all tests and reports back the result of the tests.

1✔

253

 * @customfunction

1✔

254

*/

1✔

260

1✔

267

1✔

268

/**

1✔

269

 * Function should be commented out when NOT running in a TEST SHEET.

1✔

270

 * It will create a menu option that allows you to create a gsSQL()

1✔

271

 * statement for every test SQL in TestSQL().

1✔

272

*/

1✔

290

1✔

291

/**

1✔

292

 * @type {TestedStatements[]}

1✔

293

*/

1✔

294

let sqlTestCases = [];

1✔

295

1✔

296

/**

1✔

297

 * Expected to be run as a menu item.

1✔

298

 * Runs all internal tests, collects the SQL from each test and generates

1✔

299

 * a =gsSQL() string and writes it to the current active screen.

1✔

300

 * Each customfunction is written in column A, starting two rows below the

1✔

301

 * last row in the current sheet.  Room is left after the expected results

1✔

302

 * and the subsequent gsSQL() will be updated 3 rows after.

1✔

303

*/

1✔

309

1✔

310

/**

1✔

311

1✔

312

 * @param {String} functionName

1✔

313

 * @param {any[][]} array1

1✔

314

 * @param {any[][]} array2

1✔

315

 * @returns {String[][]}

1✔

316

 * @customfunction

1✔

317

*/

1✔

328

1✔

329

class TestedStatements {

1✔

330

/**

1✔

331

1✔

332

     * @param {String} statement

1✔

333

     * @param {any[]} bindVariables

1✔

334

     * @param {any[][]} data

1✔

335

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

1✔

336

     * @param {Boolean} generateColumnTitles

1✔

337

*/

1✔

338

    constructor(statement, bindVariables, data, tables, generateColumnTitles) {

1✔

339

        this.statement = statement;

122✔

340

        this.bindVariables = bindVariables;

122✔

341

        this.expectedOutputlines = data.length;

122✔

342

        this.data = data;

122✔

343

        this.tables = tables;

122✔

344

        this.generateColumnTitles = generateColumnTitles;

122✔

345

        this.generateTableDefinition = false;

122✔

346

122✔

347

        // @ts-ignore

122✔

348

        for (let tableInfo of this.tables.values()) {

122✔

349

            if (!tableInfo.hasColumnTitle)

199✔

350

                this.generateTableDefinition = true;

199✔

351

199✔

352

122✔

353

1✔

354

/**

1✔

355

1✔

356

     * @returns {String}

1✔

357

*/

1✔

358

    getTableDefinitionString() {

1✔

386

1✔

387

1✔

388

class TestSql extends Sql {

1✔

389

    constructor() {

1✔

390

        super();

145✔

391

145✔

392

1✔

393

/**

1✔

394

1✔

395

     * @param {String} stmt

1✔

396

     * @returns {any[][]}

1✔

397

*/

1✔

398

    execute(stmt) {

1✔

399

        let bindings = [...super.getBindData()];

145✔

400

        let tables = super.getTables();

145✔

401

        let generateColumnTitles = super.areColumnTitlesOutput();

145✔

402

        let data = [];

145✔

403

145✔

404

        try {

145✔

405

            data = super.execute(stmt);

145✔

406

145✔

407

        catch (ex) {

145✔

408

            throw ex;

23✔

409

23✔

410

122✔

411

        let test = new TestedStatements(stmt, bindings, data, tables, generateColumnTitles);

122✔

412

122✔

413

        sqlTestCases.push(test);

122✔

414

122✔

415

        return data;

122✔

416

145✔

417

1✔

418

    static generateTestCustomFunctions() {

1✔

492

1✔

493

/**

1✔

494

1✔

495

     * @param {TestedStatements} testCase

1✔

496

     * @returns {String}

1✔

497

*/

1✔

498

    static makeCustomFormulaString(testCase) {

1✔

529

1✔

530

    static makeTestResultFormulaString(testNumber, rangeResults, rangeExpected) {

1✔

539

1✔

540

1✔

541

class SqlTester {

1✔

542

/*

1✔

543

    LOAD DATA INFILE '/home/cdemmings/Projects/Sheets/CanadianRetirementPlanner/SQL/csv/books.csv'

1✔

544

    INTO TABLE books

1✔

545

    FIELDS

1✔

546

        TERMINATED BY ', '

1✔

547

        ENCLOSED BY '\"'

1✔

548

        ESCAPED BY ''

1✔

549

    LINES TERMINATED BY '\n'

1✔

550

    IGNORE 1 ROWS;

1✔

551

*/

1✔

552

1✔

553

    /* CREATE TABLE books (id CHAR(6), title VARCHAR(200),

1✔

554

        type VARCHAR(20), author_id CHAR(6), editor_id CHAR(6), translator_id CHAR(6));

1✔

555

*/

1✔

556

    bookTable() {

1✔

557

        return [

31✔

558

            ["id", "title", "type", "author id", "editor id", "translator id"],

31✔

559

            ["1", "Time to Grow Up!", "original", "11", "21", ""],

31✔

560

            ["2", "Your Trip", "translated", "15", "22", "32"],

31✔

561

            ["3", "Lovely Love", "original", "14", "24", ""],

31✔

562

            ["4", "Dream Your Life", "original", "11", "24", ""],

31✔

563

            ["5", "Oranges", "translated", "12", "25", "31"],

31✔

564

            ["6", "Your Happy Life", "translated", "15", "22", "33"],

31✔

565

            ["7", "Applied AI", "translated", "13", "23", "34"],

31✔

566

            ["9", "Book with Mysterious Author", "translated", "1", "23", "34"],

31✔

567

            ["8", "My Last Book", "original", "11", "28", ""]

31✔

568

];

31✔

569

31✔

570

1✔

571

/*

1✔

572

    LOAD DATA INFILE '/home/cdemmings/Projects/Sheets/CanadianRetirementPlanner/SQL/csv/booksales.csv'

1✔

573

    INTO TABLE booksales

1✔

574

    FIELDS

1✔

575

        TERMINATED BY ', '

1✔

576

        ENCLOSED BY '\"'

1✔

577

        ESCAPED BY ''

1✔

578

    LINES TERMINATED BY '\n'

1✔

579

    IGNORE 1 ROWS;

1✔

580

*/

1✔

581

1✔

582

    /* CREATE TABLE booksales (invoice CHAR(6), book_id CHAR(6),

1✔

583

        customer_id CHAR(6), quantity integer, price double, date date);

1✔

584

*/

1✔

585

    bookSalesTable() {

1✔

586

        return [

87✔

587

            ["Invoice", "Book Id", "Customer ID", "Quantity", "Price", "Date"],

87✔

588

            ["I7200", "9", "C1", 10, 34.95, "05/01/2022"],

87✔

589

            ["I7201", "8", "C2", 3, 29.95, "05/01/2022"],

87✔

590

            ["I7201", "7", "C2", 5, 18.99, "05/01/2022"],

87✔

591

            ["I7202", "9", "C3", 1, 59.99, "05/02/2022"],

87✔

592

            ["I7203", "1", "", 1, 90, "05/02/2022"],

87✔

593

            ["I7204", "2", "C4", 100, 65.49, "05/03/2022"],

87✔

594

            ["I7204", "3", "C4", 150, 24.95, "05/03/2022"],

87✔

595

            ["I7204", "4", "C4", 50, 19.99, "05/03/2022"],

87✔

596

            ["I7205", "7", "C1", 1, 33.97, "05/04/2022"],

87✔

597

            ["I7206", "7", "C2", 100, 17.99, "05/04/2022"]

87✔

598

];

87✔

599

87✔

600

87✔

601

1✔

602

/*

1✔

603

    LOAD DATA INFILE '/home/cdemmings/Projects/Sheets/CanadianRetirementPlanner/SQL/csv/bookreturns.csv'

1✔

604

    INTO TABLE bookreturns

1✔

605

    FIELDS

1✔

606

        TERMINATED BY ', '

1✔

607

        ENCLOSED BY '\"'

1✔

608

        ESCAPED BY ''

1✔

609

    LINES TERMINATED BY '\n'

1✔

610

    IGNORE 1 ROWS;

1✔

611

*/

1✔

612

1✔

613

    /* CREATE TABLE bookreturns (rma CHAR(7), book_id CHAR(6),

1✔

614

        customer_id CHAR(6), quantity integer, price double, date date);

1✔

615

*/

1✔

616

    bookReturnsTable() {

1✔

617

        return [

4✔

618

            ["RMA", "Book Id", "Customer ID", "Quantity", "Price", "Date"],

4✔

619

            ["Rma001", "9", "c1", 10, 34.95, "05/01/2022"],

4✔

620

            ["rma020", "8", "c2", 3, 29.95, "05/01/2022"],

4✔

621

            ["rmA030", "7", "c2", 5, 18.99, "05/01/2022"],

4✔

622

            ["RMA040", "9", "c3", 1, 59.99, "05/02/2022"],

4✔

623

            ["rma005", "1", "c1", 1, 90, "05/02/2022"],

4✔

624

            ["RMA600", "2", "c4", 100, 65.49, "05/03/2022"],

4✔

625

            ["Rma701", "3", "c4", 150, 24.95, "05/03/2022"],

4✔

626

            ["RmA800", "4", "c4", 50, 19.99, "05/03/2022"],

4✔

627

            ["RMA900", "7", "c1", 1, 33.97, "05/04/2022"],

4✔

628

            ["rma1010", "7", "c2", 100, 17.99, "05/04/2022"]

4✔

629

];

4✔

630

4✔

631

4✔

632

1✔

633

/*

1✔

634

    LOAD DATA INFILE '/home/cdemmings/Projects/Sheets/CanadianRetirementPlanner/SQL/csv/customers.csv'

1✔

635

    INTO TABLE customers

1✔

636

    FIELDS

1✔

637

        TERMINATED BY ', '

1✔

638

        ENCLOSED BY '\"'

1✔

639

        ESCAPED BY ''

1✔

640

    LINES TERMINATED BY '\n'

1✔

641

    IGNORE 1 ROWS;

1✔

642

*/

1✔

643

1✔

644

    /* CREATE TABLE customers (id CHAR(6), name VARCHAR(100),

1✔

645

        address VARCHAR(200), city VARCHAR(50), phone CHAR(20), email VARCHAR(200));

1✔

646

*/

1✔

647

    customerTable() {

1✔

648

        return [

25✔

649

            ["ID", "Name", "Address", "City", "Phone", "eMail"],

25✔

650

            ["C1", "Numereo Uno", "101 One Way", "One Point City", "9051112111", "bigOne@gmail.com"],

25✔

651

            ["C2", "Dewy Tuesdays", "202 Second St.", "Second City", "4162022222", "twoguys@gmail.com"],

25✔

652

            ["C3", "Tres Buon Goods", "3 Way St", "Tres City", "5193133303", "thrice@hotmail.com"],

25✔

653

            ["C4", "ForMe Resellers", "40 Four St", "FourtNight City", "2894441234", "fourtimes@hotmail.com"],

25✔

654

            ["C5", "Fe Fi Fo Giant Tiger", "5 ohFive St.", "FifthDom", "4165551234", "   fiver@gmail.com"],

25✔

655

            ["C6", "Sx in Cars", "6 Seventh St", "Sx City", "6661116666", "gotyourSix@hotmail.com   "],

25✔

656

            ["C7", "7th Heaven", "7 Eight Crt.", "Lucky City", "5551117777", " timesAcharm@gmail.com "]

25✔

657

];

25✔

658

25✔

659

25✔

660

1✔

661

/*

1✔

662

    LOAD DATA INFILE '/home/cdemmings/Projects/Sheets/CanadianRetirementPlanner/SQL/csv/authors.csv'

1✔

663

    INTO TABLE authors

1✔

664

    FIELDS

1✔

665

        TERMINATED BY ', '

1✔

666

        ENCLOSED BY '\"'

1✔

667

        ESCAPED BY ''

1✔

668

    LINES TERMINATED BY '\n'

1✔

669

    IGNORE 1 ROWS;

1✔

670

*/

1✔

671

1✔

672

    /* CREATE TABLE authors (id CHAR(6), first_name VARCHAR(100),

1✔

673

        last_name VARCHAR(200));

1✔

674

*/

1✔

675

    authorsTable() {

1✔

676

        return [

33✔

677

            ["id", "first_name", "last_name"],

33✔

678

            ["11", "Ellen", "Writer"],

33✔

679

            ["12", "Olga", "Savelieva"],

33✔

680

            ["13", "Jack", "Smart"],

33✔

681

            ["14", "Donald", "Brain"],

33✔

682

            ["15", "Yao", "Dou"]

33✔

683

];

33✔

684

33✔

685

1✔

686

/*

1✔

687

    LOAD DATA INFILE '/home/cdemmings/Projects/Sheets/CanadianRetirementPlanner/SQL/csv/editors.csv'

1✔

688

    INTO TABLE editors

1✔

689

    FIELDS

1✔

690

        TERMINATED BY ', '

1✔

691

        ENCLOSED BY '\"'

1✔

692

        ESCAPED BY ''

1✔

693

    LINES TERMINATED BY '\n'

1✔

694

    IGNORE 1 ROWS;

1✔

695

*/

1✔

696

1✔

697

    /* CREATE TABLE editors (id CHAR(6), first_name VARCHAR(100),

1✔

698

        last_name VARCHAR(200));

1✔

699

*/

1✔

700

    editorsTable() {

1✔

701

        return [

23✔

702

            ["id", "first name", "last name"],

23✔

703

            ["13", "Jack", "Smart"],

23✔

704

            ["21", "Daniel", "Brown"],

23✔

705

            ["22", "Mark", "Johnson"],

23✔

706

            ["23", "Maria", "Evans"],

23✔

707

            ["24", "Cathrine", "Roberts"],

23✔

708

            ["25", "Sebastian", "Wright"],

23✔

709

            ["26", "Barbara", "Jones"],

23✔

710

            ["27", "Matthew", "Smith"],

23✔

711

            ["50", "Jack", "Dumb"],

23✔

712

            ["51", "Daniel", "Smart"]

23✔

713

];

23✔

714

23✔

715

1✔

716

1✔

717

/*

1✔

718

    LOAD DATA INFILE '/home/cdemmings/Projects/Sheets/CanadianRetirementPlanner/SQL/csv/translators.csv'

1✔

719

    INTO TABLE translators

1✔

720

    FIELDS

1✔

721

        TERMINATED BY ', '

1✔

722

        ENCLOSED BY '\"'

1✔

723

        ESCAPED BY ''

1✔

724

    LINES TERMINATED BY '\n'

1✔

725

    IGNORE 1 ROWS;

1✔

726

*/

1✔

727

1✔

728

    /* CREATE TABLE translators (id CHAR(6), first_name VARCHAR(100),

1✔

729

        last_name VARCHAR(200));

1✔

730

*/

1✔

731

    translatorsTable() {

1✔

732

        return [

8✔

733

            ["id", "first_name", "last_name"],

8✔

734

            ["31", "Ira", "Davies"],

8✔

735

            ["32", "Ling", "Weng"],

8✔

736

            ["33", "Kristian", "Green"],

8✔

737

            ["34", "Roman", "Edwards"]

8✔

738

];

8✔

739

8✔

740

8✔

741

1✔

742

    masterTransactionsTable() {

1✔

743

        return [

12✔

744

            ["Name of Institution", "Transaction Date", "Description 1", "Description 2", "Amount", "Expense Category", "Account", "Gross", "Balance"],

12✔

745

            ["Royal Bank of Canada", new Date("6/7/2019"), "Interac purchase - 3707 NADIM'S NO FRIL", "", -47.85, "Food & Dining - Groceries", "", "", ""],

12✔

746

            ["Royal Bank of Canada", new Date("6/7/2019"), "Interac purchase - 2357 FRESHCO 3826", "", -130.36, "Food & Dining - Groceries", "", "", ""],

12✔

747

            ["Royal Bank of Canada", new Date("6/7/2019"), "Payroll Deposit WEST UNIFIED CO", "", 2343.48, "Income - Paycheck", "", "", ""],

12✔

748

            ["Royal Bank of Canada", new Date("6/7/2019"), "MBNA-MASTERCARD", "", -500, "Transfer - CC", "", "", ""],

12✔

749

            ["Royal Bank of Canada", new Date("6/7/2019"), "e-Transfer sent S.E", "", -575, "Utilities - Rent", "", "", ""],

12✔

750

            ["Royal Bank of Canada", new Date("6/11/2019"), "Insurance ADMIN.BY GWL", "", 122.4, "Health & Fitness - Health Insurance", "", "", ""],

12✔

751

            ["Royal Bank of Canada", new Date("6/13/2019"), "Misc Payment GOODLIFE CLUBS", "", -24.85, "Health & Fitness - Gym", "", "", ""],

12✔

752

            ["Royal Bank of Canada", new Date("6/13/2019"), "WHITBY TAXES", "", -100, "Taxes - Property Tax", "", "", ""],

12✔

753

            ["Royal Bank of Canada", new Date("6/13/2019"), "Online Transfer to Deposit Account-***9", "", -15, "Transfer - Savings acct", "", "", ""],

12✔

754

            ["Royal Bank of Canada", new Date("6/14/2019"), "Interac purchase - 8727 NADIM'S NO FRIL", "", -86.73, "Food & Dining - Groceries", "", "", ""],

12✔

755

            ["Royal Bank of Canada", new Date("6/14/2019"), "Insurance ADMIN.BY GWL", "", 300, "Health & Fitness - Dentist", "", "", ""],

12✔

756

            ["Royal Bank of Canada", new Date("6/17/2019"), "Interac purchase - 0238 BAMIYAN KABOB", "", -12.98, "Food & Dining - Restaurants", "", "", ""],

12✔

757

            ["Royal Bank of Canada", new Date("6/17/2019"), "Interac purchase - 1236 NADIM'S NO FRIL", "", -33.32, "Food & Dining - Groceries", "", "", ""],

12✔

758

            ["Royal Bank of Canada", new Date("6/17/2019"), "Deposit ONLINE TRANSFER", "", 12000, "Transfer - Savings acct", "", "", ""],

12✔

759

            ["Royal Bank of Canada", new Date("6/18/2019"), "MBNA-MASTERCARD", "", -1100, "Transfer - CC", "", "", ""],

12✔

760

            ["MBNA Mastercard", new Date("6/19/2019"), "PAYMENT", "", 1100, "Transfer - Savings acct", "", "", ""],

12✔

761

            ["Royal Bank of Canada", new Date("6/19/2019"), "Utility Bill Pmt Enbridge Gas", "", -108, "Utilities - Heating (Gas)", "", "", ""],

12✔

762

            ["MBNA Mastercard", new Date("6/20/2019"), "JOE'S NO FRILLS 3141 WHITBY ON", "", -41.77, "Food & Dining - Groceries", "", "", ""],

12✔

763

            ["MBNA Mastercard", new Date("6/20/2019"), "PIONEER STN#200 WHITBY ON", "", -28.17, "Auto - Fuel", "", "", ""],

12✔

764

            ["MBNA Mastercard", new Date("6/20/2019"), "AVIVA GENERAL INSURANC MARKHAM ON", "", -137.93, "Utilities - Insurance", "", "", ""],

12✔

765

            ["MBNA Mastercard", new Date("6/20/2019"), "AVIVA GENERAL INSURANC MARKHAM ON", "", -307.73, "Auto - Insurance", "", "", ""],

12✔

766

            ["Royal Bank of Canada", new Date("6/20/2019"), "Misc Payment Archdiocese TO", "", -22, "Gifts & Donations - Donations", "", "", ""],

12✔

767

            ["Royal Bank of Canada", new Date("6/20/2019"), "ELEXICON-WHITBY", "", -95, "Utilities - Electricity", "", "", ""],

12✔

768

            ["Royal Bank of Canada", new Date("6/20/2019"), "WHITBY TAXES", "", -100, "Taxes - Property Tax", "", "", ""]

12✔

769

];

12✔

770

12✔

771

1✔

772

    yearlySalesTable() {

1✔

773

        return [

1✔

774

            ["Name", "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"],

1✔

775

            ["Chris", 50, "", "", "", 60, "", "", "", "", "", "", ""],

1✔

776

            ["Fred", "", "", "", "", "", "", 20, 30, "", "", "", ""],

1✔

777

            ["Dan", "", "", "", "", "", 10, 20, 31, "", "", "", ""],

1✔

778

            ["Kev", "", 10, 20, "", 60, "", "", "", "", "", "", ""],

1✔

779

            ["Dori", "", "", "", "", "", "", "", "", "", "", "", 50],

1✔

780

            ["Gab", 50, "", "", "", 60, "", "", 10, "20", "", "", ""]

1✔

781

1✔

782

1✔

783

1✔

784

    selectAll1() {

1✔

785

        return this.selectAllAuthors("selectAll1", "select * from authors");

1✔

786

1✔

787

1✔

788

    selectAllCase1() {

1✔

789

        return this.selectAllAuthors("selectAllCase1", "Select * from authors");

1✔

790

1✔

791

1✔

792

    selectIsNotNull1() {

1✔

793

        return this.selectAllAuthors("selectIsNotNull1", "select * from authors where id is not null");

1✔

794

1✔

795

1✔

796

    selectAllAuthors(functionName, stmt) {

1✔

797

        let data = new TestSql()

3✔

798

            .addTableData("authors", this.authorsTable())

3✔

799

            .enableColumnTitle(true)

3✔

800

            .execute(stmt);

3✔

801

3✔

802

        let expected = [["AUTHORS.ID", "AUTHORS.FIRST_NAME", "AUTHORS.LAST_NAME"],

3✔

803

        ["11", "Ellen", "Writer"],

3✔

804

        ["12", "Olga", "Savelieva"],

3✔

805

        ["13", "Jack", "Smart"],

3✔

806

        ["14", "Donald", "Brain"],

3✔

807

        ["15", "Yao", "Dou"]];

3✔

808

3✔

809

        return this.isEqual(functionName, data, expected);

3✔

810

3✔

811

1✔

812

    selectIsNull1() {

1✔

813

        let stmt = "select * from authors where id is null";

1✔

814

1✔

815

        let data = new TestSql()

1✔

816

            .addTableData("authors", this.authorsTable())

1✔

817

            .enableColumnTitle(true)

1✔

818

            .execute(stmt);

1✔

819

1✔

820

        let expected = [["AUTHORS.ID", "AUTHORS.FIRST_NAME", "AUTHORS.LAST_NAME"]];

1✔

821

1✔

822

        return this.isEqual("selectIsNull1", data, expected);

1✔

823

1✔

824

1✔

825

1✔

826

    innerJoin1a() {

1✔

827

        let stmt = "SELECT books.id, books.title, authors.first_name, authors.last_name " +

1✔

828

            "FROM books " +

1✔

829

            "INNER JOIN authors " +

1✔

830

            "ON books.author_id = authors.id " +

1✔

831

            "ORDER BY books.id";

1✔

832

1✔

833

        return this.innerJoin1(stmt, "innerJoin1a");

1✔

834

1✔

835

1✔

836

    innerJoin1case() {

1✔

837

        let stmt = "SELECT books.id, books.title, authors.first_name, authors.last_name " +

1✔

838

            "FROM books " +

1✔

839

            "Inner Join authors " +

1✔

840

            "ON books.author_id = authors.id " +

1✔

841

            "ORDER BY books.id";

1✔

842

1✔

843

        return this.innerJoin1(stmt, "innerJoin1case");

1✔

844

1✔

845

1✔

846

    innerJoin1(stmt, funcName) {

1✔

847

        let data = new TestSql()

2✔

848

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

2✔

849

            .addTableData("authors", this.authorsTable())

2✔

850

            .enableColumnTitle(true)

2✔

851

            .execute(stmt);

2✔

852

2✔

853

        let expected = [["books.id", "books.title", "authors.first_name", "authors.last_name"],

2✔

854

        ["1", "Time to Grow Up!", "Ellen", "Writer"],

2✔

855

        ["2", "Your Trip", "Yao", "Dou"],

2✔

856

        ["3", "Lovely Love", "Donald", "Brain"],

2✔

857

        ["4", "Dream Your Life", "Ellen", "Writer"],

2✔

858

        ["5", "Oranges", "Olga", "Savelieva"],

2✔

859

        ["6", "Your Happy Life", "Yao", "Dou"],

2✔

860

        ["7", "Applied AI", "Jack", "Smart"],

2✔

861

        ["8", "My Last Book", "Ellen", "Writer"]];

2✔

862

2✔

863

        return this.isEqual(funcName, data, expected);

2✔

864

2✔

865

1✔

866

    innerJoin2() {

1✔

867

        let stmt = "SELECT books.id, books.title, books.type, authors.last_name, " +

1✔

868

            "translators.last_name " +

1✔

869

            "FROM books " +

1✔

870

            "INNER JOIN authors " +

1✔

871

            "ON books.author_id = authors.id " +

1✔

872

            "INNER JOIN translators " +

1✔

873

            "ON books.translator_id = translators.id " +

1✔

874

            "ORDER BY books.id";

1✔

875

1✔

876

        let data = new TestSql()

1✔

877

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

1✔

878

            .addTableData("translators", this.translatorsTable())

1✔

879

            .addTableData("authors", this.authorsTable())

1✔

880

            .enableColumnTitle(true)

1✔

881

            .execute(stmt);

1✔

882

1✔

883

        let expected = [["books.id", "books.title", "books.type", "authors.last_name", "translators.last_name"],

1✔

884

        ["2", "Your Trip", "translated", "Dou", "Weng"],

1✔

885

        ["5", "Oranges", "translated", "Savelieva", "Davies"],

1✔

886

        ["6", "Your Happy Life", "translated", "Dou", "Green"],

1✔

887

        ["7", "Applied AI", "translated", "Smart", "Edwards"]];

1✔

888

1✔

889

        return this.isEqual("innerJoin2", data, expected);

1✔

890

1✔

891

1✔

892

    innerJoinAlias1() {

1✔

893

        let stmt = "SELECT b.id, b.title, a.first_name, a.last_name " +

1✔

894

            "FROM books as b " +

1✔

895

            "INNER JOIN authors as a " +

1✔

896

            "ON b.author_id = a.id " +

1✔

897

            "ORDER BY books.id";

1✔

898

1✔

899

        let data = new TestSql()

1✔

900

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

1✔

901

            .addTableData("authors", this.authorsTable())

1✔

902

            .enableColumnTitle(true)

1✔

903

            .execute(stmt);

1✔

904

1✔

905

        let expected = [["b.id", "b.title", "a.first_name", "a.last_name"],

1✔

906

        ["1", "Time to Grow Up!", "Ellen", "Writer"],

1✔

907

        ["2", "Your Trip", "Yao", "Dou"],

1✔

908

        ["3", "Lovely Love", "Donald", "Brain"],

1✔

909

        ["4", "Dream Your Life", "Ellen", "Writer"],

1✔

910

        ["5", "Oranges", "Olga", "Savelieva"],

1✔

911

        ["6", "Your Happy Life", "Yao", "Dou"],

1✔

912

        ["7", "Applied AI", "Jack", "Smart"],

1✔

913

        ["8", "My Last Book", "Ellen", "Writer"]];

1✔

914

1✔

915

        return this.isEqual("innerJoinAlias1", data, expected);

1✔

916

1✔

917

1✔

918

    innerJoinAlias2() {

1✔

919

        let stmt = "SELECT b.id, b.title, a.first_name, a.last_name " +

1✔

920

            "FROM books as b " +

1✔

921

            "INNER JOIN authors as a " +

1✔

922

            "ON b.author_id = a.id " +

1✔

923

            "ORDER BY books.id";

1✔

924

1✔

925

        let testSQL = new TestSql()

1✔

926

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

1✔

927

            .addTableData("authors", this.authorsTable())

1✔

928

            .enableColumnTitle(true);

1✔

929

1✔

930

        let data = testSQL.execute(stmt);

1✔

931

1✔

932

        let expected = [["b.id", "b.title", "a.first_name", "a.last_name"],

1✔

933

        ["1", "Time to Grow Up!", "Ellen", "Writer"],

1✔

934

        ["2", "Your Trip", "Yao", "Dou"],

1✔

935

        ["3", "Lovely Love", "Donald", "Brain"],

1✔

936

        ["4", "Dream Your Life", "Ellen", "Writer"],

1✔

937

        ["5", "Oranges", "Olga", "Savelieva"],

1✔

938

        ["6", "Your Happy Life", "Yao", "Dou"],

1✔

939

        ["7", "Applied AI", "Jack", "Smart"],

1✔

940

        ["8", "My Last Book", "Ellen", "Writer"]];

1✔

941

1✔

942

        this.isEqual("innerJoinAlias2a", data, expected);

1✔

943

1✔

944

        stmt = "SELECT b1.id, b1.title, a2.first_name, a2.last_name " +

1✔

945

            "FROM books as b1 " +

1✔

946

            "INNER JOIN authors as a2 " +

1✔

947

            "ON b1.author_id = a2.id " +

1✔

948

            "ORDER BY books.id";

1✔

949

        data = testSQL.execute(stmt);

1✔

950

1✔

951

        expected = [["b1.id", "b1.title", "a2.first_name", "a2.last_name"],

1✔

952

        ["1", "Time to Grow Up!", "Ellen", "Writer"],

1✔

953

        ["2", "Your Trip", "Yao", "Dou"],

1✔

954

        ["3", "Lovely Love", "Donald", "Brain"],

1✔

955

        ["4", "Dream Your Life", "Ellen", "Writer"],

1✔

956

        ["5", "Oranges", "Olga", "Savelieva"],

1✔

957

        ["6", "Your Happy Life", "Yao", "Dou"],

1✔

958

        ["7", "Applied AI", "Jack", "Smart"],

1✔

959

        ["8", "My Last Book", "Ellen", "Writer"]];

1✔

960

1✔

961

        return this.isEqual("innerJoinAlias2b", data, expected);

1✔

962

1✔

963

1✔

964

    join2a() {

1✔

965

        let stmt = "SELECT books.id, books.title, books.type, translators.last_name  " +

1✔

966

            "FROM books " +

1✔

967

            "JOIN translators " +

1✔

968

            "ON books.translator_id = translators.id " +

1✔

969

            "ORDER BY books.id";

1✔

970

1✔

971

        return this.join2(stmt, "join2a");

1✔

972

1✔

973

    join2b() {

1✔

974

        let stmt = "sElEcT books.id, books.title, books.type, translators.last_name  " +

1✔

975

            "froM books " +

1✔

976

            "Join translators " +

1✔

977

            "On books.translator_id = translators.id " +

1✔

978

            "ORDEr  By books.id";

1✔

979

1✔

980

        return this.join2(stmt, "join2b");

1✔

981

1✔

982

1✔

983

    join2(stmt, funcName) {

1✔

984

2✔

985

        let data = new TestSql()

2✔

986

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

2✔

987

            .addTableData("translators", this.translatorsTable())

2✔

988

            .enableColumnTitle(true)

2✔

989

            .execute(stmt)

2✔

990

2✔

991

        let expected = [["books.id", "books.title", "books.type", "translators.last_name"],

2✔

992

        ["2", "Your Trip", "translated", "Weng"],

2✔

993

        ["5", "Oranges", "translated", "Davies"],

2✔

994

        ["6", "Your Happy Life", "translated", "Green"],

2✔

995

        ["7", "Applied AI", "translated", "Edwards"],

2✔

996

        ["9", "Book with Mysterious Author", "translated", "Edwards"]];

2✔

997

2✔

998

        return this.isEqual(funcName, data, expected);

2✔

999

2✔

1000

1✔

1001

    join3() {

1✔

1002

        let stmt = "SELECT books.id, books.title, editors.last_name " +

1✔

1003

            "FROM books " +

1✔

1004

            "LEFT JOIN editors " +

1✔

1005

            "ON books.editor_id = editors.id " +

1✔

1006

            "ORDER BY books.id";

1✔

1007

1✔

1008

        let data = new TestSql()

1✔

1009

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

1✔

1010

            .addTableData("editors", this.editorsTable())

1✔

1011

            .enableColumnTitle(true)

1✔

1012

            .execute(stmt);

1✔

1013

1✔

1014

        let expected = [["books.id", "books.title", "editors.last_name"],

1✔

1015

        ["1", "Time to Grow Up!", "Brown"],

1✔

1016

        ["2", "Your Trip", "Johnson"],

1✔

1017

        ["3", "Lovely Love", "Roberts"],

1✔

1018

        ["4", "Dream Your Life", "Roberts"],

1✔

1019

        ["5", "Oranges", "Wright"],

1✔

1020

        ["6", "Your Happy Life", "Johnson"],

1✔

1021

        ["7", "Applied AI", "Evans"],

1✔

1022

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

1✔

1023

        ["9", "Book with Mysterious Author", "Evans"]];

1✔

1024

1✔

1025

        return this.isEqual("join3", data, expected);

1✔

1026

1✔

1027

1✔

1028

    joinLimit1() {

1✔

1029

        let stmt = "SELECT books.id, books.title, editors.last_name " +

1✔

1030

            "FROM books " +

1✔

1031

            "LEFT JOIN editors " +

1✔

1032

            "ON books.editor_id = editors.id " +

1✔

1033

            "ORDER BY books.id " +

1✔

1034

            "LIMIT 5";

1✔

1035

1✔

1036

        let data = new TestSql()

1✔

1037

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

1✔

1038

            .addTableData("editors", this.editorsTable())

1✔

1039

            .enableColumnTitle(true)

1✔

1040

            .execute(stmt);

1✔

1041

1✔

1042

        let expected = [["books.id", "books.title", "editors.last_name"],

1✔

1043

        ["1", "Time to Grow Up!", "Brown"],

1✔

1044

        ["2", "Your Trip", "Johnson"],

1✔

1045

        ["3", "Lovely Love", "Roberts"],

1✔

1046

        ["4", "Dream Your Life", "Roberts"],

1✔

1047

        ["5", "Oranges", "Wright"]];

1✔

1048

1✔

1049

        return this.isEqual("joinLimit1", data, expected);

1✔

1050

1✔

1051

1✔

1052

    leftJoin1() {

1✔

1053

        let stmt = "SELECT books.id, books.title, books.type, authors.last_name, " +

1✔

1054

            "translators.last_name " +

1✔

1055

            "FROM books " +

1✔

1056

            "LEFT JOIN authors " +

1✔

1057

            "ON books.author_id = authors.id " +

1✔

1058

            "LEFT JOIN translators " +

1✔

1059

            "ON books.translator_id = translators.id " +

1✔

1060

            "ORDER BY books.id";

1✔

1061

1✔

1062

        let data = new TestSql()

1✔

1063

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

1✔

1064

            .addTableData("translators", this.translatorsTable())

1✔

1065

            .addTableData("authors", this.authorsTable())

1✔

1066

            .enableColumnTitle(true)

1✔

1067

            .execute(stmt);

1✔

1068

1✔

1069

        let expected = [["books.id", "books.title", "books.type", "authors.last_name", "translators.last_name"],

1✔

1070

        ["1", "Time to Grow Up!", "original", "Writer", ""],

1✔

1071

        ["2", "Your Trip", "translated", "Dou", "Weng"],

1✔

1072

        ["3", "Lovely Love", "original", "Brain", ""],

1✔

1073

        ["4", "Dream Your Life", "original", "Writer", ""],

1✔

1074

        ["5", "Oranges", "translated", "Savelieva", "Davies"],

1✔

1075

        ["6", "Your Happy Life", "translated", "Dou", "Green"],

1✔

1076

        ["7", "Applied AI", "translated", "Smart", "Edwards"],

1✔

1077

        ["8", "My Last Book", "original", "Writer", ""],

1✔

1078

        ["9", "Book with Mysterious Author", "translated", "", "Edwards"]];

1✔

1079

1✔

1080

        return this.isEqual("leftJoin1", data, expected);

1✔

1081

1✔

1082

1✔

1083

    rightJoin1() {

1✔

1084

        let stmt = "SELECT books.id, books.title, editors.last_name, editors.id  " +

1✔

1085

            "FROM books " +

1✔

1086

            "RIGHT JOIN editors " +

1✔

1087

            "ON books.editor_id = editors.id " +

1✔

1088

            "ORDER BY books.id";

1✔

1089

1✔

1090

        let data = new TestSql()

1✔

1091

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

1✔

1092

            .addTableData("editors", this.editorsTable())

1✔

1093

            .enableColumnTitle(true)

1✔

1094

            .execute(stmt);

1✔

1095

1✔

1096

        let expected = [["books.id", "books.title", "editors.last_name", "editors.id"],

1✔

1097

        ["", "", "Smart", "13"],

1✔

1098

        ["", "", "Jones", "26"],

1✔

1099

        ["", "", "Smith", "27"],

1✔

1100

        ["", "", "Dumb", "50"],

1✔

1101

        ["", "", "Smart", "51"],

1✔

1102

        ["1", "Time to Grow Up!", "Brown", "21"],

1✔

1103

        ["2", "Your Trip", "Johnson", "22"],

1✔

1104

        ["3", "Lovely Love", "Roberts", "24"],

1✔

1105

        ["4", "Dream Your Life", "Roberts", "24"],

1✔

1106

        ["5", "Oranges", "Wright", "25"],

1✔

1107

        ["6", "Your Happy Life", "Johnson", "22"],

1✔

1108

        ["7", "Applied AI", "Evans", "23"],

1✔

1109

        ["9", "Book with Mysterious Author", "Evans", "23"]];

1✔

1110

1✔

1111

        return this.isEqual("rightJoin1", data, expected);

1✔

1112

1✔

1113

1✔

1114

    rightJoin2() {

1✔

1115

        let stmt = "SELECT books.id, books.title, books.translator_id, " +

1✔

1116

            "editors.last_name, editors.id,  " +

1✔

1117

            "translators.last_name " +

1✔

1118

            "FROM books " +

1✔

1119

            "RIGHT JOIN editors " +

1✔

1120

            "ON books.editor_id = editors.id " +

1✔

1121

            "RIGHT JOIN translators " +

1✔

1122

            "ON books.translator_id = translators.id " +

1✔

1123

            "ORDER BY books.id";

1✔

1124

1✔

1125

        let data = new TestSql()

1✔

1126

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

1✔

1127

            .addTableData("translators", this.translatorsTable())

1✔

1128

            .addTableData("editors", this.editorsTable())

1✔

1129

            .enableColumnTitle(true)

1✔

1130

            .execute(stmt);

1✔

1131

1✔

1132

        let expected = [["books.id", "books.title", "books.translator_id", "editors.last_name", "editors.id", "translators.last_name"],

1✔

1133

        ["2", "Your Trip", "32", "Johnson", "22", "Weng"],

1✔

1134

        ["5", "Oranges", "31", "Wright", "25", "Davies"],

1✔

1135

        ["6", "Your Happy Life", "33", "Johnson", "22", "Green"],

1✔

1136

        ["7", "Applied AI", "34", "Evans", "23", "Edwards"],

1✔

1137

        ["9", "Book with Mysterious Author", "34", "Evans", "23", "Edwards"]];

1✔

1138

1✔

1139

        return this.isEqual("rightJoin2", data, expected);

1✔

1140

1✔

1141

1✔

1142

    fullJoin1() {

1✔

1143

        let stmt = "SELECT authors.id, authors.last_name, editors.id, editors.last_name " +

1✔

1144

            "FROM authors " +

1✔

1145

            "FULL JOIN editors " +

1✔

1146

            "ON authors.id = editors.id ";

1✔

1147

1✔

1148

        let data = new TestSql()

1✔

1149

            .addTableData("authors", this.authorsTable())

1✔

1150

            .addTableData("editors", this.editorsTable())

1✔

1151

            .enableColumnTitle(true)

1✔

1152

            .execute(stmt);

1✔

1153

1✔

1154

        let expected = [["authors.id", "authors.last_name", "editors.id", "editors.last_name"],

1✔

1155

        ["11", "Writer", "", ""],

1✔

1156

        ["12", "Savelieva", "", ""],

1✔

1157

        ["13", "Smart", "13", "Smart"],

1✔

1158

        ["14", "Brain", "", ""],

1✔

1159

        ["15", "Dou", "", ""],

1✔

1160

        ["", "", "21", "Brown"],

1✔

1161

        ["", "", "22", "Johnson"],

1✔

1162

        ["", "", "23", "Evans"],

1✔

1163

        ["", "", "24", "Roberts"],

1✔

1164

        ["", "", "25", "Wright"],

1✔

1165

        ["", "", "26", "Jones"],

1✔

1166

        ["", "", "27", "Smith"],

1✔

1167

        ["", "", "50", "Dumb"],

1✔

1168

        ["", "", "51", "Smart"]];

1✔

1169

1✔

1170

        return this.isEqual("fullJoin1", data, expected);

1✔

1171

1✔

1172

1✔

1173

    fullJoin2() {

1✔

1174

        let stmt = "SELECT *, customers.address, customers.id, customers.name " +

1✔

1175

            "FROM booksales " +

1✔

1176

            "FULL JOIN customers " +

1✔

1177

            "ON booksales.customer_id = customers.id ";

1✔

1178

1✔

1179

        let data = new TestSql()

1✔

1180

            .addTableData("booksales", this.bookSalesTable())

1✔

1181

            .addTableData("customers", this.customerTable())

1✔

1182

            .enableColumnTitle(true)

1✔

1183

            .execute(stmt);

1✔

1184

1✔

1185

        let expected = [["BOOKSALES.INVOICE", "BOOKSALES.BOOK_ID", "BOOKSALES.CUSTOMER_ID", "BOOKSALES.QUANTITY", "BOOKSALES.PRICE", "BOOKSALES.DATE", "customers.address", "customers.id", "customers.name"],

1✔

1186

        ["I7200", "9", "C1", 10, 34.95, "05/01/2022", "101 One Way", "C1", "Numereo Uno"],

1✔

1187

        ["I7201", "8", "C2", 3, 29.95, "05/01/2022", "202 Second St.", "C2", "Dewy Tuesdays"],

1✔

1188

        ["I7201", "7", "C2", 5, 18.99, "05/01/2022", "202 Second St.", "C2", "Dewy Tuesdays"],

1✔

1189

        ["I7202", "9", "C3", 1, 59.99, "05/02/2022", "3 Way St", "C3", "Tres Buon Goods"],

1✔

1190

        ["I7203", "1", "", 1, 90, "05/02/2022", "", "", ""],

1✔

1191

        ["I7204", "2", "C4", 100, 65.49, "05/03/2022", "40 Four St", "C4", "ForMe Resellers"],

1✔

1192

        ["I7204", "3", "C4", 150, 24.95, "05/03/2022", "40 Four St", "C4", "ForMe Resellers"],

1✔

1193

        ["I7204", "4", "C4", 50, 19.99, "05/03/2022", "40 Four St", "C4", "ForMe Resellers"],

1✔

1194

        ["I7205", "7", "C1", 1, 33.97, "05/04/2022", "101 One Way", "C1", "Numereo Uno"],

1✔

1195

        ["I7206", "7", "C2", 100, 17.99, "05/04/2022", "202 Second St.", "C2", "Dewy Tuesdays"],

1✔

1196

        ["", "", "", "", "", "", "5 ohFive St.", "C5", "Fe Fi Fo Giant Tiger"],

1✔

1197

        ["", "", "", "", "", "", "6 Seventh St", "C6", "Sx in Cars"],

1✔

1198

        ["", "", "", "", "", "", "7 Eight Crt.", "C7", "7th Heaven"]];

1✔

1199

1✔

1200

        return this.isEqual("fullJoin2", data, expected);

1✔

1201

1✔

1202

1✔

1203

    fullJoin3() {

1✔

1204

        let stmt = "SELECT *, customers.address, customers.id, customers.name, books.id, books.title " +

1✔

1205

            "FROM booksales " +

1✔

1206

            "FULL JOIN customers " +

1✔

1207

            "ON booksales.customer_id = customers.id " +

1✔

1208

            "FULL JOIN books " +

1✔

1209

            "ON booksales.Book_Id = books.id";

1✔

1210

1✔

1211

        let data = new TestSql()

1✔

1212

            .addTableData("booksales", this.bookSalesTable())

1✔

1213

            .addTableData("customers", this.customerTable())

1✔

1214

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

1✔

1215

            .enableColumnTitle(true)

1✔

1216

            .execute(stmt);

1✔

1217

1✔

1218

        let expected = [["BOOKSALES.INVOICE", "BOOKSALES.BOOK_ID", "BOOKSALES.CUSTOMER_ID", "BOOKSALES.QUANTITY", "BOOKSALES.PRICE", "BOOKSALES.DATE", "customers.address", "customers.id", "customers.name", "books.id", "books.title"],

1✔

1219

        ["I7200", "9", "C1", 10, 34.95, "05/01/2022", "101 One Way", "C1", "Numereo Uno", "9", "Book with Mysterious Author"],

1✔

1220

        ["I7201", "8", "C2", 3, 29.95, "05/01/2022", "202 Second St.", "C2", "Dewy Tuesdays", "8", "My Last Book"],

1✔

1221

        ["I7201", "7", "C2", 5, 18.99, "05/01/2022", "202 Second St.", "C2", "Dewy Tuesdays", "7", "Applied AI"],

1✔

1222

        ["I7202", "9", "C3", 1, 59.99, "05/02/2022", "3 Way St", "C3", "Tres Buon Goods", "9", "Book with Mysterious Author"],

1✔

1223

        ["I7203", "1", "", 1, 90, "05/02/2022", "", "", "", "1", "Time to Grow Up!"],

1✔

1224

        ["I7204", "2", "C4", 100, 65.49, "05/03/2022", "40 Four St", "C4", "ForMe Resellers", "2", "Your Trip"],

1✔

1225

        ["I7204", "3", "C4", 150, 24.95, "05/03/2022", "40 Four St", "C4", "ForMe Resellers", "3", "Lovely Love"],

1✔

1226

        ["I7204", "4", "C4", 50, 19.99, "05/03/2022", "40 Four St", "C4", "ForMe Resellers", "4", "Dream Your Life"],

1✔

1227

        ["I7205", "7", "C1", 1, 33.97, "05/04/2022", "101 One Way", "C1", "Numereo Uno", "7", "Applied AI"],

1✔

1228

        ["I7206", "7", "C2", 100, 17.99, "05/04/2022", "202 Second St.", "C2", "Dewy Tuesdays", "7", "Applied AI"],

1✔

1229

        ["", "", "", "", "", "", "5 ohFive St.", "C5", "Fe Fi Fo Giant Tiger", "", ""],

1✔

1230

        ["", "", "", "", "", "", "6 Seventh St", "C6", "Sx in Cars", "", ""],

1✔

1231

        ["", "", "", "", "", "", "7 Eight Crt.", "C7", "7th Heaven", "", ""],

1✔

1232

        ["", "", "", "", "", "", "", "", "", "5", "Oranges"],

1✔

1233

        ["", "", "", "", "", "", "", "", "", "6", "Your Happy Life"]];

1✔

1234

1✔

1235

        return this.isEqual("fullJoin3", data, expected);

1✔

1236

1✔

1237

1✔

1238

    whereIn1() {

1✔

1239

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

1✔

1240

            "FROM books " +

1✔

1241

            "WHERE books.author_id IN (SELECT id from authors)" +

1✔

1242

            "ORDER BY books.title";

1✔

1243

1✔

1244

        let data = new TestSql()

1✔

1245

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

1✔

1246

            .addTableData("authors", this.authorsTable())

1✔

1247

            .enableColumnTitle(true)

1✔

1248

            .execute(stmt);

1✔

1249

1✔

1250

        let expected = [["books.id", "books.title", "books.author_id"],

1✔

1251

        ["7", "Applied AI", "13"],

1✔

1252

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

1✔

1253

        ["3", "Lovely Love", "14"],

1✔

1254

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

1✔

1255

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

1✔

1256

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

1✔

1257

        ["6", "Your Happy Life", "15"],

1✔

1258

        ["2", "Your Trip", "15"]];

1✔

1259

1✔

1260

        return this.isEqual("whereIn1", data, expected);

1✔

1261

1✔

1262

1✔

1263

    whereIn2() {

1✔

1264

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

1✔

1265

            "FROM books " +

1✔

1266

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

1✔

1267

            "ORDER BY books.title";

1✔

1268

1✔

1269

        let data = new TestSql()

1✔

1270

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

1✔

1271

            .enableColumnTitle(true)

1✔

1272

            .execute(stmt);

1✔

1273

1✔

1274

        let expected = [["books.id", "books.title", "books.author_id"],

1✔

1275

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

1✔

1276

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

1✔

1277

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

1✔

1278

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

1✔

1279

1✔

1280

        return this.isEqual("whereIn2", data, expected);

1✔

1281

1✔

1282

1✔

1283

    whereIn3() {

1✔

1284

        let stmt = "SELECT id, title, author_id " +

1✔

1285

            "FROM books " +

1✔

1286

            "WHERE author_id IN (select id from authors where first_name like '%ald') " +

1✔

1287

            "ORDER BY title";

1✔

1288

1✔

1289

        let data = new TestSql()

1✔

1290

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

1✔

1291

            .addTableData("authors", this.authorsTable())

1✔

1292

            .enableColumnTitle(true)

1✔

1293

            .execute(stmt);

1✔

1294

1✔

1295

        let expected = [["id", "title", "author_id"],

1✔

1296

        ["3", "Lovely Love", "14"]];

1✔

1297

1✔

1298

        return this.isEqual("whereIn3", data, expected);

1✔

1299

1✔

1300

1✔

1301

    whereIn4() {

1✔

1302

        let stmt = "SELECT * " +

1✔

1303

            "FROM books " +

1✔

1304

            "WHERE author_id IN (select id from authors where first_name = ?1) " +

1✔

1305

            "or editor_id in (select id from editors where last_name = ?2) " +

1✔

1306

            "or title = ?3 " +

1✔

1307

            "ORDER BY title";

1✔

1308

1✔

1309

        let data = new TestSql()

1✔

1310

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

1✔

1311

            .addTableData("authors", this.authorsTable())

1✔

1312

            .addTableData("editors", this.editorsTable())

1✔

1313

            .enableColumnTitle(true)

1✔

1314

            .addBindParameter('Donald')

1✔

1315

            .addBindParameter('Roberts')

1✔

1316

            .addBindParameter('Oranges')

1✔

1317

            .execute(stmt);

1✔

1318

1✔

1319

        let expected = [["BOOKS.ID", "BOOKS.TITLE", "BOOKS.TYPE", "BOOKS.AUTHOR_ID", "BOOKS.EDITOR_ID", "BOOKS.TRANSLATOR_ID"],

1✔

1320

        ["4", "Dream Your Life", "original", "11", "24", ""],

1✔

1321

        ["3", "Lovely Love", "original", "14", "24", ""],

1✔

1322

        ["5", "Oranges", "translated", "12", "25", "31"]];

1✔

1323

1✔

1324

        return this.isEqual("whereIn4", data, expected);

1✔

1325

1✔

1326

1✔

1327

    whereIn5() {

1✔

1328

        let stmt = "SELECT * " +

1✔

1329

            "FROM books " +

1✔

1330

            "WHERE author_id IN (select a.id from authors as a where first_name = ?1) " +

1✔

1331

            "or editor_id in (select e.id from editors as e where last_name = ?2) " +

1✔

1332

            "or title = ?3 " +

1✔

1333

            "ORDER BY title";

1✔

1334

1✔

1335

        let data = new TestSql()

1✔

1336

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

1✔

1337

            .addTableData("authors", this.authorsTable())

1✔

1338

            .addTableData("editors", this.editorsTable())

1✔

1339

            .enableColumnTitle(true)

1✔

1340

            .addBindParameter('Donald')

1✔

1341

            .addBindParameter('Roberts')

1✔

1342

            .addBindParameter('Oranges')

1✔

1343

            .execute(stmt);

1✔

1344

1✔

1345

        let expected = [["BOOKS.ID", "BOOKS.TITLE", "BOOKS.TYPE", "BOOKS.AUTHOR_ID", "BOOKS.EDITOR_ID", "BOOKS.TRANSLATOR_ID"],

1✔

1346

        ["4", "Dream Your Life", "original", "11", "24", ""],

1✔

1347

        ["3", "Lovely Love", "original", "14", "24", ""],

1✔

1348

        ["5", "Oranges", "translated", "12", "25", "31"]];

1✔

1349

1✔

1350

        return this.isEqual("whereIn5", data, expected);

1✔

1351

1✔

1352

1✔

1353

    whereIn6() {

1✔

1354

        let stmt = "SELECT * " +

1✔

1355

            "FROM editors " +

1✔

1356

            "WHERE first_name IN (' Mark ', 'Maria    ', 'CATHRINE  ', '  jacK') ";

1✔

1357

1✔

1358

        let data = new TestSql()

1✔

1359

            .addTableData("editors", this.editorsTable())

1✔

1360

            .enableColumnTitle(true)

1✔

1361

            .execute(stmt);

1✔

1362

1✔

1363

        let expected = [["EDITORS.ID", "EDITORS.FIRST_NAME", "EDITORS.LAST_NAME"],

1✔

1364

        ["22", "Mark", "Johnson"],

1✔

1365

        ["23", "Maria", "Evans"]];

1✔

1366

1✔

1367

        return this.isEqual("whereIn6", data, expected);

1✔

1368

1✔

1369

1✔

1370

1✔

1371

    whereIn7() {

1✔

1372

        let stmt = "select * from booksales where quantity in (select quantity from booksales where price < 30)";

1✔

1373

1✔

1374

        let data = new TestSql()

1✔

1375

            .addTableData("booksales", this.bookSalesTable())

1✔

1376

            .enableColumnTitle(true)

1✔

1377

            .execute(stmt);

1✔

1378

1✔

1379

        let expected = [["BOOKSALES.INVOICE", "BOOKSALES.BOOK_ID", "BOOKSALES.CUSTOMER_ID", "BOOKSALES.QUANTITY", "BOOKSALES.PRICE", "BOOKSALES.DATE"],

1✔

1380

        ["I7201", "8", "C2", 3, 29.95, "05/01/2022"],

1✔

1381

        ["I7201", "7", "C2", 5, 18.99, "05/01/2022"],

1✔

1382

        ["I7204", "2", "C4", 100, 65.49, "05/03/2022"],

1✔

1383

        ["I7204", "3", "C4", 150, 24.95, "05/03/2022"],

1✔

1384

        ["I7204", "4", "C4", 50, 19.99, "05/03/2022"],

1✔

1385

        ["I7206", "7", "C2", 100, 17.99, "05/04/2022"]];

1✔

1386

1✔

1387

        return this.isEqual("whereIn7", data, expected);

1✔

1388

1✔

1389

1✔

1390

    whereNotIn1() {

1✔

1391

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

1✔

1392

            "FROM books " +

1✔

1393

            "WHERE books.author_id NOT IN (SELECT id from authors)" +

1✔

1394

            "ORDER BY books.title";

1✔

1395

1✔

1396

        let data = new TestSql()

1✔

1397

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

1✔

1398

            .addTableData("authors", this.authorsTable())

1✔

1399

            .enableColumnTitle(true)

1✔

1400

            .execute(stmt);

1✔

1401

1✔

1402

        let expected = [["books.id", "books.title", "books.author_id"],

1✔

1403

        ["9", "Book with Mysterious Author", "1"]];

1✔

1404

1✔

1405

        return this.isEqual("whereNotIn1", data, expected);

1✔

1406

1✔

1407

1✔

1408

    whereAndOr1() {

1✔

1409

        let stmt = "select * from bookSales where date > '05/01/2022' AND date < '05/04/2022' OR book_id = '9'";

1✔

1410

1✔

1411

        let data = new TestSql()

1✔

1412

            .addTableData("bookSales", this.bookSalesTable())

1✔

1413

            .enableColumnTitle(true)

1✔

1414

            .execute(stmt);

1✔

1415

1✔

1416

        let expected = [["BOOKSALES.INVOICE", "BOOKSALES.BOOK_ID", "BOOKSALES.CUSTOMER_ID", "BOOKSALES.QUANTITY", "BOOKSALES.PRICE", "BOOKSALES.DATE"],

1✔

1417

        ["I7202", "9", "C3", 1, 59.99, "05/02/2022"],

1✔

1418

        ["I7203", "1", "", 1, 90, "05/02/2022"],

1✔

1419

        ["I7204", "2", "C4", 100, 65.49, "05/03/2022"],

1✔

1420

        ["I7204", "3", "C4", 150, 24.95, "05/03/2022"],

1✔

1421

        ["I7204", "4", "C4", 50, 19.99, "05/03/2022"],

1✔

1422

        ["I7200", "9", "C1", 10, 34.95, "05/01/2022"]];

1✔

1423

1✔

1424

        return this.isEqual("whereAndOr1", data, expected);

1✔

1425

1✔

1426

1✔

1427

    whereAndOr2() {

1✔

1428

        let stmt = "select * from bookSales where date > ?1 AND date < ?2 OR book_id = ?3";

1✔

1429

1✔

1430

        let data = new TestSql()

1✔

1431

            .addTableData("bookSales", this.bookSalesTable())

1✔

1432

            .enableColumnTitle(true)

1✔

1433

            .addBindParameter('05/01/2022')

1✔

1434

            .addBindParameter('05/04/2022')

1✔

1435

            .addBindParameter('9')

1✔

1436

            .execute(stmt);

1✔

1437

1✔

1438

        let expected = [["BOOKSALES.INVOICE", "BOOKSALES.BOOK_ID", "BOOKSALES.CUSTOMER_ID", "BOOKSALES.QUANTITY", "BOOKSALES.PRICE", "BOOKSALES.DATE"],

1✔

1439

        ["I7202", "9", "C3", 1, 59.99, "05/02/2022"],

1✔

1440

        ["I7203", "1", "", 1, 90, "05/02/2022"],

1✔

1441

        ["I7204", "2", "C4", 100, 65.49, "05/03/2022"],

1✔

1442

        ["I7204", "3", "C4", 150, 24.95, "05/03/2022"],

1✔

1443

        ["I7204", "4", "C4", 50, 19.99, "05/03/2022"],

1✔

1444

        ["I7200", "9", "C1", 10, 34.95, "05/01/2022"]];

1✔

1445

1✔

1446

        return this.isEqual("whereAndOr2", data, expected);

1✔

1447

1✔

1448

1✔

1449

    whereAndOr3() {

1✔

1450

        let stmt = "select * from bookSales where date > ?1 AND date < ?2 OR book_id = ?3";

1✔

1451

1✔

1452

        let startDate = new Date();

1✔

1453

        startDate.setDate(1);

1✔

1454

        startDate.setMonth(4);

1✔

1455

        startDate.setFullYear(2022);

1✔

1456

1✔

1457

        let data = new TestSql()

1✔

1458

            .addTableData("bookSales", this.bookSalesTable())

1✔

1459

            .enableColumnTitle(true)

1✔

1460

            .addBindParameter(startDate)

1✔

1461

            .addBindParameter('05/04/2022')

1✔

1462

            .addBindParameter('9')

1✔

1463

            .execute(stmt);

1✔

1464

1✔

1465

        let expected = [["BOOKSALES.INVOICE", "BOOKSALES.BOOK_ID", "BOOKSALES.CUSTOMER_ID", "BOOKSALES.QUANTITY", "BOOKSALES.PRICE", "BOOKSALES.DATE"],

1✔

1466

        ["I7202", "9", "C3", 1, 59.99, "05/02/2022"],

1✔

1467

        ["I7203", "1", "", 1, 90, "05/02/2022"],

1✔

1468

        ["I7204", "2", "C4", 100, 65.49, "05/03/2022"],

1✔

1469

        ["I7204", "3", "C4", 150, 24.95, "05/03/2022"],

1✔

1470

        ["I7204", "4", "C4", 50, 19.99, "05/03/2022"],

1✔

1471

        ["I7200", "9", "C1", 10, 34.95, "05/01/2022"]];

1✔

1472

1✔

1473

        return this.isEqual("whereAndOr3", data, expected);

1✔

1474

1✔

1475

1✔

1476

    whereAndNotEqual2() {

1✔

1477

        let stmt = "select * from bookSales where date >= ?1 AND date <= ?2 And book_id <> ?3";

1✔

1478

        let func = "whereAndNotEqual2";

1✔

1479

        return this.whereAndNotEqual2base(stmt, func);

1✔

1480

1✔

1481

1✔

1482

    whereAndNotEqual3() {

1✔

1483

        let stmt = "select * from bookSales where date>=?1 AND date<=?2 And book_id<>?3";

1✔

1484

        let func = "whereAndNotEqual3";

1✔

1485

        return this.whereAndNotEqual2base(stmt, func);

1✔

1486

1✔

1487

    whereAndNotEqual2base(stmt, func) {

1✔

1488

2✔

1489

        let data = new TestSql()

2✔

1490

            .addTableData("bookSales", this.bookSalesTable())

2✔

1491

            .enableColumnTitle(true)

2✔

1492

            .addBindParameter('05/01/2022')

2✔

1493

            .addBindParameter('05/04/2022')

2✔

1494

            .addBindParameter('9')

2✔

1495

            .execute(stmt);

2✔

1496

2✔

1497

        let expected = [["BOOKSALES.INVOICE", "BOOKSALES.BOOK_ID", "BOOKSALES.CUSTOMER_ID", "BOOKSALES.QUANTITY", "BOOKSALES.PRICE", "BOOKSALES.DATE"],

2✔

1498

        ["I7201", "8", "C2", 3, 29.95, "05/01/2022"],

2✔

1499

        ["I7201", "7", "C2", 5, 18.99, "05/01/2022"],

2✔

1500

        ["I7203", "1", "", 1, 90, "05/02/2022"],

2✔

1501

        ["I7204", "2", "C4", 100, 65.49, "05/03/2022"],

2✔

1502

        ["I7204", "3", "C4", 150, 24.95, "05/03/2022"],

2✔

1503

        ["I7204", "4", "C4", 50, 19.99, "05/03/2022"],

2✔

1504

        ["I7205", "7", "C1", 1, 33.97, "05/04/2022"],

2✔

1505

        ["I7206", "7", "C2", 100, 17.99, "05/04/2022"]];

2✔

1506

2✔

1507

        return this.isEqual(func, data, expected);

2✔

1508

2✔

1509

1✔

1510

    selectAgainNewBinds1() {

1✔

1511

        let stmt = "select * from bookSales where date > ?1 AND date < ?2 OR book_id = ?3";

1✔

1512

1✔

1513

        let sqlObj = new TestSql()

1✔

1514

            .addTableData("bookSales", this.bookSalesTable())

1✔

1515

            .enableColumnTitle(true)

1✔

1516

            .addBindParameter('05/01/2022')

1✔

1517

            .addBindParameter('05/04/2022')

1✔

1518

            .addBindParameter('9');

1✔

1519

1✔

1520

        let data = sqlObj.execute(stmt);

1✔

1521

1✔

1522

        let expected = [["BOOKSALES.INVOICE", "BOOKSALES.BOOK_ID", "BOOKSALES.CUSTOMER_ID", "BOOKSALES.QUANTITY", "BOOKSALES.PRICE", "BOOKSALES.DATE"],

1✔

1523

        ["I7202", "9", "C3", 1, 59.99, "05/02/2022"],

1✔

1524

        ["I7203", "1", "", 1, 90, "05/02/2022"],

1✔

1525

        ["I7204", "2", "C4", 100, 65.49, "05/03/2022"],

1✔

1526

        ["I7204", "3", "C4", 150, 24.95, "05/03/2022"],

1✔

1527

        ["I7204", "4", "C4", 50, 19.99, "05/03/2022"],

1✔

1528

        ["I7200", "9", "C1", 10, 34.95, "05/01/2022"]];

1✔

1529

1✔

1530

        let result = this.isEqual("selectAgainNewBinds1a", data, expected);

1✔

1531

1✔

1532

        data = sqlObj.clearBindParameters()

1✔

1533

            .addBindParameter('05/02/2022')

1✔

1534

            .addBindParameter('05/04/2022')

1✔

1535

            .addBindParameter('9')

1✔

1536

            .execute(stmt);

1✔

1537

1✔

1538

        expected = [["BOOKSALES.INVOICE", "BOOKSALES.BOOK_ID", "BOOKSALES.CUSTOMER_ID", "BOOKSALES.QUANTITY", "BOOKSALES.PRICE", "BOOKSALES.DATE"],

1✔

1539

        ["I7204", "2", "C4", 100, 65.49, "05/03/2022"],

1✔

1540

        ["I7204", "3", "C4", 150, 24.95, "05/03/2022"],

1✔

1541

        ["I7204", "4", "C4", 50, 19.99, "05/03/2022"],

1✔

1542

        ["I7200", "9", "C1", 10, 34.95, "05/01/2022"],

1✔

1543

        ["I7202", "9", "C3", 1, 59.99, "05/02/2022"]];

1✔

1544

1✔

1545

        return result && this.isEqual("selectAgainNewBinds1b", data, expected);

1✔

1546

1✔

1547

1✔

1548

    groupBy1() {

1✔

1549

        let stmt = "select bookSales.book_id, SUM(bookSales.Quantity) from bookSales group by book_id";

1✔

1550

1✔

1551

        let data = new TestSql()

1✔

1552

            .addTableData("bookSales", this.bookSalesTable())

1✔

1553

            .enableColumnTitle(true)

1✔

1554

            .execute(stmt);

1✔

1555

1✔

1556

        let expected = [["bookSales.book_id", "SUM(bookSales.Quantity)"],

1✔

1557

        ["1", 1],

1✔

1558

        ["2", 100],

1✔

1559

        ["3", 150],

1✔

1560

        ["4", 50],

1✔

1561

        ["7", 106],

1✔

1562

        ["8", 3],

1✔

1563

        ["9", 11]];

1✔

1564

1✔

1565

        return this.isEqual("groupBy1", data, expected);

1✔

1566

1✔

1567

1✔

1568

    groupBy2() {

1✔

1569

        let stmt =

1✔

1570

            "select bookSales.customer_id, SUM(bookSales.quantity) FROM booksales " +

1✔

1571

            "GROUP BY booksales.customer_id HAVING SUM(bookSales.quantity) > 11";

1✔

1572

1✔

1573

        let data = new TestSql()

1✔

1574

            .addTableData("bookSales", this.bookSalesTable())

1✔

1575

            .enableColumnTitle(true)

1✔

1576

            .execute(stmt);

1✔

1577

1✔

1578

        let expected = [["bookSales.customer_id", "SUM(bookSales.quantity)"],

1✔

1579

        ["C2", 108],

1✔

1580

        ["C4", 300]];

1✔

1581

1✔

1582

        return this.isEqual("groupBy2", data, expected);

1✔

1583

1✔

1584

1✔

1585

    groupBy3() {

1✔

1586

        let stmt =

1✔

1587

            "select bookSales.customer_id, date, SUM(bookSales.quantity) FROM booksales " +

1✔

1588

            "GROUP BY customer_id, date";

1✔

1589

1✔

1590

        let data = new TestSql()

1✔

1591

            .addTableData("bookSales", this.bookSalesTable())

1✔

1592

            .enableColumnTitle(true)

1✔

1593

            .execute(stmt);

1✔

1594

1✔

1595

        let expected = [["bookSales.customer_id", "date", "SUM(bookSales.quantity)"],

1✔

1596

        ["", "05/02/2022", 1],

1✔

1597

        ["C1", "05/01/2022", 10],

1✔

1598

        ["C1", "05/04/2022", 1],

1✔

1599

        ["C2", "05/01/2022", 8],

1✔

1600

        ["C2", "05/04/2022", 100],

1✔

1601

        ["C3", "05/02/2022", 1],

1✔

1602

        ["C4", "05/03/2022", 300]];

1✔

1603

1✔

1604

        return this.isEqual("groupBy3", data, expected);

1✔

1605

1✔

1606

1✔

1607

    groupBy4() {

1✔

1608

        let stmt =

1✔

1609

            "select bookSales.customer_id, date, count(customer_id), count(date) FROM booksales " +

1✔

1610

            "GROUP BY customer_id, date";

1✔

1611

1✔

1612

        let data = new TestSql()

1✔

1613

            .addTableData("bookSales", this.bookSalesTable())

1✔

1614

            .enableColumnTitle(true)

1✔

1615

            .execute(stmt);

1✔

1616

1✔

1617

        let expected = [["bookSales.customer_id", "date", "count(customer_id)", "count(date)"],

1✔

1618

        ["", "05/02/2022", 1, 1],

1✔

1619

        ["C1", "05/01/2022", 1, 1],

1✔

1620

        ["C1", "05/04/2022", 1, 1],

1✔

1621

        ["C2", "05/01/2022", 2, 2],

1✔

1622

        ["C2", "05/04/2022", 1, 1],

1✔

1623

        ["C3", "05/02/2022", 1, 1],

1✔

1624

        ["C4", "05/03/2022", 3, 3]];

1✔

1625

1✔

1626

        return this.isEqual("groupBy4", data, expected);

1✔

1627

1✔

1628

1✔

1629

1✔

1630

    avgSelect1() {

1✔

1631

        let stmt = "select AVG(quantity) from booksales";

1✔

1632

1✔

1633

        let data = new TestSql()

1✔

1634

            .addTableData("bookSales", this.bookSalesTable())

1✔

1635

            .enableColumnTitle(true)

1✔

1636

            .execute(stmt);

1✔

1637

1✔

1638

        let expected = [["AVG(quantity)"], [42.1]];

1✔

1639

1✔

1640

        return this.isEqual("avgSelect1", data, expected);

1✔

1641

1✔

1642

1✔

1643

    funcsSelect2() {

1✔

1644

        let stmt = "select AVG(quantity), MIN(quantity), MAX(quantity), SUM(quantity), COUNT(quantity) from booksales";

1✔

1645

1✔

1646

        let data = new TestSql()

1✔

1647

            .addTableData("bookSales", this.bookSalesTable())

1✔

1648

            .enableColumnTitle(true)

1✔

1649

            .execute(stmt);

1✔

1650

1✔

1651

        let expected = [["AVG(quantity)", "MIN(quantity)", "MAX(quantity)", "SUM(quantity)", "COUNT(quantity)"],

1✔

1652

        [42.1, 1, 150, 421, 10]];

1✔

1653

1✔

1654

        return this.isEqual("funcsSelect2", data, expected);

1✔

1655

1✔

1656

1✔

1657

    innerSelect1() {

1✔

1658

        let stmt = "SELECT *, customers.name FROM bookSales " +

1✔

1659

            "LEFT JOIN customers ON bookSales.customer_ID = customers.ID " +

1✔

1660

            "WHERE bookSales.quantity > (select AVG(quantity) from booksales)";

1✔

1661

1✔

1662

        let data = new TestSql()

1✔

1663

            .addTableData("bookSales", this.bookSalesTable())

1✔

1664

            .addTableData("customers", this.customerTable())

1✔

1665

            .enableColumnTitle(true)

1✔

1666

            .execute(stmt);

1✔

1667

1✔

1668

        let expected = [["BOOKSALES.INVOICE", "BOOKSALES.BOOK_ID", "BOOKSALES.CUSTOMER_ID", "BOOKSALES.QUANTITY", "BOOKSALES.PRICE", "BOOKSALES.DATE", "customers.name"],

1✔

1669

        ["I7204", "2", "C4", 100, 65.49, "05/03/2022", "ForMe Resellers"],

1✔

1670

        ["I7204", "3", "C4", 150, 24.95, "05/03/2022", "ForMe Resellers"],

1✔

1671

        ["I7204", "4", "C4", 50, 19.99, "05/03/2022", "ForMe Resellers"],

1✔

1672

        ["I7206", "7", "C2", 100, 17.99, "05/04/2022", "Dewy Tuesdays"]];

1✔

1673

1✔

1674

        return this.isEqual("innerSelect1", data, expected);

1✔

1675

1✔

1676

1✔

1677

    whereLike1() {

1✔

1678

        let stmt = "select *, books.title, authors.first_name, editors.first_name, customers.name, customers.email, booksales.quantity from bookSales " +

1✔

1679

            "LEFT JOIN books ON booksales.book_id = books.id " +

1✔

1680

            "LEFT JOIN authors on books.author_id = authors.id " +

1✔

1681

            "LEFT JOIN editors on books.editor_id = editors.id " +

1✔

1682

            "LEFT JOIN customers on bookSales.customer_id = customers.id " +

1✔

1683

            "WHERE customers.email LIKE '%gmail.com'";

1✔

1684

1✔

1685

        let data = new TestSql()

1✔

1686

            .addTableData("bookSales", this.bookSalesTable())

1✔

1687

            .addTableData("customers", this.customerTable())

1✔

1688

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

1✔

1689

            .addTableData("editors", this.editorsTable())

1✔

1690

            .addTableData("authors", this.authorsTable())

1✔

1691

            .enableColumnTitle(true)

1✔

1692

            .execute(stmt);

1✔

1693

1✔

1694

        let expected = [["BOOKSALES.INVOICE", "BOOKSALES.BOOK_ID", "BOOKSALES.CUSTOMER_ID", "BOOKSALES.QUANTITY", "BOOKSALES.PRICE", "BOOKSALES.DATE", "books.title", "authors.first_name", "editors.first_name", "customers.name", "customers.email", "booksales.quantity"],

1✔

1695

        ["I7200", "9", "C1", 10, 34.95, "05/01/2022", "Book with Mysterious Author", "", "Maria", "Numereo Uno", "bigOne@gmail.com", 10],

1✔

1696

        ["I7201", "8", "C2", 3, 29.95, "05/01/2022", "My Last Book", "Ellen", "", "Dewy Tuesdays", "twoguys@gmail.com", 3],

1✔

1697

        ["I7201", "7", "C2", 5, 18.99, "05/01/2022", "Applied AI", "Jack", "Maria", "Dewy Tuesdays", "twoguys@gmail.com", 5],

1✔

1698

        ["I7205", "7", "C1", 1, 33.97, "05/04/2022", "Applied AI", "Jack", "Maria", "Numereo Uno", "bigOne@gmail.com", 1],

1✔

1699

        ["I7206", "7", "C2", 100, 17.99, "05/04/2022", "Applied AI", "Jack", "Maria", "Dewy Tuesdays", "twoguys@gmail.com", 100]];

1✔

1700

1✔

1701

        return this.isEqual("whereLike1", data, expected);

1✔

1702

1✔

1703

1✔

1704

    whereLike2() {

1✔

1705

        let stmt = "select *, books.title as Title, auth.first_name as [First Name], editors.first_name, customers.name, customers.email, booksales.quantity from bookSales as sale" +

1✔

1706

            "LEFT JOIN books as bk ON sale.book_id = bk.id " +

1✔

1707

            "LEFT JOIN authors as auth on books.author_id = authors.id " +

1✔

1708

            "LEFT JOIN editors as ed on books.editor_id = ed.id " +

1✔

1709

            "LEFT JOIN customers on bookSales.customer_id = customers.id " +

1✔

1710

            "WHERE customers.email LIKE '%gmail.com'";

1✔

1711

1✔

1712

        let data = new TestSql()

1✔

1713

            .addTableData("bookSales", this.bookSalesTable())

1✔

1714

            .addTableData("customers", this.customerTable())

1✔

1715

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

1✔

1716

            .addTableData("editors", this.editorsTable())

1✔

1717

            .addTableData("authors", this.authorsTable())

1✔

1718

            .enableColumnTitle(true)

1✔

1719

            .execute(stmt);

1✔

1720

1✔

1721

        let expected = [["BOOKSALES.INVOICE", "BOOKSALES.BOOK_ID", "BOOKSALES.CUSTOMER_ID", "BOOKSALES.QUANTITY", "BOOKSALES.PRICE", "BOOKSALES.DATE", "Title", "First Name", "editors.first_name", "customers.name", "customers.email", "booksales.quantity"],

1✔

1722

        ["I7200", "9", "C1", 10, 34.95, "05/01/2022", "Book with Mysterious Author", "", "Maria", "Numereo Uno", "bigOne@gmail.com", 10],

1✔

1723

        ["I7201", "8", "C2", 3, 29.95, "05/01/2022", "My Last Book", "Ellen", "", "Dewy Tuesdays", "twoguys@gmail.com", 3],

1✔

1724

        ["I7201", "7", "C2", 5, 18.99, "05/01/2022", "Applied AI", "Jack", "Maria", "Dewy Tuesdays", "twoguys@gmail.com", 5],

1✔

1725

        ["I7205", "7", "C1", 1, 33.97, "05/04/2022", "Applied AI", "Jack", "Maria", "Numereo Uno", "bigOne@gmail.com", 1],

1✔

1726

        ["I7206", "7", "C2", 100, 17.99, "05/04/2022", "Applied AI", "Jack", "Maria", "Dewy Tuesdays", "twoguys@gmail.com", 100]];

1✔

1727

1✔

1728

        return this.isEqual("whereLike2", data, expected);

1✔

1729

1✔

1730

1✔

1731

    whereNotLike1() {

1✔

1732

        let stmt = "select *, books.title, authors.first_name, editors.first_name, customers.name, customers.email, booksales.quantity from bookSales " +

1✔

1733

            "LEFT JOIN books ON booksales.book_id = books.id " +

1✔

1734

            "LEFT JOIN authors on books.author_id = authors.id " +

1✔

1735

            "LEFT JOIN editors on books.editor_id = editors.id " +

1✔

1736

            "LEFT JOIN customers on bookSales.customer_id = customers.id " +

1✔

1737

            "WHERE customers.email NOT LIKE '%gmail.com'";

1✔

1738

1✔

1739

        let data = new TestSql()

1✔

1740

            .addTableData("bookSales", this.bookSalesTable())

1✔

1741

            .addTableData("customers", this.customerTable())

1✔

1742

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

1✔

1743

            .addTableData("editors", this.editorsTable())

1✔

1744

            .addTableData("authors", this.authorsTable())

1✔

1745

            .enableColumnTitle(true)

1✔

1746

            .execute(stmt);

1✔

1747

1✔

1748

        let expected = [["BOOKSALES.INVOICE", "BOOKSALES.BOOK_ID", "BOOKSALES.CUSTOMER_ID", "BOOKSALES.QUANTITY", "BOOKSALES.PRICE", "BOOKSALES.DATE", "books.title", "authors.first_name", "editors.first_name", "customers.name", "customers.email", "booksales.quantity"],

1✔

1749

        ["I7202", "9", "C3", 1, 59.99, "05/02/2022", "Book with Mysterious Author", "", "Maria", "Tres Buon Goods", "thrice@hotmail.com", 1],

1✔

1750

        ["I7203", "1", "", 1, 90, "05/02/2022", "Time to Grow Up!", "Ellen", "Daniel", "", "", 1],

1✔

1751

        ["I7204", "2", "C4", 100, 65.49, "05/03/2022", "Your Trip", "Yao", "Mark", "ForMe Resellers", "fourtimes@hotmail.com", 100],

1✔

1752

        ["I7204", "3", "C4", 150, 24.95, "05/03/2022", "Lovely Love", "Donald", "Cathrine", "ForMe Resellers", "fourtimes@hotmail.com", 150],

1✔

1753

        ["I7204", "4", "C4", 50, 19.99, "05/03/2022", "Dream Your Life", "Ellen", "Cathrine", "ForMe Resellers", "fourtimes@hotmail.com", 50]];

1✔

1754

1✔

1755

        return this.isEqual("whereNotLike1", data, expected);

1✔

1756

1✔

1757

1✔

1758

    union1() {

1✔

1759

        let stmt = "select * from authors UNION select * from editors";

1✔

1760

1✔

1761

        let data = new TestSql()

1✔

1762

            .addTableData("authors", this.authorsTable())

1✔

1763

            .addTableData("editors", this.editorsTable())

1✔

1764

            .enableColumnTitle(true)

1✔

1765

            .execute(stmt);

1✔

1766

1✔

1767

        let expected = [["AUTHORS.ID", "AUTHORS.FIRST_NAME", "AUTHORS.LAST_NAME"],

1✔

1768

        ["11", "Ellen", "Writer"],

1✔

1769

        ["12", "Olga", "Savelieva"],

1✔

1770

        ["13", "Jack", "Smart"],

1✔

1771

        ["14", "Donald", "Brain"],

1✔

1772

        ["15", "Yao", "Dou"],

1✔

1773

        ["21", "Daniel", "Brown"],

1✔

1774

        ["22", "Mark", "Johnson"],

1✔

1775

        ["23", "Maria", "Evans"],

1✔

1776

        ["24", "Cathrine", "Roberts"],

1✔

1777

        ["25", "Sebastian", "Wright"],

1✔

1778

        ["26", "Barbara", "Jones"],

1✔

1779

        ["27", "Matthew", "Smith"],

1✔

1780

        ["50", "Jack", "Dumb"],

1✔

1781

        ["51", "Daniel", "Smart"]];

1✔

1782

1✔

1783

        return this.isEqual("union1", data, expected);

1✔

1784

1✔

1785

1✔

1786

    unionAlias1() {

1✔

1787

        let stmt = "select a.id, a.first_name, a.last_name from authors as a UNION select e.id, e.first_name, e.last_name from editors as e";

1✔

1788

1✔

1789

        let data = new TestSql()

1✔

1790

            .addTableData("authors", this.authorsTable())

1✔

1791

            .addTableData("editors", this.editorsTable())

1✔

1792

            .enableColumnTitle(true)

1✔

1793

            .execute(stmt);

1✔

1794

1✔

1795

        let expected = [["a.id", "a.first_name", "a.last_name"],

1✔

1796

        ["11", "Ellen", "Writer"],

1✔

1797

        ["12", "Olga", "Savelieva"],

1✔

1798

        ["13", "Jack", "Smart"],

1✔

1799

        ["14", "Donald", "Brain"],

1✔

1800

        ["15", "Yao", "Dou"],

1✔

1801

        ["21", "Daniel", "Brown"],

1✔

1802

        ["22", "Mark", "Johnson"],

1✔

1803

        ["23", "Maria", "Evans"],

1✔

1804

        ["24", "Cathrine", "Roberts"],

1✔

1805

        ["25", "Sebastian", "Wright"],

1✔

1806

        ["26", "Barbara", "Jones"],

1✔

1807

        ["27", "Matthew", "Smith"],

1✔

1808

        ["50", "Jack", "Dumb"],

1✔

1809

        ["51", "Daniel", "Smart"]];

1✔

1810

1✔

1811

        return this.isEqual("unionAlias1", data, expected);

1✔

1812

1✔

1813

1✔

1814

    unionBind1() {

1✔

1815

        let stmt = "select * from authors where id = ?1 UNION select * from editors where id = ?2 UNION select * from translators where id = ?3";

1✔

1816

1✔

1817

        let data = new TestSql()

1✔

1818

            .addTableData("authors", this.authorsTable())

1✔

1819

            .addTableData("editors", this.editorsTable())

1✔

1820

            .addTableData("translators", this.translatorsTable())

1✔

1821

            .enableColumnTitle(true)

1✔

1822

            .addBindParameter('15')

1✔

1823

            .addBindParameter('51')

1✔

1824

            .addBindParameter('31')

1✔

1825

            .execute(stmt);

1✔

1826

1✔

1827

        let expected = [["AUTHORS.ID", "AUTHORS.FIRST_NAME", "AUTHORS.LAST_NAME"],

1✔

1828

        ["15", "Yao", "Dou"],

1✔

1829

        ["51", "Daniel", "Smart"],

1✔

1830

        ["31", "Ira", "Davies"]];

1✔

1831

1✔

1832

        return this.isEqual("unionBind1", data, expected);

1✔

1833

1✔

1834

1✔

1835

    unionAll1() {

1✔

1836

        let stmt = "select * from authors UNION ALL select * from editors";

1✔

1837

1✔

1838

        let data = new TestSql()

1✔

1839

            .addTableData("authors", this.authorsTable())

1✔

1840

            .addTableData("editors", this.editorsTable())

1✔

1841

            .enableColumnTitle(true)

1✔

1842

            .execute(stmt);

1✔

1843

1✔

1844

        let expected = [["AUTHORS.ID", "AUTHORS.FIRST_NAME", "AUTHORS.LAST_NAME"],

1✔

1845

        ["11", "Ellen", "Writer"],

1✔

1846

        ["12", "Olga", "Savelieva"],

1✔

1847

        ["13", "Jack", "Smart"],

1✔

1848

        ["14", "Donald", "Brain"],

1✔

1849

        ["15", "Yao", "Dou"],

1✔

1850

        ["13", "Jack", "Smart"],

1✔

1851

        ["21", "Daniel", "Brown"],

1✔

1852

        ["22", "Mark", "Johnson"],

1✔

1853

        ["23", "Maria", "Evans"],

1✔

1854

        ["24", "Cathrine", "Roberts"],

1✔

1855

        ["25", "Sebastian", "Wright"],

1✔

1856

        ["26", "Barbara", "Jones"],

1✔

1857

        ["27", "Matthew", "Smith"],

1✔

1858

        ["50", "Jack", "Dumb"],

1✔

1859

        ["51", "Daniel", "Smart"]];

1✔

1860

1✔

1861

        return this.isEqual("unionAll1", data, expected);

1✔

1862

1✔

1863

1✔

1864

    unionAll2() {

1✔

1865

        let stmt = "select * from authors UNION ALL select * from editors UNION ALL select * from translators";

1✔

1866

1✔

1867

        let data = new TestSql()

1✔

1868

            .addTableData("authors", this.authorsTable())

1✔

1869

            .addTableData("editors", this.editorsTable())

1✔

1870

            .addTableData("translators", this.translatorsTable())

1✔

1871

            .enableColumnTitle(true)

1✔

1872

            .execute(stmt);

1✔

1873

1✔

1874

        let expected = [["AUTHORS.ID", "AUTHORS.FIRST_NAME", "AUTHORS.LAST_NAME"],

1✔

1875

        ["11", "Ellen", "Writer"],

1✔

1876

        ["12", "Olga", "Savelieva"],

1✔

1877

        ["13", "Jack", "Smart"],

1✔

1878

        ["14", "Donald", "Brain"],

1✔

1879

        ["15", "Yao", "Dou"],

1✔

1880

        ["13", "Jack", "Smart"],

1✔

1881

        ["21", "Daniel", "Brown"],

1✔

1882

        ["22", "Mark", "Johnson"],

1✔

1883

        ["23", "Maria", "Evans"],

1✔

1884

        ["24", "Cathrine", "Roberts"],

1✔

1885

        ["25", "Sebastian", "Wright"],

1✔

1886

        ["26", "Barbara", "Jones"],

1✔

1887

        ["27", "Matthew", "Smith"],

1✔

1888

        ["50", "Jack", "Dumb"],

1✔

1889

        ["51", "Daniel", "Smart"],

1✔

1890

        ["31", "Ira", "Davies"],

1✔

1891

        ["32", "Ling", "Weng"],

1✔

1892

        ["33", "Kristian", "Green"],

1✔

1893

        ["34", "Roman", "Edwards"]];

1✔

1894

1✔

1895

        return this.isEqual("unionAll2", data, expected);

1✔

1896

1✔

1897

1✔

1898

    unionJoin1() {

1✔

1899

        let stmt = "select booksales.invoice as 'Invoice', booksales.quantity as 'Quantity', booksales.price as 'Price', booksales.quantity * booksales.price as 'Sales', booksales.date, books.title, customers.name, authors.first_name + ' ' + authors.last_name as 'Author', translators.first_name + ' ' + translators.last_name as 'Translator', editors.first_name + ' ' + editors.last_name as 'Editor' " +

1✔

1900

            "from booksales left join books on booksales.book_id = books.id " +

1✔

1901

            "left join customers on booksales.customer_id = customers.id " +

1✔

1902

            "left join authors on books.author_id = authors.id " +

1✔

1903

            "left join translators on books.translator_id = translators.id " +

1✔

1904

            "left join editors on books.editor_id = editors.id " +

1✔

1905

            "where booksales.date >= ?1 and booksales.date <= ?2 " +

1✔

1906

            "union all select 'Total', SUM(booksales.quantity), avg(booksales.price), SUM(booksales.price * booksales.quantity), '' ,'', '', '', '', '' from booksales " +

1✔

1907

            "where booksales.date >= ?3 and booksales.date <= ?4 ";

1✔

1908

1✔

1909

        let data = new TestSql()

1✔

1910

            .addTableData("authors", this.authorsTable())

1✔

1911

            .addTableData("editors", this.editorsTable())

1✔

1912

            .addTableData("translators", this.translatorsTable())

1✔

1913

            .addTableData("booksales", this.bookSalesTable())

1✔

1914

            .addTableData("customers", this.customerTable())

1✔

1915

            .addTableData("editors", this.editorsTable())

1✔

1916

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

1✔

1917

            .addBindParameter("05/01/2022")

1✔

1918

            .addBindParameter("05/02/2022")

1✔

1919

            .addBindParameter("05/01/2022")

1✔

1920

            .addBindParameter("05/02/2022")

1✔

1921

            .enableColumnTitle(true)

1✔

1922

            .execute(stmt);

1✔

1923

1✔

1924

        let expected = [["Invoice", "Quantity", "Price", "Sales", "booksales.date", "books.title", "customers.name", "Author", "Translator", "Editor"],

1✔

1925

        ["I7200", 10, 34.95, 349.5, "05/01/2022", "Book with Mysterious Author", "Numereo Uno", " ", "Roman Edwards", "Maria Evans"],

1✔

1926

        ["I7201", 3, 29.95, 89.85, "05/01/2022", "My Last Book", "Dewy Tuesdays", "Ellen Writer", " ", " "],

1✔

1927

        ["I7201", 5, 18.99, 94.94999999999999, "05/01/2022", "Applied AI", "Dewy Tuesdays", "Jack Smart", "Roman Edwards", "Maria Evans"],

1✔

1928

        ["I7202", 1, 59.99, 59.99, "05/02/2022", "Book with Mysterious Author", "Tres Buon Goods", " ", "Roman Edwards", "Maria Evans"],

1✔

1929

        ["I7203", 1, 90, 90, "05/02/2022", "Time to Grow Up!", "", "Ellen Writer", " ", "Daniel Brown"],

1✔

1930

        ["Total", 20, 46.775999999999996, 684.29, "", "", "", "", "", ""]];

1✔

1931

1✔

1932

        return this.isEqual("unionJoin1", data, expected);

1✔

1933

1✔

1934

1✔

1935

1✔

1936

    except1() {

1✔

1937

        let stmt = "select * from authors EXCEPT select * from authors where last_name like 'S%'";

1✔

1938

1✔

1939

        let data = new TestSql()

1✔

1940

            .addTableData("authors", this.authorsTable())

1✔

1941

            .addTableData("editors", this.editorsTable())

1✔

1942

            .enableColumnTitle(true)

1✔

1943

            .execute(stmt);

1✔

1944

1✔

1945

        let expected = [["AUTHORS.ID", "AUTHORS.FIRST_NAME", "AUTHORS.LAST_NAME"],

1✔

1946

        ["11", "Ellen", "Writer"],

1✔

1947

        ["14", "Donald", "Brain"],

1✔

1948

        ["15", "Yao", "Dou"]];

1✔

1949

1✔

1950

        return this.isEqual("except1", data, expected);

1✔

1951

1✔

1952

1✔

1953

    intersect1() {

1✔

1954

        let stmt = "select * from editors INTERSECT select * from authors";

1✔

1955

1✔

1956

        let data = new TestSql()

1✔

1957

            .addTableData("authors", this.authorsTable())

1✔

1958

            .addTableData("editors", this.editorsTable())

1✔

1959

            .enableColumnTitle(true)

1✔

1960

            .execute(stmt);

1✔

1961

1✔

1962

        let expected = [["EDITORS.ID", "EDITORS.FIRST_NAME", "EDITORS.LAST_NAME"],

1✔

1963

        ["13", "Jack", "Smart"]];

1✔

1964

1✔

1965

        return this.isEqual("intersect1", data, expected);

1✔

1966

1✔

1967

1✔

1968

    orderByDesc1() {

1✔

1969

        let stmt = "select * from bookSales order by DATE DESC";

1✔

1970

1✔

1971

        let data = new TestSql()

1✔

1972

            .addTableData("bookSales", this.bookSalesTable())

1✔

1973

            .enableColumnTitle(true)

1✔

1974

            .execute(stmt);

1✔

1975

1✔

1976

        let expected = [["BOOKSALES.INVOICE", "BOOKSALES.BOOK_ID", "BOOKSALES.CUSTOMER_ID", "BOOKSALES.QUANTITY", "BOOKSALES.PRICE", "BOOKSALES.DATE"],

1✔

1977

        ["I7205", "7", "C1", 1, 33.97, "05/04/2022"],

1✔

1978

        ["I7206", "7", "C2", 100, 17.99, "05/04/2022"],

1✔

1979

        ["I7204", "2", "C4", 100, 65.49, "05/03/2022"],

1✔

1980

        ["I7204", "3", "C4", 150, 24.95, "05/03/2022"],

1✔

1981

        ["I7204", "4", "C4", 50, 19.99, "05/03/2022"],

1✔

1982

        ["I7202", "9", "C3", 1, 59.99, "05/02/2022"],

1✔

1983

        ["I7203", "1", "", 1, 90, "05/02/2022"],

1✔

1984

        ["I7200", "9", "C1", 10, 34.95, "05/01/2022"],

1✔

1985

        ["I7201", "8", "C2", 3, 29.95, "05/01/2022"],

1✔

1986

        ["I7201", "7", "C2", 5, 18.99, "05/01/2022"]];

1✔

1987

1✔

1988

        return this.isEqual("orderByDesc1", data, expected);

1✔

1989

1✔

1990

1✔

1991

    orderByDesc2() {

1✔

1992

        let stmt = "select * from bookSales order by DATE DESC, PRICE ASC";

1✔

1993

1✔

1994

        let data = new TestSql()

1✔

1995

            .addTableData("bookSales", this.bookSalesTable())

1✔

1996

            .enableColumnTitle(true)

1✔

1997

            .execute(stmt);

1✔

1998

1✔

1999

        let expected = [["BOOKSALES.INVOICE", "BOOKSALES.BOOK_ID", "BOOKSALES.CUSTOMER_ID", "BOOKSALES.QUANTITY", "BOOKSALES.PRICE", "BOOKSALES.DATE"],

1✔

2000

        ["I7206", "7", "C2", 100, 17.99, "05/04/2022"],

1✔

2001

        ["I7205", "7", "C1", 1, 33.97, "05/04/2022"],

1✔

2002

        ["I7204", "4", "C4", 50, 19.99, "05/03/2022"],

1✔

2003

        ["I7204", "3", "C4", 150, 24.95, "05/03/2022"],

1✔

2004

        ["I7204", "2", "C4", 100, 65.49, "05/03/2022"],

1✔

2005

        ["I7202", "9", "C3", 1, 59.99, "05/02/2022"],

1✔

2006

        ["I7203", "1", "", 1, 90, "05/02/2022"],

1✔

2007

        ["I7201", "7", "C2", 5, 18.99, "05/01/2022"],

1✔

2008

        ["I7201", "8", "C2", 3, 29.95, "05/01/2022"],

1✔

2009

        ["I7200", "9", "C1", 10, 34.95, "05/01/2022"]];

1✔

2010

1✔

2011

        return this.isEqual("orderByDesc2", data, expected);

1✔

2012

1✔

2013

1✔

2014

    orderByDesc3() {

1✔

2015

        let stmt = "select * from customers where lower(city) like '%city%' order by email desc";

1✔

2016

1✔

2017

        let data = new TestSql()

1✔

2018

            .addTableData("customers", this.customerTable())

1✔

2019

            .enableColumnTitle(true)

1✔

2020

            .execute(stmt);

1✔

2021

1✔

2022

        let expected = [["CUSTOMERS.ID", "CUSTOMERS.NAME", "CUSTOMERS.ADDRESS", "CUSTOMERS.CITY", "CUSTOMERS.PHONE", "CUSTOMERS.EMAIL"],

1✔

2023

        ["C2", "Dewy Tuesdays", "202 Second St.", "Second City", "4162022222", "twoguys@gmail.com"],

1✔

2024

        ["C3", "Tres Buon Goods", "3 Way St", "Tres City", "5193133303", "thrice@hotmail.com"],

1✔

2025

        ["C6", "Sx in Cars", "6 Seventh St", "Sx City", "6661116666", "gotyourSix@hotmail.com   "],

1✔

2026

        ["C4", "ForMe Resellers", "40 Four St", "FourtNight City", "2894441234", "fourtimes@hotmail.com"],

1✔

2027

        ["C1", "Numereo Uno", "101 One Way", "One Point City", "9051112111", "bigOne@gmail.com"],

1✔

2028

        ["C7", "7th Heaven", "7 Eight Crt.", "Lucky City", "5551117777", " timesAcharm@gmail.com "]];

1✔

2029

1✔

2030

        return this.isEqual("orderByDesc3", data, expected);

1✔

2031

1✔

2032

1✔

2033

    distinct1() {

1✔

2034

        let stmt = "select distinct last_name from editors";

1✔

2035

1✔

2036

        let data = new TestSql()

1✔

2037

            .addTableData("editors", this.editorsTable())

1✔

2038

            .enableColumnTitle(true)

1✔

2039

            .execute(stmt);

1✔

2040

1✔

2041

        let expected = [["LAST_NAME"],

1✔

2042

        ["Brown"],

1✔

2043

        ["Dumb"],

1✔

2044

        ["Evans"],

1✔

2045

        ["Johnson"],

1✔

2046

        ["Jones"],

1✔

2047

        ["Roberts"],

1✔

2048

        ["Smart"],

1✔

2049

        ["Smith"],

1✔

2050

        ["Wright"]];

1✔

2051

1✔

2052

        return this.isEqual("distinct1", data, expected);

1✔

2053

1✔

2054

1✔

2055

    selectMath1() {

1✔

2056

        let stmt = "select book_id, -(quantity), price, Quantity * Price, booksales.quantity * booksales.price * 0.13, quantity % 2, ((quantity + 1) * price)/100  from bookSales";

1✔

2057

1✔

2058

        let data = new TestSql()

1✔

2059

            .addTableData("bookSales", this.bookSalesTable())

1✔

2060

            .enableColumnTitle(true)

1✔

2061

            .execute(stmt);

1✔

2062

1✔

2063

        let expected = [["book_id", "-(quantity)", "price", "Quantity * Price", "booksales.quantity * booksales.price * 0.13", "quantity % 2", "((quantity + 1) * price)/100"],

1✔

2064

        ["9", -10, 34.95, 349.5, 45.435, 0, 3.8445000000000005],

1✔

2065

        ["8", -3, 29.95, 89.85, 11.6805, 1, 1.198],

1✔

2066

        ["7", -5, 18.99, 94.94999999999999, 12.343499999999999, 1, 1.1394],

1✔

2067

        ["9", -1, 59.99, 59.99, 7.7987, 1, 1.1998],

1✔

2068

        ["1", -1, 90, 90, 11.700000000000001, 1, 1.8],

1✔

2069

        ["2", -100, 65.49, 6548.999999999999, 851.3699999999999, 0, 66.14489999999999],

1✔

2070

        ["3", -150, 24.95, 3742.5, 486.52500000000003, 0, 37.674499999999995],

1✔

2071

        ["4", -50, 19.99, 999.4999999999999, 129.935, 0, 10.194899999999999],

1✔

2072

        ["7", -1, 33.97, 33.97, 4.4161, 1, 0.6794],

1✔

2073

        ["7", -100, 17.99, 1798.9999999999998, 233.86999999999998, 0, 18.1699]];

1✔

2074

1✔

2075

        return this.isEqual("selectMath1", data, expected);

1✔

2076

1✔

2077

1✔

2078

    selectMathFunc1() {

1✔

2079

        let stmt = "select book_id, quantity, price, round(((quantity + 1) * price)/100), LEFT(invoice,3), RIGHT(invoice,4)  from bookSales";

1✔

2080

1✔

2081

        let data = new TestSql()

1✔

2082

            .addTableData("bookSales", this.bookSalesTable())

1✔

2083

            .enableColumnTitle(true)

1✔

2084

            .execute(stmt);

1✔

2085

1✔

2086

        let expected = [["book_id", "quantity", "price", "round(((quantity + 1) * price)/100)", "LEFT(invoice,3)", "RIGHT(invoice,4)"],

1✔

2087

        ["9", 10, 34.95, 4, "I72", "7200"],

1✔

2088

        ["8", 3, 29.95, 1, "I72", "7201"],

1✔

2089

        ["7", 5, 18.99, 1, "I72", "7201"],

1✔

2090

        ["9", 1, 59.99, 1, "I72", "7202"],

1✔

2091

        ["1", 1, 90, 2, "I72", "7203"],

1✔

2092

        ["2", 100, 65.49, 66, "I72", "7204"],

1✔

2093

        ["3", 150, 24.95, 38, "I72", "7204"],

1✔

2094

        ["4", 50, 19.99, 10, "I72", "7204"],

1✔

2095

        ["7", 1, 33.97, 1, "I72", "7205"],

1✔

2096

        ["7", 100, 17.99, 18, "I72", "7206"]];

1✔

2097

1✔

2098

        return this.isEqual("selectMathFunc1", data, expected);

1✔

2099

1✔

2100

1✔

2101

    selectMathFunc2() {

1✔

2102

        let stmt = "select book_id, quantity, price, ABS(quantity-10), CEILING(price), floor(price), log(quantity), log10(quantity), power(quantity, 2), sqrt(quantity)  from bookSales";

1✔

2103

1✔

2104

        let data = new TestSql()

1✔

2105

            .addTableData("bookSales", this.bookSalesTable())

1✔

2106

            .enableColumnTitle(true)

1✔

2107

            .execute(stmt);

1✔

2108

1✔

2109

        let expected = [["book_id", "quantity", "price", "ABS(quantity-10)", "CEILING(price)", "floor(price)", "log(quantity)", "log10(quantity)", "power(quantity, 2)", "sqrt(quantity)"],

1✔

2110

        ["9", 10, 34.95, 0, 35, 34, 3.321928094887362, 1, 100, 3.1622776601683795],

1✔

2111

        ["8", 3, 29.95, 7, 30, 29, 1.584962500721156, 0.47712125471966244, 9, 1.7320508075688772],

1✔

2112

        ["7", 5, 18.99, 5, 19, 18, 2.321928094887362, 0.6989700043360189, 25, 2.23606797749979],

1✔

2113

        ["9", 1, 59.99, 9, 60, 59, 0, 0, 1, 1],

1✔

2114

        ["1", 1, 90, 9, 90, 90, 0, 0, 1, 1],

1✔

2115

        ["2", 100, 65.49, 90, 66, 65, 6.643856189774724, 2, 10000, 10],

1✔

2116

        ["3", 150, 24.95, 140, 25, 24, 7.22881869049588, 2.1760912590556813, 22500, 12.24744871391589],

1✔

2117

        ["4", 50, 19.99, 40, 20, 19, 5.643856189774724, 1.6989700043360187, 2500, 7.0710678118654755],

1✔

2118

        ["7", 1, 33.97, 9, 34, 33, 0, 0, 1, 1], ["7", 100, 17.99, 90, 18, 17, 6.643856189774724, 2, 10000, 10]];

1✔

2119

1✔

2120

        return this.isEqual("selectMathFunc2", data, expected);

1✔

2121

1✔

2122

1✔

2123

    selectFuncs2() {

1✔

2124

        let stmt = "select name, address, LEN(name), LENGTH(address), lower(name), upper(address), trim(email), ltrim(email), rtrim(email) from customers";

1✔

2125

1✔

2126

        let data = new TestSql()

1✔

2127

            .addTableData("customers", this.customerTable())

1✔

2128

            .enableColumnTitle(true)

1✔

2129

            .execute(stmt);

1✔

2130

1✔

2131

        let expected = [["name", "address", "LEN(name)", "LENGTH(address)", "lower(name)", "upper(address)", "trim(email)", "ltrim(email)", "rtrim(email)"],

1✔

2132

        ["Numereo Uno", "101 One Way", 11, 11, "numereo uno", "101 ONE WAY", "bigOne@gmail.com", "bigOne@gmail.com", "bigOne@gmail.com"],

1✔

2133

        ["Dewy Tuesdays", "202 Second St.", 13, 14, "dewy tuesdays", "202 SECOND ST.", "twoguys@gmail.com", "twoguys@gmail.com", "twoguys@gmail.com"],

1✔

2134

        ["Tres Buon Goods", "3 Way St", 15, 8, "tres buon goods", "3 WAY ST", "thrice@hotmail.com", "thrice@hotmail.com", "thrice@hotmail.com"],

1✔

2135

        ["ForMe Resellers", "40 Four St", 15, 10, "forme resellers", "40 FOUR ST", "fourtimes@hotmail.com", "fourtimes@hotmail.com", "fourtimes@hotmail.com"],

1✔

2136

        ["Fe Fi Fo Giant Tiger", "5 ohFive St.", 20, 12, "fe fi fo giant tiger", "5 OHFIVE ST.", "fiver@gmail.com", "fiver@gmail.com", "   fiver@gmail.com"],

1✔

2137

        ["Sx in Cars", "6 Seventh St", 10, 12, "sx in cars", "6 SEVENTH ST", "gotyourSix@hotmail.com", "gotyourSix@hotmail.com   ", "gotyourSix@hotmail.com"],

1✔

2138

        ["7th Heaven", "7 Eight Crt.", 10, 12, "7th heaven", "7 EIGHT CRT.", "timesAcharm@gmail.com", "timesAcharm@gmail.com ", " timesAcharm@gmail.com"]];

1✔

2139

1✔

2140

        return this.isEqual("selectFuncs2", data, expected);

1✔

2141

1✔

2142

1✔

2143

    selectFuncs3() {

1✔

2144

        let stmt = "select name + ' = ' + upper(email), reverse(name), replicate(name,2) from customers";

1✔

2145

1✔

2146

        let data = new TestSql()

1✔

2147

            .addTableData("customers", this.customerTable())

1✔

2148

            .enableColumnTitle(true)

1✔

2149

            .execute(stmt);

1✔

2150

1✔

2151

        let expected = [["name + ' = ' + upper(email)", "reverse(name)", "replicate(name,2)"],

1✔

2152

        ["Numereo Uno = BIGONE@GMAIL.COM", "onU oeremuN", "Numereo UnoNumereo Uno"],

1✔

2153

        ["Dewy Tuesdays = TWOGUYS@GMAIL.COM", "syadseuT yweD", "Dewy TuesdaysDewy Tuesdays"],

1✔

2154

        ["Tres Buon Goods = THRICE@HOTMAIL.COM", "sdooG nouB serT", "Tres Buon GoodsTres Buon Goods"],

1✔

2155

        ["ForMe Resellers = FOURTIMES@HOTMAIL.COM", "srelleseR eMroF", "ForMe ResellersForMe Resellers"],

1✔

2156

        ["Fe Fi Fo Giant Tiger =    FIVER@GMAIL.COM", "regiT tnaiG oF iF eF", "Fe Fi Fo Giant TigerFe Fi Fo Giant Tiger"],

1✔

2157

        ["Sx in Cars = GOTYOURSIX@HOTMAIL.COM   ", "sraC ni xS", "Sx in CarsSx in Cars"],

1✔

2158

        ["7th Heaven =  TIMESACHARM@GMAIL.COM ", "nevaeH ht7", "7th Heaven7th Heaven"]];

1✔

2159

1✔

2160

        return this.isEqual("selectFuncs3", data, expected);

1✔

2161

1✔

2162

1✔

2163

    selectFuncs4() {

1✔

2164

        let stmt = "select space(5), email, stuff(email, 2, 3, 'CJD'), substring(email, 5, 5) from customers";

1✔

2165

1✔

2166

        let data = new TestSql()

1✔

2167

            .addTableData("customers", this.customerTable())

1✔

2168

            .enableColumnTitle(true)

1✔

2169

            .execute(stmt);

1✔

2170

1✔

2171

        let expected = [["space(5)", "email", "stuff(email, 2, 3, 'CJD')", "substring(email, 5, 5)"],

1✔

2172

        ["     ", "bigOne@gmail.com", "bCJDne@gmail.com", "ne@gm"],

1✔

2173

        ["     ", "twoguys@gmail.com", "tCJDuys@gmail.com", "uys@g"],

1✔

2174

        ["     ", "thrice@hotmail.com", "tCJDce@hotmail.com", "ce@ho"],

1✔

2175

        ["     ", "fourtimes@hotmail.com", "fCJDtimes@hotmail.com", "times"],

1✔

2176

        ["     ", "   fiver@gmail.com", " CJDiver@gmail.com", "iver@"],

1✔

2177

        ["     ", "gotyourSix@hotmail.com   ", "gCJDourSix@hotmail.com   ", "ourSi"],

1✔

2178

        ["     ", " timesAcharm@gmail.com ", " CJDesAcharm@gmail.com ", "esAch"]];

1✔

2179

1✔

2180

        return this.isEqual("selectFuncs4", data, expected);

1✔

2181

1✔

2182

1✔

2183

    selectFuncs5() {

1✔

2184

        let stmt = "select now(), email, stuff(email, 2, 3, 'CJD'), substring(email, 5, 5) from customers limit 1";

1✔

2185

1✔

2186

        let testSQL = new TestSql()

1✔

2187

            .addTableData("customers", this.customerTable())

1✔

2188

            .enableColumnTitle(true);

1✔

2189

1✔

2190

        Logger.log("NOTE:  selectFuncs5(), Test is attempted multiple times on failure (matching current time).")

1✔

2191

        //  NOW() is always changing, so try our test a few times.

1✔

2192

        let attempts = 0;

1✔

2193

        let success = false;

1✔

2194

        while (attempts < 5 && !success) {

1✔

2195

            let data = testSQL.execute(stmt);

1✔

2196

1✔

2197

            let expected = [["now()", "email", "stuff(email, 2, 3, 'CJD')", "substring(email, 5, 5)"],

1✔

2198

            ["%1", "bigOne@gmail.com", "bCJDne@gmail.com", "ne@gm"]];

1✔

2199

1✔

2200

            for (let row of expected) {

1✔

2201

                let nowPos = row.indexOf("%1");

2✔

2202

                if (nowPos != -1)

2✔

2203

                    row[nowPos] = new Date().toLocaleString();

2✔

2204

2✔

2205

1✔

2206

            success = this.isEqual("selectFuncs5", data, expected);

1✔

2207

            attempts++;

1✔

2208

1✔

2209

1✔

2210

        return success;

1✔

2211

1✔

2212

1✔

2213

    selectFuncs6() {

1✔

2214

        let stmt = "select date, year(date), month(date), day(date) from booksales";

1✔

2215

1✔

2216

        let data = new TestSql()

1✔

2217

            .addTableData("booksales", this.bookSalesTable())

1✔

2218

            .enableColumnTitle(true)

1✔

2219

            .execute(stmt);

1✔

2220

1✔

2221

        let expected = [["date", "year(date)", "month(date)", "day(date)"],

1✔

2222

        ["05/01/2022", 2022, 5, 1],

1✔

2223

        ["05/01/2022", 2022, 5, 1],

1✔

2224

        ["05/01/2022", 2022, 5, 1],

1✔

2225

        ["05/02/2022", 2022, 5, 2],

1✔

2226

        ["05/02/2022", 2022, 5, 2],

1✔

2227

        ["05/03/2022", 2022, 5, 3],

1✔

2228

        ["05/03/2022", 2022, 5, 3],

1✔

2229

        ["05/03/2022", 2022, 5, 3],

1✔

2230

        ["05/04/2022", 2022, 5, 4],

1✔

2231

        ["05/04/2022", 2022, 5, 4]];

1✔

2232

1✔

2233

        return this.isEqual("selectFuncs6", data, expected);

1✔

2234

1✔

2235

1✔

2236

    selectFuncs7() {

1✔

2237

        let stmt = "select name, charindex(' ', name, 2), charindex(' ', name, 4), charindex(' ', name, 6) from customers";

1✔

2238

1✔

2239

        let data = new TestSql()

1✔

2240

            .addTableData("customers", this.customerTable())

1✔

2241

            .enableColumnTitle(true)

1✔

2242

            .execute(stmt);

1✔

2243

1✔

2244

        let expected = [["name", "charindex(' ', name, 2)", "charindex(' ', name, 4)", "charindex(' ', name, 6)"],

1✔

2245

        ["Numereo Uno", 8, 8, 8],

1✔

2246

        ["Dewy Tuesdays", 5, 5, 0],

1✔

2247

        ["Tres Buon Goods", 5, 5, 10],

1✔

2248

        ["ForMe Resellers", 6, 6, 6],

1✔

2249

        ["Fe Fi Fo Giant Tiger", 3, 6, 6],

1✔

2250

        ["Sx in Cars", 3, 6, 6],

1✔

2251

        ["7th Heaven", 4, 4, 0]];

1✔

2252

1✔

2253

        return this.isEqual("selectFuncs7", data, expected);

1✔

2254

1✔

2255

1✔

2256

    selectFuncInFunc1() {

1✔

2257

        let stmt = "select email, upper(substring(email, 5, 5)), trim(upper(email)) from customers";

1✔

2258

1✔

2259

        let data = new TestSql()

1✔

2260

            .addTableData("customers", this.customerTable())

1✔

2261

            .enableColumnTitle(true)

1✔

2262

            .execute(stmt);

1✔

2263

1✔

2264

        let expected = [["email", "upper(substring(email, 5, 5))", "trim(upper(email))"],

1✔

2265

        ["bigOne@gmail.com", "NE@GM", "BIGONE@GMAIL.COM"],

1✔

2266

        ["twoguys@gmail.com", "UYS@G", "TWOGUYS@GMAIL.COM"],

1✔

2267

        ["thrice@hotmail.com", "CE@HO", "THRICE@HOTMAIL.COM"],

1✔

2268

        ["fourtimes@hotmail.com", "TIMES", "FOURTIMES@HOTMAIL.COM"],

1✔

2269

        ["   fiver@gmail.com", "IVER@", "FIVER@GMAIL.COM"],

1✔

2270

        ["gotyourSix@hotmail.com   ", "OURSI", "GOTYOURSIX@HOTMAIL.COM"],

1✔

2271

        [" timesAcharm@gmail.com ", "ESACH", "TIMESACHARM@GMAIL.COM"]];

1✔

2272

1✔

2273

        return this.isEqual("selectFuncInFunc1", data, expected);

1✔

2274

1✔

2275

1✔

2276

    selectFuncInFunc2() {

1✔

2277

        let stmt = "select email,charindex('@', email), if(charindex('@', email) > 0, trim(substring(email, 1, charindex('@', email) - 1)), email) from customers";

1✔

2278

1✔

2279

        let data = new TestSql()

1✔

2280

            .addTableData("customers", this.customerTable())

1✔

2281

            .enableColumnTitle(true)

1✔

2282

            .execute(stmt);

1✔

2283

1✔

2284

        let expected = [["email", "charindex('@', email)", "if(charindex('@', email) > 0, trim(substring(email, 1, charindex('@', email) - 1)), email)"],

1✔

2285

        ["bigOne@gmail.com", 7, "bigOne"],

1✔

2286

        ["twoguys@gmail.com", 8, "twoguys"],

1✔

2287

        ["thrice@hotmail.com", 7, "thrice"],

1✔

2288

        ["fourtimes@hotmail.com", 10, "fourtimes"],

1✔

2289

        ["   fiver@gmail.com", 9, "fiver"],

1✔

2290

        ["gotyourSix@hotmail.com   ", 11, "gotyourSix"],

1✔

2291

        [" timesAcharm@gmail.com ", 13, "timesAcharm"]];

1✔

2292

1✔

2293

        return this.isEqual("selectFuncInFunc2", data, expected);

1✔

2294

1✔

2295

1✔

2296

    selectIF1() {

1✔

2297

        let stmt = "SELECT IF(authors.id = '', 'MISSING AUTHOR', authors.id), authors.last_name, IF(editors.id = '', 'MISSING EDITOR', editors.id), editors.last_name " +

1✔

2298

            "FROM authors " +

1✔

2299

            "FULL JOIN editors " +

1✔

2300

            "ON authors.id = editors.id ";

1✔

2301

1✔

2302

        let data = new TestSql()

1✔

2303

            .addTableData("authors", this.authorsTable())

1✔

2304

            .addTableData("editors", this.editorsTable())

1✔

2305

            .enableColumnTitle(true)

1✔

2306

            .execute(stmt);

1✔

2307

1✔

2308

        let expected = [["IF(authors.id = '', 'MISSING AUTHOR', authors.id)", "authors.last_name", "IF(editors.id = '', 'MISSING EDITOR', editors.id)", "editors.last_name"],

1✔

2309

        ["11", "Writer", "MISSING EDITOR", ""],

1✔

2310

        ["12", "Savelieva", "MISSING EDITOR", ""],

1✔

2311

        ["13", "Smart", "13", "Smart"],

1✔

2312

        ["14", "Brain", "MISSING EDITOR", ""],

1✔

2313

        ["15", "Dou", "MISSING EDITOR", ""],

1✔

2314

        ["MISSING AUTHOR", "", "21", "Brown"],

1✔

2315

        ["MISSING AUTHOR", "", "22", "Johnson"],

1✔

2316

        ["MISSING AUTHOR", "", "23", "Evans"],

1✔

2317

        ["MISSING AUTHOR", "", "24", "Roberts"],

1✔

2318

        ["MISSING AUTHOR", "", "25", "Wright"],

1✔

2319

        ["MISSING AUTHOR", "", "26", "Jones"],

1✔

2320

        ["MISSING AUTHOR", "", "27", "Smith"],

1✔

2321

        ["MISSING AUTHOR", "", "50", "Dumb"],

1✔

2322

        ["MISSING AUTHOR", "", "51", "Smart"]];

1✔

2323

1✔

2324

        return this.isEqual("selectIF1", data, expected);

1✔

2325

1✔

2326

1✔

2327

    selectIF2() {

1✔

2328

        let stmt = "SELECT quantity, price, if(price > 25, 'OVER $25', 'UNDER $25') " +

1✔

2329

            "FROM booksales ";

1✔

2330

1✔

2331

        let data = new TestSql()

1✔

2332

            .addTableData("bookSales", this.bookSalesTable())

1✔

2333

            .enableColumnTitle(true)

1✔

2334

            .execute(stmt);

1✔

2335

1✔

2336

        let expected = [["quantity", "price", "if(price > 25, 'OVER $25', 'UNDER $25')"],

1✔

2337

        [10, 34.95, "OVER $25"],

1✔

2338

        [3, 29.95, "OVER $25"],

1✔

2339

        [5, 18.99, "UNDER $25"],

1✔

2340

        [1, 59.99, "OVER $25"],

1✔

2341

        [1, 90, "OVER $25"],

1✔

2342

        [100, 65.49, "OVER $25"],

1✔

2343

        [150, 24.95, "UNDER $25"],

1✔

2344

        [50, 19.99, "UNDER $25"],

1✔

2345

        [1, 33.97, "OVER $25"],

1✔

2346

        [100, 17.99, "UNDER $25"]];

1✔

2347

1✔

2348

        return this.isEqual("selectIF2", data, expected);

1✔

2349

1✔

2350

1✔

2351

    selectIF3() {

1✔

2352

        let stmt = "SELECT quantity, price, if(quantity + price > 100, 'OVER $100', 'UNDER $100') " +

1✔

2353

            "FROM booksales ";

1✔

2354

1✔

2355

        let data = new TestSql()

1✔

2356

            .addTableData("bookSales", this.bookSalesTable())

1✔

2357

            .enableColumnTitle(true)

1✔

2358

            .execute(stmt);

1✔

2359

1✔

2360

        let expected = [["quantity", "price", "if(quantity + price > 100, 'OVER $100', 'UNDER $100')"],

1✔

2361

        [10, 34.95, "UNDER $100"],

1✔

2362

        [3, 29.95, "UNDER $100"],

1✔

2363

        [5, 18.99, "UNDER $100"],

1✔

2364

        [1, 59.99, "UNDER $100"],

1✔

2365

        [1, 90, "UNDER $100"],

1✔

2366

        [100, 65.49, "OVER $100"],

1✔

2367

        [150, 24.95, "OVER $100"],

1✔

2368

        [50, 19.99, "UNDER $100"],

1✔

2369

        [1, 33.97, "UNDER $100"]

1✔

2370

            , [100, 17.99, "OVER $100"]];

1✔

2371

1✔

2372

        return this.isEqual("selectIF3", data, expected);

1✔

2373

1✔

2374

1✔

2375

    selectIF4() {

1✔

2376

        let stmt = "SELECT quantity, price, if(quantity * price > 100, 'OVER $100', 'UNDER $100') " +

1✔

2377

            "FROM booksales ";

1✔

2378

1✔

2379

        let data = new TestSql()

1✔

2380

            .addTableData("bookSales", this.bookSalesTable())

1✔

2381

            .enableColumnTitle(true)

1✔

2382

            .execute(stmt);

1✔

2383

1✔

2384

        let expected = [["quantity", "price", "if(quantity * price > 100, 'OVER $100', 'UNDER $100')"],

1✔

2385

        [10, 34.95, "OVER $100"],

1✔

2386

        [3, 29.95, "UNDER $100"],

1✔

2387

        [5, 18.99, "UNDER $100"],

1✔

2388

        [1, 59.99, "UNDER $100"],

1✔

2389

        [1, 90, "UNDER $100"],

1✔

2390

        [100, 65.49, "OVER $100"],

1✔

2391

        [150, 24.95, "OVER $100"],

1✔

2392

        [50, 19.99, "OVER $100"],

1✔

2393

        [1, 33.97, "UNDER $100"],

1✔

2394

        [100, 17.99, "OVER $100"]];

1✔

2395

1✔

2396

        return this.isEqual("selectIF4", data, expected);

1✔

2397

1✔

2398

1✔

2399

    selectWhereCalc1() {

1✔

2400

        let stmt = "SELECT quantity, price, price + quantity from booksales where (price + quantity > 100)";

1✔

2401

1✔

2402

        let data = new TestSql()

1✔

2403

            .addTableData("bookSales", this.bookSalesTable())

1✔

2404

            .enableColumnTitle(true)

1✔

2405

            .execute(stmt);

1✔

2406

1✔

2407

        let expected = [["quantity", "price", "price + quantity"],

1✔

2408

        [100, 65.49, 165.49],

1✔

2409

        [150, 24.95, 174.95],

1✔

2410

        [100, 17.99, 117.99]];

1✔

2411

1✔

2412

        return this.isEqual("selectWhereCalc1", data, expected);

1✔

2413

1✔

2414

1✔

2415

    selectWhereCalc2() {

1✔

2416

        let stmt = "SELECT quantity, price, quantity * price from booksales where price * quantity > 100";

1✔

2417

1✔

2418

        let data = new TestSql()

1✔

2419

            .addTableData("bookSales", this.bookSalesTable())

1✔

2420

            .enableColumnTitle(true)

1✔

2421

            .execute(stmt);

1✔

2422

1✔

2423

        let expected = [["quantity", "price", "quantity * price"],

1✔

2424

        [10, 34.95, 349.5],

1✔

2425

        [100, 65.49, 6548.999999999999],

1✔

2426

        [150, 24.95, 3742.5],

1✔

2427

        [50, 19.99, 999.4999999999999],

1✔

2428

        [100, 17.99, 1798.9999999999998]];

1✔

2429

1✔

2430

        return this.isEqual("selectWhereCalc2", data, expected);

1✔

2431

1✔

2432

1✔

2433

    selectCase1() {

1✔

2434

        let stmt = "SELECT quantity, price, " +

1✔

2435

            "CASE " +

1✔

2436

            "WHEN quantity = 1 THEN 'One Sold' " +

1✔

2437

            "WHEN quantity = 2 THEN 'Two Sold' " +

1✔

2438

            "WHEN quantity = 3 THEN 'Three Sold' " +

1✔

2439

            "WHEN quantity < 100 THEN 'Up to 100 Sold' " +

1✔

2440

            "ELSE quantity + ' items sold' " +

1✔

2441

            "END " +

1✔

2442

            "from booksales";

1✔

2443

1✔

2444

        let data = new TestSql()

1✔

2445

            .addTableData("bookSales", this.bookSalesTable())

1✔

2446

            .enableColumnTitle(true)

1✔

2447

            .execute(stmt);

1✔

2448

1✔

2449

        let expected = [["quantity", "price", "CASE WHEN quantity = 1 THEN 'One Sold' WHEN quantity = 2 THEN 'Two Sold' WHEN quantity = 3 THEN 'Three Sold' WHEN quantity < 100 THEN 'Up to 100 Sold' ELSE quantity + ' items sold' END"],

1✔

2450

        [10, 34.95, "Up to 100 Sold"],

1✔

2451

        [3, 29.95, "Three Sold"],

1✔

2452

        [5, 18.99, "Up to 100 Sold"],

1✔

2453

        [1, 59.99, "One Sold"],

1✔

2454

        [1, 90, "One Sold"],

1✔

2455

        [100, 65.49, "100 items sold"],

1✔

2456

        [150, 24.95, "150 items sold"],

1✔

2457

        [50, 19.99, "Up to 100 Sold"],

1✔

2458

        [1, 33.97, "One Sold"],

1✔

2459

        [100, 17.99, "100 items sold"]];

1✔

2460

1✔

2461

        return this.isEqual("selectCase1", data, expected);

1✔

2462

1✔

2463

1✔

2464

    selectCase2() {

1✔

2465

        let stmt = "SELECT quantity, price, " +

1✔

2466

            "'Invoice=' + substring(invoice,2,4) + ' ' + " +

1✔

2467

            "CASE " +

1✔

2468

            "WHEN quantity > 1 and quantity <= 5 THEN 'Low Volume ' + quantity * price " +

1✔

2469

            "WHEN quantity > 5 and quantity < 10 THEN 'Moderate Volume' + quantity " +

1✔

2470

            "WHEN quantity = 100 or quantity = 150 THEN '100 or 150' " +

1✔

2471

            "WHEN quantity * price = 90 THEN '$90, ka ching.'   " +

1✔

2472

            "ELSE quantity + ' items sold. ID=' + lower(customer_id) " +

1✔

2473

            "END as summary" +

1✔

2474

            "from booksales";

1✔

2475

1✔

2476

        let data = new TestSql()

1✔

2477

            .addTableData("bookSales", this.bookSalesTable())

1✔

2478

            .enableColumnTitle(true)

1✔

2479

            .execute(stmt);

1✔

2480

1✔

2481

        let expected = [["quantity", "price", "summary"],

1✔

2482

        [10, 34.95, "Invoice=7200 10 items sold. ID=c1"],

1✔

2483

        [3, 29.95, "Invoice=7201 Low Volume 89.85"],

1✔

2484

        [5, 18.99, "Invoice=7201 Low Volume 94.94999999999999"],

1✔

2485

        [1, 59.99, "Invoice=7202 1 items sold. ID=c3"],

1✔

2486

        [1, 90, "Invoice=7203 $90, ka ching."],

1✔

2487

        [100, 65.49, "Invoice=7204 100 or 150"],

1✔

2488

        [150, 24.95, "Invoice=7204 100 or 150"],

1✔

2489

        [50, 19.99, "Invoice=7204 50 items sold. ID=c4"],

1✔

2490

        [1, 33.97, "Invoice=7205 1 items sold. ID=c1"],

1✔

2491

        [100, 17.99, "Invoice=7206 100 or 150"]];

1✔

2492

1✔

2493

        return this.isEqual("selectCase2", data, expected);

1✔

2494

1✔

2495

1✔

2496

    selectAlias1() {

1✔

2497

        let stmt = "SELECT quantity as QTY, price as Pricing,  round(quantity * price) as Money from booksales where price * quantity > 100";

1✔

2498

1✔

2499

        let data = new TestSql()

1✔

2500

            .addTableData("bookSales", this.bookSalesTable())

1✔

2501

            .enableColumnTitle(true)

1✔

2502

            .execute(stmt);

1✔

2503

1✔

2504

        let expected = [["QTY", "Pricing", "Money"],

1✔

2505

        [10, 34.95, 350],

1✔

2506

        [100, 65.49, 6549],

1✔

2507

        [150, 24.95, 3743],

1✔

2508

        [50, 19.99, 999],

1✔

2509

        [100, 17.99, 1799]];

1✔

2510

1✔

2511

        return this.isEqual("selectAlias1", data, expected);

1✔

2512

1✔

2513

1✔

2514

    liveTest1() {

1✔

2533

1✔

2534

    liveTest2() {

1✔

2549

1✔

2550

    groupPivot1() {

1✔

2551

        let stmt = "select bookSales.date, SUM(bookSales.Quantity) from bookSales where customer_id != '' group by date pivot customer_id";

1✔

2552

1✔

2553

        let data = new TestSql()

1✔

2554

            .addTableData("bookSales", this.bookSalesTable())

1✔

2555

            .enableColumnTitle(true)

1✔

2556

            .execute(stmt);

1✔

2557

1✔

2558

        let expected = [["bookSales.date", "C1 SUM(bookSales.Quantity)", "C2 SUM(bookSales.Quantity)", "C3 SUM(bookSales.Quantity)", "C4 SUM(bookSales.Quantity)"],

1✔

2559

        ["05/01/2022", 10, 8, 0, 0],

1✔

2560

        ["05/02/2022", 0, 0, 1, 0],

1✔

2561

        ["05/03/2022", 0, 0, 0, 300],

1✔

2562

        ["05/04/2022", 1, 100, 0, 0]];

1✔

2563

1✔

2564

        return this.isEqual("groupPivot1", data, expected);

1✔

2565

1✔

2566

1✔

2567

    groupPivot2() {

1✔

2568

        let stmt = "select date, sum(quantity) from bookReturns group by date pivot customer_id";

1✔

2569

1✔

2570

        let data = new TestSql()

1✔

2571

            .addTableData("bookReturns", this.bookReturnsTable())

1✔

2572

            .enableColumnTitle(true)

1✔

2573

            .execute(stmt);

1✔

2574

1✔

2575

        let expected = [["date", "c1 sum(quantity)", "c2 sum(quantity)", "c3 sum(quantity)", "c4 sum(quantity)"],

1✔

2576

        ["05/01/2022", 10, 8, 0, 0],

1✔

2577

        ["05/02/2022", 1, 0, 1, 0],

1✔

2578

        ["05/03/2022", 0, 0, 0, 300],

1✔

2579

        ["05/04/2022", 1, 100, 0, 0]];

1✔

2580

1✔

2581

        return this.isEqual("groupPivot2", data, expected);

1✔

2582

1✔

2583

1✔

2584

    groupPivot3() {

1✔

2585

        let stmt = "select date, sum(quantity) from bookReturns where date >= ?1 and date <= ?2 group by date pivot customer_id";

1✔

2586

1✔

2587

        let data = new TestSql()

1✔

2588

            .addTableData("bookReturns", this.bookReturnsTable())

1✔

2589

            .enableColumnTitle(true)

1✔

2590

            .addBindParameter("05/01/2022")

1✔

2591

            .addBindParameter("05/04/2022")

1✔

2592

            .execute(stmt);

1✔

2593

1✔

2594

        let expected = [["date", "c1 sum(quantity)", "c2 sum(quantity)", "c3 sum(quantity)", "c4 sum(quantity)"],

1✔

2595

        ["05/01/2022", 10, 8, 0, 0],

1✔

2596

        ["05/02/2022", 1, 0, 1, 0],

1✔

2597

        ["05/03/2022", 0, 0, 0, 300],

1✔

2598

        ["05/04/2022", 1, 100, 0, 0]];

1✔

2599

1✔

2600

        return this.isEqual("groupPivot3", data, expected);

1✔

2601

1✔

2602

1✔

2603

1✔

2604

    groupFunc1() {

1✔

2605

        let stmt = "select bookSales.date, SUM(if(customer_id = 'C1', bookSales.Quantity,0)), SUM(if(customer_id = 'C2', bookSales.Quantity,0)) from bookSales where customer_id != '' group by date";

1✔

2606

1✔

2607

        let data = new TestSql()

1✔

2608

            .addTableData("bookSales", this.bookSalesTable())

1✔

2609

            .enableColumnTitle(true)

1✔

2610

            .execute(stmt);

1✔

2611

1✔

2612

        let expected = [["bookSales.date", "SUM(if(customer_id = 'C1', bookSales.Quantity,0))", "SUM(if(customer_id = 'C2', bookSales.Quantity,0))"],

1✔

2613

        ["05/01/2022", 10, 8],

1✔

2614

        ["05/02/2022", 0, 0],

1✔

2615

        ["05/03/2022", 0, 0],

1✔

2616

        ["05/04/2022", 1, 100]];

1✔

2617

1✔

2618

        return this.isEqual("groupFunc1", data, expected);

1✔

2619

1✔

2620

1✔

2621

    groupFunc2() {

1✔

2622

        let stmt = "select bookSales.date, SUM(if(customer_id = 'C1', bookSales.Quantity,0)), SUM(if(customer_id = 'C2', bookSales.Quantity,0)) from bookSales where customer_id = '1010' group by date";

1✔

2623

1✔

2624

        let data = new TestSql()

1✔

2625

            .addTableData("bookSales", this.bookSalesTable())

1✔

2626

            .enableColumnTitle(false)

1✔

2627

            .execute(stmt);

1✔

2628

1✔

2629

        let expected = [['']];

1✔

2630

1✔

2631

        return this.isEqual("groupFunc2", data, expected);

1✔

2632

1✔

2633

1✔

2634

    selectInGroupByPivot1() {

1✔

2635

        let stmt = "select bookSales.date, SUM(bookSales.Quantity) from bookSales where customer_id in (select id from customers)  group by date pivot customer_id";

1✔

2636

1✔

2637

        let data = new TestSql()

1✔

2638

            .addTableData("bookSales", this.bookSalesTable())

1✔

2639

            .addTableData("customers", this.customerTable())

1✔

2640

            .enableColumnTitle(true)

1✔

2641

            .execute(stmt);

1✔

2642

1✔

2643

        let expected = [["bookSales.date", "C1 SUM(bookSales.Quantity)", "C2 SUM(bookSales.Quantity)", "C3 SUM(bookSales.Quantity)", "C4 SUM(bookSales.Quantity)"],

1✔

2644

        ["05/01/2022", 10, 8, 0, 0],

1✔

2645

        ["05/02/2022", 0, 0, 1, 0],

1✔

2646

        ["05/03/2022", 0, 0, 0, 300],

1✔

2647

        ["05/04/2022", 1, 100, 0, 0]];

1✔

2648

1✔

2649

        return this.isEqual("selectInGroupByPivot1", data, expected);

1✔

2650

1✔

2651

1✔

2652

    selectInGroupByPivot2() {

1✔

2653

        let stmt = "select bookSales.date as 'Transaction Date', SUM(bookSales.Quantity) as [ as Much Quantity], Max(price) as Maximum from bookSales where customer_id in (select id from customers)  group by date pivot customer_id";

1✔

2654

1✔

2655

        let data = new TestSql()

1✔

2656

            .addTableData("bookSales", this.bookSalesTable())

1✔

2657

            .addTableData("customers", this.customerTable())

1✔

2658

            .enableColumnTitle(true)

1✔

2659

            .execute(stmt);

1✔

2660

1✔

2661

        let expected = [["Transaction Date", "C1  as Much Quantity", "C2  as Much Quantity", "C3  as Much Quantity", "C4  as Much Quantity", "C1 Maximum", "C2 Maximum", "C3 Maximum", "C4 Maximum"],

1✔

2662

        ["05/01/2022", 10, 8, 0, 0, 34.95, 29.95, 0, 0],

1✔

2663

        ["05/02/2022", 0, 0, 1, 0, 0, 0, 59.99, 0],

1✔

2664

        ["05/03/2022", 0, 0, 0, 300, 0, 0, 0, 65.49],

1✔

2665

        ["05/04/2022", 1, 100, 0, 0, 33.97, 17.99, 0, 0]];

1✔

2666

1✔

2667

        return this.isEqual("selectInGroupByPivot2", data, expected);

1✔

2668

1✔

2669

1✔

2670

    selectInGroupByPivot3() {

1✔

2671

        let stmt = "select bookSales.date as 'Date', SUM(bookSales.Quantity) as [Quantity], Max(price) as Maximum, min(price) as Min, avg(price) as avg, count(date) from bookSales where customer_id in (select id from customers)  group by date pivot customer_id";

1✔

2672

1✔

2673

        let data = new TestSql()

1✔

2674

            .addTableData("bookSales", this.bookSalesTable())

1✔

2675

            .addTableData("customers", this.customerTable())

1✔

2676

            .enableColumnTitle(true)

1✔

2677

            .execute(stmt);

1✔

2678

1✔

2679

        let expected = [["Date", "C1 Quantity", "C2 Quantity", "C3 Quantity", "C4 Quantity", "C1 Maximum", "C2 Maximum", "C3 Maximum", "C4 Maximum", "C1 Min", "C2 Min", "C3 Min", "C4 Min", "C1 avg", "C2 avg", "C3 avg", "C4 avg", "C1 count(date)", "C2 count(date)", "C3 count(date)", "C4 count(date)"],

1✔

2680

        ["05/01/2022", 10, 8, 0, 0, 34.95, 29.95, 0, 0, 34.95, 18.99, 0, 0, 34.95, 24.47, null, null, 1, 2, 0, 0],

1✔

2681

        ["05/02/2022", 0, 0, 1, 0, 0, 0, 59.99, 0, 0, 0, 59.99, 0, null, null, 59.99, null, 0, 0, 1, 0],

1✔

2682

        ["05/03/2022", 0, 0, 0, 300, 0, 0, 0, 65.49, 0, 0, 0, 19.99, null, null, null, 36.809999999999995, 0, 0, 0, 3],

1✔

2683

        ["05/04/2022", 1, 100, 0, 0, 33.97, 17.99, 0, 0, 33.97, 17.99, 0, 0, 33.97, 17.99, null, null, 1, 1, 0, 0]];

1✔

2684

1✔

2685

        return this.isEqual("selectInGroupByPivot3", data, expected);

1✔

2686

1✔

2687

1✔

2688

    selectCount1() {

1✔

2689

        let stmt = "select count(*) from booksales";

1✔

2690

1✔

2691

        let data = new TestSql()

1✔

2692

            .addTableData("bookSales", this.bookSalesTable())

1✔

2693

            .enableColumnTitle(true)

1✔

2694

            .execute(stmt);

1✔

2695

1✔

2696

        let expected = [["count(*)"],

1✔

2697

        [10]];

1✔

2698

1✔

2699

        return this.isEqual("selectCount1", data, expected);

1✔

2700

1✔

2701

1✔

2702

    selectCount2() {

1✔

2703

        let stmt = "select customer_id, count(*) from booksales group by customer_id having count(*) > 1";

1✔

2704

1✔

2705

        let data = new TestSql()

1✔

2706

            .addTableData("bookSales", this.bookSalesTable())

1✔

2707

            .enableColumnTitle(true)

1✔

2708

            .execute(stmt);

1✔

2709

1✔

2710

        let expected = [["customer_id", "count(*)"],

1✔

2711

        ["C1", 2],

1✔

2712

        ["C2", 3],

1✔

2713

        ["C4", 3]];

1✔

2714

1✔

2715

        return this.isEqual("selectCount2", data, expected);

1✔

2716

1✔

2717

1✔

2718

    selectCount3() {

1✔

2719

        let stmt = "select count(distinct customer_id), count(distinct invoice) from booksales where customer_id <> ''";

1✔

2720

1✔

2721

        let data = new TestSql()

1✔

2722

            .addTableData("bookSales", this.bookSalesTable())

1✔

2723

            .enableColumnTitle(true)

1✔

2724

            .execute(stmt);

1✔

2725

1✔

2726

        let expected = [["count(distinct customer_id)", "count(distinct invoice)"],

1✔

2727

        [4, 6]];

1✔

2728

1✔

2729

        return this.isEqual("selectCount3", data, expected);

1✔

2730

1✔

2731

1✔

2732

    selectCount4() {

1✔

2733

        let stmt = "select count(distinct customer_id), count(distinct invoice) from booksales";

1✔

2734

1✔

2735

        let data = new TestSql()

1✔

2736

            .addTableData("bookSales", this.bookSalesTable())

1✔

2737

            .enableColumnTitle(true)

1✔

2738

            .execute(stmt);

1✔

2739

1✔

2740

        let expected = [["count(distinct customer_id)", "count(distinct invoice)"],

1✔

2741

        [5, 7]];

1✔

2742

1✔

2743

        return this.isEqual("selectCount4", data, expected);

1✔

2744

1✔

2745

1✔

2746

    selectCount5() {

1✔

2747

        let stmt = "select count(all customer_id), count(all invoice) from booksales";

1✔

2748

1✔

2749

        let data = new TestSql()

1✔

2750

            .addTableData("bookSales", this.bookSalesTable())

1✔

2751

            .enableColumnTitle(true)

1✔

2752

            .execute(stmt);

1✔

2753

1✔

2754

        let expected = [["count(all customer_id)", "count(all invoice)"],

1✔

2755

        [10, 10]];

1✔

2756

1✔

2757

        return this.isEqual("selectCount5", data, expected);

1✔

2758

1✔

2759

1✔

2760

    selectCount6() {

1✔

2761

        let stmt = "select avg(price), max(quantity), count(customer_id) from booksales group by customer_id";

1✔

2762

1✔

2763

        let data = new TestSql()

1✔

2764

            .addTableData("bookSales", this.bookSalesTable())

1✔

2765

            .enableColumnTitle(true)

1✔

2766

            .execute(stmt);

1✔

2767

1✔

2768

        let expected = [["avg(price)", "max(quantity)", "count(customer_id)"],

1✔

2769

        [90, 1, 1],

1✔

2770

        [34.46, 10, 2],

1✔

2771

        [22.31, 100, 3],

1✔

2772

        [59.99, 1, 1],

1✔

2773

        [36.809999999999995, 150, 3]];

1✔

2774

1✔

2775

        return this.isEqual("selectCount6", data, expected);

1✔

2776

1✔

2777

1✔

2778

    selectGroupByNotInSelect() {

1✔

2779

        let stmt = "select avg(price), max(quantity)  from booksales group by customer_id";

1✔

2780

1✔

2781

        let data = new TestSql()

1✔

2782

            .addTableData("bookSales", this.bookSalesTable())

1✔

2783

            .enableColumnTitle(true)

1✔

2784

            .execute(stmt);

1✔

2785

1✔

2786

        let expected = [["avg(price)", "max(quantity)"],

1✔

2787

        [90, 1],

1✔

2788

        [34.46, 10],

1✔

2789

        [22.31, 100],

1✔

2790

        [59.99, 1],

1✔

2791

        [36.809999999999995, 150]];

1✔

2792

1✔

2793

        return this.isEqual("selectGroupByNotInSelect", data, expected);

1✔

2794

1✔

2795

1✔

2796

    selectGroupByNotInSelect2() {

1✔

2797

        let stmt = "select avg(price), max(quantity)  from booksales group by customer_id, date";

1✔

2798

1✔

2799

        let data = new TestSql()

1✔

2800

            .addTableData("bookSales", this.bookSalesTable())

1✔

2801

            .enableColumnTitle(true)

1✔

2802

            .execute(stmt);

1✔

2803

1✔

2804

        let expected = [["avg(price)", "max(quantity)"],

1✔

2805

        [90, 1],

1✔

2806

        [34.95, 10],

1✔

2807

        [33.97, 1],

1✔

2808

        [24.47, 5],

1✔

2809

        [17.99, 100],

1✔

2810

        [59.99, 1],

1✔

2811

        [36.809999999999995, 150]];

1✔

2812

1✔

2813

        return this.isEqual("selectGroupByNotInSelect2", data, expected);

1✔

2814

1✔

2815

1✔

2816

    selectOrderByNotInSelect() {

1✔

2817

        let stmt = "select price, quantity from booksales order by customer_id";

1✔

2818

1✔

2819

        let data = new TestSql()

1✔

2820

            .addTableData("bookSales", this.bookSalesTable())

1✔

2821

            .enableColumnTitle(true)

1✔

2822

            .execute(stmt);

1✔

2823

1✔

2824

        let expected = [["price", "quantity"],

1✔

2825

        [90, 1],

1✔

2826

        [34.95, 10],

1✔

2827

        [33.97, 1],

1✔

2828

        [29.95, 3],

1✔

2829

        [18.99, 5],

1✔

2830

        [17.99, 100],

1✔

2831

        [59.99, 1],

1✔

2832

        [65.49, 100],

1✔

2833

        [24.95, 150],

1✔

2834

        [19.99, 50]];

1✔

2835

1✔

2836

        return this.isEqual("selectOrderByNotInSelect", data, expected);

1✔

2837

1✔

2838

1✔

2839

    selectCoalesce() {

1✔

2840

        let stmt = "select name, coalesce(dec, nov, oct, sep, aug, jul, jun, may, apr, mar, feb, jan) from yearlysales";

1✔

2841

1✔

2842

        let data = new TestSql()

1✔

2843

            .addTableData("yearlysales", this.yearlySalesTable())

1✔

2844

            .enableColumnTitle(true)

1✔

2845

            .execute(stmt);

1✔

2846

1✔

2847

        let expected = [["name", "coalesce(dec, nov, oct, sep, aug, jul, jun, may, apr, mar, feb, jan)"],

1✔

2848

        ["Chris", 60],

1✔

2849

        ["Fred", 30],

1✔

2850

        ["Dan", 31],

1✔

2851

        ["Kev", 60],

1✔

2852

        ["Dori", 50],

1✔

2853

        ["Gab", "20"]];

1✔

2854

1✔

2855

        return this.isEqual("selectCoalesce", data, expected);

1✔

2856

1✔

2857

1✔

2858

    selectConcat_Ws() {

1✔

2859

        let stmt = "select concat_ws('-', *) as concatenated from customers " +

1✔

2860

            "where concat_ws('-', *) like '%Way%'";

1✔

2861

1✔

2862

        let data = new TestSql()

1✔

2863

            .addTableData("customers", this.customerTable())

1✔

2864

            .enableColumnTitle(true)

1✔

2865

            .execute(stmt);

1✔

2866

1✔

2867

        let expected = [["concatenated"],

1✔

2868

        ["C1-Numereo Uno-101 One Way-One Point City-9051112111-bigOne@gmail.com"],

1✔

2869

        ["C3-Tres Buon Goods-3 Way St-Tres City-5193133303-thrice@hotmail.com"]];

1✔

2870

1✔

2871

        return this.isEqual("selectConcat_Ws", data, expected);

1✔

2872

1✔

2873

1✔

2874

    selectConcat_Ws2() {

1✔

2875

        let stmt = "select concat_ws('-', *) as concatenated from booksales " +

1✔

2876

            "left join customers on booksales.customer_id = customers.id " +

1✔

2877

            "where concat_ws('-', *) like '%Way%'";

1✔

2878

1✔

2879

        let data = new TestSql()

1✔

2880

            .addTableData("customers", this.customerTable())

1✔

2881

            .addTableData("booksales", this.bookSalesTable())

1✔

2882

            .enableColumnTitle(true)

1✔

2883

            .execute(stmt);

1✔

2884

1✔

2885

        let expected = [["concatenated"],

1✔

2886

        ["I7200-9-C1-10-34.95-05/01/2022-C1-Numereo Uno-101 One Way-One Point City-9051112111-bigOne@gmail.com"],

1✔

2887

        ["I7202-9-C3-1-59.99-05/02/2022-C3-Tres Buon Goods-3 Way St-Tres City-5193133303-thrice@hotmail.com"],

1✔

2888

        ["I7205-7-C1-1-33.97-05/04/2022-C1-Numereo Uno-101 One Way-One Point City-9051112111-bigOne@gmail.com"]];

1✔

2889

1✔

2890

        return this.isEqual("selectConcat_Ws2", data, expected);

1✔

2891

1✔

2892

1✔

2893

    selectNoTitle1() {

1✔

2894

        let stmt = "SELECT booksales.A as 'Invoice', booksales.B as 'Book ID', CUST.A, CUST.B FROM booksales " +

1✔

2895

            "LEFT JOIN customers as CUST on booksales.C = customers.A ";

1✔

2896

1✔

2897

        let customers = this.customerTable();

1✔

2898

        let bookSales = this.bookSalesTable();

1✔

2899

1✔

2900

        //  Get rid of current column titles.

1✔

2901

        customers.shift();

1✔

2902

        bookSales.shift();

1✔

2903

1✔

2904

        let data = new TestSql()

1✔

2905

            .addTableData("customers", customers, 0, false)

1✔

2906

            .addTableData("booksales", bookSales, 0, false)

1✔

2907

            .enableColumnTitle(true)

1✔

2908

            .execute(stmt);

1✔

2909

1✔

2910

        let expected = [["Invoice", "Book ID", "CUST.A", "CUST.B"],

1✔

2911

        ["I7200", "9", "C1", "Numereo Uno"],

1✔

2912

        ["I7201", "8", "C2", "Dewy Tuesdays"],

1✔

2913

        ["I7201", "7", "C2", "Dewy Tuesdays"],

1✔

2914

        ["I7202", "9", "C3", "Tres Buon Goods"],

1✔

2915

        ["I7203", "1", "", ""],

1✔

2916

        ["I7204", "2", "C4", "ForMe Resellers"],

1✔

2917

        ["I7204", "3", "C4", "ForMe Resellers"],

1✔

2918

        ["I7204", "4", "C4", "ForMe Resellers"],

1✔

2919

        ["I7205", "7", "C1", "Numereo Uno"],

1✔

2920

        ["I7206", "7", "C2", "Dewy Tuesdays"]];

1✔

2921

1✔

2922

        return this.isEqual("selectNoTitle1", data, expected);

1✔

2923

1✔

2924

1✔

2925

1✔

2926

    selectNested() {

1✔

2927

        let stmt = "select * from books where id in (select book_id from booksales where price > (select avg(price) from booksales))";

1✔

2928

1✔

2929

        let data = new TestSql()

1✔

2930

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

1✔

2931

            .addTableData("booksales", this.bookSalesTable())

1✔

2932

            .enableColumnTitle(true)

1✔

2933

            .execute(stmt);

1✔

2934

1✔

2935

        let expected = [["BOOKS.ID", "BOOKS.TITLE", "BOOKS.TYPE", "BOOKS.AUTHOR_ID", "BOOKS.EDITOR_ID", "BOOKS.TRANSLATOR_ID"],

1✔

2936

        ["1", "Time to Grow Up!", "original", "11", "21", ""],

1✔

2937

        ["2", "Your Trip", "translated", "15", "22", "32"],

1✔

2938

        ["9", "Book with Mysterious Author", "translated", "1", "23", "34"]];

1✔

2939

1✔

2940

        return this.isEqual("selectNested", data, expected);

1✔

2941

1✔

2942

1✔

2943

    selectNested2() {

1✔

2944

        let stmt = "select last_name from authors where id in (select author_id from books where id in (select book_id from booksales where price > (select avg(price) from booksales)))";

1✔

2945

1✔

2946

        let data = new TestSql()

1✔

2947

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

1✔

2948

            .addTableData("booksales", this.bookSalesTable())

1✔

2949

            .addTableData("authors", this.authorsTable())

1✔

2950

            .enableColumnTitle(true)

1✔

2951

            .execute(stmt);

1✔

2952

1✔

2953

        let expected = [["last_name"],

1✔

2954

        ["Writer"],

1✔

2955

        ["Dou"]];

1✔

2956

1✔

2957

        return this.isEqual("selectNested2", data, expected);

1✔

2958

1✔

2959

1✔

2960

    selectCorrelatedSubQuery1() {

1✔

2961

        let stmt = "select id, title, (select count(*) from booksales where books.id = booksales.book_id) from books";

1✔

2962

1✔

2963

        let data = new TestSql()

1✔

2964

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

1✔

2965

            .addTableData("booksales", this.bookSalesTable())

1✔

2966

            .enableColumnTitle(true)

1✔

2967

            .execute(stmt);

1✔

2968

1✔

2969

        let expected = [["id", "title", "(select count(*) from booksales where books.id = booksales.book_id)"],

1✔

2970

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

1✔

2971

        ["2", "Your Trip", 1],

1✔

2972

        ["3", "Lovely Love", 1],

1✔

2973

        ["4", "Dream Your Life", 1],

1✔

2974

        ["5", "Oranges", ""],

1✔

2975

        ["6", "Your Happy Life", ""],

1✔

2976

        ["7", "Applied AI", 3],

1✔

2977

        ["9", "Book with Mysterious Author", 2],

1✔

2978

        ["8", "My Last Book", 1]];

1✔

2979

1✔

2980

        return this.isEqual("selectCorrelatedSubQuery1", data, expected);

1✔

2981

1✔

2982

1✔

2983

    selectCorrelatedSubQuery2() {

1✔

2984

        let stmt = "select id, title, (select count(*) from booksales where books.id = booksales.book_id) as 'Sold' from books where (select count(*)  from booksales where books.id = booksales.book_id) > 1";

1✔

2985

1✔

2986

        let data = new TestSql()

1✔

2987

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

1✔

2988

            .addTableData("booksales", this.bookSalesTable())

1✔

2989

            .enableColumnTitle(true)

1✔

2990

            .execute(stmt);

1✔

2991

1✔

2992

        let expected = [["id", "title", "Sold"],

1✔

2993

        ["7", "Applied AI", 3],

1✔

2994

        ["9", "Book with Mysterious Author", 2]];

1✔

2995

1✔

2996

        return this.isEqual("selectCorrelatedSubQuery2", data, expected);

1✔

2997

1✔

2998

1✔

2999

1✔

3000

    selectCorrelatedSubQuery3() {

1✔

3001

        let stmt = "select * from customers where exists (SELECT * FROM booksales WHERE booksales.customer_id = customers.id) and email like '%gmail.com' ";

1✔

3002

1✔

3003

        let data = new TestSql()

1✔

3004

            .addTableData("customers", this.customerTable())

1✔

3005

            .addTableData("booksales", this.bookSalesTable())

1✔

3006

            .enableColumnTitle(true)

1✔

3007

            .execute(stmt);

1✔

3008

1✔

3009

        let expected = [["CUSTOMERS.ID", "CUSTOMERS.NAME", "CUSTOMERS.ADDRESS", "CUSTOMERS.CITY", "CUSTOMERS.PHONE", "CUSTOMERS.EMAIL"],

1✔

3010

        ["C1", "Numereo Uno", "101 One Way", "One Point City", "9051112111", "bigOne@gmail.com"],

1✔

3011

        ["C2", "Dewy Tuesdays", "202 Second St.", "Second City", "4162022222", "twoguys@gmail.com"]];

1✔

3012

1✔

3013

        return this.isEqual("selectCorrelatedSubQuery3", data, expected);

1✔

3014

1✔

3015

1✔

3016

    selectCorrelatedSubQuery4() {

1✔

3017

        let stmt = "select * from customers where not exists (SELECT * FROM booksales WHERE booksales.customer_id = customers.id)";

1✔

3018

1✔

3019

        let data = new TestSql()

1✔

3020

            .addTableData("customers", this.customerTable())

1✔

3021

            .addTableData("booksales", this.bookSalesTable())

1✔

3022

            .enableColumnTitle(true)

1✔

3023

            .execute(stmt);

1✔

3024

1✔

3025

        let expected = [["CUSTOMERS.ID", "CUSTOMERS.NAME", "CUSTOMERS.ADDRESS", "CUSTOMERS.CITY", "CUSTOMERS.PHONE", "CUSTOMERS.EMAIL"],

1✔

3026

        ["C5", "Fe Fi Fo Giant Tiger", "5 ohFive St.", "FifthDom", "4165551234", "   fiver@gmail.com"],

1✔

3027

        ["C6", "Sx in Cars", "6 Seventh St", "Sx City", "6661116666", "gotyourSix@hotmail.com   "],

1✔

3028

        ["C7", "7th Heaven", "7 Eight Crt.", "Lucky City", "5551117777", " timesAcharm@gmail.com "]];

1✔

3029

1✔

3030

        return this.isEqual("selectCorrelatedSubQuery4", data, expected);

1✔

3031

1✔

3032

1✔

3033

    selectCorrelatedSubQuery5() {

1✔

3034

        let stmt = "SELECT * FROM booksales as b1 where price = (select max(booksales.price) from booksales where booksales.customer_id = b1.customer_id)";

1✔

3035

1✔

3036

        let data = new TestSql()

1✔

3037

            .addTableData("booksales", this.bookSalesTable())

1✔

3038

            .enableColumnTitle(true)

1✔

3039

            .execute(stmt);

1✔

3040

1✔

3041

        let expected = [["BOOKSALES.INVOICE", "BOOKSALES.BOOK_ID", "BOOKSALES.CUSTOMER_ID", "BOOKSALES.QUANTITY", "BOOKSALES.PRICE", "BOOKSALES.DATE"],

1✔

3042

        ["I7200", "9", "C1", 10, 34.95, "05/01/2022"],

1✔

3043

        ["I7201", "8", "C2", 3, 29.95, "05/01/2022"],

1✔

3044

        ["I7202", "9", "C3", 1, 59.99, "05/02/2022"],

1✔

3045

        ["I7203", "1", "", 1, 90, "05/02/2022"],

1✔

3046

        ["I7204", "2", "C4", 100, 65.49, "05/03/2022"]];

1✔

3047

1✔

3048

        return this.isEqual("selectCorrelatedSubQuery5", data, expected);

1✔

3049

1✔

3050

1✔

3051

    selectCorrelatedSubQuery6() {

1✔

3052

        let stmt = "select * from bookreturns as br where price < (select max(price) from bookreturns  where br.customer_id = customer_id) and price > (select min(price) from bookreturns  where br.customer_id = customer_id)";

1✔

3053

1✔

3054

        let data = new TestSql()

1✔

3055

            .addTableData("bookreturns", this.bookReturnsTable())

1✔

3056

            .enableColumnTitle(true)

1✔

3057

            .execute(stmt);

1✔

3058

1✔

3059

        let expected = [["BOOKRETURNS.RMA", "BOOKRETURNS.BOOK_ID", "BOOKRETURNS.CUSTOMER_ID", "BOOKRETURNS.QUANTITY", "BOOKRETURNS.PRICE", "BOOKRETURNS.DATE"],

1✔

3060

        ["Rma001", "9", "c1", 10, 34.95, "05/01/2022"],

1✔

3061

        ["rmA030", "7", "c2", 5, 18.99, "05/01/2022"],

1✔

3062

        ["Rma701", "3", "c4", 150, 24.95, "05/03/2022"]];

1✔

3063

1✔

3064

        return this.isEqual("selectCorrelatedSubQuery6", data, expected);

1✔

3065

1✔

3066

1✔

3067

    selectCorrelatedSubQuery7() {

1✔

3068

        let stmt = "select * from bookreturns as br where price < (select max(sub.price) from bookreturns as sub where br.customer_id = sub.customer_id) and price > (select min(price) from bookreturns  where br.customer_id = customer_id)";

1✔

3069

1✔

3070

        let data = new TestSql()

1✔

3071

            .addTableData("bookreturns", this.bookReturnsTable())

1✔

3072

            .enableColumnTitle(true)

1✔

3073

            .execute(stmt);

1✔

3074

1✔

3075

        let expected = [["BOOKRETURNS.RMA", "BOOKRETURNS.BOOK_ID", "BOOKRETURNS.CUSTOMER_ID", "BOOKRETURNS.QUANTITY", "BOOKRETURNS.PRICE", "BOOKRETURNS.DATE"],

1✔

3076

        ["Rma001", "9", "c1", 10, 34.95, "05/01/2022"],

1✔

3077

        ["rmA030", "7", "c2", 5, 18.99, "05/01/2022"],

1✔

3078

        ["Rma701", "3", "c4", 150, 24.95, "05/03/2022"]];

1✔

3079

1✔

3080

        return this.isEqual("selectCorrelatedSubQuery7", data, expected);

1✔

3081

1✔

3082

1✔

3083

    selectCorrelatedSubQuery8() {

1✔

3084

        let stmt = "select * from booksales as b1 where price in (select max(price) from booksales where b1.customer_id = customer_id)";

1✔

3085

1✔

3086

        let data = new TestSql()

1✔

3087

            .addTableData("booksales", this.bookSalesTable())

1✔

3088

            .enableColumnTitle(true)

1✔

3089

            .execute(stmt);

1✔

3090

1✔

3091

        let expected = [["BOOKSALES.INVOICE", "BOOKSALES.BOOK_ID", "BOOKSALES.CUSTOMER_ID", "BOOKSALES.QUANTITY", "BOOKSALES.PRICE", "BOOKSALES.DATE"],

1✔

3092

        ["I7200", "9", "C1", 10, 34.95, "05/01/2022"],

1✔

3093

        ["I7201", "8", "C2", 3, 29.95, "05/01/2022"],

1✔

3094

        ["I7202", "9", "C3", 1, 59.99, "05/02/2022"],

1✔

3095

        ["I7203", "1", "", 1, 90, "05/02/2022"],

1✔

3096

        ["I7204", "2", "C4", 100, 65.49, "05/03/2022"]];

1✔

3097

1✔

3098

        return this.isEqual("selectCorrelatedSubQuery8", data, expected);

1✔

3099

1✔

3100

1✔

3101

    selectCorrelatedSubQuery9() {

1✔

3102

        let stmt = "select id, name, (select max(quantity) from booksales where customers.id = booksales.customer_id) as 'max sold' from customers";

1✔

3103

1✔

3104

        let data = new TestSql()

1✔

3105

            .addTableData("booksales", this.bookSalesTable())

1✔

3106

            .addTableData("customers", this.customerTable())

1✔

3107

            .enableColumnTitle(true)

1✔

3108

            .execute(stmt);

1✔

3109

1✔

3110

        let expected = [["id", "name", "max sold"],

1✔

3111

        ["C1", "Numereo Uno", 10],

1✔

3112

        ["C2", "Dewy Tuesdays", 100],

1✔

3113

        ["C3", "Tres Buon Goods", 1],

1✔

3114

        ["C4", "ForMe Resellers", 150],

1✔

3115

        ["C5", "Fe Fi Fo Giant Tiger", ""],

1✔

3116

        ["C6", "Sx in Cars", ""],

1✔

3117

        ["C7", "7th Heaven", ""]];

1✔

3118

1✔

3119

        return this.isEqual("selectCorrelatedSubQuery9", data, expected);

1✔

3120

1✔

3121

1✔

3122

    selectCorrelatedSubQuery10() {

1✔

3123

        let stmt = "select * from booksales as b1 where price in (select max(price) from booksales where b1.customer_id = customer_id and book_id <> '9')";

1✔

3124

1✔

3125

        let data = new TestSql()

1✔

3126

            .addTableData("booksales", this.bookSalesTable())

1✔

3127

            .enableColumnTitle(true)

1✔

3128

            .execute(stmt);

1✔

3129

1✔

3130

        let expected = [["BOOKSALES.INVOICE", "BOOKSALES.BOOK_ID", "BOOKSALES.CUSTOMER_ID", "BOOKSALES.QUANTITY", "BOOKSALES.PRICE", "BOOKSALES.DATE"],

1✔

3131

        ["I7201", "8", "C2", 3, 29.95, "05/01/2022"],

1✔

3132

        ["I7203", "1", "", 1, 90, "05/02/2022"],

1✔

3133

        ["I7204", "2", "C4", 100, 65.49, "05/03/2022"],

1✔

3134

        ["I7205", "7", "C1", 1, 33.97, "05/04/2022"]];

1✔

3135

1✔

3136

        return this.isEqual("selectCorrelatedSubQuery10", data, expected);

1✔

3137

1✔

3138

1✔

3139

    selectCorrelatedSubQuery11() {

1✔

3140

        let stmt = "select *, (select sum(quantity) from booksales where b1.customer_id = customer_id) as 'Total for Customer' from booksales as b1 where customer_id = ?1";

1✔

3141

1✔

3142

        let data = new TestSql()

1✔

3143

            .addTableData("booksales", this.bookSalesTable())

1✔

3144

            .enableColumnTitle(true)

1✔

3145

            .addBindParameter("C4")

1✔

3146

            .execute(stmt);

1✔

3147

1✔

3148

        let expected = [["BOOKSALES.INVOICE", "BOOKSALES.BOOK_ID", "BOOKSALES.CUSTOMER_ID", "BOOKSALES.QUANTITY", "BOOKSALES.PRICE", "BOOKSALES.DATE", "Total for Customer"],

1✔

3149

        ["I7204", "2", "C4", 100, 65.49, "05/03/2022", 300],

1✔

3150

        ["I7204", "3", "C4", 150, 24.95, "05/03/2022", 300],

1✔

3151

        ["I7204", "4", "C4", 50, 19.99, "05/03/2022", 300]];

1✔

3152

1✔

3153

        return this.isEqual("selectCorrelatedSubQuery11", data, expected);

1✔

3154

1✔

3155

1✔

3156

    selectFromSubQuery1() {

1✔

3157

        let stmt = "select score.customer_id, score.wins, score.loss, (score.wins / (score.wins+score.loss)) as rate from (select customer_id, sum(case when quantity < 100 then 1 else 0 end) as wins, sum(case when quantity >= 100 then 1 else 0 end) as loss from booksales group by customer_id) as score";

1✔

3158

1✔

3159

        let data = new TestSql()

1✔

3160

            .addTableData("booksales", this.bookSalesTable())

1✔

3161

            .enableColumnTitle(true)

1✔

3162

            .execute(stmt);

1✔

3163

1✔

3164

        let expected = [["score.customer_id", "score.wins", "score.loss", "rate"],

1✔

3165

        ["", 1, 0, 1],

1✔

3166

        ["C1", 2, 0, 1],

1✔

3167

        ["C2", 2, 1, 0.6666666666666666],

1✔

3168

        ["C3", 1, 0, 1],

1✔

3169

        ["C4", 1, 2, 0.3333333333333333]];

1✔

3170

1✔

3171

        return this.isEqual("selectFromSubQuery1", data, expected);

1✔

3172

1✔

3173

1✔

3174

    selectFromSubQuery2() {

1✔

3175

        let stmt = "select customer_id, wins, loss, (wins / (wins+loss)) as rate from (select customer_id, sum(case when quantity < 100 then 1 else 0 end) as wins, sum(case when quantity >= 100 then 1 else 0 end) as loss from booksales group by customer_id) as score";

1✔

3176

1✔

3177

        let data = new TestSql()

1✔

3178

            .addTableData("booksales", this.bookSalesTable())

1✔

3179

            .enableColumnTitle(true)

1✔

3180

            .execute(stmt);

1✔

3181

1✔

3182

        let expected = [["customer_id", "wins", "loss", "rate"],

1✔

3183

        ["", 1, 0, 1],

1✔

3184

        ["C1", 2, 0, 1],

1✔

3185

        ["C2", 2, 1, 0.6666666666666666],

1✔

3186

        ["C3", 1, 0, 1],

1✔

3187

        ["C4", 1, 2, 0.3333333333333333]];

1✔

3188

1✔

3189

        return this.isEqual("selectFromSubQuery2", data, expected);

1✔

3190

1✔

3191

1✔

3192

    selectFromSubQuery3() {

1✔

3193

        let stmt = "select invoice from (select invoice from booksales where customer_id = 'C1') as mysales";

1✔

3194

1✔

3195

        let data = new TestSql()

1✔

3196

            .addTableData("booksales", this.bookSalesTable())

1✔

3197

            .enableColumnTitle(true)

1✔

3198

            .execute(stmt);

1✔

3199

1✔

3200

        let expected = [["invoice"],

1✔

3201

        ["I7200"],

1✔

3202

        ["I7205"]];

1✔

3203

1✔

3204

        return this.isEqual("selectFromSubQuery3", data, expected);

1✔

3205

1✔

3206

1✔

3207

    selectFromSubQuery4() {

1✔

3208

        let stmt = "select table3.invoice from " +

1✔

3209

            "(select invoice, quantity from " +

1✔

3210

                "(select invoice, table1.QTY as quantity from " +

1✔

3211

                    "(select invoice, quantity as QTY, customer_id from booksales where quantity <= 10) as table1 "

1✔

3212

                + "where customer_id = 'C1') as table2) "

1✔

3213

            + "as table3";

1✔

3214

1✔

3215

        let data = new TestSql()

1✔

3216

            .addTableData("booksales", this.bookSalesTable())

1✔

3217

            .enableColumnTitle(true)

1✔

3218

            .execute(stmt);

1✔

3219

1✔

3220

        let expected = [["table3.invoice"],

1✔

3221

        ["I7200"],

1✔

3222

        ["I7205"]];

1✔

3223

1✔

3224

        return this.isEqual("selectFromSubQuery4", data, expected);

1✔

3225

1✔

3226

1✔

3227

    selectFromSubQuery5() {

1✔

3228

        let stmt = "select table3.invoice from " +

1✔

3229

            "(select * from " +

1✔

3230

                "(select invoice, table1.QTY as quantity from " +

1✔

3231

                    "(select invoice, quantity as QTY, customer_id from booksales where quantity <= 10) as table1 "

1✔

3232

                + "where customer_id = 'C1') as table2) "

1✔

3233

            + "as table3";

1✔

3234

1✔

3235

        let data = new TestSql()

1✔

3236

            .addTableData("booksales", this.bookSalesTable())

1✔

3237

            .enableColumnTitle(true)

1✔

3238

            .execute(stmt);

1✔

3239

1✔

3240

        let expected = [["table3.invoice"],

1✔

3241

        ["I7200"],

1✔

3242

        ["I7205"]];

1✔

3243

1✔

3244

        return this.isEqual("selectFromSubQuery5", data, expected);

1✔

3245

1✔

3246

1✔

3247

    //  S T A R T   O T H E R   T E S T S

1✔

3248

    parseTableSettings1() {

1✔

3249

        let data = parseTableSettings([['authors', 'authorsNamedRange', 60, false], ['editors', 'editorsRange', 30], ['people', 'peopleRange']], "", false);

1✔

3250

        let expected = [["authors", "authorsNamedRange", 60, false],

1✔

3251

        ["editors", "editorsRange", 30, true],

1✔

3252

        ["people", "peopleRange", 60, true]];

1✔

3253

1✔

3254

        return this.isEqual("parseTableSettings1", data, expected);

1✔

3255

1✔

3256

1✔

3257

    parseTableSettings2() {

1✔

3258

        let data = parseTableSettings([['authors', 'authorsNamedRange', 60], ['editors', 'editorsRange', 30], ['people']], "", false);

1✔

3259

        let expected = [["authors", "authorsNamedRange", 60, true],

1✔

3260

        ["editors", "editorsRange", 30, true],

1✔

3261

        ["people", "people", 60, true]];

1✔

3262

        return this.isEqual("parseTableSettings2", data, expected);

1✔

3263

1✔

3264

1✔

3265

    parseTableSettings3() {

1✔

3266

        let stmt = "select *, books.title, authors.first_name, editors.first_name, customers.name, customers.email, booksales.quantity from bookSales " +

1✔

3267

            "LEFT JOIN books ON booksales.book_id = books.id " +

1✔

3268

            "LEFT JOIN authors on books.author_id = authors.id " +

1✔

3269

            "LEFT JOIN editors on books.editor_id = editors.id " +

1✔

3270

            "LEFT JOIN customers on bookSales.customer_id = customers.id " +

1✔

3271

            "WHERE customers.email NOT LIKE '%gmail.com' " +

1✔

3272

            "UNION select * from bookSales2";

1✔

3273

1✔

3274

        let data = parseTableSettings([], stmt, false);

1✔

3275

        let expected = [["BOOKSALES", "BOOKSALES", 60, true],

1✔

3276

        ["BOOKS", "BOOKS", 60, true],

1✔

3277

        ["AUTHORS", "AUTHORS", 60, true],

1✔

3278

        ["EDITORS", "EDITORS", 60, true],

1✔

3279

        ["CUSTOMERS", "CUSTOMERS", 60, true],

1✔

3280

        ["BOOKSALES2", "BOOKSALES2", 60, true]];

1✔

3281

        return this.isEqual("parseTableSettings3", data, expected);

1✔

3282

1✔

3283

1✔

3284

    parseTableSettings4() {

1✔

3285

        let stmt = "select * from 'master transactions' where account in (select account_name from accounts) ";

1✔

3286

1✔

3287

        let data = parseTableSettings([], stmt, false);

1✔

3288

        let expected = [["'MASTER TRANSACTIONS'", "'MASTER TRANSACTIONS'", 60, true],

1✔

3289

        ["ACCOUNTS", "ACCOUNTS", 60, true]];

1✔

3290

        return this.isEqual("parseTableSettings4", data, expected);

1✔

3291

1✔

3292

1✔

3293

    parseTableSettings5() {

1✔

3294

        let stmt = "SELECT * " +

1✔

3295

            "FROM books " +

1✔

3296

            "WHERE author_id IN (select a.id from authors as a where first_name = ?) " +

1✔

3297

            "or editor_id in (select e.id from editors as e where last_name = ?) " +

1✔

3298

            "or title = ? " +

1✔

3299

            "ORDER BY title";

1✔

3300

1✔

3301

        let data = parseTableSettings([], stmt, false);

1✔

3302

        let expected = [["BOOKS", "BOOKS", 60, true],

1✔

3303

        ["AUTHORS", "AUTHORS", 60, true],

1✔

3304

        ["EDITORS", "EDITORS", 60, true]];

1✔

3305

        return this.isEqual("parseTableSettings5", data, expected);

1✔

3306

1✔

3307

1✔

3308

    parseTableSettings6() {

1✔

3309

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

1✔

3310

            "FROM books " +

1✔

3311

            "WHERE books.author_id NOT IN (SELECT id from authors)" +

1✔

3312

            "ORDER BY books.title";

1✔

3313

1✔

3314

        let data = parseTableSettings([], stmt, false);

1✔

3315

        let expected = [["BOOKS", "BOOKS", 60, true],

1✔

3316

        ["AUTHORS", "AUTHORS", 60, true]];

1✔

3317

        return this.isEqual("parseTableSettings6", data, expected);

1✔

3318

1✔

3319

1✔

3320

    parseTableSettings7() {

1✔

3321

        let stmt = "SELECT booksales.A as 'Invoice', booksales.B as 'Book ID', CUST.A, CUST.B FROM booksales " +

1✔

3322

            "LEFT JOIN customers as CUST on booksales.C = customers.A ";

1✔

3323

1✔

3324

        let data = parseTableSettings([], stmt, false);

1✔

3325

        let expected = [["BOOKSALES", "BOOKSALES", 60, true],

1✔

3326

        ["CUSTOMERS", "CUSTOMERS", 60, true]];

1✔

3327

1✔

3328

        return this.isEqual("parseTableSettings7", data, expected);

1✔

3329

1✔

3330

1✔

3331

    parseTableSettings8() {

1✔

3332

        let stmt = "select id, title, (select count(*) from booksales where books.id = booksales.book_id) from books";

1✔

3333

1✔

3334

        let data = parseTableSettings([], stmt, false);

1✔

3335

        let expected = [["BOOKS", "BOOKS", 60, true],

1✔

3336

        ["BOOKSALES", "BOOKSALES", 60, true]];

1✔

3337

1✔

3338

        return this.isEqual("parseTableSettings8", data, expected);

1✔

3339

1✔

3340

1✔

3341

    parseTableSettings9() {

1✔

3342

        let stmt = "select concat_ws('-', *) as Concatenated from booksales left join customers on booksales.customer_id = customers.id where concat_ws('-', *) like '%Way%'";

1✔

3343

1✔

3344

        let data = parseTableSettings([], stmt, false);

1✔

3345

        let expected = [["BOOKSALES", "BOOKSALES", 60, true],

1✔

3346

        ["CUSTOMERS", "CUSTOMERS", 60, true]];

1✔

3347

1✔

3348

        return this.isEqual("parseTableSettings9", data, expected);

1✔

3349

1✔

3350

1✔

3351

    parseTableSettings10() {

1✔

3352

        let stmt = "select * from customers where exists (SELECT * FROM booksales WHERE booksales.customer_id = customers.id) and email like '%gmail.com' ";

1✔

3353

1✔

3354

        let data = parseTableSettings([], stmt, false);

1✔

3355

        let expected = [["CUSTOMERS", "CUSTOMERS", 60, true],

1✔

3356

        ["BOOKSALES", "BOOKSALES", 60, true]];

1✔

3357

1✔

3358

        return this.isEqual("parseTableSettings10", data, expected);

1✔

3359

1✔

3360

1✔

3361

    parseTableSettings11() {

1✔

3362

        let stmt = "select customer_id, wins, loss, (wins / (wins+loss)) as rate from (select customer_id, sum(case when quantity < 100 then 1 else 0 end) as wins, sum(case when quantity >= 100 then 1 else 0 end) as loss from booksales group by customer_id) as score";

1✔

3363

1✔

3364

        let data = parseTableSettings([], stmt, false);

1✔

3365

        let expected = [["BOOKSALES", "BOOKSALES", 60, true]];

1✔

3366

1✔

3367

        return this.isEqual("parseTableSettings11", data, expected);

1✔

3368

1✔

3369

1✔

3370

1✔

3371

    parseTableSettings12() {

1✔

3372

        let stmt = "select table3.invoice from " +

1✔

3373

        "(select invoice, quantity from " +

1✔

3374

            "(select invoice, table1.QTY as quantity from " +

1✔

3375

                "(select invoice, quantity as QTY, customer_id from booksales where quantity <= 10) as table1 "

1✔

3376

            + "where customer_id = 'C1') as table2) "

1✔

3377

        + "as table3";

1✔

3378

1✔

3379

        let data = parseTableSettings([], stmt, false);

1✔

3380

        let expected = [["BOOKSALES", "BOOKSALES", 60, true]];

1✔

3381

1✔

3382

        return this.isEqual("parseTableSettings12", data, expected);

1✔

3383

1✔

3384

1✔

3385

    //  Mock the GAS sheets functions required to load.

1✔

3386

    testTableData1() {

1✔

3387

        try {

1✔

3393

1✔

3398

1✔

3399

        let [selectTrans, allTrans, allTrans2, allTrans3, allTrans4] = this.testTableData();

1✔

3400

1✔

3401

        let masterTrans = this.masterTransactionsTable();

1✔

3402

        masterTrans.shift();

1✔

3403

1✔

3404

        let expected = [["Transaction Date", "Gross", "Net"],

1✔

3405

        ["2019-06-07T04:00:00.000Z", 0, 12399.19]];

1✔

3406

1✔

3407

        let result = true;

1✔

3408

        result = result && this.isEqual("testTableData1.a", selectTrans, expected);

1✔

3409

        result = result && this.isEqual("testTableData1.b", allTrans, masterTrans);

1✔

3410

        result = result && this.isEqual("testTableData1.c", allTrans2, masterTrans);

1✔

3411

        result = result && this.isEqual("testTableData1.d", allTrans3, masterTrans);

1✔

3412

        result = result && this.isEqual("testTableData1.d", allTrans4, masterTrans);

1✔

3413

1✔

3414

        let data = gsSQL("select Name_of_Institution, Transaction_Date, Description_1, Description_2, Amount, Expense_Category, Account, Gross, Balance " +

1✔

3415

            "from 'Master Transactions' " +

1✔

3416

            "where transaction_date >= '6/7/2019' and transaction_date <= '6/20/2019'");

1✔

3417

        data.shift();

1✔

3418

1✔

3419

        result = result && this.isEqual("testTableData1.e", data, masterTrans);

1✔

3420

1✔

3421

        return result;

1✔

3422

1✔

3423

1✔

3424

    testTableData() {

1✔

3425

        //  Hey CJD, remember to set the startIncomeDate and endIncomeDate - June 7 to June 20 2019

1✔

3426

        const itemData = new TestSql()

1✔

3427

            .addTableData('mastertransactions', 'Master Transactions!$A$1:$I', 60)

1✔

3428

            .enableColumnTitle(true)

1✔

3429

            .addBindNamedRangeParameter('startIncomeDate')

1✔

3430

            .addBindNamedRangeParameter('endIncomeDate')

1✔

3431

            .execute("select transaction_date as 'Transaction Date', sum(gross) as Gross, sum(amount) as Net " +

1✔

3432

                "from mastertransactions " +

1✔

3433

                "where transaction_date >=  ?1 and transaction_date <= ?2 ");

1✔

3434

1✔

3435

        //  Load load from sheet.

1✔

3436

        let trans = new TestSql()

1✔

3437

            .addTableData('mastertransactions', 'Master Transactions!$A$1:$I', .1)

1✔

3438

            .enableColumnTitle(false)

1✔

3439

            .addBindNamedRangeParameter('startIncomeDate')

1✔

3440

            .addBindNamedRangeParameter('endIncomeDate')

1✔

3441

            .execute("select * " +

1✔

3442

                "from mastertransactions " +

1✔

3443

                "where transaction_date >=  ?1 and transaction_date <= ?2 ");

1✔

3444

1✔

3445

        Utilities.sleep(.12);

1✔

3446

1✔

3447

        //  Should load from sheet.

1✔

3448

        trans = new TestSql()

1✔

3449

            .addTableData('mastertransactions', 'Master Transactions!$A$1:$I', 0)

1✔

3450

            .enableColumnTitle(false)

1✔

3451

            .addBindNamedRangeParameter('startIncomeDate')

1✔

3452

            .addBindNamedRangeParameter('endIncomeDate')

1✔

3453

            .execute("select * " +

1✔

3454

                "from mastertransactions " +

1✔

3455

                "where transaction_date >=  ?1 and transaction_date <= ?2 ");

1✔

3456

1✔

3457

        //  Save to long term cache.

1✔

3458

        let trans2 = new TestSql()

1✔

3459

            .addTableData('mastertransactions', 'Master Transactions!$A$1:$I30', 25000)

1✔

3460

            .enableColumnTitle(false)

1✔

3461

            .addBindNamedRangeParameter('startIncomeDate')

1✔

3462

            .addBindNamedRangeParameter('endIncomeDate')

1✔

3463

            .execute("select * " +

1✔

3464

                "from mastertransactions " +

1✔

3465

                "where transaction_date >=  ?1 and transaction_date <= ?2 ");

1✔

3466

1✔

3467

        //  Load from long term cache.

1✔

3468

        let trans3 = new TestSql()

1✔

3469

            .addTableData('mastertransactions', 'Master Transactions!$A$1:$I30', 25000)

1✔

3470

            .enableColumnTitle(false)

1✔

3471

            .addBindNamedRangeParameter('startIncomeDate')

1✔

3472

            .addBindNamedRangeParameter('endIncomeDate')

1✔

3473

            .execute("select * " +

1✔

3474

                "from mastertransactions " +

1✔

3475

                "where transaction_date >=  ?1 and transaction_date <= ?2 ");

1✔

3476

1✔

3477

        //  Force the expiry times for all items in long term cache.

1✔

3478

        //  It does not remove items from cache, it just forces that check -

1✔

3479

        //  which would never happen in testing since the check timeout is 21,000 seconds.

1✔

3480

        TableData.forceLongCacheExpiryCheck();

1✔

3481

        let trans4 = new TestSql()

1✔

3482

            .addTableData('mastertransactions', 'Master Transactions!$A$1:$I30', 25000)

1✔

3483

            .enableColumnTitle(false)

1✔

3484

            .addBindNamedRangeParameter('startIncomeDate')

1✔

3485

            .addBindNamedRangeParameter('endIncomeDate')

1✔

3486

            .execute("select * " +

1✔

3487

                "from mastertransactions " +

1✔

3488

                "where transaction_date >=  ?1 and transaction_date <= ?2 ");

1✔

3489

1✔

3490

        //  This test is not going to run long enough for long cache to be forceably

1✔

3491

        //  expired, so we should make that long cache expire.

1✔

3492

1✔

3493

        return [itemData, trans, trans2, trans3, trans4];

1✔

3494

1✔

3495

1✔

3496

1✔

3497

    selectBadTable1() {

1✔

3498

        let stmt = "SELECT quantity, price, quantity * price from booksail where price * quantity > 100";

1✔

3499

1✔

3500

        let testSQL = new TestSql()

1✔

3501

            .addTableData("booksales", this.bookSalesTable())

1✔

3502

            .addTableData("editors", this.editorsTable())

1✔

3503

            .enableColumnTitle(true);

1✔

3504

1✔

3505

        let ex = "";

1✔

3506

        try {

1✔

3507

            testSQL.execute(stmt);

1✔

3508

1✔

3509

        catch (exceptionErr) {

1✔

3510

            ex = exceptionErr;

1✔

3511

1✔

3512

1✔

3513

        return this.isFail("selectBadTable1", ex);

1✔

3514

1✔

3515

1✔

3516

    selectBadMath1() {

1✔

3517

        let stmt = "SELECT quantity, price, quantity # price from booksales where price * quantity > 100";

1✔

3518

1✔

3519

        let testSQL = new TestSql()

1✔

3520

            .addTableData("booksales", this.bookSalesTable())

1✔

3521

            .enableColumnTitle(true);

1✔

3522

1✔

3523

        let ex = "";

1✔

3524

        try {

1✔

3525

            testSQL.execute(stmt);

1✔

3526

1✔

3527

        catch (exceptionErr) {

1✔

3528

            ex = exceptionErr;

1✔

3529

1✔

3530

1✔

3531

        return this.isFail("selectBadMath1", ex);

1✔

3532

1✔

3533

1✔

3534

    selectBadField1() {

1✔

3535

        let stmt = "SELECT quantity, prices from booksales ";

1✔

3536

1✔

3537

        let testSQL = new TestSql()

1✔

3538

            .addTableData("booksales", this.bookSalesTable())

1✔

3539

            .enableColumnTitle(true);

1✔

3540

1✔

3541

        let ex = "";

1✔

3542

        try {

1✔

3543

            testSQL.execute(stmt);

1✔

3544

1✔

3545

        catch (exceptionErr) {

1✔

3546

            ex = exceptionErr;

1✔

3547

1✔

3548

1✔

3549

        return this.isFail("selectBadField1", ex);

1✔

3550

1✔

3551

1✔

3552

    selectBadField1a() {

1✔

3553

        //  A SELECT you would encounter in the sub-query of a correlated lookup.

1✔

3554

        let stmt = "select count(*) from booksales where books.id = booksales.book_id";

1✔

3555

1✔

3556

        let testSQL = new TestSql()

1✔

3557

            .addTableData("booksales", this.bookSalesTable())

1✔

3558

            .enableColumnTitle(true);

1✔

3559

1✔

3560

        let ex = "";

1✔

3561

        try {

1✔

3562

            testSQL.execute(stmt);

1✔

3563

1✔

3564

        catch (exceptionErr) {

1✔

3565

            ex = exceptionErr;

1✔

3566

1✔

3567

1✔

3568

        return this.isFail("selectBadField1a", ex);

1✔

3569

1✔

3570

1✔

3571

    selectBadField2() {

1✔

3572

        let stmt = "SELECT sum(quantitys) from booksales ";

1✔

3573

1✔

3574

        let testSQL = new TestSql()

1✔

3575

            .addTableData("booksales", this.bookSalesTable())

1✔

3576

            .enableColumnTitle(true);

1✔

3577

1✔

3578

        let ex = "";

1✔

3579

        try {

1✔

3580

            testSQL.execute(stmt);

1✔

3581

1✔

3582

        catch (exceptionErr) {

1✔

3583

            ex = exceptionErr;

1✔

3584

1✔

3585

1✔

3586

        return this.isFail("selectBadField2", ex);

1✔

3587

1✔

3588

1✔

3589

    selectBadField3() {

1✔

3590

        let stmt = "SELECT  quantity, Sumthing(price) from booksales ";

1✔

3591

1✔

3592

        let testSQL = new TestSql()

1✔

3593

            .addTableData("booksales", this.bookSalesTable())

1✔

3594

            .enableColumnTitle(true);

1✔

3595

1✔

3596

        let ex = "";

1✔

3597

        try {

1✔

3598

            testSQL.execute(stmt);

1✔

3599

1✔

3600

        catch (exceptionErr) {

1✔

3601

            ex = exceptionErr;

1✔

3602

1✔

3603

1✔

3604

        return this.isFail("selectBadField3", ex);

1✔

3605

1✔

3606

1✔

3607

    selectBadField4() {

1✔

3608

        let stmt = "SELECT invoice, SUMM(quantity) from booksales group by invoice";

1✔

3609

1✔

3610

        let testSQL = new TestSql()

1✔

3611

            .addTableData("booksales", this.bookSalesTable())

1✔

3612

            .enableColumnTitle(true);

1✔

3613

1✔

3614

        let ex = "";

1✔

3615

        try {

1✔

3616

            testSQL.execute(stmt);

1✔

3617

1✔

3618

        catch (exceptionErr) {

1✔

3619

            ex = exceptionErr;

1✔

3620

1✔

3621

1✔

3622

        return this.isFail("selectBadField4", ex);

1✔

3623

1✔

3624

1✔

3625

    selectBadOp1() {

1✔

3626

        let stmt = "SELECT  quantity, Sum(price) from booksales where price >>! 0 ";

1✔

3627

1✔

3628

        let testSQL = new TestSql()

1✔

3629

            .addTableData("booksales", this.bookSalesTable())

1✔

3630

            .enableColumnTitle(true);

1✔

3631

1✔

3632

        let ex = "";

1✔

3633

        try {

1✔

3634

            testSQL.execute(stmt);

1✔

3635

1✔

3636

        catch (exceptionErr) {

1✔

3637

            ex = exceptionErr;

1✔

3638

1✔

3639

1✔

3640

        return this.isFail("selectBadOp1", ex);

1✔

3641

1✔

3642

1✔

3643

    selectBadAs1() {

1✔

3644

        let stmt = "SELECT  quantity, price ASE PrIcE from booksales ";

1✔

3645

1✔

3646

        let testSQL = new TestSql()

1✔

3647

            .addTableData("booksales", this.bookSalesTable())

1✔

3648

            .enableColumnTitle(true);

1✔

3649

1✔

3650

        let ex = "";

1✔

3651

        try {

1✔

3652

            testSQL.execute(stmt);

1✔

3653

1✔

3654

        catch (exceptionErr) {

1✔

3655

            ex = exceptionErr;

1✔

3656

1✔

3657

1✔

3658

        return this.isFail("selectBadAs1", ex);

1✔

3659

1✔

3660

1✔

3661

    selectBadConstant1() {

1✔

3662

        let stmt = "SELECT  quantity, price AS PrIcE from booksales where invoice = 'I7200 ";

1✔

3663

1✔

3664

        let testSQL = new TestSql()

1✔

3665

            .addTableData("booksales", this.bookSalesTable())

1✔

3666

            .enableColumnTitle(true);

1✔

3667

1✔

3668

        let ex = "";

1✔

3669

        try {

1✔

3670

            testSQL.execute(stmt);

1✔

3671

1✔

3672

        catch (exceptionErr) {

1✔

3673

            ex = exceptionErr;

1✔

3674

1✔

3675

1✔

3676

        return this.isFail("selectBadConstant1", ex);

1✔

3677

1✔

3678

1✔

3679

    selectBadConstant2() {

1✔

3680

        let stmt = "SELECT  quantity, price AS PrIcE from booksales where price > 1O0 ";

1✔

3681

1✔

3682

        let testSQL = new TestSql()

1✔

3683

            .addTableData("booksales", this.bookSalesTable())

1✔

3684

            .enableColumnTitle(true);

1✔

3685

1✔

3686

        let ex = "";

1✔

3687

        try {

1✔

3688

            testSQL.execute(stmt);

1✔

3689

1✔

3690

        catch (exceptionErr) {

1✔

3691

            ex = exceptionErr;

1✔

3692

1✔

3693

1✔

3694

        return this.isFail("selectBadConstant2", ex);

1✔

3695

1✔

3696

1✔

3697

    nonSelect1() {

1✔

3698

        let stmt = "delete from booksales where price > 1O0 ";

1✔

3699

1✔

3700

        let testSQL = new TestSql()

1✔

3701

            .addTableData("booksales", this.bookSalesTable())

1✔

3702

            .enableColumnTitle(true);

1✔

3703

1✔

3704

        let ex = "";

1✔

3705

        try {

1✔

3706

            testSQL.execute(stmt);

1✔

3707

1✔

3708

        catch (exceptionErr) {

1✔

3709

            ex = exceptionErr;

1✔

3710

1✔

3711

1✔

3712

        return this.isFail("nonSelect1", ex);

1✔

3713

1✔

3714

1✔

3715

    badJoin1() {

1✔

3716

        let stmt = "SELECT books.id, books.title, authors.first_name, authors.last_name " +

1✔

3717

            "FROM books " +

1✔

3718

            "INNER JOIN authors " +

1✔

3719

            "ON books.author_id = authors.di " +

1✔

3720

            "ORDER BY books.id";

1✔

3721

1✔

3722

        let testSQL = new TestSql()

1✔

3723

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

1✔

3724

            .addTableData("authors", this.authorsTable())

1✔

3725

            .enableColumnTitle(true);

1✔

3726

1✔

3727

        let ex = "";

1✔

3728

        try {

1✔

3729

            testSQL.execute(stmt);

1✔

3730

1✔

3731

        catch (exceptionErr) {

1✔

3732

            ex = exceptionErr;

1✔

3733

1✔

3734

1✔

3735

        return this.isFail("badJoin1", ex);

1✔

3736

1✔

3737

1✔

3738

    badJoin2() {

1✔

3739

        let stmt = "SELECT books.id, books.title, authors.first_name, authors.last_name " +

1✔

3740

            "FROM books " +

1✔

3741

            "INNER JOIN authors " +

1✔

3742

            "ON books.author_di = authors.id " +

1✔

3743

            "ORDER BY books.id";

1✔

3744

1✔

3745

        let testSQL = new TestSql()

1✔

3746

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

1✔

3747

            .addTableData("authors", this.authorsTable())

1✔

3748

            .enableColumnTitle(true);

1✔

3749

1✔

3750

        let ex = "";

1✔

3751

        try {

1✔

3752

            testSQL.execute(stmt);

1✔

3753

1✔

3754

        catch (exceptionErr) {

1✔

3755

            ex = exceptionErr;

1✔

3756

1✔

3757

1✔

3758

        return this.isFail("badJoin2", ex);

1✔

3759

1✔

3760

1✔

3761

    badJoin3() {

1✔

3762

        let stmt = "SELECT books.id, books.title, authors.first_name, authors.last_name " +

1✔

3763

            "FROM books " +

1✔

3764

            "INNER JOIN on authors " +

1✔

3765

            "books.author_id = authors.id " +

1✔

3766

            "ORDER BY books.id";

1✔

3767

1✔

3768

        let testSQL = new TestSql()

1✔

3769

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

1✔

3770

            .addTableData("authors", this.authorsTable())

1✔

3771

            .enableColumnTitle(true);

1✔

3772

1✔

3773

        let ex = "";

1✔

3774

        try {

1✔

3775

            testSQL.execute(stmt);

1✔

3776

1✔

3777

        catch (exceptionErr) {

1✔

3778

            ex = exceptionErr;

1✔

3779

1✔

3780

1✔

3781

        return this.isFail("badJoin3", ex);

1✔

3782

1✔

3783

1✔

3784

    badOrderBy1() {

1✔

3785

        let stmt = "select * from bookSales order by DATE DSC, customer_id asc";

1✔

3786

1✔

3787

        let testSQL = new TestSql()

1✔

3788

            .addTableData("bookSales", this.bookSalesTable())

1✔

3789

            .enableColumnTitle(true);

1✔

3790

1✔

3791

        let ex = "";

1✔

3792

        try {

1✔

3793

            testSQL.execute(stmt);

1✔

3794

1✔

3795

        catch (exceptionErr) {

1✔

3796

            ex = exceptionErr;

1✔

3797

1✔

3798

1✔

3799

        return this.isFail("badOrderBy1", ex);

1✔

3800

1✔

3801

1✔

3802

1✔

3803

    badOrderBy2() {

1✔

3804

        let stmt = "select * from bookSales order by ORDER_DATE";

1✔

3805

1✔

3806

        let testSQL = new TestSql()

1✔

3807

            .addTableData("bookSales", this.bookSalesTable())

1✔

3808

            .enableColumnTitle(true);

1✔

3809

1✔

3810

        let ex = "";

1✔

3811

        try {

1✔

3812

            testSQL.execute(stmt);

1✔

3813

1✔

3814

        catch (exceptionErr) {

1✔

3815

            ex = exceptionErr;

1✔

3816

1✔

3817

1✔

3818

        return this.isFail("badOrderBy2", ex);

1✔

3819

1✔

3820

1✔

3821

1✔

3822

    bindVariableMissing() {

1✔

3823

        let stmt = "select * from bookSales where date > ?1 AND date < ?2 OR book_id = ?3";

1✔

3824

1✔

3825

        let testSQL = new TestSql()

1✔

3826

            .addTableData("bookSales", this.bookSalesTable())

1✔

3827

            .enableColumnTitle(true)

1✔

3828

            .addBindParameter('05/01/2022')

1✔

3829

            .addBindParameter('05/04/2022')

1✔

3830

1✔

3831

        let ex = "";

1✔

3832

        try {

1✔

3833

            testSQL.execute(stmt);

1✔

3834

1✔

3835

        catch (exceptionErr) {

1✔

3836

            ex = exceptionErr;

1✔

3837

1✔

3838

1✔

3839

        return this.isFail("bindVariableMissing", ex);

1✔

3840

1✔

3841

1✔

3842

    bindVariableMissing1() {

1✔

3843

        let stmt = "select * from bookSales where date > ? AND date < ?";

1✔

3844

1✔

3845

        let testSQL = new TestSql()

1✔

3846

            .addTableData("bookSales", this.bookSalesTable())

1✔

3847

            .enableColumnTitle(true)

1✔

3848

            .addBindParameter('05/01/2022')

1✔

3849

            .addBindParameter('05/04/2022')

1✔

3850

1✔

3851

        let ex = "";

1✔

3852

        try {

1✔

3853

            testSQL.execute(stmt);

1✔

3854

1✔

3855

        catch (exceptionErr) {

1✔

3856

            ex = exceptionErr;

1✔

3857

1✔

3858

1✔

3859

        return this.isFail("bindVariableMissing1", ex);

1✔

3860

1✔

3861

1✔

3862

    selectNoFrom() {

1✔

3863

        let stmt = "SELECT quantity, prices for booksales ";

1✔

3864

1✔

3865

        let testSQL = new TestSql()

1✔

3866

            .addTableData("booksales", this.bookSalesTable())

1✔

3867

            .enableColumnTitle(true);

1✔

3868

1✔

3869

        let ex = "";

1✔

3870

        try {

1✔

3871

            testSQL.execute(stmt);

1✔

3872

1✔

3873

        catch (exceptionErr) {

1✔

3874

            ex = exceptionErr;

1✔

3875

1✔

3876

1✔

3877

        return this.isFail("selectNoFrom", ex);

1✔

3878

1✔

3879

1✔

3880

    selectNoTitles() {

1✔

3881

        let stmt = "SELECT quantity, prices from booksales ";

1✔

3882

        let dataTable = this.bookSalesTable();

1✔

3883

        dataTable.shift();

1✔

3884

        let ex = "";

1✔

3885

1✔

3886

        try {

1✔

3887

            let testSQL = new TestSql()

1✔

3888

                .addTableData("booksales", dataTable)

1✔

3889

                .enableColumnTitle(true);

1✔

3890

1✔

3891

            testSQL.execute(stmt);

1✔

3892

1✔

3893

        catch (exceptionErr) {

1✔

3894

            ex = exceptionErr;

1✔

3895

1✔

3896

1✔

3897

        return this.isFail("selectNoTitles", ex);

1✔

3898

1✔

3899

1✔

3900

1✔

3901

    selectFromSubQueryNoAlias() {

1✔

3902

        let stmt = "select invoice from (select invoice from booksales where customer_id = 'C1')";

1✔

3903

        let ex = "";

1✔

3904

1✔

3905

        let testSQL = new TestSql()

1✔

3906

            .addTableData("booksales", this.bookSalesTable())

1✔

3907

            .enableColumnTitle(true);

1✔

3908

1✔

3909

        try {

1✔

3910

            testSQL.execute(stmt);

1✔

3911

1✔

3912

        catch (exceptionErr) {

1✔

3913

            ex = exceptionErr;

1✔

3914

1✔

3915

1✔

3916

        return this.isFail("selectFromSubQueryNoAlias", ex);

1✔

3917

1✔

3918

1✔

3919

    badParseTableSettings1() {

1✔

3920

        let ex = "";

1✔

3921

        try {

1✔

3922

            let data = parseTableSettings([['authors', 'authorsNamedRange', true, 60, true], ['editors', 'editorsRange', 30], ['people']], "", false);

1✔

3923

1✔

3924

        catch (exceptionErr) {

1✔

3925

            ex = exceptionErr;

1✔

3926

1✔

3927

1✔

3928

        return this.isFail("badParseTableSettings1", ex);

1✔

3929

1✔

3930

1✔

3931

    pivotGroupByMissing() {

1✔

3932

        let stmt = "select sum(quantity) from bookSales where date > ?1 AND date < ?2 OR book_id = ?3 pivot customer_id";

1✔

3933

1✔

3934

        let testSQL = new TestSql()

1✔

3935

            .addTableData("bookSales", this.bookSalesTable())

1✔

3936

            .enableColumnTitle(true)

1✔

3937

            .addBindParameter('05/01/2022')

1✔

3938

            .addBindParameter('05/04/2022')

1✔

3939

1✔

3940

        let ex = "";

1✔

3941

        try {

1✔

3942

            testSQL.execute(stmt);

1✔

3943

1✔

3944

        catch (exceptionErr) {

1✔

3945

            ex = exceptionErr;

1✔

3946

1✔

3947

1✔

3948

        return this.isFail("pivotGroupByMissing", ex);

1✔

3949

1✔

3950

1✔

3951

    badUnion1() {

1✔

3952

        let stmt = "select * from authors UNION select * from customers";

1✔

3953

1✔

3954

        let testSQL = new TestSql()

1✔

3955

            .addTableData("authors", this.authorsTable())

1✔

3956

            .addTableData("customers", this.customerTable())

1✔

3957

            .enableColumnTitle(true);

1✔

3958

1✔

3959

        let ex = "";

1✔

3960

        try {

1✔

3961

            testSQL.execute(stmt);

1✔

3962

1✔

3963

        catch (exceptionErr) {

1✔

3964

            ex = exceptionErr;

1✔

3965

1✔

3966

1✔

3967

        return this.isFail("badUnion1", ex);

1✔

3968

1✔

3969

1✔

3970

    badFieldNames1() {

1✔

3971

        let stmt = "select id from books where author_id is not null";

1✔

3972

1✔

3973

        let booksTable = this.bookTable();

1✔

3974

        booksTable.shift();

1✔

3975

        booksTable.unshift(["id", "title", "type", "author id", "author_id", "translator id"]);

1✔

3976

1✔

3977

        let ex = "";

1✔

3978

        try {

1✔

3979

            let testSQL = new TestSql()

1✔

3980

                .addTableData("books", booksTable)

1✔

3981

                .enableColumnTitle(true);

1✔

3982

1✔

3983

            testSQL.execute(stmt);

1✔

3984

1✔

3985

        catch (exceptionErr) {

1✔

3986

            ex = exceptionErr;

1✔

3987

1✔

3988

1✔

3989

        return this.isFail("badFieldNames1", ex);

1✔

3990

1✔

3991

1✔

3992

    isFail(functionName, exceptionErr) {

1✔

3993

        if (exceptionErr != "") {

26✔

3994

            Logger.log(functionName + "  Captured Error:  " + exceptionErr)

26✔

3995

            Logger.log(functionName + "() ***   S U C C E S S   ***");

26✔

3996

            return true;

26✔

3997

26✔

4003

26✔

4004

1✔

4005

    isEqual(functionName, sqlDataArray, expectedArry) {

1✔

4006

        let isEqualTest = false;

135✔

4007

        let jsonData = JSON.stringify(sqlDataArray);

135✔

4008

        let expectedJSON = JSON.stringify(expectedArry);

135✔

4009

135✔

4010

        let isEqual = jsonData == expectedJSON;

135✔

4011

135✔

4017

135✔

4031

        else {

135✔

4032

            Logger.log(functionName + "() ***   S U C C E S S   ***");

135✔

4033

            isEqualTest = true;

135✔

4034

135✔

4035

135✔

4036

        return isEqualTest;

135✔

4037

135✔

4038

1✔

4039

1✔

4040

//  Remove comments for testing in NODE

1✔

4041

/*  *** DEBUG START ***

1✔

4042

testerSql();

1✔

4043

//  *** DEBUG END  ***/

1✔

4044

1✔

4045

function testerSql() {

1✔

4046

    let result = true;

1✔

4047

    let tester = new SqlTester();

1✔

4048

1✔

4049

    result = result && tester.selectAll1();

1✔

4050

    result = result && tester.selectAllCase1();

1✔

4051

    result = result && tester.selectIsNotNull1();

1✔

4052

    result = result && tester.selectIsNull1();

1✔

4053

    result = result && tester.innerJoin1a();

1✔

4054

    result = result && tester.innerJoin1case();

1✔

4055

    result = result && tester.innerJoin2();

1✔

4056

    result = result && tester.innerJoinAlias1();

1✔

4057

    result = result && tester.innerJoinAlias2();

1✔

4058

    result = result && tester.join2a();

1✔

4059

    result = result && tester.join2b();

1✔

4060

    result = result && tester.join3();

1✔

4061

    result = result && tester.joinLimit1();

1✔

4062

    result = result && tester.leftJoin1();

1✔

4063

    result = result && tester.rightJoin1();

1✔

4064

    result = result && tester.rightJoin2();

1✔

4065

    result = result && tester.fullJoin1();

1✔

4066

    result = result && tester.fullJoin2();

1✔

4067

    result = result && tester.fullJoin3();

1✔

4068

    result = result && tester.whereIn1();

1✔

4069

    result = result && tester.whereIn2();

1✔

4070

    result = result && tester.whereIn3();

1✔

4071

    result = result && tester.whereIn4();

1✔

4072

    result = result && tester.whereIn5();

1✔

4073

    result = result && tester.whereIn6();

1✔

4074

    result = result && tester.whereIn7();

1✔

4075

    result = result && tester.whereNotIn1();

1✔

4076

    result = result && tester.whereAndOr1();

1✔

4077

    result = result && tester.whereAndOr2();

1✔

4078

    result = result && tester.whereAndOr3();

1✔

4079

    result = result && tester.whereAndNotEqual2();

1✔

4080

    result = result && tester.whereAndNotEqual3();

1✔

4081

    result = result && tester.groupBy1();

1✔

4082

    result = result && tester.selectAgainNewBinds1();

1✔

4083

    result = result && tester.groupBy2();

1✔

4084

    result = result && tester.groupBy3();

1✔

4085

    result = result && tester.groupBy4();

1✔

4086

    result = result && tester.avgSelect1();

1✔

4087

    result = result && tester.funcsSelect2();

1✔

4088

    result = result && tester.innerSelect1();

1✔

4089

    result = result && tester.whereLike1();

1✔

4090

    result = result && tester.whereLike2();

1✔

4091

    result = result && tester.whereNotLike1();

1✔

4092

    result = result && tester.union1();

1✔

4093

    result = result && tester.unionAlias1();

1✔

4094

    result = result && tester.unionBind1();

1✔

4095

    result = result && tester.unionAll1();

1✔

4096

    result = result && tester.unionAll2();

1✔

4097

    result = result && tester.unionJoin1();

1✔

4098

    result = result && tester.except1();

1✔

4099

    result = result && tester.intersect1();

1✔

4100

    result = result && tester.orderByDesc1();

1✔

4101

    result = result && tester.orderByDesc2();

1✔

4102

    result = result && tester.orderByDesc3();

1✔

4103

    result = result && tester.distinct1();

1✔

4104

    result = result && tester.selectMath1();

1✔

4105

    result = result && tester.selectMathFunc1();

1✔

4106

    result = result && tester.selectMathFunc2();

1✔

4107

    result = result && tester.selectFuncs2();

1✔

4108

    result = result && tester.selectFuncs3();

1✔

4109

    result = result && tester.selectFuncs4();

1✔

4110

    result = result && tester.selectFuncs5();

1✔

4111

    result = result && tester.selectFuncs6();

1✔

4112

    result = result && tester.selectFuncs7();

1✔

4113

    result = result && tester.selectFuncInFunc1();

1✔

4114

    result = result && tester.selectFuncInFunc2();

1✔

4115

    result = result && tester.selectIF1();

1✔

4116

    result = result && tester.selectIF2();

1✔

4117

    result = result && tester.selectIF3();

1✔

4118

    result = result && tester.selectIF4();

1✔

4119

    result = result && tester.selectWhereCalc1();

1✔

4120

    result = result && tester.selectWhereCalc2();

1✔

4121

    result = result && tester.selectCase1();

1✔

4122

    result = result && tester.selectCase2();

1✔

4123

    result = result && tester.selectAlias1();

1✔

4124

    result = result && tester.groupPivot1();

1✔

4125

    result = result && tester.groupPivot2();

1✔

4126

    result = result && tester.groupPivot3();

1✔

4127

    result = result && tester.groupFunc1();

1✔

4128

    result = result && tester.groupFunc2();

1✔

4129

    result = result && tester.selectInGroupByPivot1();

1✔

4130

    result = result && tester.selectInGroupByPivot2();

1✔

4131

    result = result && tester.selectInGroupByPivot3();

1✔

4132

    result = result && tester.selectCount1();

1✔

4133

    result = result && tester.selectCount2();

1✔

4134

    result = result && tester.selectCount3();

1✔

4135

    result = result && tester.selectCount4();

1✔

4136

    result = result && tester.selectCount5();

1✔

4137

    result = result && tester.selectCount6();

1✔

4138

    result = result && tester.selectGroupByNotInSelect();

1✔

4139

    result = result && tester.selectGroupByNotInSelect2();

1✔

4140

    result = result && tester.selectOrderByNotInSelect();

1✔

4141

    result = result && tester.selectCoalesce();

1✔

4142

    result = result && tester.selectConcat_Ws();

1✔

4143

    result = result && tester.selectConcat_Ws2();

1✔

4144

    result = result && tester.selectNoTitle1();

1✔

4145

    result = result && tester.selectNested();

1✔

4146

    result = result && tester.selectNested2();

1✔

4147

    result = result && tester.selectCorrelatedSubQuery1();

1✔

4148

    result = result && tester.selectCorrelatedSubQuery2();

1✔

4149

    result = result && tester.selectCorrelatedSubQuery3();

1✔

4150

    result = result && tester.selectCorrelatedSubQuery4();

1✔

4151

    result = result && tester.selectCorrelatedSubQuery5();

1✔

4152

    result = result && tester.selectCorrelatedSubQuery6();

1✔

4153

    result = result && tester.selectCorrelatedSubQuery7();

1✔

4154

    result = result && tester.selectCorrelatedSubQuery8();

1✔

4155

    result = result && tester.selectCorrelatedSubQuery9();

1✔

4156

    result = result && tester.selectCorrelatedSubQuery10();

1✔

4157

    result = result && tester.selectCorrelatedSubQuery11();

1✔

4158

    result = result && tester.selectFromSubQuery1();

1✔

4159

    result = result && tester.selectFromSubQuery2();

1✔

4160

    result = result && tester.selectFromSubQuery3();

1✔

4161

    result = result && tester.selectFromSubQuery4();

1✔

4162

    result = result && tester.selectFromSubQuery5();

1✔

4163

1✔

4164

    result = result && tester.selectBadTable1();

1✔

4165

    result = result && tester.selectBadMath1();

1✔

4166

    result = result && tester.selectBadField1();

1✔

4167

    result = result && tester.selectBadField1a();

1✔

4168

    result = result && tester.selectBadField2();

1✔

4169

    result = result && tester.selectBadField3();

1✔

4170

    result = result && tester.selectBadField4();

1✔

4171

    result = result && tester.selectBadOp1();

1✔

4172

    result = result && tester.selectBadAs1();

1✔

4173

    result = result && tester.selectBadConstant1();

1✔

4174

    result = result && tester.selectBadConstant2();

1✔

4175

    result = result && tester.nonSelect1();

1✔

4176

    result = result && tester.badJoin1();

1✔

4177

    result = result && tester.badJoin2();

1✔

4178

    result = result && tester.badJoin3();

1✔

4179

    result = result && tester.badOrderBy1();

1✔

4180

    result = result && tester.badOrderBy2();

1✔

4181

    result = result && tester.bindVariableMissing();

1✔

4182

    result = result && tester.bindVariableMissing1();

1✔

4183

    result = result && tester.selectNoFrom();

1✔

4184

    result = result && tester.selectNoTitles();

1✔

4185

    result = result && tester.selectFromSubQueryNoAlias();

1✔

4186

    result = result && tester.pivotGroupByMissing();

1✔

4187

    result = result && tester.badUnion1();

1✔

4188

    result = result && tester.badFieldNames1();

1✔

4189

1✔

4190

    //  Sql.js unit tests.

1✔

4191

    result = result && tester.parseTableSettings1();

1✔

4192

    result = result && tester.parseTableSettings2();

1✔

4193

    result = result && tester.parseTableSettings3();

1✔

4194

    result = result && tester.parseTableSettings4();

1✔

4195

    result = result && tester.parseTableSettings5();

1✔

4196

    result = result && tester.parseTableSettings6();

1✔

4197

    result = result && tester.parseTableSettings7();

1✔

4198

    result = result && tester.parseTableSettings8();

1✔

4199

    result = result && tester.parseTableSettings9();

1✔

4200

    result = result && tester.parseTableSettings10();

1✔

4201

    result = result && tester.parseTableSettings11();

1✔

4202

    result = result && tester.parseTableSettings12();

1✔

4203

    result = result && tester.testTableData1();

1✔

4204

    result = result && tester.badParseTableSettings1();

1✔

4205

1✔

4206

    tester.isEqual("===  E N D   O F   T E S T S  ===", true, result);

1✔

4207

1✔

4208

    return result;

1✔

4209

1✔

demmings / gsSQL / 4027440288

Source File
Press 'n' to go to next uncovered line, 'b' for previous

demmings / gsSQL / 4027440288

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous