Figure out how to bundle a more up-to-date SQLite

[simonw]

The version of SQLite that ships with Python 3 is a bit limited - it doesn't support row values for example https://www.sqlite.org/rowvalue.html

Figure out how to bundle a more recent SQLite engine with datasette. We need to figure out two cases:

• Bundling a recent version in a Dockerfile build. I expect this to be quite easy.
• Making a more recent version available to people hacking around in Mac OS X. I have no idea how to start on this.

I want it working on Mac OS X too because I don't want to force Docker as a dependency for anyone who just want to hack around with Datasette a little and run the test suite.

[simonw]

I think this is pretty hard. @coleifer has done some work in this direction, including https://github.com/coleifer/pysqlite3 which ports the standalone pysqlite module to Python 3.

[coleifer]

I don't think it should be too difficult... you can look at what @ghaering did with pysqlite (and similarly what I copied for pysqlite3). You would theoretically take an amalgamation build of Sqlite (all code in a single .c and .h file). The AmalgamationLibSqliteBuilder class detects the presence of this amalgamated source file and builds a statically-linked pysqlite.

[simonw]

I don't know how it happened, but I've somehow got myself into a state where my local SQLite for Python 3 on OS X is 3.23.1:

~ $ python3
Python 3.6.5 (default, Mar 30 2018, 06:41:53) 
[GCC 4.2.1 Compatible Apple LLVM 9.0.0 (clang-900.0.39.2)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> sqlite3.connect(':memory:').execute('select sqlite_version()').fetchall()
[('3.23.1',)]
>>> 

Maybe I did something in homebrew that changed this? I'd love to understand what exactly I did to get to this state.

[coleifer]

Python standard-library SQLite dynamically links against the system sqlite3. So presumably you installed a more up-to-date sqlite3 somewhere on your LD_LIBRARY_PATH.

To compile a statically-linked pysqlite you need to include an amalgamation in the project root when building the extension. Read the relevant setup.py.

[simonw]

I ran an informal survey on twitter and most people were on 3.21 - https://twitter.com/simonw/status/1001487546289815553

Maybe this is from upgrading to the latest OS X release.

[simonw]

I consider this resolved by #46