Compile SQLite with -DENABLE_FTS3_PARENTHESIS

[sandhose]

We were evaluating using this python distribution for our Docker image instead of the official python3 image, but we've encountered an interesting difference in our test suite: the SQLite extension isn't compiled with the same flags.

With docker.io/library/python

$ docker run -it --rm python
Python 3.13.2 (main, Feb 25 2025, 21:31:02) [GCC 12.2.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> for (a,) in sqlite3.connect(":memory:").execute("PRAGMA compile_options;").fetchall():
...   print(a)
...
ATOMIC_INTRINSICS=1
COMPILER=gcc-12.2.0
DEFAULT_AUTOVACUUM
DEFAULT_CACHE_SIZE=-2000
DEFAULT_FILE_FORMAT=4
DEFAULT_JOURNAL_SIZE_LIMIT=-1
DEFAULT_MMAP_SIZE=0
DEFAULT_PAGE_SIZE=4096
DEFAULT_PCACHE_INITSZ=20
DEFAULT_RECURSIVE_TRIGGERS
DEFAULT_SECTOR_SIZE=4096
DEFAULT_SYNCHRONOUS=2
DEFAULT_WAL_AUTOCHECKPOINT=1000
DEFAULT_WAL_SYNCHRONOUS=2
DEFAULT_WORKER_THREADS=0
ENABLE_COLUMN_METADATA
ENABLE_DBSTAT_VTAB
ENABLE_FTS3
ENABLE_FTS3_PARENTHESIS
ENABLE_FTS3_TOKENIZER
ENABLE_FTS4
ENABLE_FTS5
ENABLE_LOAD_EXTENSION
ENABLE_MATH_FUNCTIONS
ENABLE_PREUPDATE_HOOK
ENABLE_RTREE
ENABLE_SESSION
ENABLE_STMTVTAB
ENABLE_UNLOCK_NOTIFY
ENABLE_UPDATE_DELETE_LIMIT
HAVE_ISNAN
LIKE_DOESNT_MATCH_BLOBS
MALLOC_SOFT_LIMIT=1024
MAX_ATTACHED=10
MAX_COLUMN=2000
MAX_COMPOUND_SELECT=500
MAX_DEFAULT_PAGE_SIZE=32768
MAX_EXPR_DEPTH=1000
MAX_FUNCTION_ARG=127
MAX_LENGTH=1000000000
MAX_LIKE_PATTERN_LENGTH=50000
MAX_MMAP_SIZE=0x7fff0000
MAX_PAGE_COUNT=1073741823
MAX_PAGE_SIZE=65536
MAX_SCHEMA_RETRY=25
MAX_SQL_LENGTH=1000000000
MAX_TRIGGER_DEPTH=1000
MAX_VARIABLE_NUMBER=250000
MAX_VDBE_OP=250000000
MAX_WORKER_THREADS=8
MUTEX_PTHREADS
OMIT_LOOKASIDE
SECURE_DELETE
SOUNDEX
SYSTEM_MALLOC
TEMP_STORE=1
THREADSAFE=1
USE_URI
>>>

With this build

docker run --rm -it ghcr.io/astral-sh/uv:debian uv run --python 3.13 python
Python 3.13.2 (main, Feb 12 2025, 14:38:11) [GCC 6.3.0 20170516] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> for (a,) in sqlite3.connect(":memory:").execute("PRAGMA compile_options;").fetchall():
...     print(a)
...
ATOMIC_INTRINSICS=1
COMPILER=gcc-6.3.0 20170516
DEFAULT_AUTOVACUUM
DEFAULT_CACHE_SIZE=-2000
DEFAULT_FILE_FORMAT=4
DEFAULT_JOURNAL_SIZE_LIMIT=-1
DEFAULT_MMAP_SIZE=0
DEFAULT_PAGE_SIZE=4096
DEFAULT_PCACHE_INITSZ=20
DEFAULT_RECURSIVE_TRIGGERS
DEFAULT_SECTOR_SIZE=4096
DEFAULT_SYNCHRONOUS=2
DEFAULT_WAL_AUTOCHECKPOINT=1000
DEFAULT_WAL_SYNCHRONOUS=2
DEFAULT_WORKER_THREADS=0
DIRECT_OVERFLOW_READ
ENABLE_DBSTAT_VTAB
ENABLE_FTS3
ENABLE_FTS4
ENABLE_FTS5
ENABLE_GEOPOLY
ENABLE_MATH_FUNCTIONS
ENABLE_RTREE
MALLOC_SOFT_LIMIT=1024
MAX_ATTACHED=10
MAX_COLUMN=2000
MAX_COMPOUND_SELECT=500
MAX_DEFAULT_PAGE_SIZE=8192
MAX_EXPR_DEPTH=1000
MAX_FUNCTION_ARG=127
MAX_LENGTH=1000000000
MAX_LIKE_PATTERN_LENGTH=50000
MAX_MMAP_SIZE=0x7fff0000
MAX_PAGE_COUNT=0xfffffffe
MAX_PAGE_SIZE=65536
MAX_SQL_LENGTH=1000000000
MAX_TRIGGER_DEPTH=1000
MAX_VARIABLE_NUMBER=32766
MAX_VDBE_OP=250000000
MAX_WORKER_THREADS=8
MUTEX_PTHREADS
SYSTEM_MALLOC
TEMP_STORE=1
THREADSAFE=1

This has the impact of full-text queries not working properly:

$ docker run -it --rm python
Python 3.13.2 (main, Feb 25 2025, 21:31:02) [GCC 12.2.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> con = sqlite3.connect(":memory:")
>>> con.execute("CREATE VIRTUAL TABLE docs USING fts4(message);").fetchall()
[]
>>> con.execute("INSERT INTO docs VALUES ('Message number 4');").fetchall()
[]
>>> con.execute("SELECT * FROM docs WHERE docs MATCH 'Message AND 4';").fetchall()
[('Message number 4',)]

and with this build:

$ docker run --rm -it ghcr.io/astral-sh/uv:debian uv run --python 3.13 python
Python 3.13.2 (main, Feb 12 2025, 14:38:11) [GCC 6.3.0 20170516] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> con = sqlite3.connect(":memory:")
>>> con.execute("CREATE VIRTUAL TABLE docs USING fts4(message);").fetchall()
[]
>>> con.execute("INSERT INTO docs VALUES ('Message number 4');").fetchall()
[]
>>> con.execute("SELECT * FROM docs WHERE docs MATCH 'Message AND 4';").fetchall()
[]

FWIW, those are the flags Debian uses for building sqlite (and therefore the sqlite extension in the docker.io/library/python image): https://sources.debian.org/src/sqlite3/3.40.1-2%2Bdeb12u1/debian/rules/#L43-L61

So, I'm interested in having the -DENABLE_FTS3_PARENTHESIS flag enabled in particular, but it might be worth aligning with how popular distributions compile SQLite?

It's worth noting that the documentation about FTS in SQLite states:

New applications should also define the SQLITE_ENABLE_FTS3_PARENTHESIS macro to enable the enhanced query syntax

[zanieb]

Similar to #309 and #449 we're supportive of adding any common SQLite flags. Contribution welcome.