Features for enabling and disabling WAL mode

[simonw]

I finally figured out how to enable WAL - turns out it's a property of the database file itself: https://github.com/simonw/til/blob/master/sqlite/enabling-wal-mode.md

[simonw]

For the CLI:

$ sqlite-utils enable-wal github.db
$ sqlite-utils disable-wal github.db

For the Python library:

import sqlite_utils

db = sqlite_utils.Database("github.db")
db.enable_wal()
db.disable_wal()
mode = db.journal_mode # "wal" or "delete" or others

[simonw]

https://www.sqlite.org/pragma.html#pragma_journal_mode lists six modes: DELETE | TRUNCATE | PERSIST | MEMORY | WAL | OFF

I'm only going to implement utilities for DELETE (wal-off) and WAL (wal-on) - the other modes look like they're for specialist purposes that I don't need to support.

If it turns out I do need them I can add those to sqlite-utils later.

[simonw]

The CLI options should take multiple database files:

$ sqlite-utils enable-wal *.db

It's possible for this to fail if the DB is locked. How about a --retry option that causes it to retry a bunch of times if that happens?

[simonw]

I'm having trouble figuring out how to write a test that locks a SQLite database (so I can test that --retry actually works). I tried this recipe but it didn't seem to prevent another process from running pragma journal_mode='wal'; against that database:

import time
import sys
import sqlite3

filename = sys.argv[-1]

db = sqlite3.connect(filename)
with db:
    db.execute("create table if not exists counter(id integer primary key, counter text)")
    time.sleep(100)

[simonw]

For the moment I'll build it without the --retry mode.