On Tue, Sep 14, 2010 at 8:26 AM, Roland Bock <rbock@eudoxos.de> wrote:
currently I am working on a set of header files which are supposed to make the life of C++ developers easier when in comes to interacting with databases (based on SQL). Goals are:
* Stay close to SQL syntax * Do as much type checking as possible
Static type checking is always a plus. I tried going that route too, but found it a bit cumbersome (and my C++ lacking too ;) One of the problem with trying to generate queries like this is also the complexity of the SQL language itself, with nested queries, and all the various extensions and idiosyncrasies of the different SQL vendors. I've lurked on the Oracle forums for a few months, and it's open my eyes to the complexity of SQL (the Oracle dialect at least).
* prevent oversights and typical security holes, e.g. by forgetting to escape strings
Using bind values also avoids such issues, AFAIK
The basic idea is, that given a table definition, it should be possible to * offer a lot of checks at compile time for building a query * make it hard to misinterpret the results
The few C++ libraries I've seen in this area take a less ambitious approach, and typically provide instead an easy way to pass typed C++ values as SQL bind values, and get back the records as typed tuples for examples. The SQL text must still be correct, and will be parsed and type checked only at runtime by the DB, but in practice, with unit testing, I find that it works fairly well. A good and established library already mentioned on this list in this domain is http://soci.sourceforge.net/, which uses modern C++ and Boost. With Steven Watanabe's help from this list, I used a little fusion + tuple magic to easily bind and fetch records in a similar manner with SQLite, but that only checks arity of the select clause, and that enough bind values are provided, and it's not checking the values provided or gotten are of the right type at compile time. From my experience though, after having tried the statically type-checked approach as well, I found such an approach to be flexible and "good enough".
Until now, I have been concentrating on table definitions and select statements.
If you go that route, you'll need a way to generate the static C++ definition of the tables from existing Schemas, otherwise maintaining these to be in sync might be problematic. Then again the same applies to all the SQL text in more traditional approaches.
I wonder if there would be interest for such a library in boost.
Getting a library accepted in Boost is a tall order for sure. I'm just a lurker here, but given the recent threads on a GUI lib and a C++ Parser, I'd say you'd need to commit several months of hard work and have a pretty thick skin too. That's not even getting into which backend DB the library would support, the SQL vs NoSQL debate, etc... My own feeling is that you should explore this subject into your own public project for while, and try to attract a community of volunteers. Then again, once you've reached some kind of milestone you may want to revisit a possible submission to Boost. But I'm certainly no expert in the matter. Good luck to you, --DD