Properly install sqlite3 with FTS5 support

Question

I'm developing a Python tool which uses a sqlite3 virtual table with FTS5 (Full Text Search). I would like to know how to properly install from a tarball (or any other means) the needed requirements for my tool to work so I can pack them for portability.

Currently, I managed to install the latest release tarball of sqlite. However, when I execute:

python3 -c "import sqlite3; print(sqlite3.sqlite_version)"
# or
python2 -c "import sqlite3; print(sqlite3.sqlite_version)"

I get 3.11.0, while sqlite3 --version returns: 3.22.0 2018-01-22 18:45:57 0c55d179733b46d8d0ba4d88e01a25e10677046ee3da1d5b1581e86726f2alt1

The system version sqlite3 3.22 does support FTS5, as I do pragma compile_options; and get:

COMPILER=gcc-5.4.0 20160609
ENABLE_DBSTAT_VTAB
ENABLE_FTS4
**ENABLE_FTS5**
ENABLE_JSON1
ENABLE_RTREE
ENABLE_STMTVTAB
ENABLE_UNKNOWN_SQL_FUNCTION
HAVE_ISNAN
THREADSAFE=1

But, the python version, using this script returns this:

[(u'ENABLE_COLUMN_METADATA',), (u'ENABLE_DBSTAT_VTAB',), (u'ENABLE_FTS3',), (u'ENABLE_FTS3_PARENTHESIS',), (u'ENABLE_JSON1',), (u'ENABLE_LOAD_EXTENSION',), (u'ENABLE_RTREE',), (u'ENABLE_UNLOCK_NOTIFY',), (u'ENABLE_UPDATE_DELETE_LIMIT',), (u'HAVE_ISNAN',), (u'LIKE_DOESNT_MATCH_BLOBS',), (u'MAX_SCHEMA_RETRY=25',), (u'OMIT_LOOKASIDE',), (u'SECURE_DELETE',), (u'SOUNDEX',), (u'SYSTEM_MALLOC',), (u'TEMP_STORE=1',), (u'THREADSAFE=1',)]

Hence, my questions are:

1. Is there any way I could make a linux portable package for my app with sqlite3 FTS5 support in both python and linux system?
2. Is there any way to link the python module sqlite3 to an specific sqlite3 path?

I tried all of this in an Ubuntu 16.04 LTS, but I would like to work as well on CentOS 7.

Thank you very much in advance.

More details about the installation from the tarball that I did:

wget "https://www.sqlite.org/src/tarball/sqlite.tar.gz?r=release" -O sqlite.tar.gz
tar -xzvf sqlite.tar.gz
cd sqlite
./configure --enable-fts5
make
sudo make install

Strychnine

Answer 1

I think is a linking problem! I followed the same install steps with you and got the same results:

$ python ./test.py 
[(u'ENABLE_COLUMN_METADATA',), (u'ENABLE_FTS3',), (u'ENABLE_RTREE',), (u'ENABLE_UNLOCK_NOTIFY',), (u'ENABLE_UPDATE_DELETE_LIMIT',), (u'MAX_SCHEMA_RETRY=25',), (u'OMIT_LOOKASIDE',), (u'SECURE_DELETE',), (u'SOUNDEX',), (u'SYSTEM_MALLOC',), (u'TEMP_STORE=1',), (u'THREADSAFE=1',)]
NO

However, when you install something by configure/make/make install on Linux, it usually goes in /usr/local/lib. To make sure that python links on runtime against the correct .so I used LD_LIBRARY_PATH. In this case I got:

$ LD_LIBRARY_PATH=/usr/local/lib python ./test.py 
[(u'COMPILER=gcc-4.8.5',), (u'ENABLE_FTS5',), (u'HAVE_ISNAN',), (u'TEMP_STORE=1',), (u'THREADSAFE=1',)]
YES

Additionally, when installing libraries, you might have to update ldconfig. On my system (Ubuntu 14.04):

$ sudo ldconfig
$ python ./test.py 
[(u'COMPILER=gcc-4.8.5',), (u'ENABLE_FTS5',), (u'HAVE_ISNAN',), (u'TEMP_STORE=1',), (u'THREADSAFE=1',)]
YES

Notice that using LD_LIBRARY_PATH is not needed any more and python links against the correct lib. For this to happen you will need to have /usr/local/lib folder in your ld.so.conf somewhere... for me this is in:

$ grep -ir local /etc/ld.so.conf.d/
/etc/ld.so.conf.d/libc.conf:/usr/local/lib

Eigenvalue

I marked yours as the correct answer. I commented your answer here and added mine. Thanks. https://mcmap.net/q/1021449/-properly-install-sqlite3-with-fts5-support – Strychnine

