Re: [boost] sqlpp11: SQL for C++

Subject: Re: [boost] sqlpp11: SQL for C++
From: Roland Bock (rbock_at_[hidden])
Date: 2013-11-12 03:13:55

On 2013-11-11 23:53, Vicente J. Botet Escriba wrote:
> Le 11/11/13 23:16, Roland Bock a écrit :
>> On 2013-11-11 22:41, Abel Sinkovics wrote:
>>>
[snip]
>> I see use cases for printf and regex for instance, where the user
>> provides a textual representation of something at compile time. In those
>> cases, compile time validation of strings is a wonderful tool, and I
>> have highest respect for it.
>>
>> But in the context of sqlpp11 I don't see how or why I should use it?
>> The library is constructing the query string at runtime. There is no
>> string to be validated at compile time. This is a major difference to
>> most other C++ SQL libraries.
>>
>>
> Hi,
>
> I think that what others are saying is that as your interface is a SQL
> on, maybe a textual interfaces is even closer to the SQL one ;-)
Sure, since SQL is text based :-)
> Thus this concrete example
>
> |for (const auto& row : db.run(select(foo.name, foo.hasFun)
>                               .from(foo)
>                               .where(foo.id > 17 and
> foo.name.like("%bar%"))))
>
> |
>
> |could be rewritten with something like|
> ||
>
> |
> | |for (const auto& row : db.run<"*select* name, hasFun *from* foo
> *where ( *id > 17 *and* name *like* "%bar%" )">()) //
>
> This need to take care of escaping '"' or a way to quote |||"%bar%"| :(
>
> |Clearly this interface can be learn quicker by a SQL developer and
> can be checked statically using something like Metaparser or the
> techniques used by Metaparser. Whether this is a good idea depends on
> * the performances at compile time and :(
> * the kind of errors the user would have :(
> * there is something that can be done with the first syntax that can
> not be done with the ct-string syntax or that can be easier to do.

>From the top of my head I can think of the following things that I would
consider extremely hard to do with ct string parsing:

_Typos and similar errors:_
ct-parsing can certainly be trained to parse a string, even with
placeholders. But what would be the result for the following string?
"select nam, hasVun, from bar where name > 17 and id like '%bar%'"
Can it detect errors here?

sqlpp11 on the other hand gets this:
select(foo.nam, foo.hasVun).from(bar).where(foo.name > 17 and
foo.id.like("%bar%"));
The current sqlpp11 will detect 4 errors here at compile time: Two typos
in the selected columns, two times comparing apples and oranges.
Starting with one of the next few iterations it will even detect that
you used the wrong table.

_Result types:_
For static queries, based on the names and types of the selected columns
you can access the fields of the result row as appropriately named
members with appropriate types, in this case:

std::string = row.name; // OK
bool hasFun = row.hasFun; // OK
bool wrong = row.name; // compile error

Could that be achieved with ct string parsing of the query? I wouldn't
know where to begin. But I am certainly not an expert in this area.

_Dynamic queries:_
sqlpp11 allows you to build queries dynamically way beyond substituting
parameters. Selected columns, required tables, where conditions etc can
be added at runtime. For example the user could query table Person and
have option to add related information from table Job to the result set.

With strings? Well you could construct it like

"select person.* " + (userWantsJobInfo ? ", job.title" : "") + " from
person " + (userWantsJobInfo ? ", job" + " where person.active = 1 " +
(userWantsJobInfo ? " and job.id = person.jobId" : "");

I hope I got that right. Can a ct string parser handle it in a useful
way? I honestly don't know, but I doubt it.

With sqlpp11, on the other hand, it is easy:
auto s = dynamic_select(db,
all_of(person)).from(person).dynamic_where(person.active == true);
if (userWantsJobInfo)
{
  s.add_columns(job.title);
  s.add_from(job);
  s.add_where(job.id == person.jobId);
}

sqlpp11 will simply build the query in the correct way.
>
> I can understand that you don't want to go this path as it it s
> complex and could take a lot of time to stabilize it.
> Anyway, if you can show some examples that you think can not be done
> parsing a ct-string, this could reinforce your current interface.
>
> Note that I like your EDSL interface, but a SQL EDSL interface text
> based could complement quite nicely the yours.
> Both approaches could be provided either by your library or by a
> library on top of yours.

Thanks for your input and summary.

I don't think I would go the road of compile time string parsing for
SQL, because personally I don't think it is worth the effort. But that
should not stop anybody, of course. I could be totally wrong. And as you
said, it might be a nice complement to the EDSL approach.

Best regards,

Roland