Database Abstraction Layer

[Richard Dosselmann]

The following is a prospective interface of a (future) C++ database abstraction layer. This interface is inspired by proposal N3886 of Johann Anhofer.

This interface is made up of 2 (primary) classes. The first, namely connection, allows a user to connect to a particular database. A user can then begin, rollback and commit a transaction. A convenience function, namely exec(), is included in order to perform (simple) operations that do not return a result set, such as creating a table. The second class, named statement, allows a user to create and process more involved statements, such as those that bind parameters and extract individual the values of a result set. This class uses the (C++17) any data type to manipulate parameters and values of arbitrary data types.

We welcome all comments, feedback, revisions, enhancements, additions, deletions, etc. to this interface. Ultimately, we are seeking a candidate to present any future proposal stemming from this interface.

/*
 * References:
 * Johann Anhofer N3886, dlib.net, SQLAPI++, SOCI, POCO, MFC (CDatabase, CRecordset), Java JDBC, .NET Core 2.2
 */

#ifndef DATABASE_H
#define DATABASE_H

#include <any>
#include <stdexcept>
#include <string>

namespace std {
namespace database {

class connection_exception : public std::runtime_error { // inspired by N3886 of Johann Anhofer
public:
    explicit connection_exception(const std::string& what_arg) : std::runtime_error(what_arg) { }
    explicit connection_exception(const char* what_arg) : std::runtime_error(what_arg) { }
};

class statement_exception : public std::runtime_error { // inspired by N3886 of Johann Anhofer
public:
    explicit statement_exception(const std::string& what_arg) : std::runtime_error(what_arg) { }
    explicit statement_exception(const char* what_arg) : std::runtime_error(what_arg) { }
};

class connection {
public:
    connection() noexcept;
    explicit connection(const std::string& conn); // conn is database, username and password
    ~connection() noexcept;

    void open(const std::string& conn);
    void close();
    constexpr bool is_open() const noexcept;
    constexpr std::string conn() const noexcept;

    void begin_transaction();
    void commit();
    void rollback();

    void exec(const std::string& sql_statement); // convenience func., does not produce a result set
};

class statement {
public:
    explicit statement(connection& db);   
    statement(connection& db, const std::string& sql_statement);
    ~statement() noexcept;

   
    void sql_statement(const std::string& sql_statement);
    constexpr std::string sql_statement() const noexcept;

    void bind(const std::string &name, const std::any& value);
    void bind(int index, const std::any& value);
    statement& operator<<(const std::any& value); // bind parameters (in order)
    constexpr int param_count() const noexcept;
    constexpr int param_index(const std::string &name) const; // name -> index
    constexpr std::string param_name(int index) const; // index -> name
    constexpr std::any param_value(const std::string &name) const;
    constexpr std::any param_value(int index) const;
    constexpr bool is_param_value_null(const std::string &name) const;
    constexpr bool is_param_value_null(int pos) const;
 
    void prepare();
    constexpr bool is_prepared() const noexcept;
 
    void exec();

   
    constexpr bool is_eof() const noexcept;
    void move_first();
    void move_next();
    constexpr int field_count() const noexcept;
    constexpr int field_index(const std::string &name) const; // name -> index
    constexpr std::string field_name(int index) const; // index -> name
    constexpr std::any field_value(const std::string &name) const;
    constexpr std::any field_value(int index) const;
    constexpr bool is_field_value_null(const std::string &name) const;
    constexpr bool is_field_value_null(int index) const;
};

} // namespace database
} // namespace std

#endif

[Mateusz Loskot]

On Sun, 27 Jan 2019 at 21:12, Richard Dosselmann <doss...@cs.uregina.ca> wrote:
>
> The following is a prospective interface of a (future) C++ database abstraction layer. This interface is inspired by proposal N3886 of Johann Anhofer.
>
> This interface is made up of 2 (primary) classes.

>[...]

