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

Subject: Re: [boost] quince: queries in C++ expressions
From: Roland Bock (rbock_at_[hidden])
Date: 2014-07-17 00:48:28

On 2014-07-17 03:08, Michael Shepanski wrote:
> On 17/07/2014 1:29 AM, Roland Bock wrote:
>
>> What happens with the names when I do a self join with quince?
>
> Ah.  Then there is a problem, and there is a solution, both described
> at http://quince-lib.com/queries/join/self_joins.html
The automatic, ever changing alias is a cool feature. Since the actual
alias is unknown to the user,  that requires the std::get<index> instead
of using  a name, but that is probably OK for joins.
>
>
>>> 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 :-)
>
> Yes.  Iiuc this is a benefit you get by including DB vendor in the
> static type of your objects, i.e. a statement_t for MySQL is a
> different type from a statement_t for sqlite -- yes?
Not exactly. In sqlpp11, the database type is only required for
serialization/interpretation. If the full statement structure is known
at compile time, the statement_t is independent of the database. Only if
you use parts which are decided at runtime (e.g. some expensive column X
is in the result row only if the user really, reallly wants it), then
these dynamic parts require to know the database and it is convenient to
do this by adding the database type to the complete statement.

Thus, in at least 90% of the statements I write myself, the database
template parameter of the statement is just void.

>
> For quince I decided I preferred I wanted my queries to be simply
> query<point>, query<employee> etc.  The price I pay is that use of
> vendor-inappropriate features is not caught until run-time.  I concede
> that.
>
>> 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.
>
> Would it be correct to say that you get this because sqlpp11 does not
> generate SQL, but rather it leaves that completely to the connector
> libraries, so they are free to do something completely different instead?
Yes, the connector library is completely free, but it does not have to
re-invent the wheel, too.

sqlpp11 has a default serializer implementation which generates standard
SQL. The connector libraries only have to specialize for the things that
are actually special. For instance, MySQL uses the concat function
instead of the || operator. Thus it has to specialize string concatenation.

This is the full serializer specialization for the MySQL connector:
https://github.com/rbock/sqlpp11-connector-mysql/blob/master/include/sqlpp11/mysql/serializer.h

sqlpp11's EDSL is not complete yet, so there probably will be a few more
entries for other special things, for instance iirc, MySQL does not
support WITH, so I would disable it in that file by adding a static
assert into a specialization for with_t.

>
> I have (so far) taken the approach of centralizing as much as possible
> of the SQL generation in quince itself, with the backend libraries
> only making a contribution where there are dialectal differences.
Same in sqlpp11 as explained above.

Of course, it is different if the connector does not operate on strings
like the sqlpp11-connector-stl. That one has to implement a full
interpreter. But of course, that is because its interpretation is
entirely different from the standard string serialization, so there is
no way around it.
>
> I have not thought about this until today, but I suppose it would be
> possible to change quince so that it always calls out to backends to
> do the whole job of generating SQL and executing it; and then a
> special-purpose backend could decide to do something else instead.
>
> If I took that approach, I think I would still want some common code
> to generate "normal" SQL, rather than repeating such code in
> quince_postgresql, quince_sqlite, etc.. Quince could provide such
> code, and it would be up to each backend library to decide whether to
> use it.
That's exactly what sqlpp11 is doing in a finely grained way, so that is
not a yes/no decision but a 'OK, I like most of the default, but I'll
change this and that detail' kind of approach for most connectors.

Cheers,

Roland