Re: [boost] Interest in an ODBC library?

From: Brock Peabody (brock.peabody_at_[hidden])
Date: 2005-08-18 11:56:15

> On Behalf Of Caleb Epstein
 
> * Iterators.  I don't think these should be random access.  Of the
> database APIs I have experience with (Sybase, SQLite), none allows you
> to navigate to an arbitrary row in a result set.  You must process all
> results in sequence.

The only ones I've used (MySQL, PostgreSQL, Access, DB2) have had random
access.  In all the code I've written I don't think I've done anything
that required random access iterators for the database though.

The main reason I went with random access was because it made the
implementation of the iterator and my network database proxy easier.

If there's no real use for random access iterators and some widely used
databases don't support them then I agree we shouldn't either.

> * The transaction begin/commit/rollback methods should be moved to the
> abstract_database class as virtual methods.

That's actually the way I used to have it.

> There is just too much
> variation in the way transactions are handled to implement this at the
> top level.  For example, the syntax "START TRANSACTION" is not
> portable (isn't it just "BEGIN"?),

Oops!  That's easy to fix at least.  If we're using the correct syntax
is there still a need to add extra virtual functions?

> and I believe that in general one
> must match each "BEGIN" with an explicit  "COMMIT" or "ROLLBACK"; its
> not enough to just "COMMIT" or "ROLLBACK" once the nesting depth hits
> 1 unless you name your transactions.  Additionally, some
> implementations (e.g. SQLite) don't support nested transactions, so
> the underlying impl ought to be able to throw in the case that the
> user requests such an operation.

The way I've got this implemented, I never have to nest transactions.  I
don't think any of the databases I use let you nest transactions, which
is why the interface is set up the way it is.  Think of it as a nested
transactions simulator.  Without this it's almost impossible for modules
to use transactions.  I like your class so I'll use it in my example:

   database d = open_database();

   transaction t(d);

   save_customer(d, c);
   save_order(d,o);

   t.commit();

save_customer() may be called in other situations and it may have
transactions of its own or call other functions that do.  Counting
transaction depth allows you to do this on databases that don't allow
nesting.

> * I'd recommend a scoped_lock-like class for the transaction user
> interface.  For the same reasons that it is not advisable to manually
> lock/unlock mutexes, it is not adviseable to manually
> begin/commit/rollback transactions.  I'd suggest something like

Great idea!  We could also provide a named_transaction class for
platforms that support them.

> * Prepared Statements.  Sending data to a database is frequently done
> by preparing an SQL statement with placeholder arguments (e.g. "INSERT
> INTO foo ( col1, col2, ... ) VALUES ( ?, ?, ... )" and then binding
> program variables to those arguments and executing the prepared
> statement.  Do you think it would be good to add this ability?

What is the benefit of building a query this way?  Personally, I'd
rather see a type-safe SQL building DSEL.  What do you think?

> * Binding.  Some vendors provide APIs whereby query results can be
> directly bound to program variables.  The general workflow would be
> something like:
> 
>     double price;
>     std::string name;
> 
>     row_set results = database.execute (query);
> 
>     results.bind ("name", &name);
>     results.bind ("price", &price);
> 
>     for (row_set::iterator i = results.begin (), e = results.end ();
>           i != e && results.fetch (); ++i) {
>         std::cout << "name=" << name << ", price=" << price <<
std::endl;
>     }
> 
> This saves the variant<> conversion overhead and can be extended to
> include user-defined types and conversions.  Would you be open to
> including this in the implementation?

I don't know if this would need to be wired into the implementation; I
think we could easily build it on top of our interface though.

The second (and more exciting to me) part of this library is going to be
the serializer and it should provide facilities similar to but more
powerful than this.