设为首页 收藏本站
查看: 578|回复: 0

[经验分享] 另一个SQLite操作类(JavaScript)

[复制链接]

尚未签到

发表于 2016-11-30 09:04:33 | 显示全部楼层 |阅读模式
  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>

运维网声明 1、欢迎大家加入本站运维交流群:群②:261659950 群⑤:202807635 群⑦870801961 群⑧679858003
2、本站所有主题由该帖子作者发表,该帖子作者与运维网享有帖子相关版权
3、所有作品的著作权均归原作者享有,请您和我们一样尊重他人的著作权等合法权益。如果您对作品感到满意,请购买正版
4、禁止制作、复制、发布和传播具有反动、淫秽、色情、暴力、凶杀等内容的信息,一经发现立即删除。若您因此触犯法律,一切后果自负,我们对此不承担任何责任
5、所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其内容的准确性、可靠性、正当性、安全性、合法性等负责,亦不承担任何法律责任
6、所有作品仅供您个人学习、研究或欣赏,不得用于商业或者其他用途,否则,一切后果均由您自己承担,我们对此不承担任何法律责任
7、如涉及侵犯版权等问题,请您及时通知我们,我们将立即采取措施予以解决
8、联系人Email:admin@iyunv.com 网址:www.yunweiku.com

所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其承担任何法律责任,如涉及侵犯版权等问题,请您及时通知我们,我们将立即处理,联系人Email:kefu@iyunv.com,QQ:1061981298 本贴地址:https://www.yunweiku.com/thread-307473-1-1.html 上篇帖子: sqlite数据库操作基础(一) 下篇帖子: Android开发之SQLite技术详解
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

扫码加入运维网微信交流群X

扫码加入运维网微信交流群

扫描二维码加入运维网微信交流群,最新一手资源尽在官方微信交流群!快快加入我们吧...

扫描微信二维码查看详情

客服E-mail:kefu@iyunv.com 客服QQ:1061981298


QQ群⑦:运维网交流群⑦ QQ群⑧:运维网交流群⑧ k8s群:运维网kubernetes交流群


提醒:禁止发布任何违反国家法律、法规的言论与图片等内容;本站内容均来自个人观点与网络等信息,非本站认同之观点.


本站大部分资源是网友从网上搜集分享而来,其版权均归原作者及其网站所有,我们尊重他人的合法权益,如有内容侵犯您的合法权益,请及时与我们联系进行核实删除!



合作伙伴: 青云cloud

快速回复 返回顶部 返回列表