Le mercredi 04 mars 2020 à 16:06 +0000, Paul A Bristow via Boost a écrit :

...

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.

I expect a more versatile db library to rely on underlying low-level backends such as this one, so i tend to disagree here: trying to handle multiple database here will greatly increase complexity with few additional value for the user: differences in SQL dialects and supported types, for example, will anyway prevent any simple backend switch for the user. Having a common shared design for the different backends, however, will indeed be useful in building a higher level db abstraction, though (not sure if it was your point). Regards, Julien

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 wrote:

...

-----Original Message----- From: Boost On Behalf Of Richard Hodges via Boost Sent: 4 March 2020 13:46 To: boost@lists.boost.org List Cc: Richard Hodges 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

Thanks for the info. Is Soci interoperable with Asio (as in providing async ops working with callbacks/futures/coroutines)? I would say this is what MySQL-Asio offers as opposed to other MySQL C++ libraries, including the official connector. I have no intention on implementing this higher-level SQL library I was discussing, as I lack the time to do it (at least by now). El vie., 3 abr. 2020 20:41, Jeff Garland via Boost escribió:

I didn't read all the original discussion, but I'll point out that there's more than one c++ library that has at least attempted (we can argue the level of success) to provide the generalized sort of interface people are asking for. There's some significant thinking and effort that went into that. Have a look at soci -- not saying it's perfect, best or right -- just that I've used it in production systems and even written a backend for it to a database that isn't supported out of the box. They worked out all the issues for binding custom types like date_time, etc...

http://soci.sourceforge.net/doc/release/4.0/

On Fri, Apr 3, 2020 at 11:09 AM Ruben Perez via Boost < boost@lists.boost.org> wrote:

...

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@lists.boost.org> wrote:

...

...

-----Original Message----- From: Boost On Behalf Of Richard

...

...

Boost Sent: 4 March 2020 13:46 To: boost@lists.boost.org List Cc: Richard Hodges 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

via 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

Hodges 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

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

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

On Fri, 3 Apr 2020 at 21:04, Mateusz Loskot via Boost wrote:

On Fri, 3 Apr 2020 at 20:41, Jeff Garland via Boost wrote:

FYI, I mentioned SOCI in https://lists.boost.org/Archives/boost/2020/03/248307.php

Apologies, that is true. I have had a quick look into SOCI, and I think the aim is quite different than in MySQL-Asio. SOCI has an impressive support for a lot of backends but does not support (to my knowledge) asynchronous operations (it actually employs the official MySQL C library, which does not implement it). My aim was not to provide a universal DB connector, but to provide an implementation interoperable with Boost.Asio that supports the universal async model (callbacks, coroutines and futures). There is actually this mailing thread about async operations in SOCI: https://sourceforge.net/p/soci/mailman/message/20352440/. It mentions here that implementing async functionality in SOCI is difficult because the underlying backends don't support it (including the MySQL one). My library aims to solve these kind of problems, rather than competing with SOCI.

...

-- not saying it's perfect, best or right -- just that I've used it in production systems and even written a backend for it to a database that isn't supported out of the box. They worked out all the issues for binding custom types like date_time, etc...

Another, thin, approach to binding some types can be found in https://github.com/nanodbc/nanodbc

I see this supports async operations, but not with Asio (seems more C-like). My aim is to make a library that goes well for anyone already using Asio or Beast.

-----Original Message----- From: Boost On Behalf Of Ruben Perez via Boost Sent: 3 April 2020 19:09 To: boost@lists.boost.org Cc: Ruben Perez Subject: Re: [boost] MySQL ASIO library

Hi all,

I would like to re-take the discussion line about the MySQL library against

support-all-SQL library approach. As you all know, the current MySQL-Asio

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

- 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

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

As someone who suggested that this sort of analysis would help the cast for this proposed library, I sense that this is a considered and convincing argument that this library is extensible to other databases than MySQL. (But sadly I am not an expert on databases to know if the case will convince those much more knowledgeable). Of course, the 'proof of the pudding is in the eating' so a working link to another database would be much more persuasive, but I can understand the author reluctance to do this. So I would not discourage the author for continuing to prepare his library for Boost, but warn that it is a rocky road ahead for acceptance for any library proposal. But I can't see how there can't be users for it. Good luck. Paul the library library. the thoughts or

suggestions are welcome.

Regards, Ruben.

On Wed, 4 Mar 2020 at 16:07, Paul A Bristow via Boost wrote:

...

...

-----Original Message----- From: Boost On Behalf Of Richard Hodges via Boost Sent: 4 March 2020 13:46 To: boost@lists.boost.org List Cc: Richard Hodges 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

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

