Support for asyncio implementation of Virtual Tables

[cancan101]

Taking a look at the Virtual Tables it would be cool if the methods could be asyncio compatible such that e.g. Next is non blocking (awaitable).

This would ideally allow downstream users to await the results of a cursor call and eventually use apsw for an asyncio-compatible SQLALchemy dialect.

Related library with a ticket for asyncio: plasticityai/supersqlite#2

It seems like the db API interface could be made async followings approach in https://github.com/omnilib/aiosqlite but that doesn't solve the allowing the virtual table functions themselves to use non blocking calls.

[rogerbinns]

I can't see any way of doing this, but if you can figure it out I'll try.

The problem is that SQLite is C code and can't return to the event loop. The aiosqlite approach is to do the synchronous stuff in a separate worker thread leaving the main thread async. To call async virtual tables would require blocking the main thread and letting a helper thread run the async code and event loop.

Have you tried something like https://github.com/miyakogi/syncer to wrap your async code into a sync interface? Event that looks like you could have nested event loops which generally don't work.

[cancan101]

So if I understand how the virtual tables work, the control flow is something like:

python (application code) ---(apsw)--> sqlite (C) ---> python (VTModule class)

I wonder if there is some way to pass a callback into the VTModule class (so that the implementation can be "async") and then the apsw becomes non blocking.

This seems like the rough approach outlined here: https://stackoverflow.com/a/54620589/2638485
and this seems helpful too: https://stackoverflow.com/a/51115745/2638485

Though I guess you are ultimately limited by pretty strict API that sqlite expects: https://www.sqlite.org/c3ref/create_module.html.

[rogerbinns]

The fundamental problem is that SQLite (and hence APSW) are synchronous. Callbacks must return a value that can be used immediately. In Python, synchronous code cannot call async code.

The only way to bridge between sync and async is to run an event loop, which looks sync to its caller, and then the loop runs async items. You can call asyncio.get_event_loop().run_until_complete(async_code) which will run an event loop until async_code completes, and return that value.

If apsw had to handle an async item, it would have to invoke an event loop. It would not be able to return to a parent event loop and then resume execution later as none of the items between python app code and the vtmodule callbacks support returning and resuming (that includes a bunch of CPython machinery to call functions as well as SQLite C code).

I'm am not going to add knowledge of event loops to apsw - you'll need to do that yourself, with the syncer module a good example of how.

[cancan101]

Totally understandable not adding knowledge of event loops to apsw.

For reference I took a look at the sqlite3 bindings for npm (listed as "Asynchronous, non-blocking SQLite3 bindings for Node.js.") and it looks like the node api (n-api) offers some primitives in C/C++ to work with the event loop such as napi_queue_async_work + napi_create_async_work which used e.g. here. I am less familiar with what, if anything, Python provides to (easily) interface with the event loop.

[rogerbinns]

Javascript only has a single thread so async is essential. Also note that they only expose the query interface of SQLite - APSW provides that with cursors as iterators in the same manner.

[rogerbinns]

@cancan101 I have good news for you. I am currently working on async support in #577 - it took a bit of lateral thinking, and I currently have it working but need to fully plumb things through fully, document, test etc.

First for calls coming out of SQLite it is simple:

1. There is a contextvar named apsw.async_run_coro
2. Whenever a callback returns a coroutine (ie PyCoro_CheckExact returns true), that contextvar is called with the coroutine and execution in SQLite blocks until that returns. Note the db mutex is held during this time so another thread won't be able to make re-entrant calls into SQLite, but the same thread could.

That would easily let you write virtual tables using asyncio or any other mechanism of your choosing. I did testing by having asyncio running in one thread and then have async_run_coro send the coroutine back to that.

This is complete and works well, and was only ~10 lines of code!

For calls coming into SQLite it is more complicated. The principle is the same as aiosqlite3 - run the connection in a dedicated worker thread.

1. There is an opaque undocumented "BoxedCall" type. The only thing that can be done is run it once with no parameters.
2. A "controller" instance is required. In its constructor it would create the worker thread, and listen for work to do (eg queue.SimpleQueue)
3. The controller has a send method provided with the BoxedCall as the only parameter. What it would do is send the BoxedCall to the worker thread, and return something awaitable (eg asyncio.Future)
4. In the worker thread it would make that BoxedCall and update the future with the result/exception.
5. There is a static method apsw.Connection.as_async which takes a "controller" as a first parameter, and the remaining parameters are the same as the regular Connection constructor.
6. Behind the scenes the Connection understands it is in async mode. The as_async method does a send so the Connection constructor is run in the worker thread
7. When the Connection constructor runs in async mode, it sets a thread local value

From now on, every APSW API call checks its parameters, and then if in async mode and the thread local value is not set, then makes a BoxedCall of the method and its parameters and calls send on the controller. This ensures the calls become sync in the worker thread but async in any other thread.

The controller also has a close method which is called when the connection closes so the worker thread can be shutdown.

This just requires inserting one line of code in each method and things automagically become async. A lot more than 10 lines though.

The controller can implement the asyncio stuff any way it wants. I have implemented it for the asyncio module, and done the research for trio, and anyone can provide their own. You can see the code here. I even added a deadline feature using contextvars which will unstick deadlocks if sqlite calls into async and async calls back into sqlite requiring the result.

The controller I implemented handles both calls into SQLite and calls back out (like virtual tables). Completely different mechanisms can be used.

Do you have any feedback on this approach? Reply here or in #577