On Wed, 10 Mar 2021 at 15:14, Ruben Perez via Boost wrote:

On Wed, 10 Mar 2021 at 03:32, Edward Diener via Boost wrote:

...

On 3/9/2021 5:04 PM, Ruben Perez via Boost wrote:

...

I would like to propose a MySQL client library for Boost (under the original name of Boost.Mysql). It is a full implementation of the MySQL client protocol, based on Boost.Asio. It requires C++11.

There is a MySQL++ library at https://tangentsoft.com/mysqlpp/home, which I have used in the past and found useful. How does your API compared to that library ?

- The scope of the two libraries are inherently different. mysql++ is a C++ wrapper around libmysqlclient, and provides things like query building and connection pools. Boost.Mysql is an implementation of the MySQL client protocol, and thus provides an API to use that protocol. Boost.Mysql is a level of abstraction below mysql++ - it is at libmysqlclient level.

That makes me happy to endorse your library. Having witnessed failure of Boost efforts towards std::rdb a decade+ ago, having maintained at least two wrappers on top of a database client libraries, I'm enthusiastic about the protocol implementation approach and happy to observe the interest here. Unlike a wrapper library, this has a chance to find supporters among the Boost Community members. Best regards, -- Mateusz Loskot, http://mateusz.loskot.net

On Wed, 10 Mar 2021 at 16:16, Niall Douglas via Boost wrote:

On 10/03/2021 14:13, Ruben Perez via Boost wrote:

...

Values are stored as variants. Having more integer types means having more options in the variant. This does two nasty effects:

I am not warm on the use of variants in this situation.

One should bind types to specifics before talking to a DB, and if those types do not match, then the operation should fail.

Interestingly I take the opposite view, from the point of view of userland utility. MySQL is often used in web environments which are fluid by nature. If the program finds a value type in the variant that is unexpected it's quite at liberty to throw an exception or abort.

Even for querying unknown DBs, I still find use of variants here inappropriate. You should be able to visit a template templated with the right type, sure, but that isn't your library going off and making parsing decisions on the behalf on the end user without them telling you what parsing is, for this situation.

This sounds like a higher level interface, which may be warranted, but I would be sad to see the variants go.

...

I don't think the performance gain you may get from using 1, 2 or 4 byte integers (if any) is worth it, especially in 64 bit systems.

It's not about performance. It's about correctness.

It's also about ease of writing code quickly (particularly in environments

where MySQL is commonly used). In almost all applications, "1", 1 and true all mean the same thing. I think there's a tradeoff here: On the one hand we'd like 100% correct programs, On the other, in a startup during initial application development, under tight constraints of time and money, perfection can be the enemy of good. It's not uncommon to have to make schema changes on the fly and it's arguably better if all the users don't have to be disconnected and have their client apps upgraded first. I'd be in favour of seeing a later evolution of the library on this front, maybe with an easy-mode interface and a pedantic interface.

Niall

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

-- Richard Hodges hodges.r@gmail.com office: +442032898513 home: +376841522 mobile: +376380212

On 10/03/2021 15:34, Richard Hodges via Boost wrote:

On Wed, 10 Mar 2021 at 16:16, Niall Douglas via Boost wrote:

...

On 10/03/2021 14:13, Ruben Perez via Boost wrote:

...

Values are stored as variants. Having more integer types means having more options in the variant. This does two nasty effects:

I am not warm on the use of variants in this situation.

One should bind types to specifics before talking to a DB, and if those types do not match, then the operation should fail.

Interestingly I take the opposite view, from the point of view of userland utility. MySQL is often used in web environments which are fluid by nature. If the program finds a value type in the variant that is unexpected it's quite at liberty to throw an exception or abort.

I'm not warm on variants in general. I find them hugely overused in code written right now. Just because they're fashionable doesn't make them a wise choice.

In almost all applications, "1", 1 and true all mean the same thing.

Absolutely not. The *end user* may choose them to mean the same thing. The *library* must not do so.

I think there's a tradeoff here: On the one hand we'd like 100% correct programs, On the other, in a startup during initial application development, under tight constraints of time and money, perfection can be the enemy of good. It's not uncommon to have to make schema changes on the fly and it's arguably better if all the users don't have to be disconnected and have their client apps upgraded first.

