Support a SQLite connector

[erikerlandson]

There is some philosophical resistance to just supporting a generalized JDBC connector (#3105), so here is a tracking issue for supporting specific SQLite connector

[lhofhansl]

Curious how you envision this would work.

Would each worker load its own, independent SQLite database? Or would only one worker load the database?
Or do you have a shared (posix compliant) media, and concurrent access is handled by locking, and the each worker opens the same database file?

[erikerlandson]

@lhofhansl I would probably be satisfied with whatever makes it easiest to implement. If that means only a single worker can access the SQLite DB and that reduces parallelism, I would consider that a fair tradeoff.

Our particular use case would allow for read-only access to the SQLite DB, which usually makes concurrency easier but I don't know if a read-only connector is OK with trino.

Our particular situation is that we have some existing SQLite DB, and I mostly just want to send trino a connect-url for that DB, so that it now behaves like just another catalog in my trino deployment.

[MichaelTiemannOSC]

One of the Datasette (https://datasette.io/) patterns is to allow myriad instances of Datasette to serve readonly data to whomever. It would be fantastic to at least be able to support this pattern, making Datasette instances data sources that can be federated. It would be up to the myriad SQLite data providers to update their databases as new data arrives.

[dforsber]

This may be related: https://github.com/dacort/athena-sqlite

[MichaelTiemannOSC]

This may be related: https://github.com/dacort/athena-sqlite

Nice bit of archeology, but that repo has not been active for 4 years. It requires Python 3.7 which many packages no longer support at all (and many more deprecate).