sqlite SQLITE_ENABLE_DBSTAT_VTAB option

[cjrh]

The sqlite option SQLITE_ENABLE_DBSTAT_VTAB enables the dbstat virtual table that can show table sizes. The Python on my distro (Mint) was linked to a sqlite that had been compiled with this option, but it seems the default build-linux.py configuration does not set it.

I was going to make a PR to add it to cpython-unix/extension-modules.yml like so:

_sqlite3:
  sources:
    - _sqlite/connection.c
    - _sqlite/cursor.c
    - _sqlite/microprotocols.c
    - _sqlite/module.c
    - _sqlite/prepare_protocol.c
    - _sqlite/row.c
    - _sqlite/statement.c
    - _sqlite/util.c
  sources-conditional:
    - source: _sqlite/blob.c
      minimum-python-version: "3.11"
    - source: _sqlite/cache.c
      maximum-python-version: "3.10"
  includes-deps:
    - include
  includes:
    - Modules/_sqlite
  defines:
    - "MODULE_NAME=\\\"sqlite3\\\""
  defines-conditional:
    # Require dynamic binaries to load extensions. Cannot load on iOS.
    # 3.11+ uses opt in. <3.11 uses opt out.
    - define: PY_SQLITE_ENABLE_LOAD_EXTENSION=1
      targets:
        - .*-apple-darwin
        # TODO this should likely be restricted to gnu since musl is statically
        # linked. But this would break verification code. So enabled for
        # backwards compatibility.
        - .*-unknown-linux-.*
    # This enables the sqlite3_dbstat virtual table, which is typically
    # enabled on linux distro builds.
    # https://www.sqlite.org/dbstat.html
    - define: SQLITE_ENABLE_DBSTAT_VTAB=1                                # <------- HERE
      targets:
        - .*-apple-darwin
        - .*-unknown-linux-.*
    - define: SQLITE_OMIT_LOAD_EXTENSION=1
      targets:
        - .*-ios
  links:
    - sqlite3

However, this doesn't work. In the resulting python build the compile option has not been set:

$ ./build-linux.py --python cpython-3.12
<snip compiler output>
$ cd dist
$ tar --zstd -xf cpython-3.12.5-x86_64-unknown-linux-gnu-noopt-20240824T1350.tar.zst 
$ ./python/install/bin/python
Python 3.12.5 (main, Aug 29 2024, 13:57:20) [Clang 18.1.8 ] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> sqlite3.Connection(":memory:").execute("pragma compile_options;").fetchall()
[('ATOMIC_INTRINSICS=1',), ('COMPILER=clang-18.1.8',), ('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_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',)]

You can see that ENABLE_DBSTAT_VTAB is missing. However, if instead I insert the option into the build-sqlite.sh script like this:

<snip>
CFLAGS="${EXTRA_TARGET_CFLAGS} -DSQLITE_ENABLE_DBSTAT_VTAB -fPIC" CPPFLAGS="${EXTRA_TARGET_CFLAGS} -fPIC" LDFLAGS="${EXTRA_TARGET_LDFLAGS}" ./configure ${CONFIGURE_FLAGS}
<snip>

The build does work and show the option in the pragma compile_options output.

What am I misunderstanding about how to set defines in extension-modules.yml?

[indygreg]

I would add the define to the shell script, which is what builds SQLite itself.

The YAML config is for configuring how the Python C extensions are built, not how SQLite itself is built.

[indygreg]

Oh, I support enabling this feature. And any other SQLite features that are commonly enabled in the wild. Assuming the risks/ downsides are minimal of course. For opt in features like this, I don't see any harm.