另一个SQLite操作类（JavaScript）

huiselele

　　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>