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
}