octosql-plugin-random_data VS octosql-plugin-postgres

Hey!

> I think the main fundamental difference is that this wants all of the data upfront in a data file.

Absolutely not! Moreover, OctoSQL can push down predicates to databases so that it only has to download a small subset of the table, if the datasource and query allow it.

> Very easy to model HTTP APIs as a table.

"Very easy" is relative, but you can take a look at the random_data[0] datasource which is exactly this. I'm also planning to add a GitHub datasource fairly soon. That said, there is Steampipe[1] for which this is the main use case afaik (hitting API's and exposing them as tables through Postgres FWD's written in Go), so it might be a smoother and more polished experience. There's also tons of plugins already available for it.

> Easy to model basically anything as a table for example files on my filesystem.

Yep, definitely. That's the idea behind OctoSQL. Strive to create a tool for easily exposing anything through SQL (like your machine's processes list, an API, and join that with a file, or database). There's still lot's of documentation work left to do though, in order to make the plugin authoring experience easier.

> A decent query planner so that I can avoid expensive things (like API calls) if I can determine if I need the object based on something cheaper (like a local disk access).

Probably depends on the use-case, and it sometimes needs you to be fairly explicit, but OctoSQL does in fact do that. It will push down predicates to underlying databases, which means joining something small with something very big (while only taking very small amounts of the latter) can be very fast with LOOKUP JOIN's.

> I want something that is easy to extend to sources that are possibly non-listable or at the very least I don't want to have all of the data available.

Doable. An example of this is the `plugins.available_versions` table[2]. It requires you to provide the plugin name as a predicate, as the versions need to be downloaded from the plugin's own repository (and listing all plugin repositories on each query isn't really what you want to be doing). You can also LOOKUP JOIN with the `plugins.available_plugins` table if that is indeed what you want.

[0]: https://github.com/cube2222/octosql-plugin-random_data

[1]: https://steampipe.io

[2]: https://github.com/cube2222/octosql/blob/main/datasources/pl...

Thanks!

I'll start by saying that OctoSQL is single-machine-only, as I'm not sure what exactly you meant with "federated".

I'd recommend starting by going with a debugger through the execution of the root function in root.go, as that calls out to all the macro transformations.

Then, you can take a look at the optimizer (optimizer directory) and the Postgres plugin source[0], as an example of a plugin that is able to push down predicates to the underlying database. As well as the Typecheck (logical -> physical) and Materialize (physical -> execution) transformations.

I'm planning to write a few technical documents about the implementation soon, while writing some actual usage documentation as well.

[0]: https://github.com/cube2222/octosql-plugin-postgres