一个SQLite操作类(JS)

mouse

　　源码：
　　


/*************************************/
/* Helman, heldes.com      */
/* helman at heldes dot com    */
/* sqlitedb.js           */
/* SQLite Database Class For HTML5 */
/*************************************/
function cDB(confs){
var ret = {
_db: null,
_response: null,
_error: null,
check : function(tbl){
if(!this._db) return false;
var _sql = '', _sqlField='', _field=[];
for(var i=0;i<tbl.length;i++){
_sql = "CREATE TABLE IF NOT EXISTS "+tbl.table+" (";
_field = tbl.properties;
_sqlField = '';
for (var j=0;j<_field.length;j++){
_sqlField += ',`'+_field[j].name+'` '+_field[j].type;
}
_sql += _sqlField.substr(1)+");";
this.query(_sql,null,null,null);
}
return true;
},
getResult:function(){
return this._response;
},
getError:function(){
return this._error;
},
callback_error: function(tx,_er){
var err = '';
if(typeof(tx) == 'object'){
for(var q in tx){
err += q+' = "'+tx[q]+'"; ';
}
}else{
err += tx+'; ';
}
if(typeof(_er) == 'object'){
for(var q in _er){
err += q+' = "'+_er[q]+'"; ';
}
}else if(typeof(_er) == 'undefined'){
err += _er+'; ';
}
console.log(err);
//if(callback) callback();
return false;
},
query: function(sql,callback,params,er){
if(!this._db) return false;
var self = this;
function _er(tx,__er){
__er = jQuery.extend(__er,{sql:sql});
if(er) er(tx,__er);
else self.callback_error(tx,__er);
};
this._db.transaction(function(tx){
tx.executeSql(sql,(params?params:[]),callback,_er);
}, _er);
},
update:function(tbl,sets,clauses,callback){
var __sql = 'UPDATE '+tbl, _field = null, __set = '', __clause = '',__values=[];
for(var i=0;i<sets.length;i++){0
_field = sets;
for(var j=0;j<_field.length;j++){
__set += ',`'+_field[j].name+'`=?';
__values.push(_field[j].value);
}
}
for(var i=0;i<clauses.length;i++){
__clause += ',`'+clauses.name+'`=?';
__values.push(clauses.value);
}
__sql += ((__set!='')?' SET '+__set.substr(1):'')+((__clause!='')?' WHERE '+__clause.substr(1):'')+';';
this.query(__sql,callback,__values);
return true;
},
remove:function(tbl,clauses){
var __sql = 'DELETE FROM '+tbl, __clause = '';
for(var i=0;i<clauses.length;i++)
__clause += ',`'+clauses.name+'`="'+escape(clauses.value)+'"';
__sql += ' WHERE '+((__clause!='')?__clause.substr(1):'FALSE')+';';
this.query(__sql);
return true;
},
multiInsert: function(tbl,rows,callback,er){
if(!this._db) return false;
var self = this;
var __sql = '', _field = null, __field = '', __qs = [], __values = [];
this._db.transaction(function(tx){
for(var i=0;i<rows.length;i++){
__qs = [];
__values = [];
__field = '';
_field = rows;
for(var j=0;j<_field.length;j++){
__field += ',`'+_field[j].name+'`';
__qs.push('?');
__values.push(_field[j].value);
}
tx.executeSql('INSERT INTO '+tbl+' ('+__field.substr(1)+') VALUES('+__qs.join(',')+');',__values,function(){return false;},(er ? er : self.callback_error));
}
}, self.callback_error, function(){
if(callback) callback();
return true;
});
return true;
},
insert:function(tbl,rows,callback){
var __sql = '', _field = null, __field = '', __qs = [], __values = [], __debug = '';
for(var i=0;i<rows.length;i++){
__qs = [];
__field = '';
_field = rows;
__debug += _field[0].name+' = '+_field[0].value+';';
for(var j=0;j<_field.length;j++){
__field += ',`'+_field[j].name+'`';
__qs.push('?');
__values.push(_field[j].value);
}
__sql += 'INSERT INTO '+tbl+' ('+__field.substr(1)+') VALUES('+__qs.join(',')+');';
}
this.query(__sql,callback,__values);
return true;
},
insertReplace:function(tbl,rows,debug){
var __sql = '', _field = null, __field = '', __qs = [], __values = [], __debug = '';
for(var i=0;i<rows.length;i++){
__qs = [];
__field = '';
_field = rows;
__debug += _field[0].name+' = '+_field[0].value+';';
for(var j=0;j<_field.length;j++){
__field += ',`'+_field[j].name+'`';
__qs.push('?');
__values.push(_field[j].value);
}
__sql += 'INSERT OR REPLACE INTO '+tbl+' ('+__field.substr(1)+') VALUES('+__qs.join(',')+');';
}
this.query(__sql,null,__values);
return true;
},
dropTable:function(tbl,callback){
var __sql = '';
if(tbl==null) return false;
__sql = 'DROP TABLE IF EXISTS '+tbl;
this.query(__sql,callback);
return true;
}
}
return jQuery.extend(ret,confs);
}
　　使用方法：
　　