>
> class connection {
> public:
> connection() noexcept;
> explicit connection(const std::string& conn); // conn is database, username and password
> ~connection() noexcept;
>
> void open(const std::string& conn);
> void close();
> constexpr bool is_open() const noexcept;
> constexpr std::string conn() const noexcept;

What the conn does?
Why abbreviated name?

> void exec(const std::string& sql_statement); // convenience func., does not produce a result set

Why abbreviated name?

Why full names and abbreviated names are mixed?

Best regards,
--
Mateusz Loskot, http://mateusz.loskot.net

[Richard Dosselmann]

Good points! I will choose more appropriate names, such as names similar to those of other languages so as to ensure that they are familiar to programmers experienced in those other languages. Feel free to suggest alternate names for the various items of this code.

[Mateusz Loskot]

The parallel to Unix API is a random shot to me, if not misconceived.
An average Joe accessing using the proposed API to access a database
will more likely be familiar with Python DB-API or Perl DBI or similar.

For public members of classes, I'd use whole words, i.e. execute

Regards,
Mateusz

> --
> You received this message because you are subscribed to the Google Groups "SG11 - Databases" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to databases+...@isocpp.org.
> To post to this group, send email to data...@isocpp.org.
> Visit this group at https://groups.google.com/a/isocpp.org/group/databases/.
> For more options, visit https://groups.google.com/a/isocpp.org/d/optout.

[Roland Bock]

Hi,

Nice to see movement on this list!

However, I wonder if conjuring up an interface will work? It seems to me
that you would need to discuss a few fundamental design decisions first.

string-based queries vs. expressions (like sqlpp11)
flexibility vs. constraints
Compile-time checks vs. runtime-checks
Compile-time performance vs. runtime performance
What about connection pools?

How are various databases backends connected? Are there requirements for
their interfaces as well? I am asking because you probably do not want
to add MySQL and PostgreSQL and Oracle and a hundred others to the
standard, right? So you need to describe how that integration is
supposed to work.

I added some inline comments to the interface, too.

On 27/01/2019 21.12, Richard Dosselmann wrote:
> /*
>  * References:
>  * Johann Anhofer N3886, dlib.net, SQLAPI++, SOCI, POCO, MFC (CDatabase,
> CRecordset), Java JDBC, .NET Core 2.2
>  */
>
> #ifndef DATABASE_H
> #define DATABASE_H
>
> #include <any>
> #include <stdexcept>
> #include <string>
>
> namespace std {
> namespace database {
>
> class connection_exception : public std::runtime_error { // inspired by

> class statement_exception : public std::runtime_error { // inspired by

At this point in time, I would not worry about the exception types yet.

> class connection {
> public:
>     connection() noexcept;
>     explicit connection(const std::string& conn); // conn is database,
> username and password

And host and protocol and vendor and path to ssl certificate and many
other things potentially. Everything serialized into a string and then
de-serialized by the code that actually does something with it?

>     ~connection() noexcept;
>
>     void open(const std::string& conn);

Why do I need to pass `conn` again, here? What happens if conn is
different from what I used in the constructor? Can I choose a different
backend even?
What is the benefit of calling open and close instead of constructing
and destroying?

>     void close();
>     constexpr bool is_open() const noexcept;
>     constexpr std::string conn() const noexcept;
>
>     void begin_transaction();
>     void commit();
>     void rollback();
>
>     void exec(const std::string& sql_statement); // convenience func.,
> does not produce a result set

IF you want to provide this convenience function, then it should be a
free function, taking a connection and an sql_statement

> };

>
> class statement {
> public:
>     explicit statement(connection& db);   
>     statement(connection& db, const std::string& sql_statement);
>     ~statement() noexcept;
>    
>     void sql_statement(const std::string& sql_statement);
>     constexpr std::string sql_statement() const noexcept;

Why would I want to change the sql_statement? What is the benefit over
just creating a new one?
What happens with bound values?

>
>     void bind(const std::string &name, const std::any& value);
>     void bind(int index, const std::any& value);

Can I mix index and name? Is that valid?

>     statement& operator<<(const std::any& value); // bind parameters (in
> order)
>     constexpr int param_count() const noexcept;

The constructor is not constexpr, binding values certainly isn't either.
How can param_count be constexpr? Same with several other functions below.

>     constexpr int param_index(const std::string &name) const; // name ->
> index
>     constexpr std::string param_name(int index) const; // index -> name

How is this supposed to work? When I add values by index, there is no
name, is there?

>     constexpr std::any param_value(const std::string &name) const;
>     constexpr std::any param_value(int index) const;
>     constexpr bool is_param_value_null(const std::string &name) const;
>     constexpr bool is_param_value_null(int pos) const;

Is there a semantic difference between index and pos?
How do I indicate NULL in a parameter value?

>  
>     void prepare();
>     constexpr bool is_prepared() const noexcept;
>  
>     void exec();
>    
>     constexpr bool is_eof() const noexcept;
>     void move_first();
>     void move_next();

With this interface I cannot seem to iterate over a range of results.

>     constexpr int field_count() const noexcept;
>     constexpr int field_index(const std::string &name) const; // name ->
> index
>     constexpr std::string field_name(int index) const; // index -> name
>     constexpr std::any field_value(const std::string &name) const;
>     constexpr std::any field_value(int index) const;

Are you sure you want to yield the value by value not by reference?

>     constexpr bool is_field_value_null(const std::string &name) const;
>     constexpr bool is_field_value_null(int index) const;

In the parameters, it seems possible to indicate NULL by passing any.
Why do I need extra functions to check for any, here?

> };


Just my 2ct.


Best,

Roland


--
[](){};

[Bill Seymour]

I agree that it's good to see something happening.

I'm resuming work on this, too. I've attached
documentation for what I'm thinking about.

As I say near the end of the paper, it's not WG21's
business to say how to communicate with databases;
that's SC32 WG3's job; so anything proposed will
at least need to not conflict with ISO/IEC 9075.

There should also be a working model to prove
that what's proposed is implementable.

--Bill Seymour

[Richard Dosselmann]

Amazing Bill! This is a positive move forward. Given the important nature of this topic, it is great to see such progress.

I am glad to have had some feedback and discussion, including revisions to a prospective interface. My goal is a compact and simple design. A lot of questions remain yes. Would be it helpful to post shared documents online to be edited?

And I really like Bill's rational number class. Coming from a mathematical background, this is a wonderful feature.

[Bill Seymour]

Thanks for the kind words.

My day job is in the old "business data processing" domain.
Most of my work is in Java (often very frustrating), and I
can't reasonably argue for C++ precisely because there's
no really good standard way to communicate with relational
databases. I hope to be able to do something to change that.

<aside>
The rational stuff will be published in a Technical Specification
that WG21's Numerics Study Group is putting together "as we
speak". I expect we'll have a draft document come out of
the meeting in Hawaii later this month.
</aside>

--Bill


On Sat, Feb 2, 2019 at 2:43 PM Richard Dosselmann

[Richard Dosselmann]

What are the feelings about using the "any" data type? I understand that this deviates from the traditional use of templates, but am curious if it would be a good approach.