List of high level requirements:
1) Works over ODBC. Expanding out to other DB API's would be great at a later point, but all the major DBMS's support ODBC, and it is available on both windows and *nix.
Why? Is this a strong requirement to work over ODBC? What about specifying it as follows "A DB access library candidate should provide ODBC support" - Mateusz
Using ODBC might be a good way to support many systems "for free", but a particular DBMS can have better access mechanisms worth supporting (e.g. directly embedding SQLite or using Oracle OCI libraries). Moreover, many systems do not have ODBC or setting it up is not an option, needlessly limiting portability. ODBC use should be optional; I think there should be an abstraction similar to JDBC drivers and data sources, with a rather generic ODBC bridge and a number of DBMS-specific implementations treated the same way. - Lorenzo Gatti
2) Ability to specify full queries and recordsets quickly, concisely and within a function. The overhead of needing to create new classes for each recordset is too high.
What overhead? A very lean "record" template (similar to the existing boost::tuple, with extra functionality) can be instanced and created from compile-time constant column metadata objects that would need to be specified anyway. When (rarely) queries are really dynamic and arbitrary there is no way to create new classes for them and the library would need to resort to something relatively generic and inefficient (like the existing boost::any).- Lorenzo Gatti
3) Integration with STL. Specifically iterators, typedefs and common functions (e.g. clear()).
Many std::vector and std::list operations and guarantees make very little sense if the collection is actually a database-backed record set and the iterator is actually a cursor. For example:
A library should promote efficient SQL use, not potentially limited and inefficient leaky abstractions.- Lorenzo Gatti
4) Interface that is simple and understandable to programmers familiar with databases. (i.e. Users of the library shouldn't need to understand the intricacies of MPL, even if the library uses it.)
Programmers are familiar with very different database access API styles, so this is a very slippery objective; more universal technical considerations (generality and implementation difficulty, conciseness and elegance, portability and feasibility, performance of common operations, etc.) should be more important than catering to the habits of some users. Not exposing accidental implementation complexity is on the other hand important. - Lorenzo Gatti
5) Does not require the usage of RTTI
Why? This seems like an arbitrary requirement to me. Further, for some dyanmic query capabilities it seems like it would be a good way to implement the needed fuctionality. Of course for simple static SQL I agree -- Jeff
6) Allows for the user of the library to "plug in" their own binding / subscription system. (to support bound controls)
What do "binding" and "subscription" mean? Is it a reference to GUI "bound controls", Visual Basic style?
I'd like to expose an idea of an API that I think could be very useful and innovative. The advantages are that for people who are used to work with SQL, it would be a very easy API to use. The idea with examples is better. The idea is to use expression templates to be able to write code for which you can deduce what type is needed to hold the results of a query. The syntax is inspired in that of boost.spirit, but applied to SQL. The advantage of this approach is that you can interoperate results of queries with stl containers very easily. An example of the usage of the API would be (roughly) like this:
connection conn("db", "user", "passwd");
//Return? type is a result whose rows have an int, and two strings
query q = select >> int_d("id"), string_d(distinct("surname")), string_d("addresses") >> from >> table("customers").join("adresses").using("id") >>
where >> int_d("id") > 7;
//The? result is bound to the database, so it knows where to commit changes when done.
result<> r = conn.query(q);
//Now? results are holded in r. r can be manipulated via operator().
//Result? has an operator(int, string, string) and you can access data via the operator like this.
r(10, "Smith", "High Street") = make_row(10, "Taylor", "blabla");
//You? can also do this to update a set of rows
r(ignore, ignore, "High Street") = ....;
//Result? is updated in the database. It sends the changes done to the rows.
//Now? I want to take everything in a vector
std::vector<row<int, string, string> > vresult = r.as_vector();
The code has been written without much thought, but you get the idea.
The most interesting part is the query. The query is approximated using expression templates. This way the result type of the query can be deduced and you can store the results. The int_d in the query models DataType? concept so that you can add your own data types. It's not necessary to map data types to native c++ types. If you use the following:
select >> "id" ....;
you could return something similar to boost::any in that row, so you can always do queries without knowing the return type from the beginning.
The way to implement the library is to map select, using, join and every function to strings that will be commited to the database, but having expression templates you can get the information needed to deduce whatever you need to return from the query. Let me know your opinion. Thanks.