选择了sqlite作为sql语言入门练习的数据库，因而也选择了同样轻便的编程工具aauto，其封装了sqlite库，方便好用且很快写出一个带界面的小工具，有兴趣的osc友只需到官网下载快手开发工具，解压缩，新建工程运行以下代码即可看到效果。 透过编写这个工具了解及
选择了sqlite作为sql语言入门练习的数据库， 因而也选择了同样轻便的编程工具aauto ，其封装了sqlite 库，方便好用且很快写出一个带界面的小工具 ，有兴趣的osc友只需到官网下载快手开发工具，解压缩，新建工程运行以下代码即可看到效果。
透过编写这个工具了解及学习了以下知识：
1.sql基本语言, sqlite数据库创建、数据增加、修改、删除及模糊查询，命名参数运用。
2.列表视图listview控件的简单操作运用。
3.windows控件、窗体的命令响应、通知，消息循环等知识。
4.aauto的编程语言的数据类型、名字空间等特点。
sqlite aauto quicker
import win.ui;
import crreadb;
/*dsg{{*/
var winform = ..win.form(text="快手通讯录 by 菲菲osc";right=558;bottom=427;parent=...)
winform.add(
button={cls="button";text="添加记录";left=19;top=19;right=100;bottom=48;z=1};
button2={cls="button";text="编辑记录";left=115;top=19;right=196;bottom=48;z=2};
button3={cls="button";text="删除记录";left=211;top=19;right=292;bottom=48;z=3};
button4={cls="button";text="查找";left=476;top=22;right=536;bottom=48;z=5};
dress={cls="edit";left=375;top=152;right=541;bottom=176;edge=1;z=9};
edit={cls="edit";text="输入查找条件...";left=318;top=21;right=457;bottom=48;edge=1;z=4};
emal={cls="edit";left=375;top=193;right=541;bottom=217;edge=1;z=10};
listview={cls="listview";left=19;top=69;right=292;bottom=408;bgcolor=16777215;edge=1;fullrow=1;z=6};
mark={cls="richedit";text="备注";left=313;top=280;right=543;bottom=408;edge=1;hscroll=1;multiline=1;vscroll=1;z=17};
name={cls="edit";left=375;top=71;right=541;bottom=95;edge=1;z=7};
qq={cls="edit";left=375;top=234;right=541;bottom=258;edge=1;z=11};
static={cls="static";text="姓名";left=319;top=69;right=359;bottom=93;transparent=1;z=12};
static2={cls="static";text="电话";left=319;top=110;right=359;bottom=134;transparent=1;z=13};
static3={cls="static";text="地址";left=319;top=152;right=359;bottom=176;transparent=1;z=14};
static4={cls="static";text="邮箱";left=319;top=193;right=359;bottom=217;transparent=1;z=15};
static5={cls="static";text="qq/msn";left=319;top=234;right=359;bottom=258;transparent=1;z=16};
tel={cls="edit";left=375;top=112;right=541;bottom=136;edge=1;z=8}
)
/*}}*/
var sqlconnection = sqlite("\res\contact.db")
crreadb.careatable(sqlconnection)
// 显示数据
var showdata = function(sql){
for id,姓名,电话, 地址,邮箱,qq ,备注 in sqlconnection.each("select rowid,* from tl") {
winform.listview.additem({tostring(id);姓名;电话; 地址;邮箱;qq;备注})
}
}
winform.listview.insertcolumn("id",1)
winform.listview.insertcolumn("姓名",100)
winform.listview.insertcolumn("电话",100)
winform.listview.insertcolumn("地址",120)
winform.listview.insertcolumn("邮箱",120)
winform.listview.insertcolumn("qq",80)
winform.listview.insertcolumn("备注",150)
showdata()
winform.show()
winform.button.oncommand = function(id,event){
// 添加
var name = winform.name.text
var tel = winform.tel.text
var dress = winform.dress.text
var em = winform.emal.text
var qq = winform.qq.text
var mark = winform.mark.text
crreadb.adddata(sqlconnection,name,tel,dress,em,qq,mark)
id = sqlconnection.lastinsertrowid()
var data = sqlconnection.stepquery("select rowid,* from [tl] where rowid = "+id)
winform.listview.additem({tostring(data.id);data.name;data.tel;data.dress;data.em;data.qq;data.mark})
}
winform.button3.oncommand = function(id,event){
// 删除
var coun = winform.listview.selindex
var id = winform.listview.getitemtext(coun)
if(id){
crreadb.deldata(sqlconnection,id)
winform.listview.clear()
showdata()
winform.listview.selindex = coun
}
else {
winform.msgbox("请选择要删除的列")
}
}
winform.button2.oncommand = function(id,event){
// 编辑修改
crreadb.editdata(sqlconnection,name,tel,dress,em,qq,mark,id)
}
winform.button4.oncommand = function(id,event){
// 查询
var vv = winform.edit.text
var id = crreadb.inquiry(sqlconnection,vv)
winform.listview.clear()
if(id){
for(i=1;#id;1){
winform.listview.additem({tostring(id[i].rowid);id[i].name;id[i].tel; id[i].dress;id[i].em;id[i].qq;id[i].mark})
}
}
else {
winform.msgbox("找不到记录")
}
}
winform.edit.wndproc = function(hwnd,message,wparam,lparam){
if( message = 0x202/*_wm_lbuttonup*/ ) winform.edit.text = ""
}
winform.listview.onnotify = function(id,code,ptr){
select(code) {
case 0xffffff9b/*_lvn_itemchanged*/ {
if(winform.listview.selindex){
var lvw = winform.listview
winform.name.text = lvw.getitemtext(lvw.selindex,2)
winform.tel.text = lvw.getitemtext(lvw.selindex,3)
winform.dress.text = lvw.getitemtext(lvw.selindex,4)
winform.emal.text = lvw.getitemtext(lvw.selindex,5)
winform.qq.text = lvw.getitemtext(lvw.selindex,6)
winform.mark.tex = lvw.getitemtext(lvw.selindex,7)
}
}
}
}
win.loopmessage();
sqlconnection.exec("vacuum")
sqlconnection.close()
return winform;
namespace crreadb;
import sqlite;
careatable = function(db){
if( not db.existstable("tl") ){
//创建表
db.exec( "create table tl(
name,
tel ,
dress,
em,
qq ,
mark
);"
)
}
}
// 添加数据
adddata = function(db,name,tell,dress,em,qq,mark){
var command = db.prepare("insert into [tl] values ( @name,@tel,@dress,@em,@qq,@mark );" )
command.bind.parameteratnames(
name = name;
tel = tell;
dress = dress;
em = em;
qq = qq;
mark = mark
).step()
command.finalize()
}
// 删除
deldata = function(db,id){
db.exec("delete from [%s] where rowid=%d;","tl",id)
}
// 修改
editdata = function(db,name,tel,dress,em,qq,mark,id){
db.exec("update [tl] set name = @name,tel=@te1 ,dress=@dress,em=@em,qq=@qq,mark=@mark where rowid = @id;",{
name = name;
tel = tel;
dress = dress;
em = em;
qq = qq;
mark = mark;
id = id
});
}
// 模糊查询
inquiry = function(db,re){
var tab = db.gettable("select rowid,* from [tl] where ifnull(name,'') || ifnull(tel,'') || ifnull(qq,'') like '%"+ re+ "%'")
return tab
}