#include-once

Const $SQLITE_UTF8 = 0
Const $SQLITE_DETERMINISTIC = 0x800

#cs Example
; the following functions are usefull to 'bind' user functions to a database context to be used in your queries

#include <SQLite.au3>
#include <Array.au3>

_SQLite_Startup("sqlite3.dll", True)
_SQLite_Open()

; the user function
Func _simple_func($pCtx, $iArgs, $pArgs)
	; parameters :
	;    $pCtx - data base context, use _SQLite_ContextDbHandle($pCtx) to retreive data base handle
	;    $iArgs - number of arguments passed to the function
	;    $pArgs - pointer to the arguments, use _SQLite_FuncArgs($iArgs, $pArgs) to get an array of pointers to sqlite_value
	;             use _SQLite_GetValue($array[n]) to get the n-th argument (0-based)
	; return value :
	;	 to set the return value of the function, use _SQLite_ResultText($pCtx, $returnValue)
	;
	; note :
	;    as you see, only _SQLite_ResultText is implemented (no ResultInt, ResultBlob...) because it's like this that AutoIt
	;    handles sqlite databases: all data is UTF8 text

	; this function will add all it's parameters

	Local $Result = 0
	Local $aArgs = _SQLite_FuncArgs($iArgs, $pArgs), $Arg

	For $i = 0 To UBound($aArgs) - 1
		$Arg = _SQLite_GetValue($aArgs[$i])
		$Result += Int($Arg)
	Next

	_SQLite_ResultText($pCtx, $Result)
EndFunc

; to add function to a database handle, you must first convert it to a DllCallback, and pass a pointer (DllCallbackGetPtr) to _SQLite_CreateFunction
$dllCb = _SQLite_FuncCallbackRegister("_simple_func")
_SQLite_CreateFunction(-1, "simple_func", -1, 0, 0, DllCallbackGetPtr($dllCb), 0, 0)

; now we can use the new function in our queries!
Dim $aRow
_SQLite_QuerySingleRow(-1, 'SELECT simple_func(1, 2, 3)', $aRow)
ConsoleWrite("simple_func(1, 2, 3) = " & $aRow[0] & @CRLF)

; do not forget to DllCallbackFree the function when you don't need it
DllCallbackFree($dllCb)
_SQLite_Close()
_SQLite_Shutdown()

#ce

; parameters :
;    $hDb       - data base handle (-1 for the last used data base)
;    $sFuncName - name of the SQL function to be created or redefined
;    $iArgs     - number of arguments that the SQL function or aggregate takes (-1 for variable arguments)
;    $iTextRep  - text encoding that sqlite will prefer for the parameters (use allways 0 ($SQLITE_UTF8) because _SQLite_GetValue expects that)
;                 set $SQLITE_DETERMINISTIC (+ $SQLITE_UTF8) to signal that the function will always return the same result given the same inputs within a single SQL statement
;    $pUserData - user data that will be linked to the function, and that can be retreived in the function using _SQLite_UserData
;    $pFunc, $pStep, $pFinal
;               - pointers to C-language functions that implement the SQL function or aggregate
;                 a scalar SQL function requires an implementation of the $pFunc callback only; NULL pointers (0) must be passed as the $pStep and $pFinal parameters
;                 an aggregate SQL function requires an implementation of $pStep and $pFinal and NULL pointer must be passed for $pFunc
;                 to delete an existing SQL function or aggregate, pass NULL pointers for all three function callbacks
;
Func _SQLite_CreateFunction($hDB, $sFuncName, $iArgs, $iTextRep, $pUserData, $pFunc, $pStep, $pFinal)
	If __SQLite_hChk($hDB, 2) Then Return SetError(@error, 0, $SQLITE_MISUSE)
	; ---
	Local $iRval = DllCall($g_hDll_SQLite, "int:cdecl", "sqlite3_create_function16", _
		"ptr", $hDB, _
		"wstr", $sFuncName, _
		"int", $iArgs, _
		"int", $iTextRep, _
		"ptr", $pUserData, _
		"ptr", $pFunc, _
		"ptr", $pStep, _
		"ptr", $pFinal)
	If @error Then Return SetError(1, @error, $SQLITE_MISUSE) ; Dllcall error
	If $iRval[0] <> $SQLITE_OK Then
		__SQLite_ReportError($hDB, "_SQLite_CreateFunction")
		Return SetError(-1, 0, $iRval[0])
	EndIf
	Return $iRval[0]
