[H2] sq() - SQLite Class

Share the finished AutoHotkey v2 Scripts and libraries you made here. Please put the current version of AutoHotkey v2 you used in Square Brackets at the start of the topic title.
arcticir
Posts: 647
Joined: 17 Nov 2013, 11:32

[H2] sq() - SQLite Class

05 Mar 2020, 07:13

Note:
It applies to AHK_H V2(106+)
You need to specify the absolute path of sqlite3.
The sqlite3 function is called like this: sq.close[]
Code from @HotKeyIt
Simple example:

Code: Select all

sq:=sq(a_ahkdir "\test.db")
sq.set("create table if not exists test (a,b,c);")
sq.set("INSERT INTO test (a,b,c) VALUES(1,2,3);")
sq.set("INSERT INTO test (a,b,c) VALUES(4,5,6);")
sq.get("SELECT * FROM test")

Code: Select all

sq(db:="",opt:=""){
	return __SQLite3.new(db,opt)
}

Class __SQLite3 {
	__New(db:="",opt:=""){
		this.hd:=MemoryLoadLibrary(a_ahkdir "\sqlite\sqlite3-" (A_PtrSize=8?"64":"32") ".dll")
			,this.initialize:=DynaCall(MemoryGetProcAddress(this.hd,"sqlite3_initialize"),"i=")
			,this.open16:=DynaCall(MemoryGetProcAddress(this.hd,"sqlite3_open16"),"i==wt*")
			,this.config:=DynaCall(MemoryGetProcAddress(this.hd,"sqlite3_config"),"i=ittt")
		If opt
			isobject(opt)?this.config[opt*]:this.config[opt]	
		this.initialize[],db?This.opendb(db):""
	}
	opendb(db){
		static hDB:=""
			,Functions:="aggregate_context:t==ti|auto_extension:i==t|bind_blob:i==titit|bind_double:i==tid|bind_int:i==tii|bind_int64:i==ti6|bind_null:i==ti|bind_text:i==titit|bind_text16:i==tisit|bind_value:i==tit|bind_zeroblob:i==tii|bind_parameter_count:i==t|bind_parameter_index:i==tt|bind_parameter_name:t==ti|blob_bytes:i==t|blob_open:i==tttt6it*|blob_close:i==t|blob_read:i==ttii|blob_reopen:i==t6|blob_write:i==ttii|busy_handler:i==ttt|busy_timeout:i==ti|changes:i==t|clear_bindings:i==t|close:i==t|close_v2:i==t|collation_needed:i==ttt|collation_needed16:i==ttt|column_blob:t==ti|column_bytes:i==ti|column_bytes16:i==ti|column_double:d==ti|column_int:i==ti|column_int64:6==ti|column_text:t==ti|column_text16:s==ti|column_type:i==ti|column_value:t==ti|column_count:i==t|column_decltype:t==ti|column_decltype16:s==ti|column_name:t==ti|column_name16:s==ti|commit_hook:t==ttt|rollback_hook:t==ttt|compileoption_used:i==t|compileoption_get:t==i|complete:i==t|complete16:i==t|context_db_handle:t==t|create_collation:i==ttitt|create_collation_v2:i==ttittt|create_collation16:i==ttitt|create_function:i==ttiitttt|create_function16:i==ttiitttt|create_function_v2:i==ttiitttt|create_module:i==tttt|create_module_v2:i==ttttt|data_count:i==t|db_config:i==tittt|db_filename:t==tt|db_mutex:t==t|db_handle:t==t|db_readonly:i==tt|db_release_memory:i==t|db_status:i==tititi|declare_vtab:i==tt|enable_load_extension:i==ti|enable_shared_cache:i==i|errcode:i==t|extended_errcode:i==t|errmsg:t==t|errmsg16:s==t|exec:i==tattt*|extended_result_codes:i==ti|file_control:i==ttit|finalize:i==t|malloc:t==i|realloc:t==ti|free:t==t|get_table:i==ttt*ttt*|free_table:t==t|get_autocommit:i==t|get_auxdata:t==ti|set_auxdata:t==tittt|shutdown:i==|os_init:i==|os_end:i==|last_insert_rowid:6==t|libversion:t==|sourceid:t==|libversion_number:i==|limit:i==tii|load_extension:i==tttt*|log:t==itttt|memory_used:6==|memory_highwater:6==i|mprintf:t==ttttttttttt|vmprintf:t==ttttttttttt|snprintf:t==itttttttttt|vsnprintf:t==itttttttttt|mutex_alloc:t==i|mutex_free:t==t|mutex_enter:t==t|mutex_try:i==t|mutex_leave:t==t|next_stmt:t==tt|open_v2:i=tt*it|open:i==tt*|overload_function:i==tti|prepare:i==ttit*t*|prepare_v2:i==ttit*t*|prepare16:i==tsit*t*|prepare16_v2:i==twit*t*|trace:t==ttt|profile:t==ttt|progress_handler:t==titt|randomness:t==it|release_memory:i==i|reset:i==t|reset_auto_extension:t==|result_blob:t==ttit|result_double:t==td|result_error:t==tti|result_error16:t==tti|result_error_toobig:t==t|result_error_nomem:t==t|result_error_code:t==ti|result_int:t==ti|result_int64:t==t6|result_null:t==t|result_text:t==ttit|result_text16:t==ttit|result_text16le:t==ttit|result_text16be:t==ttit|result_value:t==tt|result_zeroblob:t==ti|set_authorizer:i==ttt|sleep:i==i|soft_heap_limit64:6==6|sql:t==t|status:i==itti|step:i==t|stmt_busy:i==t|stmt_readonly:i==t|stmt_status:i==tii|strnicmp:i==tti|stricmp:i==tti|threadsafe:i==|total_changes:i==t|backup_finish:i==t|backup_init:i==t|backup_pagecount:i==t|backup_remaining:i==t|backup_step:i==t" ;|table_column_metadata:i=tssst*t*i*i*i*"
			,ShiftParam:="|create_function|create_function16|create_function_v2|create_module|create_module_v2|db_filename|db_readonly|enable_load_extension|errcode|extended_errcode|get_table|load_extension|next_stmt|prepare|prepare_v2|prepare16|prepare16_v2|table_column_metadata|wal_autocheckpoint|wal_checkpoint|wal_checkpoint_v2|sqlite3_blob_open|busy_handler|busy_timeout|changes|close|close_v2|collation_needed|collation_needed16|commit_hook|rollback_hook|create_collation|create_collation_v2|create_collation16|db_config|db_mutex|db_release_memory|db_status|declare_vtab|errmsg|errmsg16|exec|extended_result_codes|file_control|get_autocommit|last_insert_rowid|limit|overload_function|trace|profile|progress_handler|set_authorizer|total_changes|update_hook|vtab_config|wal_hook|interrupt|"
		If (Type(db)="Integer")
			this._:=db
		else If db
		{
			if (db!=":memory:") && !FileExist(this.dir:=SubStr(db,1,InStr(db,"\","",-1)))
				DirCreate(this.dir)
			this.open16[db,hDB],this._:=hDB ; ReadWrite
		}
		Loop Parse,Functions,"|"
		{
			arg:=StrSplit(A_LoopField,":")
			If db && InStr(ShiftParam,"|" arg[1] "|")
			{
				param:=[arg[2]]
				Loop  StrLen(RegExReplace(arg[2],"i)\*|u|p|\=|\s|\t"))-1
					If A_Index>1
				param.Push(A_Index)
				If param.Length=1
					param:=arg[2] ; only one parameter, no need to shift
				else param.Push(1)
					ObjRawSet(this,arg[1],DynaCall(MemoryGetProcAddress(this.hd,"sqlite3_" arg[1]),param,hDB))
			} 
			else	ObjRawSet(this,arg[1],DynaCall(MemoryGetProcAddress(this.hd,"sqlite3_" arg[1]),arg[2]))
		}
	}
	set(stmt){
		pzTail:=&stmt,end:=pzTail + StrLen(stmt)*2
		While pzTail!=end
		{
			if this.prepare16_v2[pzTail,-1,pStmt,pzTail]
				return (ErrorLevel:="Error Prepare: " A_Index "`n" this.errmsg16[])
			if pStmt=0
				continue
			else if (err:=this.step[pStmt])!=101 && err!=100
				return (this.finalize[pStmt],"Error Step: " A_Index "`n" err ": " this.errmsg16[])
			if this.finalize[pStmt]
				return (ErrorLevel:="Error Finalize: " A_Index "`n" this.errmsg16[])
		}
	}
	get(SQL, columnName:=1){
	        pzTail:=&SQL,final:=pzTail + strlen(SQL)*2,res:=map()
	        While pzTail!=final
	        {
	                if this.prepare16_v2[pzTail,-1,pStmt,pzTail]
	                        return (ErrorLevel:=this.errmsg16[],"")
	                if pStmt=0
	                        continue
	                if 100=ret:=this.step[pStmt]
	                        break
	                else if ret!=101
	                        return (this.finalize[pStmt],ErrorLevel:=this.errmsg16[],"")
	                if this.finalize[pStmt]
	                        return (ErrorLevel:=this.errmsg16[],"")
	        }
	        if ret=101 ; Done
	                return
	        Index:=map(),row:=[]
	        Loop ColumnCount:=this.column_count[pStmt]
			m:=this.column_name16[pStmt,s:=A_Index - 1], Index[s]:=m
	        while ret=100
	        {
	                row.push(list:={})
	                Loop ColumnCount
	                      s:=A_Index-1,n:=columnName?Index[s]:A_Index,((t:=this.column_type[pStmt,s]) = 1
	                                ? list.%n%:=this.column_int64[pStmt,s]
	                                : t = 2
	                                        ? list.%n%:=this.column_double[pStmt,s] : (t = 4 or t = 5)
	                                                ? (sz:=this.column_bytes[pStmt,s])?(ptr:=this.column_blob[pStmt,s]							
							,list.%n%:=BufferAlloc(sz),RtlMoveMemory(list.%n%.ptr,ptr,sz)):list.%n%:=""
						: list.%n%:=this.column_text16[pStmt,s])
	                        ret:=this.step[pStmt]
	        }
	        return (this.finalize[pstmt],row.length?row:'')
	}
	__Delete(){
		this.close[],this.shutdown[],MemoryFreeLibrary(this.hd)
	}
}

sq_quote(ByRef s) => StrReplace(s,"'","''")

Return to “AutoHotkey v2 Scripts and Functions”

Who is online

Users browsing this forum: No registered users and 6 guests