Answer 2

The easy way is to use apsw (Another Python SQLite Wrapper). Its API is just a little different from sqlite3 and you can't just pip-install it (unless you're okay with outdated version), but the rest is good and you can have the most recent features of SQLite.

wget https://github.com/rogerbinns/apsw/releases/download/3.22.0-r1/apsw-3.22.0-r1.zip
unzip apsw-3.22.0-r1.zip
cd apsw-3.22.0-r1
python setup.py fetch --sqlite build --enable-all-extensions install

Then,

import apsw

apsw.Connection(':memory:').cursor().execute('pragma compile_options').fetchall()

Returns:

[('COMPILER=gcc-5.4.0 20160609',),
 ('ENABLE_API_ARMOR',),
 ('ENABLE_FTS3',),
 ('ENABLE_FTS3_PARENTHESIS',),
 ('ENABLE_FTS4',),
 ('ENABLE_FTS5',), 
 ('ENABLE_ICU',),
 ('ENABLE_JSON1',),
 ('ENABLE_RBU',),
 ('ENABLE_RTREE',),
 ('ENABLE_STAT4',),
 ('THREADSAFE=1',)]

The hard way is to compile Python with custom SQLite. More detail in this article by Charles Leifer.

Eskridge

I added my own answer and commented yours. Yet your information is constructive. https://mcmap.net/q/1021449/-properly-install-sqlite3-with-fts5-support – Strychnine

Answer 3

Thank you for your answers @urban and @saaj. I found your answers constructive.

The problem I see to @saaj answer is that it requires extra packages, specifically apsw package, which is not compatible with pypy, for example. I could not manage to make it work, but may be my fault.

I really like @urban answer. I did the process and got it working. I marked this answer as correct.

However I would like to add my own answer. Is quite aggressive but it worked for me. I created an Ubuntu docker with the following Dockerfile:

FROM ubuntu:16.04
RUN apt-get update -y
RUN DEBIAN_FRONTEND=noninteractive DEBCONF_NONINTERACTIVE_SEEN=true apt-get install -y apt-utils tzdata
RUN DEBIAN_FRONTEND=noninteractive DEBCONF_NONINTERACTIVE_SEEN=true dpkg-reconfigure tzdata
RUN echo "Europe/Berlin" > /etc/timezone
RUN dpkg-reconfigure -f noninteractive tzdata
RUN apt-get update -y
RUN apt-get install -y git build-essential sudo

Afterwards, inside the Ubuntu docker I did. In the process I remove sqlite3 and installed its dependencies, that I found in the following article. Afterwards I reinstalled python.

sudo apt-get update -y
echo "[ - Removing sqlite3... ]"
sudo apt-get remove -y sqlite3
sudo apt-get purge -y sqlite3
echo "[ - Installing sqlite3 dependencies... ]"
sudo apt-get install -y build-essential bzip2 git libbz2-dev libc6-dev libgdbm-dev libgeos-dev liblz-dev liblzma-dev libncurses5-dev libncursesw5-dev libreadline6 libreadline6-dev libsqlite3-dev libssl-dev lzma-dev python-dev python-pip python-software-properties python-virtualenv software-properties-common sqlite3 tcl tk-dev tk8.5-dev wget
echo "[ - Installing sqlite3... ]"
sudo wget "https://www.sqlite.org/src/tarball/sqlite.tar.gz?r=release" -O sqlite.tar.gz &> /dev/null
sudo tar -xzvf sqlite.tar.gz
cd sqlite
sudo ./configure --enable-fts5
sudo make
sudo make install
cd ..
echo "[ - Reinstalling python... ]"
sudo apt-get remove -y python python3 python-dev
sudo apt-get install -y --reinstall python2.7 python3 python-dev
sudo apt-get install -y build-essential bzip2 git libbz2-dev libc6-dev libgdbm-dev libgeos-dev liblz-dev liblzma-dev libncurses5-dev libncursesw5-dev libreadline6 libreadline6-dev libsqlite3-dev libssl-dev lzma-dev python-dev python-pip python-software-properties python-virtualenv software-properties-common sqlite3 tcl tk-dev tk8.5-dev wget

Strychnine

I think you mixed up me and @Eigenvalue in your answer. For the solution, if you're okay with packing not only your dependencies, as you wrote in the question, but your application as a whole in a container, then it's a good solution. Also probably sudo is redundant, because you're still root, but you can use USER once set up, to switch to less privileged one. – Eskridge

Yes, thanks. The sudo is for the installer once the docker is set. Also, the second part is usefule for non-docker installations, but yes, I could just say "execute as root". – Strychnine