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

From: Phil Endecott (spam_from_boost_dev_at_[hidden])
Date: 2008-03-21 12:59:13

Hi Daniel,

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

Go for it, if you already have your own vision of how it should look.  
If you spend a lot of time listening to other people and attempting to 
make a compromise, you'll run out of time before you've even started.

Did you see this thread from last year? http://lists.boost.org/Archives/boost/2007/03/117939.php

>  stmt.prepare( "INSERT INTO employee (id,name,salary) VALUES (?,?,?)" );

In PostgreSQL, you use $1,$2,$3 in place of ?,?,?.  So of course you 
could convert the ?s to $ns in your PostgreSQL back-end, but that means 
that you lose the ability to re-order parameters and to use some more 
than once.  So, you could use the PostgreSQL syntax and convert for the 
other back-ends.  Or you could do what Apache's database backend does 
and use printf-like % escapes.  But that's just a detail.

> 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);

Here's approximately how my PostgreSQL wrapper would do your example:

class EmployeeDatabase: public Database {
   typedef int    id_t;
   typedef string name_t;
   typedef int    salary_t;

   Query<>                       create_table;
   Query<id_t, name_t, salary_t> insert_employee;
   Query<>                       select_all_employees;

   EmployeeDatabase():
     Database("...pgsql connection string..."),
     create_table(*this,         "CREATE TABLE employee (id INT,name 
CHAR(20),salary INT,PRIMARY KEY(id))"),
     insert_employee(*this,      "INSERT INTO employee (id,name,salary) 
VALUES ($1,$2,$3)"),
     select_all_employees(*this, "SELECT name, salary FROM employee")
   {}
};

EmployeeDatabase db;
db.create_table();
db.insert_employee(27, "Horst Zwiebelhacker", 5000);
EmployeeDatabase::Result r = db.select_all_employees();
for (int i=0; i<r.rows; ++i) {
   cout << r.get<string>(i,0) << " gets " << r.get<int>(i,1) << " pebbles.\n";
}

I never got around to using tuples in multi-column results, hence the 
get<>() above.  It has type-checked and more concise single-column 
results though.  Thinking back, the reason why I didn't implement 
tuples for returned rows was that I really wanted structs, so that I 
could access fields by name not number, and the necessary fusion-magic 
to do that was beyond me.

I used the Boost preprocessor library to support up-to-N parameters; it 
would be much simpler using variadic templates.

> Statements could be automatically
> prepared when their execute function is called the first time.

I think this is what I do; however, if you prepare them as soon as 
possible you'll detect SQL syntax errors earlier, which could be 
useful.  I also have a runonce() method that you can use if you don't 
want to prepare the statement, which can sometimes be useful.

> 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);

Just use operator() and write stmt(27, "Horst Zwiebelhacker", 50000);

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

Good plan.  However, if someone else were considering writing "SQL in 
C++" then Proto will now make that job easier than it would have been before.

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

It's easy enough to make the query results look like STL containers 
(e.g. when iterating through them).

As it happens, I've never needed asynchronous database functions; 
generally I block waiting for the results and if there's other work to 
do at the same time it's in another thread.  Do you have an application 
using asio that's driving this?

Regards,  Phil.