Re: [boost] SQL library with full embedded SQL-syntax
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... 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"); 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 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 (though SOCI no doubt has other features that my library is missing). 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. Regards, Phil.
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
Jeff Garland wrote:
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...
Indeed.
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.
Actually, SOCI already has the necessary framework for this. We have a Rowset<> class and we plan to add support for tuple as its template parameter (we haven't thought about multiple parameters there). For the moment it looks like this: http://soci.sourceforge.net/doc/statements.html#rowset And even without the Rowset, creating such functors that will internally translate to: sql << "insert ...", use(x), use(y), use(z); or: sql << "select ...", into(x), into(y), into(z): or any such combination (while preserving the type safety) doesn't seem to be much of a problem. It doesn't seem to be much necessary neither - unless we state clearly that our objective is to support absolutely every syntax sugar that any individual cares to invent. ;-) -- Maciej Sobczak : http://www.msobczak.com/ Programming : http://www.msobczak.com/prog/
Jeff Garland <jeff <at> crystalclearsoftware.com> writes: [...]
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++.
I still think that the main problem is a lack of layering: everybody is focused on covering everything from the direct calls to RDBMS API's to their own favorite high level mechanism, where a million different approaches are possible, each sensible in its own context. What I feel is needed first and foremost is a lower level library that only abstracts away the details of the underlying RDBMS technology and the complexity of how these things are usually achieved in C, e.g. not forget the one essential call (essential to the DB API, not to the programmer!) without which nothing works.
SOCI seems to be the best bet at the moment, but nothing is set in stone.
SOCI has to its merit maturity and activity, which means a lot. I still think it tries to do a bit too much for a single library and I'm still not convinced about some of its ABI. In my opinion it would be a valid addition to Boost, but in its present form it is not a library I'd propose for addition to the standard. Unfortunately my own attempt at providing an alternative working prototype is progressing way too slowly :-( Cheers, Nicola Musatti
On 15/03/07, Jeff Garland <jeff@crystalclearsoftware.com> wrote:
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++.
We do have a C++ database abstraction layer which supports Oracle(OCI) /DB2 (CLI) /SQL Server(OLEDB) on various platforms( Solaris, AIX, Windows and HP-UX) and is in use for more than 5 years. I would like to contribute to this effort.
Jeff
-- regards, Prashant Thakre
participants (5)
-
Jeff Garland -
Maciej Sobczak -
Nicola Musatti -
Phil Endecott -
Prashant Thakre