//sqlite库面向对象用法
import sqlite

//打开控制台
io.open();

//打开数据库连接
var sqlConn = sqlite("/db.db")
 
var dbtable = sqlConn.getTable("SELECT COUNT(*) FROM sqlite_master where type='table' and name='film';")
 

//遍列所有行记录  
if( not tonumber( dbtable[1]["COUNT(*)"] )  ){

	//创建表,因为 sqlConn.exec 内部已经检查了返回值,这里不需要再判断是否出错
	re,err = sqlConn.exec( "create table film(title, length, year, starring);")
	
 	//插入数据
	sqlConn.exec( "insert into film values ('Silence of the Lambs, The', 118, 1991, 'Jodie Foster');")
	sqlConn.exec( "insert into film values ('Contact', 153, 1997, 'Jodie Foster');")
	sqlConn.exec( "insert into film values ('Crouching Tiger, Hidden Dragon', 120, 2000, 'Yun-Fat Chow');")
}
else {
	sqlConn.exec(  "UPDATE film SET year=2010, length=112 WHERE year=1991") 
	if(! sqlConn.changes() ){
		//更新语句影响的行为为0,下面插入新的
		sqlConn.exec( "insert into film values ('Silence of the Lambs, The', 112, 2010, 'Jodie Foster');")
	}
}

//sql里的问号表示参数
stm = sqlConn.prepare("insert into film values (?,123, 1991, 'Jodie Foster');" )
//绑定一个参数,其实在AAuto里所有字符串都是blob,所以非常简单
stm.bind.blob( "用string.load读入二进制字符串" )//可以使用第二个参数指定是第几个问号
stm.step() //执行
stm.finalize() //释放命令对象

//迭代方式查询数据
for title, length, year, starring in sqlConn.each("select * from film") {
	io.print( title, length, year, starring  )
	break;
}
io.print("------------迭代数据 ok")

//遍历方式
sqlConn.enum( 
	"select * from film",
	function(tname,tvalue){
		for(i=1;#tname;1){ 
			io.print(tname[i],tvalue[i])
		}
		//return false; //返回false表示停止枚举
		 
	}
)	
io.print("------------枚举数据 ok")

dbtable = sqlConn.getTable("select * from film")
//遍列所有行记录
for(i=1;#dbtable ){

	//打印所有列名和值
	for(k,v in  dbtable[i]){
		io.print(k,v);
	
	} 
}
io.print("------------数据列表 ok") 

//删除表
sqlConn.exec("drop table film")
sqlConn.close()

