We should apply some of the recommended "optimisation" compile-time options[1] to the SQLite builds for the macOS and Windows installers. The following options should be safe to apply:

- SQLITE_DEFAULT_MEMSTATUS=0
- SQLITE_LIKE_DOESNT_MATCH_BLOBS
- SQLITE_MAX_EXPR_DEPTH=0
- SQLITE_OMIT_DEPRECATED
- SQLITE_OMIT_AUTOINIT

I'm not sure about SQLITE_DEFAULT_WAL_SYNCHRONOUS=1.

Quoting the SQLite docs:
"So these options do not make a huge difference. But in some design situations, every little bit helps."


[1] https://sqlite.org/compile.html

BTW, is SQLITE_WITHOUT_ZONEMALLOC still needed for macOS?

I suggest applying the following safe options for the installers: 

# SQLITE_DEFAULT_MEMSTATUS
Memory status is currently not available in the sqlite3 API, so there's no need for SQLite to keep track of this. If we add such an API (not likely, IMO), we can just remove this compile time option. See also bpo-35056.

# SQLITE_OMIT_DEPRECATED
No deprecated API functions are used by the sqlite3 module.

# SQLITE_OMIT_AUTOINIT
The sqlite3 module explicitly initialises SQLite; we can safely apply this option.


I'll update the PR's.

Berker, do you have any opinion about this?

As noted in https://github.com/python/cpython/pull/25414#issuecomment-828501078, SQLITE_OMIT_DEPRECATED also leaves out PRAGMA's, which can break applications. I'll update the PR's to leave it out.

As long as we don't introduce behavior changes between SQLite versions in mainstream Linux distributions and macOS/Windows (i.e. an application should continue working in Linux, macOS, Windows), it sounds good to me.

Maybe it's worth checking what compile options Debian (and others) are using when packaging SQLite.

Here's a list of the SQLite recommended compile-time options (only):

- macOS (v11.2.3) (SQLite v3.32.3) defines SQLITE_DEFAULT_WAL_SYNCHRONOUS=1
- Homebrew (SQLite v3.35.4) does not define any of the recommended compile-time options
- Debian Buster (SQLite v3.27.2) defines SQLITE_LIKE_DOESNT_MATCH_BLOBS
- Ubuntu 20.04 (SQLite v3.31.1) defines SQLITE_LIKE_DOESNT_MATCH_BLOBS

AFAICS, adding SQLITE_DEFAULT_MEMSTATUS=0 and SQLITE_OMIT_AUTOINIT is safe, as it only affects the C API that we use, and nothing else.

Oh, well, SQLITE_DEFAULT_MEMSTATUS=0 does in fact affect PRAGMA soft_heap_limit. Looks like I'm left with only SQLITE_OMIT_AUTOINIT, then :)

If Berker does not disapprove, I'd like to apply GH-25413 and GH-25414 to the installers, Steve & Ned.

New changeset 555cbbe7c10c29ec4358c66c247b8d7e871a5f7b by Erlend Egeberg Aasland in branch 'master':
bpo-43851: Build SQLite with SQLITE_OMIT_AUTOINIT on macOS (GH-25413)
https://github.com/python/cpython/commit/555cbbe7c10c29ec4358c66c247b8d7e871a5f7b

New changeset 8702b667d8e7da7a1888297d84f493f26c580a2d by Erlend Egeberg Aasland in branch 'main':
bpo-43851: Build SQLite with SQLITE_OMIT_AUTOINIT on Windows (GH-25414)
https://github.com/python/cpython/commit/8702b667d8e7da7a1888297d84f493f26c580a2d