//sqlite库源代码
//修改源代码以后,在快手中点击"编译(F8)",存为"快手目录/lib/sqlite/_.aau" 即可

import raw; 

 //这里我是直接包含该dll，编译后可以脱离dll运行
//也可以重新编译该sqlite3.dll，将aau文件放到"LIB->IMPORT"的资源中，用DLL直接作为库文件
var dll = raw.loadDll($"/.res/sqlite3.dll"); 

class sqlite{
	ctor( filename ){
		this.db = open(filename)
	};
	close = function(){
		close( this.db);
		this.db = null;
	}
	exec = function(sql,...){
		sql = ..string.fromto(sql,0,65001)
		var re,err = exec(this.db,sql,...);
		self._assertf(err : ret);
		return re;
	} 
	changes = function(){
		return changes(this.db);
	}
	
	enum = function(sql,proc){
		sql = ..string.fromto(sql,0,65001);
		
		//第2个参数代表数据结果集中有几列数据
		//第3个参数代表列数据的字符串数组，第4个参数代表列名的字符串数组
		var callback = function(notused,argc,value,name){ 
			tname = ..raw.toarray( argc,"string" )
			tvalue = ..raw.toarray( argc,"string" )
			..raw.convert( name, tname);
			..raw.convert( value, tvalue); 
			 
			return proc(tname.array,tvalue.array,argc);
		} 
		
