另一个SQLite操作类（JavaScript）

sqlite.js

/**
A very simple Javascript layer for the web based SQLite database.

example usage:
var db = SQLite({ shortName: 'mydb' });
db.createTable('people', 'name TEXT, age INTEGER');
db.insert('people', { name: 'Jeremy', age: 29 });
db.update('people', { age: 30 }, { name: 'Jeremy' });
db.select('people', '*', { age: 30 }, function (results) { var x; for(x=0; x<results.rows.length; x++) { console.log(results.rows.item(x)); } });
db.destroy('people', { age: 30 });

Conditions can be:
  * a number: defaults to 'WHERE id=number'
  * a string: generates 'WHERE string'
  * an array: generates 'WHERE val1 AND val2'
  * a hash:   generates 'WHERE key=hash[key] AND key2=hash[key2]'

另外的例子：
  function pass(results, query) {
    var target = document.getElementById('results'), html = target.innerHTML;
    target.innerHTML = html + 'pass - ' + query + '<br />';
  }

  function fail(error, query) {
    var target = document.getElementById('results'), html = target.innerHTML;
    target.innerHTML = html + 'fail - ' + error.message + ': ' + query + '<br />';
  }

  var db = SQLite({ shortName: 'mydb' + parseInt(Math.random() * 100000), defaultErrorHandler: fail, defaultDataHandler: pass });

  db.createTable('people', 'name TEXT, age INTEGER');

  db.insert('people', { name: "Jeremy", age: 29 });
  db.insert('people', { name: "Tara", age: 28 });

  db.update('people', { age: 30 }, { name: 'Jeremy' });

  db.select('people', '*', { age: 30 }, null, function (r, q) { pass(r, q); var x; for(x=0; x<r.rows.length; x++) { console.log(r.rows.item(x)); } });
  db.select('people', 'name', null, { order: 'age DESC' }, function (r, q) { pass(r, q); var x; for(x=0; x<r.rows.length; x++) { console.log(r.rows.item(x)); } });
  db.select('people', 'name', null, { limit: 1 }, function (r, q) { pass(r, q); var x; for(x=0; x<r.rows.length; x++) { console.log(r.rows.item(x)); } });

  db.destroy('people', { age: 30 });

初始化参数：
  cfg.shortName：数据库名称，默认是 'mydatabase'
  cfg.version：数据库版本，默认是 '1.0'
  cfg.displayName：数据库显示名称，默认是 'My SQLite Database'
  cfg.maxSize：数据库最大占用空间，默认是 65536
  cfg.defaultErrorHandler = 出错处理函数，默认是 errorHandler
  cfg.defaultDataHandler = 数据处理函数，默认是 nullDataHandler (即不处理)
 * @param {} cfg
 */
