5 ms·
Sqlite has an api for "virtual tables" that provides a fairly easy way to bolt SQL on top of things. https://www.sqlite.org/vtab.html https://www.sqlite.org/vta
by tyingq 6y ago
Sqlite has an api for "virtual tables" that provides a fairly easy way to bolt SQL on top of things. https://www.sqlite.org/vtab.html https://www.sqlite.org/vtab.html
I haven't used it directly, but I did use a Perl module that hooks into it, and that made it very easy to cobble together. https://metacpan.org/pod/release/SALVA/SQLite-VirtualTable-0.06/lib/SQLite/VirtualTable.pm https://metacpan.org/pod/release/SALVA/SQLite-VirtualTable-0...
- thechao 6y agoThe VTAB extension is new. The documentation is a bit thin compared to the rest of SQLite. (To be clear: the documentation is good; just not fantastically awesome like the rest of SQLite.) The main Achille's Heel of VTAB is wrapping your head around the indexing API: the basic idea is you need to tell the query planner which of a set of columns is best to work with. It is exceedingly unintuitive. This is compounded by the problem that the planner essentially refuses to do binary-search queries on more than one column of a VTAB. (Hell -- getting it to use any binary-search on any index is nearly impossible.) The result is that unless you're in a real bind, you should always translate your custom VTAB data into a reified table. This is all compounded by the fact that the slightest misstep in the callback API will corrupt/confuse/lockup SQLite (the in-memory representation -- your data is still safe). I'm glad VTAB exists, and I know it's just a side-effect of the compression support, but goddamn it's frustrating for it to be almost be awesome...
- gigatexal 6y agoBummer. Perhaps over time these pain points will be remedied.