In order to support advanced FTS (full text search) features I need to add some custom functions to the sqlite engine.
Per their docs, I should use sqlite3_create_function() for this.
I should be able to accomplish this with declares and AddressOf.
However, I have trouble getting the value for the “sqlite3 *db” parameter.
I’d expect to find it in a “REALSQLDatabase.Handle” property, but there is none.
How do I get this value?
There is no handle property
Use a DLL / dylib and the load extensions property
http://documentation.xojo.com/index.php/SQLiteDatabase.LoadExtensions
Ah, of course! In fact, I had used this technique already to load an ICU extension. I had forgotten how it worked and that I could also use it to inject custom functions.
A blog post about all this is long pending anyways.
Thanks for reminding me 
Though it woild be nice to habe a handle property because the. one could implement this all directly i. Xojo code, which makes x-platform dev easier.
Okay, here is an idea: Does someone know of a web service that can generate Mac, Windows and Linus code from C source? That would be helpful to generate these code stubs. I am not keen on having to install Win and Linux systems with compilers just to create those.
VM’s
if you generate such a dylib / dll how would you test it without them ?
And if you dont intend to test them then why generate them ?
When I install Win in a VM, I don’t get a compiler with it, right? And I remember it’s quite a nuisance to get the Wind dev tools installed.
Or can I use some leaner (cmdline-only) compiler system? I believe I used mingw or something like that years ago. Is that something I can use to build libs that I can use with Xojo?
there’s several free compilers that I suspect would work
vs express used to be able to create dll’s
I’ve used mingw in the past
Also:
Because it’s not ME who’d build them - I’d let the users themthelves build them.
This is, as so often (and something that, sadly, is hardly ever considered by Xojo), about me publishing sharable classes, not built apps. When I share the classes, I only want to have to share the source code. But I also don’t want to burden users who want to add custom sqlite functions in Xojo having to learn by themselves how to build a small dylib or lib file on Windows. Unless a Xojo blog post would exist that already teaches all that so I don’t have to? But still, it’s quite the struggle for a Xojo-only developer having to understand how to install VS Express or Xode and use them to build a small lib file.
If you only want to give away source then the burden is on the receiver to compile it somehow (shared service, installing a vm, etc)
There’s no magic “give me C++ source to use in Xojo and compile it for me” command
So you should compile it for the various targets & tests it there & give away the dll / dylib + sources
Either way the support of it on the various targets is going to fall to you (not use) so you need to be able to do that or dont give away the code
Or is this “hey you got source you’re on you’re own” ?
In that case the receiver needs to be able to do something with it and that means they need to learn how to compile it and maybe even debug it
I still dont get how thats our job but …
I never meant to imply that.
I know that. That’s why I asked if there’s a web service for that.
Ok lets go round this circle one more time.
You want to give out source code
But in order to know your source code works on whatever target YOU need to compile it and test it on those targets.
Correct ?
Otherwise people will download it, maybe find a problem and ask you to fix it.
Correct ?
So give away the source AND a built dylib / dll on whatever service you decide to use for the source
All fixed & fixed
People get a built dylib / dll and you give away source code you’ve tested
Yes, Norm. There’s no way around that anyway with no public service for it.
BTW, I’ve struggled to implement a hook for the extension loading. It’s been rather complex to solve, because when the dylib gets loaded by the sqlite3 engine, you cannot pass any arguments to it. I wanted the loaded dylib to call back into my Xojo code, though, which would require passing a callback pointer to the dylib. (See also my SO question).
I’ve solved this now by having two dylibs: One (the helper) is just for storing the callback pointer - My Xojo code declares against that and set the callback pointer. Then, when the actual sqlite extension gets loaded, it also links to the same helper dylib and fetches the stored callback pointer. Then it can call into my Xojo code where I can then install my sqlite functions.
I wonder if there’s a simpler way.
I’d expect calling back into xojo code from the dylib is going to really make sqlite full text searches slow
Arghh, I keep making mistakes. I don’t actually need the extra helper lib when I do the declares and lib refs right.
Actually, it should make them faster in cases where one needs a rating 
(I am trying to implement the ranking function as doc’d on the FTS page).
Well, it remains to be seen. I’m still having my troubles getting the callbacks working properly without having to duplicate half of the code in the dylib.
Do it in C it’ll be tons faster - really
Unless you need some kind of custom rating function etc that can only be done in Xojo I’d do it right in the c lib
Right, I need to rate it based on dynamic values I have in my Xojo code. Otherwise the effort would be moot, indeed.
But out of curiosity - where do you think that it would be slower than doing it in C? Because of all the runtime lock/unlock invocations and dynamic allocations when I deal with strings?
call over head + lock / unlock
well written hand tweaked xojo code can get close to “the same” code in C but compiling with highest optimizations in C will still give you slightly faster executable code
still I get why you’d want a function written in xojo to be used in an FTS ranking
I can think of one spot I’d like that for the IDE itself
You could use my MBS SQL plugin. There I do have a handle for database.
And as I include also my own copy of SQLite I could any functions needed there directly.