Helman's Blog

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 «