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

From: Daniel Pfeifer (daniel_at_[hidden])
Date: 2008-03-22 10:37:41

Hi Phil,

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

Thanks for your encouragement!  I am ready to start.  All I need is
to be accepted for GSoC.  If I'm not accepted, I will code it anyway,
just for fun.  But I really could use the money. ;-)

> Did you see this thread from last year?

No, I did not know this one. Thanks for the link!
The threads I read before where those listed in the Boost Wiki under
Relational_Database_Access.  I also looked at the implementation of
most of the libraries linked there.

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

My plan was abstracting the different APIs but not touching the
strings.  Also I plan to use compile time polymorphy.
A boost::db::mysql::statement will have exactly the same interface as
a boost::db::postgres::statement, thus it will be easy to change to
another backend.  The only thing a programmer has to be aware of, is
the SQL "dialect" of the used backend.
"CREATE TABLE ... ENGINE=InnoDB" is fine in MySQL, but will signal
an error in SQLite.  Using the PostgreSQL backend the following code
would be correct:

 statement<
   parameters<int,string>,
   results<>
 > my_stmt(connection, "INSERT...VALUES ($1,$2,$1)");

To implement the same functionality for another backend one would have
to write
 statement<
   parameters<int,string,int>, // <- mind the additional int
   results<>
 > my_stmt(connection, "INSERT...VALUES (?,?,?)");
and pass the first parameter again as the third one.

It should be possible to build another library which hides these
differences by abstracting the strings.  Be it as an SQL DSEL or a
relational database library which completely hides SQL (better in my
opinion).

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

I like the object oriented look and feel of your design very much!
Also, I found out, that I could change my examples to this look
without touching a single line of my library! Awesome!

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

It is a good idea to use operator() as an alias to execute().  Still
I don't want to drop execute() because I want every async_ function
to have a synchronous counterpart.

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

As you wrote last year, you can implement STL-compatible containers
for results with essentially no overhead in PostgreSQL.
In SQLite no overhead is required when the iteration is forward only.
ODBC and MySQL make random access iteration possible, but both require
information "where to write the results".  The same form of overhead
is required for Firebird, which also is limited to forward only
iteration.

Using libpq (Postres' C API) a result can easily be decoupled from a
statement.  In other APIs they are bound together, so if you exequte
your query again, the first result is invalidated.  The simplest
workaround would be to copy the whole result set to an STL compatible
container.  In fact, this is what many libraries do.

Why not copy it over if you need and otherwise don't?

 struct employee{ int id, string name, int salary };
 std::vector<employee> employees;
 employee e;
 while( stmt.fetch(e.id,e.name,e.salary) )
    employees.push_back(e);

I would like to stick to my "stmt.fetch(put,results,here)" interface.
Maybe it is less flexible, but it is definitely more lightweight.

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

No, I just thought that it might be useful.  What do others think
about asynchronous database access?

Cheers,  Daniel