Optimise SQLite builds on macOS and Windows

[erlend-aasland]

BPO	43851
Nosy	@pfmoore, @ronaldoussoren, @tjguk, @ned-deily, @berkerpeksag, @zware, @zooba, @erlend-aasland
PRs	bpo-43851: Build SQLite with SQLITE_OMIT_AUTOINIT on macOS #25413 bpo-43851: Build SQLite with SQLITE_OMIT_AUTOINIT on Windows #25414

^{Note: these values reflect the state of the issue at the time it was migrated and might not reflect the current state.}

Show more details

GitHub fields:

assignee = None
closed_at = <Date 2021-10-19.11:02:40.198>
created_at = <Date 2021-04-14.23:16:08.949>
labels = ['OS-mac', 'type-feature', '3.10', 'OS-windows']
title = 'Optimise SQLite builds on macOS and Windows'
updated_at = <Date 2021-10-19.11:02:40.198>
user = 'https://github.com/erlend-aasland'

bugs.python.org fields:

activity = <Date 2021-10-19.11:02:40.198>
actor = 'erlendaasland'
assignee = 'none'
closed = True
closed_date = <Date 2021-10-19.11:02:40.198>
closer = 'erlendaasland'
components = ['macOS', 'Windows']
creation = <Date 2021-04-14.23:16:08.949>
creator = 'erlendaasland'
dependencies = []
files = []
hgrepos = []
issue_num = 43851
keywords = ['patch']
message_count = 10.0
messages = ['391109', '391110', '392200', '392204', '392206', '392233', '392235', '392395', '392753', '404288']
nosy_count = 8.0
nosy_names = ['paul.moore', 'ronaldoussoren', 'tim.golden', 'ned.deily', 'berker.peksag', 'zach.ware', 'steve.dower', 'erlendaasland']
pr_nums = ['25413', '25414']
priority = 'normal'
resolution = 'fixed'
stage = 'resolved'
status = 'closed'
superseder = None
type = 'enhancement'
url = 'https://bugs.python.org/issue43851'
versions = ['Python 3.10']

[erlend-aasland]

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

[erlend-aasland]

BTW, is SQLITE_WITHOUT_ZONEMALLOC still needed for macOS?

[erlend-aasland]

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?

[erlend-aasland]

As noted in #25414 (comment), SQLITE_OMIT_DEPRECATED also leaves out PRAGMA's, which can break applications. I'll update the PR's to leave it out.

[berkerpeksag]

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.

[erlend-aasland]

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.

[erlend-aasland]

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 :)

[erlend-aasland]

If Berker does not disapprove, I'd like to apply #69599 and #69600 to the installers, Steve & Ned.

[ned-deily]

New changeset 555cbbe by Erlend Egeberg Aasland in branch 'master':
bpo-43851: Build SQLite with SQLITE_OMIT_AUTOINIT on macOS (GH-25413)
555cbbe

[zooba]

New changeset 8702b66 by Erlend Egeberg Aasland in branch 'main':
bpo-43851: Build SQLite with SQLITE_OMIT_AUTOINIT on Windows (GH-25414)
8702b66