Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save m1ga/a66acbbdd9b3570dde17e21aa178f8f9 to your computer and use it in GitHub Desktop.
Save m1ga/a66acbbdd9b3570dde17e21aa178f8f9 to your computer and use it in GitHub Desktop.
Description bridge issue in SQLite implementation https://jira.appcelerator.org/browse/TIMOB-12291
// https://jira.appcelerator.org/browse/TIMOB-12291
exports.compareQueries = function () {
testCommonWay();
testGroupConcat();
};
const TEST_QUERY_EXEC_COUNT = 50;
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 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 selectGroupConcat() {
const dbName = 'storage';
const db = Ti.Database.open(dbName);
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) {
while (resultSet.isValidRow()) {
const concatenated = resultSet.fieldByName('conc');
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);
}
}
resultSet.next();
}
resultSet.close();
}
db.close();
// Ti.API.debug(`deviceCounter = ${devices.length}`);
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment