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

Subject: Re: [boost] sqlpp11: SQL for C++
From: Tim Keitt (tkeitt_at_[hidden])
Date: 2013-11-11 22:26:01

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

>
>
> Regards,
>
> Roland
>
> _______________________________________________
> Unsubscribe & other changes:
> http://lists.boost.org/mailman/listinfo.cgi/boost
>

-- 
http://www.keittlab.org/