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

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

On 2013-11-11 19:00, Larry Evans wrote:
> On 11/11/13 09:44, Roland Bock wrote:
>> On 2013-11-11 16:16, Larry Evans wrote:
>>> On 11/11/13 09:01, Roland Bock wrote:
>>>> On 2013-11-11 15:36, Edward Diener wrote:
>>>>> On 11/11/2013 7:53 AM, Roland Bock wrote:
>>> [snip]
>>>>>> Result rows are currently to be returned from the connector's result
>>>>>> object as const char**, see database_api/api.h.
>>>>>
>>>>> You are kidding ? Is std::vector<std::string> too advanced ? Why C++
>>>>> programmers are still using C null-terminated strings I will never
>>>>> understand.
>>>> No kidding. The database client libraries I've used so far are C
>>>> libraries yielding char**. That is why the current connector interface
>>>> also uses const char**. BTW: These are typically not
>>>> null-terminated but
>>>> are delivered with a size_t* or similar to inform about the length...
>>>>
>>>> But:
>>>>
>>>>    1. As a user of the library you don't get in touch with those. The
>>>>       members of a row are NOT char*, of course. You get integers as
>>>>       integers, floats as floats and of course you get text as
>>>>       std::string. I wouldn't dare to use char* there ;-)
>>>
>>> Since the return values from the database are char**, these must be
>>> parsed and converted into a vector of, for example, TabSample,
>>> where TabSample is from:
>>>
>>>    https://github.com/rbock/sqlpp11/blob/master/tests/TabSample.h
>>>
>>> Sounds like a job for spirit.  However, I guess this option was
>>> rejected for reasons similar to why proto was rejected for
>>> creating the sql string to the dbms, as expressed in your
>>> reply to my other post:
>>>
>>> http://article.gmane.org/gmane.comp.lib.boost.devel/246117
>>>
>>> Is that right?
>>
>> First, the result is not a sequence of TabSample. The result row type is
>> created based on what you select. That could be analogous to TabSample's
>> rows, but you could select fewer or more columns or different orders...
>>
>> The current status is that the members of the result row parse "their"
>> part of the char**, based on the position and the type of the selected
>> column, see for instance:
>>
>> https://github.com/rbock/sqlpp11/blob/master/include/sqlpp11/integral.h
>>
>> Look for struct _result_entry_t, currently at line 51.
>>
>> There are respective classes for floating_point, text and bool.
>>
>>
>> These classes could certainly be optimized. Spirit might be able to help
>> here, but yes, you are right, I fear the compile times. In our code base
>> we have hundreds of queries to compile. So compile time is an issue for
>> continuous integration.
>>
>
> After some more thought, I think Spirit (really spirit's qi) would be
> overkill since really only parsing primitive types (e.g. int, float,
> string) are needed.
>
> I ran the SelectTest and got:
> -{--cut here--
> ------------------------
> SELECT  tab_sample.alpha,tab_sample.beta,tab_sample.gamma FROM
> tab_sample WHERE ((tab_sample.alpha>7)AND(tab_sample.alpha=ANY(SELECT
> tab_sample.alpha FROM tab_sample WHERE (tab_sample.alpha<3)))) LIMIT
> 3------------------------
> SELECT
> tab_sample.alpha(tab_sample.beta||'hallenhalma')(tab_sample.beta||'hallenhalma')tab_sample.alpha
> 3
> (SELECT DISTINCT STRAIGHT_JOIN left.alpha,left.beta,(SELECT  right.a
> FROM (SELECT  (tab_sample.gamma) AS a FROM tab_sample WHERE
> (tab_sample.gamma=1)) AS right) FROM (tab_sample) AS left,(SELECT
> (tab_sample.gamma) AS a FROM tab_sample WHERE (tab_sample.gamma=1)) AS
> right WHERE ((tab_sample.beta='hello world')ANDSELECT 
> tab_sample.gamma FROM tab_sample) GROUP BY left.gamma,right.a HAVING
> (right.a!=1) ORDER BY left.beta ASC LIMIT 17 OFFSET 3) AS a
> Compilation finished at Mon Nov 11 11:08:27
> -}--cut here--
>
> It would really help if the outputs were prefixed with some title.
> That way, one could look where the title in the source is output,
> and easily find the output from the code.
>
> I assume that the output bracketed by the:
>   ------------
> lines are produced by lines 305-316:
> -{--cut here--
>     {
>         auto s = dynamic_select(db,
> all_of(t)).dynamic_from().dynamic_where().dynamic_limit().dynamic_offset();
>         s = s.add_from(t);
>         s = s.add_where(t.alpha > 7 and t.alpha ==
> any(select(t.alpha).from(t).where(t.alpha < 3)));
>         s = s.set_limit(30);
>         s = s.set_limit(3);
>         std::cerr << "------------------------\n";
>         s.serialize(std::cerr, db);
>         std::cerr << "------------------------\n";
>         using T = decltype(s);
>         static_assert(sqlpp::is_regular<T>::value, "type requirement");
> -}--cut here--
> Is there an actual run against a real database somewhere in the tests?
> The 2 .cpp files I checked (SelectTest.cpp and UpdateTest.cpp) both
> use db of type MockDb, which doesn't seem to be connected to any real
> database.
> [snip]
>
> -Larry

Hi Larry,

those tests are really just tests, mostly compile time test to ensure
that expressions yield the right types. I will have to add quite a few
additional ones which check the generated strings.

There are two connector libraries (MySQL/MariaDb and Sqlite3) listed here:

https://github.com/rbock/sqlpp11#requirements

They also contain tests, and those tests actually do something, even
though it is not much :-)

Based on that it should be rather simple to create a connector library
for other databases.

What environment (OS, compiler) are you using, btw?

Cheers,

Roland