On Tue, 7 Apr 2020 at 05:18, Robert Ramey via Boost wrote:

I'm going to come down on the side of those who suggest that you consider other database systems besides mysql.

One suggestion for you: Think BIG!!!

I respectfully disagree, not in intent but in execution. There is certainly room, and a need (IMHO) for a Universal Async-enabled Database Client library in Boost. We are in full agreement there. But I would argue that such a library ought to be built on the foundations provided by single-domain libraries maintained by people to understand those domains extremely well. The hypothetical 'Boost.UniversalDataBaseClient' library could define concepts to which the implementations in (say) 'Boost.MySql' is invited to model. In this way, someone who is familiar with (say) Postgress or SQLite can easily bring a new implementation to the table.

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

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

On Tue, 7 Apr 2020 at 17:14, Robert Ramey via Boost wrote:

On 4/7/20 3:20 AM, Richard Hodges via Boost wrote:

...

On Tue, 7 Apr 2020 at 05:18, Robert Ramey via Boost < boost@lists.boost.org> wrote:

...

I'm going to come down on the side of those who suggest that you consider other database systems besides mysql.

One suggestion for you: Think BIG!!!

First, it would have to be a concept, not an interface. By definition, async primitives in Asio are templates, which cannot be virtual. I am doing this in C++17 and may lower the requirement to C++14/11 soon, so I don't have language support for concepts. I guess we can define concepts like DatabaseConnection, PreparedStatement, Resultset... If you guys think this is the best way to go, we can explore this path. But I guess these depend a little on what the higher-level library is aiming to do. I accept suggestions and advice here. What I don't want is to assume the responsibility for implementing all the backends in the world, because then the project is going to die before achieving anything useful. It has been an awful amount of work implementing (and testing) the MySQL backend - I don't think a single person alone is able to implement all the backends. From this point of view, I agree with Richard in making each backend a separate library. Unless somebody offers their help ;) BTW, I have implemented TLS connections and widened the authentication method support. I have also made available some online documentation - may provide a better understanding a library for you all. You can check it here: https://anarthal.github.io/boost-mysql-docs/index.html Thanks all!

-----Original Message----- From: Boost On Behalf Of Ruben Perez via Boost Sent: 7 April 2020 19:58 To: boost@lists.boost.org Cc: Ruben Perez Subject: Re: [boost] MySQL ASIO library

What I don't want is to assume the responsibility for implementing all the backends in the world, because then the project is going to die before achieving anything useful. It has been an awful amount of work implementing (and testing) the MySQL backend - I don't think a single person alone is able to implement all the backends. From this point of view, I agree with Richard in making each backend a separate library. Unless somebody offers their help ;)

There would certainly not be any expectation that you would single-handedly write and/or maintain more than on backend! I can sense that a lot of work has gone into this already 😊 If you are willing to take on the BIG challenge - something that would be MUCH more valuable long-term, you might like to consider seeking a 'partner' who would work to develop a second backend on their favorite SQL. (You might change the title to Boost.SQL too? Or Boost.SQL-ASIO? Since its USP seems to be ASIO.)

BTW, I have implemented TLS connections and widened the authentication method support. I have also made available some online documentation - may provide a better understanding a library for you all. You can check it here: https://anarthal.github.io/boost-mysql-docs/index.html

Really nice docs and convincing examples (to a non-expert). I feel I could dive into this and get going (but then fools step in where angels fear to tread!) Good luck Paul PS You really should make clear that this is not *yet* a Boost library, but I really hope it will become one. The attached logo might help, or a line of text saying 'Proposed for Boost'.

Hi all, I have been working in a proof-of-concept implementation of PostgreSQL. You can find it here: https://github.com/anarthal/postgres-asio It supports connections, queries and prepared statements with an API almost identical to the one proposed for Boost.MySQL. I have only implemented synchronous operations yet. I hope this helps prove that implementing other databases with a very similar API is feasible and helps push the MySQL library forward. Note that the Postgres library is just a proof of concept; it is very far from being a full implementation. At this point I lack the time to transform this library into a full implementation. If somebody wants to volunteer and progress this library into a full implementation (or just write something from scratch), I would appreciate it a lot - just message me. As always, any feedback is welcome. Regards, Ruben.

El lun., 13 abr. 2020 17:01, Paul A Bristow via Boost escribió:

😊

I hope some PostSQL (and indeed other SQL database(s)) aficionados will take on this (and other) task(s).

