Running query leaves behind -shm and -wal files after clean shutdown.

[smoyerx]

The trivial program below opens/creates a DB file, runs a query on sqlite_schema, closes the DB file, and exits. After exit it leaves behind '-shm' and '-wal' files which shouldn't happen on a clean shutdown.

If you comment-out the query and un-comment the create table command then there is no '-shm' or '-wal' after exit.

import 'package:sqlite_async/sqlite_async.dart';

Future<void> main() async {
  final db = SqliteDatabase(path: 'txntest.db');
  await db.initialize();

  await db.getAll("SELECT * FROM sqlite_schema;");

  //await db.execute("CREATE TABLE table1 (name TEXT UNIQUE NOT NULL);");

  await db.close();
}

[jeremylcarter]

You can change to default sqlite behavior which is journal mode delete.

SqliteDatabase(
        path: "my.db",
        options: const SqliteOptions(
            journalMode: SqliteJournalMode.delete));

[rkistner]

The database is actually closed cleanly in this case, but what happens here:

1. The getAll call opens a read-only connection.
2. The execute call opens a read-write connection.
3. Closing the database closes the read-write connection first, then the read-only connection.
4. When the read-write connection is closed, the journal files are not deleted, since another connection is still open.
5. When the read-only connection is closed, the journal files are not deleted, since the connection is read-only.

So the database is closed cleanly, but the journal files remain. This should not have a significant impact on most applications, and there is no risk of data loss because of this.

That said, if we just change the order of closing connections (close read-only before read-write), these files would get cleaned up. I can't think of any downsides, so we can do that in a future version.

You can change to default sqlite behavior which is journal mode delete.

While the option is there, this library is designed around WAL mode, and this specifically affects locks used for the connection pool. You may get SQLITE_BUSY errors in DELETE and other journal modes, when performing a read concurrently with a write transaction, which you won't get in WAL mode.

[smoyerx]

That makes sense. Thanks for the detailed update.

I just started playing with sqlite_async and really enjoy the ergonomics of the API. Very nice!