逍遥阁

HTML5之JavaScript操作sqlite的类库

关键词: sqlite

HTML5之JavaScript操作sqlite的类库

该代码几年前编写，不符合编码规范，请自己优化

```
<!doctype html>
<html>
<head>
<meta http-equiv="content-type" content="text/html;charset=utf-8">
<title>sqlite类库</title>
</head>
<body>
<script type="text/javascript" language="javascript" charset="utf-8">
function sqlite(database,version,note,size){
    var db=openDatabase(database,version,note,size);

function make_field(field){
        var data=[];
        for(var key in field){data.push("'"+key+"'"+" "+field[key]);}
        return "("+data.join(",")+")";
    }

function make_update(kv){
        var data=[];
        for(var key in kv){data.push(key+"='"+kv[key]+"'");}
        return data.join(",");
    }

var r={};

r.query=function(sql,callback){
        var object=this;
        db.transaction(function(tx){tx.executeSql(sql,[],cb);});
        function cb(tx,rs){
            if(callback){
                rs.sql=sql;
                callback.call(object,rs);
            }
        }
    };

r.ct=function(table,field,callback){
        var object=this;
        var sql="create table if not exists "+table+make_field(field);
        object.query(sql,callback);
    };

r.rt=function(ot,nt,callback){
        var object=this;
        var sql="alter table "+ot+" rename to "+nt;
        object.query(sql,callback);
    };

r.dt=function(table,callback){
        var object=this;
        var sql="drop table if exists "+table;
        object.query(sql,callback);
    };

r.gt=function(callback){
        var object=this;
        var sql="select name from sqlite_master where type='table' and sql<>'' and name not in ('__WebKitDatabaseInfoTable__','sqlite_autoindex___WebKitDatabaseInfoTable___1','sqlite_sequence','sqlite_stat1','sqlite_stat2','sqlite_stat3')";
        object.query(sql,cb);
        function cb(rs){
            var list=[];
            for(var i=0;i<rs.rows.length;i++){list.push(rs.rows.item(i).name);}
            callback.call(object,list);
        }
    };

r.gts=function(table,callback){
        var object=this;
        var sql="select sql from sqlite_master where type='table' and name='"+table+"'";
        object.query(sql,cb);
        function cb(rs){
            callback.call(object,rs.rows.item(0).sql);
        }
    };

r.insert=function(table,kv,callback){
        var object=this;
        var tmp=make_insert(kv);
        var sql="insert into "+table+"("+tmp.key+") values("+tmp.value+")";
        object.query(sql,callback);
    };

r.update=function(table,kv,id,callback){
        var object=this;
        var tmp=make_update(kv);
        var sql="update "+table+" set "+tmp+" where id="+id;
        object.query(sql,callback);
    };

r.delete=function(table,id,callback){
        var object=this;
        var sql="delete from "+table+" where id in ("+id+")";
        object.query(sql,callback);
    };

r.gv=function(callback){
        var object=this;
        var sql="select sqlite_version(*) as version";
        object.query(sql,cb);
        function cb(rs){
            callback.call(object,rs.rows.item(0).version);
        }
    };

r.af=function(table,field,type,callback){
        var object=this;
        var sql="alter table "+table+" add column '"+field+"' "+type;
        object.query(sql,callback);
    };

r.gf=function(table,callback){
        var object=this;
        object.gts(table,cb);
        function cb(sql){
            var reg=sql.match(/\(.*\)/);
            reg=reg[0].slice(1,reg[0].length-1).split(",");
            var list=[];
            for(var i=0;i<reg.length;i++){
                var tmp=reg[i].split(" ");
                tmp=tmp[0].replace('"','').replace("'","");
                if(tmp!=""){list.push(tmp);}
            }
            callback.call(object,list);
        }
    };

return r;
}

//实例化
var db=new sqlite("xxfaxy","1.0","www.zhouchun.net",1073741824);

//获取版本
db.gv(function(version){alert("版本是:"+version);});

//创建表
//db.ct("media",{id:"integer primary key autoincrement",name:"text"});

//重命名表
//db.rt("movie","media");

//删除表
//db.dt("media");

//获取表
//db.gt(function(list){console.log(list);});

//获取表结构
//db.gts("media",function(list){console.log(list);});

//添加字段
//db.af("media","sex","text");

//获取字段
//db.gf("media",function(list){console.log(list);});

//插入数据
//db.insert("media",{name:"xiaoyaoge",sex:"male"});

//更新数据
//db.update("media",{name:"97media.net"},1);

//删除数据
//db.delete("media","1,2,3,4,5,6,7,8");

/*
[知识点]
回调函数有一个参数，假设为rs
取得insert操作最后的id号用rs.insertId
取得insert|update|delete操作影响的行数用rs.rowsAffected
取得select操作记录的条数用rs.rows.length
读取数据用r.rows.item(i).字段名
[其他]
代码在chrome 26.0.1410.64 下通过
根据两年多的开发经验，上面的类库完全够用
默认表有一个是主键且自增的字段id
更新数据按id更新1条，我一直这么用
删除数据按id列表删除1条或多条，我一直这么用
其他更多功能自己拓展
本文由xxfaxy原创 www.zhouchun.net
*/
</script>
</body>
</html>
```

admin
2017-02-19 22:01:25
javascript

文章分类

文章标签

文章归档

友情链接

HTML5之JavaScript操作sqlite的类库