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

Subject: Re: [boost] sqlpp11: SQL for C++
From: Roland Bock (rbock_at_[hidden])
Date: 2013-11-12 14:38:15

On 2013-11-12 19:14, Vicente J. Botet Escriba wrote:
> Le 12/11/13 09:13, Roland Bock a écrit :
>> 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" : "");
> This could not be a ct_string if userWantsJobInfo is not constexpr.
> But each of the parts could be a ct-string (Se below)
>> 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);
>> }
> What about something like
>
>   auto s = db.dynamic_query<"select person.* from person where
> person.active">();
>   if (userWantsJobInfo) {
>     s.combine<"select job.title from job where job.id ==
> person.jobId">();
>   }
>
> Here db.dynamic_query<>() would return the same type as your
> dynamic_select(db, ), but the analysis of  "select person.* from
> person where person.active" can be done statically.
> Then s.combine<> would combine the preceding query s, so it knows that
> there is a person table,  with the result of "select job.title from
> job where job.id == person.jobId".
>
> Best,
> Vicente
Sure, not impossible, but as stated before, I won't go there :-)

Best regards,

Roland