Re: boost.sqlite

From: Dominique Devienne (ddevienne_at_[hidden])
Date: 2024-01-04 10:43:28

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?

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 also use std::optional for NULLs, on both the bind side, and the get
side. Do you support it as well?
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

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

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?

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?

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?