@capacitor-community/sqlite - npm Package Compare versions

Comparing version 3.0.0-beta.3 to 3.0.0-beta.4

CHANGELOG.md

@@ -0,1 +1,8 @@
+## 3.0.0-beta.4 (2021-02-01)
+
+### Bug Fixes
+
+- Fix Data import where the ID is a UUID issue#75
+- Fix Can't create my own DB Trigger issue#76
+
 ## 3.0.0-beta.3 (2021-01-30)
@@ -30,2 +37,9 @@
 
+## 2.9.8 (2021-02-01) REFACTOR
+
+### Bug Fixes
+
+- Fix Data import where the ID is a UUID issue#75
+- Fix Can't create my own DB Trigger issue#76
+
 ## 2.9.7 (2021-01-30) REFACTOR
@@ -32,0 +46,0 @@

dist/esm/definitions.d.ts

@@ -358,2 +358,6 @@ declare module '@capacitor/core' {
     /***
+     * Array of Trigger (JsonTrigger)
+     */
+    triggers?: JsonTrigger[];
+    /***
      * Array of Table data
@@ -381,2 +385,20 @@ */
 }
+export interface JsonTrigger {
+    /**
+     * The trigger name
+     */
+    name: string;
+    /**
+     * The trigger time event fired
+     */
+    timeevent: string;
+    /**
+     * The trigger condition
+     */
+    condition?: string;
+    /**
+     * The logic of the trigger
+     */
+    logic: string;
+}
 export interface JsonIndex {
@@ -383,0 +405,0 @@ /**

dist/esm/definitions.js

@@ -266,5 +266,5 @@ /**
             console.log(`in DBConnection syncDate ${res.syncDate}`);
-            let retDate = "";
+            let retDate = '';
             if (res.syncDate > 0)
-                retDate = (new Date(res.syncDate * 1000)).toISOString();
+                retDate = new Date(res.syncDate * 1000).toISOString();
             console.log(`in DBConnection retDate ${retDate}`);
@@ -271,0 +271,0 @@ return Promise.resolve(retDate);

dist/plugin.cjs.js

@@ -272,5 +272,5 @@ 'use strict';
             console.log(`in DBConnection syncDate ${res.syncDate}`);
-            let retDate = "";
+            let retDate = '';
             if (res.syncDate > 0)
-                retDate = (new Date(res.syncDate * 1000)).toISOString();
+                retDate = new Date(res.syncDate * 1000).toISOString();
             console.log(`in DBConnection retDate ${retDate}`);
@@ -277,0 +277,0 @@ return Promise.resolve(retDate);

dist/plugin.js

@@ -269,5 +269,5 @@ var capacitorCapacitorSQLite = (function (exports, core) {
                 console.log(`in DBConnection syncDate ${res.syncDate}`);
-                let retDate = "";
+                let retDate = '';
                 if (res.syncDate > 0)
-                    retDate = (new Date(res.syncDate * 1000)).toISOString();
+                    retDate = new Date(res.syncDate * 1000).toISOString();
                 console.log(`in DBConnection retDate ${retDate}`);
@@ -274,0 +274,0 @@ return Promise.resolve(retDate);

electron/dist/esm/electron/src/electron-utils/ImportExportJson/exportToJson.d.ts

@@ -42,2 +42,9 @@ import type { JsonSQLite } from '../../definitions';
     /**
+     * GetTriggers
+     * @param mDb
+     * @param sqlStmt
+     * @param tableName
+     */
+    private getTriggers;
+    /**
      * GetValues
@@ -44,0 +51,0 @@ * @param mDb

electron/dist/esm/electron/src/electron-utils/ImportExportJson/exportToJson.js

@@ -151,2 +151,8 @@ import { __awaiter } from "tslib";
                     }
+                    // create Table's triggers if any
+                    const triggers = yield this.getTriggers(mDb, tableName);
+                    if (triggers.length > 0) {
+                        // check triggers validity
+                        yield this._uJson.checkTriggersValidity(triggers);
+                    }
                     // create Table's Data
@@ -166,2 +172,5 @@ const query = `SELECT * FROM ${tableName};`;
                     }
+                    if (triggers.length > 0) {
+                        table.triggers = triggers;
+                    }
                     if (values.length > 0) {
@@ -299,2 +308,72 @@ table.values = values;
     /**
+     * GetTriggers
+     * @param mDb
+     * @param sqlStmt
+     * @param tableName
+     */
+    getTriggers(mDb, tableName) {
+        return __awaiter(this, void 0, void 0, function* () {
+            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 = yield this._uSQLite.queryAll(mDb, 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');
+                                    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 {
+                                return Promise.reject(new Error(`GetTriggers: Table ${tableName} doesn't match`));
+                            }
+                        }
+                        else {
+                            return Promise.reject(new Error(`GetTriggers: Table ${tableName} creating indexes`));
+                        }
+                    }
+                }
+                return Promise.resolve(triggers);
+            }
+            catch (err) {
+                return Promise.reject(new Error(`GetTriggers: ${err.message}`));
+            }
+        });
+    }
+    /**
      * GetValues
@@ -386,2 +465,3 @@ * @param mDb
                     let indexes = [];
+                    let triggers = [];
                     table.name = rTable;
@@ -401,2 +481,8 @@ if (modTables[table.name] === 'Create') {
                         }
+                        // create Table's triggers if any
+                        triggers = yield this.getTriggers(mDb, tableName);
+                        if (triggers.length > 0) {
+                            // check triggers validity
+                            yield this._uJson.checkTriggersValidity(triggers);
+                        }
                     }
@@ -422,2 +508,5 @@ // create Table's Data
                     }
+                    if (triggers.length > 0) {
+                        table.triggers = triggers;
+                    }
                     if (values.length > 0) {
@@ -424,0 +513,0 @@ table.values = values;

electron/dist/esm/electron/src/electron-utils/ImportExportJson/utilsJson.d.ts

@@ -1,2 +0,2 @@
-import type { JsonColumn, JsonIndex } from '../../definitions';
+import type { JsonColumn, JsonIndex, JsonTrigger } from '../../definitions';
 export declare class UtilsJson {
@@ -86,2 +86,7 @@ private _uSQLite;
     /**
+     * isTriggers
+     * @param obj
+     */
+    private isTriggers;
+    /**
      * checkSchemaValidity
@@ -96,2 +101,7 @@ * @param schema
     checkIndexesValidity(indexes: JsonIndex[]): Promise<void>;
+    /**
+     * checkTriggersValidity
+     * @param triggers
+     */
+    checkTriggersValidity(triggers: JsonTrigger[]): Promise<void>;
 }

