I'm fairly new to both Ionic and Angular. I'm trying to write to a SQLite database and it's intermittently successful. When I do a for loop and insert records rapidly, some succeed and some fail (without apparent error). The query execution uses promises, so multiple queries may be trying to execute concurrently. It seems that this causes a synchronization issue in SQLite - or the SQLite plugin. I've tried opening a new DB connection with every execute(), reopening the existing connection on every execute(), and I've also tried opening a connection globally in app.js once and reusing that connection. They all seem to behave the same.
A custom 'dbQuery' function is used to build queries and is chainable. The idea is that any place in my app with access to the DB service can execute a query and expect the results to flow into an "out" variable like:
var my_query_results = [];
DB.begin().select("*","table1").execute(my_query_results).then(function() {
console.log("Query complete");
});
That much works, but the problem comes from writes:
var records = [
{id:1, name:"Bob"},
{id:2, name:"John"},
{id:3, name:"Jim"},
];
for (var i = 0; i < records.length; i++) {
var obj = records[i];
var result = [];
DB.begin().debug(true).insert("table1", "(id,name)", "("+obj.id+","+ obj.name+")").execute(result).then(function () {
console.log("Inserted record", JSON.stringify(obj));
});
}
Sometimes it fails without any logged or apparent error, sometimes it succeeds. If I perform the inserts slowly over time, it seems to work without issue.
app.js
var db;
angular.module('starter', ['ionic', 'starter.controllers', 'starter.services'])
.run(function ($ionicPlatform, $cordovaSQLite, appConfig, $q) {
$ionicPlatform.ready(function () {
db = $cordovaSQLite.openDB({
name: appConfig.sqlite_db,
location: appConfig.sqlite_db_location
});
dbQuery = function () {
this.bDebug = false;
this.query = "";
this.result = [];
this.params = [];
this.debug = function (value) {
this.bDebug = (value === true ? true : false);
return this;
};
this.rawQuery = function (query) {
this.query = query;
return this;
};
this.insert = function (table, fields, values) {
this.query = "INSERT INTO '" + table + "' (" + fields + ") VALUES (" + values + ")";
return this;
};
this.select = function (fields, table) {
this.query = "SELECT " + fields + " FROM " + table;
return this;
};
this.delete = function (query) {
this.query = "DELETE FROM " + query;
return this;
};
this.where = function (column, expression, value) {
expression = expression || "=";
this.query += " WHERE `" + column + "` " + expression + " ? ";
this.params[this.params.length] = value;
return this;
};
this.and = function (column, expression, value) {
expression = expression || "=";
this.query += " AND '" + column + "' " + expression + " ? ";
this.params[this.params.length] = value;
return this;
};
this.execute = function (out_var) {
var self = this;
this.result = out_var;
if (this.bDebug) {
console.log("Compiled query is", this.query);
}
var deferred = $q.defer();
db.open(function () {
console.log("Opened");
}, function () {
console.log("Failed");
});
//actually execute the query
$cordovaSQLite.execute(db, this.query, this.params).then(
function (res) {
for (var i = 0; i < res.rows.length; i++) {
self.result.push(res.rows.item(i));
console.log("Added row to set", JSON.stringify(res.rows.item(i)));
}
if (res.rows.length == 0 && self.bDebug === true) {
console.log("No results found ");
}
deferred.resolve();
}, function (err) {
console.error(JSON.stringify(err), this.query);
deferred.reject();
});
return deferred.promise;
}
services.js
.factory('DB', function ($ionicPlatform) {
return {
begin: function () {
return new dbQuery();
}
}
})
.factory('DbBootstrap', function ($cordovaSQLite, appConfig, $q, $state, DB) {
return {
wipe: function () {
DB.begin().rawQuery("DELETE FROM table1").execute().then(function () {
console.log("Purged records");
});
},
init: function () {
var result = []; //out variable
DB.begin().rawQuery("CREATE TABLE IF NOT EXISTS table1 (id integer primary key, name text)").execute(result).then(function () {
console.log("Schema create returned", JSON.stringify(result));
});
var records = [
{
id: 1, name:'Jim'
...
},
{
id: 2, name:'Bob'
...
},
{
id: 3, name:'John'
...
}
];
for (var i = 0; i < records.length; i++) {
var obj = records[i];
var result = [];
DB.begin().debug(true).insert("table1", "(id,name)", "(obj.id, obj.name).execute(result).then(function () {
console.log("Inserted record", JSON.stringify(obj));
});
}
}
})
I'm sure I'm missing something fundamental about angular, promises, and sqlite locking. If anyone has advice I'd really appreciate it.
