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

Subject: Re: [boost] quince: queries in C++ expressions
From: Michael Shepanski (mps_at_[hidden])
Date: 2014-07-16 10:11:29

On 16/07/2014 10:07 PM, Roland Bock wrote:
>
> 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.

Great!

>> The macro QUINCE_MAP_CLASS
> 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.

I agree that "Of course you want to make 100% sure that the database 
structure and your code are in sync."  Perhaps the difference between 
the two approaches can be put this way:

- Quince assumes that, in any schema evolution, the application software 
will take the lead, by creating quince::table objects that represent the 
*desired* structure, and then using the techniques in 
http://quince-lib.com/tables/table_alteration.html to bring the physical 
tables into conformity.

- Sqlpp11 assumes that the physical tables take the lead, and the 
software follows.

The benefit of quince's approach is that it lets you work in C++, even 
for upgrading.  :-)

>> 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.

I think it is safe to say that nobody likes to say "row<3>", and neither 
sqlpp11 nor quince asks its users to say that.  Can we take that off the 
table?

When you execute a query in quince, it uses the query's value mapper to 
convert complete rows into their C++ equivalent type -- which could be 
simple or complex, depending on the query's value type.  So the 
application code that receives results looks like this (from 
http://quince-lib.com/queries/executing.html):

     for(const  point  &p:  points.where(points->x  >  4).fetch_size(50))
          std::cout  <<  p.y  <<  std::endl;

As you can see, the access is via meaningful names, just as with your 
"row.host".

What I was getting at, when I said "sqlpp11 exposes the concept of 
column aliases", is that --if I understand correctly-- sqlpp11 puts the 
user in control of when column aliases are created in the SQL. Quince 
takes that level of control away from the user.

> 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.

I agree with this characterization of the difference.

I gave quite a bit of thought to this, and tried to find the "sweet 
spot" between control and automation. Before I came up with the name 
"quince".  I was looking for catchy names that included the word 
"Goldilocks" --as in "The Goldilocks Zone"-- for this very reason. :)

> 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.

http://quince-lib.com/queries/compositionality.html has some discussion 
of the aspects of control that quince takes away.

If you want to be sure that the sql will only sort based on columns that 
you know you've indexed, or if you want to avoid inspecting every row in 
a table that you know will be huge, then you have nothing to fear: 
quince leaves you in control of those important things.

>> 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.

Yes, that is correct.

> 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.

You meant to say "at compile time" just now, yes?

Indeed, this is something quince cannot do.  In order to do it, I think 
I would need to make database type a static property of each query, each 
mapper, etc. That would be significant and sweeping complication.

I suspect that, in 99% of cases, an application only deals with 
databases from one vendor. A user who only wants to deal with sqlite 
would be better served by a version of quince that didn't provide 
postgresql-specific features at all, and didn't declare virtual methods 
to implement them.  Perhaps a pragmatic solution would be to provide 
that sort of quince, by means of preprocessor switches. =-O

> 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.

This is not something I have considered.  Whenever I have thought about 
uniform treatment of queries and containers, I have thought the opposite 
way: making a query act like a container 
(http://quince-lib.com/getting_started/executing_the_query.html)

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

Fwiw, the handling of optionals, in all situations, was one of the 
things I found most difficult.

>> 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?

This is explained in http://quince-lib.com/custom.html

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

That would be great, but at this stage I'd say it's unlikely I'll be at 
either conference.
I'll let you know if anything changes.  (I'm in Australia, btw.)

Cheers,
--- Michael