Support for custom FTS5 Auxiliary Functions

[maianhvu]

FTS5 supports a couple of default auxiliary functions, while allowing us to write our own using xCreateFunction on fts5_api as shown here. GRDB currently supports adding normal sqlite functions, but not a custom fts5 auxiliary function. I managed to write my own FTS5 custom aux function by interfacing with the C API, but I can't say it was the best experience managing unsafe pointers and manually copying the implementations of several internal methods of GRDB into my application just to be able to call them. I'm sure a couple of folks around here would also appreciate being able to write FTS5 Auxiliary Functions in Swift using GRDB.

By the way, while you're at it, please also make sure DatabasePool works with the auxiliary function registration. Currently it's throwing an error when I use a pool but not a queue. I'd imagine the fix would somewhat be similar to #414.

Auxiliary function might seem trivial but it's giving my app incredible speedup when I want to sort my full-text search results according to some other column (like a timestamp, for example). Query plan shows no temporary B-tree sorting by a ranking function in the form of parsing the timestamp string into a UInt32, and allow me to completely skip an external content table.

[groue]

Hello @maianhvu,

This sounds like a very good idea.

Since you have a working implementation, would you mind submitting a PR? I'll guide you if you have any question. Such a pull request should include:

• the feature, of course
• tests that run for all GRDB variants that support FTS5
• inline documentation
• documentation in README.md, or a dedicated guide in Documentation/, depending on the level of guidance users would need. Your example use cases are interesting, and could provide a good sample code.

[...] By the way, while you're at it [...]

Maybe something has been lost in translation (I'm not a native English speaker), but this sentence sounds horribly rude to my French ears. It sounds condescending, and it also looks like you expect me to work for you out of thin air. This is not how open source works in general, and certainly not how this library is developed in particular. As a general advice, mind your language when you suggest improvements to an open source project.

I don't currently have any use for FTS5 auxiliary functions, so I don't plan dedicating any personal time on this feature. Except, as I said above, helping you building up a pull request. Other GRDB users may help you too, of course.

[maianhvu]

Hi @groue ,

[...], but this sentence sounds horribly rude to my French ears.

Oops, I never meant that and also never expected it to come across that way. Well, English is not my native language either, I'm a Vietnamese. What I meant was that if you were to implement this yourself (or anyone else), maybe do also verify that it works with DatabasePool, to avoid having another issue just to add the support like #414. I apologize for the misunderstanding.

To clarify, I'd be happy to submit a pull request for the function, along with the aforementioned check with DatabasePool. I'll try to work something out by next week. Cheers!

[groue]

All right, @maianhvu. I guess "while you're at it" is better totally avoided (since you can't assume that people "are at it").

To clarify, I'd be happy to submit a pull request for the function, along with the aforementioned check with DatabasePool.

That would be really great. Until I update CONTRIBUTING.md with a "Jump In" section, here is some advice:

Use Xcode 10. Make sure Xcode is located at /Applications/Xcode.app

After cloning GRDB, run the following command in order to download and setup a custom SQLite build that enables FTS5:

make SQLiteCustom

Start from the development branch.

Open GRDB.xcworkspace in Xcode.

Run your tests in the GRDBCustomSQLiteiOS scheme (or ...OSX):

All the steps above will let you develop and test within Xcode, using the Swift 4.2 compiler in the Swift 4.1 compatibility mode.

In order to test your changes with the regular GRDB framework (not GRDBCustomSQLite), and with Swift 4.2, you can use the following command:

make test_framework_GRDBOSX_maxSwift

For further tests, you can simply let Travis check your pull request.

Please tell me if you need more information!

[groue]

It's been a week since the issue was initially open, and no progress has been seen: I close the issue.

I've added your great suggestion to the list of suggested contributions in CONTRIBUTING.md.

Feel free to reopen this issue with new information, or bring a pull request when you're ready, @maianhvu!

[maianhvu]

Ok! I'm about halfway through the implementation, going to tie up the loose ends by this week and then documentation will follow.

[aehlke]

did you finish it @maianhvu ?

[aehlke]

@groue I'm trying to replicate this work and understand what they meant after unsuccessful contact - do you have any pointers on what internals/GRDB files he might have been talking about when needing to copy implementation into his auxiliary function implementation to interface with the C API? thanks

[groue]

Hello @aehlke. It might be easier to help if you tell about your progress and where you are stuck.

[aehlke]

Hi, I spent some more time on it and think I understand the GRDB side well enough once I found the few files using FTS5.api(db) and see now that you recently exposed that publicly. I'll give it a try and share the code/results back to GRDB. Thank you