Description bridge issue in SQLite implementation https://jira.appcelerator.org/browse/TIMOB-12291

sqliteBridgeBottleneckTest.js

// https://jira.appcelerator.org/browse/TIMOB-12291

/*
[DEBUG] sqliteBridgeBottleneckTest.compareQueries() start
[DEBUG] testCommonWay() start
[DEBUG] testCommonWay() loop:100 complete in 5087 ms
[DEBUG] testGroupConcat() start
[DEBUG] testGroupConcat() loop:100 complete in 844 ms
[DEBUG] testRs2JSON() start
[DEBUG] testRs2JSON() loop:100 complete in 3253 ms
[DEBUG] testGroupConcatRs2JSON() start
[DEBUG] testGroupConcatRs2JSON() loop:100 complete in 379 ms
[DEBUG] sqliteBridgeBottleneckTest.compareQueries() end
*/

const rs2json = require('ti.rs2json');

exports.compareQueries = function () {
    testCommonWay();
    testGroupConcat();
    testRs2JSON();
    testGroupConcatRs2JSON();
};

const TEST_QUERY_EXEC_COUNT = 100;

function testCommonWay() {
    const startTime = new Date();
    Ti.API.debug(`testCommonWay() start`);

    for (let i of Array.from({length: TEST_QUERY_EXEC_COUNT})) {
        selectCommonWay();
    }

    const endTime = new Date();
    Ti.API.debug(`testCommonWay() loop:${TEST_QUERY_EXEC_COUNT} complete in ${endTime.getTime() - startTime.getTime()} ms`);
};

function testGroupConcat() {
    const startTime = new Date();
    Ti.API.debug(`testGroupConcat() start`);

    for (let i of Array.from({length: TEST_QUERY_EXEC_COUNT})) {
        selectGroupConcat();
    }

    const endTime = new Date();
    Ti.API.debug(`testGroupConcat() loop:${TEST_QUERY_EXEC_COUNT} complete in ${endTime.getTime() - startTime.getTime()} ms`);
};

function testGroupConcatRs2JSON() {
    const startTime = new Date();
    Ti.API.debug(`testGroupConcatRs2JSON() start`);

    for (let i of Array.from({length: TEST_QUERY_EXEC_COUNT})) {
        selectGroupConcatRs2JSON();
    }

    const endTime = new Date();
    Ti.API.debug(`testGroupConcatRs2JSON() loop:${TEST_QUERY_EXEC_COUNT} complete in ${endTime.getTime() - startTime.getTime()} ms`);
};

function testRs2JSON() {
    const startTime = new Date();
    Ti.API.debug(`testRs2JSON() start`);

    for (let i of Array.from({length: TEST_QUERY_EXEC_COUNT})) {
        selectRs2JSONWay();
    }

    const endTime = new Date();
    Ti.API.debug(`testRs2JSON() loop:${TEST_QUERY_EXEC_COUNT} complete in ${endTime.getTime() - startTime.getTime()} ms`);
};

function selectCommonWay() {
    const dbName = 'storage';
    const db = Ti.Database.open(dbName);
    const commonSelectQuery = `
        SELECT _id, EquipmentType, DeviceType, ModelNumber, SerialNumber, InstallationDate, DeviceLocation, ExpirationType
        FROM devices
        LIMIT 1000
    `;

    let devices = [];

    const resultSet = db.execute(commonSelectQuery, [])
    if (resultSet) {
        while (resultSet.isValidRow()) {
            // just process result
            const device = {
                _id: resultSet.fieldByName('_id'),
                EquipmentType: resultSet.fieldByName('EquipmentType'),
                DeviceType: resultSet.fieldByName('DeviceType'),
                ModelNumber: resultSet.fieldByName('ModelNumber'),
                SerialNumber: resultSet.fieldByName('SerialNumber'),
                InstallationDate: resultSet.fieldByName('InstallationDate'),
                DeviceLocation: resultSet.fieldByName('DeviceLocation'),
                ExpirationType: resultSet.fieldByName('ExpirationType')
            };
            devices.push(device);

            resultSet.next();
        }
        resultSet.close();
    }

    db.close();

    // Ti.API.debug(`deviceCounter = ${devices.length}`);
}


