2 ms·
This is a cool project! But if you just want a simple CLI, you can query Excel and ODS files with dsq [0]. It's the same end result without needing to carry plu
by eatonphil 4y ago
This is a cool project! But if you just want a simple CLI, you can query Excel and ODS files with dsq [0]. It's the same end result without needing to carry plugins around and with support for many additional data formats. Plus a growing standard library of functions that don't come built into SQLite such as best-effort date parsing, URL parsing/extraction, statistical aggregation functions, math functions, string and regex helpers, hashing functions and so on [1].
An annoying thing about this extension-based style of file support is needing to create a new table for every new file if the schema is different. This is a limitation [2] of sqlite unfortunately. dsq doesn't work this way so it doesn't have that limit.
On the other hand, if you go this route you can more easily combine with other extensions. That's not really possible with dsq right now.
[0] https://github.com/multiprocessio/dsq https://github.com/multiprocessio/dsq
[1] https://github.com/multiprocessio/go-sqlite3-stdlib https://github.com/multiprocessio/go-sqlite3-stdlib
[2] https://sqlite.org/forum/forumpost/ec944414fa https://sqlite.org/forum/forumpost/ec944414fa
- sitkack 4y agoI think when Wasm gets a little bit more mature, it will enable the creation of more composable tools. One might be able to hot create modules? Kinda ironic that this is how it is structured given that arbitrary projections is what SQL does. Is there a way to hook lower in the SQLite stack, and make it think that your DS is something it already understands? What about a function that returns a view that it dynamically generates?
- eatonphil 4y agoSqlite does not allow table valued functions to return results with varying schemas. The schema must be defined once up front and then cannot change. That forum post is a request I made to them to allow dynamic columns but they don't seem interested so far.
- sitkack 4y agoI get that as how it currently stands. But somewhere in the sqlite codebase, everything is a projection. Still thinking and reading through sqlite source ...
- simonw 4y agoI've been contemplating a workaround for this: since registering a new function to a SQLite connection (and deregistering it later) is a pretty fast operation, it should be possible to register a custom function with the detected columns for a single use only and then remove it after running the query.
- eatonphil 4y agoThe problem is if you have multiple function calls within one query. You could give them all a unique name though. Like call "the importX family": import1, import2, import3, etc... Still not ideal but at least possible. Edit: Actually if you just transform the query to add the suffix then that's pretty reasonable a UX. This might give me a way forward to try out TVFs!
- sitkack 4y agoYeah, I was thinking along the same lines, possibly running SQLite under Cling [1] via cppyy [2] [1] https://www.infoq.com/news/2015/05/cling-cpp-interpreter/ https://www.infoq.com/news/2015/05/cling-cpp-interpreter/ [2] https://github.com/wlav/cppyy https://github.com/wlav/cppyy