1 /** 2 * @fileoverview Provides error handling and database initializations on top of HTML database API 3 * @author <a href="mailto:semprebon@gmail.com">Andrew Semprebon</a> 4 * @version 1.0 5 */ 6 /*jslint white: false, onevar: false, plusplus: false, browser: true */ 7 var openDatabase; 8 9 /** 10 * @namespace NimbuslyDB namespace 11 */ 12 var nimbusly; 13 if (nimbusly === undefined) { nimbusly = {}; } 14 15 /** 16 * A simple class for storing migrations. 17 * 18 * A migration object holds the SQL needed to upgrade the database from one version to the next. An 19 * array of migrations is passed to the database when it is opened, and defined the order in which 20 * the versions come. This way, if you upgrade the software on the server,users with older versions 21 * will have their local databases automatically updated to the new database schema, regardless of 22 * what version they are on. 23 * 24 * The version numbers should normally start at one for the first version, and increment with each 25 * specific database change released. For example: 26 * <code> 27 * var migrations = [ 28 * nimbusly.Migration(1, "CREATE TABLE users (id REAL UNIQUE, name TEXT, password TEXT)"), 29 * nimbusly.Migration(2, "ALTER TABLE users ADD COLUMN email TEXT"), 30 * nimbusly.Migration(3, [ 31 * "CREATE TABLE user_emails (id REAL UNIQUE, user_id REAL, name TEXT, email TEXT"), 32 * "INSERT INTO user_mails (id, user_id, name, email) SELECT id, id, 'main', email FROM users", 33 * ] 34 * </code> 35 * 36 * @class 37 * @param {Number} version version of the database this migration will upgrade to 38 * @param {String|String[]|String[][]} sql one or more sql statements to migrate the database 39 * 40 * @see nimbusly.Database 41 */ 42 nimbusly.Migration = function(version, sql) { 43 this.version = version; 44 this.sql = sql; 45 }; 46 47 /** 48 * TransactionLogger is used to format error messages in a standard way. 49 * 50 * It is mainly used internally to handle logging 51 * 52 * @private 53 * @constructor 54 * @param database the database this TransactionLogger is for 55 * @param name name of TransactionLogger displayed in log 56 */ 57 nimbusly.TransactionLogger = function(database, name) { 58 this.database = database; 59 this.name = name || 'transaction'; 60 this.index = database.transactionIndex++; 61 database.logger(this.index + ". " + this.name + " started"); 62 }; 63 64 nimbusly.TransactionLogger.prototype = (function() { 65 /**#@+ 66 * @memberOf nimbusly.TransactionLogger.prototype 67 */ 68 69 var proto = {}; 70 71 /** 72 * Log a message related to this loggers transaction 73 * 74 * @name nimbusly.TransactionLogger.logger 75 * @param {String} message message to send to logger 76 */ 77 proto.logger = function(message) { 78 this.database.logger(this.index + ". " + message); 79 }; 80 81 /** 82 * Transaction callback to report action complete 83 * 84 * @name nimbusly.TransactionLogger.logger 85 * @memberOf nimbusly.TransactionLogger 86 * @param {String} message message to send to logger 87 */ 88 proto.onTransactionComplete = function(message) { 89 this.database.logger(this.index + ". " + this.name + " " + message); 90 }; 91 92 /** 93 * Wrap a transaction success callback to also log that the transaction has completed. 94 * 95 * @private 96 * @memberOf nimbusly.TransactionLogger 97 * @param {Function} [callback] function to call when we have logged the transaction 98 */ 99 proto.callbackWrapper = function(callback) { 100 var txLogger = this; 101 return function() { 102 txLogger.onTransactionComplete("completed"); 103 if (callback) { callback.call(txLogger.database); } 104 }; 105 }; 106 107 /** 108 * Wrap a transaction error callback to also log that the transaction has failed. 109 * 110 * @private 111 * @memberOf nimbusly.TransactionLogger 112 * @param {Function|null} callback function to call when we have logged the failure 113 */ 114 proto.errorWrapper = function(callback) { 115 var txLogger = this; 116 return function() { 117 txLogger.onTransactionComplete("aborted"); 118 if (callback) { 119 callback.call(txLogger.database); 120 } else { 121 this.onTransactionFailed(); 122 } 123 }; 124 }; 125 126 /** 127 * Default transaction failure error handler 128 * 129 * @private 130 * @memberOf nimbusly.TransactionLogger 131 */ 132 proto.onTransactionFailed = function() { 133 throw "Transaction failed: " + tx.index + " - " + tx.name; 134 }; 135 136 /** 137 * Handle a statement error. 138 * 139 * @private 140 * @memberOf nimbusly.TransactionLogger 141 * @param {String} sql reports an error on sql statement 142 */ 143 proto.sqlErrorHandler = function(sql) { 144 var txLogger = this; 145 return function(tx, error) { 146 txLogger.logger("SQL Statement error on " + sql); 147 txLogger.logger(" " + error.message) 148 }; 149 }; 150 /**#@-*/ 151 return proto; 152 }()); 153 154 /** 155 * HTML5 Database wrapper class 156 * <pre> 157 * var db = nimbus.Database.new('mydb', migrations); 158 * </pre> 159 * 160 * Most of the methods in this class can accept SQL in a number of different formats: 161 * <ol> 162 * <li>String - a simple string contaning SQL 163 * <li>Array - a sql statement, followed by a list of paramters 164 * <li>Array of arrays - an array of sql statements with parameters, to be executed as a single transaction 165 * </ol> 166 * 167 * So any of the following are correct: 168 * <pre> 169 * database.executeSqlUpdate("update user set enabled=true where id=4"); 170 * database.executeSqlUpdate("update user set enabled=true where id=?", 4); 171 * database.executeSqlUpdate([ 172 * ["update user set enabled=true where id=?", 4], 173 * ["insert user_permissions (id, code) values (?, 'login)", 4] 174 * ]); 175 * </pre> 176 * @see nimbusly.Migration 177 * 178 * @class 179 * @param {string} databaseName name of HTML database 180 * @param {nimbusly.Migration[]} migrations migrations for udpdating or initializing the database 181 * @param {String} [version] version of database needed (defaults to latest version) 182 */ 183 nimbusly.Database = function(databaseName, migrations, version) { 184 this.migrations = migrations; 185 this.databaseName = databaseName; 186 this.databaseDescription = databaseName; 187 this.latestVersion = version || this.highestVersion(); 188 this.transactionIndex = 0; 189 this.dbReady = false; 190 }; 191 192 nimbusly.Database.prototype = (function() { 193 var prototype = {}; 194 195 var errors = []; 196 197 /** 198 * Logging method XX 199 * 200 * <p>To actually get database logging, set this to your own database logging method, which should be 201 * of the form function(message) where message will be a string, 202 * <pre> 203 * db.logger = function(message) { console.log(message); } 204 * </pre> 205 * </p> 206 * @name nimbusly.Database.logger 207 * @field 208 */ 209 prototype.logger = function(message) {}; 210 211 prototype.onTransactionFailed = function() { 212 var error = new Error(errors.join('\n')); 213 errors = []; 214 throw error; 215 }; 216 217 prototype.versions = function() { 218 return this.migrations.map(function(mig) { return mig.version; }); 219 }; 220 221 prototype.highestVersion = function() { 222 var vers = this.versions(); 223 var m = vers.reduce(Math.max); 224 return m; 225 }; 226 227 /** 228 * Open the database, do any upgrade/initialization, then call the callback when it is ready to use 229 * 230 * @name nimbusly.Database.open 231 * @param {Function} [callback] function to call when database has been opened and initialized 232 * @param {Boolean} [dropDataFlag] if true, reinitializes the database from scratch 233 */ 234 prototype.open = function(callback, dropDataFlag) { 235 this.onDatabaseReady = callback; 236 this.db = openDatabase(this.databaseName, "", this.databaseDescription, 100000); 237 if (!this.db) { 238 throw new Error("Failed to open the database on disk: " + this.databaseName); 239 } 240 this.logger("Initial database version is " + this.db.version); 241 if (dropDataFlag) { 242 this.dropData(this.migrations); 243 } else if (this.migrations) { 244 this.upgradeDatabase(); 245 } 246 }; 247 248 /** 249 * Report an error; if only passed what, it will curry the result, returning a function that can be 250 * passed in as a sql statement or transaction error callback. This assumes we always want to abort 251 * the transaction if an error occurred. 252 */ 253 prototype.saveError = function(what, error, txIndexOrError) { 254 if (error === undefined) { 255 var database = this; 256 var txIndex = this.transactionIndex; 257 return function(tx, error) { 258 database.logger("error callback called! tx=" + tx + ", error=" + error); 259 if (error === undefined) { 260 error = txIndex; 261 tx = null; 262 } 263 database.logger('Error! ' + error); 264 database.saveError(what, error, txIndex); 265 if (tx) { 266 return true; 267 } 268 }; 269 } else { 270 if (txIndexOfError !== undefined) { 271 what = txIndexOfError + "." + what; 272 } 273 errors.push(what + ": " + error.message + " (" + error.code + ")"); 274 return true; 275 } 276 }; 277 278 /** 279 * Run all migrations up to a given version 280 */ 281 prototype.runMigrationsInRange = function(tx, currentVersion, finalVersion, txLogger) { 282 txLogger.logger("Migrating from " + currentVersion + " to " + finalVersion); 283 for (var i = 0; i < this.migrations.length; ++i) { 284 var migration = this.migrations[i]; 285 if (currentVersion < migration.version && migration.version <= finalVersion) { 286 this.executeSqlInTransaction(tx, migration.sql, null, txLogger); 287 } 288 } 289 }; 290 291 /** 292 * upgrade the database to the latest version 293 */ 294 prototype.upgradeDatabase = function() { 295 this.currentVersion = this.db.version ? this.db.version.to_i : 0; 296 var txLogger = new nimbusly.TransactionLogger(this, "Upgrade Database"); 297 var database = this; 298 this.db.changeVersion(this.db.version, String(this.latestVersion), 299 function(tx) { 300 database.runMigrationsInRange(tx, database.currentVersion, database.latestVersion, txLogger); 301 database.dbReady = true; 302 }, 303 txLogger.errorWrapper(this.onTransactionFailed), 304 txLogger.callbackWrapper(this.onDatabaseReady)); 305 }; 306 307 /** 308 * Restore the database to its initial state 309 */ 310 prototype.dropData = function(migrations) { 311 this.logger("Dropping data and setting database back to initial state"); 312 var database = this; 313 var dropTablesSql = []; 314 this.executeSqlQuery( 315 "SELECT name FROM sqlite_master WHERE type = 'table'", 316 // success callback - save generated sql statements for transaction callback 317 function(tx, results) { 318 for (var i = 0; i < results.rows.length; ++i) { 319 var name = results.rows.item(i).name; 320 if (!(/(^__)|(^sqlite_)/i.test(name))) { 321 dropTablesSql.push(['DROP TABLE ' + name]); 322 } 323 } 324 }, 325 // transaction callback - run sql generated, then update database 326 function() { 327 var txLogger = new nimbusly.TransactionLogger(database, "Drop data"); 328 database.db.changeVersion(database.db.version, "", 329 function(tx) { 330 if (dropTablesSql.length > 0) { 331 database.executeSqlInTransaction(tx, dropTablesSql, null, txLogger); 332 } 333 }, 334 txLogger.errorWrapper(database.onTransactionFailed), 335 txLogger.callbackWrapper(database.upgradeDatabase)); 336 }); 337 }; 338 339 /** 340 * Convert incomming sql into array: [[sql1, params...], [sql2, params...], ...] 341 * 342 * param sql can be a string, an array of the form [stement, param, ...], or an array of arrays 343 * [sql, ...] where each sql is either a string or an array 344 */ 345 prototype.normalizeSql = function(sql) { 346 if (!(sql instanceof Array)) { 347 return [[sql]]; 348 } else if (!(sql[0] instanceof Array)) { 349 return [sql]; 350 } else { 351 return sql; 352 } 353 }; 354 355 /** 356 * Executes some sql in the context of the given transaction. If successful, the callback is called 357 * with the results. If an error occurs, it is reported through reportError. 358 * 359 * @private 360 * @name nimbusly.Database.executeSqlInTransaction 361 * @methodOf nimbusly.Database.prototype 362 * @param {String|String[]|String[][]|Function} sql a function function(tx) or string containing sql statement 363 * @params {Function} [callback] must be function(tx, results) 364 */ 365 prototype.executeSqlInTransaction = function(tx, sql, callback, txLogger) { 366 var database = this; 367 sql = this.normalizeSql(sql); 368 sql.each(function(statement_with_params, i) { 369 var statement = statement_with_params[0]; 370 var params = statement_with_params.slice(1); 371 if (txLogger) { 372 txLogger.logger(statement + " with (" + params + ") - (" + (callback ? callback.name : "") + ")"); 373 } 374 tx.executeSql(statement, params, callback, txLogger.sqlErrorHandler(sql)); 375 }); 376 }; 377 378 /** 379 * Executes a SQL update statement, 380 * 381 * If successful, the callback, if any, is called. 382 * 383 * <pre> 384 * db.executeSqlUpdate(["UPDATE user SET password = ?", new_password], function() { 385 * alert("Password changed!"); 386 * }); 387 * </pre> 388 * 389 * @name nimbusly.Database.executeSqlUpdate 390 * @methodOf nimbusly.Database.prototype 391 * @memberOf nimbusly.Database 392 * @param {String|String[]|String[][]} sql sql statement(s) to execute 393 * @param {Function} [callback] must be of the type function(), and is called on a successful update 394 */ 395 prototype.executeSqlUpdate = function(sql, callback) { 396 var txLogger = new nimbusly.TransactionLogger(this); 397 var database = this; 398 this.db.transaction( 399 function(tx) { 400 database.executeSqlInTransaction(tx, sql, null, txLogger); 401 }, 402 txLogger.errorWrapper(), 403 txLogger.callbackWrapper(callback) 404 ); 405 }; 406 407 408 /** 409 * Executes a SQL insert statement and returns the rowid of the row inserted. 410 * 411 * If successful, the callback, if any, is called. If executeSqlInsert is called with a single 412 * insert statement, a single row id is returned to the callback. If multiple insert statements 413 * are specified, then the callback will be passed an array or rowids corresponding to 414 * the sql statements. 415 * 416 * <pre> 417 * db.executeSqlInsert(["INSERT INTO user (name, password) values (?, ?)", name, password], function(result) { 418 * alert("User added with id " + result); 419 * }); 420 * </pre> 421 * 422 * @name nimbusly.Database.executeSqlInsert 423 * @methodOf nimbusly.Database.prototype 424 * @param {String|String[]|String[][]} sql sql statement 425 * @param {Function} [callback] must be of the type function(results) where results will be a single row id, or an array of row ids 426 */ 427 prototype.executeSqlInsert = function(sql, callback) { 428 var txLogger = new nimbusly.TransactionLogger(this); 429 var rowids = []; 430 var database = this; 431 this.db.transaction( 432 function(tx) { 433 database.executeSqlInTransaction(tx, sql, 434 function(tx) { 435 database.executeSqlInTransaction(tx, [["SELECT last_insert_rowid() AS id"]], 436 function(tx, result) { 437 rowids.push(result.rows.item(0).id); 438 }, txLogger); 439 440 }, txLogger); 441 }, 442 this.saveError(sql), 443 txLogger.callbackWrapper( 444 function() { 445 callback((sql instanceof Array && sql[0] instanceof Array) ? rowids : rowids[0]); 446 })); 447 }; 448 449 /** 450 * Convert our query callback (function(rows)) into a Database API SQLStatementCallback (function(tx, result)). 451 */ 452 prototype.queryCallbackWrapper = function(callback) { 453 // If we arelady have a SQLStatementCallback, don't wrap it 454 if (callback.length === 2) { 455 return callback; 456 } 457 return function(tx, result) { 458 var rows = []; 459 for (var i = 0; i < result.rows.length; ++i) { 460 rows.push(result.rows.item(i)); 461 } 462 callback(rows); 463 }; 464 }; 465 466 /** 467 * Executes a SQL query and returns the results as an array of objects. 468 * 469 * If succesful, the callback, if any, is called with the results. 470 * 471 * <pre> 472 * var userSelect = document.getElementById('userSelect); 473 * db.executeSqlQuery("SELECT * FROM users", function(users) { 474 * for (var i = 0; i < users.length; ++i) { 475 * userSelect.add(new Option(user.name, user.rowid)); 476 * } 477 * }); 478 * </pre> 479 * 480 * @name nimbusly.Database.executeSqlQuery 481 * @methodOf nimbusly.Database.prototype 482 * @param {String|String[]|String[][]} sql sql statement 483 * @param {Function} [callback] must be of the form function(results) where results will be an array of objects 484 */ 485 prototype.executeSqlQuery = function(sql, callback, transactionCallback) { 486 var txLogger = new nimbusly.TransactionLogger(this); 487 var database = this; 488 this.db.transaction( 489 function(tx) { 490 database.executeSqlInTransaction(tx, sql, database.queryCallbackWrapper(callback), txLogger); 491 }, 492 txLogger.errorWrapper(database.onTransactionFailed), 493 txLogger.callbackWrapper(transactionCallback)); 494 }; 495 496 /**#@-*/ 497 return prototype; 498 }()); 499 500 501