/*************************************/
/* 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[i].table + " (";
_field = tbl[i].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[i];
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[i].name + '`=?';
__values.push(clauses[i].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[i].name + '`="' + escape(clauses[i].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[i];
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[i];
__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[i];
__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');
}
});
原创文章，作者：ItWorker，如若转载，请注明出处：https://blog.ytso.com/tech/pnotes/9990.html