Re: MySQL ASIO library

From: Ruben Perez (rubenperez038_at_[hidden])
Date: 2020-04-03 18:08:41

Hi all,

I would like to re-take the discussion line about the MySQL library against
the support-all-SQL library approach. As you all know, the current
MySQL-Asio library only supports MySQL, following the approach of "doing a
single thing and doing it right". As I already mentioned, I see this as a
building block that a higher-level library can use, or as a directly usable
library if you just need MySQL.

However, I know some of you were concerned about the potential difficulty
in integrating different libraries like MySQL-Asio, in terms of complexity
and efficiency. I have been looking into another SQL database protocol
(concretely PostgreSQL, as it's the one I know the most after MySQL). In
this mail I want to compare the two protocols from a high level
perspective, and focus on the possible trouble that a higher-level SQL
library could encounter when integrating MySQL-Asio with its equivalent for
PostgreSQL (please note that the library for PostgreSQL does NOT exist yet).

- Both protocols start with a handshake. Most of the handshake parameters
(credentials, schema to use...) are common, but some may be
database-specific (e.g. collation to use for MySQL). I don't see much
problem creating a wrapper with the minimum set of parameters for all
backends in the higher-level library.
- Both protocols support a "query single" operation, where a SQL text
string is sent to the server and a resultset is sent back (more on
resultsets later).
- Both protocols support prepared statements, where you send a statement to
be prepared, as a text string, and something representing the prepared
statement is returned. This prepared statement may then be executed as many
times as required, returning a resultset each time. For PostgreSQL, the
execution model is more granular than in MySQL (i.e. in MySQL there would
be a single execute() call, in PostgreSQL there would be a bind(), an
execute() and a sync(), the result of these three being a resultset). The
higher-level library expose the minimum subset of steps. Again, I think it
can be done without too many problems.
- When a resultset is returned, both protocols send each individual row in
a separate message. This is something I use in MySQL-Asio to allow
single-row retrieval and could be implemented for PostgreSQL similarly.
- When a resultset is returned, both protocols return some metadata
describing the columns the resultset is made of. This is a little bit more
heterogeneous, but there is common stuff (field name, field type...).
Again, I see feasible that a high-level library exposes the common subset
of metadata.
- In MySQL-Asio, values are represented as a variant of all the types
supported by the database. MySQL-Asio exposes all types supported by MySQL.
Some of them are SQL standard and other are extensions. I guess the
higher-level library should only expose the SQL standard types, and thus a
mapping is required here. MySQL-Asio tries to make things as efficient as
possible and avoids copying as much as possible. All used types are either
ints/floats, datetimes (from the date library and chrono), or
string_view's. Concretely, strings are not copied, but the original message
is kept alive instead. With all this, I think a reasonably efficient
mapping could be implemented by a higher-level library.

I hope this analysis may help convince those of you still in doubt. Any
thoughts or suggestions are welcome.

Regards,
Ruben.

On Wed, 4 Mar 2020 at 16:07, Paul A Bristow via Boost <boost_at_[hidden]>
wrote:

>
>
> > -----Original Message-----
> > From: Boost <boost-bounces_at_[hidden]> On Behalf Of Richard Hodges
> via
> > Boost
> > Sent: 4 March 2020 13:46
> > To: boost_at_[hidden] List <boost_at_[hidden]>
> > Cc: Richard Hodges <hodges.r_at_[hidden]>
> > Subject: Re: [boost] MySQL ASIO library
> >
> > >
> > > I'm not sure about Boost.MPI, but I thought it was not a wrapper of a
> > > single library, but of a standard API that can be implemented by
> > > different libraries. Boost.Regex is not a wrapper at all; it
> > > implements regular expressions from scratch. asio::ssl is not a
> > > library but a plugin for Boost.ASIO that provides one small piece of
> > > functionality compared to the rest of the library. Boost.Python is
> > > probably closest to an exception, although it is a binding to another
> > > language (not a library), which arguably only has one C API and
> > > implementation. Yes, there is CPython, but I don't believe it offers a
> > > C API.
> > >
> >
> > This line of discussion between us is now moot. The author has confirmed
> that
> the
> > implementation of the mysql protocol is original work.
> >
> > I don't think the amount of contributions by itself is the goal. There
> >
> > > has to be value associated with the contribution. I just don't think a
> > > C++ wrapper of a specific library has enough value.
> > >
> >
> > I for one have needed a good async mysql database layer on two occasions
> in
> > production systems.
> >
> > The first time I wrote a minimal wrapper around the c mysql libs (the
> c++ one
> is
> > awful).
> >
> > The second time I used amy, which is not fully asio compliant (it doesn't
> support
> > coroutines or futures).
> >
> > As a user of boost for over ten years, I would have benefitted greatly
> from a
> library
> > like this being in boost.
> >
> > I am not alone.
> >
> > Talking to MySQL is a fundamental operation in the web world, which
> represents
> a
> > huge chunk of programming effort.
> >
> > It seems a no-brainer to me that a well maintained means of efficiently
> doing
> so
> > would be a positive addition to boost.
>
> By itself, this is a reasonably convincing case, but what would quiet some
> of
> doubters would be to have at least an outline of connecting to another
> database.
> Showing reasonable confidence that extension to other databases is feasible
> would be a big plus IMO.
>
> Paul
>
>
>
> _______________________________________________
> Unsubscribe & other changes:
> http://lists.boost.org/mailman/listinfo.cgi/boost
>