FTS5 CJK Support (ICU or other options)?

[brian-superlist]

Heya, thanks again for all your fantastic work with this library and speaking to us at Superlist!

I had a question: I'm looking at adding a full search index to our app. I was interested in trying the ICU tokenizer for FTS5. I saw in a comment that ICU support might be interesting, but I did not see it listed on the official extensions page.

If we wanted to try ICU tokenization, what would you recommendation be? Should we follow the examples in https://github.com/simolus3/sqlite3.dart/tree/e682c9792cc1ea094281a7d2edfc7db5ca802b05/sqlite3 to compile a version with ICU support?

Thanks again,
Brian

[simolus3]

Hi!

I saw in a comment that ICU support might be interesting, but I did not see it listed on the official extensions page.

I'm also wondering where that came from. There are ICU extensions for SQLite, but they're meant to add locale-aware implementations of functions like upper() and lower() in SQLite. As far as I know, they don't add a new tokenizer for FTS5. There's just the default unicode61 tokenizer that's builtin to SQLite.

So it's definitely possible to build SQLite with extensions linked into it (and I'm happy to help with more pointers if you need that, unfortunately it requires a custom build at the moment). But I'm not sure if the ICU extensions from the SQLite project would be helpful for better tokenization here.

[brian-superlist]

Ah yah, that makes sense. Going back, I was reading the wrong documentation for FTS3 tokenizers.

It seems the Unicode61 really isn't useful for CJK languages, the only thing I've found that might help out is this tokenizer from Signal, which is a custom extension.

In that case, to make it work for web, iOS, Android, and MacOS, I'd probably need to follow your custom_extension example to build for all platforms, is that correct?

Thanks for the guidance and help here!

[simolus3]

The custom extension example covers native platforms, following that should work.

Unfortunately, wasm builds are a bit more complex. There's an example for them here (which happens to use Rust too, so you could pull in the Signal tokenizer there). Unfortunately that example doesn't really grant extensions access to the database connection which would be required here. I'll take a look at expanding that example soon.

[simolus3]

I've pushed a branch that copies code from the Signal tokenizer (with some minor modifications to call SQLite methods statically).

I got things to compile, but there are runtime errors because the internal FTS5 interfaces have changed in the meantime. You'd likely get runtime errors for native platforms as well, that tokenizer needs to be updated to work with more recent SQLite versions.

[brian-superlist]

Excellent, thanks so much! I'll take a look and see if we can adjust the signal code to fit our case.

Appreciate the help as always, Simon!