[SOLVED]SQLite User Defined Functions (SQLite UDFs)

Good day, folks.

I see a lot of dialog in the forums about SQLite and AHK, and there seems to be some confusion around if implementing SQLite functionality in AHK is do-able and stable. I am here to assert that it most certainly is. I have a script running that allows interactivity with a DB that is ~150MB containing ~142000 records and am using FTS to query and get result sets from the DB. FTS is REALLY FAST.!.

Now that I have the ability to pull all of this data in, I am in need of the ability to sort it by relevance, before presentation to the user. I am struggling with something, however, hence my post...

I am looking for some guidance regarding implementation of User Defined Functions in SQLite (SQLite UDFs) via AHK.

I am using nicks SQLite lib which you can still get your hands on here.

Therein a lot of SQLite functionality is facilitated. What is missing is the ability to hook SQLite with User Defined Functions for creating standard or aggregate UDFs (Standard Functions take a single record of data and returns a single result, where as Aggregate Functions take a set of records and iterates through them and then returns a single result).

I am inplementing FTS4 in an AHK project. It is working, in that FTS is performing all of the FTS functions it is natively designed to. What I want to do is to implement the Rank UDF that is described in the latter portions of this.

Is someone willing to assist with pointing me in the right direction regarding setting up the dll call and callback constructs, so that I can come up with some lib enhancements that will facilitate being able to programmatically setup AHK-based SQLite UDFs?

Please advise. Thank you, in advance, for your time.

-t

EDIT: Here is an example of why the sorting by relevance is needed. My search term was FTS4. It is expected that if the search term is in the title of the document, that it should have pretty high relevance and be one of the first results presented. Right now, I don't have a good mechanism for sorting by relevance. As such, the most applicable document was returned as the 6th result. Not good. Ugh.

Here's a quick, untested translation:

result := DllCall("sqlite3\sqlite3_create_function"
   , "uint", hDB     ; or "Ptr" on AHKL
   , "str", FunctionName
   , "int", -1       ; nArg: -1 means accept any number of args
   , "int", 5        ; eTextRep: 5 means any, 1 means UTF-8.
   , "uint", 0       ; Value defined by you. Not necessary.
   , "uint", xFunc   ; Callback for scalar functions, 0 for aggregate functions.
   , "uint", xStep   ; Callback for aggregate functions, 0 for scalar functions.
   , "uint", xFinal) ; As above.

Since the callbacks are "C-language functions", they probably use the default C calling convention, so use the "Cdecl" option when creating the callback. xFunc and xStep take three parameters while xFinal takes only one. I couldn't find where they are documented, but I guess they are as follows:
[*:1tateqkx]A pointer representing the context of the function-call. This is used when setting the result of the function-call.
[*:1tateqkx]Probably the number of arguments, since there doesn't appear to be any other way to determine this.
[*:1tateqkx]"Args" - An array of pointers to arguments. ArgN := NumGet(Args + N*4) can be used to retrieve the Nth argument. (4 could be replaced with A_PtrSize on AHKL for 64-bit compatibility.) sqlite3_value_type can be used to determine the type of value and sqlite3_value_xxx can be used to retrieve an actual value of type xxx. You could probably use just sqlite3_value_text to retrieve the value as a string. (The documentation states that the column access functions "attempt to convert the value where appropriate" and that the value access functions "work just like the corresponding column access functions".)If you're interested in getting the script to work on Unicode and/or 64-bit builds of AutoHotkey_L, there's a little added complexity:
[*:1tateqkx]The "16"-suffixed versions of sqlite3_create_function, sqlite3_value_text and sqlite3_result_text should be used; these accept or return UTF-16 strings.
[*:1tateqkx]"Ptr" should be used instead of "uint" for any pointer parameters, including the last three parameters of sqlite3_create_function.
[*:1tateqkx]Whenever pointer math is involved, A_PtrSize should be used instead of assuming a pointer is 4 bytes. (For AutoHotkey Basic compatibility, you can use something like (A_PtrSize ? A_PtrSize : 4).)However, the rest of the SQLite script would also need to be revised.


