How to use editdist3 in sqlite

Question

According to an answer to another question, in sqlite the Levenshtein distance is implemented in a SQL function called editdist3. (Compare also the documentation)

Now when I try to use it, all I get is an error that it doesn’t exist:

╰┄┄> sqlite3
SQLite version 3.11.1 2016-03-03 16:17:53
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE test (col1 TEXT);
sqlite> INSERT INTO test VALUES ('foobar');
sqlite> SELECT * FROM test WHERE editdist3(col1, 'f00bar') < 3;
Error: no such function: editdist3

I’m using sqlite-3.11.1 on Gentoo Linux with (default) USE flags icu, readline and secure-delete.

Brachypterous

Answer 1

It turns out editdist3 is contained in an sqlite extension that has to be loaded explicitly. As I didn’t find it in the Gentoo sqlite package, I also had to build it myself. As the documentation says:

The spellfix1 virtual table is not included in the SQLite amalgamation and is not a part of any standard SQLite build. It is a loadable extension.

First I fetched the source code

wget https://sqlite.org/2016/sqlite-src-3110100.zip
unzip sqlite-src-3110100.zip

then it has to be compiled

gcc -shared -fPIC -Wall -Isqlite-src-3110100 sqlite-src-3110100/ext/misc/spellfix.c -o spellfix.so

and finally it can be loaded with

.load ./spellfix

Note that sqlite automatically appends the extension .so.

To use it in python – which was my original intention – the following needs to be done:

db = sqlite3.connect(':memory:')

db.enable_load_extension(True)
db.load_extension('./spellfix')
db.enable_load_extension(False)

Brachypterous

fatal error: sqlite3ext.h: No such file or directory: which library should I install first? apt-get install unzip build-essentials of course, but what else? I've searched libsqlite but there are dozains of such packages. Thanks in advance @Brachypterous – Bayer

@Basj: I assume you're on a Debian-based distribution. Then the package you need is libsqlite3-dev. See e.g. packages.debian.org/… – Brachypterous

Thanks a lot @bodo! By the way, I just discovered debian.org/distrib/packages#search_contents that I didn't know, thanks! – Bayer

For future reference: here is a detailed HOWTO + ready-to-use example for both Windows and Linux. – Bayer