		//创建回调函数 cdcel调用约定
		callback_c = ..raw.tocdecl( callback,"int(pointer,int,pointer argv,pointer azColName)") 
		ret,err = exec(this.db,sql,callback_c, /*第4个参数是个随意指针，用途可用户指定，它将传给回调函数的第1个参数*/  );
		self._assertf(err : ret);
		
	}
	prepare = function(sql){
		sql = ..string.fromto(sql,0,65001)
		var ret,pstmt =prepare(this.db, sql, #sql ); 
		self._assertf(ret)  //检查API调用是否正确
		return {
			step = function(){
				..sqlite.step(pstmt);
			}
			prepare = function(){
				var ret,pstmt =prepare(this.db, sql, #sql,pstmt ); 
				self._assertf(ret)  //检查API调用是否正确
			}
			finalize = function(){
				finalize(pstmt);
			}
			reset = function(){
				reset(pstmt)
			}
			bind ={
				//ind表示?号的位置
				blob = function( str,ind=1){
					bind.blob(pstmt,ind,str,#str);
				}
				double = function( n,ind=1){
					bind.double(pstmt,ind,n);
				} 
				int = function( n,ind=1){
					bind.int(pstmt,ind,n);
				} 
				long64 = function( n,ind=1){
					bind.long64(pstmt,ind,n);
				} 
				nil = function( ind=1){
					bind.nil(pstmt,ind);
				} 
				text = function( str,ind=1){
					bind.text(pstmt,ind,str,#str);
				}
				text16 = function( str,ind=1){
					bind.text16(pstmt,ind,str,#str);
				} 
				parameter_index = function(cmd){
					bind.parameter_index(pstmt,cmd);
				}
				
			}
			
		}
		
	}  
	each = function(sql){
		sql = ..string.fromto(sql,0,65001);
		
		//pstmt相当于ODBC的Command对象，用于保存编译好的SQL语句
		var ret,pstmt = prepare(this.db, sql, #sql );
		self._assertf(ret)  //检查API调用是否正确
		var name,value,len;
		return function(){
			var ret = step(pstmt);  
			if( ret !=  ROW ){   
				return;
			}
				
			var tab ={} 
			while( 1 ){
				value = column.text(pstmt, #tab);
				if(!value)
					break; 
				..table.push(tab, value ); 
			} 
			return ..table.unpack(tab);
		},function(){
			finalize(pstmt); 
		}
		
		
	}
	getTable = function(sql){
		sql = ..string.fromto(sql,0,65001);
		var re,result,row,col,err = table.get(this.db, sql,  , 0, 0 );
		self._assertf(err : ret) 
		
		var result_c = ..raw.toarray( (row+1)*col,"string" );
		..raw.convert(result,result_c);//转换指针为动态数组
		
		var tab ={}
		for( r=1;row ){ 
			tab[r] = {}
    			for( c=1;col ) {
    				tab[r][ result_c.array[c] ] = result_c.array[r*col + c]; 
    			}
		}
		table.free(result);
		return tab;
	}
	
}

/**intellisense()
!sqlConn.each = @for 字段名,字段名2 starring in sqlConn.each("select * from table") {
	io.print( 字段名,字段名2 )
}
!sqlConn.enum(sql) = @.enum( \n	__/*sql*/,\n	function(tname,tvalue){\n		for(i=1;#tname;1){ \n			io.print(tname[i],tvalue[i])\n		}\n		 \n	}\n)
!sqlConn.getTable(__/*sql*/) = 返回表\n包含行记录组成的数组\n每行数组是由列名、值组成的哈希表
!sqlConn.exec(__/*sql*/) = 执行sql语句
!sqlConn.db = 当前打开的数据库连接对象
!sqlConn.changes() = 返回数据库最近一次运行exec()所改变的行数
!stmt.bind.parameter_index("__/*参数名*/") = 绑定参数名到索引
!stmt.bind.blob(__/*二进制字符串*/) = 绑定一个参数,参数在sql语句中用问号表示\n可使用第二个参数指定问号的位置
!stmt.bind.double(__/*浮点数值*/) = 绑定一个参数,参数在sql语句中用问号表示\n可使用第二个参数指定问号的位置
!stmt.bind.int(__/*数值*/) = 绑定一个参数,参数在sql语句中用问号表示\n可使用第二个参数指定问号的位置
!stmt.bind.long64(__/*64位数值*/) = 绑定一个参数,参数在sql语句中用问号表示\n可使用第二个参数指定问号的位置
!stmt.bind.nil() = 绑定一个参数,参数在sql语句中用问号表示\n可使用第二个参数指定问号的位置
!stmt.bind.text(__/*UTF8编码文本*/) = 绑定一个参数,参数在sql语句中用问号表示\n可使用第二个参数指定问号的位置
!stmt.bind.text16(__/*UTF16编码文本*/) = 绑定一个参数,参数在sql语句中用问号表示\n可使用第二个参数指定问号的位置
!stmt.prepare(__/*sql*/) = 添加SQL指令
!stmt.finalize() = 每一个预处理语句都必须调用这个接口进行销毁以避免内存泄漏
!stmt.reset() = 重置该预处理语句并继续解析执行
?.prepare =!stmt.
!sqlConn.prepare(__/*sql*/) = 创建sql命令对象
!sqlConn.close() = 关闭数据库连接
?sqlite = !sqlConn.
sqlite(":memory:") = 创建内存数据库
sqlite("\数据库路径") = 创建或打开数据库
end intellisense**/


namespace sqlite;

//打开关闭数据库
sqlite3_open = dll.api("sqlite3_open","int(STRING filename,pointer &ppDB)","cdecl");

open = function(filename){
	filename = ..io.localpath(filename) : filename
	filename = ..string.fromto(filename,0,65001) //sqllite内部使用UTF8编码,如果路径包含中文名,要转换为UTF8
	return self._assertf( sqlite3_open( filename ) );//如果库不存在，会自动创建文件 
}

err_message ={
[0] = "成功执行";
[1] = "SQL错误或数据库不存在";
[2] = "SQLite内部逻辑错误（没有使用）";
[3] = "访问许可禁止";
[4] = "回调函数请求中止";
[5] = "数据库文件被锁定";
[6] = "数据库中的一表被锁定";
[7] = "malloc()分配堆失败";
[8] = "企图写只读数据库";
[9] = "操作被sqlie3_interrupt()中止";
[10] = "磁盘I=O错误";
[11] = "数据库文件变形";
[12] = "表或纪录没有找到（没有使用）";
[13] = "插入失败因为数据库满";
[14] = "不能打开数据库文件";
[15] = "数据库锁协议错误";
[16] = "数据库是空的";
[17] = "数据库模式改变";
[18] = "单行数据过多（没有使用）";
[19] = "因约束违背而中止";
[20] = "数据类型不匹配";
[21] = "库使用方法不当";
[22] = "主机不支持库中操作系统功能";
[23] = "认证禁止";
[24] = "附属数据库格式错误";
[25] = "sqlite3_bind()第2个参数超出范围";
[26] = "打开非数据库文件";
[100] = "sqlite3_step()有另一行数据就绪";
[101] = "sqlite3_step()完成执行，已无数据";
}
self.getErrMsg = function(err){
	if(!err)return;
	var errmsg
	if(type(err)==type.pointer){
		errmsg = ..raw.tostring(err)
		free(err);
	}
	else{
		errmsg = err_message[errcode]
	}
	errmsg := "未知错误";
	return errmsg
}

self.assertf = function(err,...){ 
	if(!err)
		return ... 
	error( getErrMsg(err) ,2);
}
self._assertf = function(err,...){ 
	if(!err)
		return ... 
	error( getErrMsg(err) ,3);
}



/**intellisense(sqlite)
assertf(__/*调用sqlite API函数*/) = 第一个参数是API返回的状态值,或错误信息指针\n如果发生错误则抛出异常终断程序,并显示错误信息
_assertf(__/*调用sqlite API函数*/) = 在sqlite库内部使用的断言函数,会向更上层的调用抛出异常
open("/__/*数据库路径*/") = 可以是指定不存在的数据库文件路径\nsqlite会自动创建文件.
end intellisense**/



/*intellisense(sqlite)*/ 
OK=0 //成功执行
ERROR=1 //SQL错误或数据库不存在
INTERNAL=2 //SQLite内部逻辑错误（没有使用）
PERM=3 //访问许可禁止
ABORT=4 //回调函数请求中止
BUSY=5 //数据库文件被锁定
LOCKED=6 //数据库中的一表被锁定
NOMEM=7 //malloc()分配堆失败
READONLY=8 //企图写只读数据库
INTERRUPT=9 //操作被sqlie3_interrupt()中止
IOERR=10 //磁盘I=O错误
CORRUPT=11 //数据库文件变形
NOTFOUND=12 //表或纪录没有找到（没有使用）
FULL=13 //插入失败因为数据库满
CANTOPEN=14 //不能打开数据库文件
PROTOCOL=15 //数据库锁协议错误
EMPTY=16 //数据库是空的
SCHEMA=17 //数据库模式改变
TOOBIG=18 //单行数据过多（没有使用）
CONSTRAINT=19 //因约束违背而中止
MISMATCH=20 //数据类型不匹配
MISUSE=21 //库使用方法不当
NOLFS=22 //主机不支持库中操作系统功能
AUTH=23 //认证禁止
FORMAT=24 //附属数据库格式错误
RANGE=25 //sqlite3_bind()第2个参数超出范围
NOTADB=26 //打开非数据库文件
ROW=100 //sqlite3_step()有另一行数据就绪
DONE=101 //sqlite3_step()完成执行，已无数据

close = dll.api("sqlite3_close","int(pointer db)","cdecl");

//获取释放错误信息
errmsg = dll.api("sqlite3_errmsg","string(pointer db)","cdecl");
free = dll.api("sqlite3_free","void(pointer errmsg)","cdecl");

//使用回调函数查询数据库
exec = dll.api("sqlite3_exec","int(pointer db,STRING sql,pointer callback,pointer callback_arg,pointer &)","cdecl");
changes = dll.api("sqlite3_changes","int(pointer db)","cdecl");


//不使用回调函数查询数据库 方法之一
prepare= dll.api("sqlite3_prepare","int(pointer db,string szSql,int nByte,pointer &stmt,pointer& pzTail)","cdecl");
prepare2= dll.api("sqlite3_prepare_v2","int(pointer db,string szSql,int nByte,pointer &stmt,pointer& pzTail)","cdecl");
step= dll.api("sqlite3_step","int(pointer stmt)","cdecl");
finalize= dll.api("sqlite3_finalize","int(pointer stmt)","cdecl");
reset= dll.api("sqlite3_reset","int(pointer stmt)","cdecl");

/*end intellisense*/

namespace column{
	/*intellisense(sqlite.column)*/
	blob= dll.api("sqlite3_column_blob","pointer(pointer stmt, int iCol)","cdecl");
	bytes= dll.api("sqlite3_column_bytes","int(pointer stmt, int iCol)","cdecl");
	bytes16= dll.api("sqlite3_column_bytes16","int(pointer stmt, int iCol)","cdecl");
	double= dll.api("sqlite3_column_double","double(pointer stmt, int iCol)","cdecl");
	int= dll.api("sqlite3_column_int","int(pointer stmt, int iCol)","cdecl");
	long64= dll.api("sqlite3_column_int64","sqlite3_int64(pointer stmt, int iCol)","cdecl");
	text= dll.api("sqlite3_column_text","string(pointer stmt, int iCol)","cdecl");
	text16= dll.api("sqlite3_column_text16","pointer(pointer stmt, int iCol)","cdecl");
	self.type= dll.api("sqlite3_column_type","int(pointer stmt, int iCol)","cdecl");
	value= dll.api("sqlite3_column_value","int(pointer stmt, int iCol)","cdecl");
	count= dll.api("sqlite3_column_count","int(pointer stmt)","cdecl"); 
	name= dll.api("sqlite3_column_name","string(pointer stmt, int iCol)","cdecl");
	/*end intellisense*/
}

//不使用回调函数查询数据库 方法之二
namespace table{
	/*intellisense(sqlite.table)*/
	get = dll.api("sqlite3_get_table","int(pointer db,string zSql,pointer &pazResult,int &pnRow,int &pnColumn,pointer &errmsg)","cdecl");
	free = dll.api("sqlite3_free_table","int(pointer azResult)","cdecl")
	/*end intellisense*/
}

 
namespace bind{
	/*intellisense(sqlite.bind)*/
	blob = dll.api("sqlite3_bind_blob","int(pointer stmt, int, string, int n, pointer )","cdecl");
	double = dll.api("sqlite3_bind_double","int(pointer stmt, int, double)","cdecl");
	int = dll.api("sqlite3_bind_int","int(pointer stmt, int, int)","cdecl");
	long64 = dll.api("sqlite3_bind_int64","int(pointer stmt, int, long)","cdecl");
	nil = dll.api("sqlite3_bind_null","int(pointer stmt, int)","cdecl");
	text = dll.api("sqlite3_bind_text","int(pointer stmt, int, string, int n,pointer )","cdecl");
	text16 = dll.api("sqlite3_bind_text16","int(pointer stmt, int,string, int, pointer )","cdecl");
	value = dll.api("sqlite3_bind_value","int(pointer stmt, int, pointer)","cdecl");
	zeroblob = dll.api("sqlite3_bind_zeroblob","int(pointer stmt, int, int n)","cdecl");
	parameter_index = dll.api("sqlite3_bind_parameter_index","int(pointer stmt, string zName)","cdecl"); 
	/*end intellisense*/
} 


//-------------------------------------------------------------------
//API声明 结束;
//-------------------------------------------------------------------
