Helman's Blog

Solved iOS Database Size Limitation of 5MB (using Plugin)

February 6th, 2013 § 1 comment § permalink

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’

1(function() {
2  var callbacks, cbref, counter, getOptions, root, Cordova = cordova;
3 
4  root = this;
5 
6  callbacks = {};
7 
8  counter = 0;
9 
10  cbref = function(hash) {
11    var f;
12    f = "cb" + (counter += 1);
13    callbacks[f] = hash;
14    return f;
15  };
16 
17  getOptions = function(opts, success, error) {
18    var cb, has_cbs;
19    cb = {};
20    has_cbs = false;
21    if (typeof success === "function") {
22      has_cbs = true;
23      cb.success = success;
24    }
25    if (typeof error === "function") {
26      has_cbs = true;
27      cb.error = error;
28    }
29    if (has_cbs) opts.callback = cbref(cb);
30    return opts;
31  };
32 
33  root.PGSQLitePlugin = (function() {
34 
35    PGSQLitePlugin.prototype.openDBs = {};
36 
37    function PGSQLitePlugin(dbPath, openSuccess, openError) {
38      this.dbPath = dbPath;
39      this.openSuccess = openSuccess;
40      this.openError = openError;
41      if (!dbPath) {
42        throw new Error("Cannot create a PGSQLitePlugin instance without a dbPath");
43      }
44      this.openSuccess || (this.openSuccess = function() {
45        console.log("DB opened: " + dbPath);
46      });
47      this.openError || (this.openError = function(e) {
48        console.log(e.message);
49      });
50      this.open(this.openSuccess, this.openError);
51    }
52 
53    PGSQLitePlugin.handleCallback = function(ref, type, obj) {
54      var _ref;
55      if ((_ref = callbacks[ref]) != null) {
56        if (typeof _ref[type] === "function") _ref[type](obj);
57      }
58      callbacks[ref] = null;
59      delete callbacks[ref];
60    };
61 
62    PGSQLitePlugin.prototype.executeSql = function(sql, success, error) {
63      var opts;
64      if (!sql) throw new Error("Cannot executeSql without a query");
65      opts = getOptions({
66        query: [].concat(sql || []),
67        path: this.dbPath
68      }, success, error);
69      Cordova.exec("PGSQLitePlugin.backgroundExecuteSql", opts);
70    };
71 
72    PGSQLitePlugin.prototype.transaction = function(fn, success, error) {
73      var t;
74      t = new root.PGSQLitePluginTransaction(this.dbPath);
75      fn(t);
76      return t.complete(success, error);
77    };
78 
79    PGSQLitePlugin.prototype.open = function(success, error) {
80      var opts;
81      if (!(this.dbPath in this.openDBs)) {
82        this.openDBs[this.dbPath] = true;
83        opts = getOptions({
84          path: this.dbPath
85        }, success, error);
86        Cordova.exec("PGSQLitePlugin.open", opts);
87      }
88    };
89 
90    PGSQLitePlugin.prototype.close = function(success, error) {
91      var opts;
92      if (this.dbPath in this.openDBs) {
93        delete this.openDBs[this.dbPath];
94        opts = getOptions({
95          path: this.dbPath
96        }, success, error);
97        Cordova.exec("PGSQLitePlugin.close", opts);
98      }
99    };
100 
101    return PGSQLitePlugin;
102 
103  })();
104 
105  root.PGSQLitePluginTransaction = (function() {
106 
107    function PGSQLitePluginTransaction(dbPath) {
108      this.dbPath = dbPath;
109      this.executes = [];
110    }
111 
112    PGSQLitePluginTransaction.prototype.executeSql = function(sql, success, error) {
113      this.executes.push(getOptions({
114        query: [].concat(sql || []),
115        path: this.dbPath
116      }, success, error));
117    };
118 
119    PGSQLitePluginTransaction.prototype.complete = function(success, error) {
120      var begin_opts, commit_opts, executes, opts;
121      if (this.__completed) throw new Error("Transaction already run");
122      this.__completed = true;
123      begin_opts = getOptions({
124        query: ["BEGIN;"],
125        path: this.dbPath
126      });
127      commit_opts = getOptions({
128        query: ["COMMIT;"],
129        path: this.dbPath
130      }, success, error);
131      executes = [begin_opts].concat(this.executes).concat([commit_opts]);
132      opts = {
133        executes: executes
134      };
135      Cordova.exec("PGSQLitePlugin.backgroundExecuteSqlBatch", opts);
136      this.executes = [];
137    };
138 
139    return PGSQLitePluginTransaction;
140 
141  })();
142 
143}).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:

