jeep-sqlite - npm Package Compare versions

Comparing version 0.0.1-beta.3 to 0.0.1

dist/cjs/jeep-sqlite.cjs.js

@@ -18,3 +18,3 @@ 'use strict';
 patchBrowser().then(options => {
-  return index.bootstrapLazy([["jeep-sqlite.cjs",[[1,"jeep-sqlite",{"echo":[64],"createConnection":[64],"closeConnection":[64],"open":[64],"close":[64],"execute":[64],"executeSet":[64],"run":[64],"query":[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]}]]]], options);
+  return index.bootstrapLazy([["jeep-sqlite.cjs",[[1,"jeep-sqlite",{"echo":[64],"createConnection":[64],"closeConnection":[64],"open":[64],"close":[64],"execute":[64],"executeSet":[64],"run":[64],"query":[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],"addUpgradeStatement":[64]}]]]], options);
 });

dist/cjs/loader.cjs.js

@@ -17,3 +17,3 @@ 'use strict';
   return patchEsm().then(() => {
-  return index.bootstrapLazy([["jeep-sqlite.cjs",[[1,"jeep-sqlite",{"echo":[64],"createConnection":[64],"closeConnection":[64],"open":[64],"close":[64],"execute":[64],"executeSet":[64],"run":[64],"query":[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]}]]]], options);
+  return index.bootstrapLazy([["jeep-sqlite.cjs",[[1,"jeep-sqlite",{"echo":[64],"createConnection":[64],"closeConnection":[64],"open":[64],"close":[64],"execute":[64],"executeSet":[64],"run":[64],"query":[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],"addUpgradeStatement":[64]}]]]], options);
   });
@@ -20,0 +20,0 @@ };

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

@@ -10,2 +10,3 @@ import { Component, Method } from '@stencil/core';
     this._dbDict = {};
+    this._versionUpgrades = {};
   }
@@ -327,2 +328,26 @@ //**********************
   }
+  async addUpgradeStatement(options) {
+    let keys = Object.keys(options);
+    if (!keys.includes('database')) {
+      return Promise.reject('Must provide a database name');
+    }
+    if (!keys.includes('upgrade')) {
+      return Promise.reject('Must provide an upgrade statement');
+    }
+    const dbName = options.database;
+    const upgrade = options.upgrade[0];
+    keys = Object.keys(upgrade);
+    if (!keys.includes('fromVersion') ||
+      !keys.includes('toVersion') ||
+      !keys.includes('statement')) {
+      return Promise.reject('Must provide an upgrade capSQLiteVersionUpgrade Object');
+    }
+    if (typeof upgrade.fromVersion != 'number') {
+      return Promise.reject('ugrade.fromVersion must be a number');
+    }
+    const upgVDict = {};
+    upgVDict[upgrade.fromVersion] = upgrade;
+    this._versionUpgrades[dbName] = upgVDict;
+    return Promise.resolve();
+  }
   //*******************************
@@ -342,4 +367,9 @@ //* Component Lifecycle Methods *
   async _createConnection(database, version) {
+    let upgDict = {};
+    const vUpgKeys = Object.keys(this._versionUpgrades);
+    if (vUpgKeys.length !== 0 && vUpgKeys.includes(database)) {
+      upgDict = this._versionUpgrades[database];
+    }
     try {
-      const mDB = new Database(database + 'SQLite.db', version, this.store);
+      const mDB = new Database(database + 'SQLite.db', version, upgDict, this.store);
       this._dbDict[database] = mDB;
@@ -596,3 +626,3 @@ return Promise.resolve();
     // Create the database
-    const mDb = new Database(dbName, dbVersion, this.store);
+    const mDb = new Database(dbName, dbVersion, {}, this.store);
     try {
@@ -1245,4 +1275,34 @@ // Open the database
       }
+    },
+    "addUpgradeStatement": {
+      "complexType": {
+        "signature": "(options: SQLiteUpgradeOptions) => Promise<void>",
+        "parameters": [{
+            "tags": [],
+            "text": ""
+          }],
+        "references": {
+          "Promise": {
+            "location": "global"
+          },
+          "SQLiteUpgradeOptions": {
+            "location": "import",
+            "path": "../../interfaces/interfaces"
+          },
+          "Record": {
+            "location": "global"
+          },
+          "SQLiteVersionUpgrade": {
+            "location": "import",
+            "path": "../../interfaces/interfaces"
+          }
+        },
+        "return": "Promise<void>"
+      },
+      "docs": {
+        "text": "",
+        "tags": []
+      }
     }
   }; }
 }

dist/collection/utils/database.js

 import initSqlJs from 'sql.js';
-import { getDBFromStore, setInitialDBToStore, setDBToStore, removeDBFromStore, isDBInStore } from './utils-store';
-import { dbChanges, beginTransaction, rollbackTransaction, commitTransaction, execute, executeSet, run, queryAll, isTableExists } from './utils-sqlite';
+import { getDBFromStore, setInitialDBToStore, setDBToStore, removeDBFromStore, isDBInStore, restoreDBFromStore } from './utils-store';
+import { dbChanges, beginTransaction, rollbackTransaction, commitTransaction, execute, executeSet, run, queryAll, isTableExists, getVersion, setVersion } from './utils-sqlite';
 import { createDatabaseSchema, createTablesData } from './utils-importJson';
 import { isJsonSQLite } from './utils-json';
 import { createExportObject, getSynchroDate } from './utils-exportJson';
