Mac: sqlite3 command-line one-liner load extension

You are using the sqlite3 command-line binary and you need an extension, for example the Levenshtein algorithm which will allow you to find in a database text similar to other texts, let’s say you would like to consider “Jules Verne” and “Julio Verne” the same author.

You learn that you can load the spellfix extension which will allow you to use a function called editdist3, which is this algorithm (also called edit-distance, meaning the amount of changes needed in a string to look like another string).

Mac OS X’s built-in sqlite3 binary doesn’t allow you to load extensions (maybe because of security issues), so after some time googling, you do the following:

1. Install an alternate copy of sqlite3 through brew (this is a must-have utility if you are some kind of programmer):

   $ brew install sqlite

   This will install a new copy of the sqlite3 binary with the ability to load extensions enabled by default. You will keep your system’s sqlite3 unalthered, so you are safe, as you will be the only one using the “unsafe” sqlite3, installed in
   /usr/local/opt/sqlite3/bin/sqlite3.

2. Compile yourself the spellfix extension. You will download sqlite’s source code. I got mine from here: https://www.sqlite.org/src/, clicking here or here =>
   
   You may get the tarball if you feel better.
   Unzip the file and look for spellfix.c inside sqlite/ext/misc. Right? Now let’s compile it following official sqlite’s instructions:

   $ cd /path/to/sqlite/ext/misc

   $ gcc -g -fPIC -dynamiclib spellfix.c -o spellfix.dylib

   If you are compiling a different extension, just substitute the words in red (spellfix.c and spellfix.dylib) with the right ones. If you receive this kind of message:

   -bash: gcc: command not found

   Then you dont’ have installed Apple’s command-line tools for Xcode. Go and get them @ Apple’s developer website.

3. Okay. This was the easy part. Now you are ready to load and run your brand-new copy of sqlite3 with loading-extensions enabled, and your brand-new compiled extension. It doesn’t exist a man page for sqlite3 and you don’t realize the how-to if you happen to run sqlite3 -help in a Terminal window… Using the trial and error method for minutes or hours, depending on your skills or how spirited you are, you will find the syntax. This is the one-liner, as I will be running this code from within another utility which doesn’t let me interact with sqlite3. And this is the reason of this entire post:

   cd /path/to/dir/with/spellfix/; /usr/local/opt/sqlite3/bin/sqlite3 /path/to/database.db 'SELECT load_extension("spellfix.dylib")' 'SELECT title FROM books WHERE editdist3(author, "Jules Verne") < 450'

I colored some things:

/path/to/dir/with/spellfix/ You will run your command from within the directory containing spellfix.dylib. Most probably you could specify the full path to spellfix.dylib later in the load_extension command, but this could lead to issues depending on your OS (ie, Win), so I prefer it this way.

Now follows the full path to the new sqlite3 binary you installed, as the system’s built-in sqlite3 won’t do the job.

/path/to/database.db This is the full path to the database. This means that we will be running the load_extension command from within SQL, not from the binary itself. I didn’t find a way. I tried using the -cmd switch mixed with the dot-command syntax (.load extension) but didn’t work.

load_extension(“spellfix.dylib”) Now we run two separate SQL statements, quoted and space-separated. In the first one we load the extension. In the second one, our statements (ie, searching for something).

editdist3(author, “Verne, Jules”) And here is how we are using the function which is available only after loading the spellfix extension.

BTW, here is the link to the spellfix documentation, just in case you are interested in its capabilities => https://www.sqlite.org/spellfix1.html

Advertisement