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 «

Hi all,

Nice to see you all again here, still working with google map, almost done the google map project using phonegap, but i was facing a little bit problem about polygon. I want to draw polygon on-the-fly , not by passing the coordinate to google, but drawing polygon by mouse click to defined some area.

I have tried googling around to find some plugin, but couldn’t find any. I forget to tell you that I’m using Google Maps v3, not v2. Google has some plugin / library for Polygon drawing on version 2.

After some time, i decided to modified Polyline drawing plugin from snipplr.com.

what you need to do is to change the polyLine variable to Polygon definition:
» Read the rest of this entry «

Hi all,

It’s been a while since the last post of iOS Databases Limit. Now, I’m doing an experiment about the geolocation on iPhone using Navigator – Geolocation on Google Maps.

This is just for your information about HTML5 navigator – GeoLocation. GeoLocation API is part of HTML5 library that can be used to define your device location.

methods:

getCurrentPosition(callback[, error[, opts]])
return: none;

watchPositioin(callback[, error[, opts]])
return: watchID (long)

clearWatch(watchID)
return: none;

/* callback -> triggered when success of getting position */
callback(position)
return: none;
position: {
	coords:  {
		latitude: (double),
		longitude: (double),
		altitude: (double),
		accuracy: (double),
		altitudeAccuracy: (double),
		heading: (double),
		speed: (double)
	},
	timestamp: (timestamp)
};

/* callback -> triggered when error occur while getting position */
error(e)
return: none;
error {
	PERMISSION_DENIED: 1;
	POSITION_UNAVAILABLE: 2;
	TIMEOUT: 3;
	code: (enum);
	message: (string)
};

/* Options */
opts: {
	enableHighAccuracy: (boolean),
	timeout: (long);
	long maximumAge: (long);
};

» Read the rest of this entry «

Hi all,

As we know, iOS only allow us to have 5MB Database if we use web-base application. I also faced the same problem when i’m doing application that need more than 5MB Database size on iPad / iPhone using PhoneGap as a wrapper.

After some experiment on iPad and iPhone, i found some thing that very interesting:
1. We cannot define / open database with more than 5MB size allocation.
2. We able to define / open more than one databases that each one has 5MB or less size allocation
3. Every databases being recorded in Databases.db under ‘~/Library/WebKit/Databases’
4. Databases file is stored in ‘~/Library/WebKit/Databases/file__0’ path.
5. ‘~/Library/WebKit/Databases/file__0’ folder has limit definition (5MB) in Databases.db under origin table.

What i did:
1. Run my application using iPhone / iPad simulation (run it once)
2. Get the Databases.db file under simulation folder
3. Open Databases.db using SQLite client, in here i’m using “SQLite Database Browser 1.3”

» Read the rest of this entry «

This is my Javascript Class for HTML5 Databases connection to SQLite. I use this to build mobile application, including iOS, Android, and BlackBerry. I wrote this class to do the instance query to SQLite, and also some function for delete, insert, and update database records and table creation and deletion.

Here is my class:

/*************************************/
/* Helman, heldes.com			 */
/* helman at heldes dot com		 */
/* sqlitedb.js					 */
/* SQLite Database Class For HTML5 */
/*************************************/

function cDB(confs){
	var ret = {
		_db: null,
		_response: null,
		_error: null,
		check : function(tbl){
			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,null,null,null);
			}

			return true;
		},
		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+'; ';
			}
			console.log(err);
			//if(callback) callback();
			return false;
		},
		query: function(sql,callback,params,er){
			if(!this._db) return false;
			var self = this;
			function _er(tx,__er){
				__er = jQuery.extend(__er,{sql:sql});
				if(er) er(tx,__er);
				else self.callback_error(tx,__er);
			};
			this._db.transaction(function(tx){
				tx.executeSql(sql,(params?params:[]),callback,_er);
			}, _er);
		},
		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){
			var __sql = 'DELETE FROM '+tbl, __clause = '';

			for(var i=0;i<clauses.length;i++)
				__clause += ',`'+clauses[i].name+'`="'+escape(clauses[i].value)+'"';

			__sql += ' WHERE '+((__clause!='')?__clause.substr(1):'FALSE')+';';

			this.query(__sql);
			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);
					}
					tx.executeSql('INSERT INTO '+tbl+' ('+__field.substr(1)+') VALUES('+__qs.join(',')+');',__values,function(){return false;},(er ? er : self.callback_error));
				}
			}, self.callback_error, function(){
				if(callback) callback();
				return true;
			});
			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);
				}
				__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);
				}
				__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 «