1// JavaScript Document
2function cDB(confs){
3    var ret = {
4        _db: null,
5        _response: null,
6        _error: null,
7        check : function(tbl,callback){
8            if(!this._db) return false;
9             
10            var _sql = '', _sqlField='', _field=[];
11             
12            for(var i=0;i<tbl.length;i++){
13                _sql = "CREATE TABLE IF NOT EXISTS "+tbl[i].table+" (";
14                _field = tbl[i].properties;
15                _sqlField = '';
16                 
17                for (var j=0;j<_field.length;j++){
18                    _sqlField += ',`'+_field[j].name+'` '+_field[j].type;
19                }
20 
21                _sql += _sqlField.substr(1)+");";
22                 
23                this.query(_sql,callback,null,null);
24            }
25 
26            return true;
27        },
28        __: function(s){
29            return s;
30        },
31        getResult:function(){
32            return this._response;
33        },
34        getError:function(){
35            return this._error;
36        },
37        callback_error: function(tx,_er){
38            var err = '';
39            if(typeof(tx) == 'object'){
40                for(var q in tx){
41                    err += q+' = "'+tx[q]+'"; ';
42                }
43            }else{
44                err += tx+'; ';
45            }
46            if(typeof(_er) == 'object'){
47                for(var q in _er){
48                    err += q+' = "'+_er[q]+'"; ';
49                }
50            }else if(typeof(_er) == 'undefined'){
51                err += _er+'; ';
52            }
53            echo(err);
54            //if(callback) callback();
55            return false;
56        },
57        query: function(sql,callback,params,er){
58            if(!this._db) return false;
59            var self = this;
60            function _genErrorCallback(sql){
61                return function(tx,__er){
62                    __er = jQuery.extend(__er,{sql:sql});
63                    if(er) er(tx,__er);
64                    else self.callback_error(tx,__er);
65                }
66            };
67            var _query = [];
68                if(params)
69                    _query = params;
70                _query.splice(0,0,sql);
71             
72            this._db.transaction(function(tx){
73                tx.executeSql(_query,function(res){
74                    //echo('finish execute sql ' + _query , 300);
75                    callback(this,res);
76                },self.callback_error);
77            },function(){return false;}, _genErrorCallback(_query));
78        },
79        update:function(tbl,sets,clauses,callback){
80            var __sql = 'UPDATE '+tbl, _field = null, __set = '', __clause = '',__values=[];
81             
82            for(var i=0;i<sets.length;i++){0
83                _field = sets[i];
84                for(var j=0;j<_field.length;j++){
85                    __set += ',`'+_field[j].name+'`=?';
86                    __values.push(_field[j].value);
87                }
88            }
89 
90            for(var i=0;i<clauses.length;i++){
91                __clause += ',`'+clauses[i].name+'`=?';
92                __values.push(clauses[i].value);
93            }
94            __sql += ((__set!='')?' SET '+__set.substr(1):'')+((__clause!='')?' WHERE '+__clause.substr(1):'')+';';
95            this.query(__sql,callback,__values);
96            return true;
97        },
98        remove:function(tbl, clauses, callback){
99            var __sql = 'DELETE FROM '+tbl, __clause = '';
100             
101            if(typeof(clauses) != 'undefined'){
102                for(var i=0;i<clauses.length;i++)
103                    __clause += ',`'+clauses[i].name+'`="'+escape(clauses[i].value)+'"';
104                echo('doing clauses'); 
105                if(clauses.length > 0)
106                    __sql += ' WHERE ' + ((__clause!='')?__clause.substr(1):'FALSE');
107            }
108            __sql += ';';
109            this.query(__sql,callback);
110            return true;
111        },
112        multiInsert: function(tbl,rows,callback,er){
113            if(!this._db) return false;
114            var self = this;
115            var __sql = '', _field = null, __field = '', __qs = [], __values = [];
116             
117            this._db.transaction(function(tx){
118                for(var i=0;i<rows.length;i++){
119                    __qs = [];
120                    __values = [];
121                    __field = '';
122                    _field = rows[i];
123                     
124                    for(var j=0;j<_field.length;j++){
125                        __field += ',`'+_field[j].name+'`';
126                        __qs.push('?');
127                        __values.push(_field[j].value ? _field[j].value : '');
128                    }
129 
130                    var _sql = 'INSERT INTO '+tbl+' ('+__field.substr(1)+') VALUES('+__qs.join(',')+');';
131                    var _query = [];
132                    if(__values)
133                        _query = __values;
134                    _query.splice(0,0,_sql);
135                    tx.executeSql(_query,function(){return false;},self.callback_error);
136                }
137            }, function(){
138                if(callback) callback();
139                return true;
140            }, self.callback_error);
141 
142            return true;
143        },
144        insert:function(tbl,rows,callback){
145            var __sql = '', _field = null, __field = '', __qs = [], __values = [], __debug = '';
146             
147            for(var i=0;i<rows.length;i++){
148                __qs = [];
149                __field = '';
150                _field = rows[i];
151                 
152                __debug += _field[0].name+' = '+_field[0].value+';';
153                for(var j=0;j<_field.length;j++){
154                    __field += ',`'+_field[j].name+'`';
155                    __qs.push('?');
156                    __values.push(_field[j].value ? _field[j].value : '');
157                }
158                __sql += 'INSERT INTO '+tbl+' ('+__field.substr(1)+') VALUES('+__qs.join(',')+');';
159            }
160            this.query(__sql,callback,__values);
161            return true;
162        },
163        insertReplace:function(tbl,rows,debug){
164            var __sql = '', _field = null, __field = '', __qs = [], __values = [], __debug = '';
165             
166            for(var i=0;i<rows.length;i++){
167                __qs = [];
168                __field = '';
169                _field = rows[i];
170                 
171                __debug += _field[0].name+' = '+_field[0].value+';';
172                for(var j=0;j<_field.length;j++){
173                    __field += ',`'+_field[j].name+'`';
174                    __qs.push('?');
175                    __values.push(_field[j].value ? _field[j].value : '');
176                }
177                __sql += 'INSERT OR REPLACE INTO '+tbl+' ('+__field.substr(1)+') VALUES('+__qs.join(',')+');';
178            }
179            this.query(__sql,null,__values);
180            return true;
181        },
182        dropTable:function(tbl,callback){
183            var __sql = '';
184            if(tbl==null) return false;
185            __sql = 'DROP TABLE IF EXISTS '+tbl;
186            this.query(__sql,callback);
187            return true;
188        }
189    }
190    return jQuery.extend(ret,confs);
191}

