Re: [boost] boost.sqlite

On Thu, Jan 4, 2024 at 6:43 PM Dominique Devienne wrote:

On Thu, Jan 4, 2024 at 10:51 AM Klemens Morgenstern via Boost wrote:

...

I've written a boost.sqlite library last year mainly to experiment, [...] Hence I'd like to gauge interest to see if it's worth proposing for boost.

Sure, I'd be interested. I've used SQLite extensively for years, both with of-the-shelve wrappers, and internal ones (historical).

I could use a good one, that went through a Boost review, from someone with good meta-programming experience like yourself.

Note that we push lots of large text and blob values through SQLite's binding APIs, and I use no-copy binding (SQLITE_STATIC) extensively (memory needs to be "alive" only until the statement is step'd, not really static). Does your binding support that?

If you use views with parameterized queries, yes: https://github.com/klemens-morgenstern/sqlite/blob/02824d508ff6e79a8432f3ee5...

In a separate PostgreSQL internal wrapper, I use std::ref() as a "marker" for no-copy binding. Do you do something similar? Would you support the idea?

I don't, it's just the type (string_view or blob_view). This is simpler (because it'll never be async) and since sqlite doesn't have a dynamic type system, I think anything else is overkill.

I also use std::optional for NULLs, on both the bind side, and the get side. Do you support it as well?

I don't, I ended up writing my own type wrapping values. But you can extend the bind side.

For example, in my PostgreSQL wrapper, getting a value that's NULL is a runtime error, unless the value is an std::optional, i.e. the NULL is expected.

A (very) quick look at the doc shows r.at(N).get_text() to get row values. Do you have an alternative that assigns to typed variables instead? Something like

No, on purpose. that's not how sqlite works and it's very easy to just wrap `sqlite::value` to do your own thing. That means I don't need to pick things like error handling for a user. Since you don't own the data, you'll get a blob or string_view, which is as fast as it gets.

``` bool flag; std::string text; std::vectorstd::byte blob; row >> flag >> text >> blob; ``` I my wrappers, I also support std::span, both on bind and get side as well. In both SQLite and PostgreSQL, on the get side, the memory is owned by them, (the result set in PostgreSQL, the statement in SQLite, until step'd at least, again) so there's no reason to always copy it out, often you just want to inspect at it, and do something with it, w/o an extra needless copy. We move GBs via SQLite ETLs, so that no-copy matters, and even though the gains are small, they exist.

I'd expect a Boost quality wrapper allowing maximum performance.

The vtable support is also interesting. We've wrapped it years ago, and I haven't touched it for years, and I sure would welcome something easier / better. One large app of ours uses vtables extensively, on top of Boost-Multi-Index containers, exposing the indexes to SQLite. If you haven't thought of that, that could make for a good example. It's tricky to implement xBestIndex, so a good showcase of your wrapper.

Doing that automatically wasn't within my skills, but I got an example: https://github.com/klemens-morgenstern/sqlite/blob/develop/example/multi_ind...

The fact you mention subtypes shows you're aware of SQLite subtleties so that's good.

Native C++ support for the new jsonb format might also be interesting. Richard Hipp says it's an internal format, but since it's just a blob to the outside, there's no reason not to process it separately from SQLite, outside SQL. This is fairly new and not even officially released perhaps. Are you aware of it?

I am aware of jsonb, but not of any C-API to process it. I just used boost.json to handle json data up to now, but that might be slow for jsonb.

Anyways, I'll try to find some time to look at your wrapper. And I definitely endorse a production quality SQLite wrapper in Boost. Heck, I'd welcome a PostgreSQL one, I've peeved there's only MySQL support :)

--DD

PS: And with two SQL-DB-related wrappers in Boost, maybe an ORM isn't far? :) I'm personally not a fan of ORMs, but who knows, maybe I haven't found a good one?

I don't like ORMs, but I think a boost.sql library that makes it easy to write "portable sql" would be cool.

PPS: Perhaps with Peter's Boost.Describe (awaiting native C++ support), and the no-annotation variant for simple structs (PFR?), at least each wrapper could provide auto-magic assignments to structs from the SQLite stepped rows? I think I saw something like that for Boost.MySQL, no?

That's possible, but would be something I'd put in above mentioned boost.sql.