+import { onUpgrade } from './utils-upgrade';
 export class Database {
-  constructor(databaseName, version, store) {
+  constructor(databaseName, version, upgDict, store) {
+    this.vUpgDict = {};
     this.dbName = databaseName;
@@ -13,2 +15,3 @@ this.store = store;
     this.mDb = null;
+    this.vUpgDict = upgDict;
     this._isDBOpen = false;
@@ -32,3 +35,35 @@ }
       }
+      // get the current version
+      let curVersion = await getVersion(this.mDb);
+      if (curVersion === 0) {
+        await setVersion(this.mDb, 1);
+        curVersion = await getVersion(this.mDb);
+      }
       this._isDBOpen = true;
+      if (this.version > curVersion) {
+        try {
+          // execute the upgrade flow process
+          const changes = await onUpgrade(this.mDb, this.vUpgDict, this.dbName, curVersion, this.version, this.store);
+          if (changes === -1) {
+            // restore the database from backup
+            try {
+              await restoreDBFromStore(this.dbName, 'backup', this.store);
+            }
+            catch (err) {
+              return Promise.reject(new Error(`Open: ${err.message}`));
+            }
+          }
+          // delete the backup database
+          await removeDBFromStore(`backup-${this.dbName}`, this.store);
+        }
+        catch (err) {
+          // restore the database from backup
+          try {
+            await restoreDBFromStore(this.dbName, 'backup', this.store);
+          }
+          catch (err) {
+            return Promise.reject(new Error(`Open: ${err.message}`));
+          }
+        }
+      }
       return Promise.resolve();
@@ -35,0 +70,0 @@ }

dist/collection/utils/utils-drop.js

@@ -1,2 +0,2 @@
-import { queryAll, run } from './utils-sqlite';
+import { queryAll, run, execute } from './utils-sqlite';
 export const getTablesNames = async (db) => {
@@ -76,1 +76,20 @@ let sql = 'SELECT name FROM sqlite_master WHERE ';
 };
+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'));
+    if (changes < 0) {
+      return Promise.reject(new Error('DropTempTables: changes < 0'));
+    }
+    return Promise.resolve();
+  }
+  catch (err) {
+    return Promise.reject(new Error(`DropTempTables: ${err.message}`));
+  }
+};

dist/collection/utils/utils-sqlite.js

@@ -0,1 +1,2 @@
+import { getTablesNames } from '../utils/utils-drop';
 export const beginTransaction = async (db, isOpen) => {
@@ -208,1 +209,143 @@ const msg = 'BeginTransaction: ';
 };
+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;
+      }
+      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}`));
+  }
+};
+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);
+    // prefix the table with _temp_
+    let stmt = `ALTER TABLE ${table} RENAME `;
+    stmt += `TO _temp_${table};`;
+    const lastId = await run(db, stmt, []);
+    if (lastId < 0) {
+      let msg = 'BackupTable: lastId < 0';
+      try {
+        await rollbackTransaction(db, true);
+      }
+      catch (err) {
+        msg += `: ${err.message}`;
+      }
+      return Promise.reject(new Error(`${msg}`));
+    }
+    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(`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);
+      }
+    }
+    return Promise.resolve(retNames);
+  }
+  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"));
+    }
+    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);
+      }
+    }
+    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 [];
+  }
+};
+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'));
+    if (changes < 0) {
+      let msg = 'updateNewTablesData: ' + 'changes < 0';
+      try {
+        await rollbackTransaction(db, true);
+      }
+      catch (err) {
+        msg += `: ${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}`));
+  }
+};

dist/collection/utils/utils-store.js

@@ -7,3 +7,3 @@ export const getDBFromStore = async (dbName, store) => {
   catch (err) {
-    return Promise.reject(`in getDBFromStore ${err.message}`);
+    return Promise.reject(`GetDBFromStore: ${err.message}`);
   }
@@ -20,3 +20,3 @@ };
   catch (err) {
-    return Promise.reject(`in setDBToStore ${err.message}`);
+    return Promise.reject(`SetInitialDBToStore: ${err.message}`);
   }
@@ -33,3 +33,3 @@ };
   catch (err) {
-    return Promise.reject(`in setDBToStore ${err.message}`);
+    return Promise.reject(`SetDBToStore: ${err.message}`);
   }
@@ -44,3 +44,3 @@ };
   catch (err) {
-    return Promise.reject(`in setDBToStore ${err.message}`);
+    return Promise.reject(`SaveDBToStore: ${err.message}`);
   }
@@ -54,3 +54,3 @@ };
   catch (err) {
-    return Promise.reject(`in removeDBFromStore ${err.message}`);
+    return Promise.reject(`RemoveDBFromStore: ${err.message}`);
   }
@@ -69,4 +69,46 @@ };
   catch (err) {
-    return Promise.reject(`in isDBInStore ${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);
+      if (isFile) {
+        const retDb = await getDBFromStore(mFileName, store);
+        await saveDBToStore(dbName, retDb, store);
+        await 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`));
+    }
+  }
+  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();
+    }
+    else {
+      return Promise.reject(new Error(`CopyDBToStore: ${dbName} does not exist`));
+    }
+  }
+  catch (err) {
+    return Promise.reject(`CopyDBToStore: ${err.message}`);
+  }
+};

dist/esm/jeep-sqlite.js

@@ -16,3 +16,3 @@ import { p as promiseResolve, b as bootstrapLazy } from './index-44bbc037.js';
 patchBrowser().then(options => {
-  return bootstrapLazy([["jeep-sqlite",[[1,"jeep-sqlite",{"echo":[64],"createConnection":[64],"closeConnection":[64],"open":[64],"close":[64],"execute":[64],"executeSet":[64],"run":[64],"query":[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]}]]]], options);
+  return bootstrapLazy([["jeep-sqlite",[[1,"jeep-sqlite",{"echo":[64],"createConnection":[64],"closeConnection":[64],"open":[64],"close":[64],"execute":[64],"executeSet":[64],"run":[64],"query":[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],"addUpgradeStatement":[64]}]]]], options);
 });

dist/esm/loader.js

