Database synchronization

Posts   
 
    
Posts: 61
Joined: 14-Feb-2017
# Posted on: 20-Mar-2017 15:51:49   

Hi,

I need a way to synchronize 2 databases (LOCAL AND REMOTE) : * the 2 databases have identical schemas * the 2 databases use the same database engine but the database engine can be oracle, sql server, mysql sql, ... => so i use DataAccessAdapter to manage it * changes will only be take into account on the LOCAL database

The typical scenario would be * create a dump of the REMOTE database and overwrite LOCAL database with this dump * each time user modify the LOCAL database (insert, update, delete), log the query in a file (or in a database table) * when synchronization is needed, just reinject the queries to the REMOTE database

So, what I would need is to log the query in a way I would be able to reexecute it (and not just for information) ?

1) To detect, changes on LOCAL database, I thought using audit interceptor or override DataAccessAdapter method? Is it the right place? 2) To log the query, is it possible to use TraceListener or can you give me a better way ?

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 20-Mar-2017 22:53:19   

IMHO, this should be handled at the database level, with an architecture of replication. I'm sure each of the mentioned database engines has it's own tools or third party tools that can keep 2 databases in synch.

Posts: 61
Joined: 14-Feb-2017
# Posted on: 21-Mar-2017 18:12:41   

I will try to explain the situation.

  • the application store data in a MASTER database
  • the same application when not connected will have to store data on a LOCAL database
  • when the application is again connected, it must be able to syncrhonize with the MASTER database

1) I don't want to have to install a tool on each computer which has the LOCAL database 2) I don't want to have to manage different tool, one for database engine (it would be certainly the case)

Note : I know that if 2 persons update (or delete) the same table and synchronize, all of the information updated by the 1st person who synchronize its data will loose its changes because the 2nd one will overwrite them but I will live with this !!!

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 22-Mar-2017 10:12:57   

An Auditor can log the activity done at the ORM level, you could use that to replay the actions again at the ORM level: read the audit entities, build actions on entities based on these, perform the actions.

Another way is indeed to use a tracelistener. This allows you to log SQL queries and parameter values. One downside is that the tracers log text, so the parameter values are in text too. This can be problematic with binary values as they're not stored in the trace (as well as large values in text)

You can also override ExecuteActionQuery, as you only need changes to sync, not fetches. Overriding that method will give you the IActionQuery object which contains the SQL command and parameters. You can then log that somewhere to replay it later.

Syncing of data isn't as simple as it seems, be aware of that. You need strict time measurements to be sure a change locally happened before a change elsewhere.

Frans Bouma | Lead developer LLBLGen Pro
Posts: 61
Joined: 14-Feb-2017
# Posted on: 22-Mar-2017 18:15:05   

Thanks.

Before you answer me, I tried to override the OnSaveEntityComplete, OnUpdateEntitiesDirectlyComplete, **OnDeleteEntityComplete **and **OnDeleteEntitiesDirectlyComplete **of the DataAccessAdapter, use the IActionQuery parameter to log the command as text in a table using the following link to create executable query : https://searchcode.com/codesearch/view/9442413/

Is it better to use ExecuteActionQuery? If so, I will change my code.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 22-Mar-2017 19:06:17   

Definitely ExecuteActionQuery is easier, as it's the unified path through all other methods you are using. So instead of scattering your code, it's easier to implement it in one place.

Posts: 61
Joined: 14-Feb-2017
# Posted on: 23-Mar-2017 10:30:16   

I see that there is also ExecuteActionQueryAsync, ExecuteSQL and ExecuteSQLAsync. I suppose I need to use also these methods, no?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 25-Mar-2017 10:11:57   

gilles.marceau wrote:

I see that there is also ExecuteActionQueryAsync, ExecuteSQL and ExecuteSQLAsync. I suppose I need to use also these methods, no?

ExecuteActionQueryAsync, yes, if you use async calls. ExecuteSQL and ExecuteSQLAsync are the plain SQL execution API elements and they eventually will call ExecuteActionQuery(Async) so you don't need to override those.

Frans Bouma | Lead developer LLBLGen Pro