1. In Python, PHP, and JS, it seems there isn't a convenient way to use sqlite3_wal_hook?

2. Since wal2 hasn't been merged into the main branch, does this mean it is still unstable?

In Python, PHP, and JS, it seems there isn't a convenient way to use sqlite3_wal_hook?

While i cannot speak to any Python or PHP wrappers, nor any of the numerous 3rd-party JS wrappers, this project's own JS/WASM build does not currently support WAL mode. There are no near-term plans to do so, for the simple reason that there are always higher-priority things on the TODO and NiceToHave lists.

this project's own JS/WASM build does not currently support WAL mode

You keep saying that the WASM build doesn't support WAL mode, but I think it's been established that it does work with PRAGMA locking_mode=EXCLUSIVE (and so this callback might make sense in that context).

Is the project stance that even though this works it isn't supported usage? Or perhaps you're just forgetting that you changed the build so this could work? It seems strange to intentionally enable code for unsupported use.

You keep saying that the WASM build doesn't support WAL mode, but I think it's been established that it does work with PRAGMA locking_mode=EXCLUSIVE (and so this callback might make sense in that context).

It was my recollection that we still specifically build with OMIT_WAL, but that is indeed no longer the case.

Is the project stance that even though this works it isn't supported usage?

It's been a position of "it's completely untested, therefore no claim to support can be made, so it seems better to claim it's not supported." (whether or not that's the better claim is debatable, of course).

Or perhaps you're just forgetting that you changed the build...

And then there's that, too ;).

so this could work?

Perhaps, but it's not been explored on this end.

It seems strange to intentionally enable code for unsupported use.

Indeed. It was always my intent, after the very first WAL-related build errors 2 years ago, to keep OMIT_WAL until/unless it became necessary to go down that rabbit hole. That OMIT got removed from the build at Nuno's request, but the corresponding rabbit hole has never been explored.

i've added a note to my near-term TODO list to try out the locking_mode=exclusive approach, but...

One definite issue will be that the "oo1" API (which is the one end users use) runs a set of hard-coded pragmas after open()ing each db, in order to tweak the page cache size and the journal mode. Per the WAL docs, however, locking_mode=exclusive has to be run "before the first attempted access." That won't be possible with the oo1 API unless we force that mode for all OPFS databases (the post-open() startup pragmas are defined on a per-VFS basis). That might not be a bad thing, in particular on the SAHPool VFS, but it's as yet untried.

For completeness's sake, this is what our post-open() pragma list currently looks like:

[
  /* As of July 2023, the PERSIST journal mode on OPFS is
     somewhat slower than DELETE or TRUNCATE (it was faster
     before Chrome version 108 or 109). TRUNCATE and DELETE
     have very similar performance on OPFS.

     Roy Hashimoto notes that TRUNCATE and PERSIST modes may
     decrease OPFS concurrency because multiple connections
     can open the journal file in those modes:

     https://github.com/rhashimoto/wa-sqlite/issues/68

     Given that, and the fact that testing has not revealed
     any appreciable difference between performance of
     TRUNCATE and DELETE modes on OPFS, we currently (as of
     2023-07-13) default to DELETE mode.
  */
  "pragma journal_mode=DELETE;",
  /*
    This vfs benefits hugely from cache on moderate/large
    speedtest1 --size 50 and --size 100 workloads. We
    currently rely on setting a non-default cache size when
    building sqlite3.wasm. If that policy changes, the cache
    can be set here.
  */
  "pragma cache_size=-16384;"
]

The convenient wrapper around sqlite3_wal_hook is PRAGMA wal_autocheckpoint, but those checkpoints are PASSIVE and can't restart or truncate the WAL.

In Python

The standard library sqlite3 does not allow that nor many other SQLite APIs.

However my APSW module allows doing everything you can from the C API - see set_wal_hook.