DESIGN NOTES ============ This file attempts to document the issues, pros and cons of various design decisions that were made during the development of this library Boost mutexes vs. SQLite mutexes -------------------------------- - Using boost's mutexes creates an additional dependency on a boost library - With sqlite's mutexes, all statements play well together, even those that do not explicitly use the database mutex (such as sqlite3 calls made by the user from outside sqlite3cc) Invalidating in-progress statements before rollback and commit -------------------------------------------------------------- The issue is that attempting to execute a ROLLBACK or COMMIT can sometimes result in an error, depending on the state of other statements. Potentially, rollbacks are done by the transaction_guard dtor when unwinding the stack in response to an exception. Therefore, it is essential that they don't fail because of outstanding statements as we cannot throw an additional exception. In practice, this issue only happens when queries, specifically, are still "in- progress" (i.e., the previous call to sqlite3_step() for the statement resulted in SQLITE_ROW being returned; before sqlite_step() is called and after sqlite_step() returns SQLITE_DONE, statements are no longer "in-progress"). For commands, there should be no results (SQLITE_ROW should never be returned) so finalising the statement in command::step() is acceptable and guarantees that commands will not be an issue. The errors caused: - exec BEGIN, prep/step SELECT (returning row), prep/step on ROLLBACK gives SQLITE_BUSY "database is locked" - exec BEGIN, exec INSERT, prep/step SELECT (returning row), prep/step ROLLBACK gives SQLITE_ERROR "SQL statements in progress" Possible solution is to reset queries during rollback and commit. This has a danger associated with it that if the application is multi-threaded, we might end up resetting a query from another thread. But then, if you're sharing a database connection between threads and using transactions, you probably need your head looking at. So, there are two ways we can do this: 1. simply user sqlite3_next_stmt() to get outstanding statements and call sqlite3_reset() on them - this is an efficient solution, although not very OO. 2. keep a map of active (i.e. those for whom step() last returned SQLITE_ROW) queries in the database (using the sqlite3_stmt pointer as the key) and iterate through asking queries to reset themselves. - this is a more OO way of implementing it but has a greater overhead