Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> Database synchronization
 

Pages: 1
LLBLGen Pro Runtime Framework
Database synchronization
Page:1/1 

  Print all messages in this thread  
Poster Message
gilles.marceau
User



Location:

Joined on:
14-Feb-2017 11:35:46
Posted:
25 posts
# 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 ?




  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14584 posts
# 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.


  Top
gilles.marceau
User



Location:

Joined on:
14-Feb-2017 11:35:46
Posted:
25 posts
# 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 !!!
  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37859 posts
# 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
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
gilles.marceau
User



Location:

Joined on:
14-Feb-2017 11:35:46
Posted:
25 posts
# 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.

  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14584 posts
# 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.

  Top
gilles.marceau
User



Location:

Joined on:
14-Feb-2017 11:35:46
Posted:
25 posts
# 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?
  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37859 posts
# 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
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.