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