Hi all,
It’s been long time since my last post, I got a bit busy with work and other stuff :D. I’m going to write a new solution for iOS database limit, I solve it last year but don’t have time to post it.
To make it short, This is the plugin that I use to solve all the limitation problem, and gaining the speed. I use SQLitePlugin by davibe, many thanks to him.
just in-case you can’t find the js file from the plugin:
file name ‘pgsqlite_plugin.js’
(function() {
var callbacks, cbref, counter, getOptions, root, Cordova = cordova;
root = this;
callbacks = {};
counter = 0;
cbref = function(hash) {
var f;
f = "cb" + (counter += 1);
callbacks[f] = hash;
return f;
};
getOptions = function(opts, success, error) {
var cb, has_cbs;
cb = {};
has_cbs = false;
if (typeof success === "function") {
has_cbs = true;
cb.success = success;
}
if (typeof error === "function") {
has_cbs = true;
cb.error = error;
}
if (has_cbs) opts.callback = cbref(cb);
return opts;
};
root.PGSQLitePlugin = (function() {
PGSQLitePlugin.prototype.openDBs = {};
function PGSQLitePlugin(dbPath, openSuccess, openError) {
this.dbPath = dbPath;
this.openSuccess = openSuccess;
this.openError = openError;
if (!dbPath) {
throw new Error("Cannot create a PGSQLitePlugin instance without a dbPath");
}
this.openSuccess || (this.openSuccess = function() {
console.log("DB opened: " + dbPath);
});
this.openError || (this.openError = function(e) {
console.log(e.message);
});
this.open(this.openSuccess, this.openError);
}
PGSQLitePlugin.handleCallback = function(ref, type, obj) {
var _ref;
if ((_ref = callbacks[ref]) != null) {
if (typeof _ref[type] === "function") _ref[type](obj);
}
callbacks[ref] = null;
delete callbacks[ref];
};
PGSQLitePlugin.prototype.executeSql = function(sql, success, error) {
var opts;
if (!sql) throw new Error("Cannot executeSql without a query");
opts = getOptions({
query: [].concat(sql || []),
path: this.dbPath
}, success, error);
Cordova.exec("PGSQLitePlugin.backgroundExecuteSql", opts);
};
PGSQLitePlugin.prototype.transaction = function(fn, success, error) {
var t;
t = new root.PGSQLitePluginTransaction(this.dbPath);
fn(t);
return t.complete(success, error);
};
PGSQLitePlugin.prototype.open = function(success, error) {
var opts;
if (!(this.dbPath in this.openDBs)) {
this.openDBs[this.dbPath] = true;
opts = getOptions({
path: this.dbPath
}, success, error);
Cordova.exec("PGSQLitePlugin.open", opts);
}
};
PGSQLitePlugin.prototype.close = function(success, error) {
var opts;
if (this.dbPath in this.openDBs) {
delete this.openDBs[this.dbPath];
opts = getOptions({
path: this.dbPath
}, success, error);
Cordova.exec("PGSQLitePlugin.close", opts);
}
};
return PGSQLitePlugin;
})();
root.PGSQLitePluginTransaction = (function() {
function PGSQLitePluginTransaction(dbPath) {
this.dbPath = dbPath;
this.executes = [];
}
PGSQLitePluginTransaction.prototype.executeSql = function(sql, success, error) {
this.executes.push(getOptions({
query: [].concat(sql || []),
path: this.dbPath
}, success, error));
};
PGSQLitePluginTransaction.prototype.complete = function(success, error) {
var begin_opts, commit_opts, executes, opts;
if (this.__completed) throw new Error("Transaction already run");
this.__completed = true;
begin_opts = getOptions({
query: ["BEGIN;"],
path: this.dbPath
});
commit_opts = getOptions({
query: ["COMMIT;"],
path: this.dbPath
}, success, error);
executes = [begin_opts].concat(this.executes).concat([commit_opts]);
opts = {
executes: executes
};
Cordova.exec("PGSQLitePlugin.backgroundExecuteSqlBatch", opts);
this.executes = [];
};
return PGSQLitePluginTransaction;
})();
}).call(this);
You need to add libsqlite3.dylib to your project, go to ‘Build Phases’ > ‘Link Binary with Libraries’ > Add and type in the library name.
And then I wrote, I mean modified my SQLite Class for HTML 5 Database to connect to database, so I won’t get alot of changes on my code to use it, and this is the code:
// JavaScript Document
function cDB(confs){
var ret = {
_db: null,
_response: null,
_error: null,
check : function(tbl,callback){
if(!this._db) return false;
var _sql = '', _sqlField='', _field=[];
for(var i=0;i<tbl.length;i++){
_sql = "CREATE TABLE IF NOT EXISTS "+tbl[i].table+" (";
_field = tbl[i].properties;
_sqlField = '';
for (var j=0;j<_field.length;j++){
_sqlField += ',`'+_field[j].name+'` '+_field[j].type;
}
_sql += _sqlField.substr(1)+");";
this.query(_sql,callback,null,null);
}
return true;
},
__: function(s){
return s;
},
getResult:function(){
return this._response;
},
getError:function(){
return this._error;
},
callback_error: function(tx,_er){
var err = '';
if(typeof(tx) == 'object'){
for(var q in tx){
err += q+' = "'+tx[q]+'"; ';
}
}else{
err += tx+'; ';
}
if(typeof(_er) == 'object'){
for(var q in _er){
err += q+' = "'+_er[q]+'"; ';
}
}else if(typeof(_er) == 'undefined'){
err += _er+'; ';
}
echo(err);
//if(callback) callback();
return false;
},
query: function(sql,callback,params,er){
if(!this._db) return false;
var self = this;
function _genErrorCallback(sql){
return function(tx,__er){
__er = jQuery.extend(__er,{sql:sql});
if(er) er(tx,__er);
else self.callback_error(tx,__er);
}
};
var _query = [];
if(params)
_query = params;
_query.splice(0,0,sql);
this._db.transaction(function(tx){
tx.executeSql(_query,function(res){
//echo('finish execute sql ' + _query , 300);
callback(this,res);
},self.callback_error);
},function(){return false;}, _genErrorCallback(_query));
},
update:function(tbl,sets,clauses,callback){
var __sql = 'UPDATE '+tbl, _field = null, __set = '', __clause = '',__values=[];
for(var i=0;i<sets.length;i++){0
_field = sets[i];
for(var j=0;j<_field.length;j++){
__set += ',`'+_field[j].name+'`=?';
__values.push(_field[j].value);
}
}
for(var i=0;i<clauses.length;i++){
__clause += ',`'+clauses[i].name+'`=?';
__values.push(clauses[i].value);
}
__sql += ((__set!='')?' SET '+__set.substr(1):'')+((__clause!='')?' WHERE '+__clause.substr(1):'')+';';
this.query(__sql,callback,__values);
return true;
},
remove:function(tbl, clauses, callback){
var __sql = 'DELETE FROM '+tbl, __clause = '';
if(typeof(clauses) != 'undefined'){
for(var i=0;i<clauses.length;i++)
__clause += ',`'+clauses[i].name+'`="'+escape(clauses[i].value)+'"';
echo('doing clauses');
if(clauses.length > 0)
__sql += ' WHERE ' + ((__clause!='')?__clause.substr(1):'FALSE');
}
__sql += ';';
this.query(__sql,callback);
return true;
},
multiInsert: function(tbl,rows,callback,er){
if(!this._db) return false;
var self = this;
var __sql = '', _field = null, __field = '', __qs = [], __values = [];
this._db.transaction(function(tx){
for(var i=0;i<rows.length;i++){
__qs = [];
__values = [];
__field = '';
_field = rows[i];
for(var j=0;j<_field.length;j++){
__field += ',`'+_field[j].name+'`';
__qs.push('?');
__values.push(_field[j].value ? _field[j].value : '');
}
var _sql = 'INSERT INTO '+tbl+' ('+__field.substr(1)+') VALUES('+__qs.join(',')+');';
var _query = [];
if(__values)
_query = __values;
_query.splice(0,0,_sql);
tx.executeSql(_query,function(){return false;},self.callback_error);
}
}, function(){
if(callback) callback();
return true;
}, self.callback_error);
return true;
},
insert:function(tbl,rows,callback){
var __sql = '', _field = null, __field = '', __qs = [], __values = [], __debug = '';
for(var i=0;i<rows.length;i++){
__qs = [];
__field = '';
_field = rows[i];
__debug += _field[0].name+' = '+_field[0].value+';';
for(var j=0;j<_field.length;j++){
__field += ',`'+_field[j].name+'`';
__qs.push('?');
__values.push(_field[j].value ? _field[j].value : '');
}
__sql += 'INSERT INTO '+tbl+' ('+__field.substr(1)+') VALUES('+__qs.join(',')+');';
}
this.query(__sql,callback,__values);
return true;
},
insertReplace:function(tbl,rows,debug){
var __sql = '', _field = null, __field = '', __qs = [], __values = [], __debug = '';
for(var i=0;i<rows.length;i++){
__qs = [];
__field = '';
_field = rows[i];
__debug += _field[0].name+' = '+_field[0].value+';';
for(var j=0;j<_field.length;j++){
__field += ',`'+_field[j].name+'`';
__qs.push('?');
__values.push(_field[j].value ? _field[j].value : '');
}
__sql += 'INSERT OR REPLACE INTO '+tbl+' ('+__field.substr(1)+') VALUES('+__qs.join(',')+');';
}
this.query(__sql,null,__values);
return true;
},
dropTable:function(tbl,callback){
var __sql = '';
if(tbl==null) return false;
__sql = 'DROP TABLE IF EXISTS '+tbl;
this.query(__sql,callback);
return true;
}
}
return jQuery.extend(ret,confs);
}
How To Use It
Database Constructor:
» Read the rest of this entry «