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>