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!

[HeftyCoder]

Hello! I find it interesting that ICU support does not come out of the box. Is there any reason?

I'm making an application that should support searching for a pattern in a TEXT column of a table. However, the ASCII only implementation of the LIKE operator means that I have to do the search in the application layer, which seems counter-intuitive, especially since a really common use-case for Drift would be in a Flutter application.

Curiously, Is it typical to avoid string pattern matching in SQLite, and instead opt to do it in the application?

For reference, I think ICU (or something of the like) is automatically enabled in CPython and since I had only used it there, I struggled until I found out about how it defaults to only ASCII in general.

[simolus3]

Is there any reason?

It would require linking something like libicu which is not a trivial library to include (also regarding code size). AFAIK, this library is not linked by Flutter by default (and even if it was we'd probably have to include our own second copy just for SQLite).

Curiously, Is it typical to avoid string pattern matching in SQLite, and instead opt to do it in the application?

If you have a Dart package for ICU that you're using, one thing you can do is define like and other operators as user defined functions. The like expression is a function invocation, and you can patch that function by registering your own one. So you can keep using LIKE to filter in SQLite, except that the filtering is locale-aware because you're implementing it in Dart.