Ultimately, some formal tests using Boost.Test (or similar) will be needed to give confidence in the portability of the infrastructure.

Agree - testing is one of the biggest differences between the MySQL library and the Postgres proof-of-concept (and where most of my time using the library has been to).

Meanwhile this looks convincing (to a non-expert) - but how about experts?

I especially would like to know the opinion of those who were concerned about the extensibility of the MySQL implementation, as showing the viability of the interface proposed by my MySQL library was the main target of the Postgres proof of concept.

If you know someone (probably not on the Boost lists) who might have the expertise to take this on, then please forward this challenge to them.

Unfortunately I don't know anyone with a especially deep knowledge in any of the databases. I think someone with a deeper understanding on MySQL could be very valuable for the review process.

On Tue, 14 Apr 2020 at 14:16, Dominique Devienne via Boost < boost@lists.boost.org> wrote:

On Mon, Apr 13, 2020 at 1:52 PM Ruben Perez via Boost < boost@lists.boost.org> wrote:

...

I have been working in a proof-of-concept implementation of PostgreSQL. You can find it here: https://github.com/anarthal/postgres-asio

Hi. Great, I've been following this thread from afar, because first I'm using/learning ASIO myself, and second because I call databases as part of the processing I do in my ASIO-scheduled "tasks". But I'm a PostgreSQL user, not a MySQL one, thus my relative lack of interest until now :).

...

It supports connections, queries and prepared statements with an API almost identical to the one proposed for Boost.MySQL. I have only implemented synchronous operations yet. I hope this helps prove that implementing other databases with a very similar API is feasible and helps push the MySQL library forward.

I'm confused. What's the point of implementing synchronous at all rather than use libpq directly? libpq is "official" and synchronous, so the *only* reason I'd prefer your re-implementation of the protocol would be to gain async via ASIO.

Thanks for checking out! The point of that library was just a small proof of concept. There were a considerable amount of people concerned about the MySQL library being too MySQL specific. I just wanted to show that you *could* write a similar kind of library for other database. So at this moment you are right, there is absolutely no reason to choose my implementation. Just to be clear, the MySQL implementation is indeed complete and tested.

I haven't looked at the code, but I would also have assumed that any synchronuous API for a library that stated to the async, would be as a convenience layer on top of the async layer, doing the waiting/synchronization for you, to simplify things when async is not strictly necessary.

But what you wrote above seems to imply the contrary. Is the async API implemented on top of the sync API? Or you implement two separate APIs completely?

There is no async API at the moment. In the MySQL library both APIs are implemented separately, with most of the code factored out into functions/classes. But once you've got the synchronous implementation and API, you can "just write" the async one, as I keep the sync and async API consistent. So, for the proof of concept, I just did the sync one (which is easier).

...

Note that the Postgres library is just a proof of concept; it is very far from being a full implementation. At this point I lack the time to transform this library into a full implementation. If somebody wants to volunteer and progress this library into a full implementation (or just write something from scratch), I would appreciate it a lot - just message me.

It's hard to get traction on alternatives to official APIs (like libpq for PostgreSQL), w/o some pretty strong guarantees on testing. And testing with databases is complicated because of the need to access that remote DB; you can't easily mock a database reliably. I'm not sure how that would work within Boost. Are there precedent for Boost libraries that need "external" resources like full blown databases? Perhaps via VMs and/or Docker?

For MySQL, I have set up CI testing (Travis for Linux and OSX, Appveyor for Windows). I deploy the database (at this moment using the system package manager, I am considering switching to Docker containers to test a wider range of DB versions). Most of the tests run with a real database connection. At this moment, The MySQL library database tests cover most of the native types, both in text queries and statements, as well as the most used authentication methods. If I were to do a fully-fledged implementation for Postgres, I would implement a similar strategy. But doing so for MySQL has proven to be really time-consuming. I would prefer to push the MySQL library forward before moving into another one.

...

As always, any feedback is welcome.

A few things that came to my mind reading this thread, from my DB experience, is that the re-implement-the-low-level-protocol approach applies to very few DB systems, given that most commercial DBs use proprietary protocols (I know Oracle does for sure). Or the most used DB in the world (SQLite) doesn't have a network protocol at all. So that pretty much only leaves MySQL and PostgreSQL, no? So at the very least, both should be supported by a library that wants to gain widespread appeal IMHO. And from my POV, you chose the wrong one to start with :)

I have read that SQLite has an async API (using the filesystem asynchronous API), so maybe we could do something with it. I haven't explored that at all, TBH.

