On Fri, Mar 22, 2024 at 11:14 PM Daniele Lupo via Boost wrote:

Hi.

The idea seems good, but I've a bit worried about it.

We already have a boost mysql library, and a redis library. Now we want to introduce a sqlite library, and maybe tomorrow a keydb library, a dragonfly library, a postgres library, a db2 library and so on...

I don't understand the worry. Boost could supply different db clients that all follow best practices.

I mean, this is a lot of fragmentation, for tools that are, in my opinion, tiny wrappers over native connection libraries, and thich the purpose is the same, executing queries. What's the big advantage of using those libraries instead than native ones?

/snip

This sqlite library does not seems to have any real advantage if we see official ones, and I'm convinced that if we'll develop it we'll have another library that does not add any feature but boost asio and coroutines native support. If we create it, we'll have another library that's specific for a specific tool.

That is a fair criticism. An sqlite wrapper is much more limited in scope, since it doesn't add support for asynchronicity (since sqlite is not async). However, when using sqlite directly from C++, you'll end up with loads of boilerplate and boost.sqlite would solve that problem. It does not add support for asio or coroutines.

Maybe in this case the right repository is not boost, but the tool's one. Maybe if we want to support sql and no-sql databases, it's worth to think to a general solution, like Qt Sql library, that with a unique interface we can use different databases?

I think this would be great, but I would prefer it that would be a layer above mysql & sqlite (and future libraries). That is a boost.sql library should use the available boost sql libraries itself to provide such an interface.

I don't like Qt Sql for other reasons (plugins, moc, etc.), but the general idea is good. If we want to pollute boost with a lot of libraries that do the same thing (execute queries) with different programs (sqlite, mysql, redis etc), maybe it's better to stop a think a bit about a more general solution, a single library that embrace them all. Because this way we can have a unique library and interface for linking to a specific repository by using coroutines and asio, and give to detail implementation, or custom plugins, the effort to link the library to a specific tool. We already have boost libraries that can have external dependencies, for example Locale that can use ICU, or Odeint. This will be no different from those ones.

I like the sentiment, but I wonder if it's even possible to solve the general problem here. Many ORMs have tried and I can't think of one that succeeded.

Obviously, those are my two cents.

I appreciate it!

On Fri, Mar 22, 2024 at 8:14 AM Daniele Lupo via Boost wrote:

...maybe it's better to stop a think a bit about a more general solution, a single library that embrace them all.

If I were to write such a library, I would start by cloning the SOCI repository: https://github.com/SOCI/soci Then I would make the following changes: * Support boost::system::error_code in addition to the current use of exceptions * Use the boost idioms like source_location and BOOST_ASSERT * Modernize the customization point interface ("exchange" in soci parlance) * Export backends using run-time dependency injection to make linking easier * Adjust the APIs as needed to make writing insecure queries less likely This would bring about the following benefits: * 13 years of SOCI development for free: bugs, design, features * Very little effort required compared to writing a lib from scratch * Existing users of soci already know the API * Multiple backends are supported, with the possibility for adding more Thanks

On Fri, Mar 22, 2024 at 9:39 AM Julien Blanc via Boost wrote:

This is the kind of feature i would definitely like from a modern db higher level library.

I don't think that's reasonable to be honest. Reinventing parsing, and constexpr / at compile-time at that, is an enormous ask. Thanks

Am 22.03.2024 um 17:55 schrieb Vinnie Falco via Boost:

On Fri, Mar 22, 2024 at 9:39 AM Julien Blanc via Boost wrote:

...

This is the kind of feature i would definitely like from a modern db higher level library. I don't think that's reasonable to be honest. Reinventing parsing, and constexpr / at compile-time at that, is an enormous ask.

I wouldn't characterise that as 'unreasonable' - others obviously feel the same. Because of that, a library with such capabilities that already exists: https://github.com/rbock/sqlpp11. It might be interesting to see how proposed boost.sqllite compares to such a strong, mature contender. Thanks,   Dani -- PGP/GPG: 2CCB 3ECB 0954 5CD3 B0DB 6AA0 BA03 56A1 2C4638C5

On Sat, Mar 23, 2024 at 7:49 PM Vinnie Falco wrote:

On Fri, Mar 22, 2024 at 10:51 PM Daniela Engert via Boost wrote:

...

I wouldn't characterise that as 'unreasonable' - others obviously feel the same. Because of that, a library with such capabilities that already exists: https://github.com/rbock/sqlpp11.

That is a beautiful library but I still maintain that the original request, type-safe SQL strings (and constexpr at that) is unreasonable.

The library you linked does not use SQL strings at all, it has its own EDSL. It is not actually SQL (which represents database queries using a string) but rather "sql-flavored" C++ API. Julien wanted this:

prepare("insert into author (first_name, last_name) values (?1, ?2), (?3, ?4), (?5, ?6), (?7, ?8)");

to be type-safe. Doing so would require parsing the SQL string. It is this which is unreasonable as it is reinventing the parser from sqlite.

It's not very useful either. Yes, you could check if an argument is missing, but you can never know if a query is valid at runtime, e.g. if the `author` table exists can only ever be checked at runtime. And on top of that, missing an input argument is not an error in sqlite - it's just null.

I have to ask an obvious question, why didn't the author of proposed Boost.Sqlite just start with one of these mature libraries? This sqlpp11 looks very nice, comparable to soci if not better.

I think that's because you have multiple tasks that are often done by different libraries in multiple languages. In the lowest layer (1) you have the specific database client (e.g. boost.mysql), on layer 2 a generic database client (e.g. SoCi), then a query builder on layer 3 (e.g. sqlpp11) and then you usually have ORMs in layer 4, when you have reflection in the language. boost.sqlite would be strictly layer (1) so it can support sqlite specific features. A generic database client would be great, too, especially when built on top of boost.mysql and boost.sqlite, but that's just not what boost.sqlite tries to be. So I don't see either library as contenders. The actual contenders are other sqlite client libraries in C++ and I list some in the readme. I don't think it's helpful to compare this proposed library to generic sql clients or query builders, just like it wouldn't have been helpful to compare boost.beast to python requests. Peter pointed out that it might be a good idea to add some side to side comparison of using sqlite with using my library and he's correct since this does not seem apparent. But that's the problem this library tries to solve.

On Sat, Mar 23, 2024 at 10:13 AM Richard Hodges via Boost < boost@lists.boost.org> wrote:

Using the mailing list as a peanut gallery to punish innovation is distasteful and rude.

How do we determine whether there is innovation without discussion? It is very fair to ask what, precisely, is the innovation offered by this proposed library?

High quality submissions to boost are welcome.

And the responses to the request for endorsement, for which you have expressed a desire to see less of, are precisely aimed at determining whether or not the library in question is "high quality." There is a tendency to prefer writing new code to using what already exists. What makes this library better than others? Discussion is needed to determine this. Regards

Le dimanche 24 mars 2024 à 00:51 +0800, Klemens Morgenstern via Boost a écrit :

On Sat, Mar 23, 2024 at 7:49 PM Vinnie Falco wrote:

...

Julien wanted this:

    prepare("insert into author (first_name, last_name) values (?1, ?2), (?3, ?4), (?5, ?6), (?7, ?8)");

to be type-safe. Doing so would require parsing the SQL string. It is this which is unreasonable as it is reinventing the parser from sqlite.

That's right, but to be honest i did not expected it to be true. However, the kind of reply i expected was like "No, it won't, but you can achieve a similar result by X". I see it's more "it's well outside the scope of the library" (which is fine as well, just not attracting my attention).

It's not very useful either. Yes, you could check if an argument is missing, but you can never know if a query is valid at runtime, e.g. if the `author` table exists can only ever be checked at runtime.

Allow me to disagree here: everything you can catch sooner than later, is useful.

And on top of that, missing an input argument is not an error in sqlite - it's just null.

I wasn't aware of that. But it just feels plain wrong.

I think that's because you have multiple tasks that are often done by different libraries in multiple languages.

In the lowest layer (1) you have the specific database client (e.g. boost.mysql), on layer 2 a generic database client (e.g. SoCi), then a query builder on layer 3 (e.g. sqlpp11) and then you usually have ORMs in layer 4, when you have reflection in the language.