Having worked in several startups in my career, I have not once found ANY sacrifice of correctness ever worth it. Those startups who insisted ended up with a buggy PoS which had to be completely rewritten, which rarely happens because the VC money runs out first. That opinion you just stated is therefore, in my opinion, equal to one major cause of why startups fail. Exactly why strict binding is important is to prevent people changing the DB schema on the fly. DB schemas are like library ABIs, you design them right, once, and from then on your ship has sailed. If people come along later wanting them to be changed in a breaking way, you tell them no. If management insist, you freshen your CV and start looking for new employment elsewhere.

I'd be in favour of seeing a later evolution of the library on this front, maybe with an easy-mode interface and a pedantic interface.

Boost has historically aimed for correctness before performance, ease of use, or anything else. Claiming that talking to DBs somehow is an exception to correctness because "it's not C++" doesn't wash with me, especially as SQL is a strictly typed language just like C++, with a near one-one mapping onto C++ types. Even if it were Python duck typing here, I'd tend towards strictness as the default design. Only if you're interfacing with weakly typed languages such as Javascript do things like variant storage, eager parsing, and how this proposed library appears to be designed, make sense to me. Instead of variants here, I'd accept a visit based interface functioning as dependency injection whereby generic code is instantiated at runtime from the DB schema. i.e. move the variant switching up to the top of logic, instead of being at the bottom. I would like to see rules about what combinations are to be banned or accepted, a bit like proposed Boost.DI has. In fact, there is not a weak argument that the proper bottom up runtime DB abstraction is written using DI, and a completely separate DB abstraction comes top down with compile time static binding. My other big objection to a Boost.MySQL is the same as that from /r/cpp, that I can't see any good reason why there shouldn't be a generic DB abstracted API suitable for at least SQLite, Postgre, and MySQL. One of many possible backends would be MySQL. I don't mind if only MySQL and SQLite backends are implemented, but point is it is cheap to design this thing right, and the current proposed library doesn't do it for no obvious reason. Any arguments about "performance" don't cut it for me like they might (weakly) for Boost.JSON, any SQL DB is very slow by definition, if you are using a DB, you can even do virtual inheritance with generates terrible codegen and it would not matter a jot. Niall

On 10.03.21 16:34, Richard Hodges via Boost wrote:

In almost all applications, "1", 1 and true all mean the same thing.

Absolutely not. Strings are not, and must not be implicitly converted to, booleans. "True" is a perfectly valid last name, as is "Null". There are real people with these names. Software that treats these names as a boolean value and no value respectively is broken. -- Rainer Deyke (rainerd@eldwood.com)

Rainer Deyke wrote:

On 10.03.21 16:34, Richard Hodges via Boost wrote:

...

In almost all applications, "1", 1 and true all mean the same thing.

Absolutely not. Strings are not, and must not be implicitly converted to, booleans.

"True" is a perfectly valid last name, as is "Null". There are real people with these names. Software that treats these names as a boolean value and no value respectively is broken.

https://twitter.com/RachelTrue/status/1368004197166108676

On Thu, 11 Mar 2021 at 12:27, Kostas Savvidis via Boost < boost@lists.boost.org> wrote:

...

On Mar 10, 2021, at 17:34, Richard Hodges via Boost < boost@lists.boost.org> wrote: On Wed, 10 Mar 2021 at 16:16, Niall Douglas via Boost < boost@lists.boost.org> wrote:

...

On 10/03/2021 14:13, Ruben Perez via Boost wrote:

...

I don't think the performance gain you may get from using 1, 2 or 4 byte integers (if any) is worth it, especially in 64 bit systems.

It's not about performance. It's about correctness.

It's also about ease of writing code quickly (particularly in environments where MySQL is commonly used). In almost all applications, "1", 1 and true all mean the same thing.

In C and C++ we have implicit promotion rules, usual arithmetic conversions, and what not. Headache, yes for sure, but I will conjecture that bringing any SQL integer type, even unsigned, and treating it as int64_t does not violate any standard and thus is not an issue of correctness.

Signed types are represented as int64_t and unsigned integers as uint64_t, to guarantee there is no overflow in any case.

However, I would like to enquire of the author about the treatment of the following:

1) Section 11.1.4 - Floating-Point Types SQL allows to specify floating point precision p as FLOAT( p )- do you respect columns with p != 24 or 53?

Yes, this library does support any FLOAT(p) types. Note however that MySQL does a quite simplistic handling here - it just uses p to determine storage size. If p <= 23, you get a float, else you get a double. This page https://dev.mysql.com/doc/refman/8.0/en/floating-point-types.html provides more info. Still, we have automated integration tests for such cases. This file https://github.com/anarthal/mysql/blob/master/test/integration/db_setup.sql contains the source code for the database types that are part of this library's CI builds - if you spot something missing please let me know.

