Boost logo

Boost :

Subject: Re: [boost] sqlpp11, 3rd iteration
From: Roland Bock (rbock_at_[hidden])
Date: 2014-08-21 15:31:59

On 2014-08-21 18:45, Adam Wulkiewicz wrote:
> Roland Bock wrote:
>> Dear Boosters,
>> Quite a bit has happened since last I reported about sqlpp11 in this
>> forum [1,2]. I have incorporated a lot of the feedback you gave me,
>> hopefully bringing the library closer to a reviewable state.
>> Source:
>> Doku: (not at all formal yet)
>> I am hoping for more feedback both here and live at CppCon
>> (
> I didn't mention it earlier but your library looks great!
Thank you :-)
> In my work I musn't write code that uses such functionality but as a
> C++ developer I appreciate that I could handle queries, errors, etc.
> at the C++ level.
> Which brings me to a question about the SQL extensions. In order to
> support such extensions, e.g. SQL/MM or SQL/SFA [1][2] which specifies
> a storage, access model, operations, etc. for handling of
> geometrical/geographical data, a user would be forced to extend your
> library with additional functions/methods/structures to e.g. perform a
> query:
> select(
> .from(streets)
> .where( intersects(streets.geometry, some_polygon) )
> or
> select(
> .from(streets)
> .where( streets.geometry.within(from_wkt("POLYGON((0 0,10 0,10
> 10,0 10,0 0))")) )
> or
> select(
> .from(streets)
> .where( streets.geometry.distance(some_point) < 100 )
> or something like that. For more info see: WKT [3], spatial relations
> [4].
> How simple/complicated would it be (for the user) to add the support
> for such extensions currently?

This is actually all very simple (famous last words of a library
writer). But I invested quite some time to be able to say this without
hesitation :-)

For the things above, you would need

  * A few more "sql" types like point, polygon, linestring.
      o You need to write the respective classes for representing
        values, parameters (if you want those in prepared statements)
        and result fields, see for instance integral.h.
      o The value classes also contain the specific member functions,
        like the operators for integrals or the like() method for texts.
      o That is simple. The worst part is to figure out the interface
        you want these types to have.
      o Your connector library requires a few more functions to bind
        parameters and yield results of these types
      o The interface is simple and I assume that the backend provides
        everything necessary for the implementation to be simple, too.
  * A few free functions like the intersect function in your first example.
  * A few template classes to represent nodes in the expression tree,
    for instance and intersect_t, which is the return value of the
    intersect method and its parameters.
      o Those are really simple :-)
  * specializations of the serializer or interpreter for the nodes
      o The serializer simply writes the node into the context
        (typically an ostream).
      o That should be simple if your backend expects a query in the
        form of a string.

The last part becomes more complex, if your backend does not expect a
string representation. In that case, it is still conceptually simple:
You just walk the expression tree and transform it in any way you like.
For a compile-time transformation, see the interpreter at

> Would the user be forced to implement it in the library directly and
> e.g. always include it with the rest of the library? Or would it be
> possible to implement it as a separate addon that could be optionally
> included?
The latter. sqlpp11 uses value type and tags. If your class says it is
an sqlpp expression with a boolean value type, then it welcome wherever
a boolean sql expression is required.

struct sample
    using _traits = sqlpp::make_traits<sqlpp::boolean,
    using _recursive_traits = sqlpp::make_recursive_traits<>;

This is a boolean expression as far as sqlpp1 is concerned.

> In addition to the above, would it be possible to map the same C++
> functions/methods to different SQL functions for different database
> servers? In various servers there are non-standard extensions which
> may have various SQL functions names or different number of
> parameters, etc. E.g. related to the above example, one server can
> support SQL/MM defining operation ST_Intersects() and other one
> SQL/SFA defining Intersects().
Again, no problem at all.

You can use partial specialization in the serializer/interpreter to
create different ways of serialization or other transformation for
individual databases. See for instance the serializer of mysql at
for different serialization than standard and the serializer of sqlite3
for compile-time disabled SQL features.

> Assuming that various servers may support various functionalities on
> which layer of sqlpp this support should be checked and the error
> returned if necessary?

As explained above, this kind of adjustments could be done in the
serializer/interpreter. It would produce static_asserts at compile time,
typically (that's the sqlpp11 way), but it is up to you in the end.

Your database connector has a serialization context. You can give it any
kind of information or throw exceptions, up to you. I would use
static_asserts to indicate missing support, but see also below.

> E.g. ST_CoveredBy() isn't defined in the SQL/MM standard but it can be
> used in PostgreSQL/PostGIS, but currently not in MySQL (version 5.7).
> Or should all servers support the same functionalities?

Nah, that would be quite annoying. You would constrain yourself to the
minimum set. I would use the partial specialization as described.

> If such errors was reported at compile-time then AFAIU specific
> version of the library (or just a lowest level connector?) would be
> forced to work with specific version of a server?

I am not sure I fully understand your question.

You could write an extension for sqlpp11, say sqlpp11-spatial. It would
probably live in its own namespace in sqlpp. This would be a vendor
neutral library, like sqlpp11. And you would write new connector
libraries or extend existing ones.

In your code you would then use sqlpp11, sqlpp11-spatial and one of
those connectors.

If you want to choose the database at runtime, you will have a shared
connector library and you will have to turn those static_asserts in the
serializer into exceptions for instance.

Hope this helps. Are you asking for a specific project? Or just out of

FYI: There is one more way to extend sqlpp11 queries: You can add
additional clauses or change the interface of clauses. For instance,
uses a CONNECT clause in SELECT.

That also isn't very hard, but I would not call it really simple either :-)

> Referenes:
> [1]
> [2]
> [3]
> [4]
Thanks for the links :-)



Boost list run by bdawes at, gregod at, cpdaniel at, john at