boost.sqlite would be strictly layer (1) so it can support sqlite specific features. A generic database client would be great, too, especially when built on top of boost.mysql and boost.sqlite, but that's just not what boost.sqlite tries to be. So I don't see either library as contenders. The actual contenders are other sqlite client libraries in C++ and I list some in the readme.

I agree with that. However, sqlite c++ wrappers have been here for ages, 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 * json support Did i miss others ?

Peter pointed out that it might be a good idea to add some side to side comparison of using sqlite with using my library and he's correct since this does not seem apparent. But that's the problem this library tries to solve.

Another useful addition would be side-by-side comparison with sqlitecpp. As i understand from the docs, boost.sqlite has a few more features. Are the missing features what made the author start its own library? Regards, Julien

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/sqli... 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_inde...

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

On Tue, Mar 26, 2024 at 9:03 AM Peter Dimov via Boost wrote:

Klemens Morgenstern wrote:

...

...

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 `*` ?

In the usual manner? The rows of `SELECT x, y FROM ...` can be retrieved as

struct R { int x; std::string y; };

BOOST_DESCRIBE_STRUCT(R, (), (x, y))

I added support for describe, tuples and pfr (on C++20) see here: https://github.com/klemens-morgenstern/sqlite?tab=readme-ov-file#typed-queri... The implementation is currently done through tag_invoke in `detail`, so it would be possible to open this up to more types if I can be convinced of a use-case. struct query_result { std::string first_name, lib_name;}; BOOST_DESCRIBE_STRUCT(query_result, (), (first_name, lib_name)); for (auto q : conn.query( "select first_name, collect_libs(name) as lib_name" " from author inner join library l on author.id = l.author group by last_name")) std::cout << q.first_name << " authored " << q.lib_name << std::endl;

On Wed, Mar 27, 2024 at 2:32 AM Artyom Beilis via Boost wrote:

Hello,

Few comments on the lib:

1. Documentation is far from sufficient and examples are not really clear

I have yet to see documentation of a boost library that people are happy with.

2. Transaction handling seems to be very simplistics.

The main reason here is that the sqlite api doesn't have any transaction handling either, it's just more sql queries. With boost.scope handwriting a guard is trivial - and you don't have the issue that you might potentially throw from a destructor.

I suggest create transaction guard that it destructor would call rollback unless committed See how I do it in cppdb: http://cppcms.com/sql/cppdb/transaction.html

Basically cppdb::transaction guard(sql); sql << "UPDATE accounts SET amount=amount+? WHERE user=?" << amount << receiver << cppdb::exec; sql << "UPDATE accounts SET amount=amount-? WHERE user=?" << amount << sender << cppdb::exec; guard.commit(); And if something happens the guard would call rollback automatically, this is something that is required for any SQL library. 3. Error handling: how do you handle deadlocks/timeouts/SQLIGHT BUSY events?

I don't. That's what the error_code overloads are for.

It seems to have rather good potential.

On a side note: I'm the author of cppdb: http://cppcms.com/sql/cppdb/index.html it supports sqlite, mysql, postgress and ODBC/MSSQL It developed it back than and it does quite a lot of good stuff including caching prepared statements and many other goodies.

It is included in Debian and Ubuntu distributions.

Unfortunately I don't have much time to spend on it, it wasn't updated to C++11 friendly interface, would you consider using it as a base and extending it? It needs a more modern interface and many updates. If you do I'll release it under Boost license as well

I think the generic sql client that could excite me would be an asio-based one. I don't think cppdb is a got fit there.

Best, Artyom Beilis

On Fri, Mar 22, 2024 at 3:30 AM Klemens Morgenstern via Boost < boost@lists.boost.org> wrote:

...

A while back I asked about interest in a boost.sqlite library and got enough engagement that I think it's worth proposing.

Hence I am seeking an endorsement and would like to see if someone wants to volunteer as review manager.

Code: https://github.com/klemens-morgenstern/sqlite Docs: https://klemens.dev/sqlite/

_______________________________________________ Unsubscribe & other changes: http://lists.boost.org/mailman/listinfo.cgi/boost

_______________________________________________ Unsubscribe & other changes: http://lists.boost.org/mailman/listinfo.cgi/boost