This is all based on my interpretation of the documentation and a bit of guesswork. I haven't tested any of it as I've never used SQLite (directly).

Thanks Lex. It is very much appreciated!

Hope things are well with you on that big arse island (assuming that you are still residing there)!

If you celebrate, Merry Christmas my friend (of several years now... ).

It is good to chat with you via the forums again. I am glad that you are still involved and active. Kudos on 1.0.90.00. Now get back on #ahk once in a while...

Again, in all seriousness, thank you for giving me some of your valuable time. Take care.

-t

OK, so I got the UDF defined and actually am getting the dll to call the script programmatically via a callback - this is wicked kewl!

So. the next step is to try to get data into the script from the SQLite DLL. Reference this image for my hack-arse test code and a sample code:

<!-- m -->http://gyazo.com/583...5d19c6129a1.png<!-- m -->

The 2nd arg passed to the script is indeed the # of params. the rest of the stuff is a gigantic unknown to me. I have been banging away on the keyboard trying to come up with a logical result, for the balance of the data, but am failing.

I am thinking that I need to call the sqlite3_value_blob(3rd_Arg), in order to arrive at something useful, sorta like what they did on the sample app.?.

Any suggestions?

-t

As I mentioned, the xFunc and xStep callbacks should take exactly three parameters. Since they are CDecl it shouldn't be harmful to accept 9 parameters (as in your screenshot), but parameters 4-9 will contain garbage.

See #3 in my previous post. I suppose...

aMatchinfo := DllCall("sqlite3\sqlite3_value_blob", "ptr", NumGet(apVal+0))

... would be equivalent to the line shown in your screenshot. apVal[n] would be equivalent to NumGet(apVal+n*4) - or NumGet(apVal+n*A_PtrSize) in AutoHotkey_L. Similarly, NumGet(aMatchinfo+0,0,"int") would be equivalent to aMatchInfo[0].

Thank you, Lexikos, for your always patient and wonderful help both herein and on Freenode's #AHK!

For some obtuse reason (one that I have given up on trying to figure out), I believe that there was a pointer that was getting clobbered (when it shouldn't have been.?.).

Regardless, I rearranged the logical approach on the code and viola' - the Scalar UDF is functioning as desired, intended, and designed!!!

Upon getting that resolved, I stumbled on another issue that kicked my backside for some time until a came across your easy way of pulling in a zero-terminated sting into a var:

_string := DllCall("MulDiv","int",pointer,"int",1,"int",1,"str")
VarSetCapacity(_string, -1)

That fixed the issue that came out of having to host variables that where extremely large (from a full text search of 300+ many documents). I got the good ole #maxmem error dialogue for the first time. Putting #maxmem 256 resolved it.

The root issue was that nick's SQLite library was not written to handle variables that blew past some arbitrary value (that value is still unknown to me as I changed the code's implementation and all is right in the world.).

I had realized that some of the script crashing that I was seeing was related to this, implemented the fix and moved on.

Thank you for your help. For those that might be interested, here is some code from my lib enhancements:

This creates the actual callback, allowing for SQL statement constructs to cause the SQLite DLL to call the AHK function to perform actions related to and needed by the SQL:

Bot_DBMS_Create_RankUDF() {
	global
	aFunc := registercallback("Bot_DBMS_Rank","Cdecl", 3)
	If (aFunc = "")	{
		Bot_DBMS_Toss_Error("Failure on Registering Rank UDF Callback", 4, 0, 5, 0)
		}

	ErrorLevel := 0
	result := DllCall("sqlite3\sqlite3_create_function"  
	   , "uint", Bot_RIM_API_DB_HWND     ; or "Ptr" on AHKL
	   , "str", "rank"		; sql function name to use in SQL constructs
	   , "int", -1			; nArg: -1 means accept any number of args  <- sql query parser helper
	   , "int", 5			; eTextRep: 5 means any, 1 means UTF-8.
	   , "uint", 0			; Value defined by you. Not necessary.
	   , "uint", aFunc		; Callback addr for scalar functions, 0 for aggregate functions.
	   , "uint", ""			; Step Func Callback addr for aggregate functions, null for scalar functions.
	   , "uint", ""			; Stop Func Callback addr for aggregate functions, null for scalar functions.
	   , "Cdecl")
	If ((ErrorLevel <> 0) or (result = ""))	{		; a_lasterror was a 2 even though
		Bot_DBMS_Toss_Error("Create sqlite3 UDF ErrorLeve|Result|a_lasterror:" . ErrorLevel . "|" . result . "|" . a_lasterror, 4, 0, 5, 1)
		}
	}

