making interaction with WebSQL easier

gistfile1.js

(function () {

  "use strict";

  /**
   * DB constructor
   *
   * @param {Object} options
   * @param {Function} callback
   */
  function DB(options, callback) {
    options || (options = {});
    this.size = options.size || 5 * 1024 * 1024;
    this.name = options.name;
    this.columns = options.columns;
    this.log = options.log;
    if (!this.name) throw new Error('Please provide a database name');
    this.db = openDatabase(this.name, "1", this.name, this.size);
    this.init(callback);
  }

  DB.prototype = {
    constructor: DB,

    /**
     * Initializes database.
     *
     * @param {Function} callback
     */
    init: function (callback) {
      var create = 'CREATE TABLE IF NOT EXISTS ' + this.name + '(id INTEGER PRIMARY KEY AUTOINCREMENT';
      if (this.columns) {
        var keys = '';
        for (var column in this.columns) {
          keys += ', ' + column + ' ' + this.columns[column];
        }
        create += keys;
      }
      create += ')';

      this.db.transaction(function (t) {
        t.executeSql(create, [], callback, errorCallback);
      });
    },

    /**
     * Batch insert.
     *
     * @param {Array} array of rows ready for batch
     * @param {Function} eachRowCallback iterator which executes
     *                   for each row before row is beining inserted into db.
     *                   Useful for data cleanup.
     *
     * @param {Function} callback executed after batch is finished
     *
     */
    batch: function (rows, eachRowCallback, callback) {
      var insert = 'INSERT INTO ' + this.name;
      var values = "";
      var columns = "";

      for (var column in this.columns) {
        columns += (columns) ? ', ' + column : column;
        values += (values) ? ', ?' : '?';
      }

      insert += '(' + columns + ') VALUES ' + '(' + values + ')';
      this.log && console.log(insert);
      this.db.transaction(function(tx) {
        rows.forEach(function(row) {
          if (row) {
            row = (eachRowCallback) ? eachRowCallback(row) : row;
            tx.executeSql(insert, row);
          }
        });
      }, errorCallback, callback, callback);
    },

    /**
     * Caches the name of the table/db for join.
     * Used with `search` and other calls where join is neede
     *
     * @param {DB} other table/db
     *
     * @param {Array} columns array which holds the names of the
     *        keys/columns the join should be perform againts.
     *        index 0 holds the name of the key from outer table/db
     *        index 1 holds the anem of the key from inner table/db
     *
     * example: ['id', 'product_id']
     *
     */
    join: function (table, columns) {
      this.joinedTable = table;
      this.joinedColumns = columns;
      return this;
    },

    /**
     * Perfoms join between two tables/columns.
     *
     * TODO: this is very basic now and only handles relations
     * between primary and secondary keys which are integers.
     *
     * @param {Array} records records returned from outer query
     * @param {Function} callback executes after join is done
     *
     */
    _join: function (records, callback) {
      var self = this;
      var fromC = this.joinedColumns[0];
      var toC = this.joinedColumns[1];
      var ids = "";
      var cache = {};

      for (var i = 0, l = records.length; i < l; i++) {
        var record = records[i];
        ids += (ids) ? " OR " + toC + "=" + record[fromC] : record[fromC];
        cache[record[fromC]] = record;
      }

      var query = {};
      query[toC] = ids;

      this.joinedTable.search(query, null, function (subrecords) {
        if (subrecords) {
          subrecords = self._intersect(cache, subrecords, toC);
        }

        callback(subrecords);
        cache = null;
        self.joinedTable = null;
        self.joinedBy = null;
      });
    },

    /**
     * Intersects records based on primary/secondary keys.
     *
     * @param {Object} records1 cached records from outer query
     *                 keys represent ids
     * @param {Array} records2 array of results from inner query
     * @param {String} toC primary key name
     *
     * @return records after intesection
     */
    _intersect: function (records1, records2, toC) {
      for (var j = 0, len = records2.length; j < len; j++) {
        var record = records2[j];
        extend(record, records1[record[toC]]);
      }
      return records2;
    },

    /**
     * Performs search based on given criteria and limit.
     *
     * TODO: support multiple criteria
     * @param {Object} criteria represents criteria used for search
     *         format: [{columnName1: ["OPERATION", "value"]},
     *                  {columnName2: "value"}];
     *
     * @param {Number} limit query limit
     * @param {Function} callback executed after search is done
     */
    search: function (criteria, limit, callback) {
      var self = this;
      var sql = "SELECT * FROM " + this.name;
      var records = [];

      if (criteria) {
        sql += ' WHERE ';
        for (var column in criteria) {
          if (isArray(criteria[column])) {
            // TODO: escape
            sql += column + " " + criteria[column][0] + ' ' + criteria[column][1];
          }
          else {
            sql += column + '=' + criteria[column];
          }
        }
      }

      if (limit) {
       sql += " LIMIT " + limit;
      }

      this.log && console.log(sql);
      this.db.readTransaction(function (t) {
        t.executeSql(sql, [], function (err, result) {
          if (result.rows.length != 0) {
            for (var i = 0, l = result.rows.length; i < l; i++) {
              records.push(result.rows.item(i));
            }
          }

          if (records.length > 0 && self.joinedTable) {
            self._join(records, callback);
          }
          else {
            callback(records);
          }
        }, errorCallback);
      });
    },

    save: function () {
      // TODO
    },

    /**
     * Returns number of records.
     *
     * @param {Function} callback
     */
    count: function (callback) {
      var sql = "SELECT count(*) as rowsize FROM " + this.name;
      this.db.readTransaction(function (t) {
        t.executeSql(sql, [], function (err, result) {
          callback(result.rows.item(0).rowsize);
        }, errorCallback);
      });
    },

    /**
     * Removes all records
     *
     * @param {Function} callback
     */
    removeAll: function (callback) {
      var sql = "DELETE FROM " + this.name;
      var seqSql = "DELETE FROM sqlite_sequence WHERE name='" + this.name + "'";
      this.db.transaction(function (t) {
        t.executeSql(sql, []);
        t.executeSql(seqSql, [], callback, errorCallback);
      });
    }
  };

  this.DB = DB;

  // helpers
  var slice = Array.prototype.slice;

  function errorCallback(tx, e) {
    throw new Error(e.message);
  }

  function isArray(obj) {
    return toString.call(obj) == "[object Array]";
  }

  function extend(target) {
    slice.call(arguments, 1).forEach(function(source) {
      for (var key in source) {
        if (source[key] !== undefined && target[key] == undefined) {
          target[key] = source[key];
        }
      }
    });
    return target;
  }

}).call(this);