Align sqlite3.Connection.iterdump with the .dump() command of the SQLite shell

[erlend-aasland]

The iterdump docs promise the following:

Return an iterator to dump the database as SQL source code.

The current implementation has several drawbacks compared to the SQLite shell .dump command. We should try to align our implementation with the .dump command of the SQLite shell.

See also #64662

Linked PRs

• gh-108364: In sqlite3, disable foreign keys before dumping SQL schema #108471
• gh-108364: Simplify quoting values and identifiers in sqlite3's iterdump() #108472
• gh-108364: In sqlite3, disable foreign keys before dumping SQL schema #113957

[erlend-aasland]

The SQLite shell always starts the dump by disabling foreign key support. It has been doing so for a long time:

$ ~/install/sqlite-3.42.0/bin/sqlite3
SQLite version 3.42.0 2023-05-16 12:36:15
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
COMMIT;
sqlite> ^D
$ ~/install/sqlite-3.7.15/bin/sqlite3
SQLite version 3.7.15 2012-12-12 13:36:53
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
COMMIT;
sqlite> ^D

I suggest we align to this practise.

In SQLite foreign key support is always disabled by default; one has to explicitly enable it, if needed.

[erlend-aasland]

I restyled this as a feature, not a bugfix.