- /*************************************/
- /* 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');
- }
- });
来源: http://www.phpxs.com/code/1003865/