Re: [boost] sqlpp11, 3rd iteration

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: https://github.com/rbock/sqlpp11
>> Doku: https://github.com/rbock/sqlpp11/wiki (not at all formal yet)
>>
>> I am hoping for more feedback both here and live at CppCon
>> (http://sched.co/1r4lue3)
>>
>
> 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(streets.name)
>     .from(streets)
>     .where( intersects(streets.geometry, some_polygon)  )
>
> or
>
> select(streets.name)
>     .from(streets)
>     .where( streets.geometry.within(from_wkt("POLYGON((0 0,10 0,10
> 10,0 10,0 0))"))  )
>
> or
>
> select(streets.name)
>     .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
https://github.com/rbock/sqlpp11-connector-stl

> 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,
sqlpp::tag::expression>;
    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
https://github.com/rbock/sqlpp11-connector-mysql/blob/master/include/sqlpp11/mysql/serializer.h
for different serialization than standard and the serializer of sqlite3
at
https://github.com/rbock/sqlpp11-connector-sqlite3/blob/master/include/sqlpp11/sqlite3/serializer.h
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
curiosity?

FYI: There is one more way to extend sqlpp11 queries: You can add
additional clauses or change the interface of clauses. For instance,
http://docs.oracle.com/cd/B19306_01/server.102/b14200/queries003.htm
uses a CONNECT clause in SELECT.

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

> Referenes:
> [1] http://www.opengeospatial.org/standards/sfa
> [2] http://www.opengeospatial.org/standards/sfs
> [3] http://en.wikipedia.org/wiki/Well-known_text
> [4] http://en.wikipedia.org/wiki/DE-9IM
>
Thanks for the links :-)

Regards,

Roland