[boost] [gsoc2008] Database Library (asynchronous)

From: Daniel Pfeifer (daniel_at_[hidden])
Date: 2008-03-21 08:23:53

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