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

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

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

Postby raananb » Sun Jul 30, 2017 2:25 pm

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);

Return to “wxCode”

Who is online

Users browsing this forum: No registered users and 1 guest