Re: [boost] SQL library with full embedded SQL-syntax

From: Jeff Garland (jeff_at_[hidden])
Date: 2007-03-15 11:31:16

Phil Endecott wrote:
>> Choosing C++ for a projects where databases are involved is probably 
>> rare
> 
> Well it's what I spend quite a lot of my time doing, and I find it a 
> good combination.  But then I often seem to be doing things differently 
> from everyone else...

I've found this to be mostly an advantage personally :-)

> In my case I have written a C++ binding for PostgreSQL's libpq which 
> allows me to define queries as functors:
> 
> Query<string,int> insert_thing(db, "insert into things(name,qty) values ($1,$2)");
> SingletonQuery<int, string> count_things(db, "select sum(qty) from 
> things where name=$1");
> 
> insert_thing("table",1);
> insert_thing("chair",4);
> int n_pens = count_things("pen");
> 

Very interesting...

> Note the template type parameters in the query declarations.  These 
> give the C++ types corresponding to the $n parameter placeholders in 
> the SQL, and are mapped to the "oids" (in postgresql-speak) of the 
> corresponding SQL types.  The queries are prepared, and when they are 
> run the parameters are passed to the database server in binary format.  
> You get compile-time checking that the types in the query invocations 
> match those in the declarations and run-time checking that the backend 
> is happy with those types.

I'd say that your interface is another use case for variadic templates.

http://www.generic-programming.org/~dgregor/cpp/variadic-templates.html

How many columns can Query support?

> I like this approach as it gives about as much type safety as is 
> possible and lets you do database accesses with essentially the same 
> syntax that you would use to access C++ data.  I prefer this to the way 
> that SOCI is used, where you always know that you're using SQL 

After a quick glance it looks like a nice interface. I believe it's basically 
at the same level of type safety as the SOCI interface which could probably be 
trivially wrapped to provide the functional view.

> (though  SOCI no doubt has other features that my library is missing).

I think the main thing about SOCI is that it has solves some big issues like 
support for multiple back-ends and user defined types.  These are essential 
requirements for standardized db access.

> There is more detail here:
> 
>    http://svn.chezphil.org/libpbe/trunk/doc/Database
> 
> I would be happy to describe my experiences further if anyone is 
> considering adding something along these lines to Boost.  My code is 
> currently GPL but I am flexible about that.

Careful what you ask for ;-)  It's been my dream for a couple years now to try 
and get all the folks interested in db access to work together to get a Boost 
library and then subsequently a standards proposal completed.  I don't think 
my cajoling has led to anything of substance -- various folks have their own 
solutions they are happy with: DTL, OTL, native interfaces, whatever.  The 
problem with this lack of standard in C++ is that since the efforts are 
divergent, there's not enough critical mass to build tools around any of the 
API's: which is frankly where, in my view, Java, Ruby, etc get much of their 
productivity advantages w.r.t. database access over C++.

SOCI seems to be the best bet at the moment, but nothing is set in stone. 
So, from my view, I'd love to see a detailed review from you of the SOCI 
interface -- advantages, disadvantages, etc.  Also, it might be interesting to 
try and port your interface to the SOCI core...I see no reason why your 
interface wouldn't be a possible alternative or addition to the existing SOCI 
setup.

Jeff