/sqlite3cc

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