Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

SQLite FTS5 Extension

sqlite.org

Post reply on HN
Print view View original

41–50 of 61 posts

Re: SQLite FTS5 Extension

#41

Post by jbaiter »

jerrygenser wrote: post #38
jbaiter wrote: post #24

Earlier quoted context omitted.

One thing you could do: FTS5 has the `fts5vocab` virtual table [1] that has all the terms. You could provide a user-defined function that computes the levenshtein distance between your query terms and the terms in that table, obtain candidate terms that way and build a big query that searches for all those lexically close terms. [1] https://www.sqlite.org/fts5.html#the_fts5vocab_virtual_table...

I can confirm an approach like this works in practice. Although instead of levenshtein I use spellfix (maybe it uses that under the covers? not sure). If there is no match from the first search, I use the sqlite spellfix extension [0] to find matches. Then feed those candidates into the terms. https://www.sqlite.org/spellfix1.html

Ah, that's great, didn't know about that :-) It seems to use a similar edit-distance algorithm under the hood, and the docs explicitely mention integration with the FTS extension, so this is probably the way to go!

Re: SQLite FTS5 Extension

#42

Post by troupo »

wahnfrieden wrote: post #5
simonw wrote: post #3

This is hugely underrated in my opinion: it’s a very competent search engine. It also ships as part of the Python standard library, so if your machine has Python installed you have a high quality search engine ready to use without installing anything else. I have a CLI tool (and Python library) for working with it here: https://sqlite-utils.datasette.io/en/stable/cli.html#configu...

though useless for many non english languages

Of all/many opensource projects I looked at, only meilisearch is good for non-English languages: https://www.meilisearch.com/docs/learn/resources/language

Re: SQLite FTS5 Extension

#43

Post by wahnfrieden »

m_fayer wrote: post #28
wahnfrieden wrote: post #19

Earlier quoted context omitted.

Yes

How’d it go?

GRDB on iOS and macOS

It’s easy and works well

It is slower than Realm in some benchmarking (I forget if it’s reads or writes) and might be less memory friendly (Realm does lazy evaluation). You need to use some third party open source to make sqlite faster by precompiling model definitions, which I didn’t bother with. I currently only use it for fts5 and Realm for other needs, but would like to try using it for more.

I also recommend looking at https://skip.tools which has cross platform SQLite for iOS and Android (you write your app in swift and SwiftUI and it generates the Android project and kotlin code)

Btw I use GRDB in my iOS/macOS app here: https://reader.manabi.io Manabi Reader, a Japanese learning app. I use SQLite for dictionary searches which works ok for Japanese only because I’m only searching dictionary expressions and not sentences

Re: SQLite FTS5 Extension

#44

Post by wahnfrieden »

troupo wrote: post #42
wahnfrieden wrote: post #5

Earlier quoted context omitted.

though useless for many non english languages

Of all/many opensource projects I looked at, only meilisearch is good for non-English languages: https://www.meilisearch.com/docs/learn/resources/language

Japanese support looks good. But it looks like I can’t deploy this inside an iOS app :(

Re: SQLite FTS5 Extension

#45

Post by [deleted] »

wahnfrieden wrote: post #5
simonw wrote: post #3

This is hugely underrated in my opinion: it’s a very competent search engine. It also ships as part of the Python standard library, so if your machine has Python installed you have a high quality search engine ready to use without installing anything else. I have a CLI tool (and Python library) for working with it here: https://sqlite-utils.datasette.io/en/stable/cli.html#configu...

though useless for many non english languages

[deleted]

Re: SQLite FTS5 Extension

#46

Post by iansinnott »

ThatPlayer wrote: post #25
iansinnott wrote: post #2

It's a very useful feature of sqlite and it also works great in-browser using wa-sqlite[0]. Example, if anyone's curious [1]. [0]: https://github.com/rhashimoto/wa-sqlite [1]: https://github.com/iansinnott/prompta/blob/master/src/lib/mi...

I've been trying something similar, but using https://github.com/mmomtchev/sqlite-wasm-http to stream the database over http for a SPA without a backend. It's actually able to do searches (for queries that aren't super short) without downloading the entire FTS table.

Interesting, thanks for sharing. I didn't realize you could use search features when streaming the db over a network.

Re: SQLite FTS5 Extension

#47

Post by plq »

wahnfrieden wrote: post #44
troupo wrote: post #42

Earlier quoted context omitted.

Of all/many opensource projects I looked at, only meilisearch is good for non-English languages: https://www.meilisearch.com/docs/learn/resources/language

Japanese support looks good. But it looks like I can’t deploy this inside an iOS app :(

Why not?

Re: SQLite FTS5 Extension

#48

Post by [deleted] »

jbaiter wrote: post #24
spa5k wrote: post #20

Earlier quoted context omitted.

I don't think that will work for me, since I needed something that can handle mistakes in the words, like Du'ha to duha etc, Rahman to rehman, basically whatever looks closest.

One thing you could do: FTS5 has the `fts5vocab` virtual table [1] that has all the terms. You could provide a user-defined function that computes the levenshtein distance between your query terms and the terms in that table, obtain candidate terms that way and build a big query that searches for all those lexically close terms. [1] https://www.sqlite.org/fts5.html#the_fts5vocab_virtual_table...

[deleted]

Re: SQLite FTS5 Extension

#50

Post by bomewish »

We are running FTS5 with a bunch of sqlite databases for an internal search tool, one with 15m records (we sharded that one, one shard per core), others with 200k-2m records. Speed is very fast -- the biggest dbs are hundreds of ms response time, but others are sub 10 or tens of ms. By _far_ the simplest search option for a small organization where a lot of the data is already in a SQL db, and we don't have the resources to stand up and maintain an ES cluster.
Post reply on HN