function selectRs2JSONWay() {
    const dbName = 'storage';
    const db = Ti.Database.open(dbName);
    const commonSelectQuery = `
        SELECT _id, EquipmentType, DeviceType, ModelNumber, SerialNumber, InstallationDate, DeviceLocation, ExpirationType
        FROM devices
        LIMIT 1000
    `;

    let devices = [];

    const resultSet = db.execute(commonSelectQuery, []);

    if (resultSet) {
        const results = rs2json.asJSON(resultSet);

        resultSet.close();

        for (let device in results) {
            devices.push(device);
        }
    }

    db.close();

    // Ti.API.debug(`selectRs2JSONWay deviceCounter = ${devices.length}`);
}

function selectGroupConcat() {
    const dbName = 'storage';
    const db = Ti.Database.open(dbName);
    const groupConcatQuery = `
        SELECT GROUP_CONCAT(JSON_OBJECT(
                'EquipmentType', EquipmentType,
                'DeviceType', DeviceType,
                'ModelNumber', ModelNumber,
                'SerialNumber', SerialNumber,
                'InstallationDate', InstallationDate,
                'DeviceLocation', DeviceLocation,
                'ExpirationType', ExpirationType
            )) as conc
        FROM (
            SELECT *
            FROM devices
            LIMIT 1000
        )
    `;

    let devices = [];

    const resultSet = db.execute(groupConcatQuery, []);
    if (resultSet) {
        while (resultSet.isValidRow()) {
            const concatenated = resultSet.fieldByName('conc');
            if (concatenated) {
                devices = JSON.parse(`[${concatenated.split(',')}]`);
            }
            resultSet.next();
        }
        resultSet.close();
    }

    db.close();

    // Ti.API.debug(`selectGroupConcat deviceCounter = ${devices.length}`);
    // Ti.API.debug(`selectGroupConcat deviceCounter = ${JSON.stringify(devices.slice(0, 3))}`);
}

function selectGroupConcatRs2JSON() {
    const dbName = 'storage';
    const db = Ti.Database.open(dbName);
    const groupConcatQuery = `
        SELECT  GROUP_CONCAT(JSON_OBJECT(
                'EquipmentType', EquipmentType,
                'DeviceType', DeviceType,
                'ModelNumber', ModelNumber,
                'SerialNumber', SerialNumber,
                'InstallationDate', InstallationDate,
                'DeviceLocation', DeviceLocation,
                'ExpirationType', ExpirationType
            )) as conc
        FROM (
            SELECT *
            FROM devices
            LIMIT 1000
        )
    `;

    // without JSON_OBJECT works x2 faster

    // const groupConcatQuery = `
    //     SELECT  GROUP_CONCAT(
    //                 _id                             ||','||
    //                 COALESCE(EquipmentType, 0)      ||','||
    //                 COALESCE(DeviceType, 0)         ||','||
    //                 COALESCE(ModelNumber, 0)        ||','||
    //                 COALESCE(SerialNumber, 0)       ||','||
    //                 COALESCE(InstallationDate, 0)   ||','||
    //                 COALESCE(DeviceLocation, 0)     ||','||
    //                 COALESCE(ExpirationType, 0)
    //             ) as conc
    //     FROM (
    //         SELECT *
    //         FROM devices
    //         LIMIT 1000
    //     )
    // `;

    let devices = [];


    const resultSet = db.execute(groupConcatQuery, []);

    if (resultSet) {
        const [results] = rs2json.asJSON(resultSet);
        resultSet.close();

        const concatenated = results['conc'];
        if (concatenated) {
            devices = JSON.parse(`[${concatenated.split(',')}]`);
        }

        // if (concatenated) {
        //     const concatenatedArray = concatenated.split(',');
        //     for (let j = 0; j < concatenatedArray.length; j = j + 8) {
        //         const device = {
        //             _id: concatenatedArray[j + 0],
        //             EquipmentType: concatenatedArray[j + 1],
        //             DeviceType: concatenatedArray[j + 2],
        //             ModelNumber: concatenatedArray[j + 3],
        //             SerialNumber: concatenatedArray[j + 4],
        //             InstallationDate: concatenatedArray[j + 5],
        //             DeviceLocation: concatenatedArray[j + 6],
        //             ExpirationType: concatenatedArray[j + 7]
        //         };
        //         devices.push(device);
        //     }
        // }
    }

    db.close();

    // Ti.API.debug(`selectGroupConcatRs2JSON deviceCounter = ${devices.length}`);
}