//sqlite面向对象用法

import sqlite

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

//打开数据库连接
var sqlConnection = sqlite("/db.db")  
if( not sqlConnection.existsTable("film") ){

	//创建表
	sqlConnection.exec( "create table film(
		title, 
		length, 
		year, 
		starring
		);"
	)
	
 	//插入数据
	sqlConnection.exec( "insert into film values ('Silence of the Lambs, The', 11.8, datetime('now','localtime'),'Jodie Foster' );")
	sqlConnection.exec( "insert into film values ('Contact', 153, 1997, 'Jodie Foster');")
	sqlConnection.exec( "insert into film values ('Crouching Tiger, Hidden Dragon', 120, 2000, 'Yun-Fat Chow');")
	
	io.print("最后插入ID",sqlConnection.last_insert_rowid())
}
else {
	sqlConnection.exec(  "UPDATE film SET year=2010, length=112 WHERE year=1991") 
	if( ! sqlConnection.changes() ){
		//更新语句影响的行为为0,下面插入新的
		sqlConnection.exec( "insert into film values ('Silence of the Lambs, The', 112, 2010, 'Jodie Foster');")
	}
	
	io.print("最后插入ID",sqlConnection.last_insert_rowid())
}

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

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

//枚举方式 注意枚举返回的数据都是文本类型.
sqlConnection.enum( 
	"select * from film",
	function(tName,tValue){
		for(col=1;#tName;1){ 
			io.print(tName[col],tValue[col])
		}
		//return false; //返回false表示停止枚举 
	}
)	
io.print("------------枚举数据 ok")

dbTable = sqlConnection.getTable("select * from film")
//遍列所有行记录
for(row=1;#dbTable ){

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

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

execute("pause") //按任意键继续
io.close();//关闭控制台

