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

Subject: Re: [boost] sqlpp11: SQL for C++
From: Vicente J. Botet Escriba (vicente.botet_at_[hidden])
Date: 2013-11-12 13:14:15

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