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 11:29:55

[skipped a few items that seem to require no further immediate discussion]

On 2014-07-16 16:11, Michael Shepanski wrote:
> On 16/07/2014 10:07 PM, Roland Bock wrote: 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.
>
> 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".
Sorry, I misinterpreted some piece of documentation.

> 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.
What happens with the names when I do a self join with quince?
> 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.
Ok, cool :-)
>
>> 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?

Ooops, yes, of course :-)
>
> 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
That's a solution, but it also means that you might have to change
quince for each additional database dialect.

sqlpp11 aims to be completely vendor neutral, offering the tools to
adjust for dialects and non-standard features in the connector. I hope
that in the long run this will lead to more distributed efforts :-)

>
>> 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)
That part is common for both approaches :-)

I am not sure what will happen with the treat-container-as-databases
thing, but LINQ uses that with great success and sqlpp11 offers the
mechanisms to do the same.

>
>> 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.
>>
[...]
>
> Fwiw, the handling of optionals, in all situations, was one of the
> things I found most difficult.
Thanks for the heads up :-)
>
>
>>> 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
OK. You could use your own datatypes in sqlpp11 as long as they provide
the correct API (which is not documented yet, but rather lean and easy
to copy/paste from existing types).
>  
>> 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.)
I am living in Germany myself, but I hope the conference will be worth
the effort :-)

Best,

Roland