Re: boost.sqlite seeking endorsement

From: Klemens Morgenstern (klemensdavidmorgenstern_at_[hidden])
Date: 2024-03-26 00:53:47

> I endorse Klemens library. I've looked at it in the (recent) past, and liked it's design and implementation.
> There are aspects of the library around lifetimes that could trip beginners, and missing probably things, but that's for the review.

Much appreciated.

> I'm a long standing SQLite user, still using it daily extensively, thus I consider myself experienced in the domain.
> We have our own wrappers (plural), and even code based on Fusion (used in tests only) that I got here,
> which would probably be much nicer using more modern C++ techniques. Integration with PFR or Describe would also be pluses.

I thought about doing that, but it's unclear to me how. Mapping a
struct to a table seems intuitive, but then how do I handle anything
select other than `*` ?

> One very tricky area of SQLite are virtual tables, also used for table-valued UDFs (user-defined functions),
> which is another area we've wrapped (a looong time ago, so I don't remember much), which is not addressed by Klemens,
> but which would be a great help for those that need it (e.g. we have dozens of vtables, backed by Boost.Multi-Index containers).

I reckon you found the vtable support before your second email?

I got an example of using boost.url as a table based function here:

https://github.com/klemens-morgenstern/sqlite/blob/master/example/url.cpp
https://github.com/klemens-morgenstern/sqlite/blob/master/example/url.sql

I think you're correct that an example that actually uses multiple
indices of a multi_index class would be useful.

> I'm aware of Roland Bock's sqlpp11 library, but Klemens library is something else entirely.
> Personally I'm not a fan of "generic" SQL libraries, and prefer specific ones with "native" APIs for the RDBMS.
> Unlike Ruben's MySQL library, it's "just" a wrapper around the native C API, but there is a lot
> of value in a well design and low-overhead wrapper to provide type-safety and convenience.
> Whether such a library belongs to Boost is not for me to say, but I'd welcome it.

> A library like Klemens is basically a building block for those that want to write higher-level APIs or even cross-RDBMS generic libraries.

That's exactly my intent.

> There's still a lot of value is a well designed, peer-reviewed, efficient and well documented wrapper around SQLite. My $0.02. --DD

>
> > Le dimanche 24 mars 2024 Ã  00:51 +0800, Klemens Morgenstern via Boost a
> > what's making the proposed boost.sqlite different from existing libraries?
> > It's not so clear to me. I see two things that are not  widespread:
> >
> > * variant-base interface
> >
>
> SQLite has it's own opaque internal variant (call value).
> https://www.sqlite.org/c3ref/value.html
>
> I'd expect an SQLite wrapper to wrap that, instead of using std::variant or
> boost::variant2.
> Which does not prevent interop with those variants, but that's a separate
> topic.
>

boost.sqlite uses `boost.variant2` for parameters internally. That's
an implementation detail though.

>
> > * json support
> >
>
> SQLite has built-in support for JSON. https://www.sqlite.org/json1.html
> It used to be a pure extension, but nowdays it's backed in,
> even in the grammar to support PostgreSQL like -> and ->> operators.
>
> No need for Boost.JSON. What I'd like to see OTOH, is an example of the
> library
> that duplicates the behavior of JSON1 using Boost.JSON, to demonstrate the
> ease
> of writing a virtual-table module with the proposed library, with a bunch
> of table-valued functions.
> That should outperform JSON1, if Boost.JSON is has fast as it is supposed
> to be.

The support for json is really simple and completely optional:
https://github.com/klemens-morgenstern/sqlite/blob/master/include/boost/sqlite/json.hpp

This is mainly for convenience, however it also shows how easy it is
to add your own subtype.
The above linked url example also uses a 'U' subtype for urls.

>
> As Julien already wrote, there are many SQLite C++ wrapper libraries.
> but I'm not aware of any that tackles virtual tables. That would be the
> standout feature IMHO.
> One that's hard-code and in keeping with Boost's tradition of pushing the
> envelop.
>
> Another example of integration with v-tables and Boost.MultiIndex would
> also be very Boost like.

I have an example, but it's only using simple indices. I think adding
examples for more advanced usage of multi_index would be great, but I
am not familiar enough with it I fear.

https://github.com/klemens-morgenstern/sqlite/blob/master/example/multi_index.cpp

> Wiring up the v-table to leverage the various indexes of the BMIs in
> xBestIndex is non-trivial.
> These are the kind of advanced features and demos that would make a
> Boost-based SQLite wrapper shine IMHO. --DD
>
> _______________________________________________
> Unsubscribe & other changes: http://lists.boost.org/mailman/listinfo.cgi/boost