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.