sqlite fixtures unlink the file without closing the connection, flaky on Windows

[simontaurus]

Symptom

test_local_db_periodic_cleanup errors at teardown on Windows runners:

ERROR at teardown of test_local_db_periodic_cleanup
E   PermissionError: [WinError 32] The process cannot access the file
    because it is being used by another process:
    'C:\Users\RUNNER~1\AppData\Local\Temp\tmpXXXXXXXX.sqlite'
tests\test_controller.py:87: PermissionError

The tests themselves pass: 141 passed, 11 skipped, 1 error. Only the
teardown fails, so the job goes red on an otherwise green run.

Seen on the v0.42.8.post1000002004006 tag build, on test (3.11, windows-latest), after the same commit had passed on the PR and on
main. Re-running the job made it pass, which is what makes it a nuisance
rather than a blocker.

Cause

Both sqlite fixtures unlink the temp file without closing the controller
first:

    db = LocalTimeSeriesDatabaseController(..., db_path=path)
    yield db
    os.unlink(path)          # tests/test_controller.py:87

and the v1 variant at tests/test_controller.py:608. Windows refuses to
unlink a file with an open handle, so whether teardown succeeds depends
on whether the connection, or the periodic cleanup task the test starts,
has been finalised yet. On POSIX the unlink succeeds regardless, which is
why it only shows up on Windows and only sometimes.

Suggested fix

Close the connection in the fixture before unlinking, and tolerate a
failed unlink:

    yield db
    await db.close()          # or the sync equivalent
    with contextlib.suppress(PermissionError, FileNotFoundError):
        os.unlink(path)

The periodic_cleanup task started by the test should also be cancelled
and awaited before teardown, otherwise it can re-open the database after
the close.

[simontaurus]

Root cause, more precisely

The driver opens a fresh connection per call (async with aiosqlite.connect(...) in _drivers.py), so there is no long-lived
handle for the fixture to close. The leak is the cleanup task:

cleanup_task.cancel()      # tests/test_controller.py:380
# never awaited

cancel() only requests cancellation. The coroutine is very likely
suspended inside async with aiosqlite.connect(...), and aiosqlite runs
the connection on a worker thread. asyncio.run then tears the loop
down without the cancellation having been delivered and the __aexit__
having run, so the worker thread can still hold the file open when the
fixture reaches os.unlink(path) a moment later. On Windows an open
handle makes unlink fail; on POSIX it does not, which is why this is
Windows-only and intermittent.

Proposed fix

1. Await the cancellation

cleanup_task.cancel()
with contextlib.suppress(asyncio.CancelledError):
    await cleanup_task

This is the actual fix: it guarantees the async with has exited and
the connection is closed before the test returns.

2. Make the fixture defensive anyway

@pytest.fixture
def sqlite_db():
    with tempfile.NamedTemporaryFile(suffix=".sqlite", delete=False) as f:
        path = f.name
    db = LocalTimeSeriesDatabaseController(...)
    yield db
    with contextlib.suppress(PermissionError, FileNotFoundError):
        os.unlink(path)

A temp file left behind is not worth failing a green test run over. Apply
to the v1 fixture at line 608 as well.

Both changes are worth having: (1) removes the cause, (2) stops any
future stray task from turning a passing suite red.