Another remark is that PostgreSQL has two modes, text (the default) and binary to send row data back and forth. We've tested both and went the most difficult route of using the binary route for performance reasons, which complicates things. Do you handle both modes at the protocol level?

No, the proof of concept just does text.

I'd conclude by saying that from my perspective, the one-true-SQL-library is a pipe dream when you want to maximum performance, or when wanting to avoid the lowest-common denominator approach.

I've used Array-binding in Oracle, and XMLTYPE, and low-level C/C++ binding of Spatial objects in OCI, and pretty much no SQL abstraction libraries can do these things. After doing lots of Oracle and SQLite, and now doing PostgreSQL, the differences are actually very large between these DBs, to the point a lots of the optimization done for one are not applicable to the other, and many things just don't translate at all. If one stays within a *tiny* subset of SQL and features, maybe... But any non-trivial sophisticated DB use, you quickly find the limits libraries that try to be multi-DB IMHO.

I completely agree with this. My view on this is that there should be a database for each library, so that you can expose the details specific to each database. (E.g. Portals, the binary protocol and execution row count for Postgres; Portals are not even a thing in MySQL). Then, if you want a multi-database library, you use the database-specific building blocks. However, there were people arguing that these building blocks may not be valuable enough to be part of Boost.

So in your case, what matters most is the async ASIO-based design, and having a framework to develop async DB client libraries, but not necessarily exposing exactly the same API for all of them. What matters is to be able to start processing rows and sending them downstream for further processing, *BEFORE* the whole result set made it client-side.

Interesting feature, we could do that in MySQL, too.

Maybe what I'm saying doesn't make sense, because I haven't studied your code and examples yet, but FWIW, these are the thoughts I had following this thread.

Thanks, --DD

All you said makes sense, thanks. Glad to know there would be interest for a Postgres library, too.

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

On Mar 4, 2020, at 16:24, Ruben Perez via Boost wrote:

Just to clarify, this is *not* a wrapper around the MySQL C API, it is an implementation of the MySQL protocol based in ASIO.

I would like to know if your software also manages the network protocol complexities? 1) Can one can talk to a server over a unix pipe or only over IP network? 2) In all DBI systems I have seen, example code starts with creating a connection object and then nobody explains to the casual user like me what is supposed to happen if the connection to the database is to be maintained over hours or days. Does your library manage this or are we still supposed to write all the connection checking and reconnection code manually? Thanks, Kostas ============================================================================================ Institute of Nuclear and Particle Physics NCSR Demokritos https://github.com/kotika/random https://github.com/kotika/random https://mixmax.hepforge.org https://mixmax.hepforge.org/

El mié., 4 mar. 2020 18:36, Richard Hodges via Boost escribió:

On Wed, 4 Mar 2020 at 17:48, Kostas Savvidis via Boost < boost@lists.boost.org> wrote:

...

...

On Mar 4, 2020, at 16:24, Ruben Perez via Boost

wrote:

...

Just to clarify, this is *not* a wrapper around the MySQL C API, it is

an

...

...

implementation of the MySQL protocol based in ASIO.

I would like to know if your software also manages the network protocol complexities? 1) Can one can talk to a server over a unix pipe or only over IP network?

From looking at the code, it seems that the MySQL protocol will work on any asio stream type, including posix streams (pipes). Setting up the stream is a (simple) Asio concern. The communication over that stream is a mysql concern.

That is correct. Anything being an Asio stream should just work. For TCP sockets there are tests and some typedefs to make things easier. I have in my TODO to add tests and typedefs for UNIX sockets and named pipes.

...

2) In all DBI systems I have seen, example code starts with creating a connection object and then nobody explains to the casual user like me what is supposed to happen if the connection to the database is to be maintained over hours or days. Does your library manage this or are we still supposed to write all the connection checking and reconnection code manually?

Asio streams do not auto-reconnect. I would imagine that if you need this, it would be logic you have to implement over the top of this library.

In practice, using the auto-reconnect feature of the mysql C library is problematic for anything other than the simplest of operations. Reconnected connections forget any state they contained.

Also true. At this point, if your stream has a communication error, you will have to handle the reconnect yourself. I can add some notes about that in the docs. That can be a feature for the future. However I see that the handling may depend too much on the application (time between retries, algorithm to compute retry time, max retries... Plus we would need a sort of a callback system to perform things like logging). So I think it may be feasible but not straightforward.

...

Thanks, Kostas

============================================================================================

...

Institute of Nuclear and Particle Physics NCSR Demokritos https://github.com/kotika/random https://github.com/kotika/random https://mixmax.hepforge.org https://mixmax.hepforge.org/

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

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

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