/public/javascripts/lib/html5sql.js

/* ***** html5sql.js ******
 *
 * Description: A helper javascript module for creating and working with
 *     HTML5 Web Databases.
 *
 * License: MIT license <http://www.opensource.org/licenses/mit-license.php>
 *
 * Authors: Ken Corbett Jr
 *
 * Version 0.9.2
 * 
 */

var html5sql = (function () {
	
	var readTransactionAvailable = false,
		doNothing = function () {},
		emptyArray = [],
		trim = function (string) {
			return string.replace(/^\s+/, "").replace(/\s+$/, "");
		},
		isArray = function (obj) { // From Underscore.js
			return Object.prototype.toString.call(obj) === '[object Array]'; 
		},
		isUndefined = function(obj) { // From Underscore.js
		    return obj === void 0;
		},
		SelectStmtMatch = new RegExp('^select\\s', 'i'),
	    isSelectStmt = function (sqlstring) {
			return SelectStmtMatch.test(sqlstring);
		},
		doNothing = function(){},
		// transaction is an sql transaction, sqlObjects are properly formated
		// and cleaned SQL objects
		sqlProcessor = function (transaction, sqlObjects, finalSuccess, failure) {
			
			var sequenceNumber = 0,
				dataForNextTransaction = null,
				currentSqlObject = null,
				runTransaction = function () {
					transaction.executeSql(sqlObjects[sequenceNumber].sql,
										   sqlObjects[sequenceNumber].data,
										   successCallback,
										   failureCallback);
				},
				successCallback = function (transaction, results) {
					var i, max, rowsArray = [];

					if(html5sql.logInfo){
						console.log("Success processing: " + sqlObjects[sequenceNumber].sql);
					}
					
					//Process the results of a select puting them in a much more manageable array form.
					if(html5sql.putSelectResultsInArray && isSelectStmt(sqlObjects[sequenceNumber].sql)){
						for(i = 0, max = results.rows.length; i < max; i++){
							rowsArray[i] = results.rows.item(i);
						}
					} else {
						rowsArray = null;
					}

					//Call the success callback provided with sql object
					//If an array of data is returned use that data as the
					//data attribute of the next transaction
					dataForNextTransaction = sqlObjects[sequenceNumber].success(transaction, results, rowsArray);
					sequenceNumber++;
					if (dataForNextTransaction && $.isArray(dataForNextTransaction)) {
						sqlObjects[sequenceNumber].data = dataForNextTransaction;
						dataForNextTransaction = null;
					} else {
						dataForNextTransaction = null;
					}
					
					if (sqlObjects.length > sequenceNumber) {
						runTransaction();
					} else {
						finalSuccess(transaction, results, rowsArray);
					}
				},
				failureCallback = function (transaction, error) {
					if(html5sql.logErrors){
						console.error("Error: " + error.message + " while processing statment "+(sequenceNumber + 1)+": " + sqlObjects[sequenceNumber].sql);
					}
					failure(error, sqlObjects[sequenceNumber].sql);
				};
			
			runTransaction();
		},
		sqlObjectCreator = function (sqlInput) {
			var i;
			if (typeof sqlInput === "string") {
				trim(sqlInput);
				
				//Separate sql statements by their ending semicolon
				sqlInput = sqlInput.split(';');
				
				for(i = 1; i < sqlInput.length; i++){
					//Ensure semicolons within quotes are replaced
					while(sqlInput[i].split(/["]/gm).length % 2 === 0 ||
						  sqlInput[i].split(/[']/gm).length % 2 === 0 ||
						  sqlInput[i].split(/[`]/gm).length % 2 === 0){
						 sqlInput.splice(i,2,sqlInput[i] + ";" + sqlInput[i+1]);
					}
					//Add back the semicolon at the end of the line
					sqlInput[i] = trim(sqlInput[i]) + ';';
					//Get rid of any empty statements
					if(sqlInput[i] === ';'){
						sqlInput.splice(i, 1);
					}
				}
			}
			for (i = 0; i < sqlInput.length; i++) {
				//If the array item is only a string format it into an sql object
				if (typeof sqlInput[i] === "string") {
					sqlInput[i] = {
						"sql": sqlInput[i],
						"data": [],
						"success": doNothing
					};
				} else {
					if(isUndefined(sqlInput[i].data)){
						sqlInput[i].data = [];
					}
					if(isUndefined(sqlInput[i].success)){
						sqlInput[i].success = doNothing;
					}
					// Check to see that the sql object is formated correctly.
					if (typeof sqlInput[i]         !== "object"   ||
					    typeof sqlInput[i].sql     !== "string"   ||
					    typeof sqlInput[i].success !== "function" ||
						!$.isArray(sqlInput[i].data)) {
						throw new Error("Malformed sql object: "+sqlInput[i]);
					}
				}
			}
			return sqlInput;
		},
		statementsAreSelectOnly = function (SQLObjects) {
		// Returns true if all SQL statement objects are SELECT statements.
			var i = 0;
				
			//Loop over SQL objects ensuring they are select statments
			do {
				//If the sql string is not a select statement return false
				if (!isSelectStmt(SQLObjects[i].sql)) {
					return false;
				}
				i++;
			} while (i < SQLObjects.length);
		
			//If all the statments happen to be select statments return true
			return true;
		};
	return {
		database: null,
		logInfo: false,
		logErrors: false,
		defaultFailureCallback: doNothing,
		putSelectResultsInArray: true,
		openDatabase: function (name, displayname, size, whenOpen) {
			html5sql.database = openDatabase(name, "", displayname, size);
			readTransactionAvailable = typeof html5sql.database.readTransaction === 'function';
			if (whenOpen) {
				whenOpen();
			}
		},
		
		process: function (sqlInput, finalSuccessCallback, failureCallback) {
		/*
		 *
		 *	Arguments:
		 *	
		 *  sql = [array SQLObjects] ~ collection of SQL statement objects
		 *           or
		 *        [array SQLStrings] ~ collection of SQL statement strings
		 *           or
		 *        "SQLstring"        ~ SQL string to be split at the ';'
		 *                             character and processed sequentially
         *
		 *  finalSuccessCallback = (function) ~ called after all sql statments have
		 *                               		been processed.  Optional.
		 * 
		 *  failureCallback = (function) ~ called if any of the sql statements
		 *                                 fails.  A default one is used if none
		 *                                 is provided.
		 *                             
		 *                               
		 *	SQL statement object:
		 *	{
		 *	 sql: "string",      !Required! ~ Your sql as a string
		 *	 data: [array],       Optional  ~ The array of data to be sequentially
		 *	                                  inserted into your sql at the ?
		 *   success: (function), Optional  ~ A function to be called if this
		 *                                    individual sql statment succeeds.
		 *                                    If an array is returned it is used as
		 *                                    the data for the next sql statement
		 *                                    processed.
		 *  }
		 *
		 *	Usage:
		 *	html5sql.process(
		 *		[{
		 *		   sql: "SELECT * FROM table;",
		 *		   data: [],
		 *		   success: function(){}
		 *		 },
		 *		 {
		 *		   sql: "SELECT * FROM table;",
		 *		   data: [],
		 *		   success: function(){}
		 *		 }],
		 *		function(){},
		 *		function(){}
		 *	);
		 *	
		 */
			if (html5sql.database) {
				
				var sqlObjects = sqlObjectCreator(sqlInput);

				if(isUndefined(finalSuccessCallback)){
					finalSuccessCallback = doNothing;
				}

				if(isUndefined(failureCallback)){
					failureCallback = html5sql.defaultFailureCallback;
				}

				if (statementsAreSelectOnly(sqlObjects) && readTransactionAvailable) {
					html5sql.database.readTransaction(function (transaction) {
						sqlProcessor(transaction, sqlObjects, finalSuccessCallback, failureCallback);
					});
				} else {
					html5sql.database.transaction(function (transaction) {
						sqlProcessor(transaction, sqlObjects, finalSuccessCallback, failureCallback);
					});
				}
			} else {
				// Database hasn't been opened.
				if(html5sql.logErrors){
					console.error("Error: Database needs to be opened before sql can be processed.");
				}
				return false;
			}
		},
	
		changeVersion: function (oldVersion, newVersion, sqlInput, finalSuccessCallback, failureCallback) {
		/* This is the same as html5sql.process but used when you want to change the
		 * version of your database.  If the database version matches the oldVersion
		 * passed to the function the statements passed to the funciton are
		 * processed and the version of the database is changed to the new version.
		 *
		 *	Arguments:
		 *	oldVersion = "String"             ~ the old version to upgrade
		 *	newVersion = "String"             ~ the new version after the upgrade
		 *  sql = [array SQLObjects] ~ collection of SQL statement objects
		 *           or
		 *        [array SQLStrings] ~ collection of SQL statement strings
		 *           or
		 *        "SQLstring"        ~ SQL string to be split at the ';'
		 *                             character and processed sequentially
         *
		 *  finalSuccessCallback = (function) ~ called after all sql statments have
		 *                               		been processed.  Optional.
		 * 
		 *  failureCallback = (function) ~ called if any of the sql statements
		 *                                 fails.  A default one is used if none
		 *                                 is provided.
		 *
		 *	SQL statement object:
		 *	{
		 *	 sql: "string",      !Required! ~ Your sql as a string
		 *	 data: [array],       Optional  ~ The array of data to be sequentially
		 *	                                  inserted into your sql at the ?
		 *   success: (function), Optional  ~ A function to be called if this
		 *                                    individual sql statment succeeds
		 *   failure: (function), Optional  ~ A function to be called if this
		 *                                    individual sql statement fails
		 *  }
		 *
		 *	Usage:
		 *	html5sql.changeVersion(
		 *	    "1.0",
		 *	    "2.0",
		 *		[{
		 *		   sql: "SELECT * FROM table;",
		 *		   data: [],
		 *		   success: function(){},
		 *		   failure: function(){}
		 *		 },
		 *		 {
		 *		   sql: "SELECT * FROM table;",
		 *		   data: [],
		 *		   success: function(){},
		 *		   failure: function(){}
		 *		 }],
		 *		function(){},
		 *		function(){}
		 *	);
		 *	
		 */
			if (html5sql.database) {
				if(html5sql.database.version === oldVersion){
					var sqlObjects = sqlObjectCreator(sqlInput);
				
					if(isUndefined(finalSuccessCallback)){
					finalSuccessCallback = doNothing;
					}

					if(isUndefined(failureCallback)){
						failureCallback = html5sql.defaultFailureCallback;
					}

					html5sql.database.changeVersion(oldVersion, newVersion, function (transaction) {
						sqlProcessor(transaction, sqlObjects, finalSuccessCallback, failureCallback);
					});
				}
			} else {
				// Database hasn't been opened.
				if(html5sql.logErrors){
					console.log("Error: Database needs to be opened before sql can be processed.");	
				}
				return false;
			}
		
		}
	};
	
})();