Re: [boost] [gsoc2008] Database Library (asynchronous)

From: Jeff Garland (jeff_at_[hidden])
Date: 2008-03-21 09:20:53

Daniel Pfeifer wrote:
> 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. 

Yep....

> So I just hope you don't bite my head off if I propose this as 
> a GSoC project. ;)

No, but I would expect varied opinions.  BTW in case you didn't catch it the 
leading candidate for a boost database access library is soci:

http://soci.sourceforge.net/

That said one has to wonder if the authors are ever going to bring it for 
review....

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

3 months is a short time.  I'd suggest you pick a smaller number of backends 
if you're going to propose this.  The design should support expansion to the 
others, but my sense is that 5 backends in 3 months isn't doable.

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

That's an interesting capability that AFAIK soci doesn't support.

> 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?

Yeah, that's cool :-)

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

This is a difficult area as I'd like to suggest you work on extending SOCI to 
do this, but given that soci isn't a Boost library I can't really do that.  I 
think the biggest issue you have is keeping a reasonable scope -- in general a 
full database binding library is too large for SoC.  That said I see some good 
ideas in your proposal so I'm not going to discourage you -- the mentors and 
other boosters can help you refine your proposal down to something useful and 
doable.

Thanks!

Jeff