uk.co.workingedge.cordova.plugin.sqliteporter - npm Package Compare versions

Comparing version 0.0.7 to 0.0.8

package.json

 {
-    "version": "0.0.7",
+    "version": "0.0.8",
     "name": "uk.co.workingedge.cordova.plugin.sqliteporter",
@@ -4,0 +4,0 @@ "cordova_name": "sqlite porter",

README.md

 SQLite Porter Cordova/Phonegap Plugin
-=====================================
+=================================
 
-**Git repo: [https://github.com/dpa99c/cordova-sqlite-porter](https://github.com/dpa99c/cordova-sqlite-porter)**
-
 ## Contents
@@ -343,5 +341,6 @@
 
-## Batch inserts
+## Batched inserts
 
-Using UNION SELECT syntax (see [this stackoverflow post](http://stackoverflow.com/a/5009740/777265) for details), INSERTS are grouped by up to 500 in a single SQL statement.
+Using UNION SELECT syntax (see [this stackoverflow post](http://stackoverflow.com/a/5009740/777265) for details),
+so if the JSON structure contains "inserts", they are grouped by up to 500 in a single SQL statement.
 This leads to significant performance gains when bulk importing data as to populate a database
@@ -356,2 +355,7 @@
 
+## Delayed index creation
+
+If the JSON structure "otherSql" key contains CREATE INDEX statements, these are executed after all other SQL commands, and in a separate transaction
+in order to optimise performance when inserting large amounts of data.
+
 # Example projects
@@ -376,3 +380,3 @@
 
-Copyright (c) 2015 [Working Edge Ltd.](http://www.workingedge.co.uk)
+Copyright (c) 2015 Working Edge Ltd.
 
@@ -379,0 +383,0 @@ Permission is hereby granted, free of charge, to any person obtaining a copy

www/sqlitePorter.js

@@ -335,3 +335,3 @@ /**
         opts = opts || {};
-        var sql = "";
+        var mainSql = "", createIndexSql = "";
 
@@ -344,7 +344,12 @@ try{
                 for(var tableName in json.structure.tables){
-                    sql += "DROP TABLE IF EXISTS " + tableName + separator
+                    mainSql += "DROP TABLE IF EXISTS " + tableName + separator
                     + "CREATE TABLE " + tableName + json.structure.tables[tableName] + separator;
                 }
                 for(var i=0; i<json.structure.otherSQL.length; i++){
-                    sql += json.structure.otherSQL[i] + separator;
+                    var command = json.structure.otherSQL[i];
+                    if(command.match(/CREATE INDEX/i)){
+                        createIndexSql += json.structure.otherSQL[i] + separator;
+                    }else{
+                        mainSql += json.structure.otherSQL[i] + separator;
+                    }
                 }
@@ -359,3 +364,3 @@ }
                         if(_count === 500){
-                            sql += separator;
+                            mainSql += separator;
                             _count = 1;
@@ -373,15 +378,15 @@ }
                         if(_count === 1){
-                            sql += "INSERT OR REPLACE INTO " + tableName + " SELECT";
+                            mainSql += "INSERT OR REPLACE INTO " + tableName + " SELECT";
                             for(var j=0; j<_fields.length; j++){
-                                sql += " '"+_values[j]+"' AS '"+_fields[j]+"'";
+                                mainSql += " '"+_values[j]+"' AS '"+_fields[j]+"'";
                                 if(j < _fields.length-1){
-                                    sql += ",";
+                                    mainSql += ",";
                                 }
                             }
                         }else{
-                            sql += " UNION SELECT ";
+                            mainSql += " UNION SELECT ";
                             for(var j=0; j<_values.length; j++){
-                                sql += " '"+_values[j]+"'";
+                                mainSql += " '"+_values[j]+"'";
                                 if(j < _values.length-1){
-                                    sql += ",";
+                                    mainSql += ",";
                                 }
@@ -391,3 +396,3 @@ }
                     }
-                    sql += separator;
+                    mainSql += separator;
                 }
@@ -401,8 +406,8 @@ }
                             _row = json.data.deletes[tableName][i];
-                        sql += "DELETE FROM " + tableName;
+                        mainSql += "DELETE FROM " + tableName;
                         for(var col in _row){
-                            sql += (_count === 0 ? " WHERE " : " AND ") + col + "='"+sanitiseForSql(_row[col])+"'";
+                            mainSql += (_count === 0 ? " WHERE " : " AND ") + col + "='"+sanitiseForSql(_row[col])+"'";
                             _count++;
                         }
-                        sql += separator;
+                        mainSql += separator;
                     }
@@ -417,7 +422,7 @@ }
                         var _row = json.data.updates[tableName][i];
-                        sql += "UPDATE " + tableName;
+                        mainSql += "UPDATE " + tableName;
 
                         _count = 0;
                         for(_col in _row.set){
-                            sql += (_count === 0 ? " SET " : ", ") + _col + "='" + sanitiseForSql(_row.set[_col]) + "'";
+                            mainSql += (_count === 0 ? " SET " : ", ") + _col + "='" + sanitiseForSql(_row.set[_col]) + "'";
                         }
@@ -427,6 +432,6 @@
                         for(_col in _row.where){
-                            sql += (_count === 0 ? " WHERE " : " AND ") + _col + "='" + sanitiseForSql(_row.where[_col]) + "'";
+                            mainSql += (_count === 0 ? " WHERE " : " AND ") + _col + "='" + sanitiseForSql(_row.where[_col]) + "'";
                         }
 
-                        sql += separator;
+                        mainSql += separator;
                     }
@@ -436,3 +441,24 @@ }
 
-            sqlitePorter.importSqlToDb(db, sql, opts);
+            // If creating indexes, do it in a different transaction after other SQL to optimise performance
+            if(createIndexSql){
+                sqlitePorter.importSqlToDb(db, mainSql, extend({}, opts, {
+                    successFn:function(mainTotalCount){
+                        sqlitePorter.importSqlToDb(db, createIndexSql, extend({}, opts, {
+                            successFn:function(totalCount){
+                                if(opts.successFn){
+                                    opts.successFn(mainTotalCount+totalCount);
+                                }
+                            },
+                            progressFn:function(count, totalCount){
+                                if(opts.progressFn){
+                                    opts.progressFn(mainTotalCount+count, mainTotalCount+totalCount);
+                                }
+                            }
+                        }));
+                    }
+                }));
+            }else{
+                sqlitePorter.importSqlToDb(db, mainSql, opts);
+            }
+
         }catch(e){
@@ -519,3 +545,16 @@ e.message = "Failed to parse JSON structure to SQL: "+ e.message;
 
+    /**
+     * Applies properties to the 1st object specified from the 2nd, 3rd, 4th, etc.
+     * Emulates jQuery's $.extend()
+     * @returns {object}
+     */
+    function extend(){
+        for(var i=1; i<arguments.length; i++)
+            for(var key in arguments[i])
+                if(arguments[i].hasOwnProperty(key))
+                    arguments[0][key] = arguments[i][key];
+        return arguments[0];
+    }
+
     module.exports = sqlitePorter;
 }());

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

44716

increased by4.61%

Lines of code

508

increased by7.86%

Number of lines in readme file

396

increased by1.02%

No dependency changes