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:

/*************************************/
/* 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:

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

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);

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:

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&gt;=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');
	}
});

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 , , , , , , , , , , , , , , , ,

§ 11 Responses to SQLite Class for HTML5 Database"

  • TC says:

    Just curous, do you stll use ths for your moble applcatons? Im learnng and want to use html5, css, and jquery, but, mostly learnng html5 currently, doesnt seem too hard, but, tryng to learn about data storage, rght now, creaton of categores, basc storage lke lst of names, and creatng new lsts under those names.. sounds smple enough, but f you dont even know how to add, edt, delete, and used to mysql database wth php…lol.. lttle more complcated than I thought..lol… So, do you suggest usng ths for data or anythng else???? Thanks

    • admin says:

      H, thanks for your vst.
      Depend on what s your usage, dd catalog app usng offlne database, because my clent want the fast loadng of data, so usng ths, my clent only need to update the catalog f there are any changes from webste.
      HTML5 database (SQLte) s a lttle bt dfferent from server database (mySQL). It s faster because of async processng, and because of async, t s qute defferent from php callng mySQL, we need to use Callback functon to get the response of our query.
      So, usng offlne db s very helpfull f you’re consderng the loadng tme. But t s only lmted to 5MB only for moble storage.

      Cheers,
      Helman

  • Martin says:

    I really lke ths pece of code, but I seem to have some trouble usng t. I can wrte nformaton out to a database, but I can’t seem to query t. I know orgnally when I was lookng at the code I could. I’m not sure what changed. I see t wll return an error code, but I’m not sure how to do that.

    Could you show an example of query: functon(sql,callback,params,er) usng all the felds. For example, show an example usng the params and er parameters. I thnk f I can see the error I can fgure out the problem I’m havng.

    Thanks,
    Martn

    • admin says:

      H Martn,
      Thank you for consderng of usng ths DB Class. Ths s the smple code for Query:

      // Query wth params, to use params, you need to use "?" (Queston mark) n your query
      var sql = "SELECT * FROM foo WHERE name=? AND age=?";
      // Use params for automatc escapng strng / values.
      var params = ['John',20];
      var callback = functon(tx,res){
      	f(res.rows.length){
      		alert('found '+res.rows.length+' record(s)');
      	}else{
      		alert('table foo s empty');
      	}
      }
      // for the error callback functon,  already put the error message on browser console, or f you want to replace the error functon just refer to callback_error n the class, there are 2 parameters that you need to pass "tx" and "error" t self, and you need to "return false", ths s the example of replacement
      var error = functon(tx,_er){
      	var err = '';
      	f(typeof(tx) == 'object'){
      		for(var q n tx){
      			err += q+' = "'+tx[q]+'"; ';
      		}
      	}else{
      		err += tx+'; ';
      	}
      	f(typeof(_er) == 'object'){
      		for(var q n _er){
      			err += q+' = "'+_er[q]+'"; ';
      		}
      	}else f(typeof(_er) == 'undefned'){
      		err += _er+'; ';
      	}
      	alert(err);
      	return false;
      }
      db.query(sql,callback,params,error);
      

      Just poke me, f you have any queston. :)

      Cheers,
      Helman.

  • David says:

    Hello,

    thanks for the lb! I have a lttle queston: The lb always a new DB, why?

  • David says:

    Hello,

    thanks for the lb! I have a lttle queston: The lb add always a new DB, why?

    Grettngs Davd

  • temujin jumlani says:

    Great class!

    Can you gve an example on usng transactons and gettng last nserted row d on a table? Notfy me va my emal .

    Thanks and good day.

  • Janek says:

    I notce a “zero” from class fle at lne 75. Is t a bug?

  • nader says:

    What n case more than 5 MB s needed. I am developng an app usng HTML5 and javascrpt wth SQL server backend (webservces used), but I need to store data offlne for sales entry and then sync t back to server when n offlne mode.
    I have seen tutorals for natve development wth Sqlte but not whle usng HTML5 & jquery alone. Do you have any suggestons about hts
    Thank you
    Nad

Leave a Reply