sqlite操作类（Js）

[javascript]代码库

/*************************************/
/* 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');
    }
});