@@ -13,3 +13,3 @@ import { p as promiseResolve, b as bootstrapLazy } from './index-44bbc037.js';
   return patchEsm().then(() => {
-  return bootstrapLazy([["jeep-sqlite",[[1,"jeep-sqlite",{"echo":[64],"createConnection":[64],"closeConnection":[64],"open":[64],"close":[64],"execute":[64],"executeSet":[64],"run":[64],"query":[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]}]]]], options);
+  return bootstrapLazy([["jeep-sqlite",[[1,"jeep-sqlite",{"echo":[64],"createConnection":[64],"closeConnection":[64],"open":[64],"close":[64],"execute":[64],"executeSet":[64],"run":[64],"query":[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],"addUpgradeStatement":[64]}]]]], options);
   });
@@ -16,0 +16,0 @@ };

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

@@ -1,1 +0,1 @@
-import{p as e,b as s}from"./p-0238e775.js";(()=>{const s=import.meta.url,t={};return""!==s&&(t.resourcesUrl=new URL(".",s).href),e(t)})().then((e=>s([["p-27b4daa8",[[1,"jeep-sqlite",{echo:[64],createConnection:[64],closeConnection:[64],open:[64],close:[64],execute:[64],executeSet:[64],run:[64],query:[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]}]]]],e)));
+import{p as e,b as t}from"./p-0238e775.js";(()=>{const t=import.meta.url,s={};return""!==t&&(s.resourcesUrl=new URL(".",t).href),e(s)})().then((e=>t([["p-2d0ca263",[[1,"jeep-sqlite",{echo:[64],createConnection:[64],closeConnection:[64],open:[64],close:[64],execute:[64],executeSet:[64],run:[64],query:[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],addUpgradeStatement:[64]}]]]],e)));

dist/types/components.d.ts

@@ -8,5 +8,6 @@ /* eslint-disable */
 import { HTMLStencilElement, JSXBase } from "./stencil-public-runtime";
