Editing Relational Database Access

BOOST WIKI | RecentChanges | Preferences | Page List | Links List

A couple of discussions on the mailing list have led to renewed interest in a library to facilitate RDB access in boost.  This page documents the dicussion and other thoughts.

=== Overall Requirements ===

*Clean, simple, modern C++ interfaces
*Provide access to multiple RDB's (ODBC is probably the only reasonable approach)
*Integration with STL collections

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" - [[People/Mateusz Loskot|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:

* All modifications require issuing appropriate INSERT, UPDATE or DELETE commands; remove_if is particularly nasty. 
* Even with a way to specify all these SQL implementations of STL functionality, what should happen to open database transactions? 
* Where do insert() and push_back() add records?

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 -- [[People/Jeff Garland|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");

conn.open();

//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.

r.commit();

//Now I want to take everything in a vector

std::vector<row<int, string, string> > vresult = r.as_vector();

conn.close();

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.
=== Mailing List References ===
Discussion really started Mid-Oct 2004

*DB dicussion (Aug 2005) Brock P. http://lists.boost.org/Archives/boost/2005/08/92275.php
*ODBC proposal (Jul/Aug 2005) http://lists.boost.org/Archives/boost/2005/07/90890.php
*Boost DB Proposal (Oct 2004) http://lists.boost.org/MailArchives/boost/msg73184.php
*RTL Discussion http://lists.boost.org/MailArchives/boost/msg73279.php

=== Current Libraries ===
*Database from Brock P in boost sandbox cvs http://boost-sandbox.cvs.sourceforge.net/boost-sandbox/database/
*SOCI - The C++ Database Access Library -- http://soci.sourceforge.net/
*Microsoft OLE DB Consumer Templates -- C++ templates for easy binding of database parameters and columns. Wide range of optimization options. Integrated with Microsoft Visual C++. http://msdn.microsoft.com/en-us/library/fk4h509a(VS.80).aspx
*<nowiki>MySQL</nowiki> C++ API -- http://mysqlcppapi.sourceforge.net/
*<nowiki>MySQL</nowiki>++ -- http://tangentsoft.net/mysql++/
*OTL (Oracle/ODBC Template Library) -- http://otl.sourceforge.net/home.htm
*DTL (Database Template Library) -- http://dtemplatelib.sourceforge.net/index.htm
*<nowiki>MyMySQL</nowiki> (A lightweight C++ wrapper around the <nowiki>MySQL</nowiki> client API) -- http://www.redi.uklinux.net/mymysql/
*libodbc++ aka freeodbc++ (A c++ class library for accessing SQL databases) http://libodbcxx.sourceforge.net/
*unixodbc (odbc 3.51 gpl, lgpl driver manager) http://www.unixodbc.org
*iodbc (odbc 3.52; lgpl driver manager) http://www.iodbc.org
*http://www.garret.ru/~knizhnik/fastdb/FastDB.htm Fast DB
*IBPP (C++ Client Interface to Firebird Server) -- http://www.ibpp.org/
*QTSql Reference http://doc.trolltech.com/4.1/qtsql.html
*Postgress C binding docs -- http://www.postgresql.org/docs/8.1/interactive/libpq.html
*Libpqxx (C++ API for Postgresql) -- http://pqxx.org/development/libpqxx/
*SQLite C API -- http://www.sqlite.org/capi3ref.html
*POCO Data (wrapper around ODBC and SQLight with other backends possible, released under boost license) -- http://www.appinf.com/poco/info/index.html
(Note, not sure what these libraries are able to be licensed under, so need avoid taking code until we confirm which are allowed to be sublicensed under the BSL.)

Summary:
This change is a minor edit.
(Visit Preferences to set your user name.)

View other revisions
BOOST WIKI | RecentChanges | Preferences | Page List | Links List

Disclaimer: This site not officially maintained by Boost Developers