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

Subject: Re: [boost] quince: queries in C++ expressions
From: Roland Bock (rbock_at_[hidden])
Date: 2014-07-16 08:07:11

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<Database,
select_t,
no_select_flag_list_t,
no_select_column_list_t,
no_from_t,
no_extra_tables_t,
no_where_t<true>,
no_group_by_t,
no_having_t,
no_order_by_t,
no_limit_t,
no_offset_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<T, ...> and
> boost::optional<T>, whenever it provides mappings for the Ts. E.g. a
> boost::optional<int32_t> 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<T1, boost::optional<T2>>.
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