2) Section 11.1.6 - UNSIGNED is allowed in MySQL even on floating point types - what do you do when you insert into such a column?

First of all, I haven't done an awful lot of tests here because MySQL has deprecated the feature. As far as I know, MySQL treats such a type like a regular FLOAT, but will restrict your range to only positive values. I don't do any special handling here. I don't have integration tests for this particular case, but the errors are generated server-side, so I don't think it should cause any problem. I would expect connection::query() or prepared_statement::execute() to fail with the corresponding MySQL error code if you provide a negative value here. Unsigned floats don't get a special representation in the C++ side.

3) Section 11.1.3 talks of both DECIMAL and NUMERIC as exact values and MySQL works with up to 65 decimal digit precision, presumably doing calculations in decimal as well. What is this type mapped into?

They are represented as string values (so boost::string_view). This avoids loosing precision in applications where precision is critical. That string_view can be parsed and used by a specialized library later. MySQL protocol takes this approach (it sends DECIMAL and NUMERIC values as strings even in the "binary protocol", used in prepared statements), so I found it easier to just respect it.

4) Section 11.1.6 - Do you need to handle ZEROFILL?

It does not get any special handling, but there is a good amount of integration testing around here to double check that none is needed. It will just be mapped to the corresponding integer. In this case, it will always be uint64_t, as ZEROFILL implies UNSIGNED. You can get this sort of information using the field metadata https://anarthal.github.io/mysql/mysql/ref/boost__mysql__field_metadata.html API.

5) Some general comment about Date and Time related stuff?

This library supports DATE, TIME, DATETIME and TIMESTAMP. In a nutshell, DATE is mapped to std::chrono::time_point (where days mimics C++20 std::chrono::days definition), DATETIME and TIMESTAMP are mapped to std::chrono::time_point, and TIME is mapped to std::chrono::microseconds. There are a couple edge cases depending on how you configured your MySQL server, though. For reference, I would suggest reading this section https://anarthal.github.io/mysql/mysql/types.html of the documentation for these topics. If there is anything unclear, please let me know.

Thanks, Kostas

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

On Wed, 10 Mar 2021 at 21:55, Edward Diener via Boost wrote:

...

On Wed, 10 Mar 2021 at 03:32, Edward Diener via Boost < boost@lists.boost.org> wrote:

...

...

Hi all,

I would like to propose a MySQL client library for Boost (under the original name of Boost.Mysql). It is a full implementation of the MySQL client protocol, based on Boost.Asio. It requires C++11.

The library is at https://github.com/anarthal/mysql and the documentation is at https://anarthal.github.io/mysql/. Examples under https://anarthal.github.io/mysql/mysql/examples.html.

The purpose of this library is to provide a low-level building block for applications or other libraries to interact with MySQL, in the same way Boost.Beast provides a way to interact with HTTP. It complies with Boost.Asio universal asynchronous model, and provides the usual sync and async overloads for every network operation.

Some notes:

- The scope of this library is limited to MySQL. Its value

On 3/9/2021 5:04 PM, Ruben Perez via Boost wrote: proposition

...

is implementing the client protocol in an Asio-compliant way.

Supporting

...

any database other than MySQL is not in scope of this library. - This library does NOT make use of MySQL libmysqlclient C API. It provides a from-the-ground-up implementation of the MySQL client

protocol.

There is a MySQL++ library at https://tangentsoft.com/mysqlpp/home, which I have used in the past and found useful. How does your API compared to that library ?

- The scope of the two libraries are inherently different. mysql++ is a C++ wrapper around libmysqlclient, and provides things like query building and connection pools. Boost.Mysql is an implementation of the MySQL client protocol, and thus provides an API to use that protocol. Boost.Mysql is a level of abstraction below mysql++ - it is at libmysqlclient level. - Boost.Mysql provides sync and async operations, both using Boost.Asio, and async operations follow Asio's universal async model, so you can use callbacks, Boost coroutines, C++20 coroutines or futures. mysql++ does not provide async functions because libmysqlclient doesn't do it, either. - Boost.Mysql provides access to server-side prepared statements, while I haven't seen the option in mysql++ (from having a quick look, their template queries seem to be handled client side). Server-side

On 3/10/2021 9:13 AM, Ruben Perez via Boost wrote: prepared

