"Home made" data replicaion with Nexusdb

[alogrep 0]

HI.
It appears that Nexusdb fórums treat any question about data replication as if were radioactive. Nobody touches it.
I hope some courageous soul would look at this scheme of data replication to another PC (PC2) on the same LAN .
Maybe I am way off: if so an answer “Yes, you are way off” would suffice

Have 2 identical data modules, DM1 with aliaspath pointing to the working directory, and DM2 with aliaspath pointing to a backup directory on another PC and running on a separate thread. The datasets in both DM's are identical.
I also have a unit (Unit0)accessible to both data modules where I store
some variables and structures/records.
Each table has a field NOT autoinc "DR" wich is a unique number
identifying each record, and an index based on it.
I put
   A) a "GlobalBeforePost" on each table in DM! which saves the dataset state
      (dsEdit or dsInsert), the record buffer and the "DR" value to Unit0,
   B) a "GlobalAfterPost" on each table in the first data module (DM1) which calls a function           in Unit0.
 

The pseudo code in the function in Unit0 is:
  Retrieve the dataset state and "DR"
  If state = dsEdit then begin
    If not Findkey(DR]) then
      Raise error
  End else
    Append
  Retrieve record buffer and copy it to the corresponding table in DM2
  Post.
The entire operation is inside a starttransaction /commit/except.
If this scheme is somewhat feasible, I have 2 questions:
1. Could there be a conflict if 2 users at the same time attempt to update
the same table?
2. How do you Save/Copy a record buffer?

[Fr0sT.Brutal 900]

On 8/27/2022 at 8:35 PM, alogrep said:

1. Could there be a conflict if 2 users at the same time attempt to update
the same table?

Sure it could.

On 8/27/2022 at 8:35 PM, alogrep said:

2. How do you Save/Copy a record buffer?

I wouldn't. Field values are better and much more flexible.

 

In general, if talking about your client-side replication (which I rather call duplication), I consider the following scheme to be more or less correct:

- Start transaction on Master blocking access to record (if DB engine can do this) or to table

- Start the same transaction on Replica

- Update Master

- Update Replica

- Commit Master

- Commit Replica

- In case of any error rollback both

- In case of error at Replica only - you seem to have troubles.

 

