Use WAL by default

[lemon24]

https://www.sqlite.org/wal.html

This is mainly to improve concurrency (although using a SQLite with HAVE_USLEEP would yield better results; TODO: mention this somewhere in the code).

It should be possible to turn it off via a storage argument (pending #168) for backwards compatibility.

[lemon24]

This shows that enabling WAL greatly reduces "database is locked" errors: #175 (comment).

[lemon24]

The PyPy tests are failing:

_sqlite3.OperationalError: cannot commit transaction - SQL statements in progress

This is most likely because PRAGMA journal_mode=... in create_db() gets run within a transaction. I am not sure why there's a difference in behavior between the PyPy and CPython bindings.

Regardless, the CLI shows that enabling WAL is not supported within a transaction:

sqlite> pragma journal_mode;
delete
sqlite> begin;
sqlite> create table u(a);
sqlite> pragma journal_mode=wal;
delete
sqlite> commit;
sqlite> pragma journal_mode;
delete
sqlite> begin;
sqlite> pragma journal_mode=wal;
Error: cannot change into wal mode from within a transaction
sqlite> commit;

I am also not sure why we don't get the same error in Python.

[lemon24]

So, turns out the "cannot commit transaction - SQL statements in progress" PyPy error is because we should be explicitly closing cursors, and not relying on the GC to do so for us: https://bitbucket.org/pypy/pypy/issues/3080/sql-connection-error-cannot-commit

In order to fix this regression, we need to at least explicitly close the cursors up to the PRAGMA statement (ideally, we should do all of them, but there's a lot).