This code performs the rank function, allowing for grouping by relevance. This is a port from the code linked to in the first post:

Bot_DBMS_Rank(pCtx,nVal,apVal) {	; this function is called for each phrase that is searched for in the SQL tables.

	iSize := 4			; takes care of the assert int size of 4 thingy
	dScore := 0.0000	; inits a double
	iPhrase := 0		; inits a counter
	
	if ((nVal="") or (pCtx="") or (apVal="") or (nVal<=0) or (pCtx=0) or (apVal=0))  {
		Bot_DBMS_Toss_Error("malformed callback parameters or construct syntax...", 4, 0, 1, 0)
		return
		}

	ErrorLevel := 0
	aMatchInfo := DllCall("sqlite3\sqlite3_value_blob", "uint", NumGet(apVal+(0*iSize)), "Cdecl")	; good, uses apVal abv, used blw
	If ((ErrorLevel <> 0) or (aMatchInfo = ""))	{
		Bot_DBMS_Toss_Error("sqlite3_value_blob ErrorLeve|Result|a_lasterror:" . ErrorLevel . "|" . result . "|" . a_lasterror, 4, 0, 1, 0)
		}
	nPhrase := NumGet(aMatchInfo+(0*iSize))
	nCol :=  NumGet(aMatchInfo+(1*iSize))
	
	if (nVal<>(nCol+1)) {
		msgbox, wrong # of args
		return 0
		}
		
	loop, %nCol% {	; pull in weights arguments - should be a double for each column being searched.
		dWeight_%a_index% := DllCall("sqlite3\sqlite3_value_double", "Uint", NumGet(apVal+(a_index*iSize)), "Cdecl Double") ; look up the value and feed it to a tool function in the dll to convert it to a proper double
		If ((ErrorLevel <> 0) or (dWeight_%a_index% = "")) {
			Bot_DBMS_Toss_Error("sqlite3\sqlite3_value_double ErrorLeve|Result|a_lasterror:" . ErrorLevel . "|" . result . "|" . a_lasterror, 4, 0, 1, 0)
			}
		}
	
	loop, %nPhrase% { ; loop through all phrases searched for and build up weights by column
		iPhrase := a_index -1	; need to make a 0-offset index
		pPhraseInfo := aMatchInfo+((2+(iPhrase*nCol*3))*iSize)	; start addy of Ps/Cs 3 match vals - hits this row, hits all rows, docs with hits

		loop, % nCol	;%
			{
			iCol := a_index - 1	;make iCol 0-offset
			nHitCount := numget(pPhraseInfo+((3*iCol)*iSize))			; aPhraseinfo[3*iCol]
			nGlobalHitCount := numget(pPhraseInfo+((1+(3*iCol))*iSize))		;aPhraseinfo[3*iCol+1]
			
			if ((nGlobalHitCount > 0) and (nHitCount > 0)) { ; avoid divide by zero errors
				dScore := dScore + ((nHitCount/nGlobalHitCount) * dWeight_%a_index%)
				}		
			}
		}
		
	dScoreResult := DllCall("sqlite3\sqlite3_result_double","Uint",pCtx,"Double",dScore, "Cdecl")	;void sqlite3_result_double(pCtx, dScore);   using a tool dll function feed the double back to the calling dll
	If ((ErrorLevel <> 0) or (dScoreResult = "")) {
		Bot_DBMS_Toss_Error("sqlite3\sqlite3_result_double ErrorLeve|Result|a_lasterror:" . ErrorLevel . "|" . result . "|" . a_lasterror, 4, 0, 1 ,0)
		}
	return
	}