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

Subject: Re: [boost] sqlpp11: SQL for C++
From: Larry Evans (cppljevans_at_[hidden])
Date: 2013-11-11 13:00:29

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