Mechanism for executing "isolated" database operations without prepared connection stuff

[simonw]

This one is a bit complicated.

Datasette Cloud needs a way to prevent people with write access (via datasette-write) from dropping certain important tables.

I've been building https://github.com/datasette/datasette-sqlite-authorizer to handle this - it can now be used to make a table completely read-only such that it can't be dropped even by db.execute_write(...).

Then I found a problem: running VACUUM against a database needs permission to insert/update into tables that should otherwise be protected.

... and datasette-edit-schema runs a VACUUM after deleting a table.

So I need a way to run a SQL operation that doesn't get affected by the various things plugins may have done to the DB connection (such as installing authorization callbacks).

[simonw]

Design for this:

def vacuum(conn):
    conn.execute("vacuum")

await db.execute_isolated_fn(vacuum)

[simonw]

Implemented in a branch. Needs tests and documentation.

Another neat thing this can be used for is running "unsafe" SQLite extensions safely from within a plugin.

The plugin can define a function which takes the connection, enables the extension, then does things with it - and that extension will never be available to other connections outside of that isolated function execution.

[simonw]

I think I can test if a connection was reused or not with CREATE TEMPORARY TABLE temp_table_name (... - that should only exist within the same connection.

I'll create one in the write thread, then use the isolated mechanism to see if it's there or not and create a new one, then check if that new one is gone in the write thread again.

[simonw]

Documentation preview: https://datasette--2220.org.readthedocs.build/en/2220/internals.html#await-db-execute-isolated-fn-fn