Socket
Socket
Sign inDemoInstall

jeep-sqlite

Package Overview
Dependencies
Maintainers
1
Versions
104
Alerts
File Explorer

Advanced tools

License
Socket logo

Install Socket

Detect and block malicious and high-risk dependencies

Install

jeep-sqlite - npm Package Compare versions

Comparing version 2.3.9 to 2.4.0

dist/collection/utils/utils-delete.js
dist/collection/utils/utils-delete.js.map
dist/collection/utils/utils-sqlstatement.js
dist/collection/utils/utils-sqlstatement.js.map
dist/jeep-sqlite/p-d6900b29.entry.js
dist/jeep-sqlite/p-d6900b29.entry.js.map
dist/types/utils/utils-delete.d.ts
dist/types/utils/utils-sqlstatement.d.ts

123

dist/collection/utils/database.js
import initSqlJs from 'sql.js';
import { getDBFromStore, setInitialDBToStore, setDBToStore, copyDBToStore, removeDBFromStore, isDBInStore, restoreDBFromStore } from './utils-store';
import { dbChanges, beginTransaction, rollbackTransaction, commitTransaction, execute, executeSet, run, queryAll, isTableExists, getVersion, isSqlDeleted, isLastModified, getTableList, setForeignKeyConstraintsEnabled } from './utils-sqlite';
import { createDatabaseSchema, createTablesData, createViews } from './utils-importJson';
import { isJsonSQLite } from './utils-json';
import { createExportObject, getSynchroDate, setLastExportDate, delExportedRows } from './utils-exportJson';
import { onUpgrade } from './utils-upgrade';
import { UtilsStore } from './utils-store';
import { UtilsImportJSON } from './utils-importJson';
import { UtilsJSON } from './utils-json';
import { UtilsExportJSON } from './utils-exportJson';
import { UtilsUpgrade } from './utils-upgrade';
import { UtilsSQLite } from './utils-sqlite';
export class Database {

@@ -32,3 +32,3 @@ constructor(databaseName, version, upgDict, store, autoSave, wasmPath) {

// retrieve the database if stored on localforage
const retDB = await getDBFromStore(this.dbName, this.store);
const retDB = await UtilsStore.getDBFromStore(this.dbName, this.store);
if (retDB != null) {

@@ -41,17 +41,17 @@ // Open existing database

this.mDb = new SQL.Database();
await setInitialDBToStore(this.dbName, this.store);
await UtilsStore.setInitialDBToStore(this.dbName, this.store);
}
this._isDBOpen = true;
// get the current version
let curVersion = await getVersion(this.mDb);
let curVersion = await UtilsSQLite.getVersion(this.mDb);
if (this.version > curVersion && (Object.keys(this.vUpgDict)).length > 0) {
try {
// copy the db
const isDB = await isDBInStore(this.dbName, this.store);
const isDB = await UtilsStore.isDBInStore(this.dbName, this.store);
if (isDB) {
await copyDBToStore(this.dbName, `backup-${this.dbName}`, this.store);
await UtilsStore.copyDBToStore(this.dbName, `backup-${this.dbName}`, this.store);
this.isBackup = true;
}
// execute the upgrade flow process
const changes = await onUpgrade(this.mDb, this.vUpgDict, curVersion, this.version);
const changes = await UtilsUpgrade.onUpgrade(this.mDb, this.vUpgDict, curVersion, this.version);
if (changes === -1) {

@@ -61,3 +61,3 @@ // restore the database from backup

if (this.isBackup) {
await restoreDBFromStore(this.dbName, 'backup', this.store);
await UtilsStore.restoreDBFromStore(this.dbName, 'backup', this.store);
}

@@ -71,3 +71,3 @@ }

if (this.isBackup) {
await removeDBFromStore(`backup-${this.dbName}`, this.store);
await UtilsStore.removeDBFromStore(`backup-${this.dbName}`, this.store);
}

@@ -79,3 +79,3 @@ }

if (this.isBackup) {
await restoreDBFromStore(this.dbName, 'backup', this.store);
await UtilsStore.restoreDBFromStore(this.dbName, 'backup', this.store);
}

@@ -98,3 +98,3 @@ }

// set Foreign Keys On
await setForeignKeyConstraintsEnabled(this.mDb, true);
await UtilsSQLite.setForeignKeyConstraintsEnabled(this.mDb, true);
return resolve();

@@ -132,6 +132,6 @@ });

// save the database to store
await setDBToStore(this.mDb, this.dbName, this.store);
await UtilsStore.setDBToStore(this.mDb, this.dbName, this.store);
if (setFK) {
// set Foreign Keys On
await setForeignKeyConstraintsEnabled(this.mDb, true);
await UtilsSQLite.setForeignKeyConstraintsEnabled(this.mDb, true);
}

@@ -160,3 +160,3 @@ }

// save the database to store
const curVersion = await getVersion(this.mDb);
const curVersion = await UtilsSQLite.getVersion(this.mDb);
return Promise.resolve(curVersion);

@@ -172,3 +172,3 @@ }

try {
const isExists = await isDBInStore(database, this.store);
const isExists = await UtilsStore.isDBInStore(database, this.store);
return Promise.resolve(isExists);

@@ -192,3 +192,3 @@ }

if (isExists) {
await removeDBFromStore(database, this.store);
await UtilsStore.removeDBFromStore(database, this.store);
}

