Fuzzy Search of a sqlite database?

General
1

I have an SQLite database for music. There are three columns per row: Artist, Title, Filename.

The database can get quite large, on my system it has about 4.2 million rows.

Looking for a way to search the entire db using a fuzzy match. To catch things like minor misspellings, and character replacements (for example, both “kesha” and “Ke$ha” should yield the same results) and before I dive into this on my own, I wanted to see how others have accomplished this before.

All the big music apps like Spoitfy, YouTube, Amazon Music etc all seem to have it figured out on much larger databases than mine (then again they’re probably not using Sqlite) so I’m curious what the standard approach for something like this is.

I’m aware of algorithms like Levenshtein Distance I just am not sure how I would use those with SQLite.

Happy New Year and thanks in advance for any tips!

2

A quick google search produced Spellfix1 virtual table. The Spellfix1 Virtual Table

1 Like
3

Thanks for that @Bob_Keeney3 ! I was completely unaware of Sqlite extensions. Will look deeper into this. Cheers!

4

No problem. I hadn’t seen anything on the forums so I figured someone in the larger developer community MUST have asked something similar. That search took me to StackOverflow which led me to the SQLite page. I would say, in general, that unless it’s something specific to Xojo, a generic web search may yield better results. YMMV.

1 Like
5

I use FTS to do full text search in SQLite for a Valentina database. The table is created with the following code:

CREATE VIRTUAL TABLE bodyindex USING fts4(tokenize=unicode61,content='', messagebody);

The SQLite in the MBS plugin has a unicode extension which can be loaded with

IsICULoaded = InternalSQLiteLibraryMBS.LoadICU

1 Like
6

Not sure if loading SQLite extensions has become easier with Xojo now, but just in case, here’s a way to do it:

db.SQLSelect ("SELECT load_extension ("""+extFile.NativePath+""", ""sqlite3_icu_init"")")

The above is used to load the ICU extension (sqlite3_icu_extension.dylib) so that you get unicode support for textual functions and comparisons.

See also: SQLite ICU (unicode support) for Mac updated

3 Likes