EndFunc

Func _SQLite_FuncCallbackRegister($sFunc)
	Return DllCallbackRegister($sFunc, "none:cdecl", "ptr;int;ptr")
EndFunc

Func _SQLite_ContextDbHandle($pCtx)
	If $g_hDll_SQLite = 0 Then Return SetError(1, $SQLITE_MISUSE, $SQLITE_MISUSE)
	; ---
	Local $iRval = DllCall($g_hDll_SQLite, "ptr:cdecl", "sqlite3_context_db_handle", "ptr", $pCtx)
	If @error Then Return SetError(1, @error, $SQLITE_MISUSE) ; Dllcall error
	; ---
	Return $iRval[0]
EndFunc

Func _SQLite_UserData($pCtx)
	If $g_hDll_SQLite = 0 Then Return SetError(1, $SQLITE_MISUSE, $SQLITE_MISUSE)
	; ---
	Local $iRval = DllCall($g_hDll_SQLite, "ptr:cdecl", "sqlite3_user_data", "ptr", $pCtx)
	If @error Then Return SetError(1, @error, $SQLITE_MISUSE) ; Dllcall error
	; ---
	Return $iRval[0]
EndFunc

Func _SQLite_GetValue($pValue, $bBinary = False)
	If $g_hDll_SQLite = 0 Then Return SetError(1, $SQLITE_MISUSE, $SQLITE_MISUSE)
	; ---
	If $pValue = 0 Then
		Return SetError(1, $SQLITE_MISUSE, $SQLITE_MISUSE)
	EndIf
	; ---
	Local $iType = DllCall($g_hDll_SQLite, "int:cdecl", "sqlite3_value_type", "ptr", $pValue)
	If @error Then Return SetError(1, @error, $SQLITE_MISUSE) ; Dllcall error
	; ---
	If $iType[0] = $SQLITE_TYPE_NULL Then
		Return ""
	Else
		If Not $bBinary And $iType[0] <> $SQLITE_TYPE_BLOB Then
			Local $aRet = DllCall($g_hDll_SQLite, "wstr:cdecl", "sqlite3_value_text16", "ptr", $pValue)
			If @error Then Return SetError(3, @error, $SQLITE_MISUSE) ; Dllcall error

			Return $aRet[0]
		Else
			Local $aResult = DllCall($g_hDll_SQLite, "ptr:cdecl", "sqlite3_value_blob", "ptr", $pValue)
			If @error Then Return SetError(6, @error, $SQLITE_MISUSE) ; Dllcall error

			Local $iColBytes = DllCall($g_hDll_SQLite, "int:cdecl", "sqlite3_value_bytes", "ptr", $pValue)
			If @error Then Return SetError(5, @error, $SQLITE_MISUSE) ; Dllcall error

			Local $vResultStruct = DllStructCreate("byte[" & $iColBytes[0] & "]", $aResult[0])
			Return Binary(DllStructGetData($vResultStruct, 1))
		EndIf
	EndIf
EndFunc

Func _SQLite_FuncArgs($iArgs, $pArgs)
	If $iArgs = 0 Or $pArgs = 0 Then Return 0
	; ---
	Local $aRet[$iArgs]
	$pArgs = DllStructCreate("ptr[" & $iArgs & "]", $pArgs)
	For $i = 0 To $iArgs - 1
		$aRet[$i] = DllStructGetData($pArgs, 1, $i + 1)
	Next
	Return $aRet
EndFunc


Func _SQLite_ResultText($pCtx, $sText)
	If $g_hDll_SQLite = 0 Then Return SetError(1, $SQLITE_MISUSE, $SQLITE_MISUSE)
	; ---
	Local $iRval = DllCall($g_hDll_SQLite, "none:cdecl", "sqlite3_result_text16", "ptr", $pCtx, "wstr", $sText, "int", -1, "ptr", -1) ; SQLITE_TRANSIENT
	If @error Then Return SetError(1, @error, $SQLITE_MISUSE) ; Dllcall error
	; ---
	Return 1
EndFunc