diff options
Diffstat (limited to 'javascripts/libs/browser_engines')
| -rw-r--r-- | javascripts/libs/browser_engines/engines.js | 6 | ||||
| -rw-r--r-- | javascripts/libs/browser_engines/sqlite_driver.js | 71 | ||||
| -rw-r--r-- | javascripts/libs/browser_engines/sqljs_driver.js | 160 | ||||
| -rw-r--r-- | javascripts/libs/browser_engines/websql_driver.js | 268 |
4 files changed, 505 insertions, 0 deletions
diff --git a/javascripts/libs/browser_engines/engines.js b/javascripts/libs/browser_engines/engines.js new file mode 100644 index 0000000..ce0dff6 --- /dev/null +++ b/javascripts/libs/browser_engines/engines.js @@ -0,0 +1,6 @@ +define(['sqljs_driver', 'websql_driver'], function (SQLjs, WebSQL) { + return { + sqljs: SQLjs, + websql: WebSQL + }; +}); diff --git a/javascripts/libs/browser_engines/sqlite_driver.js b/javascripts/libs/browser_engines/sqlite_driver.js new file mode 100644 index 0000000..1aa6586 --- /dev/null +++ b/javascripts/libs/browser_engines/sqlite_driver.js @@ -0,0 +1,71 @@ +define(function () { + var SQLite_driver = function () { + return this; + } + + SQLite_driver.prototype.getSchemaStructure = function (args) { + /* + We are going to get the schema structure by running a special query to get back the table + definitions. We'll use that query to find the column names and types by parsing out the columns + from the create table statements. + */ + // this query gets back a result set which contains each table, along with the create table statement used + var schema_sql = "select * from sqlite_master where type IN ('table', 'view') and name != '__WebKitDatabaseInfoTable__' order by type,name"; + + var localCallback = function (resultSets) { + var colMap = {}; + var schemaStruct = []; + + for (var i in resultSets[0]["RESULTS"]["COLUMNS"]) + { + colMap[resultSets[0]["RESULTS"]["COLUMNS"][i]] = i; + } + + for (var j in resultSets[0]["RESULTS"]["DATA"]) + { + var tableDef = resultSets[0]["RESULTS"]["DATA"][j][colMap["sql"]]; + var tableName = resultSets[0]["RESULTS"]["DATA"][j][colMap["name"]]; + var tableType = resultSets[0]["RESULTS"]["DATA"][j][colMap["type"]]; + + var tableStruct = { + "table_name": tableName, + "table_type": tableType, + "columns": [] + }; + + if (tableType == "table") { // sadly, there is no easy way to get the columns back from views in WebSQL + var colsDef = /^[\s\S]*?\(([\s\S]*)\)$/.exec(tableDef)[1].split(/,\s*/); + + + for (var k in colsDef) { + var col_components = colsDef[k].replace(/(^\s*)|(\s*$)/, '').split(/\s+/); + tableStruct["columns"].push({ + "name": col_components.shift(), // first part of the col def + "type": col_components.join(' ') // rest of the col def + }) + } + } + schemaStruct.push(tableStruct); + } + + args["callback"](schemaStruct); + + } + + + // use the method available in child classes to get the results from our special query + this.executeQuery({sql: schema_sql, "success": localCallback}); + + } + + SQLite_driver.prototype.splitStatement = function (statements, separator) + { + if (! separator) separator = ";"; + var escaped_separator = separator.replace(/([.?*+^$[\]\\(){}|-])/g, "\\$1"); + + var sArray = (statements ? statements.split(new RegExp(escaped_separator + "\s*\r?(\n|$)")) : []); + return sArray; + } + + return SQLite_driver; +});
\ No newline at end of file diff --git a/javascripts/libs/browser_engines/sqljs_driver.js b/javascripts/libs/browser_engines/sqljs_driver.js new file mode 100644 index 0000000..89831da --- /dev/null +++ b/javascripts/libs/browser_engines/sqljs_driver.js @@ -0,0 +1,160 @@ +define(["jQuery", "sqlite_driver"], function ($, SQLite_driver) { + + var SQLjs_driver = function () { + this.db = null; + return this; + } + + $.extend(SQLjs_driver.prototype,SQLite_driver.prototype); // inherit from parent class + + + SQLjs_driver.prototype.buildSchema = function (args) { + + var _this = this; // preserve reference to current object through local closures + + try { + + /* + * Closure used to handle both cases of when the sql.js library + * has already been loaded, or when it has not yet been. + */ + var jsBuildSchema = function () { + + _this.db = SQL.open(); + $.each(SQLite_driver.prototype.splitStatement.call(this,args["ddl"],args["statement_separator"]), function (i, statement) { + _this.db.exec(statement); + }); + + args["success"](); + } + + // If the sql.js code isn't yet loaded, do it now. + if (window.SQL === undefined) + { + $.getScript("javascripts_static/sql.js", function (script, textStatus, jqXHR) { + jsBuildSchema(); + }).fail(function(jqxhr, settings, exception){ + args["error"]("Your browser does not work with SQL.js. Try using a different browser (Chrome, Safari, Firefox, IE 10, etc...), or a newer version of your current one."); + }); + } + else + { + if (_this.db) + { + _this.db.close(); + } + + jsBuildSchema(); + } + + } + catch (e) + { + args["error"](e); + } + + } + + SQLjs_driver.prototype.executeQuery = function (args) { + + var _this = this; // preserve reference to current object through local closures + + try { + if (! _this.db) + { + throw ("Database Schema not available!"); + } + + var returnSets = []; + + _this.db.exec("BEGIN TRANSACTION"); + + $.each(SQLite_driver.prototype.splitStatement.call(this,args["sql"],args["statement_separator"]), function (i, statement) { + if ($.trim(statement).length) { + var startTime = new Date(); + + var setArray = []; + + try { + setArray = _this.db.exec(statement); + + var thisSet = { + "SUCCEEDED": true, + "EXECUTIONTIME": (new Date()) - startTime, + "RESULTS": { + "COLUMNS": [], + "DATA": [] + }, + "EXECUTIONPLAN": { + "COLUMNS": [], + "DATA": [] + } + + }; + + if (setArray.length) { + $.each(setArray, function(rowNumber, row){ + var rowVals = []; + $.each(row, function(columnNumber, col){ + if (rowNumber == 0) { + thisSet["RESULTS"]["COLUMNS"].push(col.column); + } + rowVals.push(col.value); + }); + thisSet["RESULTS"]["DATA"].push(rowVals); + }); + } + + try { + + exectionPlanArray = _this.db.exec("EXPLAIN QUERY PLAN " + statement); + + if (exectionPlanArray.length) { + $.each(exectionPlanArray, function(rowNumber, row){ + var rowVals = []; + $.each(row, function(columnNumber, col){ + if (rowNumber == 0) { + thisSet["EXECUTIONPLAN"]["COLUMNS"].push(col.column); + } + rowVals.push(col.value); + }); + thisSet["EXECUTIONPLAN"]["DATA"].push(rowVals); + }); + } + + } + catch (e) { + // if we get an error with the execution plan, just ignore and move on. + } + + returnSets.push(thisSet); + + + } + catch (e) { + var thisSet = { + "SUCCEEDED": false, + "EXECUTIONTIME": (new Date()) - startTime, + "ERRORMESSAGE": e + }; + returnSets.push(thisSet); + return false; // breaks the each loop + } + + } + }); + + _this.db.exec("ROLLBACK TRANSACTION"); + + args["success"](returnSets); + + } + catch (e) + { + args["error"](e); + } + + } + + return SQLjs_driver; +}); diff --git a/javascripts/libs/browser_engines/websql_driver.js b/javascripts/libs/browser_engines/websql_driver.js new file mode 100644 index 0000000..2795b6c --- /dev/null +++ b/javascripts/libs/browser_engines/websql_driver.js @@ -0,0 +1,268 @@ +window.WebSQL_driver = function(){ + this.db = null; + this.ddl = []; + this.nativeSQLite = (window.openDatabase !== undefined); + return this; +}; + +$.extend(window.WebSQL_driver.prototype,window.SQLite_driver.prototype); // inherit from parent class + +window.WebSQL_driver.prototype.buildSchema = function (args) { + + var _this = this; // preserve reference to current object through local closures + + try { + + if (_this.nativeSQLite) + { + _this.db = openDatabase(args["short_code"], '1.0', args["short_code"], args["ddl"].length * 1024); + + _this.db.transaction(function(tx){ + + var statements = _this.splitStatement(args["ddl"],args["statement_separator"]); + _this.ddl = statements; + + var currentStatement = 0; + var statement = statements[currentStatement]; + + var sequentiallyExecute = function(tx2, result){ + if (currentStatement < statements.length-1) + { + do { + currentStatement++; + statement = statements[currentStatement]; + } while (currentStatement < statements.length-1 && statement.match(/^\s*$/)); + + if (!statement.match(/^\s*$/)) { + tx.executeSql(statement, [], sequentiallyExecute, handleFailure); + } + else + { + tx.executeSql("intentional failure used to rollback transaction"); + args["success"](); + } + + } + else + { + tx.executeSql("intentional failure used to rollback transaction"); + args["success"](); + } + }; + + var handleFailure = function (tx2, result) { + if (result.message != "not an error") // thank you safari, for this + { + args["error"](result.message); + } + else + { + args["success"](); + } + + return true; // roll back transaction + }; + + if (statement) { + tx.executeSql(statement, [], sequentiallyExecute, handleFailure); + } + else { + args["success"](); + } + }); + + } + else + { + args["error"]("SQLite (WebSQL) not available in your browser. Try either using a webkit-based browser (such as Safari or Chrome) or using the SQLite (SQL.js) database type.") + } + + } + catch (e) + { + args["error"](e); + } + + } + + +window.WebSQL_driver.prototype.executeQuery = function (args) { + + var _this = this; // preserve reference to current object through local closures + + try { + + if (_this.db == null ) { + throw("You need to build the schema before you can run a query."); + } + + var returnSets = []; + + _this.db.transaction(function(tx){ + + var sequentiallyExecute = function(tx2, result) { + + var thisSet = { + "SUCCEEDED": true, + "EXECUTIONTIME": (new Date()) - startTime, + "RESULTS": { + "COLUMNS": [], + "DATA": [] + }, + "EXECUTIONPLAN": { + "COLUMNS": [], + "DATA": [] + } + + }; + + for (var i = 0; i < result.rows.length; i++) { + var rowVals = []; + var item = result.rows.item(i); + + /* We can't be sure about the order of the columns returned, since they are returned as + * a simple unordered structure. So, we'll just take the order returned the from the first + * request, then just use that order for each row. + */ + if (i == 0) { + for (col in item) { + thisSet["RESULTS"]["COLUMNS"].push(col); + } + } + + for (var j = 0; j < thisSet["RESULTS"]["COLUMNS"].length; j++) { + rowVals.push(item[thisSet["RESULTS"]["COLUMNS"][j]]); + } + + thisSet["RESULTS"]["DATA"].push(rowVals); + } + + tx.executeSql("EXPLAIN QUERY PLAN " + statement, [], function (tx3, executionPlanResult) { + + for (var l = 0; l < executionPlanResult.rows.length; l++) { + var rowVals = []; + var item = executionPlanResult.rows.item(l); + + /* We can't be sure about the order of the columns returned, since they are returned as + * a simple unordered structure. So, we'll just take the order returned the from the first + * request, then just use that order for each row. + */ + if (l == 0) { + for (col in item) { + thisSet["EXECUTIONPLAN"]["COLUMNS"].push(col); + } + } + + for (var j = 0; j < thisSet["EXECUTIONPLAN"]["COLUMNS"].length; j++) { + rowVals.push(item[thisSet["EXECUTIONPLAN"]["COLUMNS"][j]]); + } + + thisSet["EXECUTIONPLAN"]["DATA"].push(rowVals); + } + + if (currentStatement > _this.ddl.length-1) + returnSets.push(thisSet); + + // executeSQL runs asynchronously, so we have to make recursive calls to handle subsequent queries in order. + if (currentStatement < (statements.length - 1)) + { + do { + currentStatement++; + statement = statements[currentStatement]; + } while (currentStatement < statements.length-1 && statement.match(/^\s*$/)); + + if (! statement.match(/^\s*$/)) + tx.executeSql(statement, [], sequentiallyExecute, handleFailure); + else + { + tx.executeSql("intentional failure used to rollback transaction"); + args["success"](returnSets); + } + + } + else + { + tx.executeSql("intentional failure used to rollback transaction"); + args["success"](returnSets); + } + + + }, + function(tx3, executionPlanResult){ + // if the explain failed, then just append the base set to the result and move on.... + + if (currentStatement > _this.ddl.length-1) + returnSets.push(thisSet); + + // executeSQL runs asynchronously, so we have to make recursive calls to handle subsequent queries in order. + if (currentStatement < (statements.length - 1)) + { + do { + currentStatement++; + statement = statements[currentStatement]; + } while (currentStatement < statements.length-1 && statement.match(/^\s*$/)); + + if (! statement.match(/^\s*$/)) + tx.executeSql(statement, [], sequentiallyExecute, handleFailure); + else + { + tx.executeSql("intentional failure used to rollback transaction"); + args["success"](returnSets); + } + } + else + { + tx.executeSql("intentional failure used to rollback transaction"); + args["success"](returnSets); + } + + + }); + + } + + var handleFailure = function (tx, result) { + if (result.message != "not an error") // thank you safari, for this + { + var thisSet = { + "SUCCEEDED": false, + "EXECUTIONTIME": (new Date()) - startTime, + "ERRORMESSAGE": result.message + }; + returnSets.push(thisSet); + } + + args["success"](returnSets); // 'success' - slightly confusing here, but in this context a failed query is still a valid result from the database + return true; // roll back transaction + } + + var setArray = [], k, stop = false; + + var statements = _this.ddl.slice(0); + + $.each(_this.splitStatement(args["sql"],args["statement_separator"]), function (i, stmt) { statements.push(stmt); }); + + var currentStatement = 0; + var statement = statements[currentStatement]; + + var startTime = new Date(); + + /* + * executeSql runs asynchronously, so I impose a semblance of synchronous-ness via recusive calls + */ + tx.executeSql(statement, [], sequentiallyExecute, handleFailure); + + + + }); + + } + catch (e) + { + args["error"](e); + } + + + } + + |