操作sqlite的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'); } });</pre>