Re: boost.sqlite

From: Klemens Morgenstern (klemensdavidmorgenstern_at_[hidden])
Date: 2024-01-05 02:52:59

On Thu, Jan 4, 2024 at 6:43 PM Dominique Devienne <ddevienne_at_[hidden]> wrote:
>
> On Thu, Jan 4, 2024 at 10:51 AM Klemens Morgenstern via Boost <boost_at_[hidden]> 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/02824d508ff6e79a8432f3ee553445819f2ac17d/include/boost/sqlite/statement.hpp#L118

>
> 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::vector<std::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_index.cpp

> 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.