/*************************************/
/* 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);
            }
            
            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 : []),_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,__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,er) {
            if (!this._db)
                return false;
            var self = this;
            var __sql = '',__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,callback) {
            var __sql = '',__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,insertReplace : function (tbl,debug) {
            var __sql = '',`' + _field[j].name + '`';
                    __qs.push('?');
                    __values.push(_field[j].value);
                }
                __sql += 'INSERT OR REPLACE INTO ' + tbl + ' (' + __field.substr(1) + ') VALUES(' + __qs.join(',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',{
                name : 'boo_field_1',{
                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',{
            'name' : 'boo_field_1',{
            'name' : 'boo_field_2','value' : 'value 1 field_2']
        }
    ]);
rows.push([{
            'name' : 'boo_id','value' : 2
        },'value' : 'value 2 field_1'
        },'value' : 'value 2 field_2']
        }
    ]);
db.multiInsert('boo',function () {
    alert('insertion done');
});

/*
如果想合并insert 和 multiInsert两个函数，可以按下面的方法增加一个判断来处理
 */

if (rows.length >= 2) {
    db.multiInsert('boo',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');
    }
});