-import { ConnectionOptions, EchoResult, SQLiteChanges, SQLiteExecuteOptions, SQLiteExportOptions, SQLiteImportOptions, SQLiteJson, SQLiteOptions, SQLiteQueryOptions, SQLiteResult, SQLiteRunOptions, SQLiteSet, SQLiteSetOptions, SQLiteSyncDate, SQLiteSyncDateOptions, SQLiteTableOptions, SQLiteValues } from "./interfaces/interfaces";
+import { ConnectionOptions, EchoResult, SQLiteChanges, SQLiteExecuteOptions, SQLiteExportOptions, SQLiteImportOptions, SQLiteJson, SQLiteOptions, SQLiteQueryOptions, SQLiteResult, SQLiteRunOptions, SQLiteSet, SQLiteSetOptions, SQLiteSyncDate, SQLiteSyncDateOptions, SQLiteTableOptions, SQLiteUpgradeOptions, SQLiteValues, SQLiteVersionUpgrade } from "./interfaces/interfaces";
 export namespace Components {
     interface JeepSqlite {
+        "addUpgradeStatement": (options: SQLiteUpgradeOptions) => Promise<void>;
         "close": (options: SQLiteOptions) => Promise<void>;
@@ -13,0 +14,0 @@ "closeConnection": (options: SQLiteOptions) => Promise<void>;

dist/types/components/jeep-sqlite/jeep-sqlite.d.ts

@@ -1,2 +0,2 @@
-import { ConnectionOptions, SQLiteOptions, SQLiteExecuteOptions, SQLiteQueryOptions, SQLiteRunOptions, SQLiteSetOptions, SQLiteTableOptions, SQLiteSyncDateOptions, SQLiteImportOptions, SQLiteExportOptions, EchoResult, SQLiteChanges, SQLiteResult, SQLiteValues, SQLiteSyncDate, SQLiteJson } from '../../interfaces/interfaces';
+import { ConnectionOptions, SQLiteOptions, SQLiteExecuteOptions, SQLiteQueryOptions, SQLiteRunOptions, SQLiteSetOptions, SQLiteTableOptions, SQLiteSyncDateOptions, SQLiteImportOptions, SQLiteExportOptions, SQLiteUpgradeOptions, EchoResult, SQLiteChanges, SQLiteResult, SQLiteValues, SQLiteSyncDate, SQLiteJson } from '../../interfaces/interfaces';
 export declare class JeepSqlite {
@@ -24,2 +24,3 @@ echo(value: string): Promise<EchoResult>;
   exportToJson(options: SQLiteExportOptions): Promise<SQLiteJson>;
+  addUpgradeStatement(options: SQLiteUpgradeOptions): Promise<void>;
   private store;
@@ -30,2 +31,3 @@ private storeName;
   private databaseList;
+  private _versionUpgrades;
   componentWillLoad(): Promise<void>;
@@ -32,0 +34,0 @@ componentDidLoad(): void;

dist/types/interfaces/interfaces.d.ts

@@ -152,2 +152,19 @@ export interface EchoOptions {
 }
+export interface SQLiteUpgradeOptions {
+  /**
+   * The database name
+   */
+  database?: string;
+  /**
+   * The upgrade options for version upgrade
+   * Array of length 1 to easiest the iOS plugin
+   */
+  upgrade?: SQLiteVersionUpgrade[];
+}
+export interface SQLiteVersionUpgrade {
+  fromVersion: number;
+  toVersion: number;
+  statement: string;
+  set?: SQLiteSet[];
+}
 export interface SQLiteSet {
@@ -154,0 +171,0 @@ /**

dist/types/utils/database.d.ts

@@ -1,2 +0,2 @@
-import { SQLiteSet, JsonSQLite } from '../interfaces/interfaces';
+import { SQLiteSet, JsonSQLite, SQLiteVersionUpgrade } from '../interfaces/interfaces';
 export declare class Database {
@@ -8,3 +8,4 @@ private _isDBOpen;
   private mDb;
-  constructor(databaseName: string, version: number, store: LocalForage);
+  private vUpgDict;
+  constructor(databaseName: string, version: number, upgDict: Record<number, SQLiteVersionUpgrade>, store: LocalForage);
   open(): Promise<void>;
@@ -11,0 +12,0 @@ isDBOpen(): boolean;

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

 export declare const getTablesNames: (db: 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>;

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

@@ -15,1 +15,6 @@ export declare const beginTransaction: (db: any, isOpen: boolean) => Promise<void>;
 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>;

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

@@ -7,1 +7,3 @@ export declare const getDBFromStore: (dbName: string, store: LocalForage) => Promise<Uint8Array>;
 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>;

package.json

 {
   "name": "jeep-sqlite",
-  "version": "0.0.1-beta.3",
+  "version": "0.0.1",
   "description": "Browser SQLite Stencil Component",
@@ -5,0 +5,0 @@ "main": "dist/index.cjs.js",

readme.md

@@ -13,4 +13,2 @@ ![Built With Stencil](https://img.shields.io/badge/-Built%20With%20Stencil-16161d.svg?logo=data%3Aimage%2Fsvg%2Bxml%3Bbase64%2CPD94bWwgdmVyc2lvbj0iMS4wIiBlbmNvZGluZz0idXRmLTgiPz4KPCEtLSBHZW5lcmF0b3I6IEFkb2JlIElsbHVzdHJhdG9yIDE5LjIuMSwgU1ZHIEV4cG9ydCBQbHVnLUluIC4gU1ZHIFZlcnNpb246IDYuMDAgQnVpbGQgMCkgIC0tPgo8c3ZnIHZlcnNpb249IjEuMSIgaWQ9IkxheWVyXzEiIHhtbG5zPSJodHRwOi8vd3d3LnczLm9yZy8yMDAwL3N2ZyIgeG1sbnM6eGxpbms9Imh0dHA6Ly93d3cudzMub3JnLzE5OTkveGxpbmsiIHg9IjBweCIgeT0iMHB4IgoJIHZpZXdCb3g9IjAgMCA1MTIgNTEyIiBzdHlsZT0iZW5hYmxlLWJhY2tncm91bmQ6bmV3IDAgMCA1MTIgNTEyOyIgeG1sOnNwYWNlPSJwcmVzZXJ2ZSI%2BCjxzdHlsZSB0eXBlPSJ0ZXh0L2NzcyI%2BCgkuc3Qwe2ZpbGw6I0ZGRkZGRjt9Cjwvc3R5bGU%2BCjxwYXRoIGNsYXNzPSJzdDAiIGQ9Ik00MjQuNywzNzMuOWMwLDM3LjYtNTUuMSw2OC42LTkyLjcsNjguNkgxODAuNGMtMzcuOSwwLTkyLjctMzAuNy05Mi43LTY4LjZ2LTMuNmgzMzYuOVYzNzMuOXoiLz4KPHBhdGggY2xhc3M9InN0MCIgZD0iTTQyNC43LDI5Mi4xSDE4MC40Yy0zNy42LDAtOTIuNy0zMS05Mi43LTY4LjZ2LTMuNkgzMzJjMzcuNiwwLDkyLjcsMzEsOTIuNyw2OC42VjI5Mi4xeiIvPgo8cGF0aCBjbGFzcz0ic3QwIiBkPSJNNDI0LjcsMTQxLjdIODcuN3YtMy42YzAtMzcuNiw1NC44LTY4LjYsOTIuNy02OC42SDMzMmMzNy45LDAsOTIuNywzMC43LDkyLjcsNjguNlYxNDEuN3oiLz4KPC9zdmc%2BCg%3D%3D&colorA=16161d&style=flat-square)
 
-This is the initial version `ALPHA` and does not include all the functionalities especially in the import and export of databases.
-
 It will be used at that stage to test the integration with the `@capacitor-community-sqlite` but can also be used in development of `Stencil` or `Ionic/Angular` applications.
@@ -22,11 +20,2 @@
 
-# Stencil
-
-Stencil is a compiler for building fast web apps using Web Components.
-
-Stencil combines the best concepts of the most popular frontend frameworks into a compile-time rather than run-time tool.  Stencil takes TypeScript, JSX, a tiny virtual DOM layer, efficient one-way data binding, an asynchronous rendering pipeline (similar to React Fiber), and lazy-loading out of the box, and generates 100% standards-based Web Components that run in any browser supporting the Custom Elements v1 spec.
-
-Stencil components are just Web Components, so they work in any major framework or with no framework at all.
-
-
 ## Getting Started
@@ -61,2 +50,3 @@
 | :-------------------------- | :------ |
+| echo                        | ✅      |
 | createConnection            | ✅      |
@@ -82,3 +72,5 @@ | closeConnection             | ✅      |
 | copyFromAssets              | ✅      |
+| addUpgradeStatement         | ✅      |
 
+The database is saved when you run the methods `close`or `closeConnection`, in the Browser Storage `IndexedDB` as a `localforage` store under the `jeepSqliteStore` name and `databases`table name.
 
@@ -120,8 +112,7 @@ The `copyFromAssets` required to have a `databases.json`file having the name of the databases in the `assets/databases`
     const jeepSqlite = document.querySelector('jeep-sqlite');
-    console.log("jeepSqlite " + JSON.stringify(jeepSqlite));
-      console.log("$$$ in script before createConnection");
       let result = await jeepSqlite.echo("Hello World from Jeep");
-      console.log("from Echo " + result.value);
       if(await jeepSqlite.isStoreOpen()) {
           try {
+
+            // *** test all basic methods
             await jeepSqlite.createConnection({
@@ -131,6 +122,5 @@ database:"testNew",
                   });
-            // open db testNew
+              // open db testNew
               await jeepSqlite.open({database: "testNew"});
               const isDB = await jeepSqlite.isDBOpen({database: "testNew"})
-              console.log(`in script ${JSON.stringify(isDB)}`);
               let sql = "CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY NOT NULL,email TEXT UNIQUE NOT NULL,name TEXT,company TEXT,size REAL,age INTEGER,last_modified INTEGER DEFAULT (strftime('%s', 'now')));";
@@ -141,5 +131,15 @@ sql += "CREATE INDEX IF NOT EXISTS users_index_name ON users (name);";
               sql += "PRAGMA user_version = 1;";
-              console.log("@@@ sql " + sql);
               let ret = await jeepSqlite.execute({database: "testNew", statements: sql});
-              console.log(`after Execute 1 ${JSON.stringify(ret)}`);
+              ret = await jeepSqlite.isTableExists({database: "testNew", table: "users"});
+              ret = await jeepSqlite.isTableExists({database: "testNew", table: "contact"});
+              // create synchronization table
+              ret = await jeepSqlite.createSyncTable({database: "testNew"});
+              // set the synchronization date
+              let syncDate = "2021-08-01T08:42:25.000Z";
+              await jeepSqlite.setSyncDate({database: "testNew", syncdate: syncDate});
+              // get the synchronization date
+              ret = await jeepSqlite.getSyncDate({database: "testNew"});
+              if(ret.syncDate !== 1627807345) {
+                throw new Error("Get the synchronization date failed");
+              }
               // Insert some Users
@@ -150,7 +150,5 @@ const row = [["Whiteley","Whiteley.com",30,1.83],["Jones","Jones.com",44,1.75]];
               ret = await jeepSqlite.execute({database: "testNew", statements: delUsers, transaction: false});
-              console.log(`after Execute 2 ${JSON.stringify(ret)}`);
               let twoUsers = `INSERT INTO users (name,email,age,size) VALUES ("${row[0][0]}","${row[0][1]}",${row[0][2]},${row[0][3]});`;
               twoUsers += `INSERT INTO users (name,email,age,size) VALUES ("${row[1][0]}","${row[1][1]}",${row[1][2]},${row[1][3]});`;
               ret = await jeepSqlite.execute({database: "testNew", statements: twoUsers});
-              console.log(`after Execute 3 ${JSON.stringify(ret)}`);
               if (ret.changes.changes !== 2) {
@@ -162,3 +160,2 @@ throw new Error("Execute 3 users failed");
                                             statement: "SELECT * FROM users;"});
-              console.log(`after Query 1 ${JSON.stringify(ret)}`);
               // Select users where size > 1.80
@@ -168,3 +165,2 @@ ret = await jeepSqlite.query({database: "testNew",
                                             values:[1.80]});
-              console.log(`after Query 2 ${JSON.stringify(ret)}`);
               // add one user with statement and values
@@ -176,3 +172,2 @@ let sqlcmd = "INSERT INTO users (name,email,age,size,company) VALUES (?,?,?,?,?)";
                                             values: values});
-              console.log(`after run 1: ${JSON.stringify(ret)} `);
               if(ret.changes.lastId !== 3) {
@@ -192,7 +187,7 @@ throw new Error("Run 1 user failed");
                                             statement: "SELECT * FROM users;"});
-              console.log(`after Query 3 ${JSON.stringify(ret)}`);
               if(ret.values.length != 4) {
                 throw new Error("Query 3 user failed");
               }
-              // Test executeSet
+
+              // *** test ExecuteSet
               await jeepSqlite.createConnection({
@@ -203,3 +198,2 @@ database:"testSet",
               ret = await jeepSqlite.isDBExists({database:"testSet"});
-              console.log(`is "testSet" database exist : ${ret.result}`);
               if (ret.result) {
@@ -243,6 +237,4 @@ await jeepSqlite.deleteDatabase({database:"testSet"});
               ret = await jeepSqlite.execute({database: "testSet", statements: createSchemaContacts});
-              console.log(`after Contact Execute 1 ${JSON.stringify(ret)}`);
               // Create testSet contact
               ret = await jeepSqlite.executeSet({database: "testSet", set: setContacts});
-              console.log(`after Contact Execute 1 ${JSON.stringify(ret)}`);
               if (ret.changes.changes !== 5) {
@@ -252,5 +244,419 @@ return Promise.reject(new Error("ExecuteSet 5 contacts failed"));
 
+              // *** test Import from Json
+              const dataToImport = {
+                database : "db-from-json",
+                version : 1,
+                encrypted : false,
+                mode : "full",
+                tables :[
+                  {
+                    name: "users",
+                    schema: [
+                        {column:"id", value: "INTEGER PRIMARY KEY NOT NULL"},
+                        {column:"email", value:"TEXT UNIQUE NOT NULL"},
+                        {column:"name", value:"TEXT"},
+                        {column:"age", value:"REAL"},
+                        {column:"last_modified", value:"INTEGER"}
+                    ],
+                    indexes: [
+                        {name: "index_user_on_name",value: "name"},
+                        {name: "index_user_on_last_modified",value: "last_modified DESC"},
+                        {name: "index_user_on_email_name", value: "email ASC, name", mode: "UNIQUE"}
+                    ],
+                    values: [
+                        [1,"Whiteley.com","Whiteley",30.5,1582536810],
+                        [2,"Jones.com","Jones",44.2,1582812800],
+                        [3,"[e-mail]","Simpson",69,1583570630],
+                        [4,"[e-mail]","Brown",15,1590383895]
+                    ]
+                  },
+                  {
+                    name: "messages",
+                    schema: [
+                      {column:"id", value: "INTEGER PRIMARY KEY NOT NULL"},
+                      {column:"title", value:"TEXT NOT NULL"},
+                      {column:"body", value:"TEXT NOT NULL"},
+                      {column:"last_modified", value:"INTEGER"}
+                    ],
+                    values: [
+                        [1,"test post 1","content test post 1",1587310030],
+                        [2,"test post 2","content test post 2",1590388125]
+                    ]
+                  },
+                ]
+              };
+              // test Json object validity
+              result = await jeepSqlite.isJsonValid({jsonstring: JSON.stringify(dataToImport)});
+              if(!result.result) {
+                return Promise.reject(new Error("IsJsonValid failed"));
+              }
+              // full import
+              result = await jeepSqlite.importFromJson({jsonstring: JSON.stringify(dataToImport)});
+              if(result.changes.changes === -1 ) return Promise.reject(new Error("ImportFromJson 'full' dataToImport failed"));
+              // create the connection to the database
+              await jeepSqlite.createConnection({database:"db-from-json", version: 1});
+              // open db testNew
+              await jeepSqlite.open({database: "db-from-json"});
+              const isDBJson = await jeepSqlite.isDBOpen({database: "db-from-json"})
+              if (!isDBJson.result) return Promise.reject(new Error("isDBOpen 'db-from-json' failed"));
+              // create synchronization table
+              ret = await jeepSqlite.createSyncTable({database: "db-from-json"});
+              if (result.changes.changes < 0) return Promise.reject(new Error("CreateSyncTable failed"));
+              ret = await jeepSqlite.getSyncDate({database: "db-from-json"});
+              if(ret.length === 0) return Promise.reject(new Error("GetSyncDate failed"));
+              // Select all users
+              ret = await jeepSqlite.query({database: "db-from-json",
+                                            statement: "SELECT * FROM users;"});
+              if(ret.values.length !== 4 ||
+                    ret.values[0].name !== "Whiteley" ||
+                    ret.values[1].name !== "Jones" ||
+                    ret.values[2].name !== "Simpson" ||
+                    ret.values[3].name !== "Brown"  ) {
+                return Promise.reject(new Error("Query 1 db-from-json Users failed"));
+              }
+              await jeepSqlite.closeConnection({database:"db-from-json"});
 
+              const partialImport1 = {
+                database : "db-from-json",
+                version : 1,
+                encrypted : false,
+                mode : "partial",
+                tables :[
+                  {
+                      name: "users",
+                      values: [
+                          [5,"Addington.com","Addington",22.7,1590388335],
+                          [6,"Bannister.com","Bannister",59,1590393015],
+                          [2,"[e-mail]","Jones",45,1590393325]
+
+                      ]
+                  },
+                ]
+              };
+              // test Json object validity
+              result = await jeepSqlite.isJsonValid({jsonstring: JSON.stringify(partialImport1)});
+              if(!result.result) {
+                return Promise.reject(new Error("IsJsonValid failed"));
+              }
+              // partial import
+              result = await jeepSqlite.importFromJson({jsonstring: JSON.stringify(partialImport1)});
+              if(result.changes.changes === -1 ) return Promise.reject(new Error("ImportFromJson 'partial1' dataToImport failed"));
+
+
+              const partialImport2 = {
+                database : "db-from-json",
+                version : 1,
+                encrypted : false,
+                mode : "partial",
+                tables :[
+                  {
+                    name: "messages",
+
+                    indexes: [
+                      {name: "index_messages_on_title",value: "title"},
+                      {name: "index_messages_on_last_modified",value: "last_modified DESC"}
+
+                    ],
+                    values: [
+                        [3,"test post 3","content test post 3",1590396146],
+                        [4,"test post 4","content test post 4",1590396288]
+                    ]
+                  }
+                ]
+              };
+              result = await jeepSqlite.importFromJson({jsonstring: JSON.stringify(partialImport2)});
+              if(result.changes.changes === -1 ) return Promise.reject(new Error("ImportFromJson 'partial2' dataToImport failed"));
+
+              const partialImport3 = {
+                database : "db-from-json",
+                version : 1,
+                encrypted : false,
+                mode : "partial",
+                tables :[
+                  {
+                    name: "test113",
+                    schema: [
+                      {column:"id", value: "TEXT PRIMARY KEY NOT NULL"},
+                      {column:"name", value:"TEXT UNIQUE NOT NULL"},
+                      {column:"code", value:"TEXT"},
+                      {column:"last_modified", value:"INTEGER"}
+                    ],
+                    indexes: [
+                      {name: "index_test113_on_title",value: "name"},
+                      {name: "index_test113_on_last_modified",value: "last_modified DESC"}
+
+                    ],
+                    values: [
+                        ["ef5c57d5-b885-49a9-9c4d-8b340e4abdbc","valve","BV50",1590396146],
+                        ["bced3262-5d42-470a-9585-d3fd12c45452","pipe","PIPE100",1590396288],
+                        ["ef5c57d5-b885-49a9-9c4d-8b340e4abdbc","valve","BV100",1590396300],
+                    ]
+                  }
+                ]
+              };
+              result = await jeepSqlite.importFromJson({jsonstring: JSON.stringify(partialImport3)});
+              if(result.changes.changes === -1 ) return Promise.reject(new Error("ImportFromJson 'partial3' dataToImport failed"));
+
+              // create the connection to the database
+              await jeepSqlite.createConnection({database:"db-from-json", version: 1});
+              // open db testNew
+              await jeepSqlite.open({database: "db-from-json"});
+              // Select all users
+              ret = await jeepSqlite.query({database: "db-from-json",
+                                            statement: "SELECT * FROM users;"});
+              if(ret.values.length !== 6 ||
+                    ret.values[0].name !== "Whiteley" ||
+                    ret.values[1].name !== "Jones" ||
+                    ret.values[2].name !== "Simpson" ||
+                    ret.values[3].name !== "Brown" ||
+                    ret.values[4].name !== "Addington" ||
+                    ret.values[5].name !== "Bannister" ) {
+                return Promise.reject(new Error("Query 2 db-from-json Users failed"));
+              }
+
+              // Select all messages
+              ret = await jeepSqlite.query({database: "db-from-json",
+                                            statement: "SELECT * FROM messages;"});
+              if(ret.values.length !== 4 ||
+                    ret.values[0].title !== "test post 1" ||
+                    ret.values[1].title !== "test post 2" ||
+                    ret.values[2].title !== "test post 3" ||
+                    ret.values[3].title !== "test post 4" ) {
+                return Promise.reject(new Error("Query 3 db-from-json Messages failed"));
+              }
+
+              // *** test Export to Json
+              // test full export
+              let jsonObj = await jeepSqlite.exportToJson({database: "db-from-json",jsonexportmode: 'full'});
+              // test Json object validity
+              result = await jeepSqlite.isJsonValid({jsonstring: JSON.stringify(jsonObj.export)});
+              if(!result.result) {
+                return Promise.reject(new Error("IsJsonValid 'full' export failed"));
+              }
+              //test partial export
+              syncDate = "2020-05-20T18:40:00.000Z";
+              await jeepSqlite.setSyncDate({database: "db-from-json", syncdate: syncDate});
+              jsonObj = await jeepSqlite.exportToJson({database: "db-from-json",jsonexportmode: 'partial'});
+              // test Json object validity
+              result = await jeepSqlite.isJsonValid({jsonstring: JSON.stringify(jsonObj.export)});
+              if(!result.result) {
+                return Promise.reject(new Error("IsJsonValid 'partial' export failed"));
+              }
+              if(jsonObj.export.tables.length != 3 || jsonObj.export.tables[0].name != 'users'
+                  || jsonObj.export.tables[1].name != 'messages'
+                  || jsonObj.export.tables[2].name != 'test113'
+                  || jsonObj.export.tables[0].values.length != 4
+                  || jsonObj.export.tables[1].values.length != 3
+                  || jsonObj.export.tables[2].values.length != 2) {
+                return Promise.reject(new Error("IsJsonValid 'partial' export failed: No 4 tables"));
+              }
+
               await jeepSqlite.closeConnection({database:"testNew"});
               await jeepSqlite.closeConnection({database:"testSet"});
+              await jeepSqlite.closeConnection({database:"db-from-json"});
+
+              // *** test copy from asset ***
+
+              await jeepSqlite.copyFromAssets();
+              // create connection to myDB
+              await jeepSqlite.createConnection({database:"myDB",version: 1});
+              // open db myDB
+              await jeepSqlite.open({database: "myDB"});
+              let statement = `SELECT name FROM sqlite_master WHERE type='table';`;
+              // Select all tables
+              ret = await jeepSqlite.query({database: "myDB",
+                                            statement: statement});
+              if(ret.values.length !== 3 ||
+                  ret.values[0].name !== "users" ||
+                  ret.values[1].name !== "messages" ||
+                  ret.values[2].name !== "sync_table"
+              ) {
+                throw new Error("Query MyDB Tables failed");
+              }
+
+              // Select all users
+              ret = await jeepSqlite.query({database: "myDB",
+                                            statement: "SELECT * FROM users;"});
+              if(ret.values.length != 7 ||
+                  ret.values[0].name !== "Whiteley" ||
+                  ret.values[1].name !== "Jones" ||
+                  ret.values[2].name !== "Simpson" ||
+                  ret.values[3].name !== "Brown" ||
+                  ret.values[4].name !== "Jackson" ||
+                  ret.values[5].name !== "Kennedy" ||
+                  ret.values[6].name !== "Bush"
+              ) {
+                throw new Error("Query MyDB Users failed");
+              }
+              await jeepSqlite.closeConnection({database:"myDB"});
+              // create connection to dbForCopy
+              await jeepSqlite.createConnection({database:"dbForCopy",version: 1});
+              // open db myDB
+              await jeepSqlite.open({database: "dbForCopy"});
+              // Select all users
+              ret = await jeepSqlite.query({database: "dbForCopy",
+                                            statement: "SELECT * FROM areas;"});
+              if(ret.values.length != 3 ||
+                  ret.values[0].name !== "Access road" ||
+                  ret.values[1].name !== "Accessway" ||
+                  ret.values[2].name !== "Air handling system"              ) {
+                throw new Error("Query dbForCopy Areas failed");
+              }
+              await jeepSqlite.closeConnection({database:"dbForCopy"});
+
+              // *** test upgrade version ***
+              // create database version 1
+              await jeepSqlite.createConnection({
+                      database:"test-updversion",
+                      version: 1
+              });
+              // delete the database if exists (multiple runs)
+              ret = await jeepSqlite.isDBExists({database:"test-updversion"});
+              if (ret.result) {
+                await jeepSqlite.deleteDatabase({database:"test-updversion"});
+              }
+              // open db test-updversion
+              await jeepSqlite.open({database: "test-updversion"});
+              const createSchemaVersion1 = `
+                CREATE TABLE IF NOT EXISTS users (
+                  id INTEGER PRIMARY KEY NOT NULL,
+                  email TEXT UNIQUE NOT NULL,
+                  name TEXT,
+                  company TEXT,
+                  size REAL,
+                  age INTEGER,
+                  last_modified INTEGER DEFAULT (strftime('%s', 'now'))
+                );
+                CREATE INDEX IF NOT EXISTS users_index_name ON users (name);
+                CREATE INDEX IF NOT EXISTS users_index_last_modified ON users (last_modified);
+                CREATE TRIGGER IF NOT EXISTS users_trigger_last_modified
+                  AFTER UPDATE ON users
+                  FOR EACH ROW WHEN NEW.last_modified <= OLD.last_modified
+                  BEGIN
+                      UPDATE users SET last_modified= (strftime('%s', 'now')) WHERE id=OLD.id;
+                  END;
+              `;
+              // Create test-updversion schema
+              ret = await jeepSqlite.execute({database: "test-updversion", statements: createSchemaVersion1});
+              if (ret.changes.changes < 0) {
+                throw new Error("Execute createSchemaVersion1 failed");
+              }
+              // Insert some Users
+              const rowU = [["Whiteley","Whiteley.com",30.5],["Jones","Jones.com",44]];
+              const twoUsersU = `
+                DELETE FROM users;
+                INSERT INTO users (name,email,age) VALUES ("${rowU[0][0]}","${rowU[0][1]}",${rowU[0][2]});
+                INSERT INTO users (name,email,age) VALUES ("${rowU[1][0]}","${rowU[1][1]}",${rowU[1][2]});
+              `;
+              ret = await jeepSqlite.execute({database: "test-updversion", statements: twoUsersU});
+              if (ret.changes.changes !== 2) {
+                throw new Error("Execute twoUsers failed");
+              }
+              // Select all users
+              ret = await jeepSqlite.query({database: "test-updversion",
+                                            statement: "SELECT * FROM users;"});
+              if(ret.values.length !== 2 ||
+                    ret.values[0].name !== "Whiteley" ||
+                    ret.values[1].name !== "Jones") {
+                throw new Error("Query 2 Users failed");
+              }
+              await jeepSqlite.closeConnection({database:"test-updversion"});
+              // create version 2 of test-updversion
+              const createSchemaVersion2 = `
+                CREATE TABLE users (
+                  id INTEGER PRIMARY KEY NOT NULL,
+                  email TEXT UNIQUE NOT NULL,
+                  name TEXT,
+                  company TEXT,
+                  country TEXT,
+                  age INTEGER,
+                  last_modified INTEGER DEFAULT (strftime('%s', 'now'))
+                );
+                CREATE TABLE messages (
+                  id INTEGER PRIMARY KEY NOT NULL,
+                  userid INTEGER,
+                  title TEXT NOT NULL,
+                  body TEXT NOT NULL,
+                  last_modified INTEGER DEFAULT (strftime('%s', 'now')),
+                  FOREIGN KEY (userid) REFERENCES users(id) ON DELETE SET DEFAULT
+                );
+                CREATE INDEX users_index_name ON users (name);
+                CREATE INDEX users_index_last_modified ON users (last_modified);
+                CREATE INDEX messages_index_title ON messages (title);
+                CREATE INDEX messages_index_last_modified ON messages (last_modified);
+                CREATE TRIGGER users_trigger_last_modified
+                  AFTER UPDATE ON users
+                  FOR EACH ROW WHEN NEW.last_modified <= OLD.last_modified
+                  BEGIN
+                      UPDATE users SET last_modified= (strftime('%s', 'now')) WHERE id=OLD.id;
+                  END;
+                CREATE TRIGGER messages_trigger_last_modified
+                  AFTER UPDATE ON messages
+                  FOR EACH ROW WHEN NEW.last_modified <= OLD.last_modified
+                  BEGIN
+                      UPDATE messages SET last_modified= (strftime('%s', 'now')) WHERE id=OLD.id;
+                  END;
+              `;
+              setArrayVersion2 = [
+                { statement:"INSERT INTO messages (userid,title,body) VALUES (?,?,?);",
+                  values:[
+                    [1,"test message 1","content test message 1"],
+                    [2,"test message 2","content test message 2"],
+                    [1,"test message 3","content test message 3"]
+                  ]
+                },
+                { statement:"UPDATE users SET country = ?  WHERE id = ?;",
+                  values:["United Kingdom",1]
+                },
+                { statement:"UPDATE users SET country = ?  WHERE id = ?;",
+                  values:["Australia",2]
+                },
+
+              ];
+              await jeepSqlite.addUpgradeStatement({database: "test-updversion",
+                upgrade: [{fromVersion: 1, toVersion: 2, statement: createSchemaVersion2,
+                           set: setArrayVersion2}]
+                });
+
+              await jeepSqlite.createConnection({
+                      database:"test-updversion",
+                      version: 2
+              });
+              await jeepSqlite.open({database: "test-updversion"});
+              // select all user's country in db
+              ret = await jeepSqlite.query({database: "test-updversion",
+                                            statement: "SELECT country FROM users;"});
+              if(ret.values.length !== 2 ||
+                    ret.values[0].country !== "United Kingdom" ||
+                    ret.values[1].country !== "Australia") {
+                throw new Error("Query Version 2 Users failed");
+              }
+              // select all messages for user 1
+              const userMessages = `
+                SELECT users.name,messages.title,messages.body FROM users
+                INNER JOIN messages ON users.id = messages.userid
+                WHERE users.id = ?;
+              `;
+              ret = await jeepSqlite.query({database: "test-updversion",
+                                            statement: userMessages,
+                                            values : [1]});
+              if(ret.values.length !== 2 ||
+                  ret.values[0].name !== "Whiteley" ||
+                  ret.values[0].title !== "test message 1" ||
+                  ret.values[1].name !== "Whiteley" ||
+                  ret.values[1].title !== "test message 3") {
+                return Promise.reject(new Error("Query Messages User 1 Version 2 failed"));
+              }
+              // select all messages for user 2
+              ret = await jeepSqlite.query({database: "test-updversion",
+                                            statement: userMessages,
+                                            values : [2]});
+              if(ret.values.length !== 1 ||
+                  ret.values[0].name !== "Jones" ||
+                  ret.values[0].title !== "test message 2") {
+                return Promise.reject(new Error("Query Messages User 2 Version 2 failed"));
+              }
+              // close the connection test-updversion
+              await jeepSqlite.closeConnection({database:"test-updversion"});
+
               console.log("db success");
@@ -257,0 +663,0 @@ } catch (err) {

New alerts

License Policy Violation

License

This package is not allowed per your license policy. Review the package's license to ensure compliance.

Found 1 instance in 1 package

Fixed alerts

License Policy Violation

License

This package is not allowed per your license policy. Review the package's license to ensure compliance.

Found 1 instance in 1 package

Improved metrics

Total package byte prevSize

1449917

increased by6.06%

Number of package files

59

increased by3.51%

Lines of code

30704

increased by5.9%

Number of lines in readme file

658

increased by161.11%

No dependency changes