Re: [boost] sqlpp11, 3rd iteration

Subject: Re: [boost] sqlpp11, 3rd iteration
From: Adam Wulkiewicz (adam.wulkiewicz_at_[hidden])
Date: 2014-08-21 16:50:01

Roland Bock wrote:
> On 2014-08-21 18:45, Adam Wulkiewicz wrote:
>> 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 :-)
Great!
>
> 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
Yes, there is also possible to describe geometries using WKB (binary) 
format. And AFAIK some databeses uses slightly modified/extended version.

>> 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.
Great, so the extension like this could be included if needed or even be 
a standalone library.
>> 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.

Let me clarify. Let's say that I supported this ST_CoveredBy() function 
for both PostGIS and MySQL. Let's say that in version 5.9 MySQL will 
support this function. So the library should work with some future 
release. But I'm using sqlpp with MySQL 5.7. What will be the result of 
performing this "unsupported" query? An exception? AFAIU ideally it 
should be a compile-time error?

<snip>

> Hope this helps. Are you asking for a specific project? Or just out of
> curiosity?
I'm curious. I'm a contributor at Boost.Geometry and this is just a 
problem from my domain.
I'm wondering what would be needed to use sqlpp in a 
GIS/Geometry-related application. E.g. to load some geometrical objects 
from some database into objects of types adapted to Boost.Geometry 
concepts (polygon, linestring, etc.), do something with them and maybe 
write the results back or display them, etc. Something like that.

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

Thanks for the answers and tips!

Regards,
Adam