@@ -207,5 +207,7 @@ return Promise.resolve();

}
let initChanges = -1;
try {
initChanges = await UtilsSQLite.dbChanges(this.mDb);
if (transaction && !this.isTransactionActive) {
await beginTransaction(this.mDb, this._isDBOpen);
await UtilsSQLite.beginTransaction(this.mDb, this._isDBOpen);
this.isTransactionActive = true;

@@ -219,8 +221,9 @@ }

try {
const changes = await execute(this.mDb, sql, false);
if (changes < 0) {
const mChanges = await UtilsSQLite.execute(this.mDb, sql, false);
if (mChanges < 0) {
return Promise.reject(new Error('ExecuteSQL: changes < 0'));
}
if (transaction && this.isTransactionActive)
await commitTransaction(this.mDb, this._isDBOpen);
await UtilsSQLite.commitTransaction(this.mDb, this._isDBOpen);
const changes = (await UtilsSQLite.dbChanges(this.mDb)) - initChanges;
return Promise.resolve(changes);

@@ -232,3 +235,3 @@ }

if (transaction && this.isTransactionActive)
await rollbackTransaction(this.mDb, this._isDBOpen);
await UtilsSQLite.rollbackTransaction(this.mDb, this._isDBOpen);
}

@@ -262,5 +265,5 @@ catch (err) {

try {
initChanges = await dbChanges(this.mDb);
initChanges = await UtilsSQLite.dbChanges(this.mDb);
if (transaction && !this.isTransactionActive) {
await beginTransaction(this.mDb, this._isDBOpen);
await UtilsSQLite.beginTransaction(this.mDb, this._isDBOpen);
this.isTransactionActive = true;

@@ -274,3 +277,3 @@ }

try {
const retObj = await executeSet(this.mDb, set, false, returnMode);
const retObj = await UtilsSQLite.executeSet(this.mDb, set, false, returnMode);
let lastId = retObj["lastId"];

@@ -281,4 +284,4 @@ if (lastId < 0) {

if (transaction && this.isTransactionActive)
await commitTransaction(this.mDb, this._isDBOpen);
const changes = (await dbChanges(this.mDb)) - initChanges;
await UtilsSQLite.commitTransaction(this.mDb, this._isDBOpen);
const changes = (await UtilsSQLite.dbChanges(this.mDb)) - initChanges;
retRes.changes = changes;

@@ -293,3 +296,3 @@ retRes.lastId = lastId;

if (transaction && this.isTransactionActive)
await rollbackTransaction(this.mDb, this._isDBOpen);
await UtilsSQLite.rollbackTransaction(this.mDb, this._isDBOpen);
}

@@ -321,3 +324,3 @@ catch (err) {

try {
let retArr = await queryAll(this.mDb, sql, values);
let retArr = await UtilsSQLite.queryAll(this.mDb, sql, values);
return Promise.resolve(retArr);

@@ -339,5 +342,5 @@ }

try {
initChanges = await dbChanges(this.mDb);
initChanges = await UtilsSQLite.dbChanges(this.mDb);
if (transaction && !this.isTransactionActive) {
await beginTransaction(this.mDb, this._isDBOpen);
await UtilsSQLite.beginTransaction(this.mDb, this._isDBOpen);
this.isTransactionActive = true;

@@ -351,3 +354,3 @@ }

try {
const retObj = await run(this.mDb, statement, values, false, returnMode);
const retObj = await UtilsSQLite.run(this.mDb, statement, values, false, returnMode);
lastId = retObj["lastId"];

@@ -358,4 +361,4 @@ if (lastId < 0) {

if (transaction && this.isTransactionActive)
await commitTransaction(this.mDb, this._isDBOpen);
const changes = (await dbChanges(this.mDb)) - initChanges;
await UtilsSQLite.commitTransaction(this.mDb, this._isDBOpen);
const changes = (await UtilsSQLite.dbChanges(this.mDb)) - initChanges;
retRes.changes = changes;

@@ -370,3 +373,3 @@ retRes.lastId = lastId;

if (transaction && this.isTransactionActive)
await rollbackTransaction(this.mDb, this._isDBOpen);
await UtilsSQLite.rollbackTransaction(this.mDb, this._isDBOpen);
}

@@ -398,3 +401,3 @@ catch (err) {

try {
let retArr = await getTableList(this.mDb);
let retArr = await UtilsSQLite.getTableList(this.mDb);
return Promise.resolve(retArr);

@@ -413,3 +416,3 @@ }

try {
const retB = await isTableExists(this.mDb, tableName);
const retB = await UtilsSQLite.isTableExists(this.mDb, tableName);
return Promise.resolve(retB);

@@ -430,6 +433,6 @@ }

try {
const retB = await isTableExists(this.mDb, 'sync_table');
const retB = await UtilsSQLite.isTableExists(this.mDb, 'sync_table');
if (!retB) {
const isLastMod = await isLastModified(this.mDb, this._isDBOpen);
const isDel = await isSqlDeleted(this.mDb, this._isDBOpen);
const isLastMod = await UtilsSQLite.isLastModified(this.mDb, this._isDBOpen);
const isDel = await UtilsSQLite.isSqlDeleted(this.mDb, this._isDBOpen);
if (isLastMod && isDel) {

@@ -444,3 +447,3 @@ const date = Math.round(new Date().getTime() / 1000);

"${date}");`;
changes = await execute(this.mDb, stmts, false);
changes = await UtilsSQLite.execute(this.mDb, stmts, false);
return Promise.resolve(changes);

@@ -468,7 +471,7 @@ }

try {
const isTable = await isTableExists(this.mDb, 'sync_table');
const isTable = await UtilsSQLite.isTableExists(this.mDb, 'sync_table');
if (!isTable) {
return Promise.reject(new Error('No sync_table available'));
}
const res = await getSynchroDate(this.mDb);
const res = await UtilsExportJSON.getSynchroDate(this.mDb);
return Promise.resolve(res);

@@ -488,3 +491,3 @@ }

try {
const isTable = await isTableExists(this.mDb, 'sync_table');
const isTable = await UtilsSQLite.isTableExists(this.mDb, 'sync_table');
if (!isTable) {

@@ -496,3 +499,3 @@ return Promise.reject(new Error('No sync_table available'));

stmt += `${sDate} WHERE id = 1;`;
const changes = await execute(this.mDb, stmt, false);
const changes = await UtilsSQLite.execute(this.mDb, stmt, false);
if (changes < 0) {

@@ -514,6 +517,6 @@ return { result: false, message: 'setSyncDate failed' };

// set Foreign Keys Off
await setForeignKeyConstraintsEnabled(this.mDb, false);
await UtilsSQLite.setForeignKeyConstraintsEnabled(this.mDb, false);
if (jsonData.tables && jsonData.tables.length > 0) {
// create the database schema
changes = await createDatabaseSchema(this.mDb, jsonData);
changes = await UtilsImportJSON.createDatabaseSchema(this.mDb, jsonData);
let msg = `Schema creation completed changes: ${changes}`;

@@ -523,3 +526,3 @@ importProgress.emit({ progress: msg });

// create the tables data
changes += await createTablesData(this.mDb, jsonData, importProgress);
changes += await UtilsImportJSON.createTablesData(this.mDb, jsonData, importProgress);
let msg = `Tables data creation completed changes: ${changes}`;

@@ -531,6 +534,6 @@ importProgress.emit({ progress: msg });

// create the views
changes += await createViews(this.mDb, jsonData);
changes += await UtilsImportJSON.createViews(this.mDb, jsonData);
}
// set Foreign Keys On
await setForeignKeyConstraintsEnabled(this.mDb, true);
await UtilsSQLite.setForeignKeyConstraintsEnabled(this.mDb, true);
await this.saveToStore();

@@ -555,7 +558,9 @@ return Promise.resolve(changes);

try {
const isTable = await isTableExists(this.mDb, 'sync_table');
const isTable = await UtilsSQLite.isTableExists(this.mDb, 'sync_table');
if (isTable) {
await setLastExportDate(this.mDb, (new Date()).toISOString());
await UtilsExportJSON
.setLastExportDate(this.mDb, (new Date()).toISOString());
}
const retJson = await createExportObject(this.mDb, inJson, exportProgress);
const retJson = await UtilsExportJSON
.createExportObject(this.mDb, inJson, exportProgress);
const keys = Object.keys(retJson);

@@ -567,3 +572,3 @@ if (keys.length === 0) {

}
const isValid = isJsonSQLite(retJson);
const isValid = UtilsJSON.isJsonSQLite(retJson);
if (isValid) {

@@ -587,3 +592,3 @@ return Promise.resolve(retJson);

try {
await delExportedRows(this.mDb);
await UtilsExportJSON.delExportedRows(this.mDb);
return Promise.resolve();

@@ -590,0 +595,0 @@ }

216

dist/collection/utils/utils-drop.js

@@ -1,119 +0,121 @@

import { queryAll, run, execute } from './utils-sqlite';
export const getTablesNames = async (db) => {
let sql = 'SELECT name FROM sqlite_master WHERE ';
sql += "type='table' AND name NOT LIKE 'sync_table' ";
sql += "AND name NOT LIKE '_temp_%' ";
sql += "AND name NOT LIKE 'sqlite_%' ";
sql += "ORDER BY rootpage DESC;";
const retArr = [];
try {
const retQuery = await queryAll(db, sql, []);
for (const query of retQuery) {
retArr.push(query.name);
import { UtilsSQLite } from './utils-sqlite';
export class UtilsDrop {
static async getTablesNames(db) {
let sql = 'SELECT name FROM sqlite_master WHERE ';
sql += "type='table' AND name NOT LIKE 'sync_table' ";
sql += "AND name NOT LIKE '_temp_%' ";
sql += "AND name NOT LIKE 'sqlite_%' ";
sql += "ORDER BY rootpage DESC;";
const retArr = [];
try {
const retQuery = await UtilsSQLite.queryAll(db, sql, []);
for (const query of retQuery) {
retArr.push(query.name);
}
return Promise.resolve(retArr);
}
return Promise.resolve(retArr);
catch (err) {
return Promise.reject(new Error(`GetTablesNames: ${err.message}`));
}
}
catch (err) {
return Promise.reject(new Error(`GetTablesNames: ${err.message}`));
static async getViewsNames(mDb) {
let sql = 'SELECT name FROM sqlite_master WHERE ';
sql += "type='view' AND name NOT LIKE 'sqlite_%' ";
sql += 'ORDER BY rootpage DESC;';
const retArr = [];
try {
const retQuery = await UtilsSQLite.queryAll(mDb, sql, []);
for (const query of retQuery) {
retArr.push(query.name);
}
return Promise.resolve(retArr);
}
catch (err) {
return Promise.reject(new Error(`getViewsNames: ${err.message}`));
}
}
};
export const getViewsNames = async (mDb) => {
let sql = 'SELECT name FROM sqlite_master WHERE ';
sql += "type='view' AND name NOT LIKE 'sqlite_%' ";
sql += 'ORDER BY rootpage DESC;';
const retArr = [];
try {
const retQuery = await queryAll(mDb, sql, []);
for (const query of retQuery) {
retArr.push(query.name);
static async dropElements(db, type) {
let msg = '';
let stmt1 = `AND name NOT LIKE ('sqlite_%')`;
switch (type) {
case 'index':
msg = 'DropIndexes';
break;
case 'trigger':
msg = 'DropTriggers';
break;
case 'table':
msg = 'DropTables';
stmt1 += ` AND name NOT IN ('sync_table')`;
break;
case 'view':
msg = 'DropViews';
break;
default:
return Promise.reject(new Error(`DropElements: ${type} ` + 'not found'));
}
return Promise.resolve(retArr);
}
catch (err) {
return Promise.reject(new Error(`getViewsNames: ${err.message}`));
}
};
export const dropElements = async (db, type) => {
let msg = '';
let stmt1 = `AND name NOT LIKE ('sqlite_%')`;
switch (type) {
case 'index':
msg = 'DropIndexes';
break;
case 'trigger':
msg = 'DropTriggers';
break;
case 'table':
msg = 'DropTables';
stmt1 += ` AND name NOT IN ('sync_table')`;
break;
case 'view':
msg = 'DropViews';
break;
default:
return Promise.reject(new Error(`DropElements: ${type} ` + 'not found'));
}
// get the element's names
let stmt = 'SELECT name FROM sqlite_master WHERE ';
stmt += `type = '${type}' ${stmt1};`;
try {
const elements = await queryAll(db, stmt, []);
if (elements.length > 0) {
const upType = type.toUpperCase();
const statements = [];
for (const elem of elements) {
let stmt = `DROP ${upType} IF EXISTS `;
stmt += `${elem.name};`;
statements.push(stmt);
}
for (const stmt of statements) {
const lastId = await run(db, stmt, [], false, 'no');
if (lastId < 0) {
return Promise.reject(new Error(`DropElements: ${msg}: lastId < 0`));
// get the element's names
let stmt = 'SELECT name FROM sqlite_master WHERE ';
stmt += `type = '${type}' ${stmt1};`;
try {
const elements = await UtilsSQLite.queryAll(db, stmt, []);
if (elements.length > 0) {
const upType = type.toUpperCase();
const statements = [];
for (const elem of elements) {
let stmt = `DROP ${upType} IF EXISTS `;
stmt += `${elem.name};`;
statements.push(stmt);
}
for (const stmt of statements) {
const lastId = await UtilsSQLite.run(db, stmt, [], false, 'no');
if (lastId < 0) {
return Promise.reject(new Error(`DropElements: ${msg}: lastId < 0`));
}
}
}
return Promise.resolve();
}
return Promise.resolve();
catch (err) {
return Promise.reject(new Error(`DropElements: ${msg}: ${err.message}`));
}
}
catch (err) {
return Promise.reject(new Error(`DropElements: ${msg}: ${err.message}`));
static async dropAll(db) {
try {
// drop tables
await UtilsDrop.dropElements(db, 'table');
// drop indexes
await UtilsDrop.dropElements(db, 'index');
// drop triggers
await UtilsDrop.dropElements(db, 'trigger');
// drop views
await UtilsDrop.dropElements(db, 'view');
// vacuum the database
await UtilsSQLite.run(db, 'VACUUM;', [], false, 'no');
return Promise.resolve();
}
catch (err) {
return Promise.reject(new Error(`DropAll: ${err.message}`));
}
}
};
export const dropAll = async (db) => {
try {
// drop tables
await dropElements(db, 'table');
// drop indexes
await dropElements(db, 'index');
// drop triggers
await dropElements(db, 'trigger');
// drop views
await dropElements(db, 'view');
// vacuum the database
await run(db, 'VACUUM;', [], false, 'no');
return Promise.resolve();
}
catch (err) {
return Promise.reject(new Error(`DropAll: ${err.message}`));
}
};
export const dropTempTables = async (db, alterTables) => {
const tempTables = Object.keys(alterTables);
const statements = [];
for (const tTable of tempTables) {
let stmt = 'DROP TABLE IF EXISTS ';
stmt += `_temp_${tTable};`;
statements.push(stmt);
}
try {
const changes = await execute(db, statements.join('\n'), false);
if (changes < 0) {
return Promise.reject(new Error('DropTempTables: changes < 0'));
static async dropTempTables(db, alterTables) {
const tempTables = Object.keys(alterTables);
const statements = [];
for (const tTable of tempTables) {
let stmt = 'DROP TABLE IF EXISTS ';
stmt += `_temp_${tTable};`;
statements.push(stmt);
}
return Promise.resolve();
try {
const changes = await UtilsSQLite.execute(db, statements.join('\n'), false);
if (changes < 0) {
return Promise.reject(new Error('DropTempTables: changes < 0'));
}
return Promise.resolve();
}
catch (err) {
return Promise.reject(new Error(`DropTempTables: ${err.message}`));
}
}
catch (err) {
return Promise.reject(new Error(`DropTempTables: ${err.message}`));
}
};
}
//# sourceMappingURL=utils-drop.js.map

1107

dist/collection/utils/utils-exportJson.js

@@ -1,35 +0,148 @@

import { execute, queryAll, run, isTableExists, getTableList } from './utils-sqlite';
import { checkSchemaValidity, checkIndexesValidity, checkTriggersValidity, getValues } from './utils-json';
export const createExportObject = async (db, sqlObj, exportProgress) => {
const retObj = {};
let tables = [];
let views = [];
let errmsg = '';
try {
// get View's name
views = await getViewsName(db);
// get Table's name
const resTables = await getTablesNameSQL(db);
if (resTables.length === 0) {
return Promise.reject(new Error("createExportObject: table's names failed"));
import { UtilsSQLite } from './utils-sqlite';
import { UtilsJSON } from './utils-json';
export class UtilsExportJSON {
static async createExportObject(db, sqlObj, exportProgress) {
const retObj = {};
let tables = [];
let views = [];
let errmsg = '';
try {
// get View's name
views = await UtilsExportJSON.getViewsName(db);
// get Table's name
const resTables = await UtilsExportJSON.getTablesNameSQL(db);
if (resTables.length === 0) {
return Promise.reject(new Error("createExportObject: table's names failed"));
}
else {
const isTable = await UtilsSQLite.isTableExists(db, 'sync_table');
if (!isTable && sqlObj.mode === 'partial') {
return Promise.reject(new Error('No sync_table available'));
}
switch (sqlObj.mode) {
case 'partial': {
tables = await UtilsExportJSON.getTablesPartial(db, resTables, exportProgress);
break;
}
case 'full': {
tables = await UtilsExportJSON.getTablesFull(db, resTables, exportProgress);
break;
}
default: {
errmsg =
'createExportObject: expMode ' + sqlObj.mode + ' not defined';
break;
}
}
if (errmsg.length > 0) {
return Promise.reject(new Error(errmsg));
}
if (tables.length > 0) {
retObj.database = sqlObj.database;
retObj.version = sqlObj.version;
retObj.encrypted = sqlObj.encrypted;
retObj.mode = sqlObj.mode;
retObj.tables = tables;
if (views.length > 0) {
retObj.views = views;
}
}
return Promise.resolve(retObj);
}
}
else {
const isTable = await isTableExists(db, 'sync_table');
if (!isTable && sqlObj.mode === 'partial') {
return Promise.reject(new Error('No sync_table available'));
catch (err) {
return Promise.reject(new Error('createExportObject: ' + err.message));
}
}
static async getViewsName(mDb) {
const views = [];
let sql = 'SELECT name,sql FROM sqlite_master WHERE ';
sql += "type='view' AND name NOT LIKE 'sqlite_%';";
let retQuery = [];
try {
retQuery = await UtilsSQLite.queryAll(mDb, sql, []);
for (const query of retQuery) {
const view = {};
view.name = query.name;
view.value = query.sql.substring(query.sql.indexOf('AS ') + 3);
views.push(view);
}
switch (sqlObj.mode) {
case 'partial': {
tables = await getTablesPartial(db, resTables, exportProgress);
return Promise.resolve(views);
}
catch (err) {
return Promise.reject(new Error(`getViewsName: ${err.message}`));
}
}
static async getTablesFull(db, resTables, exportProgress) {
const tables = [];
let errmsg = '';
try {
// Loop through the tables
for (const rTable of resTables) {
let tableName;
let sqlStmt;
if (rTable.name) {
tableName = rTable.name;
}
else {
errmsg = 'GetTablesFull: no name';
break;
}
case 'full': {
tables = await getTablesFull(db, resTables, exportProgress);
if (rTable.sql) {
sqlStmt = rTable.sql;
}
else {
errmsg = 'GetTablesFull: no sql';
break;
}
default: {
errmsg =
'createExportObject: expMode ' + sqlObj.mode + ' not defined';
const table = {};
// create Table's Schema
const schema = await UtilsExportJSON.getSchema(sqlStmt /*, tableName*/);
if (schema.length === 0) {
errmsg = 'GetTablesFull: no Schema returned';
break;
}
// check schema validity
await UtilsJSON.checkSchemaValidity(schema);
// create Table's indexes if any
const indexes = await UtilsExportJSON.getIndexes(db, tableName);
if (indexes.length > 0) {
// check indexes validity
await UtilsJSON.checkIndexesValidity(indexes);
}
// create Table's triggers if any
const triggers = await UtilsExportJSON.getTriggers(db, tableName);
if (triggers.length > 0) {
// check triggers validity
await UtilsJSON.checkTriggersValidity(triggers);
}
let msg = `Full: Table ${tableName} schema export completed ...`;
exportProgress.emit({ progress: msg });
// create Table's Data
const query = `SELECT * FROM ${tableName};`;
const values = await UtilsJSON.getValues(db, query, tableName);
table.name = tableName;
if (schema.length > 0) {
table.schema = schema;
}
else {
errmsg = `GetTablesFull: must contain schema`;
break;
}
if (indexes.length > 0) {
table.indexes = indexes;
}
if (triggers.length > 0) {
table.triggers = triggers;
}
if (values.length > 0) {
table.values = values;
}
if (Object.keys(table).length <= 1) {
errmsg = `GetTablesFull: table ${tableName} is not a jsonTable`;
break;
}
msg = `Full: Table ${tableName} table data export completed ...`;
exportProgress.emit({ progress: msg });
tables.push(table);
}

@@ -39,575 +152,468 @@ if (errmsg.length > 0) {

}
if (tables.length > 0) {
retObj.database = sqlObj.database;
retObj.version = sqlObj.version;
retObj.encrypted = sqlObj.encrypted;
retObj.mode = sqlObj.mode;
retObj.tables = tables;
if (views.length > 0) {
retObj.views = views;
}
}
return Promise.resolve(retObj);
return Promise.resolve(tables);
}
}
catch (err) {
return Promise.reject(new Error('createExportObject: ' + err.message));
}
};
export const getViewsName = async (mDb) => {
const views = [];
let sql = 'SELECT name,sql FROM sqlite_master WHERE ';
sql += "type='view' AND name NOT LIKE 'sqlite_%';";
let retQuery = [];
try {
retQuery = await queryAll(mDb, sql, []);
for (const query of retQuery) {
const view = {};
view.name = query.name;
view.value = query.sql.substring(query.sql.indexOf('AS ') + 3);
views.push(view);
catch (err) {
return Promise.reject(new Error(`GetTablesFull: ${err.message}`));
}
return Promise.resolve(views);
}
catch (err) {
return Promise.reject(new Error(`getViewsName: ${err.message}`));
}
};
export const getTablesFull = async (db, resTables, exportProgress) => {
const tables = [];
let errmsg = '';
try {
// Loop through the tables
for (const rTable of resTables) {
let tableName;
let sqlStmt;
if (rTable.name) {
tableName = rTable.name;
static async getSchema(sqlStmt /*, tableName: string*/) {
const schema = [];
// take the substring between parenthesis
const openPar = sqlStmt.indexOf('(');
const closePar = sqlStmt.lastIndexOf(')');
let sstr = sqlStmt.substring(openPar + 1, closePar);
// check if there is other parenthesis and replace the ',' by '§'
try {
sstr = await UtilsExportJSON.modEmbeddedParentheses(sstr);
const sch = sstr.split(",");
// for each element of the array split the
// first word as key
for (let j = 0; j < sch.length; j++) {
let row = [];
const scht = sch[j].replace(/\n/g, "").trim();
row[0] = scht.substring(0, scht.indexOf(" "));
row[1] = scht.substring(scht.indexOf(" ") + 1);
const jsonRow = {};
if (row[0].toUpperCase() === "FOREIGN") {
const oPar = scht.indexOf("(");
const cPar = scht.indexOf(")");
const fk = scht.substring(oPar + 1, cPar);
const fknames = fk.split('§');
row[0] = fknames.join(',');
row[0] = row[0].replace(/, /g, ",");
row[1] = scht.substring(cPar + 2);
jsonRow['foreignkey'] = row[0];
}
else if (row[0].toUpperCase() === "PRIMARY") {
const oPar = scht.indexOf("(");
const cPar = scht.indexOf(")");
const pk = scht.substring(oPar + 1, cPar);
const pknames = pk.split('§');
row[0] = "CPK_" + pknames.join('_');
row[0] = row[0].replace(/_ /g, "_");
row[1] = scht;
jsonRow['constraint'] = row[0];
}
else if (row[0].toUpperCase() === "CONSTRAINT") {
let tRow = [];
const row1t = row[1].trim();
tRow[0] = row1t.substring(0, row1t.indexOf(" "));
tRow[1] = row1t.substring(row1t.indexOf(" ") + 1);
row[0] = tRow[0];
jsonRow['constraint'] = row[0];
row[1] = tRow[1];
}
else {
jsonRow['column'] = row[0];
}
jsonRow['value'] = row[1].replace(/§/g, ",");
schema.push(jsonRow);
}
else {
errmsg = 'GetTablesFull: no name';
break;
}
if (rTable.sql) {
sqlStmt = rTable.sql;
}
else {
errmsg = 'GetTablesFull: no sql';
break;
}
const table = {};
// create Table's Schema
const schema = await getSchema(sqlStmt /*, tableName*/);
if (schema.length === 0) {
errmsg = 'GetTablesFull: no Schema returned';
break;
}
// check schema validity
await checkSchemaValidity(schema);
// create Table's indexes if any
const indexes = await getIndexes(db, tableName);
if (indexes.length > 0) {
// check indexes validity
await checkIndexesValidity(indexes);
}
// create Table's triggers if any
const triggers = await getTriggers(db, tableName);
if (triggers.length > 0) {
// check triggers validity
await checkTriggersValidity(triggers);
}
let msg = `Full: Table ${tableName} schema export completed ...`;
exportProgress.emit({ progress: msg });
// create Table's Data
const query = `SELECT * FROM ${tableName};`;
const values = await getValues(db, query, tableName);
table.name = tableName;
if (schema.length > 0) {
table.schema = schema;
}
else {
errmsg = `GetTablesFull: must contain schema`;
break;
}
if (indexes.length > 0) {
table.indexes = indexes;
}
if (triggers.length > 0) {
table.triggers = triggers;
}
if (values.length > 0) {
table.values = values;
}
if (Object.keys(table).length <= 1) {
errmsg = `GetTablesFull: table ${tableName} is not a jsonTable`;
break;
}
msg = `Full: Table ${tableName} table data export completed ...`;
exportProgress.emit({ progress: msg });
tables.push(table);
return Promise.resolve(schema);
}
if (errmsg.length > 0) {
return Promise.reject(new Error(errmsg));
catch (err) {
return Promise.reject(new Error(err.message));
}
return Promise.resolve(tables);
}
catch (err) {
return Promise.reject(new Error(`GetTablesFull: ${err.message}`));
}
};
export const getSchema = async (sqlStmt /*, tableName: string*/) => {
const schema = [];
// take the substring between parenthesis
const openPar = sqlStmt.indexOf('(');
const closePar = sqlStmt.lastIndexOf(')');
let sstr = sqlStmt.substring(openPar + 1, closePar);
// check if there is other parenthesis and replace the ',' by '§'
try {
sstr = await modEmbeddedParentheses(sstr);
const sch = sstr.split(",");
// for each element of the array split the
// first word as key
for (let j = 0; j < sch.length; j++) {
let row = [];
const scht = sch[j].replace(/\n/g, "").trim();
row[0] = scht.substring(0, scht.indexOf(" "));
row[1] = scht.substring(scht.indexOf(" ") + 1);
const jsonRow = {};
if (row[0].toUpperCase() === "FOREIGN") {
const oPar = scht.indexOf("(");
const cPar = scht.indexOf(")");
const fk = scht.substring(oPar + 1, cPar);
const fknames = fk.split('§');
row[0] = fknames.join(',');
row[0] = row[0].replace(/, /g, ",");
row[1] = scht.substring(cPar + 2);
jsonRow['foreignkey'] = row[0];
}
else if (row[0].toUpperCase() === "PRIMARY") {
const oPar = scht.indexOf("(");
const cPar = scht.indexOf(")");
const pk = scht.substring(oPar + 1, cPar);
const pknames = pk.split('§');
row[0] = "CPK_" + pknames.join('_');
row[0] = row[0].replace(/_ /g, "_");
row[1] = scht;
jsonRow['constraint'] = row[0];
}
else if (row[0].toUpperCase() === "CONSTRAINT") {
let tRow = [];
const row1t = row[1].trim();
tRow[0] = row1t.substring(0, row1t.indexOf(" "));
tRow[1] = row1t.substring(row1t.indexOf(" ") + 1);
row[0] = tRow[0];
jsonRow['constraint'] = row[0];
row[1] = tRow[1];
}
else {
jsonRow['column'] = row[0];
}
jsonRow['value'] = row[1].replace(/§/g, ",");
schema.push(jsonRow);
}
return Promise.resolve(schema);
}
catch (err) {
return Promise.reject(new Error(err.message));
}
};
export const getIndexes = async (db, tableName) => {
const indexes = [];
let errmsg = '';
try {
let stmt = 'SELECT name,tbl_name,sql FROM sqlite_master WHERE ';
stmt += `type = 'index' AND tbl_name = '${tableName}' `;
stmt += `AND sql NOTNULL;`;
const retIndexes = await queryAll(db, stmt, []);
if (retIndexes.length > 0) {
for (const rIndex of retIndexes) {
const keys = Object.keys(rIndex);
if (keys.length === 3) {
if (rIndex['tbl_name'] === tableName) {
const sql = rIndex['sql'];
const mode = sql.includes('UNIQUE') ? 'UNIQUE' : '';
const oPar = sql.lastIndexOf('(');
const cPar = sql.lastIndexOf(')');
const index = {};
index.name = rIndex['name'];
index.value = sql.slice(oPar + 1, cPar);
if (mode.length > 0)
index.mode = mode;
indexes.push(index);
static async getIndexes(db, tableName) {
const indexes = [];
let errmsg = '';
try {
let stmt = 'SELECT name,tbl_name,sql FROM sqlite_master WHERE ';
stmt += `type = 'index' AND tbl_name = '${tableName}' `;
stmt += `AND sql NOTNULL;`;
const retIndexes = await UtilsSQLite.queryAll(db, stmt, []);
if (retIndexes.length > 0) {
for (const rIndex of retIndexes) {
const keys = Object.keys(rIndex);
if (keys.length === 3) {
if (rIndex['tbl_name'] === tableName) {
const sql = rIndex['sql'];
const mode = sql.includes('UNIQUE') ? 'UNIQUE' : '';
const oPar = sql.lastIndexOf('(');
const cPar = sql.lastIndexOf(')');
const index = {};
index.name = rIndex['name'];
index.value = sql.slice(oPar + 1, cPar);
if (mode.length > 0)
index.mode = mode;
indexes.push(index);
}
else {
errmsg = `GetIndexes: Table ${tableName} doesn't match`;
break;
}
}
else {
errmsg = `GetIndexes: Table ${tableName} doesn't match`;
errmsg = `GetIndexes: Table ${tableName} creating indexes`;
break;
}
}
else {
errmsg = `GetIndexes: Table ${tableName} creating indexes`;
break;
if (errmsg.length > 0) {
return Promise.reject(new Error(errmsg));
}
}
if (errmsg.length > 0) {
return Promise.reject(new Error(errmsg));
}
return Promise.resolve(indexes);
}
return Promise.resolve(indexes);
catch (err) {
return Promise.reject(new Error(`GetIndexes: ${err.message}`));
}
}
catch (err) {
return Promise.reject(new Error(`GetIndexes: ${err.message}`));
}
};
export const getTriggers = async (db, tableName) => {
const triggers = [];
try {
let stmt = 'SELECT name,tbl_name,sql FROM sqlite_master WHERE ';
stmt += `type = 'trigger' AND tbl_name = '${tableName}' `;
stmt += `AND sql NOT NULL;`;
const retTriggers = await queryAll(db, stmt, []);
if (retTriggers.length > 0) {
for (const rTrg of retTriggers) {
const keys = Object.keys(rTrg);
if (keys.length === 3) {
if (rTrg['tbl_name'] === tableName) {
const sql = rTrg['sql'];
const name = rTrg['name'];
let sqlArr = sql.split(name);
if (sqlArr.length != 2) {
return Promise.reject(new Error(`GetTriggers: sql split name does not return 2 values`));
}
if (!sqlArr[1].includes(tableName)) {
return Promise.reject(new Error(`GetTriggers: sql split does not contains ${tableName}`));
}
const timeEvent = sqlArr[1].split(tableName, 1)[0].trim();
sqlArr = sqlArr[1].split(timeEvent + ' ' + tableName);
if (sqlArr.length != 2) {
return Promise.reject(new Error(`GetTriggers: sql split tableName does not return 2 values`));
}
let condition = '';
let logic = '';
if (sqlArr[1].trim().substring(0, 5).toUpperCase() !== 'BEGIN') {
sqlArr = sqlArr[1].trim().split('BEGIN');
static async getTriggers(db, tableName) {
const triggers = [];
try {
let stmt = 'SELECT name,tbl_name,sql FROM sqlite_master WHERE ';
stmt += `type = 'trigger' AND tbl_name = '${tableName}' `;
stmt += `AND sql NOT NULL;`;
const retTriggers = await UtilsSQLite.queryAll(db, stmt, []);
if (retTriggers.length > 0) {
for (const rTrg of retTriggers) {
const keys = Object.keys(rTrg);
if (keys.length === 3) {
if (rTrg['tbl_name'] === tableName) {
const sql = rTrg['sql'];
const name = rTrg['name'];
let sqlArr = sql.split(name);
if (sqlArr.length != 2) {
return Promise.reject(new Error(`GetTriggers: sql split BEGIN does not return 2 values`));
return Promise.reject(new Error(`GetTriggers: sql split name does not return 2 values`));
}
condition = sqlArr[0].trim();
logic = 'BEGIN' + sqlArr[1];
if (!sqlArr[1].includes(tableName)) {
return Promise.reject(new Error(`GetTriggers: sql split does not contains ${tableName}`));
}
const timeEvent = sqlArr[1].split(tableName, 1)[0].trim();
sqlArr = sqlArr[1].split(timeEvent + ' ' + tableName);
if (sqlArr.length != 2) {
return Promise.reject(new Error(`GetTriggers: sql split tableName does not return 2 values`));
}
let condition = '';
let logic = '';
if (sqlArr[1].trim().substring(0, 5).toUpperCase() !== 'BEGIN') {
sqlArr = sqlArr[1].trim().split('BEGIN');
if (sqlArr.length != 2) {
return Promise.reject(new Error(`GetTriggers: sql split BEGIN does not return 2 values`));
}
condition = sqlArr[0].trim();
logic = 'BEGIN' + sqlArr[1];
}
else {
logic = sqlArr[1].trim();
}
const trigger = {};
trigger.name = name;
trigger.logic = logic;
if (condition.length > 0)
trigger.condition = condition;
trigger.timeevent = timeEvent;
triggers.push(trigger);
}
else {
logic = sqlArr[1].trim();
return Promise.reject(new Error(`GetTriggers: Table ${tableName} doesn't match`));
}
const trigger = {};
trigger.name = name;
trigger.logic = logic;
if (condition.length > 0)
trigger.condition = condition;
trigger.timeevent = timeEvent;
triggers.push(trigger);
}
else {
return Promise.reject(new Error(`GetTriggers: Table ${tableName} doesn't match`));
return Promise.reject(new Error(`GetTriggers: Table ${tableName} creating indexes`));
}
}
else {
return Promise.reject(new Error(`GetTriggers: Table ${tableName} creating indexes`));
}
}
return Promise.resolve(triggers);
}
return Promise.resolve(triggers);
catch (err) {
return Promise.reject(new Error(`GetTriggers: ${err.message}`));
}
}
catch (err) {
return Promise.reject(new Error(`GetTriggers: ${err.message}`));
}
};
export const getTablesPartial = async (db, resTables, exportProgress) => {
const tables = [];
let modTables = {};
let syncDate = 0;
let modTablesKeys = [];
let errmsg = '';
try {
// Get the syncDate and the Modified Tables
const partialModeData = await getPartialModeData(db, resTables);
if (Object.keys(partialModeData).includes('syncDate')) {
syncDate = partialModeData.syncDate;
}
if (Object.keys(partialModeData).includes('modTables')) {
modTables = partialModeData.modTables;
modTablesKeys = Object.keys(modTables);
}
// Loop trough tables
for (const rTable of resTables) {
let tableName = '';
let sqlStmt = '';
if (rTable.name) {
tableName = rTable.name;
static async getTablesPartial(db, resTables, exportProgress) {
const tables = [];
let modTables = {};
let syncDate = 0;
let modTablesKeys = [];
let errmsg = '';
try {
// Get the syncDate and the Modified Tables
const partialModeData = await UtilsExportJSON
.getPartialModeData(db, resTables);
if (Object.keys(partialModeData).includes('syncDate')) {
syncDate = partialModeData.syncDate;
}
else {
errmsg = 'GetTablesFull: no name';
break;
if (Object.keys(partialModeData).includes('modTables')) {
modTables = partialModeData.modTables;
modTablesKeys = Object.keys(modTables);
}
if (rTable.sql) {
sqlStmt = rTable.sql;
}
else {
errmsg = 'GetTablesFull: no sql';
break;
}
if (modTablesKeys.length == 0 ||
modTablesKeys.indexOf(tableName) === -1 ||
modTables[tableName] == 'No') {
continue;
}
const table = {};
let schema = [];
let indexes = [];
let triggers = [];
table.name = rTable;
if (modTables[table.name] === 'Create') {
// create Table's Schema
schema = await getSchema(sqlStmt /*, tableName*/);
// Loop trough tables
for (const rTable of resTables) {
let tableName = '';
let sqlStmt = '';
if (rTable.name) {
tableName = rTable.name;
}
else {
errmsg = 'GetTablesFull: no name';
break;
}
if (rTable.sql) {
sqlStmt = rTable.sql;
}
else {
errmsg = 'GetTablesFull: no sql';
break;
}
if (modTablesKeys.length == 0 ||
modTablesKeys.indexOf(tableName) === -1 ||
modTables[tableName] == 'No') {
continue;
}
const table = {};
let schema = [];
let indexes = [];
let triggers = [];
table.name = rTable;
if (modTables[table.name] === 'Create') {
// create Table's Schema
schema = await UtilsExportJSON.getSchema(sqlStmt /*, tableName*/);
if (schema.length > 0) {
// check schema validity
await UtilsJSON.checkSchemaValidity(schema);
}
// create Table's indexes if any
indexes = await UtilsExportJSON.getIndexes(db, tableName);
if (indexes.length > 0) {
// check indexes validity
await UtilsJSON.checkIndexesValidity(indexes);
}
// create Table's triggers if any
triggers = await UtilsExportJSON.getTriggers(db, tableName);
if (triggers.length > 0) {
// check triggers validity
await UtilsJSON.checkTriggersValidity(triggers);
}
}
let msg = `Partial: Table ${tableName} schema export completed ...`;
exportProgress.emit({ progress: msg });
// create Table's Data
let query = '';
if (modTables[tableName] === 'Create') {
query = `SELECT * FROM ${tableName};`;
}
else {
query =
`SELECT * FROM ${tableName} ` +
`WHERE last_modified > ${syncDate};`;
}
const values = await UtilsJSON.getValues(db, query, tableName);
// check the table object validity
table.name = tableName;
if (schema.length > 0) {
// check schema validity
await checkSchemaValidity(schema);
table.schema = schema;
}
// create Table's indexes if any
indexes = await getIndexes(db, tableName);
if (indexes.length > 0) {
// check indexes validity
await checkIndexesValidity(indexes);
table.indexes = indexes;
}
// create Table's triggers if any
triggers = await getTriggers(db, tableName);
if (triggers.length > 0) {
// check triggers validity
await checkTriggersValidity(triggers);
table.triggers = triggers;
}
if (values.length > 0) {
table.values = values;
}
if (Object.keys(table).length <= 1) {
errmsg = `GetTablesPartial: table ${tableName} is not a jsonTable`;
break;
}
msg = `Partial: Table ${tableName} table data export completed ...`;
exportProgress.emit({ progress: msg });
tables.push(table);
}
let msg = `Partial: Table ${tableName} schema export completed ...`;
exportProgress.emit({ progress: msg });
// create Table's Data
let query = '';
if (modTables[tableName] === 'Create') {
query = `SELECT * FROM ${tableName};`;
if (errmsg.length > 0) {
return Promise.reject(new Error(errmsg));
}
else {
query =
`SELECT * FROM ${tableName} ` +
`WHERE last_modified > ${syncDate};`;
return Promise.resolve(tables);
}
catch (err) {
return Promise.reject(new Error(`GetTablesPartial: ${err.message}`));
}
}
static async getPartialModeData(db, resTables) {
const retData = {};
try {
// get the synchronization date
const syncDate = await UtilsExportJSON.getSynchroDate(db);
if (syncDate <= 0) {
return Promise.reject(new Error(`GetPartialModeData: no syncDate`));
}
const values = await getValues(db, query, tableName);
// check the table object validity
table.name = tableName;
if (schema.length > 0) {
table.schema = schema;
// get the tables which have been updated
// since last synchronization
const modTables = await UtilsExportJSON.getTablesModified(db, resTables, syncDate);
if (modTables.length <= 0) {
return Promise.reject(new Error(`GetPartialModeData: no modTables`));
}
if (indexes.length > 0) {
table.indexes = indexes;
}
if (triggers.length > 0) {
table.triggers = triggers;
}
if (values.length > 0) {
table.values = values;
}
if (Object.keys(table).length <= 1) {
errmsg = `GetTablesPartial: table ${tableName} is not a jsonTable`;
break;
}
msg = `Partial: Table ${tableName} table data export completed ...`;
exportProgress.emit({ progress: msg });
tables.push(table);
retData.syncDate = syncDate;
retData.modTables = modTables;
return Promise.resolve(retData);
}
if (errmsg.length > 0) {
return Promise.reject(new Error(errmsg));
catch (err) {
return Promise.reject(new Error(`GetPartialModeData: ${err.message}`));
}
return Promise.resolve(tables);
}
catch (err) {
return Promise.reject(new Error(`GetTablesPartial: ${err.message}`));
}
};
export const getPartialModeData = async (db, resTables) => {
const retData = {};
try {
// get the synchronization date
const syncDate = await getSynchroDate(db);
if (syncDate <= 0) {
return Promise.reject(new Error(`GetPartialModeData: no syncDate`));
static async getTablesNameSQL(db) {
let sql = 'SELECT name,sql FROM sqlite_master WHERE ';
sql += "type='table' AND name NOT LIKE 'sync_table' ";
sql += "AND name NOT LIKE '_temp_%' ";
sql += "AND name NOT LIKE 'sqlite_%';";
try {
const retQuery = await UtilsSQLite.queryAll(db, sql, []);
return Promise.resolve(retQuery);
}
// get the tables which have been updated
// since last synchronization
const modTables = await getTablesModified(db, resTables, syncDate);
if (modTables.length <= 0) {
return Promise.reject(new Error(`GetPartialModeData: no modTables`));
catch (err) {
return Promise.reject(new Error(`getTablesNamesSQL: ${err.message}`));
}
retData.syncDate = syncDate;
retData.modTables = modTables;
return Promise.resolve(retData);
}
catch (err) {
return Promise.reject(new Error(`GetPartialModeData: ${err.message}`));
}
};
export const getTablesNameSQL = async (db) => {
let sql = 'SELECT name,sql FROM sqlite_master WHERE ';
sql += "type='table' AND name NOT LIKE 'sync_table' ";
sql += "AND name NOT LIKE '_temp_%' ";
sql += "AND name NOT LIKE 'sqlite_%';";
try {
const retQuery = await queryAll(db, sql, []);
return Promise.resolve(retQuery);
}
catch (err) {
return Promise.reject(new Error(`getTablesNamesSQL: ${err.message}`));
}
};
export const getTablesModified = async (db, tables, syncDate) => {
let errmsg = '';
try {
const retModified = {};
for (const rTable of tables) {
let mode;
// get total count of the table
let stmt = 'SELECT count(*) AS tcount ';
stmt += `FROM ${rTable.name};`;
let retQuery = await queryAll(db, stmt, []);
if (retQuery.length != 1) {
errmsg = 'GetTableModified: total ' + 'count not returned';
break;
static async getTablesModified(db, tables, syncDate) {
let errmsg = '';
try {
const retModified = {};
for (const rTable of tables) {
let mode;
// get total count of the table
let stmt = 'SELECT count(*) AS tcount ';
stmt += `FROM ${rTable.name};`;
let retQuery = await UtilsSQLite.queryAll(db, stmt, []);
if (retQuery.length != 1) {
errmsg = 'GetTableModified: total ' + 'count not returned';
break;
}
const totalCount = retQuery[0]['tcount'];
// get total count of modified since last sync
stmt = 'SELECT count(*) AS mcount FROM ';
stmt += `${rTable.name} WHERE last_modified > `;
stmt += `${syncDate};`;
retQuery = await UtilsSQLite.queryAll(db, stmt, []);
if (retQuery.length != 1)
break;
const totalModifiedCount = retQuery[0]['mcount'];
if (totalModifiedCount === 0) {
mode = 'No';
}
else if (totalCount === totalModifiedCount) {
mode = 'Create';
}
else {
mode = 'Modified';
}
const key = rTable.name;
retModified[key] = mode;
}
const totalCount = retQuery[0]['tcount'];
// get total count of modified since last sync
stmt = 'SELECT count(*) AS mcount FROM ';
stmt += `${rTable.name} WHERE last_modified > `;
stmt += `${syncDate};`;
retQuery = await queryAll(db, stmt, []);
if (retQuery.length != 1)
break;
const totalModifiedCount = retQuery[0]['mcount'];
if (totalModifiedCount === 0) {
mode = 'No';
if (errmsg.length > 0) {
return Promise.reject(new Error(errmsg));
}
else if (totalCount === totalModifiedCount) {
mode = 'Create';
}
else {
mode = 'Modified';
}
const key = rTable.name;
retModified[key] = mode;
return Promise.resolve(retModified);
}
if (errmsg.length > 0) {
return Promise.reject(new Error(errmsg));
catch (err) {
return Promise.reject(new Error(`GetTableModified: ${err.message}`));
}
return Promise.resolve(retModified);
}
catch (err) {
return Promise.reject(new Error(`GetTableModified: ${err.message}`));
}
};
export const getSynchroDate = async (db) => {
try {
const stmt = `SELECT sync_date FROM sync_table WHERE id = 1;`;
const res = await queryAll(db, stmt, []);
return Promise.resolve(res[0]["sync_date"]);
}
catch (err) {
const msg = `GetSynchroDate: ${err.message}`;
return Promise.reject(new Error(msg));
}
};
export const getLastExportDate = async (db) => {
try {
const stmt = `SELECT sync_date FROM sync_table WHERE id = 2;`;
const res = await queryAll(db, stmt, []);
if (res.length === 0) {
return Promise.resolve(-1);
}
else {
static async getSynchroDate(db) {
try {
const stmt = `SELECT sync_date FROM sync_table WHERE id = 1;`;
const res = await UtilsSQLite.queryAll(db, stmt, []);
return Promise.resolve(res[0]["sync_date"]);
}
catch (err) {
const msg = `GetSynchroDate: ${err.message}`;
return Promise.reject(new Error(msg));
}
}
catch (err) {
const msg = `getLastExport: ${err.message}`;
return Promise.reject(new Error(msg));
}
};
export const setLastExportDate = async (db, lastExportedDate) => {
try {
const isTable = await isTableExists(db, 'sync_table');
if (!isTable) {
return Promise.reject(new Error('setLastExportDate: No sync_table available'));
static async getLastExportDate(db) {
try {
const stmt = `SELECT sync_date FROM sync_table WHERE id = 2;`;
const res = await UtilsSQLite.queryAll(db, stmt, []);
if (res.length === 0) {
return Promise.resolve(-1);
}
else {
return Promise.resolve(res[0]["sync_date"]);
}
}
const sDate = Math.round(new Date(lastExportedDate).getTime() / 1000);
let stmt = "";
if (await getLastExportDate(db) > 0) {
stmt = `UPDATE sync_table SET sync_date = ${sDate} WHERE id = 2;`;
catch (err) {
const msg = `getLastExport: ${err.message}`;
return Promise.reject(new Error(msg));
}
else {
stmt = `INSERT INTO sync_table (sync_date) VALUES (${sDate});`;
}
static async setLastExportDate(db, lastExportedDate) {
try {
const isTable = await UtilsSQLite.isTableExists(db, 'sync_table');
if (!isTable) {
return Promise.reject(new Error('setLastExportDate: No sync_table available'));
}
const sDate = Math.round(new Date(lastExportedDate).getTime() / 1000);
let stmt = "";
if (await UtilsExportJSON.getLastExportDate(db) > 0) {
stmt = `UPDATE sync_table SET sync_date = ${sDate} WHERE id = 2;`;
}
else {
stmt = `INSERT INTO sync_table (sync_date) VALUES (${sDate});`;
}
const changes = await UtilsSQLite.execute(db, stmt, false);
if (changes < 0) {
return { result: false, message: 'setLastExportDate failed' };
}
else {
return { result: true };
}
}
const changes = await execute(db, stmt, false);
if (changes < 0) {
return { result: false, message: 'setLastExportDate failed' };
catch (err) {
return { result: false, message: `setLastExportDate failed: ${err.message}` };
}
else {
return { result: true };
}
}
catch (err) {
return { result: false, message: `setLastExportDate failed: ${err.message}` };
}
};
export const delExportedRows = async (db) => {
let lastExportDate;
try {
// check if synchronization table exists
const isTable = await isTableExists(db, 'sync_table');
if (!isTable) {
return Promise.reject(new Error('DelExportedRows: No sync_table available'));
static async delExportedRows(db) {
let lastExportDate;
try {
// check if synchronization table exists
const isTable = await UtilsSQLite.isTableExists(db, 'sync_table');
if (!isTable) {
return Promise.reject(new Error('DelExportedRows: No sync_table available'));
}
// get the last export date
lastExportDate = await UtilsExportJSON.getLastExportDate(db);
if (lastExportDate < 0) {
return Promise.reject(new Error("DelExportedRows: no last exported date available"));
}
// get the table' name list
const resTables = await UtilsSQLite.getTableList(db);
if (resTables.length === 0) {
return Promise.reject(new Error("DelExportedRows: No table's names returned"));
}
// Loop through the tables
for (const table of resTables) {
let lastId = -1;
// define the delete statement
const delStmt = `DELETE FROM ${table}
WHERE sql_deleted = 1 AND last_modified < ${lastExportDate};`;
lastId = await UtilsSQLite.run(db, delStmt, [], true, 'no');
if (lastId < 0) {
return Promise.reject(new Error('DelExportedRows: lastId < 0'));
}
}
}
// get the last export date
lastExportDate = await getLastExportDate(db);
if (lastExportDate < 0) {
return Promise.reject(new Error("DelExportedRows: no last exported date available"));
catch (err) {
return Promise.reject(new Error(`DelExportedRows failed: ${err.message}`));
}
// get the table' name list
const resTables = await getTableList(db);
if (resTables.length === 0) {
return Promise.reject(new Error("DelExportedRows: No table's names returned"));
}
static async modEmbeddedParentheses(sstr) {
const oParArray = UtilsExportJSON.indexOfChar(sstr, '(');
const cParArray = UtilsExportJSON.indexOfChar(sstr, ')');
if (oParArray.length != cParArray.length) {
return Promise.reject("ModEmbeddedParentheses: Not same number of '(' & ')'");
}
// Loop through the tables
for (const table of resTables) {
let lastId = -1;
// define the delete statement
const delStmt = `DELETE FROM ${table}
WHERE sql_deleted = 1 AND last_modified < ${lastExportDate};`;
lastId = await run(db, delStmt, [], true, 'no');
if (lastId < 0) {
return Promise.reject(new Error('DelExportedRows: lastId < 0'));
}
if (oParArray.length === 0) {
return Promise.resolve(sstr);
}
}
catch (err) {
return Promise.reject(new Error(`DelExportedRows failed: ${err.message}`));
}
};
const modEmbeddedParentheses = async (sstr) => {
const oParArray = indexOfChar(sstr, '(');
const cParArray = indexOfChar(sstr, ')');
if (oParArray.length != cParArray.length) {
return Promise.reject("ModEmbeddedParentheses: Not same number of '(' & ')'");
}
if (oParArray.length === 0) {
return Promise.resolve(sstr);
}
let resStmt = sstr.substring(0, oParArray[0] - 1);
for (let i = 0; i < oParArray.length; i++) {
let str;
if (i < oParArray.length - 1) {
if (oParArray[i + 1] < cParArray[i]) {
str = sstr.substring(oParArray[i] - 1, cParArray[i + 1]);
i++;
let resStmt = sstr.substring(0, oParArray[0] - 1);
for (let i = 0; i < oParArray.length; i++) {
let str;
if (i < oParArray.length - 1) {
if (oParArray[i + 1] < cParArray[i]) {
str = sstr.substring(oParArray[i] - 1, cParArray[i + 1]);
i++;
}
else {
str = sstr.substring(oParArray[i] - 1, cParArray[i]);
}
}

@@ -617,20 +623,17 @@ else {

}
const newS = str.replace(/,/g, "§");
resStmt += newS;
if (i < oParArray.length - 1) {
resStmt += sstr.substring(cParArray[i], oParArray[i + 1] - 1);
}
}
else {
str = sstr.substring(oParArray[i] - 1, cParArray[i]);
}
const newS = str.replace(/,/g, "§");
resStmt += newS;
if (i < oParArray.length - 1) {
resStmt += sstr.substring(cParArray[i], oParArray[i + 1] - 1);
}
resStmt += sstr.substring(cParArray[cParArray.length - 1], sstr.length);
return Promise.resolve(resStmt);
}
resStmt += sstr.substring(cParArray[cParArray.length - 1], sstr.length);
return Promise.resolve(resStmt);
};
const indexOfChar = (str, char) => {
let tmpArr = [...str];
char = char.toLowerCase();
return tmpArr.reduce((results, elem, idx) => elem.toLowerCase() === char ? [...results, idx] : results, []);
};
static indexOfChar(str, char) {
let tmpArr = [...str];
char = char.toLowerCase();
return tmpArr.reduce((results, elem, idx) => elem.toLowerCase() === char ? [...results, idx] : results, []);
}
}
//# sourceMappingURL=utils-exportJson.js.map

806

dist/collection/utils/utils-importJson.js

@@ -1,276 +0,298 @@

import { setVersion, beginTransaction, rollbackTransaction, commitTransaction, execute, dbChanges, run, queryAll, isTableExists } from './utils-sqlite';
import { dropAll } from './utils-drop';
import { getTableColumnNamesTypes, getValues } from './utils-json';
export const createDatabaseSchema = async (db, jsonData) => {
let changes = -1;
const version = jsonData.version;
try {
// set User Version PRAGMA
await setVersion(db, version);
// DROP ALL when mode="full"
if (jsonData.mode === 'full') {
await dropAll(db);
import { UtilsSQLite } from './utils-sqlite';
import { UtilsDrop } from './utils-drop';
import { UtilsJSON } from './utils-json';
export class UtilsImportJSON {
static async createDatabaseSchema(db, jsonData) {
let changes = -1;
const version = jsonData.version;
try {
// set User Version PRAGMA
await UtilsSQLite.setVersion(db, version);
// DROP ALL when mode="full"
if (jsonData.mode === 'full') {
await UtilsDrop.dropAll(db);
}
// create database schema
changes = await UtilsImportJSON.createSchema(db, jsonData);
return Promise.resolve(changes);
}
// create database schema
changes = await createSchema(db, jsonData);
return Promise.resolve(changes);
catch (err) {
return Promise.reject(new Error('CreateDatabaseSchema: ' + `${err.message}`));
}
}
catch (err) {
return Promise.reject(new Error('CreateDatabaseSchema: ' + `${err.message}`));
}
};
export const createSchema = async (db, jsonData) => {
// create the database schema
let changes = 0;
try {
// start a transaction
await beginTransaction(db, true);
}
catch (err) {
return Promise.reject(new Error(`CreateSchema: ${err.message}`));
}
const stmts = await createSchemaStatement(jsonData);
if (stmts.length > 0) {
const schemaStmt = stmts.join('\n');
static async createSchema(db, jsonData) {
// create the database schema
let changes = 0;
try {
changes = await execute(db, schemaStmt, true);
if (changes < 0) {
// start a transaction
await UtilsSQLite.beginTransaction(db, true);
}
catch (err) {
return Promise.reject(new Error(`CreateSchema: ${err.message}`));
}
const stmts = await UtilsImportJSON.createSchemaStatement(jsonData);
if (stmts.length > 0) {
const schemaStmt = stmts.join('\n');
try {
changes = await UtilsSQLite.execute(db, schemaStmt, true);
if (changes < 0) {
try {
await UtilsSQLite.rollbackTransaction(db, true);
}
catch (err) {
return Promise.reject(new Error('CreateSchema: changes < 0 ' + `${err.message}`));
}
}
}
catch (err) {
const msg = err.message;
try {
await rollbackTransaction(db, true);
await UtilsSQLite.rollbackTransaction(db, true);
return Promise.reject(new Error(`CreateSchema: ${msg}`));
}
catch (err) {
return Promise.reject(new Error('CreateSchema: changes < 0 ' + `${err.message}`));
return Promise.reject(new Error('CreateSchema: changes < 0 ' + `${err.message}: ${msg}`));
}
}
}
try {
await UtilsSQLite.commitTransaction(db, true);
return Promise.resolve(changes);
}
catch (err) {
const msg = err.message;
try {
await rollbackTransaction(db, true);
return Promise.reject(new Error(`CreateSchema: ${msg}`));
}
catch (err) {
return Promise.reject(new Error('CreateSchema: changes < 0 ' + `${err.message}: ${msg}`));
}
return Promise.reject(new Error('CreateSchema: commit ' + `${err.message}`));
}
}
try {
await commitTransaction(db, true);
return Promise.resolve(changes);
}
catch (err) {
return Promise.reject(new Error('CreateSchema: commit ' + `${err.message}`));
}
};
export const createSchemaStatement = async (jsonData) => {
const statements = [];
let isLastModified = false;
let isSqlDeleted = false;
// Prepare the statement to execute
try {
for (const jTable of jsonData.tables) {
if (jTable.schema != null && jTable.schema.length >= 1) {
// create table
statements.push('CREATE TABLE IF NOT EXISTS ' + `${jTable.name} (`);
for (let j = 0; j < jTable.schema.length; j++) {
if (j === jTable.schema.length - 1) {
if (jTable.schema[j].column) {
statements.push(`${jTable.schema[j].column} ${jTable.schema[j].value}`);
if (jTable.schema[j].column === "last_modified") {
isLastModified = true;
static async createSchemaStatement(jsonData) {
const statements = [];
let isLastModified = false;
let isSqlDeleted = false;
// Prepare the statement to execute
try {
for (const jTable of jsonData.tables) {
if (jTable.schema != null && jTable.schema.length >= 1) {
// create table
statements.push('CREATE TABLE IF NOT EXISTS ' + `${jTable.name} (`);
for (let j = 0; j < jTable.schema.length; j++) {
if (j === jTable.schema.length - 1) {
if (jTable.schema[j].column) {
statements.push(`${jTable.schema[j].column} ${jTable.schema[j].value}`);
if (jTable.schema[j].column === "last_modified") {
isLastModified = true;
}
if (jTable.schema[j].column === "sql_deleted") {
isSqlDeleted = true;
}
}
if (jTable.schema[j].column === "sql_deleted") {
isSqlDeleted = true;
else if (jTable.schema[j].foreignkey) {
statements.push(`FOREIGN KEY (${jTable.schema[j].foreignkey}) ${jTable.schema[j].value}`);
}
else if (jTable.schema[j].constraint) {
statements.push(`CONSTRAINT ${jTable.schema[j].constraint} ${jTable.schema[j].value}`);
}
}
else if (jTable.schema[j].foreignkey) {
statements.push(`FOREIGN KEY (${jTable.schema[j].foreignkey}) ${jTable.schema[j].value}`);
else {
if (jTable.schema[j].column) {
statements.push(`${jTable.schema[j].column} ${jTable.schema[j].value},`);
}
else if (jTable.schema[j].foreignkey) {
statements.push(`FOREIGN KEY (${jTable.schema[j].foreignkey}) ${jTable.schema[j].value},`);
}
else if (jTable.schema[j].primarykey) {
statements.push(`FOREIGN KEY ${jTable.schema[j].value},`);
}
else if (jTable.schema[j].constraint) {
statements.push(`CONSTRAINT ${jTable.schema[j].constraint} ${jTable.schema[j].value},`);
}
}
else if (jTable.schema[j].constraint) {
statements.push(`CONSTRAINT ${jTable.schema[j].constraint} ${jTable.schema[j].value}`);
}
}
else {
if (jTable.schema[j].column) {
statements.push(`${jTable.schema[j].column} ${jTable.schema[j].value},`);
}
else if (jTable.schema[j].foreignkey) {
statements.push(`FOREIGN KEY (${jTable.schema[j].foreignkey}) ${jTable.schema[j].value},`);
}
else if (jTable.schema[j].primarykey) {
statements.push(`FOREIGN KEY ${jTable.schema[j].value},`);
}
else if (jTable.schema[j].constraint) {
statements.push(`CONSTRAINT ${jTable.schema[j].constraint} ${jTable.schema[j].value},`);
}
statements.push(');');
if (isLastModified && isSqlDeleted) {
// create trigger last_modified associated with the table
let trig = 'CREATE TRIGGER IF NOT EXISTS ';
trig += `${jTable.name}`;
trig += `_trigger_last_modified `;
trig += `AFTER UPDATE ON ${jTable.name} `;
trig += 'FOR EACH ROW WHEN NEW.last_modified < ';
trig += 'OLD.last_modified BEGIN UPDATE ';
trig += `${jTable.name} `;
trig += `SET last_modified = `;
trig += "(strftime('%s','now')) WHERE id=OLD.id; END;";
statements.push(trig);
}
}
statements.push(');');
if (isLastModified && isSqlDeleted) {
// create trigger last_modified associated with the table
let trig = 'CREATE TRIGGER IF NOT EXISTS ';
trig += `${jTable.name}`;
trig += `_trigger_last_modified `;
trig += `AFTER UPDATE ON ${jTable.name} `;
trig += 'FOR EACH ROW WHEN NEW.last_modified < ';
trig += 'OLD.last_modified BEGIN UPDATE ';
trig += `${jTable.name} `;
trig += `SET last_modified = `;
trig += "(strftime('%s','now')) WHERE id=OLD.id; END;";
statements.push(trig);
if (jTable.indexes != null && jTable.indexes.length >= 1) {
for (const jIndex of jTable.indexes) {
const tableName = jTable.name;
let stmt = `CREATE ${Object.keys(jIndex).includes('mode') ? jIndex.mode + ' ' : ''} INDEX IF NOT EXISTS `;
stmt += `${jIndex.name} ON ${tableName} (${jIndex.value});`;
statements.push(stmt);
}
}
}
if (jTable.indexes != null && jTable.indexes.length >= 1) {
for (const jIndex of jTable.indexes) {
const tableName = jTable.name;
let stmt = `CREATE ${Object.keys(jIndex).includes('mode') ? jIndex.mode + ' ' : ''} INDEX IF NOT EXISTS `;
stmt += `${jIndex.name} ON ${tableName} (${jIndex.value});`;
statements.push(stmt);
}
}
if (jTable.triggers != null && jTable.triggers.length >= 1) {
for (const jTrg of jTable.triggers) {
const tableName = jTable.name;
if (jTrg.timeevent.toUpperCase().endsWith(" ON")) {
jTrg.timeevent = jTrg.timeevent.substring(0, jTrg.timeevent.length - 3);
if (jTable.triggers != null && jTable.triggers.length >= 1) {
for (const jTrg of jTable.triggers) {
const tableName = jTable.name;
if (jTrg.timeevent.toUpperCase().endsWith(" ON")) {
jTrg.timeevent = jTrg.timeevent.substring(0, jTrg.timeevent.length - 3);
}
let stmt = `CREATE TRIGGER IF NOT EXISTS `;
stmt += `${jTrg.name} ${jTrg.timeevent} ON ${tableName} `;
if (jTrg.condition)
stmt += `${jTrg.condition} `;
stmt += `${jTrg.logic};`;
statements.push(stmt);
}
let stmt = `CREATE TRIGGER IF NOT EXISTS `;
stmt += `${jTrg.name} ${jTrg.timeevent} ON ${tableName} `;
if (jTrg.condition)
stmt += `${jTrg.condition} `;
stmt += `${jTrg.logic};`;
statements.push(stmt);
}
}
return Promise.resolve(statements);
}
return Promise.resolve(statements);
}
catch (err) {
return Promise.reject(err);
}
};
export const createTablesData = async (db, jsonData, importProgress) => {
let changes = 0;
let isValue = false;
let lastId = -1;
let msg = '';
let initChanges = -1;
try {
initChanges = await dbChanges(db);
// start a transaction
await beginTransaction(db, true);
}
catch (err) {
return Promise.reject(new Error(`createTablesData: ${err.message}`));
}
for (const jTable of jsonData.tables) {
if (jTable.values != null && jTable.values.length >= 1) {
// Create the table's data
try {
lastId = await createTableData(db, jTable, jsonData.mode);
const msg = `create table data ${jTable.name}`;
importProgress.emit({ progress: msg });
if (lastId < 0)
break;
isValue = true;
}
catch (err) {
msg = err.message;
isValue = false;
break;
}
catch (err) {
return Promise.reject(err);
}
}
if (isValue) {
static async createTablesData(db, jsonData, importProgress) {
let changes = 0;
let isValue = false;
let lastId = -1;
let msg = '';
let initChanges = -1;
try {
await commitTransaction(db, true);
changes = (await dbChanges(db)) - initChanges;
return Promise.resolve(changes);
initChanges = await UtilsSQLite.dbChanges(db);
// start a transaction
await UtilsSQLite.beginTransaction(db, true);
}
catch (err) {
return Promise.reject(new Error('CreateTablesData: ' + `${err.message}`));
return Promise.reject(new Error(`createTablesData: ${err.message}`));
}
}
else {
if (msg.length > 0) {
for (const jTable of jsonData.tables) {
if (jTable.values != null && jTable.values.length >= 1) {
// Create the table's data
try {
lastId = await UtilsImportJSON.createTableData(db, jTable, jsonData.mode);
const msg = `create table data ${jTable.name}`;
importProgress.emit({ progress: msg });
if (lastId < 0)
break;
isValue = true;
}
catch (err) {
msg = err.message;
isValue = false;
break;
}
}
}
if (isValue) {
try {
await rollbackTransaction(db, true);
return Promise.reject(new Error(`CreateTablesData: ${msg}`));
await UtilsSQLite.commitTransaction(db, true);
changes = (await UtilsSQLite.dbChanges(db)) - initChanges;
return Promise.resolve(changes);
}
catch (err) {
return Promise.reject(new Error('CreateTablesData: ' + `${err.message}: ${msg}`));
return Promise.reject(new Error('CreateTablesData: ' + `${err.message}`));
}
}
else {
// case were no values given
return Promise.resolve(0);
}
}
};
export const createTableData = async (db, table, mode) => {
let lastId = -1;
try {
// Check if the table exists
const tableExists = await isTableExists(db, table.name);
if (!tableExists) {
return Promise.reject(new Error('CreateTableData: Table ' + `${table.name} does not exist`));
}
// Get the column names and types
const tableNamesTypes = await getTableColumnNamesTypes(db, table.name);
const tableColumnTypes = tableNamesTypes.types;
const tableColumnNames = tableNamesTypes.names;
if (tableColumnTypes.length === 0) {
return Promise.reject(new Error('CreateTableData: Table ' + `${table.name} info does not exist`));
}
// Loop on Table Values
for (let j = 0; j < table.values.length; j++) {
let row = table.values[j];
let isRun = true;
const stmt = await createRowStatement(db, tableColumnNames, row, j, table.name, mode);
isRun = await checkUpdate(db, stmt, row, table.name, tableColumnNames);
if (isRun) {
if (stmt.substring(0, 6).toUpperCase() === "DELETE") {
row = [];
if (msg.length > 0) {
try {
await UtilsSQLite.rollbackTransaction(db, true);
return Promise.reject(new Error(`CreateTablesData: ${msg}`));
}
lastId = await run(db, stmt, row, true, 'no');
if (lastId < 0) {
return Promise.reject(new Error('CreateTableData: lastId < 0'));
catch (err) {
return Promise.reject(new Error('CreateTablesData: ' + `${err.message}: ${msg}`));
}
}
else {
lastId = 0;
// case were no values given
return Promise.resolve(0);
}
}
return Promise.resolve(lastId);
}
catch (err) {
return Promise.reject(new Error(`CreateTableData: ${err.message}`));
static async createTableData(db, table, mode) {
let lastId = -1;
try {
// Check if the table exists
const tableExists = await UtilsSQLite.isTableExists(db, table.name);
if (!tableExists) {
return Promise.reject(new Error('CreateTableData: Table ' + `${table.name} does not exist`));
}
// Get the column names and types
const tableNamesTypes = await UtilsJSON
.getTableColumnNamesTypes(db, table.name);
const tableColumnTypes = tableNamesTypes.types;
const tableColumnNames = tableNamesTypes.names;
if (tableColumnTypes.length === 0) {
return Promise.reject(new Error('CreateTableData: Table ' + `${table.name} info does not exist`));
}
// Loop on Table Values
for (let j = 0; j < table.values.length; j++) {
let row = table.values[j];
let isRun = true;
const stmt = await UtilsImportJSON
.createRowStatement(db, tableColumnNames, row, j, table.name, mode);
isRun = await UtilsImportJSON.checkUpdate(db, stmt, row, table.name, tableColumnNames);
if (isRun) {
if (stmt.substring(0, 6).toUpperCase() === "DELETE") {
row = [];
}
lastId = await UtilsSQLite.run(db, stmt, row, true, 'no');
if (lastId < 0) {
return Promise.reject(new Error('CreateTableData: lastId < 0'));
}
}
else {
lastId = 0;
}
}
return Promise.resolve(lastId);
}
catch (err) {
return Promise.reject(new Error(`CreateTableData: ${err.message}`));
}
}
};
export const createRowStatement = async (db, tColNames, row, j, tableName, mode) => {
// Check the row number of columns
if (row.length != tColNames.length || row.length === 0 || tColNames.length === 0) {
return Promise.reject(new Error(`CreateRowStatement: Table ${tableName} ` +
`values row ${j} not correct length`));
}
try {
const retisIdExists = await isIdExists(db, tableName, tColNames[0], row[0]);
let stmt;
if (mode === 'full' || (mode === 'partial' && !retisIdExists)) {
// Insert
const nameString = tColNames.join();
const questionMarkString = await createQuestionMarkString(tColNames.length);
stmt = `INSERT INTO ${tableName} (${nameString}) VALUES (`;
stmt += `${questionMarkString});`;
static async createRowStatement(db, tColNames, row, j, tableName, mode) {
// Check the row number of columns
if (row.length != tColNames.length || row.length === 0 || tColNames.length === 0) {
return Promise.reject(new Error(`CreateRowStatement: Table ${tableName} ` +
`values row ${j} not correct length`));
}
else {
// Update or Delete
let isUpdate = true;
const isColDeleted = (element) => element === `sql_deleted`;
const idxDelete = tColNames.findIndex(isColDeleted);
if (idxDelete >= 0) {
if (row[idxDelete] === 1) {
isUpdate = false;
try {
const retisIdExists = await UtilsImportJSON.isIdExists(db, tableName, tColNames[0], row[0]);
let stmt;
if (mode === 'full' || (mode === 'partial' && !retisIdExists)) {
// Insert
const nameString = tColNames.join();
const questionMarkString = await UtilsImportJSON.createQuestionMarkString(tColNames.length);
stmt = `INSERT INTO ${tableName} (${nameString}) VALUES (`;
stmt += `${questionMarkString});`;
}
else {
// Update or Delete
let isUpdate = true;
const isColDeleted = (element) => element === `sql_deleted`;
const idxDelete = tColNames.findIndex(isColDeleted);
if (idxDelete >= 0) {
if (row[idxDelete] === 1) {
isUpdate = false;
stmt =
`DELETE FROM ${tableName} WHERE `;
if (typeof row[0] == "string") {
stmt +=
`${tColNames[0]} = '${row[0]}';`;
}
else {
stmt +=
`${tColNames[0]} = ${row[0]};`;
}
}
}
if (isUpdate) {
// Update
const setString = await UtilsImportJSON.setNameForUpdate(tColNames);
if (setString.length === 0) {
return Promise.reject(new Error(`CreateRowStatement: Table ${tableName} ` +
`values row ${j} not set to String`));
}
stmt =
`DELETE FROM ${tableName} WHERE `;
`UPDATE ${tableName} SET ${setString} WHERE `;
if (typeof row[0] == "string") {

@@ -286,209 +308,191 @@ stmt +=

}
if (isUpdate) {
// Update
const setString = await setNameForUpdate(tColNames);
if (setString.length === 0) {
return Promise.reject(new Error(`CreateRowStatement: Table ${tableName} ` +
`values row ${j} not set to String`));
return Promise.resolve(stmt);
}
catch (err) {
return Promise.reject(new Error(`CreateRowStatement: ${err.message}`));
}
}
static async checkUpdate(db, stmt, values, tbName, tColNames) {
let isRun = true;
if (stmt.substring(0, 6) === "UPDATE") {
try {
let query = `SELECT * FROM ${tbName} WHERE `;
if (typeof values[0] == "string") {
query +=
`${tColNames[0]} = '${values[0]}';`;
}
stmt =
`UPDATE ${tableName} SET ${setString} WHERE `;
if (typeof row[0] == "string") {
stmt +=
`${tColNames[0]} = '${row[0]}';`;
else {
query +=
`${tColNames[0]} = ${values[0]};`;
}
const resQuery = await UtilsJSON.getValues(db, query, tbName);
let resValues = [];
if (resQuery.length > 0) {
resValues = resQuery[0];
}
if (values.length > 0 && resValues.length > 0
&& values.length === resValues.length) {
for (let i = 0; i < values.length; i++) {
if (values[i] !== resValues[i]) {
return Promise.resolve(true);
}
}
return Promise.resolve(false);
}
else {
stmt +=
`${tColNames[0]} = ${row[0]};`;
const msg = "Both arrays not the same length";
return Promise.reject(new Error(`CheckUpdate: ${msg}`));
}
}
catch (err) {
return Promise.reject(new Error(`CheckUpdate: ${err.message}`));
}
}
return Promise.resolve(stmt);
else {
return Promise.resolve(isRun);
}
}
catch (err) {
return Promise.reject(new Error(`CreateRowStatement: ${err.message}`));
}
};
export const checkUpdate = async (db, stmt, values, tbName, tColNames) => {
let isRun = true;
if (stmt.substring(0, 6) === "UPDATE") {
static async isIdExists(db, dbName, firstColumnName, key) {
let ret = false;
let query = `SELECT ${firstColumnName} FROM ` +
`${dbName} WHERE ${firstColumnName} = `;
if (typeof key === 'number')
query += `${key};`;
if (typeof key === 'string')
query += `'${key}';`;
try {
let query = `SELECT * FROM ${tbName} WHERE `;
if (typeof values[0] == "string") {
query +=
`${tColNames[0]} = '${values[0]}';`;
}
else {
query +=
`${tColNames[0]} = ${values[0]};`;
}
const resQuery = await getValues(db, query, tbName);
let resValues = [];
if (resQuery.length > 0) {
resValues = resQuery[0];
}
if (values.length > 0 && resValues.length > 0
&& values.length === resValues.length) {
for (let i = 0; i < values.length; i++) {
if (values[i] !== resValues[i]) {
return Promise.resolve(true);
}
}
return Promise.resolve(false);
}
else {
const msg = "Both arrays not the same length";
return Promise.reject(new Error(`CheckUpdate: ${msg}`));
}
const resQuery = await UtilsSQLite.queryAll(db, query, []);
if (resQuery.length === 1)
ret = true;
return Promise.resolve(ret);
}
catch (err) {
return Promise.reject(new Error(`CheckUpdate: ${err.message}`));
return Promise.reject(new Error(`IsIdExists: ${err.message}`));
}
}
else {
return Promise.resolve(isRun);
}
};
export const isIdExists = async (db, dbName, firstColumnName, key) => {
let ret = false;
let query = `SELECT ${firstColumnName} FROM ` +
`${dbName} WHERE ${firstColumnName} = `;
if (typeof key === 'number')
query += `${key};`;
if (typeof key === 'string')
query += `'${key}';`;
try {
const resQuery = await queryAll(db, query, []);
if (resQuery.length === 1)
static async isType(type, value) {
let ret = false;
if (type === 'NULL' && typeof value === 'object')
ret = true;
return Promise.resolve(ret);
if (type === 'TEXT' && typeof value === 'string')
ret = true;
if (type === 'INTEGER' && typeof value === 'number')
ret = true;
if (type === 'REAL' && typeof value === 'number')
ret = true;
if (type === 'BLOB' && typeof value === 'string')
ret = true;
if (ret) {
return Promise.resolve();
}
else {
return Promise.reject(new Error('IsType: not a SQL Type'));
}
}
catch (err) {
return Promise.reject(new Error(`IsIdExists: ${err.message}`));
}
};
export const isType = async (type, value) => {
let ret = false;
if (type === 'NULL' && typeof value === 'object')
ret = true;
if (type === 'TEXT' && typeof value === 'string')
ret = true;
if (type === 'INTEGER' && typeof value === 'number')
ret = true;
if (type === 'REAL' && typeof value === 'number')
ret = true;
if (type === 'BLOB' && typeof value === 'string')
ret = true;
if (ret) {
static async checkColumnTypes(tableTypes, rowValues) {
for (let i = 0; i < rowValues.length; i++) {
if (rowValues[i] != null) {
try {
await UtilsImportJSON.isType(tableTypes[i], rowValues[i]);
}
catch (err) {
return Promise.reject(new Error('CheckColumnTypes: Type not found'));
}
}
}
return Promise.resolve();
}
else {
return Promise.reject(new Error('IsType: not a SQL Type'));
}
};
export const checkColumnTypes = async (tableTypes, rowValues) => {
for (let i = 0; i < rowValues.length; i++) {
if (rowValues[i] != null) {
try {
await isType(tableTypes[i], rowValues[i]);
}
catch (err) {
return Promise.reject(new Error('CheckColumnTypes: Type not found'));
}
static async createQuestionMarkString(length) {
let retString = '';
for (let i = 0; i < length; i++) {
retString += '?,';
}
if (retString.length > 1) {
retString = retString.slice(0, -1);
return Promise.resolve(retString);
}
else {
return Promise.reject(new Error('CreateQuestionMarkString: length = 0'));
}
}
return Promise.resolve();
};
export const createQuestionMarkString = async (length) => {
let retString = '';
for (let i = 0; i < length; i++) {
retString += '?,';
}
if (retString.length > 1) {
retString = retString.slice(0, -1);
return Promise.resolve(retString);
}
else {
return Promise.reject(new Error('CreateQuestionMarkString: length = 0'));
}
};
export const setNameForUpdate = async (names) => {
let retString = '';
for (const name of names) {
retString += `${name} = ? ,`;
}
if (retString.length > 1) {
retString = retString.slice(0, -1);
return Promise.resolve(retString);
}
else {
return Promise.reject(new Error('SetNameForUpdate: length = 0'));
}
};
export const createView = async (mDB, view) => {
const stmt = `CREATE VIEW IF NOT EXISTS ${view.name} AS ${view.value};`;
try {
const changes = await execute(mDB, stmt, true);
if (changes < 0) {
return Promise.reject(new Error(`CreateView: ${view.name} failed`));
static async setNameForUpdate(names) {
let retString = '';
for (const name of names) {
retString += `${name} = ? ,`;
}
return Promise.resolve();
if (retString.length > 1) {
retString = retString.slice(0, -1);
return Promise.resolve(retString);
}
else {
return Promise.reject(new Error('SetNameForUpdate: length = 0'));
}
}
catch (err) {
return Promise.reject(new Error(`CreateView: ${err.message}`));
}
};
export const createViews = async (mDB, jsonData) => {
let isView = false;
let msg = '';
let initChanges = -1;
let changes = -1;
try {
initChanges = await dbChanges(mDB);
// start a transaction
await beginTransaction(mDB, true);
}
catch (err) {
return Promise.reject(new Error(`createViews: ${err.message}`));
}
for (const jView of jsonData.views) {
if (jView.value != null) {
// Create the view
try {
await createView(mDB, jView);
isView = true;
static async createView(mDB, view) {
const stmt = `CREATE VIEW IF NOT EXISTS ${view.name} AS ${view.value};`;
try {
const changes = await UtilsSQLite.execute(mDB, stmt, true);
if (changes < 0) {
return Promise.reject(new Error(`CreateView: ${view.name} failed`));
}
catch (err) {
msg = err.message;
isView = false;
break;
}
return Promise.resolve();
}
catch (err) {
return Promise.reject(new Error(`CreateView: ${err.message}`));
}
}
if (isView) {
static async createViews(mDB, jsonData) {
let isView = false;
let msg = '';
let initChanges = -1;
let changes = -1;
try {
await commitTransaction(mDB, true);
changes = (await dbChanges(mDB)) - initChanges;
return Promise.resolve(changes);
initChanges = await UtilsSQLite.dbChanges(mDB);
// start a transaction
await UtilsSQLite.beginTransaction(mDB, true);
}
catch (err) {
return Promise.reject(new Error('createViews: ' + `${err.message}`));
return Promise.reject(new Error(`createViews: ${err.message}`));
}
}
else {
if (msg.length > 0) {
for (const jView of jsonData.views) {
if (jView.value != null) {
// Create the view
try {
await UtilsImportJSON.createView(mDB, jView);
isView = true;
}
catch (err) {
msg = err.message;
isView = false;
break;
}
}
}
if (isView) {
try {
await rollbackTransaction(mDB, true);
return Promise.reject(new Error(`createViews: ${msg}`));
await UtilsSQLite.commitTransaction(mDB, true);
changes = (await UtilsSQLite.dbChanges(mDB)) - initChanges;
return Promise.resolve(changes);
}
catch (err) {
return Promise.reject(new Error('createViews: ' + `${err.message}: ${msg}`));
return Promise.reject(new Error('createViews: ' + `${err.message}`));
}
}
else {
// case were no views given
return Promise.resolve(0);
if (msg.length > 0) {
try {
await UtilsSQLite.rollbackTransaction(mDB, true);
return Promise.reject(new Error(`createViews: ${msg}`));
}
catch (err) {
return Promise.reject(new Error('createViews: ' + `${err.message}: ${msg}`));
}
}
else {
// case were no views given
return Promise.resolve(0);
}
}
}
};
}
//# sourceMappingURL=utils-importJson.js.map

578

dist/collection/utils/utils-json.js

@@ -1,328 +0,330 @@

import { queryAll } from './utils-sqlite';
export const isJsonSQLite = async (obj) => {
const keyFirstLevel = [
'database',
'version',
'overwrite',
'encrypted',
'mode',
'tables',
'views'
];
if (obj == null ||
(Object.keys(obj).length === 0 && obj.constructor === Object))
return false;
for (const key of Object.keys(obj)) {
if (keyFirstLevel.indexOf(key) === -1)
import { UtilsSQLite } from './utils-sqlite';
export class UtilsJSON {
static async isJsonSQLite(obj) {
const keyFirstLevel = [
'database',
'version',
'overwrite',
'encrypted',
'mode',
'tables',
'views'
];
if (obj == null ||
(Object.keys(obj).length === 0 && obj.constructor === Object))
return false;
if (key === 'database' && typeof obj[key] != 'string')
return false;
if (key === 'version' && typeof obj[key] != 'number')
return false;
if (key === 'overwrite' && typeof obj[key] != 'boolean')
return false;
if (key === 'encrypted' && typeof obj[key] != 'boolean')
return false;
if (key === 'mode' && typeof obj[key] != 'string')
return false;
if (key === 'tables' && typeof obj[key] != 'object')
return false;
if (key === 'tables') {
for (const oKey of obj[key]) {
const retTable = await isTable(oKey);
if (!retTable)
return false;
for (const key of Object.keys(obj)) {
if (keyFirstLevel.indexOf(key) === -1)
return false;
if (key === 'database' && typeof obj[key] != 'string')
return false;
if (key === 'version' && typeof obj[key] != 'number')
return false;
if (key === 'overwrite' && typeof obj[key] != 'boolean')
return false;
if (key === 'encrypted' && typeof obj[key] != 'boolean')
return false;
if (key === 'mode' && typeof obj[key] != 'string')
return false;
if (key === 'tables' && typeof obj[key] != 'object')
return false;
if (key === 'tables') {
for (const oKey of obj[key]) {
const retTable = await UtilsJSON.isTable(oKey);
if (!retTable)
return false;
}
}
}
if (key === 'views' && typeof obj[key] != 'object')
return false;
if (key === 'views') {
for (const oKey of obj[key]) {
const retView = await isView(oKey);
if (!retView)
return false;
if (key === 'views' && typeof obj[key] != 'object')
return false;
if (key === 'views') {
for (const oKey of obj[key]) {
const retView = await UtilsJSON.isView(oKey);
if (!retView)
return false;
}
}
}
return true;
}
return true;
};
export const isTable = async (obj) => {
const keyTableLevel = [
'name',
'schema',
'indexes',
'triggers',
'values',
];
let nbColumn = 0;
if (obj == null ||
(Object.keys(obj).length === 0 && obj.constructor === Object))
return false;
for (const key of Object.keys(obj)) {
if (keyTableLevel.indexOf(key) === -1)
static async isTable(obj) {
const keyTableLevel = [
'name',
'schema',
'indexes',
'triggers',
'values',
];
let nbColumn = 0;
if (obj == null ||
(Object.keys(obj).length === 0 && obj.constructor === Object))
return false;
if (key === 'name' && typeof obj[key] != 'string')
return false;
if (key === 'schema' && typeof obj[key] != 'object')
return false;
if (key === 'indexes' && typeof obj[key] != 'object')
return false;
if (key === 'triggers' && typeof obj[key] != 'object')
return false;
if (key === 'values' && typeof obj[key] != 'object')
return false;
if (key === 'schema') {
obj['schema'].forEach((element) => {
if (element.column) {
nbColumn++;
for (const key of Object.keys(obj)) {
if (keyTableLevel.indexOf(key) === -1)
return false;
if (key === 'name' && typeof obj[key] != 'string')
return false;
if (key === 'schema' && typeof obj[key] != 'object')
return false;
if (key === 'indexes' && typeof obj[key] != 'object')
return false;
if (key === 'triggers' && typeof obj[key] != 'object')
return false;
if (key === 'values' && typeof obj[key] != 'object')
return false;
if (key === 'schema') {
obj['schema'].forEach((element) => {
if (element.column) {
nbColumn++;
}
});
for (let i = 0; i < nbColumn; i++) {
const retSchema = await UtilsJSON.isSchema(obj[key][i]);
if (!retSchema)
return false;
}
});
for (let i = 0; i < nbColumn; i++) {
const retSchema = await isSchema(obj[key][i]);
if (!retSchema)
return false;
}
}
if (key === 'indexes') {
for (const oKey of obj[key]) {
const retIndexes = await isIndexes(oKey);
if (!retIndexes)
return false;
if (key === 'indexes') {
for (const oKey of obj[key]) {
const retIndexes = await UtilsJSON.isIndexes(oKey);
if (!retIndexes)
return false;
}
}
}
if (key === 'triggers') {
for (const oKey of obj[key]) {
const retTriggers = await isTriggers(oKey);
if (!retTriggers)
return false;
}
}
if (key === 'values') {
if (nbColumn > 0) {
if (key === 'triggers') {
for (const oKey of obj[key]) {
if (typeof oKey != 'object' || oKey.length != nbColumn)
const retTriggers = await UtilsJSON.isTriggers(oKey);
if (!retTriggers)
return false;
}
}
if (key === 'values') {
if (nbColumn > 0) {
for (const oKey of obj[key]) {
if (typeof oKey != 'object' || oKey.length != nbColumn)
return false;
}
}
}
}
return true;
}
return true;
};
export const isSchema = async (obj) => {
const keySchemaLevel = [
'column',
'value',
'foreignkey',
'primarykey',
'constraint',
];
if (obj == null ||
(Object.keys(obj).length === 0 && obj.constructor === Object))
return false;
for (const key of Object.keys(obj)) {
if (keySchemaLevel.indexOf(key) === -1)
static async isSchema(obj) {
const keySchemaLevel = [
'column',
'value',
'foreignkey',
'primarykey',
'constraint',
];
if (obj == null ||
(Object.keys(obj).length === 0 && obj.constructor === Object))
return false;
if (key === 'column' && typeof obj[key] != 'string')
return false;
if (key === 'value' && typeof obj[key] != 'string')
return false;
if (key === 'foreignkey' && typeof obj[key] != 'string')
return false;
if (key === 'primarykey' && typeof obj[key] != 'string')
return false;
if (key === 'constraint' && typeof obj[key] != 'string')
return false;
for (const key of Object.keys(obj)) {
if (keySchemaLevel.indexOf(key) === -1)
return false;
if (key === 'column' && typeof obj[key] != 'string')
return false;
if (key === 'value' && typeof obj[key] != 'string')
return false;
if (key === 'foreignkey' && typeof obj[key] != 'string')
return false;
if (key === 'primarykey' && typeof obj[key] != 'string')
return false;
if (key === 'constraint' && typeof obj[key] != 'string')
return false;
}
return true;
}
return true;
};
export const isIndexes = async (obj) => {
const keyIndexesLevel = ['name', 'value', 'mode'];
if (obj == null ||
(Object.keys(obj).length === 0 && obj.constructor === Object))
return false;
for (const key of Object.keys(obj)) {
if (keyIndexesLevel.indexOf(key) === -1)
static async isIndexes(obj) {
const keyIndexesLevel = ['name', 'value', 'mode'];
if (obj == null ||
(Object.keys(obj).length === 0 && obj.constructor === Object))
return false;
if (key === 'name' && typeof obj[key] != 'string')
return false;
if (key === 'value' && typeof obj[key] != 'string')
return false;
if (key === 'mode' &&
(typeof obj[key] != 'string' || obj[key].toUpperCase() != 'UNIQUE'))
return false;
for (const key of Object.keys(obj)) {
if (keyIndexesLevel.indexOf(key) === -1)
return false;
if (key === 'name' && typeof obj[key] != 'string')
return false;
if (key === 'value' && typeof obj[key] != 'string')
return false;
if (key === 'mode' &&
(typeof obj[key] != 'string' || obj[key].toUpperCase() != 'UNIQUE'))
return false;
}
return true;
}
return true;
};
export const isTriggers = async (obj) => {
const keyTriggersLevel = [
'name',
'timeevent',
'condition',
'logic',
];
if (obj == null ||
(Object.keys(obj).length === 0 && obj.constructor === Object))
return false;
for (const key of Object.keys(obj)) {
if (keyTriggersLevel.indexOf(key) === -1)
static async isTriggers(obj) {
const keyTriggersLevel = [
'name',
'timeevent',
'condition',
'logic',
];
if (obj == null ||
(Object.keys(obj).length === 0 && obj.constructor === Object))
return false;
if (key === 'name' && typeof obj[key] != 'string')
return false;
if (key === 'timeevent' && typeof obj[key] != 'string')
return false;
if (key === 'condition' && typeof obj[key] != 'string')
return false;
if (key === 'logic' && typeof obj[key] != 'string')
return false;
for (const key of Object.keys(obj)) {
if (keyTriggersLevel.indexOf(key) === -1)
return false;
if (key === 'name' && typeof obj[key] != 'string')
return false;
if (key === 'timeevent' && typeof obj[key] != 'string')
return false;
if (key === 'condition' && typeof obj[key] != 'string')
return false;
if (key === 'logic' && typeof obj[key] != 'string')
return false;
}
return true;
}
return true;
};
export const isView = async (obj) => {
const keyViewLevel = ['name', 'value'];
if (obj == null ||
(Object.keys(obj).length === 0 && obj.constructor === Object))
return false;
for (const key of Object.keys(obj)) {
if (keyViewLevel.indexOf(key) === -1)
static async isView(obj) {
const keyViewLevel = ['name', 'value'];
if (obj == null ||
(Object.keys(obj).length === 0 && obj.constructor === Object))
return false;
if (key === 'name' && typeof obj[key] != 'string')
return false;
if (key === 'value' && typeof obj[key] != 'string')
return false;
}
return true;
};
export const checkSchemaValidity = async (schema) => {
for (let i = 0; i < schema.length; i++) {
const sch = {};
const keys = Object.keys(schema[i]);
if (keys.includes('column')) {
sch.column = schema[i].column;
for (const key of Object.keys(obj)) {
if (keyViewLevel.indexOf(key) === -1)
return false;
if (key === 'name' && typeof obj[key] != 'string')
return false;
if (key === 'value' && typeof obj[key] != 'string')
return false;
}
if (keys.includes('value')) {
sch.value = schema[i].value;
}
if (keys.includes('foreignkey')) {
sch.foreignkey = schema[i].foreignkey;
}
if (keys.includes('constraint')) {
sch.constraint = schema[i].constraint;
}
const isValid = await isSchema(sch);
if (!isValid) {
return Promise.reject(new Error(`CheckSchemaValidity: schema[${i}] not valid`));
}
return true;
}
return Promise.resolve();
};
export const checkIndexesValidity = async (indexes) => {
for (let i = 0; i < indexes.length; i++) {
const index = {};
const keys = Object.keys(indexes[i]);
if (keys.includes('value')) {
index.value = indexes[i].value;
static async checkSchemaValidity(schema) {
for (let i = 0; i < schema.length; i++) {
const sch = {};
const keys = Object.keys(schema[i]);
if (keys.includes('column')) {
sch.column = schema[i].column;
}
if (keys.includes('value')) {
sch.value = schema[i].value;
}
if (keys.includes('foreignkey')) {
sch.foreignkey = schema[i].foreignkey;
}
if (keys.includes('constraint')) {
sch.constraint = schema[i].constraint;
}
const isValid = await UtilsJSON.isSchema(sch);
if (!isValid) {
return Promise.reject(new Error(`CheckSchemaValidity: schema[${i}] not valid`));
}
}
if (keys.includes('name')) {
index.name = indexes[i].name;
}
if (keys.includes('mode')) {
index.mode = indexes[i].mode;
}
const isValid = await isIndexes(index);
if (!isValid) {
return Promise.reject(new Error(`CheckIndexesValidity: indexes[${i}] not valid`));
}
return Promise.resolve();
}
return Promise.resolve();
};
export const checkTriggersValidity = async (triggers) => {
for (let i = 0; i < triggers.length; i++) {
const trigger = {};
const keys = Object.keys(triggers[i]);
if (keys.includes('logic')) {
trigger.logic = triggers[i].logic;
static async checkIndexesValidity(indexes) {
for (let i = 0; i < indexes.length; i++) {
const index = {};
const keys = Object.keys(indexes[i]);
if (keys.includes('value')) {
index.value = indexes[i].value;
}
if (keys.includes('name')) {
index.name = indexes[i].name;
}
if (keys.includes('mode')) {
index.mode = indexes[i].mode;
}
const isValid = await UtilsJSON.isIndexes(index);
if (!isValid) {
return Promise.reject(new Error(`CheckIndexesValidity: indexes[${i}] not valid`));
}
}
if (keys.includes('name')) {
trigger.name = triggers[i].name;
}
if (keys.includes('timeevent')) {
trigger.timeevent = triggers[i].timeevent;
}
if (keys.includes('condition')) {
trigger.condition = triggers[i].condition;
}
const isValid = await isTriggers(trigger);
if (!isValid) {
return Promise.reject(new Error(`CheckTriggersValidity: triggers[${i}] not valid`));
}
return Promise.resolve();
}
return Promise.resolve();
};
export const checkViewsValidity = async (views) => {
for (let i = 0; i < views.length; i++) {
const view = {};
const keys = Object.keys(views[i]);
if (keys.includes('value')) {
view.value = views[i].value;
static async checkTriggersValidity(triggers) {
for (let i = 0; i < triggers.length; i++) {
const trigger = {};
const keys = Object.keys(triggers[i]);
if (keys.includes('logic')) {
trigger.logic = triggers[i].logic;
}
if (keys.includes('name')) {
trigger.name = triggers[i].name;
}
if (keys.includes('timeevent')) {
trigger.timeevent = triggers[i].timeevent;
}
if (keys.includes('condition')) {
trigger.condition = triggers[i].condition;
}
const isValid = await UtilsJSON.isTriggers(trigger);
if (!isValid) {
return Promise.reject(new Error(`CheckTriggersValidity: triggers[${i}] not valid`));
}
}
if (keys.includes('name')) {
view.name = views[i].name;
}
const isValid = await isView(view);
if (!isValid) {
return Promise.reject(new Error(`CheckViewsValidity: views[${i}] not valid`));
}
return Promise.resolve();
}
return Promise.resolve();
};
export const getTableColumnNamesTypes = async (db, tableName) => {
let resQuery = [];
const retNames = [];
const retTypes = [];
const query = `PRAGMA table_info('${tableName}');`;
try {
resQuery = await queryAll(db, query, []);
if (resQuery.length > 0) {
for (const query of resQuery) {
retNames.push(query.name);
retTypes.push(query.type);
static async checkViewsValidity(views) {
for (let i = 0; i < views.length; i++) {
const view = {};
const keys = Object.keys(views[i]);
if (keys.includes('value')) {
view.value = views[i].value;
}
if (keys.includes('name')) {
view.name = views[i].name;
}
const isValid = await UtilsJSON.isView(view);
if (!isValid) {
return Promise.reject(new Error(`CheckViewsValidity: views[${i}] not valid`));
}
}
return Promise.resolve({ names: retNames, types: retTypes });
return Promise.resolve();
}
catch (err) {
return Promise.reject(new Error('GetTableColumnNamesTypes: ' + `${err.message}`));
}
};
export const getValues = async (db, query, tableName) => {
const values = [];
try {
// get table column names and types
const tableNamesTypes = await getTableColumnNamesTypes(db, tableName);
let rowNames = [];
if (Object.keys(tableNamesTypes).includes('names')) {
rowNames = tableNamesTypes.names;
static async getTableColumnNamesTypes(db, tableName) {
let resQuery = [];
const retNames = [];
const retTypes = [];
const query = `PRAGMA table_info('${tableName}');`;
try {
resQuery = await UtilsSQLite.queryAll(db, query, []);
if (resQuery.length > 0) {
for (const query of resQuery) {
retNames.push(query.name);
retTypes.push(query.type);
}
}
return Promise.resolve({ names: retNames, types: retTypes });
}
else {
return Promise.reject(new Error(`GetValues: Table ${tableName} no names`));
catch (err) {
return Promise.reject(new Error('GetTableColumnNamesTypes: ' + `${err.message}`));
}
const retValues = await queryAll(db, query, []);
for (const rValue of retValues) {
const row = [];
for (const rName of rowNames) {
if (Object.keys(rValue).includes(rName)) {
row.push(rValue[rName]);
}
static async getValues(db, query, tableName) {
const values = [];
try {
// get table column names and types
const tableNamesTypes = await UtilsJSON.getTableColumnNamesTypes(db, tableName);
let rowNames = [];
if (Object.keys(tableNamesTypes).includes('names')) {
rowNames = tableNamesTypes.names;
}
else {
return Promise.reject(new Error(`GetValues: Table ${tableName} no names`));
}
const retValues = await UtilsSQLite.queryAll(db, query, []);
for (const rValue of retValues) {
const row = [];
for (const rName of rowNames) {
if (Object.keys(rValue).includes(rName)) {
row.push(rValue[rName]);
}
else {
row.push(null);
}
}
else {
row.push(null);
}
values.push(row);
}
values.push(row);
return Promise.resolve(values);
}
return Promise.resolve(values);
catch (err) {
return Promise.reject(new Error(`GetValues: ${err.message}`));
}
}
catch (err) {
return Promise.reject(new Error(`GetValues: ${err.message}`));
}
};
}
//# sourceMappingURL=utils-json.js.map

1055

dist/collection/utils/utils-sqlite.js

@@ -1,154 +0,163 @@

import { getTablesNames } from '../utils/utils-drop';
import { getTableColumnNamesTypes } from '../utils/utils-json';
export const beginTransaction = async (db, isOpen) => {
const msg = 'BeginTransaction: ';
if (!isOpen) {
return Promise.reject(new Error(`${msg}database not opened`));
import { UtilsDrop } from '../utils/utils-drop';
import { UtilsJSON } from '../utils/utils-json';
import { UtilsDelete } from './utils-delete';
import { UtilsSQLStatement } from './utils-sqlstatement';
export class UtilsSQLite {
static async beginTransaction(db, isOpen) {
const msg = 'BeginTransaction: ';
if (!isOpen) {
return Promise.reject(new Error(`${msg}database not opened`));
}
const sql = 'BEGIN TRANSACTION;';
try {
db.exec(sql);
return Promise.resolve();
}
catch (err) {
return Promise.reject(new Error(`${msg}${err.message}`));
}
}
const sql = 'BEGIN TRANSACTION;';
try {
db.exec(sql);
return Promise.resolve();
static async rollbackTransaction(db, isOpen) {
const msg = 'RollbackTransaction: ';
if (!isOpen) {
return Promise.reject(new Error(`${msg}database not opened`));
}
const sql = 'ROLLBACK TRANSACTION;';
try {
db.exec(sql);
return Promise.resolve();
}
catch (err) {
return Promise.reject(new Error(`${msg}${err.message}`));
}
}
catch (err) {
return Promise.reject(new Error(`${msg}${err.message}`));
static async commitTransaction(db, isOpen) {
const msg = 'CommitTransaction: ';
if (!isOpen) {
return Promise.reject(new Error(`${msg}database not opened`));
}
const sql = 'COMMIT TRANSACTION;';
try {
db.exec(sql);
return Promise.resolve();
}
catch (err) {
return Promise.reject(new Error(`${msg}${err.message}`));
}
}
};
export const rollbackTransaction = async (db, isOpen) => {
const msg = 'RollbackTransaction: ';
if (!isOpen) {
return Promise.reject(new Error(`${msg}database not opened`));
static async dbChanges(db) {
const SELECT_CHANGE = 'SELECT total_changes()';
let changes = 0;
try {
const res = db.exec(SELECT_CHANGE);
// process the row here
changes = res[0].values[0][0];
return Promise.resolve(changes);
}
catch (err) {
return Promise.reject(new Error(`DbChanges failed: ${err.message}`));
}
}
const sql = 'ROLLBACK TRANSACTION;';
try {
db.exec(sql);
return Promise.resolve();
static async getLastId(db) {
const SELECT_LAST_ID = 'SELECT last_insert_rowid()';
let lastId = -1;
try {
const res = db.exec(SELECT_LAST_ID);
// process the row here
lastId = res[0].values[0][0];
return Promise.resolve(lastId);
}
catch (err) {
return Promise.reject(new Error(`GetLastId failed: ${err.message}`));
}
}
catch (err) {
return Promise.reject(new Error(`${msg}${err.message}`));
static async setForeignKeyConstraintsEnabled(db, toggle) {
let stmt = 'PRAGMA foreign_keys=OFF';
if (toggle) {
stmt = 'PRAGMA foreign_keys=ON';
}
try {
db.run(stmt);
return Promise.resolve();
}
catch (err) {
const msg = err.message ? err.message : err;
return Promise.reject(new Error(`SetForeignKey: ${msg}`));
}
}
};
export const commitTransaction = async (db, isOpen) => {
const msg = 'CommitTransaction: ';
if (!isOpen) {
return Promise.reject(new Error(`${msg}database not opened`));
static async getVersion(db) {
let version = 0;
try {
const res = db.exec('PRAGMA user_version;');
version = res[0].values[0][0];
return Promise.resolve(version);
}
catch (err) {
return Promise.reject(new Error(`GetVersion: ${err.message}`));
}
}
const sql = 'COMMIT TRANSACTION;';
try {
db.exec(sql);
return Promise.resolve();
static async setVersion(db, version) {
try {
db.exec(`PRAGMA user_version = ${version}`);
return Promise.resolve();
}
catch (err) {
return Promise.reject(new Error(`SetVersion: ${err.message}`));
}
}
catch (err) {
return Promise.reject(new Error(`${msg}${err.message}`));
}
};
export const dbChanges = async (db) => {
const SELECT_CHANGE = 'SELECT total_changes()';
let changes = 0;
try {
const res = db.exec(SELECT_CHANGE);
// process the row here
changes = res[0].values[0][0];
return Promise.resolve(changes);
}
catch (err) {
return Promise.reject(new Error(`DbChanges failed: ${err.message}`));
}
};
export const getLastId = async (db) => {
const SELECT_LAST_ID = 'SELECT last_insert_rowid()';
let lastId = -1;
try {
const res = db.exec(SELECT_LAST_ID);
// process the row here
lastId = res[0].values[0][0];
return Promise.resolve(lastId);
}
catch (err) {
return Promise.reject(new Error(`GetLastId failed: ${err.message}`));
}
};
export const setForeignKeyConstraintsEnabled = async (db, toggle) => {
let stmt = 'PRAGMA foreign_keys=OFF';
if (toggle) {
stmt = 'PRAGMA foreign_keys=ON';
}
try {
db.run(stmt);
return Promise.resolve();
}
catch (err) {
const msg = err.message ? err.message : err;
return Promise.reject(new Error(`SetForeignKey: ${msg}`));
}
};
export const getVersion = async (db) => {
let version = 0;
try {
const res = db.exec('PRAGMA user_version;');
version = res[0].values[0][0];
return Promise.resolve(version);
}
catch (err) {
return Promise.reject(new Error(`GetVersion: ${err.message}`));
}
};
export const setVersion = async (db, version) => {
try {
db.exec(`PRAGMA user_version = ${version}`);
return Promise.resolve();
}
catch (err) {
return Promise.reject(new Error(`SetVersion: ${err.message}`));
}
};
export const execute = async (db, sql, fromJson) => {
let changes = -1;
let initChanges = -1;
try {
initChanges = await dbChanges(db);
var sqlStmt = sql;
// Check for DELETE FROM in sql string
if (!fromJson && sql.toLowerCase().includes('DELETE FROM'.toLowerCase())) {
sqlStmt = sql.replace(/\n/g, '');
let sqlStmts = sqlStmt.split(';');
var resArr = [];
for (const stmt of sqlStmts) {
const trimStmt = stmt.trim().substring(0, 11).toUpperCase();
if (trimStmt === 'DELETE FROM' && stmt.toLowerCase().includes('WHERE'.toLowerCase())) {
const whereStmt = stmt.trim();
const rStmt = await deleteSQL(db, whereStmt, []);
resArr.push(rStmt);
static async execute(db, sql, fromJson) {
try {
var sqlStmt = sql;
// Check for DELETE FROM in sql string
if (!fromJson && sql.toLowerCase().includes('DELETE FROM'.toLowerCase())) {
sqlStmt = sql.replace(/\n/g, '');
let sqlStmts = sqlStmt.split(';');
var resArr = [];
for (const stmt of sqlStmts) {
const trimStmt = stmt.trim().substring(0, 11).toUpperCase();
if (trimStmt === 'DELETE FROM' && stmt.toLowerCase().includes('WHERE'.toLowerCase())) {
const whereStmt = stmt.trim();
const rStmt = await UtilsSQLite.deleteSQL(db, whereStmt, []);
resArr.push(rStmt);
}
else {
resArr.push(stmt);
}
}
else {
resArr.push(stmt);
}
sqlStmt = resArr.join(';');
}
sqlStmt = resArr.join(';');
db.exec(sqlStmt);
const changes = await UtilsSQLite.dbChanges(db);
return Promise.resolve(changes);
}
db.exec(sqlStmt);
changes = (await dbChanges(db)) - initChanges;
return Promise.resolve(changes);
catch (err) {
return Promise.reject(new Error(`Execute: ${err.message}`));
}
}
catch (err) {
return Promise.reject(new Error(`Execute: ${err.message}`));
}
};
export const executeSet = async (db, set, fromJson, returnMode) => {
const retValues = [];
let lastId = -1;
let retObj = {};
for (let i = 0; i < set.length; i++) {
const statement = 'statement' in set[i] ? set[i].statement : null;
const values = 'values' in set[i] && set[i].values.length > 0 ? set[i].values : [];
if (statement == null) {
let msg = 'ExecuteSet: Error No statement';
msg += ` for index ${i}`;
return Promise.reject(new Error(msg));
}
try {
if (Array.isArray(values[0])) {
for (const val of values) {
const mVal = await replaceUndefinedByNull(val);
retObj = await run(db, statement, mVal, fromJson, returnMode);
static async executeSet(db, set, fromJson, returnMode) {
const retValues = [];
let lastId = -1;
let retObj = {};
for (let i = 0; i < set.length; i++) {
const statement = 'statement' in set[i] ? set[i].statement : null;
const values = 'values' in set[i] && set[i].values.length > 0 ? set[i].values : [];
if (statement == null) {
let msg = 'ExecuteSet: Error No statement';
msg += ` for index ${i}`;
return Promise.reject(new Error(msg));
}
try {
if (Array.isArray(values[0])) {
for (const val of values) {
const mVal = await UtilsSQLite.replaceUndefinedByNull(val);
retObj = await UtilsSQLite.run(db, statement, mVal, fromJson, returnMode);
lastId = retObj["lastId"];
if (Object.keys(retObj).includes("values") && retObj["values"].length > 0) {
retValues.push(retObj["values"]);
}
}
}
else {
const mVal = await UtilsSQLite.replaceUndefinedByNull(values);
retObj = await UtilsSQLite.run(db, statement, mVal, fromJson, returnMode);
lastId = retObj["lastId"];

@@ -160,528 +169,364 @@ if (Object.keys(retObj).includes("values") && retObj["values"].length > 0) {

}
catch (err) {
return Promise.reject(new Error(`ExecuteSet: ${err.message}`));
}
}
retObj["lastId"] = lastId;
retObj["values"] = returnMode === 'all' ? retValues :
returnMode === 'one' ? retValues[0] : [];
return Promise.resolve(retObj);
}
static async queryAll(db, sql, values) {
const result = [];
try {
let retArr = [];
if (values != null && values.length > 0) {
retArr = db.exec(sql, values);
}
else {
const mVal = await replaceUndefinedByNull(values);
retObj = await run(db, statement, mVal, fromJson, returnMode);
lastId = retObj["lastId"];
if (Object.keys(retObj).includes("values") && retObj["values"].length > 0) {
retValues.push(retObj["values"]);
retArr = db.exec(sql);
}
if (retArr.length == 0)
return Promise.resolve([]);
for (const valRow of retArr[0].values) {
const row = {};
for (let i = 0; i < retArr[0].columns.length; i++) {
row[retArr[0].columns[i]] = valRow[i];
}
result.push(row);
}
return Promise.resolve(result);
}
catch (err) {
return Promise.reject(new Error(`ExecuteSet: ${err.message}`));
return Promise.reject(new Error(`queryAll: ${err.message}`));
}
}
retObj["lastId"] = lastId;
retObj["values"] = returnMode === 'all' ? retValues :
returnMode === 'one' ? retValues[0] : [];
return Promise.resolve(retObj);
};
export const queryAll = async (db, sql, values) => {
const result = [];
try {
let retArr = [];
if (values != null && values.length > 0) {
retArr = db.exec(sql, values);
}
else {
retArr = db.exec(sql);
}
if (retArr.length == 0)
return Promise.resolve([]);
for (const valRow of retArr[0].values) {
const row = {};
for (let i = 0; i < retArr[0].columns.length; i++) {
row[retArr[0].columns[i]] = valRow[i];
static async run(db, statement, values, fromJson, returnMode) {
let stmtType = statement.replace(/\n/g, "").trim().substring(0, 6).toUpperCase();
let sqlStmt = statement;
let retValues = [];
let retObj = {};
try {
if (!fromJson && stmtType === "DELETE") {
sqlStmt = await UtilsSQLite.deleteSQL(db, statement, values);
}
result.push(row);
}
return Promise.resolve(result);
}
catch (err) {
return Promise.reject(new Error(`queryAll: ${err.message}`));
}
};
export const run = async (db, statement, values, fromJson, returnMode) => {
let stmtType = statement.replace(/\n/g, "").trim().substring(0, 6).toUpperCase();
let sqlStmt = statement;
let retValues = [];
let retObj = {};
try {
if (!fromJson && stmtType === "DELETE") {
sqlStmt = await deleteSQL(db, statement, values);
}
const mValues = values ? values : [];
if (mValues.length > 0) {
const mVal = await replaceUndefinedByNull(mValues);
const res = db.exec(sqlStmt, mVal);
if (returnMode === "all" || returnMode === "one") {
if (res && res.length > 0) {
retValues = getReturnedValues(res[0], returnMode);
const mValues = values ? values : [];
if (mValues.length > 0) {
const mVal = await UtilsSQLite.replaceUndefinedByNull(mValues);
const res = db.exec(sqlStmt, mVal);
if (returnMode === "all" || returnMode === "one") {
if (res && res.length > 0) {
retValues = UtilsSQLite.getReturnedValues(res[0], returnMode);
}
else {
return Promise.reject(new Error(`run: ${sqlStmt} does not returned any change`));
}
}
else {
return Promise.reject(new Error(`run: ${sqlStmt} does not returned any change`));
}
}
}
else {
const res = db.exec(sqlStmt);
if (returnMode === "all" || returnMode === "one") {
if (res && res.length > 0) {
retValues = getReturnedValues(res[0], returnMode);
else {
const res = db.exec(sqlStmt);
if (returnMode === "all" || returnMode === "one") {
if (res && res.length > 0) {
retValues = UtilsSQLite.getReturnedValues(res[0], returnMode);
}
else {
return Promise.reject(new Error(`run: ${sqlStmt} does not returned any change`));
}
}
else {
return Promise.reject(new Error(`run: ${sqlStmt} does not returned any change`));
}
}
const lastId = await UtilsSQLite.getLastId(db);
retObj["lastId"] = lastId;
if (retValues != null && retValues.length > 0)
retObj["values"] = retValues;
return Promise.resolve(retObj);
}
const lastId = await getLastId(db);
retObj["lastId"] = lastId;
if (retValues != null && retValues.length > 0)
retObj["values"] = retValues;
return Promise.resolve(retObj);
}
catch (err) {
return Promise.reject(new Error(`run: ${err.message}`));
}
};
const getReturnedValues = (result, returnMode) => {
const retValues = [];
for (let i = 0; i < result.values.length; i++) {
let row = {};
for (let j = 0; j < result.columns.length; j++) {
row[result.columns[j]] = result.values[i][j];
catch (err) {
return Promise.reject(new Error(`run: ${err.message}`));
}
retValues.push(row);
if (returnMode === 'one')
break;
}
return retValues;
};
export const deleteSQL = async (db, statement, values) => {
let sqlStmt = statement;
try {
const isLast = await isLastModified(db, true);
const isDel = await isSqlDeleted(db, true);
if (isLast && isDel) {
// Replace DELETE by UPDATE and set sql_deleted to 1
const wIdx = statement.toUpperCase().indexOf("WHERE");
const preStmt = statement.substring(0, wIdx - 1);
const clauseStmt = statement.substring(wIdx, statement.length);
const tableName = preStmt.substring(("DELETE FROM").length).trim();
sqlStmt = `UPDATE ${tableName} SET sql_deleted = 1 ${clauseStmt}`;
// Find REFERENCES if any and update the sql_deleted column
await findReferencesAndUpdate(db, tableName, clauseStmt, values);
static getReturnedValues(result, returnMode) {
const retValues = [];
for (let i = 0; i < result.values.length; i++) {
let row = {};
for (let j = 0; j < result.columns.length; j++) {
row[result.columns[j]] = result.values[i][j];
}
retValues.push(row);
if (returnMode === 'one')
break;
}
return Promise.resolve(sqlStmt);
return retValues;
}
catch (err) {
return Promise.reject(new Error(`deleteSQL: ${err.message}`));
}
};
export const findReferencesAndUpdate = async (db, tableName, whereStmt, values) => {
try {
const references = await getReferences(db, tableName);
const tableNameWithRefs = references.pop();
for (const refe of references) {
// get the tableName of the reference
const refTable = await getReferencedTableName(refe);
if (refTable.length <= 0) {
continue;
static async deleteSQL(db, statement, values) {
let sqlStmt = statement;
try {
const isLast = await UtilsSQLite.isLastModified(db, true);
const isDel = await UtilsSQLite.isSqlDeleted(db, true);
if (!isLast || !isDel) {
return sqlStmt;
}
// get the with references columnName
const withRefsNames = await getWithRefsColumnName(refe);
if (withRefsNames.length <= 0) {
continue;
// Replace DELETE by UPDATE
// set sql_deleted to 1 and the last_modified to
// timenow
const whereClause = UtilsSQLStatement.extractWhereClause(sqlStmt);
if (!whereClause) {
const msg = 'deleteSQL: cannot find a WHERE clause';
return Promise.reject(new Error(`${msg}`));
}
// get the referenced columnName
const colNames = await getReferencedColumnName(refe);
if (colNames.length <= 0) {
continue;
const tableName = UtilsSQLStatement.extractTableName(sqlStmt);
if (!tableName) {
const msg = 'deleteSQL: cannot find a WHERE clause';
return Promise.reject(new Error(`${msg}`));
}
// update the where clause
const uWhereStmt = await updateWhere(whereStmt, withRefsNames, colNames);
if (uWhereStmt.length <= 6) {
continue;
const colNames = UtilsSQLStatement.extractColumnNames(whereClause);
if (colNames.length === 0) {
const msg = 'deleteSQL: Did not find column names in the WHERE Statement';
return Promise.reject(new Error(`${msg}`));
}
let updTableName = tableNameWithRefs;
let updColNames = colNames;
if (tableNameWithRefs === tableName) {
updTableName = refTable;
updColNames = withRefsNames;
const setStmt = 'sql_deleted = 1';
// Find REFERENCES if any and update the sql_deleted
// column
const hasToUpdate = await UtilsDelete.findReferencesAndUpdate(db, tableName, whereClause, colNames, values);
if (hasToUpdate) {
const whereStmt = whereClause.endsWith(';')
? whereClause.slice(0, -1)
: whereClause;
sqlStmt = `UPDATE ${tableName} SET ${setStmt} WHERE ${whereStmt} AND sql_deleted = 0;`;
}
//update sql_deleted for this reference
const stmt = "UPDATE " + updTableName + " SET sql_deleted = 1 " + uWhereStmt;
if (values != null && values.length > 0) {
const mVal = await replaceUndefinedByNull(values);
let arrVal = whereStmt.split('?');
if (arrVal[arrVal.length - 1] === ';')
arrVal = arrVal.slice(0, -1);
let selValues = [];
for (const [j, val] of arrVal.entries()) {
for (let i = 0; i < updColNames.length; i++) {
const idxVal = val.indexOf(updColNames[i]);
if (idxVal > -1) {
selValues.push(mVal[j]);
}
}
}
db.exec(stmt, selValues);
}
else {
db.exec(stmt);
sqlStmt = '';
}
const lastId = await getLastId(db);
if (lastId == -1) {
const msg = `UPDATE sql_deleted failed for references table: ${refTable}`;
return Promise.reject(new Error(`findReferencesAndUpdate: ${msg}`));
}
return Promise.resolve(sqlStmt);
}
return;
catch (err) {
let msg = err.message ? err.message : err;
return Promise.reject(new Error(`deleteSQL: ${msg}`));
}
}
catch (err) {
return Promise.reject(new Error(`findReferencesAndUpdate: ${err.message}`));
}
};
export const getReferencedTableName = async (refValue) => {
var tableName = '';
if (refValue.length > 0) {
const arr = refValue.split(new RegExp('REFERENCES', 'i'));
if (arr.length === 2) {
const oPar = arr[1].indexOf("(");
tableName = arr[1].substring(0, oPar).trim();
static async getTableList(db) {
try {
const result = await UtilsDrop.getTablesNames(db);
return Promise.resolve(result);
}
catch (err) {
return Promise.reject(new Error(`getTableList: ${err.message}`));
}
}
return tableName;
};
export const getReferencedColumnName = async (refValue) => {
let colNames = [];
if (refValue.length > 0) {
const arr = refValue.split(new RegExp('REFERENCES', 'i'));
if (arr.length === 2) {
const oPar = arr[1].indexOf("(");
const cPar = arr[1].indexOf(")");
const colStr = arr[1].substring(oPar + 1, cPar).trim();
colNames = colStr.split(',');
static async isTableExists(db, tableName) {
try {
let statement = 'SELECT name FROM sqlite_master WHERE ';
statement += `type='table' AND name='${tableName}';`;
const res = await UtilsSQLite.queryAll(db, statement, []);
const ret = res.length > 0 ? true : false;
return Promise.resolve(ret);
}
catch (err) {
return Promise.reject(new Error(`isTableExists: ${err.message}`));
}
}
return colNames;
};
export const getWithRefsColumnName = async (refValue) => {
let colNames = [];
if (refValue.length > 0) {
const arr = refValue.split(new RegExp('REFERENCES', 'i'));
if (arr.length === 2) {
const oPar = arr[0].indexOf("(");
const cPar = arr[0].indexOf(")");
const colStr = arr[0].substring(oPar + 1, cPar).trim();
colNames = colStr.split(',');
/**
* isLastModified
* @param db
* @param isOpen
*/
static async isLastModified(db, isOpen) {
if (!isOpen) {
return Promise.reject('isLastModified: database not opened');
}
}
return colNames;
};
export const updateWhere = async (whStmt, withRefsNames, colNames) => {
var whereStmt = '';
if (whStmt.length > 0) {
const index = whStmt.toLowerCase().indexOf("WHERE".toLowerCase());
const stmt = whStmt.substring(index + 6);
if (withRefsNames.length === colNames.length) {
for (let i = 0; i < withRefsNames.length; i++) {
let colType = 'withRefsNames';
let idx = stmt.indexOf(withRefsNames[i]);
if (idx === -1) {
idx = stmt.indexOf(colNames[i]);
colType = 'colNames';
try {
const tableList = await UtilsDrop.getTablesNames(db);
for (const table of tableList) {
const tableNamesTypes = await UtilsJSON
.getTableColumnNamesTypes(db, table);
const tableColumnNames = tableNamesTypes.names;
if (tableColumnNames.includes("last_modified")) {
return Promise.resolve(true);
}
if (idx > -1) {
let valStr = "";
const fEqual = stmt.indexOf("=", idx);
if (fEqual > -1) {
const iAnd = stmt.indexOf("AND", fEqual);
const ilAnd = stmt.indexOf("and", fEqual);
if (iAnd > -1) {
valStr = (stmt.substring(fEqual + 1, iAnd - 1)).trim();
}
else if (ilAnd > -1) {
valStr = (stmt.substring(fEqual + 1, ilAnd - 1)).trim();
}
else {
valStr = (stmt.substring(fEqual + 1, stmt.length)).trim();
}
if (i > 0) {
whereStmt += ' AND ';
}
if (colType === 'withRefsNames') {
whereStmt += `${colNames[i]} = ${valStr}`;
}
else {
whereStmt += `${withRefsNames[i]} = ${valStr}`;
}
}
}
}
/*
const fEqual: number = stmt.indexOf("=");
const whereColName: string = stmt.substring(0, fEqual).trim();
whereStmt = whStmt.replace(whereColName, colName);
*/
whereStmt = "WHERE " + whereStmt;
}
catch (err) {
return Promise.reject(`isLastModified: ${err}`);
}
}
return whereStmt;
};
export const getReferences = async (db, tableName) => {
const sqlStmt = "SELECT sql FROM sqlite_master " +
"WHERE sql LIKE('%FOREIGN KEY%') AND sql LIKE('%REFERENCES%') AND " +
"sql LIKE('%" + tableName + "%') AND sql LIKE('%ON DELETE%');";
try {
const res = await queryAll(db, sqlStmt, []);
// get the reference's string(s)
let retRefs = [];
if (res.length > 0) {
retRefs = getRefs(res[0].sql);
/**
* isSqlDeleted
* @param db
* @param isOpen
*/
static async isSqlDeleted(db, isOpen) {
if (!isOpen) {
return Promise.reject('isSqlDeleted: database not opened');
}
return Promise.resolve(retRefs);
}
catch (err) {
return Promise.reject(new Error(`getReferences: ${err.message}`));
}
};
export const getTableList = async (db) => {
try {
const result = await getTablesNames(db);
return Promise.resolve(result);
}
catch (err) {
return Promise.reject(new Error(`getTableList: ${err.message}`));
}
};
export const isTableExists = async (db, tableName) => {
try {
let statement = 'SELECT name FROM sqlite_master WHERE ';
statement += `type='table' AND name='${tableName}';`;
const res = await queryAll(db, statement, []);
const ret = res.length > 0 ? true : false;
return Promise.resolve(ret);
}
catch (err) {
return Promise.reject(new Error(`isTableExists: ${err.message}`));
}
};
/**
* isLastModified
* @param db
* @param isOpen
*/
export const isLastModified = async (db, isOpen) => {
if (!isOpen) {
return Promise.reject('isLastModified: database not opened');
}
try {
const tableList = await getTablesNames(db);
for (const table of tableList) {
const tableNamesTypes = await getTableColumnNamesTypes(db, table);
const tableColumnNames = tableNamesTypes.names;
if (tableColumnNames.includes("last_modified")) {
return Promise.resolve(true);
try {
const tableList = await UtilsDrop.getTablesNames(db);
for (const table of tableList) {
const tableNamesTypes = await UtilsJSON
.getTableColumnNamesTypes(db, table);
const tableColumnNames = tableNamesTypes.names;
if (tableColumnNames.includes("sql_deleted")) {
return Promise.resolve(true);
}
}
}
catch (err) {
return Promise.reject(`isSqlDeleted: ${err}`);
}
}
catch (err) {
return Promise.reject(`isLastModified: ${err}`);
static async replaceUndefinedByNull(values) {
const retValues = [];
for (const val of values) {
let mVal = val;
if (typeof val === 'undefined')
mVal = null;
retValues.push(mVal);
}
return Promise.resolve(retValues);
}
};
/**
* isSqlDeleted
* @param db
* @param isOpen
*/
export const isSqlDeleted = async (db, isOpen) => {
if (!isOpen) {
return Promise.reject('isSqlDeleted: database not opened');
}
try {
const tableList = await getTablesNames(db);
for (const table of tableList) {
const tableNamesTypes = await getTableColumnNamesTypes(db, table);
const tableColumnNames = tableNamesTypes.names;
if (tableColumnNames.includes("sql_deleted")) {
return Promise.resolve(true);
static async backupTables(db) {
const msg = 'BackupTables: ';
let alterTables = {};
try {
const tables = await UtilsDrop.getTablesNames(db);
for (const table of tables) {
try {
const colNames = await UtilsSQLite.backupTable(db, table);
alterTables[`${table}`] = colNames;
}
catch (err) {
return Promise.reject(new Error(`${msg}table ${table}: ` + `${err.message}`));
}
}
return Promise.resolve(alterTables);
}
catch (err) {
return Promise.reject(new Error(`BackupTables: ${err.message}`));
}
}
catch (err) {
return Promise.reject(`isSqlDeleted: ${err}`);
}
};
export const replaceUndefinedByNull = async (values) => {
const retValues = [];
for (const val of values) {
let mVal = val;
if (typeof val === 'undefined')
mVal = null;
retValues.push(mVal);
}
return Promise.resolve(retValues);
};
export const backupTables = async (db) => {
const msg = 'BackupTables: ';
let alterTables = {};
try {
const tables = await getTablesNames(db);
for (const table of tables) {
try {
const colNames = await backupTable(db, table);
alterTables[`${table}`] = colNames;
static async backupTable(db, table) {
try {
// start a transaction
await UtilsSQLite.beginTransaction(db, true);
// get the table's column names
const colNames = await UtilsSQLite.getTableColumnNames(db, table);
const tmpTable = `_temp_${table}`;
// Drop the tmpTable if exists
const delStmt = `DROP TABLE IF EXISTS ${tmpTable};`;
await UtilsSQLite.run(db, delStmt, [], false, 'no');
// prefix the table with _temp_
let stmt = `ALTER TABLE ${table} RENAME `;
stmt += `TO ${tmpTable};`;
const lastId = await UtilsSQLite.run(db, stmt, [], false, 'no');
if (lastId < 0) {
let msg = 'BackupTable: lastId < 0';
try {
await UtilsSQLite.rollbackTransaction(db, true);
}
catch (err) {
msg += `: ${err.message}`;
}
return Promise.reject(new Error(`${msg}`));
}
catch (err) {
return Promise.reject(new Error(`${msg}table ${table}: ` + `${err.message}`));
else {
try {
await UtilsSQLite.commitTransaction(db, true);
return Promise.resolve(colNames);
}
catch (err) {
return Promise.reject(new Error('BackupTable: ' + `${err.message}`));
}
}
}
return Promise.resolve(alterTables);
catch (err) {
return Promise.reject(new Error(`BackupTable: ${err.message}`));
}
}
catch (err) {
return Promise.reject(new Error(`BackupTables: ${err.message}`));
}
};
export const backupTable = async (db, table) => {
try {
// start a transaction
await beginTransaction(db, true);
// get the table's column names
const colNames = await getTableColumnNames(db, table);
const tmpTable = `_temp_${table}`;
// Drop the tmpTable if exists
const delStmt = `DROP TABLE IF EXISTS ${tmpTable};`;
await run(db, delStmt, [], false, 'no');
// prefix the table with _temp_
let stmt = `ALTER TABLE ${table} RENAME `;
stmt += `TO ${tmpTable};`;
const lastId = await run(db, stmt, [], false, 'no');
if (lastId < 0) {
let msg = 'BackupTable: lastId < 0';
try {
await rollbackTransaction(db, true);
static async getTableColumnNames(db, tableName) {
let resQuery = [];
const retNames = [];
const query = `PRAGMA table_info('${tableName}');`;
try {
resQuery = await UtilsSQLite.queryAll(db, query, []);
if (resQuery.length > 0) {
for (const query of resQuery) {
retNames.push(query.name);
}
}
catch (err) {
msg += `: ${err.message}`;
}
return Promise.reject(new Error(`${msg}`));
return Promise.resolve(retNames);
}
else {
try {
await commitTransaction(db, true);
return Promise.resolve(colNames);
}
catch (err) {
return Promise.reject(new Error('BackupTable: ' + `${err.message}`));
}
catch (err) {
return Promise.reject(new Error('GetTableColumnNames: ' + `${err.message}`));
}
}
catch (err) {
return Promise.reject(new Error(`BackupTable: ${err.message}`));
}
};
export const getTableColumnNames = async (db, tableName) => {
let resQuery = [];
const retNames = [];
const query = `PRAGMA table_info('${tableName}');`;
try {
resQuery = await queryAll(db, query, []);
if (resQuery.length > 0) {
for (const query of resQuery) {
retNames.push(query.name);
static async findCommonColumns(db, alterTables) {
let commonColumns = {};
try {
// Get new table list
const tables = await UtilsDrop.getTablesNames(db);
if (tables.length === 0) {
return Promise.reject(new Error('FindCommonColumns: get ' + "table's names failed"));
}
for (const table of tables) {
// get the column's name
const tableNames = await UtilsSQLite.getTableColumnNames(db, table);
// find the common columns
const keys = Object.keys(alterTables);
if (keys.includes(table)) {
commonColumns[table] = UtilsSQLite.arraysIntersection(alterTables[table], tableNames);
}
}
return Promise.resolve(commonColumns);
}
return Promise.resolve(retNames);
catch (err) {
return Promise.reject(new Error(`FindCommonColumns: ${err.message}`));
}
}
catch (err) {
return Promise.reject(new Error('GetTableColumnNames: ' + `${err.message}`));
}
};
export const findCommonColumns = async (db, alterTables) => {
let commonColumns = {};
try {
// Get new table list
const tables = await getTablesNames(db);
if (tables.length === 0) {
return Promise.reject(new Error('FindCommonColumns: get ' + "table's names failed"));
static arraysIntersection(a1, a2) {
if (a1 != null && a2 != null) {
const first = new Set(a1);
const second = new Set(a2);
return [...first].filter(item => second.has(item));
}
for (const table of tables) {
// get the column's name
const tableNames = await getTableColumnNames(db, table);
// find the common columns
const keys = Object.keys(alterTables);
if (keys.includes(table)) {
commonColumns[table] = arraysIntersection(alterTables[table], tableNames);
}
else {
return [];
}
return Promise.resolve(commonColumns);
}
catch (err) {
return Promise.reject(new Error(`FindCommonColumns: ${err.message}`));
}
};
const arraysIntersection = (a1, a2) => {
if (a1 != null && a2 != null) {
const first = new Set(a1);
const second = new Set(a2);
return [...first].filter(item => second.has(item));
}
else {
return [];
}
};
const getRefs = (str) => {
let retRefs = [];
const arrFor = str.split(new RegExp('FOREIGN KEY', 'i'));
// Loop through Foreign Keys
for (let i = 1; i < arrFor.length; i++) {
retRefs.push((arrFor[i].split(new RegExp('ON DELETE', 'i')))[0].trim());
}
// find table name with references
if (str.substring(0, 12).toLowerCase() === 'CREATE TABLE'.toLowerCase()) {
const oPar = str.indexOf("(");
const tableName = str.substring(13, oPar).trim();
retRefs.push(tableName);
}
return retRefs;
};
export const updateNewTablesData = async (db, commonColumns) => {
try {
// start a transaction
await beginTransaction(db, true);
const statements = [];
const keys = Object.keys(commonColumns);
keys.forEach(key => {
const columns = commonColumns[key].join(',');
let stmt = `INSERT INTO ${key} `;
stmt += `(${columns}) `;
stmt += `SELECT ${columns} FROM _temp_${key};`;
statements.push(stmt);
});
const changes = await execute(db, statements.join('\n'), false);
if (changes < 0) {
let msg = 'updateNewTablesData: ' + 'changes < 0';
try {
await rollbackTransaction(db, true);
static async updateNewTablesData(db, commonColumns) {
try {
// start a transaction
await UtilsSQLite.beginTransaction(db, true);
const statements = [];
const keys = Object.keys(commonColumns);
keys.forEach(key => {
const columns = commonColumns[key].join(',');
let stmt = `INSERT INTO ${key} `;
stmt += `(${columns}) `;
stmt += `SELECT ${columns} FROM _temp_${key};`;
statements.push(stmt);
});
const changes = await UtilsSQLite.execute(db, statements.join('\n'), false);
if (changes < 0) {
let msg = 'updateNewTablesData: ' + 'changes < 0';
try {
await UtilsSQLite.rollbackTransaction(db, true);
}
catch (err) {
msg += `: ${err.message}`;
}
return Promise.reject(new Error(`${msg}`));
}
catch (err) {
msg += `: ${err.message}`;
else {
try {
await UtilsSQLite.commitTransaction(db, true);
return Promise.resolve();
}
catch (err) {
return Promise.reject(new Error('updateNewTablesData: ' + `${err.message}`));
}
}
return Promise.reject(new Error(`${msg}`));
}
else {
try {
await commitTransaction(db, true);
return Promise.resolve();
}
catch (err) {
return Promise.reject(new Error('updateNewTablesData: ' + `${err.message}`));
}
catch (err) {
return Promise.reject(new Error('updateNewTablesData: ' + `${err.message}`));
}
}
catch (err) {
return Promise.reject(new Error('updateNewTablesData: ' + `${err.message}`));
}
};
}
//# sourceMappingURL=utils-sqlite.js.map

196

dist/collection/utils/utils-store.js

@@ -1,118 +0,120 @@

export const getDBFromStore = async (dbName, store) => {
try {
const retDb = await store.getItem(dbName);
return Promise.resolve(retDb);
export class UtilsStore {
static async getDBFromStore(dbName, store) {
try {
const retDb = await store.getItem(dbName);
return Promise.resolve(retDb);
}
catch (err) {
return Promise.reject(`GetDBFromStore: ${err.message}`);
}
}
catch (err) {
return Promise.reject(`GetDBFromStore: ${err.message}`);
static async setInitialDBToStore(dbName, store) {
try {
// export the database
const data = null;
// store the database
await store.setItem(dbName, data);
return Promise.resolve();
}
catch (err) {
return Promise.reject(`SetInitialDBToStore: ${err.message}`);
}
}
};
export const setInitialDBToStore = async (dbName, store) => {
try {
// export the database
const data = null;
// store the database
await store.setItem(dbName, data);
return Promise.resolve();
static async setDBToStore(mDb, dbName, store) {
try {
// export the database
const data = mDb.export();
// store the database
await UtilsStore.saveDBToStore(dbName, data, store);
return Promise.resolve();
}
catch (err) {
return Promise.reject(`SetDBToStore: ${err.message}`);
}
}
catch (err) {
return Promise.reject(`SetInitialDBToStore: ${err.message}`);
static async saveDBToStore(dbName, data, store) {
try {
// store the database
await store.setItem(dbName, data);
return Promise.resolve();
}
catch (err) {
return Promise.reject(`SaveDBToStore: ${err.message}`);
}
}
};
export const setDBToStore = async (mDb, dbName, store) => {
try {
// export the database
const data = mDb.export();
// store the database
await saveDBToStore(dbName, data, store);
return Promise.resolve();
static async removeDBFromStore(dbName, store) {
try {
await store.removeItem(dbName);
return Promise.resolve();
}
catch (err) {
return Promise.reject(`RemoveDBFromStore: ${err.message}`);
}
}
catch (err) {
return Promise.reject(`SetDBToStore: ${err.message}`);
static async isDBInStore(dbName, store) {
try {
const retDb = await store.getItem(dbName);
if (retDb != null && retDb.length > 0) {
return Promise.resolve(true);
}
else {
return Promise.resolve(false);
}
}
catch (err) {
return Promise.reject(`IsDBInStore: ${err}`);
}
}
};
export const saveDBToStore = async (dbName, data, store) => {
try {
// store the database
await store.setItem(dbName, data);
return Promise.resolve();
}
catch (err) {
return Promise.reject(`SaveDBToStore: ${err.message}`);
}
};
export const removeDBFromStore = async (dbName, store) => {
try {
await store.removeItem(dbName);
return Promise.resolve();
}
catch (err) {
return Promise.reject(`RemoveDBFromStore: ${err.message}`);
}
};
export const isDBInStore = async (dbName, store) => {
try {
const retDb = await store.getItem(dbName);
if (retDb != null && retDb.length > 0) {
return Promise.resolve(true);
static async restoreDBFromStore(dbName, prefix, store) {
const mFileName = `${prefix}-${dbName}`;
try {
// check if file exists
const isFilePre = await UtilsStore.isDBInStore(mFileName, store);
if (isFilePre) {
const isFile = await UtilsStore.isDBInStore(dbName, store);
if (isFile) {
const retDb = await UtilsStore.getDBFromStore(mFileName, store);
await UtilsStore.saveDBToStore(dbName, retDb, store);
await UtilsStore.removeDBFromStore(mFileName, store);
return Promise.resolve();
}
else {
return Promise.reject(new Error(`RestoreDBFromStore: ${dbName} does not exist`));
}
}
else {
return Promise.reject(new Error(`RestoreDBFromStore: ${mFileName} does not exist`));
}
}
else {
return Promise.resolve(false);
catch (err) {
return Promise.reject(`RestoreDBFromStore: ${err.message}`);
}
}
catch (err) {
return Promise.reject(`IsDBInStore: ${err}`);
}
};
export const restoreDBFromStore = async (dbName, prefix, store) => {
const mFileName = `${prefix}-${dbName}`;
try {
// check if file exists
const isFilePre = await isDBInStore(mFileName, store);
if (isFilePre) {
const isFile = await isDBInStore(dbName, store);
static async copyDBToStore(dbName, toDb, store) {
try {
// check if file exists
const isFile = await UtilsStore.isDBInStore(dbName, store);
if (isFile) {
const retDb = await getDBFromStore(mFileName, store);
await saveDBToStore(dbName, retDb, store);
await removeDBFromStore(mFileName, store);
const retDb = await UtilsStore.getDBFromStore(dbName, store);
await UtilsStore.saveDBToStore(toDb, retDb, store);
return Promise.resolve();
}
else {
return Promise.reject(new Error(`RestoreDBFromStore: ${dbName} does not exist`));
return Promise.reject(new Error(`CopyDBToStore: ${dbName} does not exist`));
}
}
else {
return Promise.reject(new Error(`RestoreDBFromStore: ${mFileName} does not exist`));
catch (err) {
return Promise.reject(`CopyDBToStore: ${err.message}`);
}
}
catch (err) {
return Promise.reject(`RestoreDBFromStore: ${err.message}`);
}
};
export const copyDBToStore = async (dbName, toDb, store) => {
try {
// check if file exists
const isFile = await isDBInStore(dbName, store);
if (isFile) {
const retDb = await getDBFromStore(dbName, store);
await saveDBToStore(toDb, retDb, store);
return Promise.resolve();
static async getDBListFromStore(store) {
try {
const retDbList = await store.keys();
return Promise.resolve(retDbList);
}
else {
return Promise.reject(new Error(`CopyDBToStore: ${dbName} does not exist`));
catch (err) {
return Promise.reject(`GetDBListFromStore: ${err.message}`);
}
}
catch (err) {
return Promise.reject(`CopyDBToStore: ${err.message}`);
}
};
export const getDBListFromStore = async (store) => {
try {
const retDbList = await store.keys();
return Promise.resolve(retDbList);
}
catch (err) {
return Promise.reject(`GetDBListFromStore: ${err.message}`);
}
};
}
//# sourceMappingURL=utils-store.js.map

79

dist/collection/utils/utils-upgrade.js

@@ -1,44 +0,47 @@

import { setForeignKeyConstraintsEnabled, dbChanges, execute, beginTransaction, commitTransaction, rollbackTransaction, setVersion } from '../utils/utils-sqlite';
export const onUpgrade = async (mDb, vUpgDict, curVersion, targetVersion) => {
let changes = -1;
const sortedKeys = new Int32Array(Object.keys(vUpgDict)
.map(item => parseInt(item)))
.sort();
for (const versionKey of sortedKeys) {
if (versionKey > curVersion && versionKey <= targetVersion) {
const statements = vUpgDict[versionKey].statements;
if (statements.length === 0) {
return Promise.reject('onUpgrade: statements not given');
import { UtilsSQLite } from '../utils/utils-sqlite';
export class UtilsUpgrade {
static async onUpgrade(mDb, vUpgDict, curVersion, targetVersion) {
let changes = -1;
const sortedKeys = new Int32Array(Object.keys(vUpgDict)
.map(item => parseInt(item)))
.sort();
for (const versionKey of sortedKeys) {
if (versionKey > curVersion && versionKey <= targetVersion) {
const statements = vUpgDict[versionKey].statements;
if (statements.length === 0) {
return Promise.reject('onUpgrade: statements not given');
}
try {
// set Foreign Keys Off
await UtilsSQLite.setForeignKeyConstraintsEnabled(mDb, false);
const initChanges = await UtilsSQLite.dbChanges(mDb);
await UtilsUpgrade.executeStatementsProcess(mDb, statements);
await UtilsSQLite.setVersion(mDb, versionKey);
// set Foreign Keys On
await UtilsSQLite.setForeignKeyConstraintsEnabled(mDb, true);
changes = (await UtilsSQLite.dbChanges(mDb)) - initChanges;
}
catch (err) {
return Promise.reject(new Error(`onUpgrade: ${err.message}`));
}
}
try {
// set Foreign Keys Off
await setForeignKeyConstraintsEnabled(mDb, false);
const initChanges = await dbChanges(mDb);
await executeStatementsProcess(mDb, statements);
await setVersion(mDb, versionKey);
// set Foreign Keys On
await setForeignKeyConstraintsEnabled(mDb, true);
changes = (await dbChanges(mDb)) - initChanges;
}
catch (err) {
return Promise.reject(new Error(`onUpgrade: ${err.message}`));
}
}
return Promise.resolve(changes);
}
return Promise.resolve(changes);
};
export const executeStatementsProcess = async (mDb, statements) => {
try {
await beginTransaction(mDb, true);
for (const statement of statements) {
await execute(mDb, statement, false);
;
static async executeStatementsProcess(mDb, statements) {
try {
await UtilsSQLite.beginTransaction(mDb, true);
for (const statement of statements) {
await UtilsSQLite.execute(mDb, statement, false);
}
await UtilsSQLite.commitTransaction(mDb, true);
return Promise.resolve();
}
await commitTransaction(mDb, true);
return Promise.resolve();
catch (err) {
await UtilsSQLite.rollbackTransaction(mDb, true);
return Promise.reject(`ExecuteStatementProcess: ${err}`);
}
}
catch (err) {
await rollbackTransaction(mDb, true);
return Promise.reject(`ExecuteStatementProcess: ${err}`);
}
};
}
//# sourceMappingURL=utils-upgrade.js.map

2

dist/jeep-sqlite/jeep-sqlite.esm.js

@@ -1,2 +0,2 @@

import{p as e,b as t}from"./p-68f52286.js";export{s as setNonce}from"./p-68f52286.js";const o=()=>{const t=import.meta.url;const s={};if(t!==""){s.resourcesUrl=new URL(".",t).href}return e(s)};o().then((e=>t([["p-065476b7",[[1,"jeep-sqlite",{autoSave:[516,"autosave"],wasmPath:[513,"wasmpath"],pickText:[513,"picktext"],saveText:[513,"savetext"],buttonOptions:[513,"buttonoptions"],innerAutoSave:[32],innerWasmPath:[32],innerPickText:[32],innerSaveText:[32],innerButtonOptions:[32],echo:[64],createConnection:[64],isConnection:[64],closeConnection:[64],open:[64],close:[64],getVersion:[64],execute:[64],executeSet:[64],run:[64],query:[64],getTableList:[64],isDBExists:[64],isDBOpen:[64],deleteDatabase:[64],isStoreOpen:[64],copyFromAssets:[64],isTableExists:[64],createSyncTable:[64],getSyncDate:[64],setSyncDate:[64],isJsonValid:[64],importFromJson:[64],exportToJson:[64],deleteExportedRows:[64],addUpgradeStatement:[64],isDatabase:[64],getDatabaseList:[64],checkConnectionsConsistency:[64],saveToStore:[64],saveToLocalDisk:[64],getFromLocalDiskToStore:[64],getFromHTTPRequest:[64]}]]]],e)));
import{p as e,b as t}from"./p-68f52286.js";export{s as setNonce}from"./p-68f52286.js";const o=()=>{const t=import.meta.url;const s={};if(t!==""){s.resourcesUrl=new URL(".",t).href}return e(s)};o().then((e=>t([["p-d6900b29",[[1,"jeep-sqlite",{autoSave:[516,"autosave"],wasmPath:[513,"wasmpath"],pickText:[513,"picktext"],saveText:[513,"savetext"],buttonOptions:[513,"buttonoptions"],innerAutoSave:[32],innerWasmPath:[32],innerPickText:[32],innerSaveText:[32],innerButtonOptions:[32],echo:[64],createConnection:[64],isConnection:[64],closeConnection:[64],open:[64],close:[64],getVersion:[64],execute:[64],executeSet:[64],run:[64],query:[64],getTableList:[64],isDBExists:[64],isDBOpen:[64],deleteDatabase:[64],isStoreOpen:[64],copyFromAssets:[64],isTableExists:[64],createSyncTable:[64],getSyncDate:[64],setSyncDate:[64],isJsonValid:[64],importFromJson:[64],exportToJson:[64],deleteExportedRows:[64],addUpgradeStatement:[64],isDatabase:[64],getDatabaseList:[64],checkConnectionsConsistency:[64],saveToStore:[64],saveToLocalDisk:[64],getFromLocalDiskToStore:[64],getFromHTTPRequest:[64]}]]]],e)));
//# sourceMappingURL=jeep-sqlite.esm.js.map

12

dist/types/utils/utils-drop.d.ts

@@ -1,5 +0,7 @@

export declare const getTablesNames: (db: any) => Promise<string[]>;
export declare const getViewsNames: (mDb: any) => Promise<string[]>;
export declare const dropElements: (db: any, type: string) => Promise<void>;
export declare const dropAll: (db: any) => Promise<void>;
export declare const dropTempTables: (db: any, alterTables: Record<string, string[]>) => Promise<void>;
export declare class UtilsDrop {
static getTablesNames(db: any): Promise<string[]>;
static getViewsNames(mDb: any): Promise<string[]>;
static dropElements(db: any, type: string): Promise<void>;
static dropAll(db: any): Promise<void>;
static dropTempTables(db: any, alterTables: Record<string, string[]>): Promise<void>;
}

32

dist/types/utils/utils-exportJson.d.ts
import { EventEmitter } from '../stencil-public-runtime';
import { JsonSQLite, JsonTable, JsonColumn, JsonIndex, JsonTrigger, JsonView, JsonProgressListener } from '../interfaces/interfaces';
export declare const createExportObject: (db: any, sqlObj: JsonSQLite, exportProgress: EventEmitter<JsonProgressListener>) => Promise<JsonSQLite>;
export declare const getViewsName: (mDb: any) => Promise<JsonView[]>;
export declare const getTablesFull: (db: any, resTables: any[], exportProgress: EventEmitter<JsonProgressListener>) => Promise<JsonTable[]>;
export declare const getSchema: (sqlStmt: string) => Promise<JsonColumn[]>;
export declare const getIndexes: (db: any, tableName: string) => Promise<JsonIndex[]>;
export declare const getTriggers: (db: any, tableName: string) => Promise<JsonTrigger[]>;
export declare const getTablesPartial: (db: any, resTables: any[], exportProgress: EventEmitter<JsonProgressListener>) => Promise<JsonTable[]>;
export declare const getPartialModeData: (db: any, resTables: any[]) => Promise<any>;
export declare const getTablesNameSQL: (db: any) => Promise<any[]>;
export declare const getTablesModified: (db: any, tables: any[], syncDate: number) => Promise<any>;
export declare const getSynchroDate: (db: any) => Promise<number>;
export declare const getLastExportDate: (db: any) => Promise<number>;
export declare const setLastExportDate: (db: any, lastExportedDate: string) => Promise<any>;
export declare const delExportedRows: (db: any) => Promise<void>;
export declare class UtilsExportJSON {
static createExportObject(db: any, sqlObj: JsonSQLite, exportProgress: EventEmitter<JsonProgressListener>): Promise<JsonSQLite>;
static getViewsName(mDb: any): Promise<JsonView[]>;
static getTablesFull(db: any, resTables: any[], exportProgress: EventEmitter<JsonProgressListener>): Promise<JsonTable[]>;
static getSchema(sqlStmt: string): Promise<JsonColumn[]>;
static getIndexes(db: any, tableName: string): Promise<JsonIndex[]>;
static getTriggers(db: any, tableName: string): Promise<JsonTrigger[]>;
static getTablesPartial(db: any, resTables: any[], exportProgress: EventEmitter<JsonProgressListener>): Promise<JsonTable[]>;
static getPartialModeData(db: any, resTables: any[]): Promise<any>;
static getTablesNameSQL(db: any): Promise<any[]>;
static getTablesModified(db: any, tables: any[], syncDate: number): Promise<any>;
static getSynchroDate(db: any): Promise<number>;
static getLastExportDate(db: any): Promise<number>;
static setLastExportDate(db: any, lastExportedDate: string): Promise<any>;
static delExportedRows(db: any): Promise<void>;
static modEmbeddedParentheses(sstr: string): Promise<string>;
static indexOfChar(str: string, char: string): number[];
}

30

dist/types/utils/utils-importJson.d.ts
import { EventEmitter } from '../stencil-public-runtime';
import { JsonSQLite, JsonProgressListener, JsonView } from '../interfaces/interfaces';
export declare const createDatabaseSchema: (db: any, jsonData: JsonSQLite) => Promise<number>;
export declare const createSchema: (db: any, jsonData: any) => Promise<number>;
export declare const createSchemaStatement: (jsonData: any) => Promise<string[]>;
export declare const createTablesData: (db: any, jsonData: JsonSQLite, importProgress: EventEmitter<JsonProgressListener>) => Promise<number>;
export declare const createTableData: (db: any, table: any, mode: string) => Promise<number>;
export declare const createRowStatement: (db: any, tColNames: string[], row: any[], j: number, tableName: string, mode: string) => Promise<string>;
export declare const checkUpdate: (db: any, stmt: string, values: any[], tbName: string, tColNames: string[]) => Promise<boolean>;
export declare const isIdExists: (db: any, dbName: string, firstColumnName: string, key: any) => Promise<boolean>;
export declare const isType: (type: string, value: any) => Promise<void>;
export declare const checkColumnTypes: (tableTypes: any[], rowValues: any[]) => Promise<void>;
export declare const createQuestionMarkString: (length: number) => Promise<string>;
export declare const setNameForUpdate: (names: string[]) => Promise<string>;
export declare const createView: (mDB: any, view: JsonView) => Promise<void>;
export declare const createViews: (mDB: any, jsonData: JsonSQLite) => Promise<number>;
export declare class UtilsImportJSON {
static createDatabaseSchema(db: any, jsonData: JsonSQLite): Promise<number>;
static createSchema(db: any, jsonData: any): Promise<number>;
static createSchemaStatement(jsonData: any): Promise<string[]>;
static createTablesData(db: any, jsonData: JsonSQLite, importProgress: EventEmitter<JsonProgressListener>): Promise<number>;
static createTableData(db: any, table: any, mode: string): Promise<number>;
static createRowStatement(db: any, tColNames: string[], row: any[], j: number, tableName: string, mode: string): Promise<string>;
static checkUpdate(db: any, stmt: string, values: any[], tbName: string, tColNames: string[]): Promise<boolean>;
static isIdExists(db: any, dbName: string, firstColumnName: string, key: any): Promise<boolean>;
static isType(type: string, value: any): Promise<void>;
static checkColumnTypes(tableTypes: any[], rowValues: any[]): Promise<void>;
static createQuestionMarkString(length: number): Promise<string>;
static setNameForUpdate(names: string[]): Promise<string>;
static createView(mDB: any, view: JsonView): Promise<void>;
static createViews(mDB: any, jsonData: JsonSQLite): Promise<number>;
}

26

dist/types/utils/utils-json.d.ts
import { JsonColumn, JsonIndex, JsonTrigger, JsonView } from '../interfaces/interfaces';
export declare const isJsonSQLite: (obj: any) => Promise<boolean>;
export declare const isTable: (obj: any) => Promise<boolean>;
export declare const isSchema: (obj: any) => Promise<boolean>;
export declare const isIndexes: (obj: any) => Promise<boolean>;
export declare const isTriggers: (obj: any) => Promise<boolean>;
export declare const isView: (obj: any) => Promise<boolean>;
export declare const checkSchemaValidity: (schema: JsonColumn[]) => Promise<void>;
export declare const checkIndexesValidity: (indexes: JsonIndex[]) => Promise<void>;
export declare const checkTriggersValidity: (triggers: JsonTrigger[]) => Promise<void>;
export declare const checkViewsValidity: (views: JsonView[]) => Promise<void>;
export declare const getTableColumnNamesTypes: (db: any, tableName: string) => Promise<any>;
export declare const getValues: (db: any, query: string, tableName: string) => Promise<any[]>;
export declare class UtilsJSON {
static isJsonSQLite(obj: any): Promise<boolean>;
static isTable(obj: any): Promise<boolean>;
static isSchema(obj: any): Promise<boolean>;
static isIndexes(obj: any): Promise<boolean>;
static isTriggers(obj: any): Promise<boolean>;
static isView(obj: any): Promise<boolean>;
static checkSchemaValidity(schema: JsonColumn[]): Promise<void>;
static checkIndexesValidity(indexes: JsonIndex[]): Promise<void>;
static checkTriggersValidity(triggers: JsonTrigger[]): Promise<void>;
static checkViewsValidity(views: JsonView[]): Promise<void>;
static getTableColumnNamesTypes(db: any, tableName: string): Promise<any>;
static getValues(db: any, query: string, tableName: string): Promise<any[]>;
}

76

dist/types/utils/utils-sqlite.d.ts

@@ -1,39 +0,37 @@

export declare const beginTransaction: (db: any, isOpen: boolean) => Promise<void>;
export declare const rollbackTransaction: (db: any, isOpen: boolean) => Promise<void>;
export declare const commitTransaction: (db: any, isOpen: boolean) => Promise<void>;
export declare const dbChanges: (db: any) => Promise<number>;
export declare const getLastId: (db: any) => Promise<number>;
export declare const setForeignKeyConstraintsEnabled: (db: any, toggle: boolean) => Promise<void>;
export declare const getVersion: (db: any) => Promise<number>;
export declare const setVersion: (db: any, version: number) => Promise<void>;
export declare const execute: (db: any, sql: string, fromJson: boolean) => Promise<number>;
export declare const executeSet: (db: any, set: any, fromJson: boolean, returnMode: string) => Promise<any>;
export declare const queryAll: (db: any, sql: string, values: any[]) => Promise<any[]>;
export declare const run: (db: any, statement: string, values: any[], fromJson: boolean, returnMode: string) => Promise<any>;
export declare const deleteSQL: (db: any, statement: string, values: any[]) => Promise<string>;
export declare const findReferencesAndUpdate: (db: any, tableName: string, whereStmt: string, values: any[]) => Promise<void>;
export declare const getReferencedTableName: (refValue: string) => Promise<string>;
export declare const getReferencedColumnName: (refValue: string) => Promise<string[]>;
export declare const getWithRefsColumnName: (refValue: string) => Promise<string[]>;
export declare const updateWhere: (whStmt: string, withRefsNames: string[], colNames: string[]) => Promise<string>;
export declare const getReferences: (db: any, tableName: string) => Promise<any[]>;
export declare const getTableList: (db: any) => Promise<any[]>;
export declare const isTableExists: (db: any, tableName: string) => Promise<boolean>;
/**
* isLastModified
* @param db
* @param isOpen
*/
export declare const isLastModified: (db: any, isOpen: boolean) => Promise<boolean>;
/**
* isSqlDeleted
* @param db
* @param isOpen
*/
export declare const isSqlDeleted: (db: any, isOpen: boolean) => Promise<boolean>;
export declare const replaceUndefinedByNull: (values: any[]) => Promise<any[]>;
export declare const backupTables: (db: any) => Promise<Record<string, string[]>>;
export declare const backupTable: (db: any, table: string) => Promise<string[]>;
export declare const getTableColumnNames: (db: any, tableName: string) => Promise<string[]>;
export declare const findCommonColumns: (db: any, alterTables: Record<string, string[]>) => Promise<Record<string, string[]>>;
export declare const updateNewTablesData: (db: any, commonColumns: Record<string, string[]>) => Promise<void>;
export declare class UtilsSQLite {
static beginTransaction(db: any, isOpen: boolean): Promise<void>;
static rollbackTransaction(db: any, isOpen: boolean): Promise<void>;
static commitTransaction(db: any, isOpen: boolean): Promise<void>;
static dbChanges(db: any): Promise<number>;
static getLastId(db: any): Promise<number>;
static setForeignKeyConstraintsEnabled(db: any, toggle: boolean): Promise<void>;
static getVersion(db: any): Promise<number>;
static setVersion(db: any, version: number): Promise<void>;
static execute(db: any, sql: string, fromJson: boolean): Promise<number>;
static executeSet(db: any, set: any, fromJson: boolean, returnMode: string): Promise<any>;
static queryAll(db: any, sql: string, values: any[]): Promise<any[]>;
static run(db: any, statement: string, values: any[], fromJson: boolean, returnMode: string): Promise<any>;
static getReturnedValues(result: any, returnMode: string): any[];
static deleteSQL(db: any, statement: string, values: any[]): Promise<string>;
static getTableList(db: any): Promise<any[]>;
static isTableExists(db: any, tableName: string): Promise<boolean>;
/**
* isLastModified
* @param db
* @param isOpen
*/
static isLastModified(db: any, isOpen: boolean): Promise<boolean>;
/**
* isSqlDeleted
* @param db
* @param isOpen
*/
static isSqlDeleted(db: any, isOpen: boolean): Promise<boolean>;
static replaceUndefinedByNull(values: any[]): Promise<any[]>;
static backupTables(db: any): Promise<Record<string, string[]>>;
static backupTable(db: any, table: string): Promise<string[]>;
static getTableColumnNames(db: any, tableName: string): Promise<string[]>;
static findCommonColumns(db: any, alterTables: Record<string, string[]>): Promise<Record<string, string[]>>;
static arraysIntersection(a1: any[], a2: any[]): any[];
static updateNewTablesData(db: any, commonColumns: Record<string, string[]>): Promise<void>;
}

20

dist/types/utils/utils-store.d.ts

@@ -1,9 +0,11 @@

export declare const getDBFromStore: (dbName: string, store: LocalForage) => Promise<Uint8Array>;
export declare const setInitialDBToStore: (dbName: string, store: LocalForage) => Promise<void>;
export declare const setDBToStore: (mDb: any, dbName: string, store: LocalForage) => Promise<void>;
export declare const saveDBToStore: (dbName: string, data: Uint8Array, store: LocalForage) => Promise<void>;
export declare const removeDBFromStore: (dbName: string, store: LocalForage) => Promise<void>;
export declare const isDBInStore: (dbName: string, store: LocalForage) => Promise<boolean>;
export declare const restoreDBFromStore: (dbName: string, prefix: string, store: LocalForage) => Promise<void>;
export declare const copyDBToStore: (dbName: string, toDb: string, store: LocalForage) => Promise<void>;
export declare const getDBListFromStore: (store: LocalForage) => Promise<string[]>;
export declare class UtilsStore {
static getDBFromStore(dbName: string, store: LocalForage): Promise<Uint8Array>;
static setInitialDBToStore(dbName: string, store: LocalForage): Promise<void>;
static setDBToStore(mDb: any, dbName: string, store: LocalForage): Promise<void>;
static saveDBToStore(dbName: string, data: Uint8Array, store: LocalForage): Promise<void>;
static removeDBFromStore(dbName: string, store: LocalForage): Promise<void>;
static isDBInStore(dbName: string, store: LocalForage): Promise<boolean>;
static restoreDBFromStore(dbName: string, prefix: string, store: LocalForage): Promise<void>;
static copyDBToStore(dbName: string, toDb: string, store: LocalForage): Promise<void>;
static getDBListFromStore(store: LocalForage): Promise<string[]>;
}

6

dist/types/utils/utils-upgrade.d.ts
import { SQLiteVersionUpgrade } from '../interfaces/interfaces';
export declare const onUpgrade: (mDb: any, vUpgDict: Record<number, SQLiteVersionUpgrade>, curVersion: number, targetVersion: number) => Promise<number>;
export declare const executeStatementsProcess: (mDb: any, statements: string[]) => Promise<void>;
export declare class UtilsUpgrade {
static onUpgrade(mDb: any, vUpgDict: Record<number, SQLiteVersionUpgrade>, curVersion: number, targetVersion: number): Promise<number>;
static executeStatementsProcess(mDb: any, statements: string[]): Promise<void>;
}

2

package.json
{
"name": "jeep-sqlite",
"version": "2.3.9",
"version": "2.4.0",
"description": "Browser SQLite Stencil Component",

@@ -5,0 +5,0 @@ "main": "dist/index.cjs.js",

dist/jeep-sqlite/p-065476b7.entry.js
dist/jeep-sqlite/p-065476b7.entry.js.map
dist/cjs/jeep-sqlite.cjs.entry.js

Sorry, the diff of this file is too big to display

dist/cjs/jeep-sqlite.cjs.entry.js.map

Sorry, the diff of this file is not supported yet

dist/collection/components/jeep-sqlite/jeep-sqlite.js

Sorry, the diff of this file is too big to display

dist/collection/components/jeep-sqlite/jeep-sqlite.js.map

Sorry, the diff of this file is not supported yet

dist/collection/utils/database.js.map

Sorry, the diff of this file is not supported yet

dist/collection/utils/utils-drop.js.map

Sorry, the diff of this file is not supported yet

dist/collection/utils/utils-exportJson.js.map

Sorry, the diff of this file is not supported yet

dist/collection/utils/utils-importJson.js.map

Sorry, the diff of this file is not supported yet

dist/collection/utils/utils-json.js.map

Sorry, the diff of this file is not supported yet

dist/collection/utils/utils-sqlite.js.map

Sorry, the diff of this file is not supported yet

dist/collection/utils/utils-store.js.map

Sorry, the diff of this file is not supported yet

dist/collection/utils/utils-upgrade.js.map

Sorry, the diff of this file is not supported yet

dist/components/jeep-sqlite.js

Sorry, the diff of this file is too big to display

dist/components/jeep-sqlite.js.map

Sorry, the diff of this file is not supported yet

dist/esm/jeep-sqlite.entry.js

Sorry, the diff of this file is too big to display

dist/esm/jeep-sqlite.entry.js.map

Sorry, the diff of this file is not supported yet

SocketSocket SOC 2 Logo

Product

  • Package Alerts
  • Integrations
  • Docs
  • Pricing
  • FAQ
  • Roadmap
  • Changelog

About

Packages

npm

Go

Maven

PyPI

Rubygems

Stay in touch

Get open source security insights delivered straight into your inbox.

  • Terms
  • Privacy
  • Security

Made with ⚡️ by Socket Inc