However, this scheme will only work if both DBs are online. If any of them goes offline even for a second, the state could get unsync'ed. That's why replications usually made at server side. Either with update logs filled by triggers (but be careful with rollback'ed transactions!) or some kind of "last modified" marks.

Anyway Nexus doesn't seem the good choice for this. There are plenty of opensource DB's which have replication out-of-the-box

[alogrep 0]

Thanks. 

"Anyway Nexus doesn't seem the good choice for this. There are plenty of opensource DB's which have replication out-of-the-box"

I agree, but that is feasible if you start a new project. But if you have an app with hundreds of thousand lines of code it would be unbearable.

[KenR 29]

I have been doing my own replication using NexusDB for years. Basically in the application there are triggers for each table that needs to be synchronised which inserts a record for every field changed into a simgle table, the data itself is not recorded at this time. There is a flag for the type of opertation e.g. update, delete, etc. There is a service running at each site that every minute, if there are any entries in the table, processes the records into a new table with the data, zips the table and uploads it by FTP to a central site. The same service, running at another site downloads the zip and applies the changes. Obviously there are many safeguards that need to be applied during each stage. This may seem rather Heath Robinson but it has been working flawlessly for a long time.

[sjordi 39]

Replication is very complex.

TMS Echo offers some kind of help but doesn't really handle conflicts besides "the latest record" wins over the oldest.

But now what if user A changes a record's FirstName field and user B changes the same record's Address field? The latest change would win, meaning that either the FirstName or the Address would be lost.

Real sync should take into account everything at fields level, not only record levels.

That means handling tables of changes and then trying to reconcile.

Reconcile should also be able to offer, as an option, a dialog box to ask the user which field wins over which, in case both users A and B change, say, the FirstName field. Which one is correct?

I still struggle with that but will move forward with Echo for now. No choice. Be aware that to use TMS Echo you need TMS Aurelius and TMS XData

 

Another solution is to use Interbase Change Views. But for this to work you need an Interbase server as a broker (and pay for it).

You can't use Change Views in an embedded solution like when you want to sync smartphones/tablets with a Mac or Windows standalone embedded desktop app (that would be my case).

It's a shame.

[Fr0sT.Brutal 900]

2 hours ago, sjordi said:

Real sync should take into account everything at fields level, not only record levels.

That means handling tables of changes and then trying to reconcile.

That's why the only really reliable option is to record log of all changes and then replay it over replica.

[alogrep 0]

Maybe I took the wrong term (replication). What I would Like to do is something like mirrored drives, but not with 2 drives on the same PC, rather the mirrored drive on another PC on the LAN.

[sjordi 39]

8 hours ago, Fr0sT.Brutal said:

That's why the only really reliable option is to record log of all changes and then replay it over replica.

If it's not trading secrets, hints and tips to achieve this, or a tutorial blog would be nice for others.
But I totally understand that's it's not always possible, and always time consuming...

[Fr0sT.Brutal 900]

12 hours ago, sjordi said:

If it's not trading secrets, hints and tips to achieve this, or a tutorial blog would be nice for others.

I'm just theorizing basing on what I read on Firebird forum from a guy that develops universal FB replicator and adding some logic.The idea is simple - having identical initial state and set of changes, current state could be easily achieved. The devil is in details. If replica is just a RO mirror, things are relatively simple but if it must be a fully functional node with all modifications allowed - things are getting much more interesting

[alogrep 0]

HI frost.brutal.

Do you have the link to "what I read on Firebird forum from a guy that develops universal FB replicator"?

Basically what I need is a solution to this scenario.

I have a database and the database management server on PC1, connected over the LAN to various PCs. In the LAN there is also a PC101 which has a copy of the database manager server (inactive) and should have in real time the same data that are stored in the database on PC1. If PC1 fails, the administrator would activate the server on PC101 and  the clients would automatically change the alias path to that of PC10! so the user can continue to work normally.

[Fr0sT.Brutal 900]

15 hours ago, alogrep said:

Do you have the link to "what I read on Firebird forum from a guy that develops universal FB replicator"?

Alas, no. The info I read was scattered into numerous topics where he explained why there's no "quick & simple" replication. It was Dmitry Sibiryakov, the one of active contributors to FB code, and the product is https://www.ibphoenix.com/products/software/ibreplicator

The forum where I read that is dead now so maybe you can only reach Dmitry in FB mailing list.

Edited September 2, 2022 by Fr0sT.Brutal

[Sriram 4]

On 8/30/2022 at 11:57 PM, sjordi said:

Another solution is to use Interbase Change Views. But for this to work you need an Interbase server as a broker (and pay for it).

You can't use Change Views in an embedded solution like when you want to sync smartphones/tablets with a Mac or Windows standalone embedded desktop app (that would be my case).

It's a shame.

For "data change", one would use InterBase Change Views to subscribe to changes in tables. Read further at https://docwiki.embarcadero.com/InterBase/2020/en/Change_Views

 

You can see further how to fetch and merge such changes in the database with your local list/resultset in your applications, using FireDAC. See the YouTube demo featuring this at https://www.youtube.com/watch?v=hFAyAqoZLrE&t=1745s

You can use InterBase server (embedded with your Desktop system application), and IBToGo on the Mobile platforms for this. Your Desktop application of course cannot use the InterBase protocol to connect to the Mobile phone/tablet, but the other way around is definitely possible. The IBToGo application running on the smartphone/tablet can connect to remote InterBase servers running in the network (Windows/Linux/macOS InterBase Server Editions), and can thus subscribe/pull/push changes from/to the remote database, with InterBase Change Views. 

Am I missing something in your comment? If so, please elaborate. Thanks!

[sjordi 39]

I would love to have data change (change views) without the server in between.
It would be nice to have only IBToGo on each smartphone, the Mac, the PC and be able to sync together.

For example, sync an iPhone directly to an Android smartphone or an iPad over local WiFi. No Server here.
But a protocol to compare both device contents.

I don't want the server. That means you need a server dedicated to sync AND an internet connection. On the go, it's a problem for me. And I don't want an internet connection to sync my iPhone with my Mac.

That's what TMS XData/Echo is kind of doing by comparing datasets. But it's not perfect the way I need it.

 

I use plenty of paid apps that do this over wifi, no server anywhere. One DB compared to another DB (same structure of course, but different devices)

[Sriram 4]

12 hours ago, sjordi said:

I would love to have data change (change views) without the server in between.
It would be nice to have only IBToGo on each smartphone, the Mac, the PC and be able to sync together.

For example, sync an iPhone directly to an Android smartphone or an iPad over local WiFi. No Server here.
But a protocol to compare both device contents.

I don't want the server. That means you need a server dedicated to sync AND an internet connection. On the go, it's a problem for me. And I don't want an internet connection to sync my iPhone with my Mac.

 

Steve,

I hear you. Thanks for the additional notes. InterBase, just by itself, does not have a protocol for providing/resolving datasets over WiFi/Bluetooth etc. Other than the Server, there is no "receiving" component over the network (local or otherwise) in the InterBase core product.

 

One can use IBToGo on both the Desktop and Mobile platforms, and use Change Views. But, the syncing between the devices have to be custom coded (I am not much aware about RAD or other 3rd party packages in this area) to send the changed dataset rows (received from Change Views subscription) from one, to the target device where is received and resolved. If I get to hear of any package that tackles this, I'll surely post here. Am eager to know if anyone else has insight into potential solutions for this.