Database access has been discussed quite often in the Boost mailing list. I read through the discussions from Oct 2004 and Jul/Aug 2005 and I am well aware of the fact that there is a lot of disagreement concerning the interface presented to the programmer. So I just hope you don't bite my head off if I propose this as a GSoC project. ;) Things I would like to implement: * generic binding of parameters and results (using Boost.Fusion) * asynchronous access (using Boost.Asio) * odbc, postgres, firebird, mysql and sqlite3 backends (using their proper C API) * DATE, TIME and DATETIME fields (using Boost.Date_Time or struct tm) Surely other backends can be added, but these are the ones I am (more or less) familiar with. BLOBs would be interesting to implement but I am afraid time won't permit it. The following code snippet is a quick draft using sqlite3 as a backend. This is not the interface I am going to propose, please read on. boost::asio::io_service io_service; boost::database::sqlite3::connection conn(io_service); conn.open( "testdb" ); std::cout << "client version: " << conn.client_version() << std::endl; std::cout << "server version: " << conn.server_version() << std::endl; boost::database::sqlite3::statement stmt = db.statement(); stmt.prepare( "CREATE TABLE employee" "(id INT,name CHAR(20),salary INT,PRIMARY KEY(id))" ); stmt.execute(); // prepared statement with placeholders stmt.prepare( "INSERT INTO employee (id,name,salary) VALUES (?,?,?)" ); stmt.execute( boost::fusion::make_vector( 27, "Horst Zwiebelhacker", 5000) ); stmt.prepare( "SELECT name, salary FROM employee" ); stmt.execute(); // fetch directly into specified results std::string name; int salary; while( stmt.fetch( boost::fusion::vector_tie(name,salary) ) ) { std::cout << name << " gets " << salary << " pebbles." << std::endl; } The "open", "prepare" and "execute" functions have "async_" counterparts. The sync functions throw exceptions while the async ones pass an error to the bound handle as the first parameter. Both the synchronous and the asynchronous test programs run fine, though they are not thread safe (the final library definitely will be). I checked if it would be straightforward to implement other backends, which turned out to be true for most of them. I struggled with PostgreSQL where the type of the parameters has to be known when the statement is prepared. The syntax suggested by Tito Villalobos for declaring a statement seems to be a better approach (slightly modified by me): statement< parameters<...>, // query/stored procedure parameter types results<...> // recordset column types
my_stmt(connection, query_string);
This would make it easier to prepare statements in PostgreSQL and can be a benefit for other backends too. Statements could be automatically prepared when their execute function is called the first time. Finally, variadic templates in C++0x allow to hide the functions boost::fusion::make_vector and boost::fusion::vector_tie from the programmer, which makes the interface much more intuitive. Imagine stmt.execute(27, "Horst Zwiebelhacker", 50000); or while( stmt.fetch(name,salary) ) ... isn't that lovely? Just for clarity: The design I have in mind will provide the programmer with an easy way to use SQL. Still the programmer has to write her own SQL strings. A library which provides STL functionality by abstracting SQL could be built on top of this one. I would like to concentrate on making it asynchronous rather than on the STL part. Cheers. Daniel Pfeifer