sqlite入门练习,通讯录增删改查

选择了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
}