SQLite Class for HTML5 Database

March 15th, 2011 § 11 comments

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:

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

How To Use It
Database Constructor:

1/* Create or open database with 'websiteDB' as database name and 'website DB' as title, and database site is 5MB */
2/* I'm not using 1024 for the size multiplying because i don't want to be near at the margin size                          */
3var db = new cDB({_db:window.openDatabase("websiteDB", "", "website DB", 5*1000*1000)});

Table Creation:

1/* dbTable is database structure in this example, and contains 2 tables 'foo' and 'boo' */
2/* and also the table structure in table properties                                                           */
3var dbTable = [
4        {table:'foo',properties: [
5            {name:'foo_id', type: 'INT PRIMARY KEY ASC'},
6            {name:'foo_field_1', type: ''},
7            {name:'foo)field_2', type: ''}
8        ]},
9        {table:'boo',properties: [
10            {name:'boo_id', type: 'INT PRIMARY KEY ASC'},
11            {name:'boo_field_1', type: ''},
12            {name:'boo_field_2', type: ''}
13        ]}
14    ];
15 
16/* this line is checking if the database exist or not and then create the database structure.  */
17/* table will be created if the table is not exist yet, if the table already exist, it will skip the */
18/* table and continue with others tables                                                                                  */
19if(!db.check(dbTable)){
20    db = false;
21    alert('Failed to cennect to database.');
22}

Drop Table:

`1`	`db.dropTable('foo');`

Add / Insert Record:

1var row = [];
2row.push([
3    {'name':'foo_id','value':1},
4    {'name':'foo_field_1','value':'value 1 field_1'},
5    {'name':'foo_field_2','value':'value 1 field_2']}
6]);
7db.insert('foo',row);

SQLite is not accepting more than 1 line statement, that is the reason why we not able to do more than one statement query, like insertion. If you want to insert more than 1 record at the time, you need to use this function.

Multiline records Insertion:

1var rows = [];
2rows.push([
3    {'name':'boo_id','value':1},
4    {'name':'boo_field_1','value':'value 1 field_1'},
5    {'name':'boo_field_2','value':'value 1 field_2']}
6]);
7rows.push([
8    {'name':'boo_id','value':2},
9    {'name':'boo_field_1','value':'value 2 field_1'},
10    {'name':'boo_field_2','value':'value 2 field_2']}
11]);
12 
13db.multiInsert('boo',rows,function(){alert('insertion done');});

Or, if you want to combine that two functions, just put if condition:

1if(rows.length&gt;=2){
2    db.multiInsert('boo',rows,function(){alert('insertion done');});
3}else{
4    db.insert('boo',rows);
5}

Delete record:

`1`	`db.remove('boo',[{'name':'boo_id','value':1}])`

Update record:

1db.update('boo',[[
2    {'name':'boo_id','value':2},
3    {'name':'boo_field_1','value':'boo value'}
4]],['name':'boo_id','value':2])

Query:

1var query = 'SELECT * FROM foo';
2db.query(query,function(tx,res){
3    if(res.rows.length){
4        alert('found '+res.rows.length+' record(s)');
5    }else{
6        alert('table foo is empty');
7    }
8});

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.

link of this class on github: https://github.com/helman/jSQLite

Cheers,
Helman

Tagged Class, Database Class, drop, html5, HTML5 database, insert, insert replace, iOS Database, ipad, iPad Databases, iphone, iPhone Databases, Javascript, multi lines insert, query, remove, update

§ 11 Responses to SQLite Class for HTML5 Database"

TC says:

September 16, 2011 at 6:39 am

Just curious, do you still use this for your mobile applications? Im learning and want to use html5, css, and jquery, but, mostly learning html5 currently, doesnt seem too hard, but, trying to learn about data storage, right now, creation of categories, basic storage like list of names, and creating new lists under those names.. sounds simple enough, but if you dont even know how to add, edit, delete, and used to mysql database with php…lol.. little more complicated than I thought..lol… So, do you suggest using this for data or anything else???? Thanks

