Sqlite Virtual Table as Datasette plugin?

[DavidPratten]

Hi,

I have a use-case for virtual tables in Sqlite.

Can the definition of a Sqlite virtual table be packaged as a datasette plugin?

David

[simonw]

Frustratingly I don't have a good pattern for this yet.

The Python standard library sqlite3 module doesn't provide a way for Python code to define virtual tables.

The apsw SQLite module DOES have this ability: https://rogerbinns.github.io/apsw/vtable.html - but Datasette doesn't yet have a way to integrate with that library. I've been pondering having pluggable database backends for Datasette which might make it easier to swap in apsw, but that's not going to make it in for Datasette 1.0 (though could be added shortly afterwards).

The other option would be to define them using C or Rust or similar (maybe with a future version of https://observablehq.com/@asg017/introducing-sqlite-loadable-rs ) and create a loadable SQLite extension.

These could potentially then be bundled into a Datasette plugin using binary wheels and clever tricks to compile versions of the extension for multiple operating systems - I think the key to getting that to work may be https://github.com/pypa/cibuildwheel but I've not even started figuring that out yet!

[DavidPratten]

Thanks Simon.

ATM that sounds a few more moving pieces than I'm comfortable with. I'll continue to keep an eye on Datasette.

All the best,

David