3 ms·

The problem with doing CJK and other languages properly is that you need a lot of data lookup tables. As an example Python has a unicodedata module with severa

The problem with doing CJK and other languages properly is that you need a lot of data lookup tables. As an example Python has a unicodedata module with several tables available, but not enough to implement word splitting. It is compiled code and 1.2MB in size. The ICU C library does include all the tables and algorithms, which are 30MB compiled data plus 12MB of compiled code. Run through gzip the compiled data becomes 12MB giving a reasonable minimum bound. This gives an idea of the payload that would have to be included if you want up to date Unicode definitions, or to be included in base images. SQLite does have optional integration with ICU, but it doesn't cover segmentation. https://sqlite.org/src/dir/ext/icu https://sqlite.org/src/dir/ext/icu

resoluteteeth 3y ago
> The problem with doing CJK and other languages properly is that you need a lot of data lookup tables Hmm.. My impression is that with other search software, just splitting CJK text into bigrams often works well enough for a lot of things, but I guess because sqlite FTS doesn't allow fuzzy searches where it returns the best ranked results even if they don't include all the input tokens, that might not work. Perhaps a good compromise would be to figure out a way to include an extension by default that requires the additional data lookup tables for CJK support to be loaded at runtime if required? That way the binary sqlite distribution could still be standardized, since loading custom binary extensions isn't really that convenient if you're using a language other than c and trying to build binaries for multiple platforms. Unfortunately, it doesn't seem like the creators of sqlite are that interested in solving this problem since theoretically you can just use extensions to do whatever you need.
- rogerbinns 3y ago
  > Unfortunately, it doesn't seem like the creators of sqlite are that interested in solving this problem since theoretically you can just use extensions to do whatever you need. Small. Fast. Reliable. Choose any three. :) > sqlite FTS doesn't allow fuzzy searches where it returns the best ranked results even if they don't include all the input tokens I'm busy updating my Python SQLite extension with full text search and this is one of things I'll be addressing. You can do a fuzzy search by having the query OR all tokens for the match, and then use a ranking function to figure out an appropriate score. I'm also doing other things like query expansion (find statistically similar search terms), and other issues like spelling correction (playystation -> playstation), split tokens (noone -> no one), combining (play station -> playstation), larger tokens (one -> gone, phone, opponent).
  - resoluteteeth 3y ago
    > I'm busy updating my Python SQLite extension with full text search and this is one of things I'll be addressing. You can do a fuzzy search by having the query OR all tokens for the match, and then use a ranking function to figure out an appropriate score. > I'm also doing other things like query expansion (find statistically similar search terms), and other issues like spelling correction (playystation -> playstation), split tokens (noone -> no one), combining (play station -> playstation), larger tokens (one -> gone, phone, opponent). That's nice but it just seems unfortunate that people have to keep reimplementing this stuff in different languages on top of sqlite
- bomewish 3y ago
  Problem with bigrams is that it will create a lot of nonwords. In the example given 你好世界 we’d get one bigram 好世 which no one is going to be looking for. Lots of wasted space in the index.

resoluteteeth 3y ago

> The problem with doing CJK and other languages properly is that you need a lot of data lookup tables Hmm.. My impression is that with other search software, just splitting CJK text into bigrams often works well enough for a lot of things, but I guess because sqlite FTS doesn't allow fuzzy searches where it returns the best ranked results even if they don't include all the input tokens, that might not work. Perhaps a good compromise would be to figure out a way to include an extension by default that requires the additional data lookup tables for CJK support to be loaded at runtime if required? That way the binary sqlite distribution could still be standardized, since loading custom binary extensions isn't really that convenient if you're using a language other than c and trying to build binaries for multiple platforms. Unfortunately, it doesn't seem like the creators of sqlite are that interested in solving this problem since theoretically you can just use extensions to do whatever you need.

rogerbinns 3y ago
> Unfortunately, it doesn't seem like the creators of sqlite are that interested in solving this problem since theoretically you can just use extensions to do whatever you need. Small. Fast. Reliable. Choose any three. :) > sqlite FTS doesn't allow fuzzy searches where it returns the best ranked results even if they don't include all the input tokens I'm busy updating my Python SQLite extension with full text search and this is one of things I'll be addressing. You can do a fuzzy search by having the query OR all tokens for the match, and then use a ranking function to figure out an appropriate score. I'm also doing other things like query expansion (find statistically similar search terms), and other issues like spelling correction (playystation -> playstation), split tokens (noone -> no one), combining (play station -> playstation), larger tokens (one -> gone, phone, opponent).
- resoluteteeth 3y ago
  > I'm busy updating my Python SQLite extension with full text search and this is one of things I'll be addressing. You can do a fuzzy search by having the query OR all tokens for the match, and then use a ranking function to figure out an appropriate score. > I'm also doing other things like query expansion (find statistically similar search terms), and other issues like spelling correction (playystation -> playstation), split tokens (noone -> no one), combining (play station -> playstation), larger tokens (one -> gone, phone, opponent). That's nice but it just seems unfortunate that people have to keep reimplementing this stuff in different languages on top of sqlite
bomewish 3y ago
Problem with bigrams is that it will create a lot of nonwords. In the example given 你好世界 we’d get one bigram 好世 which no one is going to be looking for. Lots of wasted space in the index.