electron/dist/esm/electron/src/electron-utils/ImportExportJson/utilsJson.js

@@ -141,3 +141,3 @@ import { __awaiter } from "tslib";
                             const tableName = jTable.name;
-                            let stmt = `CREATE ${Object.keys(jIndex).includes('mode') ? jIndex.mode + ' ' : ''}INDEX `;
+                            let stmt = `CREATE ${Object.keys(jIndex).includes('mode') ? jIndex.mode + ' ' : ''}INDEX IF NOT EXISTS `;
                             stmt += `${jIndex.name} ON ${tableName} (${jIndex.value});`;
@@ -147,2 +147,13 @@ statements.push(stmt);
                     }
+                    if (jTable.triggers != null && jTable.triggers.length >= 1) {
+                        for (const jTrg of jTable.triggers) {
+                            const tableName = jTable.name;
+                            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);
+                        }
+                    }
                 }
@@ -306,4 +317,8 @@ return Promise.resolve(statements);
             let ret = false;
-            const query = `SELECT ${firstColumnName} FROM ` +
-                `${dbName} WHERE ${firstColumnName} = ${key};`;
+            let query = `SELECT ${firstColumnName} FROM ` +
+                `${dbName} WHERE ${firstColumnName} = `;
+            if (typeof key === 'number')
+                query += `${key};`;
+            if (typeof key === 'string')
+                query += `'${key}';`;
             try {
@@ -401,3 +416,9 @@ const resQuery = yield this._uSQLite.queryAll(db, query, []);
     isTable(obj) {
-        const keyTableLevel = ['name', 'schema', 'indexes', 'values'];
+        const keyTableLevel = [
+            'name',
+            'schema',
+            'indexes',
+            'triggers',
+            'values',
+        ];
         let nbColumn = 0;
@@ -416,2 +437,4 @@ if (obj == null ||
                 return false;
+            if (key === 'triggers' && typeof obj[key] != 'object')
+                return false;
             if (key === 'values' && typeof obj[key] != 'object')
@@ -438,2 +461,9 @@ return false;
             }
+            if (key === 'triggers') {
+                for (const oKey of obj[key]) {
+                    const retTriggers = this.isTriggers(oKey);
+                    if (!retTriggers)
+                        return false;
+                }
+            }
             if (key === 'values') {
@@ -501,2 +531,30 @@ if (nbColumn > 0) {
     /**
+     * isTriggers
+     * @param obj
+     */
+    isTriggers(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)
+                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;
+    }
+    /**
      * checkSchemaValidity
@@ -556,3 +614,32 @@ * @param schema
     }
+    /**
+     * checkTriggersValidity
+     * @param triggers
+     */
+    checkTriggersValidity(triggers) {
+        return __awaiter(this, void 0, void 0, function* () {
+            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 = this.isTriggers(trigger);
+                if (!isValid) {
+                    return Promise.reject(new Error(`CheckTriggersValidity: triggers[${i}] not valid`));
+                }
+            }
+            return Promise.resolve();
+        });
+    }
 }
 //# sourceMappingURL=utilsJson.js.map

electron/dist/esm/src/definitions.d.ts

@@ -358,2 +358,6 @@ declare module '@capacitor/core' {
     /***
+     * Array of Trigger (JsonTrigger)
+     */
+    triggers?: JsonTrigger[];
+    /***
      * Array of Table data
@@ -381,2 +385,20 @@ */
 }
+export interface JsonTrigger {
+    /**
+     * The trigger name
+     */
+    name: string;
+    /**
+     * The trigger time event fired
+     */
+    timeevent: string;
+    /**
+     * The trigger condition
+     */
+    condition?: string;
+    /**
+     * The logic of the trigger
+     */
+    logic: string;
+}
 export interface JsonIndex {
@@ -383,0 +405,0 @@ /**

electron/dist/esm/src/definitions.js

@@ -308,5 +308,5 @@ import { __awaiter } from "tslib";
                 console.log(`in DBConnection syncDate ${res.syncDate}`);
-                let retDate = "";
+                let retDate = '';
                 if (res.syncDate > 0)
-                    retDate = (new Date(res.syncDate * 1000)).toISOString();
+                    retDate = new Date(res.syncDate * 1000).toISOString();
                 console.log(`in DBConnection retDate ${retDate}`);
@@ -313,0 +313,0 @@ return Promise.resolve(retDate);

electron/src/electron-utils/ImportExportJson/exportToJson.ts

@@ -6,2 +6,3 @@ import type {
   JsonIndex,
+  JsonTrigger,
 } from '../../definitions';
@@ -154,2 +155,8 @@ import { UtilsSQLite } from '../utilsSQLite';
         }
+        // create Table's triggers if any
+        const triggers: JsonTrigger[] = await this.getTriggers(mDb, tableName);
+        if (triggers.length > 0) {
+          // check triggers validity
+          await this._uJson.checkTriggersValidity(triggers);
+        }
         // create Table's Data
@@ -168,2 +175,5 @@ const query = `SELECT * FROM ${tableName};`;
         }
+        if (triggers.length > 0) {
+          table.triggers = triggers;
+        }
         if (values.length > 0) {
@@ -294,2 +304,90 @@ table.values = values;
   /**
+   * GetTriggers
+   * @param mDb
+   * @param sqlStmt
+   * @param tableName
+   */
+  private async getTriggers(
+    mDb: any,
+    tableName: string,
+  ): Promise<JsonTrigger[]> {
+    const triggers: JsonTrigger[] = [];
+    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 this._uSQLite.queryAll(mDb, stmt, []);
+      if (retTriggers.length > 0) {
+        for (const rTrg of retTriggers) {
+          const keys: string[] = Object.keys(rTrg);
+          if (keys.length === 3) {
+            if (rTrg['tbl_name'] === tableName) {
+              const sql: string = rTrg['sql'];
+
+              const name: string = rTrg['name'];
+              let sqlArr: string[] = 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');
+                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: JsonTrigger = {} as JsonTrigger;
+              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`),
+              );
+            }
+          } else {
+            return Promise.reject(
+              new Error(`GetTriggers: Table ${tableName} creating indexes`),
+            );
+          }
+        }
+      }
+      return Promise.resolve(triggers);
+    } catch (err) {
+      return Promise.reject(new Error(`GetTriggers: ${err.message}`));
+    }
+  }
+  /**
    * GetValues
@@ -391,2 +489,3 @@ * @param mDb
         let indexes: JsonIndex[] = [];
+        let triggers: JsonTrigger[] = [];
         table.name = rTable;
@@ -406,2 +505,8 @@ if (modTables[table.name] === 'Create') {
           }
+          // create Table's triggers if any
+          triggers = await this.getTriggers(mDb, tableName);
+          if (triggers.length > 0) {
+            // check triggers validity
+            await this._uJson.checkTriggersValidity(triggers);
+          }
         }
@@ -427,2 +532,5 @@ // create Table's Data
         }
+        if (triggers.length > 0) {
+          table.triggers = triggers;
+        }
         if (values.length > 0) {
@@ -429,0 +537,0 @@ table.values = values;

electron/src/electron-utils/ImportExportJson/utilsJson.ts

@@ -1,2 +0,2 @@
-import type { JsonColumn, JsonIndex } from '../../definitions';
+import type { JsonColumn, JsonIndex, JsonTrigger } from '../../definitions';
 import { UtilsSQLite } from '../utilsSQLite';
@@ -149,3 +149,3 @@
               Object.keys(jIndex).includes('mode') ? jIndex.mode + ' ' : ''
-            }INDEX `;
+            }INDEX IF NOT EXISTS `;
             stmt += `${jIndex.name} ON ${tableName} (${jIndex.value});`;
@@ -155,2 +155,12 @@ statements.push(stmt);
         }
+        if (jTable.triggers != null && jTable.triggers.length >= 1) {
+          for (const jTrg of jTable.triggers) {
+            const tableName = jTable.name;
+            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);
+          }
+        }
       }
@@ -349,5 +359,8 @@ return Promise.resolve(statements);
     let ret = false;
-    const query: string =
+    let query: string =
       `SELECT ${firstColumnName} FROM ` +
-      `${dbName} WHERE ${firstColumnName} = ${key};`;
+      `${dbName} WHERE ${firstColumnName} = `;
+    if (typeof key === 'number') query += `${key};`;
+    if (typeof key === 'string') query += `'${key}';`;
+
     try {
@@ -437,3 +450,9 @@ const resQuery: any[] = await this._uSQLite.queryAll(db, query, []);
   private isTable(obj: any): boolean {
-    const keyTableLevel: string[] = ['name', 'schema', 'indexes', 'values'];
+    const keyTableLevel: string[] = [
+      'name',
+      'schema',
+      'indexes',
+      'triggers',
+      'values',
+    ];
     let nbColumn = 0;
@@ -450,2 +469,3 @@ if (
       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;
@@ -475,2 +495,8 @@ if (key === 'schema') {
       }
+      if (key === 'triggers') {
+        for (const oKey of obj[key]) {
+          const retTriggers: boolean = this.isTriggers(oKey);
+          if (!retTriggers) return false;
+        }
+      }
       if (key === 'values') {
@@ -538,2 +564,28 @@ if (nbColumn > 0) {
   /**
+   * isTriggers
+   * @param obj
+   */
+  private isTriggers(obj: any): boolean {
+    const keyTriggersLevel: string[] = [
+      '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) 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;
+  }
+
+  /**
    * checkSchemaValidity
@@ -594,2 +646,32 @@ * @param schema
   }
+  /**
+   * checkTriggersValidity
+   * @param triggers
+   */
+  public async checkTriggersValidity(triggers: JsonTrigger[]): Promise<void> {
+    for (let i = 0; i < triggers.length; i++) {
+      const trigger: JsonTrigger = {} as JsonTrigger;
+      const keys: string[] = 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: boolean = this.isTriggers(trigger);
+      if (!isValid) {
+        return Promise.reject(
+          new Error(`CheckTriggersValidity: triggers[${i}] not valid`),
+        );
+      }
+    }
+    return Promise.resolve();
+  }
 }

package.json

 {
   "name": "@capacitor-community/sqlite",
-  "version": "3.0.0-beta.3",
+  "version": "3.0.0-beta.4",
   "description": "Community plugin for native & electron SQLite databases",
@@ -5,0 +5,0 @@ "main": "dist/plugin.cjs.js",

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

1477495

increased by4.7%

Number of package files

116

increased by0.87%

Lines of code

15289

increased by4.07%

No dependency changes