After creating a new SQLite database in code and then setting WriteAheadLogging to true, I do some work in Xojo that includes reading and writing to the database using transactions.
After running and closing the application multiple times, when I look at my database folder there is only the one database file that I named authservice.db. There are no -shm or -wal files in the directory.
Besides using WriteAheadLogging = True, I have also tried executing “PRAGMA journal_mode = WAL;” immediately after connecting.
If I connect to the same Xojo created database with the SQLite command line tool, and do anything including just running .schema to list the database schema and then .quit, my database folder then contains:
So I am wondering if this is a bug with Xojo SQLiteDatabase WriteAheadLogging. Whether I enabled logging with the property or with the Pragma command, the -shm and -wal files are never produced, unless I connect to the database using the SQLite command line tool, or even JetBrains DataGrip database tool.
When using Xojo exclusively, these WAL files are never actually created. I would think this means that even though the database seems to be set to wal mode (as evidenced by the wal files being created when using a different tool, without having to change the journal mode), that while using Xojo exclusively WAL mode is never actually used?
I’ve tested this both on Windows and Mac using Xojo Version 2022 Release 4.1, and this seems to be happening on both the platforms.
I’m hoping someone will be able to shed some light on what might be going on here.
When I do that it reports the journal mode is WAL and then the WAL files are created.
If I never use the SQLite CLI, those -shm and -wal files are never created. So WAL mode is being set by Xojo, but the WAL files are never actually created or used until a different tool connects to the database - which I don’t understand what might be going on.
Looking closer at the SQLite docs I found the following:
The WAL file exists for as long as any database connection has the database open. Usually, the WAL file is deleted automatically when the last connection to the database closes. However, if the last process to have the database open exits without cleanly shutting down the database connection, or if the SQLITE_FCNTL_PERSIST_WAL file control is used, then the WAL file might be retained on disk after all connections to the database have been closed.
So perhaps the SQLite command line tool requests to persist the files and Xojo does not. I will have to look at this closer with the debugger now.
OK. Well, my actual knowledge is scanty, so asking in the SQLite Forum gets the best answers; the Devs hang out there and answer all reasonable (and sometimes unreasonable) questions.
Ok, so now I feel silly. When I was testing my API with httpie, I only had one terminal open and was not watching what was happening on the file system while the database was actually in use, only when the application was still running (database connection was closed at that time).
So the -shm and -wal files are actually created when the database is in use by Xojo. Once the connection completes and the database is fully checkpointed, the -shm and -wal files are actually removed.
The SQLite command line tool must request that the files persist, as the docs mention is an option.
So thank you Tim for suggesting to search beyond Xojo for an answer. I feel like I had read the SQLite journal mode docs thoroughly before - but must have forgotten the exact details.
Hopefully, if nothing else, this might help someone else who notices the same thing while using Xojo.
I have used SQLite apps that ‘clean’ the files and only show the .db after close, others leave the 3 files if you just quit the application and not close the db connection first.
I see that you confirm that the files are created but as you close your app the ‘cleaning’ happens and only the db is shown. I guess it is working correctly. Good to know.
I’ll probably just start using a more current version on all the platforms, like you are. This is not really an issue - it just led me to believe there was a problem in Xojo where there was not.