How to sync a set of data bases with sqlite/wxSQLite3 Topic is solved

Talk here about issues with one of the components hosted at wxCode, or suggest features for it.
Post Reply
raananb
Super wx Problem Solver
Super wx Problem Solver
Posts: 488
Joined: Fri Oct 27, 2006 4:35 pm
Location: Paris, France
Contact:

How to sync a set of data bases with sqlite/wxSQLite3

Post by raananb »

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.

Code: Select all

#include "wxsqlite3.h"

wxSQLite3Database* global;

global = new wxSQLite3Database();

global->Open(globalDataBaseFile);

globalDB->SetLimit(WXSQLITE_LIMIT_ATTACHED, numberOfAnnualBasesFiles); // numberOfAnnualBasesFiles <= SQLITE_MAX_ATTACHED.

try
{	
	for (int i = 0; i < numberOfAnnualBasesFiles; i++)
	{  // give a distinct alias to each attachment. Note the quotes
		global->ExecuteUpdate("ATTACH DATABASE 'annualbaseFile[i]' AS 'wxString::Format("Alias%d",i)'"); 
	}

	Savepoint("GlobalSavepoint"); // the savepoint name has no significance
	
	global->ExcuteUpdate("UPDATE Table1 SET dataElement1='value1'"); // update the global database

	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
		
		Savepoint(name);
		
		global->ExecuteUpdate("UPDATE name.TableX SET dataElement1='value1'") // update TableX of annual base
		global->ExecuteUpdate("UPDATE name.TableY SET dataElement1='value1'") // update TableY of annual base
		
		ReleaseSavepoint(name); 
	}

	ReleaseSavepoint("GlobalSavepoint"); // name used in Savepoint()
}
catch(...)
{
	global->Rollback("GlobalSavepoint");
}

global->Close();

for (int i = 0; i < numberOfAnnualBasesFiles; i++)
{   // Detach the databases, with the aliases used to attach earlier
    global->ExecuteUpdate("DETACH DATABASE 'wxString::Format("Alias%d",i)'"); 
}

wxDELETE(global);
Post Reply