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