/*=======================================*/
创建数据库：
/* Create or open database with 'websiteDB' as database name and 'website DB' as title, and database site is 5MB */
/* I'm not using 1024 for the size multiplying because i don't want to be near at the margin size                          */
var db = new cDB({_db:window.openDatabase("websiteDB", "", "website DB";, 5*1000*1000)});
/*=======================================*/
建表:
/* dbTable is database structure in this example, and contains 2 tables 'foo' and 'boo' */
/* and also the table structure in table properties                                                           */
var dbTable = [
{table:'foo',properties: [
{name:'foo_id', type: 'INT PRIMARY KEY ASC'},
{name:'foo_field_1', type: ''},
{name:'foo)field_2', type: ''}
]},
{table:'boo',properties: [
{name:'boo_id', type: 'INT PRIMARY KEY ASC'},
{name:'boo_field_1', type: ''},
{name:'boo_field_2', type: ''}
]}
];
/* this line is checking if the database exist or not and then create the database structure.  */
/* table will be created if the table is not exist yet, if the table already exist, it will skip the */
/* table and continue with others tables                                                                                  */
if(!db.check(dbTable)){
db = false;
alert('Failed to cennect to database.');
}
/*=======================================*/
删除表：
db.dropTable('foo');
/*=======================================*/
插入数据：
var row = [];
row.push([
{'name':'foo_id','value':1},
{'name':'foo_field_1','value':'value 1 field_1'},
{'name':'foo_field_2','value':'value 1 field_2']}
]);
db.insert('foo',row);

插入多行记录：
/*
SQLite is not accepting more than 1 line statement,
that is the reason why we not able to do more than one statement query, like insertion.
If you want to insert more than 1 record at the time, you need to use this function.
*/
var rows = [];
rows.push([
{'name':'boo_id','value':1},
{'name':'boo_field_1','value':'value 1 field_1'},
{'name':'boo_field_2','value':'value 1 field_2']}
]);
rows.push([
{'name':'boo_id','value':2},
{'name':'boo_field_1','value':'value 2 field_1'},
{'name':'boo_field_2','value':'value 2 field_2']}
]);
db.multiInsert('boo',rows,function(){alert('insertion done');});
/*
如果想合并insert 和 multiInsert两个函数，可以按下面的方法增加一个判断来处理
*/
if(rows.length>=2){
db.multiInsert('boo',rows,function(){alert('insertion done');});
}else{
db.insert('boo',rows);
}
/*=======================================*/
删除数据：
db.remove('boo',[{'name':'boo_id','value':1}])
/*=======================================*/
更新数据
db.update('boo',[[
{'name':'boo_id','value':2},
{'name':'boo_field_1','value':'boo value'}
]],['name':'boo_id','value':2])
/*=======================================*/
查询
var query = 'SELECT * FROM foo';
db.query(query,function(tx,res){
if(res.rows.length){
alert('found '+res.rows.length+' record(s)');
}else{
alert('table foo is empty');
}
});