How To Use It
Database Constructor:
» Read the rest of this entry «

How To Uninstall / Re-Install XCode 4

August 20th, 2011 § 0 comments § permalink

hi every one, I was trying to uninstall xcode 4, and I managed to uninstall it, but, when i want to re-install, i can’t get it from AppStore, it still marked as installed.
I was trying to remove all of the folder and no luck. After some time (15 minutes) go around to my folders, I found out “Install XCode” still in my /Application folder, that’s the reason why appStore keep it as Installed.
If you want to re-install the XCode, you just need to double click on “Install XCode” again and it will start the Installation step again.

This is a my small tips to uninstall XCode:

#from Terminal
$ sudo /Developer/Library/uninstall-devtools --mode=all
# or
$ sudo /Xcode4/Library/uninstall-devtools --mode=all

Depend on where is your Xcode get installed, sometime it’s not installed in /Developer but /Xcode4 because you have previous version Xcode 3.x

If you want the fresh Installation file of XCode, you will need to delete “Install Xcode” in /Application folder, and go to appStore again to download it.

thanks is all, talk to you guys soon, if you have some questions, just POKE me.

cheers,
helman.

Geo Location

June 15th, 2011 § 2 comments § permalink

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:

1getCurrentPosition(callback[, error[, opts]])
2return: none;
3 
4watchPositioin(callback[, error[, opts]])
5return: watchID (long)
6 
7clearWatch(watchID)
8return: none;
9 
10/* callback -> triggered when success of getting position */
11callback(position)
12return: none;
13position: {
14    coords:  {
15        latitude: (double),
16        longitude: (double),
17        altitude: (double),
18        accuracy: (double),
19        altitudeAccuracy: (double),
20        heading: (double),
21        speed: (double)
22    },
23    timestamp: (timestamp)
24};
25 
26/* callback -> triggered when error occur while getting position */
27error(e)
28return: none;
29error {
30    PERMISSION_DENIED: 1;
31    POSITION_UNAVAILABLE: 2;
32    TIMEOUT: 3;
33    code: (enum);
34    message: (string)
35};
36 
37/* Options */
38opts: {
39    enableHighAccuracy: (boolean),
40    timeout: (long);
41    long maximumAge: (long);
42};

» Read the rest of this entry «

iOS Database Limit to 5MB (Solved – Hacked)

April 6th, 2011 § 2 comments § permalink

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 «

Where Am I?

You are currently browsing the iOS category at Helman's Blog.

Helman's Blog

Solved iOS Database Size Limitation of 5MB (using Plugin)

How To Uninstall / Re-Install XCode 4

Geo Location

iOS Database Limit to 5MB (Solved – Hacked)

Where Am I?

Tags

Cheap VPS