The context: In an application using a global database and a set of annual databases, updating a data element in the global data base entails the need to update the annual databases.

The problem: How to ensure the atomicity of the update?

sqlite provides the mechanism Begin/Commit/Rollback to ensure atomicity for a database connection.

However, in the context above, Begin/Commit will apply independently to each connection: to the global database and to each annual database, and the atomicity of the set of databases will not be ensured.

A solution (with the help of Ulrich Telle):

The solution consists of attaching each of the annual databases (with a distinct alias) through the connection of the global database, and use the Savepoint/ReleaseSavepoint/Rollback mechanism. There is no direct connection (Open) for the annual databases. The global connection is used for all the updates, including updates of the annual databases. Rollback will roll back all transactions, including those of annual databases already released. Detach the annual databases when through.

sqlite (version 3.16.2) sets a default limit of 10 on the number of databases which can be attached (# define SQLITE_MAX_ATTACHED 10 in sqlite.c). A limit can be defined in the application with wxSetLimit(), and must not be bigger than the value defined in sqlite (up to 125). If the limit in sqlite is modified, sqlite must be recompiled.

To access Table1 in the global base use the usual syntax: "UPDATE Table1 SET dataElement1='value1', dataElement2='value2'".

To access Table1 in annual base attached as 'alias1' use alias1.Table1: "UPDATE alias1.Table1 SET dataElement1='value1', dataElement2='value2'".

the programming skeleton which follows was written for clarity, not executablity. Use your own taste & methods when coding.

for (int i = 0; i < numberOfAnnualBasesFiles; i++) { // each Savepoint needs a distinct name. Here, 'name' is identical to the alias of the base (but does not have to) wxString name = wxString::Format("Alias%d",i); // same as in Attach above