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

Subject: Re: [boost] sqlpp11: SQL for C++
From: Matthijs Möhlmann (matthijs_at_[hidden])
Date: 2013-11-12 04:00:49

On 11/12/13, 4:26 AM, Tim Keitt wrote:
> On Sat, Nov 9, 2013 at 4:03 PM, Roland Bock <rbock_at_[hidden]> wrote:
>
>> Hi,
>>
>> over the last four or five years I developed several SQL libraries for
>> C++. With C++11 I was finally able to create an SQL library that meets
>> my own expectations and requirements. It is being used in production, I
>> recently put it on github, and I would really like to hear from you guys
>> whether this is something that could be interesting for you personally
>> or for boost?
>>
>> https://github.com/rbock/sqlpp11
>> https://github.com/rbock/sqlpp11/wiki
>>
>> sqlpp11 is a template library representing an embedded domain specific
>> language (EDSL) that allows you to
>>
>>    * define types representing tables and columns,
>>    * construct type safe queries checked at compile time for syntax
>>      errors, type errors, name errors and even some semantic errors,
>>    * interpret results by iterating over query-specific structs with
>>      appropriately named and typed members.
>>
>> This results in several benefits, e.g.
>>
>>    * the library user operates comfortably on structs and functions,
>>    * the compiler reports many kinds of errors long before the code
>>      enters unit testing or production,
>>    * the library hides the gory details of string construction for
>>      queries and interpreting string based results returned by select
>>      calls. I.e. you don't need to use strings in query construction
>>      where you wouldn't use them in SQL and there is no need to use
>>      positional arguments or to parse strings when obtaining fields from
>>      a result row (the latter being true unless you do not know which
>>      columns to select at compile time).
>>
>> The library supports both static and dynamic queries. The former offers
>> greater benefit in terms of type and consistency checking. The latter
>> makes it easier to construct queries on the flight.
>>
>> Specific traits of databases (e.g. unsupported or non-standard features)
>> are known at compile time as well. This way, the compiler can tell the
>> developer at compile time if a query is not accepted by the database
>> (e.g. if a feature is missing). And the library can form the query in
>> the correct manner, for instance if the engine uses concat instead of
>> operator|| to concatenate strings.
>>
>> Two Examples:
>> =============
>> Static Select:
>> --------------
>> // selecting zero or more results, iterating over the results
>> for (const auto& row : db.run(
>>                          select(foo.name, foo.hasFun)
>>                          .from(foo)
>>                          .where(foo.id > 17 and foo.name.like("%bar%"))))
>> {
>>    if (row.name.is_null())
>>      std::cerr << "name will convert to empty string" << std::endl;
>>    std::string name = row.name;  // text fields are implicitly
>>                                     convertible to string
>>    bool hasFun = hasFun;         // bool fields are implicitly
>>                                     convertible to bool
>> }
>>
>> Dynamic Select:
>> ----------------
>> auto s = dynamic_select(db, tab.foo).dynamic_columns().from(tab);
>> if (userWantsBar)
>>    s.add_column(tab.bar);
>> for(const auto& row : run(s))
>> {
>>    std::cerr << "row.foo: " << row.foo;
>>    if (userWantsBar)
>>      std::cerr << "row.bar" << row.at("bar");
>>    std::cerr << std::endl;
>> };
>>
>>
>> Please let me know your questions/thoughts/suggestions/rants.
>> Contributions welcome, of course :-)
>>
> This is an interesting thread and I thought I'd comment.
>
> I am a pretty heavy user of postgresql/postgis (spatial extension) in my
> work. I wrote the first R package to access postgresql and contributed to
> the current R DBI package. I did a proof-of-concept (= not very pretty ;-)
> embedding of the Boost Graph Library in postgresql replacing local storage
> with prepared queries called on demand.
>
> I have to say when I look at this, I don't really want to learn another
> SQL. I am perfectly happy to send query strings to the database and let it
> parse them. I can debug these separate from my C++ code. I think for
> complex queries (recursive with anyone?) it would be quite difficult to get
> the C++ right.
>
> What I would really like is a mapping of binary cursors to iterator
> concepts + easy type-safe endian-aware customizable data conversion. But
> that's my bias. I've always liked mapping on-demand data to common
> interfaces.
>
> But your use cases are probably different and I can see how this would be
> very useful to some.
>
> THK
>
Hi,

Isn't libpqxx then not what you are looking for? I know its specifically 
written
for PostgreSQL.

http://pqxx.org/development/libpqxx/

Regards, Matthijs