Dockerfile should build more recent SQLite with FTS5 and spatialite support

[simonw]

The SQLite bundled with Python 3 doesn't support the FTS5 search extension. It would be nice if the SQLite built by our Dockerfile could support as many modern SQLite features as possible.

https://web.archive.org/web/20170212034155/http://charlesleifer.com/blog/using-the-sqlite-json1-and-fts5-extensions-with-python/ has instructions on building a more recent SQLite and the pysqlite package. Our Dockerfile could carry out an updated version of this process.

[simonw]

Spatial extensions would be really useful too. https://www.gaia-gis.it/spatialite-2.1/SpatiaLite-manual.html

[simonw]

http://charlesleifer.com/blog/going-fast-with-sqlite-and-python/ is useful here too.

[simonw]

Also requested on Twitter: https://twitter.com/DenubisX/status/930322813864439808

[ingenieroariel]

@simonw On the spatialite support, here is some info to make it work and a screenshot:

I used the following Dockerfile:

FROM prolocutor/python3-sqlite-ext:3.5.1-spatialite as build

RUN mkdir /code
ADD . /code/

RUN pip install /code/

EXPOSE 8001
CMD ["datasette", "serve", "/code/ne.sqlite", "--host", "0.0.0.0"]

and added this to prepare_connection:

  conn.enable_load_extension(True)
  conn.execute("SELECT load_extension('/usr/local/lib/mod_spatialite.so')")

[simonw]

That's fantastic! Thank you very much for that.

Do you know if it's possible to view the Dockerfile used by https://hub.docker.com/r/prolocutor/python3-sqlite-ext/ ?

[simonw]

Found a relevant Dockerfile on Reddit: https://www.reddit.com/r/Python/comments/5unkb3/install_sqlite3_on_python_3/ddzdz2b/

[simonw]

Maybe part of the solution here is to add a --load-extension argument to datasette - so when you run the command you can specify SQLite extensions that should be loaded.

[simonw]

OK, --load-extension is now a supported command line option - see #110 which includes my notes on how I manually tested it using the prolocutor/python3-sqlite-ext Docker image.

[simonw]

The fact that prolocutor/python3-sqlite-ext doesn't provide a visible Dockerfile and hasn't been updated in two years makes me hesitant to bake it into datasette itself. I'd rather put together a Dockerfile that enables the necessary extensions and can live in the datasette repository itself.

[simonw]

The JSON extension would be very worthwhile too: https://www.sqlite.org/json1.html

[ingenieroariel]

I'll try to find alternatives to the Dockerfile option - I also think we should not use that old one without sources or license.

[simonw]

We now have a Dockerfile that compiles spatialite! 6c6b63d

[simonw]

OK, I can confirm that the version in the new docker container supports FTS5, JSON and spatialite!

Notes on how I built the container and tested the spatialite extension are here: #112 (comment)

To confirm that JSON and FTS5 are working, I ran the following:

$ docker run -it -p 8001:8001 6c9ca7e29181 python
Python 3.6.3 (default, Nov  4 2017, 14:24:48) 
[GCC 6.3.0 20170516] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> sqlite3.connect(':memory:').execute('CREATE VIRTUAL TABLE email USING fts5(sender, title, body);')
<sqlite3.Cursor object at 0x7f2d90839960>
>>> list(sqlite3.connect(':memory:').execute('''SELECT json(' { "this" : "is", "a": [ "test" ] } ') '''))
[('{"this":"is","a":["test"]}',)]

If I do the same thing in python3 on my OS X laptop directly, I get this:

$ python3
Python 3.5.1 (default, Apr 18 2016, 11:46:32) 
[GCC 4.2.1 Compatible Apple LLVM 7.3.0 (clang-703.0.29)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> sqlite3.connect(':memory:').execute('CREATE VIRTUAL TABLE email USING fts5(sender, title, body);')
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
sqlite3.OperationalError: no such module: fts5
>>> list(sqlite3.connect(':memory:').execute('''SELECT json(' { "this" : "is", "a": [ "test" ] } ') '''))
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
sqlite3.OperationalError: no such function: json