Reply
- admin says:
  
  September 19, 2011 at 11:17 am
  
  Hi, thanks for your visit.
  Depend on what is your usage, i did catalog app using offline database, because my client want the fast loading of data, so using this, my client only need to update the catalog if there are any changes from website.
  HTML5 database (SQLite) is a little bit different from server database (mySQL). It is faster because of async processing, and because of async, it is quite defferent from php calling mySQL, we need to use Callback function to get the response of our query.
  So, using offline db is very helpfull if you’re considering the loading time. But it is only limited to 5MB only for mobile storage.
  
  Cheers,
  Helman
  
  Reply

Martin says:

September 22, 2011 at 9:42 pm

I really like this piece of code, but I seem to have some trouble using it. I can write information out to a database, but I can’t seem to query it. I know originally when I was looking at the code I could. I’m not sure what changed. I see it will return an error code, but I’m not sure how to do that.

Could you show an example of query: function(sql,callback,params,er) using all the fields. For example, show an example using the params and er parameters. I think if I can see the error I can figure out the problem I’m having.

Thanks,
Martin

admin says:

September 24, 2011 at 12:40 pm

Hi Martin,
Thank you for considering of using this DB Class. This is the simple code for Query:

1// Query with params, to use params, you need to use "?" (Question mark) in your query
2var sql = "SELECT * FROM foo WHERE name=? AND age=?";
3// Use params for automatic escaping string / values.
4var params = ['John',20];
5var callback = function(tx,res){
6    if(res.rows.length){
7        alert('found '+res.rows.length+' record(s)');
8    }else{
9        alert('table foo is empty');
10    }
11}
12// for the error callback function, i already put the error message on browser console, or if you want to replace the error function just refer to callback_error in the class, there are 2 parameters that you need to pass "tx" and "error" it self, and you need to "return false", this is the example of replacement
13var error = function(tx,_er){
14    var err = '';
15    if(typeof(tx) == 'object'){
16        for(var q in tx){
17            err += q+' = "'+tx[q]+'"; ';
18        }
19    }else{
20        err += tx+'; ';
21    }
22    if(typeof(_er) == 'object'){
23        for(var q in _er){
24            err += q+' = "'+_er[q]+'"; ';
25        }
26    }else if(typeof(_er) == 'undefined'){
27        err += _er+'; ';
28    }
29    alert(err);
30    return false;
31}
32db.query(sql,callback,params,error);

Just poke me, if you have any question.

Cheers,
Helman.

David says:

February 19, 2012 at 11:00 am

Hello,

thanks for the lib! I have a little question: The lib always a new DB, why?

Reply
David says:

February 19, 2012 at 11:00 am

Hello,

thanks for the lib! I have a little question: The lib add always a new DB, why?

Grettings David

Reply
temujin jumlani says:

March 29, 2012 at 6:00 pm

Great class!

Can you give an example on using transactions and getting last inserted row id on a table? Notify me via my email .

Thanks and good day.

Reply
Janek says:

April 13, 2012 at 7:00 am

I notice a “zero” from class file at line 75. Is it a bug?

Reply
- admin says:
  
  July 9, 2012 at 6:39 am
  
  Hi Janek,
  I just create a repository on github, and this is the link https://github.com/helman/jSQLite with the latest update.
  
  cheers,
  helman
  
  Reply
nader says:

December 1, 2012 at 4:03 pm

What in case more than 5 MB is needed. I am developing an app using HTML5 and javascript with SQL server backend (webservices used), but I need to store data offline for sales entry and then sync it back to server when in offline mode.
I have seen tutorials for native development with Sqlite but not while using HTML5 & jquery alone. Do you have any suggestions about htis
Thank you
Nad

Reply
- admin says:
  
  February 6, 2013 at 3:48 am
  
  Hi Nader, have a look at my new post Solved iOS Database Size Limitation of 5MB (using Plugin)
  
  Cheers,
  Helman
  
  Reply

What's this?

You are currently reading SQLite Class for HTML5 Database at Helman's Blog.

Helman's Blog