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