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.
> 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:
That said one has to wonder if the authors are ever going to bring it for
> 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))" );
> // 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" );
> // 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):
> 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);
> 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
Boost list run by bdawes at acm.org, gregod at cs.rpi.edu, cpdaniel at pacbell.net, john at johnmaddock.co.uk