...

statements are faster, more robust and more secure. - Boost.Mysql represents MySQL values as variants, allowing standard operations like visit. Each member if the variant is a vocabulary

type

...

(e.g. std::chrono::time_point/duration for datetimes/times, boost::string_view for strings...). mysql++ opts for custom types for strings, dates, times and datetimes. The mysqlpp::String type is

kind of a

...

variant type, also - it can be converted to any other mysqlpp type.

This,

...

however, closes the door for prepared statements, as the format the

server

...

uses is not string-based. - One nice thing about mysqlpp is that it provides the ability to

parse

...

rows into custom data structures. While Boost.Mysql can't do that at

the

...

moment, we will likely add the feature soon (using Boost.Describe if

it

...

gets into Boost). - Boost.Mysql is compatible with CMake and B2.

Thanks for the information. I do not recall needing an MySQL client library when programming in the past using the MySQL++ library, even in a client-server environment where the program was on one machine and the MySQL database was on another, so maybe you can explain better what your MySql client library would give me. As I recall when I made the connection in the program to the server database there was a way of specifying in the code a web address of where the server database resided and that, along with a username/password got my code access to the server database with a set of priveleges based on my username/password. So I admit I do not understand very well what the need is for a Boost MySQL client library over and above, for instance, what the MySQL++ library offers. Maybe you could add an explanation of this in your documentation as I do believe that MySQL++ is an extremely popular library for C++ programmers to do their programming when working with a MySQL for MariaDB database and by specifying what your MySql client library offers and how it interfaces with the MySql++ library, you would be promoting the importance and usefulness of your library.

MySQL server and client communicate using the MySQL client protocol. That's an application protocol over TCP (or UNIX socket). If your application communicates with the MySQL server, then you are using a MySQL client library. mysql++ is one of these client libraries - that's why it allows you to interface with the MySQL server. mysql++ doesn't implement the protocol itself - instead, it relies on libmysqlclient - the official C client library - for that. The mysql command line utility also uses libmysqlclient to communicate with the server. This library is similar to libmysqlclient in the sense that it implements the MySQL protocol, too. I personally didn't know of mysql++. Thanks for the note, I will add a comment in the docs. Opened https://github.com/anarthal/mysql/issues/50 to track that.

...

...

I also looked at your mappings and very small database types are mapped to int64_t rather than smaller integers. Is there a reason for this, as it seems like a waste of space that should not be necessary ?

Values are stored as variants. Having more integer types means having

more

...

options in the variant. This does two nasty effects: - It increases user code complexity, as you have more options to consider. - Visiting (which is used by both user code and the library itself) becomes more expensive (the vtable ends up having more entries).

I don't think the performance gain you may get from using 1, 2 or 4 byte integers (if any) is worth it, especially in 64 bit systems.

I think you are wrong. Either your use of variants in your library is wrong or you should not be using variants. A C++ variant is like an advanced C++ union, whose purpose is to represent one of a number of different C++ types, with a value of one of those types being current at any one time. So your explanation of why you must waste space by holding values in a much greater type than needed makes no sense to me and is not the way I would want to program in general. It is your design and you can do what you want with it, but I would strongly suggest that in C++ code the decision to just keep some data in the largest type possible, when you know that the data could "fit" into a smaller type is generally not correct or optimal programming.

A quite common operation you would use for a variant is visit(), which will call a different function depending on the type the variant holds. We use this in the library when serializing prepared statement parameters, for example. As far as I know, the usual way visit() is implemented is by creating a function pointer table (similar to a vtable) using template magic. You have a function pointer in that table for each possible type in the variant. That means that having more types in the variant does have a cost, indeed. As you also mentioned, a variant is like a tagged union, meaning that its size is bounded by its largest member. Adding smaller integer types to the variant won't decrease its size. If we add parsing rows to custom types (currently not supported), that would be a different story.

...

...

I do like the fact that you are using asio for asynchronous handling and advance features above C++11 when compiling at a level higher than

C++11.

...

...

...

Features:

- Text queries and server-side prepared statements. - Sync and async functions, following Boost.Asio's universal async model. You may use callbacks, Boost coroutines, futures, C++20

coroutines,

...

or any completion handler that Asio adds support for. - Can be used with any Stream including TCP sockets, UNIX sockets

and

...

...

Boost.Beast TCP streams. - Supports MySQL 5.x, MySQL 8.x and MariaDB.

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