Re: [boost] quince: queries in C++ expressions

On 2014-07-15 08:04, Michael Shepanski wrote:

Hello, boost!

I've released a library called "quince", which lets you use a relational database in C++ rather than SQL. So it provides an EDSL to wrap the SQL syntax, it converts between C++ types (including user-defined class types) and SQL column types, and it enforces type safety at compile-time.

I wanted to ask whether there would be an interest in this for boost, so I subscribed to this list today, and for the first time I discover Roland Bock's sqlpp11, which does all of those things too.

First of all I'd like to say: nice work Roland! The truth is, if I had seen sqlcpp11 earlier, I would not have had felt the need to write quince. Thanks, and vice versa :-)

I think it is quite important, that there are at least two EDSL libraries for SQL in C++. It makes it easier to discuss alternatives and see what else is possible/desirable. Looking forward to the discussion.

Nevertheless, I did write it, and so of course it has a different set of features. The full doco is at http://quince-lib.com, but let me quickly point out a few differences from sqlpp11.

1. If you want some class to be mapped into SQL data, quince lets you write this sort of thing:

struct point { float x; float y; }; QUINCE_MAP_CLASS(point, (x)(y))

extern database db; table<point> points(db, "points");

The macro QUINCE_MAP_CLASS uses a bit of Boost.Preprocessor magic and a bit of template metaprogramming to produce all the statically typed metadata that quince needs, regarding the user's type. There is no code generation step.

Adding such macros to sqlpp11 is possible, too, of course, and it would not be too hard, I guess. It just has to be done. I think that the macro variant is great, if you have a system which is to be installed from scratch and creates its required tables. The code generation approach is made for a different scenario: You create and maintain your database outside of your application code and evolve your software accordingly. Of course you want to make 100% sure that the database structure and your code are in sync. This is where the code generator approach is very helpful. You can use the database as master. Its structure dictates the table definitions in the C++ code. Obviously, this approach could also be implemented in quince. So I'd say this is a small difference which probably will be removed over time anyway since both approaches of defining table structures are valid.

2. Quince's EDSL looks somewhat different. This is my first day as a student of sqlpp11, so I'm not really qualified to define the distinctions, but I think part of it is that sqlpp11 exposes the concept of column aliases,

Yes, in sqlpp11, columns of tables have names, columns in result rows have names, parameters in prepared statements have names. They are represented as members in structs which are constructed at compile time. Thus, to access column "host" in a result row you write const std::string host = row.host; Personally, I like this much better than const std::string host = row<3>; since there might be 'protocol' and 'path' as well if the whole row represents a query (all strings) and later in the life time of the product, someone wants to add username and password. Then you have to rewrite those numbers to access members of a tuple. And the compiler cannot tell you if you mix up your numbers if the types are the same. It is much easier to get it right using names, IMHO. I therefore believe that the name approach is friendlier for maintenance.

where quince does not, and quince is preoccupied with the idea of compositionality, in a way that sqlpp11 is not (as far as I know). Anyway you can see an example of the quince EDSL here: http://quince-lib.com/getting_started.html . The way you can compose queries in quince is nice. The philosophy is quite different here, I'd say.

sqlpp11 builds an expression tree that represents an SQL expression. The EDSL is as close to SQL as I could manage. Of course, you can segment your code with functions and variables for sub queries, for instance, but in the end, you basically write SQL in C++. In quince you write expressions which are then transformed into SQL. The expressions have aspects which are very similar to SQL, but the way you can combine them is quite different. This allows to write some things much simpler, but the developer also has to give away control. Personally I like to have that control, since only the developer knows has all the information like which indexes can be used, number of rows, etc. I therefore like the idea, but I would want to have the complete-control version as well.

3. Just as sqlpp11 uses /connectors/, quince uses /backend libraries/, or /backends/. Currently I provide backends for PostgreSQL and sqlite. Unlike sqlpp11, however, I have not yet published the interface between quince and the backends. It's no secret --the whole lot is open source-- but I'm not yet ready to declare that I know all the customization points I'm going to need.

Apart from the different names, there seems to be one major difference, if I understood your code correctly: Different database vendors interpret the standard in different ways. Quince has virtual functions to handle dialect differences. As far as I can tell, that means that quince will report failures to translate a statement to a vendor's dialect at runtime. Please correct me if I am wrong, I haven't read/understood everything yet, of course. sqlpp11's connectors have two options to reflect specific dialects: a) They can provide specializations of the interpreter/serializer template for parts (or all) of the expression language. With this mechanism they can either interpret (sub-)expressions according to their dialect or they can use static asserts to reject certain constructs. b) They can extend the EDSL with features that are not in the standard. This is a rather new feature and not yet documented, but it is actually quite easy, IMHO. To get an impression, take a look at https://github.com/rbock/sqlpp11/blob/master/include/sqlpp11/select.h : template<typename Database> using blank_select_t = statement_t; With those two mechanisms, sqlpp11's connector libraries can tell the developer at runtime, if a feature is supported or not, including special features which might be available for one vendor only. In addition, the SQL expression can be rewritten at compile time not only to talk to string based database backends, but also to other databases. Thus sqlpp11 can be used as an SQL frontend to structs in std::vector, for instance, see https://github.com/rbock/sqlpp11-connector-stl Due to the virtual functions in quince's backends, I assume that this would be harder to do with quince and probably not with comparable performance since the expression would be reinterpreted at runtime. But I might certainly be missing something.

4. Quince provides mappings for std::tuple and boost::optional<T>, whenever it provides mappings for the Ts. E.g. a boost::optional is mapped as an INTEGER column with its NOT NULL constraint turned off. If you have a query q1 that produces results of type T1, and a query q2 that produces results of type T2, then q1.left_join(q2, some_predicate) produces results of type std::tuple.

As of now, sqlpp11 is not using boost::optional (there's been a long discussion about it). There are several use cases, one of which works wonderfully with boost::optional. So, I would like to add support for that, too. One thing which is currently stopping me is that I have not found a good way to bind to a boost::optional, because I think it would be pretty weird to use the address of the optional value and then change it without using the interface. But I need to research on that topic and ask questions on the list to figure it out. Calculating whether a result value can be NULL or not is nice. sqlpp11 does not do that yet, but it is on my TODO list :-)

5. An application can customize the representation of data in a particular database, or a particular table.

Not sure what that means. When used as an ORM?

6. Quince does not currently provide bulk insert. (It's on my to-do list.)

I've barely scratched the surface, but right now I have the impression that both products have their distinctive pluses.

BTW: I am going to give a talk about sqlpp11 at CppCon in September and at MeetingC++ in December. Maybe we can have a live discussion there, too? Best regards, Roland