Re: Review of Boost.MySql

From: Dennis Luehring (dl.soluz_at_[hidden])
Date: 2022-05-12 08:21:17

Am 12.05.2022 um 09:46 schrieb Ruben Perez:
> On Wed, 11 May 2022 at 06:16, Dennis Luehring via Boost
> <boost_at_[hidden]> wrote:
> >
> > Am 10.05.2022 um 22:33 schrieb Marcelo Zimbres Silva via Boost:
> > > users could do
> > >
> > >     boost::mysql::row<T1, T2, T3, T3> row;
> > >     while (result.async_read_one(row, additional_info, yield[ec]))
> > >
> > > avoiding unnecessary further copies. In principle it would be even
> > > possible to parse directly in a custom data structure, for example,
> > > when storing json string in the database.
> >
> > i've developed a small C++17 example of how to work with
> > Input/Output transformers to write the statements more compact
> > and type-safe
> >
> >
> > the base idea is that the MySQL <-> C++ needs sometimes some sort
> > of conversions and to combine most of the infos at one point so its
> > easier or at least possible to optimize better - the current interface
> > looks very runtime allocation stylish :)
>
> Thanks for sharing. I think I get the idea of "allowing the user to define
> custom MySQL <=> C++ mappings".
>
> I'm not really getting the interface that your input/output
> transformers would expose, though. Let's take this one, for example:
>
> template <>
> struct OutputTransformer<NullAsEmptyString>
> {
>      using value_type = std::string;
>
>      static std::string get_value( int /*index_*/ )
>      {
>          static std::string x = "hello";
>          return x;
>      };
>      static void get_value( int index_, std::string& value_ )
>      {
>          value_ = get_value( index_ );
>      };
> };
>
> I assume this is user-provided code. Where does the user get the
> string value from? What does index_ mean in this context?

sorry for beeing not clear (and sending a not directly fitting example)

that code should be library code - more a less a collection of
base-mysql concepts
that can be used - this sample transformer lets you act empty strings as
null in mysql
- the implementation is a dummy - only to get a feeling how the data-flow is

my adaption is used with SQLite and the index is the parameter index
that would then map to SQLite bind functions or as in this case checks
if the
value is null and returns ""

plus serveral other "typical" helper for adaption problems

the transformer get also used for all fetch routines

> Let's also take a look at actually using the statements:
>
>          My_select my_select( db_connection, "select a, b, c from test
> where d == ?1" );
>
>          {
>              // fetch into ref tuple
>              int some_int{};
>              float some_float{};
>              std::string some_string;
>              my_select( { some_int, some_float, some_string }, { 123 } );
>          }
>
> How is that different from the following snippet?
>
> resultset r = conn.query("select a, b, c from test where d == ?1" );
> tuple<int, float, string> row;
> r.read_one(row);

my goal was to keep the sql-string combined with the Prepared_fetch_1
instanciation
but string use in templates is a little bit limited

and i also map input types for inserts or where clauses - thats also
possible with
splitted tuples for the input/output data but then its even more
separated from the statement (which is tied to the input/output types)

to know as much as possible before-hand - allows maybe deeper
optimization etc. for example the my_select instance
can use prepared statements per default (and this is connection oriented
with sqlite)

the "readers" are just variants (that also allow to beeing const - see
const auto tuple):

// fetch into ref tuple
my_select( { ein_int, ein_float, ein_string }, { 123 } );

// return value tuple
const auto [ein_int2, ein_float2, ein_string2] = my_select( { 123 } );

// fetch into class/struct...
Result3 result;
my_select( result, { 123 } );

the real optimization party starts with multi row fetches

// multi row fetch
using My_select = Prepared_fetch<std::tuple<int>, std::tuple<int, float,
NullAsEmptyString>>;
My_select my_select( db_connection, "select a, b from test where c == ?1" );

std::vector<Result2> result;
my_select.fetch_copy( std::back_inserter( result ), 34 );
my_select.fetch_copy( result, 34 );

auto fetch_func = []( const int& /*ein_int_*/, const float& /*ein_float_*/,
 Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â  const std::string& /*ein_string_*/ ) {};
my_select.fetch_func( fetch_func, 34 );
auto fetch_func_cancel = []( const int& /*ein_int_*/, const float&
/*ein_float_*/,
 Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â  const std::string& /*ein_string_*/ ) {
return false; };
my_select.fetch_func_with_cancel( fetch_func_cancel, 34 );

because i know at instanciation times what parts are fixed, variant size
etc. - so i can further reduce
the memory overhead etc. - you could directly combine the procotocl
result parsing with the result-set content etc.
its not implemented in my sqlite wrapper so far but the interface allows
such optization (if the backend is deep enough - like yours)

that means fetch-copy can be prepared at compile time for exact the data
etc. would allow zero or less-copy concepts

> >
> >
> > the Input- and Output Transformers help to work with basic types
> > and also with SQL/MySQL special types like Null-string etc. - its not
> > possible to map MySQL types always 1:1 to C++ and back, sometimes you
> > want to
> > behave the transformation different
> >
> > the Transformers aren't visible when used with basic types
> >
> >
> > its just an example to promote the Idea: https://pastebin.com/raw/vepbTAKL
> >
> >
> > the best combination would be some sort of fluent SQL interface like:
> > https://github.com/rbock/sqlpp11
>
> This library is supposed to be a protocol driver library, so it
> provides primitives
> close to the MySQL protocol. sqlpp11 is great, but it's a higher level library.
> I don't think it makes sense trying to incorporate this kind of features here.
> It would make more sense for a higher level library like sqlpp11 to build
> on top of Boost.MySQL, instead.

Boost does only provide low level stuff for real low level concepts
(smart-pointer, maps etc.-)
but most other libraries are always introducing very high level concepts

>
> >
> > but i still think a plain string+binding interface like Boost.MySql
> > currently got is also needed
> >
> >
> >
> >
> > _______________________________________________
> > Unsubscribe & other changes: http://lists.boost.org/mailman/listinfo.cgi/boost