Ogrinfo leaves -wal and -shm files behind when reading SQLite in WAL mode

[Jukka Rahkonen]

If SQLite db is in journal_mode=WAL state, reading the db file with ogrinfo leads to creation of two temporary files which are named as

basename-wal

basename-shm

Creation of the temporary files is normal, but by the SQLite documentation ​https://sqlite.org/tempfiles.html SQLite should remove first the WAL file and then also SHM file automatically when the last connection to the database closes cleanly. Files are not harmful and they can be deleted safely because -wal is empty. However, users can't know that and deleting a non-empty wal file leads always to data loss so we cannot recommend to delete them manually.

Also, if db is in WAL mode it cannot be opened even as read-only from a read-only directory because SQLite needs to write the -wal and -shm files. In this case ogrinfo prints an error message that does not give right information for the user

ogrinfo -ro wal_mode.gpkg
ERROR 1: Unable to execute PRAGMA foreign_key_check
ERROR 1: pragma foreign_key_check on 'wal_mode.gpkg' failed. You can disable this check by setting the OGR_GPKG_FOREIGN_KEY_CHECK configuration option to NO

[Even Rouault]

In 37121:

SQLite/GPKG: add explicit error message when trying to open a read-only WAL-enabled database (refs #6776)

[Even Rouault]

In 37122:

SQLite/GPKG: make sure when closing a WAL-enabled database opened in read-only mode to reopen it in read-write mode so that the -wal and -shm files are removed (fixes #6776)

[Even Rouault]

In 37123:

Commit new test that should have gone with previous commit (refs #6776)

[Even Rouault]

In 37124:

SQLite/GPKG: add explicit error message when trying to open a read-only WAL-enabled database, and make sure when closing a WAL-enabled database opened in read-only mode to reopen it in read-write mode so that the -wal and -shm files are removed (fixes #6776)