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:
var db = new cDB({_db:new PGSQLitePlugin("websiteDB")});
Table Creation:
/* dbTable is database structure in this example, and contains 2 tables 'foo' and 'boo' */
/* and also the table structure in table properties */
var dbTable = [
{table:'foo',properties: [
{name:'foo_id', type: 'INT PRIMARY KEY ASC'},
{name:'foo_field_1', type: ''},
{name:'foo)field_2', type: ''}
]},
{table:'boo',properties: [
{name:'boo_id', type: 'INT PRIMARY KEY ASC'},
{name:'boo_field_1', type: ''},
{name:'boo_field_2', type: ''}
]}
];
/* this line is checking if the database exist or not and then create the database structure. */
/* table will be created if the table is not exist yet, if the table already exist, it will skip the */
/* table and continue with others tables */
if(!db.check(dbTable)){
db = false;
alert('Failed to cennect to database.');
}
Drop Table:
db.dropTable('foo');
Add / Insert Record:
var row = [];
row.push([
{'name':'foo_id','value':1},
{'name':'foo_field_1','value':'value 1 field_1'},
{'name':'foo_field_2','value':'value 1 field_2']}
]);
db.insert('foo',row,function(tx,res){
console.log('Insert ID: '+res.insertId);
});
Multilines records Insertion:
var rows = [];
rows.push([
{'name':'boo_id','value':1},
{'name':'boo_field_1','value':'value 1 field_1'},
{'name':'boo_field_2','value':'value 1 field_2']}
]);
rows.push([
{'name':'boo_id','value':2},
{'name':'boo_field_1','value':'value 2 field_1'},
{'name':'boo_field_2','value':'value 2 field_2']}
]);
db.multiInsert('boo',rows,function(){alert('insertion done');});
Or, if you want to combine that two functions, just put if condition:
if(rows.length>=2){
db.multiInsert('boo',rows,function(){alert('insertion done');});
}else{
db.insert('boo',rows);
}
Delete record:
db.remove('boo',[{'name':'boo_id','value':1}])
Update record:
db.update('boo',[[
{'name':'boo_id','value':2},
{'name':'boo_field_1','value':'boo value'}
]],['name':'boo_id','value':2])
Query:
var query = 'SELECT * FROM foo';
db.query(query,function(tx,res){
if(res.rows.length){
alert('found '+res.rows.length+' record(s)');
}else{
alert('table foo is empty');
}
});
Haven’t got time to put it in github, I’ll put it soon. Just fell free to use it and change it, and don’t forget to ask if you don’t know how to use it, or if there is some bugs. 😀
Cheers,
Helman
[…] a look at this http://blog.heldes.com/html5/solved-ios-database-size-limitation-of-5mb-using-plugin/, Solve the limitation without using this […]