Unsupported PRAGMAs are no-ops

[timsehn]

DoltLite accepts every PRAGMA that configures the pager, the journal, or the
on-disk layout, and then ignores it. The value never changes and no error is
raised. This follows SQLite's own convention — SQLite silently accepts unknown
pragmas and unsupported values rather than erroring — but it means a caller
cannot tell that the setting did nothing.

The dividing line is DoltLite's architecture: everything above btree.h is
upstream SQLite and works unchanged, so connection-level pragmas behave
normally. Everything below it is the prolly chunk store, which has no pages, no
freelist, no rollback journal and no -wal/-shm sidecars, so pragmas
describing those have nothing to configure.

Verified by setting each pragma on a brand-new database and reading it back,
against stock SQLite for comparison.

Silently accepted, no effect

PRAGMA	DoltLite always reports	Stock
journal_mode	wal	honours delete/memory/off/…
auto_vacuum	0	2 when set before creation
secure_delete	0	1
journal_size_limit	-1	32768
mmap_size	0	1048576
encoding	UTF-8	UTF-16le (see #1838)
writable_schema	0	1

page_size is a special case: DoltLite accepts it and reports the new value,
so it looks honoured while meaning nothing — the store has no pages.

Readable, but synthetic

page_count (chunk count, not file pages), freelist_count (always 0),
schema_version (hash-derived, not a counter), data_version.
lock_status and stats exist only in SQLITE_TEST builds and report
chunk-store internals that have no page-level equivalent.

Explicitly rejected

wal_checkpoint(FULL|RESTART|TRUNCATE) is the one pragma that errors rather
than no-oping: "wal_checkpoint mode is not configurable on doltlite-format
databases; use the default (PASSIVE) form".

Supported, behaves as stock

locking_mode, cache_size, max_page_count, read_uncommitted,
synchronous, temp_store, foreign_keys, recursive_triggers,
legacy_alter_table, ignore_check_constraints, automatic_index,
query_only, defer_foreign_keys, cell_size_check,
reverse_unordered_selects, analysis_limit, threads, busy_timeout,
wal_autocheckpoint, application_id, user_version, fullfsync,
checkpoint_fullfsync, trusted_schema, integrity_check, quick_check,
and the schema-introspection pragmas (table_info, index_info, …).

Why this issue exists

These no-ops account for a large share of the inherited testfixture divergence
list, which currently attributes them one file at a time. Mentions of these
pragma names across known_testfixture_divergences.txt and
known_testfixture_crashes.txt: journal_mode 197, page_count 83, stats
48, auto_vacuum 47, page_size 34, lock_status 34, wal_checkpoint 28,
encoding 24, locking_mode 14, freelist_count 13, incremental_vacuum 10,
writable_schema 9, secure_delete 8, mmap_size 4.

This is the anchor those gates can cite instead of restating the reason per
suite.

Open question: should the no-ops be visible?

Two of the failure classes this causes are not cosmetic:

• pager4 asserts that journal mode decides whether writing to a renamed
  database is allowed (OFF/MEMORY succeed, DELETE/TRUNCATE/PERSIST
  return SQLITE_READONLY). DoltLite has one behaviour for all of them, so at
  least two of those assertions can never pass while journal_mode is a no-op.
  Making journal_mode report memory — which is the closest match, since
  DoltLite keeps its log inside the single file and creates no sidecar — would
  let the OFF/MEMORY cases pass honestly.
• A caller that sets secure_delete=1 or journal_size_limit for durability or
  disk-usage reasons gets silence, not a diagnostic.

Worth deciding whether these should stay silent (SQLite-compatible), start
reporting the mode DoltLite actually implements, or warn.

[timsehn]

writable_schema is not a prolly no-op

PRAGMA writable_schema is a connection flag, not a pager/chunk-store setting. It was incorrectly listed with journal_mode / auto_vacuum as “always 0 / silently accepted.”

What was wrong

• The default CLI forced SQLITE_DBCONFIG_DEFENSIVE on at open (upstream shell behavior unless --unsafe-testing).
• Under defensive mode, SQLite intentionally makes PRAGMA writable_schema=ON a silent no-op, so the CLI always reported 0.
• The C API (sqlite3_open) already left defensive off; PRAGMA writable_schema=ON worked there.

Fix

PR: will link once opened — default CLI leaves defensive off so PRAGMA writable_schema=ON round-trips to 1. --safe still enables defensive; .dbconfig defensive on still available.

Still true no-ops (this issue’s real subject)

journal_mode, auto_vacuum, secure_delete, journal_size_limit, mmap_size, page_size (reports but no pages), etc. — those remain format-architecture no-ops. writable_schema should be removed from that table once the CLI fix lands.

[timsehn]

Fix PR: #1848

[timsehn]

Correction: two entries were measurement artifacts, not engine behaviour

The original table was probed with build/doltlite against the system
sqlite3, and those two CLIs did not agree on SQLITE_DBCONFIG_DEFENSIVE. The
DoltLite shell turned defensive on; the stock shell leaves it off. Upstream
makes exactly three PRAGMAs no-ops under defensive mode — journal_mode=OFF
(pragma.c:753), writable_schema=ON (pragma.c:1174) and
schema_version=VALUE (pragma.c:2361) — so any of those three would look like
a DoltLite no-op when it was upstream behaviour on both sides.

#1848 turned defensive off in the CLI by default, so the comparison is now
apples-to-apples. Re-probed on db9e3e9d86 with defensive off on both sides:

writable_schema — remove from the no-op table. It works.

PRAGMA writable_schema; PRAGMA writable_schema=1; PRAGMA writable_schema;
  DoltLite: 0 -> 1        stock: 0 -> 1

schema_version — settable on both; only the default is synthetic.

DoltLite: -522755387 -> 42    stock: 1 -> 42

It belongs under "readable, but synthetic" for its value only, not as
something that cannot be set.

Confirmed still no-ops, defensive ruled out

PRAGMA	DoltLite	stock
journal_mode	always wal	delete → memory/off honoured
secure_delete	always 0	1
journal_size_limit	always -1	32768
mmap_size	always 0	1048576
auto_vacuum	always 0	2 (fresh database only)
encoding	always UTF-8	UTF-16le (fresh database only)

journal_mode=off is on this list on its own merits: it stays wal with
defensive off, so it is not the upstream defensive rule.

page_size is unchanged from the original report — accepted and echoed
(4096 → 8192) while stock keeps 4096 pending a VACUUM, so DoltLite is the
more permissive of the two and the value means nothing either way.

wal_checkpoint(FULL) still errors, as reported.

Note on the journal_mode question

Testing the "report memory instead of wal" option measured worse than
expected: across the storage regression bucket alone it took unexpected
failures from 20 to 119, because two dozen suites set journal_mode=WAL and
assert wal back. The "store and echo whatever was requested" policy looks
better on paper — the WAL suites would get wal, the mode-transition suites
would get what they asked for — but it could not be implemented as a small
change: the shim already stores the requested value and is not recreated, yet
the following read still returns the default, through a path that is not
pragma.c's handler, sqlite3JournalModename, or shimPagerOpenWal. Finding
that write is the prerequisite for the policy.