SQLite helper library for Mozilla

gistfile1.js

// SQLite helper library for Mozilla
var $sqlite = {
	
	storageService: [],
	mDBConn: [],
	
	_initService : function(file){
		var db = Components.classes["@mozilla.org/file/directory_service;1"].getService(Components.interfaces.nsIProperties).get("ProfD", Components.interfaces.nsIFile);
		db.append(file);
		this.storageService[file] = Components.classes["@mozilla.org/storage/service;1"].getService(Components.interfaces.mozIStorageService);
		this.mDBConn[file] = (this.storageService[file]).openDatabase(db);
			
	},
	
	array_query : function(file,sql,param){
		if (!this.storageService[file]){
        	this._initService(file);
		}
		var ourTransaction = false;
		if ((this.mDBConn[file]).transactionInProgress){
			ourTransaction = true;
			(this.mDBConn[file]).beginTransactionAs((this.mDBConn[file]).TRANSACTION_DEFERRED);
		}
		var statement = (this.mDBConn[file]).createStatement(sql);
        if (param){
			for (var m=2, arg=null; arg=arguments[m]; m++) {
				statement.bindUTF8StringParameter(m-2, arg);
			}
		}
		try{
			var dataset = [];
			while (statement.executeStep()){
				var row = [];
				for(var i=0,k=statement.columnCount; i<k; i++){
					row[statement.getColumnName(i)] = statement.getUTF8String(i);
				}
				dataset.push(row);
			}
		}
		finally {
			statement.reset();
		}
		if (ourTransaction){
			(this.mDBConn[file]).commitTransaction();
		}
		
		return dataset;	
	},
	
	
	execute : function(file,sql,param){
		if (!this.storageService[file]){
	    	this._initService(file);
		}
		var ourTransaction = false;
		if ((this.mDBConn[file]).transactionInProgress){
			ourTransaction = true;
			(this.mDBConn[file]).beginTransactionAs((this.mDBConn[file]).TRANSACTION_DEFERRED);
		}
		var statement = (this.mDBConn[file]).createStatement(sql);
		if (param){
			for (var m=2, arg=null; arg=arguments[m]; m++) {
				statement.bindUTF8StringParameter(m-2, arg);
			}
		}
		try{
			statement.execute();
		}
		finally {
			statement.reset();
		}
		if (ourTransaction){
			(this.mDBConn[file]).commitTransaction();
		}
		
	},
	
	
	execute_async : function(file,sql){
		if (!this.storageService[file]){
	    	this._initService(file);
		}
		var statements = [] ; 
		for(i = 0, k = sql.length; i < k ; i++){
			statements[i] = (this.mDBConn[file]).createStatement(sql[i]);
		}
		(this.mDBConn[file]).executeAsync(statements, statements.length);
	}
	
};

/////////////////////////////////////////////////////////////

Usage:

<script language="javascript">
//some variables :
//assuming db file is in user's profile directory:
var myDBFile = 'mydb.sqlite';

// some example SQL queries:
var myCreateDBQuery = 'CREATE TABLE IF NOT EXISTS mybooks_tbl (id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT);';

var myInsertQuery = 'INSERT INTO mybooks_tbl(title) VALUES("book title1");';
var myInsertQueryParameterized = 'INSERT INTO mybooks_tbl(title) VALUES(?1);';

var mySelectQuery = 'SELECT id,title FROM mybooks_tbl';
var mySelectQueryParameterized = 'SELECT id,title FROM mybooks_tbl WHERE id = ?1 AND title = ?2';



// For anything other than SELECT statement, use $sqlite.cmd() :
 
// creating a DB:
function test_createDB(){
	$sqlite.execute(myDBFile,myCreateDBQuery);
}

// simple add record:
function test_addRecord(){
	$sqlite.execute(myDBFile,myInsertQuery);
}

// parameterized add record, add parameters as much as you want:	
function test_addRecordParameterized(){
	// for example, adding 3 records:
	for(var i = 1 ; i < 4; i++){
		$sqlite.execute(myDBFile,myInsertQueryParameterized,'book title'+i+'');
	}
}

// for SELECT, use $sqlite.select() :

// simple select:
function test_Select(){
	var myArray1 = $sqlite.array_query(myDBFile,mySelectQuery);
	// Now you can loop through the array:
	for(var j=0;j<myArray1.length;j++){
		// change this as you wish:
		alert(myArray1[j]['title']);
	}
}

// select with bound parameters, add parameters as much as you want:
function test_SelectParameterized(){
	var myArray1 = $sqlite.array_query(myDBFile,mySelectQueryParameterized,'1','book title1');
	// Now you can loop through the array:
	for(var j=0;j<myArray1.length;j++){
		// change this as you wish:
		alert(myArray1[j]['title']);
	}
}
</script>
<a href="#" onclick="test_createDB();">Create DB</a> | 
<a href="#" onclick="test_addRecord()">Simple Add Record</a> | 
<a href="#" onclick="test_addRecordParameterized()">Parameterized Add Record</a> | 
<a href="#" onclick="test_Select()">Simple Select</a> | 
<a href="#" onclick="test_SelectParameterized()">Parameterized Select</a> |