function SQLite(cfg) {
  if (typeof window.openDatabase === 'undefined') {
    return;
  }

  function log(str) {
    if (typeof console !== 'undefined') {
      console.log(str);
    }
  }

  function isNumber(val) {
    switch (typeof val) {
    case 'number':
      return true;
    case 'string':
      return (/^\d+$/).test(val);
    case 'object':
      return false;
    }
  }

  /**
   * 默认数据处理函数
   * @param {} results
   */
  function nullDataHandler(results) { }

  /**
   * 默认出错处理函数
   * @param {} error
   */
  function errorHandler(error) {
    log('Oops. ' + error.message + ' (Code ' + error.code + ')');
  }

  var config = cfg || {}, db;
  
  config.shortName = config.shortName || 'mydatabase';
  config.version = config.version || '1.0';
  config.displayName = config.displayName || 'My SQLite Database';
  config.maxSize = 65536;
  config.defaultErrorHandler = config.defaultErrorHandler || errorHandler;
  config.defaultDataHandler = config.defaultDataHandler || nullDataHandler;

  //创建数据库
  try {
    db = openDatabase(config.shortName, config.version, config.displayName, config.maxSize);
  } catch (e) {
    if (e === 2) {
      log("Invalid database version.");
    } else {
      log("Unknown error " + e + ".");
    }

    return;
  }

  /**
   * 执行查询
   * @param {} query 查询语句
   * @param {} v 数据
   * @param {} d 数据处理回调函数
   * @param {} e 出错处理回调函数
   */
  function execute(query, v, d, e) {
    var values = v || [],
      dH = d || config.defaultDataHandler,
      eH = e || config.defaultErrorHandler;

    if (!query || query === '') {
      return;
    }

    function err(t, error) {
      eH(error, query);
    }

    function data(t, result) {
      dH(result, query);
    }

    db.transaction(
      function (transaction) {
        transaction.executeSql(query, values, data, err);
      }
    );
  }

  /**
   * 构造查询条件
   * @param {} conditions
   * @return {}
   */
  function buildConditions(conditions) {
    var results = [], values = [], x;

    if (typeof conditions === 'string') {
      results.push(conditions);
    } else if (typeof conditions === 'number') {
      results.push("id=?");
      values.push(conditions);
    } else if (typeof conditions === 'object') {
      for (x in conditions) {
        if (conditions.hasOwnProperty(x)) {
          if (isNumber(x)) {
            results.push(conditions[x]);
          } else {
            results.push(x + '=?');
            values.push(conditions[x]);
          }
        }
      }
    }

    if (results.length > 0) {
      results = " WHERE " + results.join(' AND ');
    } else {
      results = '';
    }

    return [results, values];
  }

  function createTableSQL(name, cols) {
    var query = "CREATE TABLE " + name + "(" + cols + ");";

    return [query, []];
  }

  function dropTableSQL(name) {
    var query = "DROP TABLE " + name + ";";

    return [query, []];
  }

  function insertSQL(table, map) {
    var query = "INSERT INTO " + table + " (#k#) VALUES(#v#);", keys = [], holders = [], values = [], x;

    for (x in map) {
      if (map.hasOwnProperty(x)) {
        keys.push(x);
        holders.push('?');
        values.push(map[x]);
      }
    }

    query = query.replace("#k#", keys.join(','));
    query = query.replace("#v#", holders.join(','));

    return [query, values];
  }

  function updateSQL(table, map, conditions) {
    var query = "UPDATE " + table + " SET #k##m#", keys = [], values = [], x;

    for (x in map) {
      if (map.hasOwnProperty(x)) {
        keys.push(x + '=?');
        values.push(map[x]);
      }
    }

    conditions = buildConditions(conditions);

    values = values.concat(conditions[1]);

    query = query.replace("#k#", keys.join(','));
    query = query.replace("#m#", conditions[0]);

    return [query, values];
  }

  function selectSQL(table, columns, conditions, options) {
    var query = 'SELECT #col# FROM ' + table + '#cond#', values = [];

    if (typeof columns === 'undefined') {
      columns = '*';
    } else if (typeof columns === 'object') {
      columns.join(',');
    }

    conditions = buildConditions(conditions);

    values = values.concat(conditions[1]);

    query = query.replace("#col#", columns);
    query = query.replace('#cond#', conditions[0]);

    if (options) {
      if (options.limit) {
        query = query + ' LIMIT ?';
        values.push(options.limit);
      }
      if (options.order) {
        query = query + ' ORDER BY ?';
        values.push(options.order);
      }
      if (options.offset) {
        query = query + ' OFFSET ?';
        values.push(options.offset);
      }
    }

    query = query + ';';

    return [query, values];
  }

  function destroySQL(table, conditions) {
    var query = 'DELETE FROM ' + table + '#c#;';

    conditions = buildConditions(conditions);

    query = query.replace('#c#', conditions[0]);

    return [query, conditions[1]];
  }

  return {
    database: db,
    createTable: function (name, cols, data, error) {
      var sql = createTableSQL(name, cols);
      execute(sql[0], sql[1], data, error);
    },
    dropTable: function (name, data, error) { 
      var sql = dropTableSQL(name);
      execute(sql[0], sql[1], data, error);
    },
    insert: function (table, map, data, error) {
      var sql = insertSQL(table, map);
      execute(sql[0], sql[1], data, error);
    },
    update: function (table, map, conditions, data, error) {
      var sql = updateSQL(table, map, conditions);
      execute(sql[0], sql[1], data, error);
    },
    select: function (table, columns, conditions, options, data, error) {
      var sql = selectSQL(table, columns, conditions, options);
      execute(sql[0], sql[1], data, error);
    },
    destroy: function (table, conditions, data, error) {
      var sql = destroySQL(table, conditions);
      execute(sql[0], sql[1], data, error);
    }
  };
}

 

用法：

 

<!DOCTYPE html>

<html lang="en-us">
<head>
  <title>Test Suite for sqlite.js</title>
  <script src="sqlite.js" type="text/javascript"></script>
</head>
<body>
<div id="results"></div>
<script type="text/javascript" charset="utf-8">

  function pass(results, query) {
    var target = document.getElementById('results'), html = target.innerHTML;
    target.innerHTML = html + 'pass - ' + query + '<br />';
  }

  function fail(error, query) {
    var target = document.getElementById('results'), html = target.innerHTML;
    target.innerHTML = html + 'fail - ' + error.message + ': ' + query + '<br />';
  }

  var db = SQLite({ shortName: 'mydb' + parseInt(Math.random() * 100000), defaultErrorHandler: fail, defaultDataHandler: pass });

  db.createTable('people', 'name TEXT, age INTEGER');

  db.insert('people', { name: "Jeremy", age: 29 });
  db.insert('people', { name: "Tara", age: 28 });

  db.update('people', { age: 30 }, { name: 'Jeremy' });

  db.select('people', '*', { age: 30 }, null, function (r, q) { pass(r, q); var x; for(x=0; x<r.rows.length; x++) { console.log(r.rows.item(x)); } });
  db.select('people', 'name', null, { order: 'age DESC' }, function (r, q) { pass(r, q); var x; for(x=0; x<r.rows.length; x++) { console.log(r.rows.item(x)); } });
  db.select('people', 'name', null, { limit: 1 }, function (r, q) { pass(r, q); var x; for(x=0; x<r.rows.length; x++) { console.log(r.rows.item(x)); } });

  db.destroy('people', { age: 30 });
</script>
</body>
</html>