Mechanism for automatically picking up changes when on-disk .db file changes

[simonw]

It would be useful if Datasette could spot when a SQLite database file changes on disk and restart itself (hence re-running .inspect() and picking up the new content hash). Ideally this could happen in an atomic way so no requests get dropped during the switch-over.

This may not play well with SQLite opening databases in immutable mode. Research required.

[simonw]

This is a departure from how Datasette has been designed so far, and it may turn out that it's not feasible or it requires too many philosophical changes to be worthwhile.

If we CAN do it though it would mean Datasette could stay running pointed at a directory on disk and new SQLite databases could be dropped into that directory by another process and served directly as they become available.

[simonw]

From https://www.sqlite.org/c3ref/open.html

immutable: The immutable parameter is a boolean query parameter that indicates that the database file is stored on read-only media. When immutable is set, SQLite assumes that the database file cannot be changed, even by a process with higher privilege, and so the database is opened read-only and all locking and change detection is disabled. Caution: Setting the immutable property on a database file that does in fact change can result in incorrect query results and/or SQLITE_CORRUPT errors. See also: SQLITE_IOCAP_IMMUTABLE.

So this would probably have to be a new mode, datasette serve --detect-db-changes, which no longer opens in immutable mode. Or maybe current behavior becomes not-the-default and you opt into it with datasette serve --immutable

[simonw]

As seen in #316 inspect is already taking a VERY long time to run against large (600GB) databases.

To get this working I may have to make inspect an optional optimization and run introspection for columns and primary keys in demand.

The one catch here is the count(*) queries - Datasette may need to learn not to return full table counts in circumstances where the count has not been pre-calculates and takes more than Xms to generate.

[simonw]

It turns out this was much easier to support than I expected: eac08f0