Sync two DB

Hi
I need idea how to sync to DataBase .

One is Local and the other one is Remote Server

example code

[quote=35120:@Alexis Colon Lugo]Hi
I need idea how to sync to DataBase .

One is Local and the other one is Remote Server

example code[/quote]

What kind of database ?
Maybe the database applications already have this feature.

PostgreSQL

“Sync” could mean a lot of things
One way (i.e… moving data & schema changes from a “test or qa server to a production one” which is usually one way.
Or do you mean two way - so that data that is missing on either is pushed into the other.
The trick then is what if you have modifications in the same rows & columns ?
Which one overwrites the other ? Or does it ?

yes
mean two way

Another issue is whether the data is missing on DB-A because it has been added to DB-B or has been deleted from DB-A.
Things might be manageable if there is only 1 local and 1 hosted DB to sync, but once you have multiple local DBs then it gets extremely messy, What do you do if 2 people edit different columns in the same record? Is the data correct with the second edit’s complete changes (as person 2 viewed the record when the last change was made) or with only the edited fields from both people? And what if a third person deletes the record altogether?

[quote=37526:@Alexis Colon Lugo]yes
mean two way[/quote]
With a great deal of pain since usually two way requires an app that implements certain business rules which vary from one company to another.

If one is “the master” and one is the “slave or client” and is the slave / client allowed to overwrite data in the mast IF the masters has been updated ? This usually ends up being a business decisions and NOT so much a programming problem.

I’ve written these before but they all vary from company to company as no single set of rules applies to every set up.

ok

thanks for help

I wouldn’t use Xojo for this sort of thing with PG. I’d look at PgCluster or Slony.

Synchronization isn’t quite the same as replication.
I wish it was then there are lots of tools.

What I’ve had to do in the past was write custom apps that embedded the business rules.
“If head office updates this field it ALWYAs overwrites the one in the field”
“If the field updates this and head office hasn’t touched it then the field office value overwrites it”
and so on.
That sort of thing isn’t handled by any tools as its VERY business specific.

Norman, I just had a good smile this morning… thanks!

Our product is a Synchronization tool (and its built on Xojo). In fact, I own the trademark for “BizSync”… which made me crack up when you were describing business rules and syncing.

You can see that here.

http://www.channelbrain.com/products/bizsyncxl/multichannel-order-manager/

So yeah, you’re 100% right – syncing data from one provider is not the same as replication. Absolutely not the same.

Maybe I read into his words too much? If you read what Alexis said, which was “One is Local and the other one is Remote Server” that to me smacked of a replication need.

Of course, he gave us little information to go on other than that one sentence, but I assumed that if the local and remote need to be “sync’d”, it wasn’t a business rule sync, but a replication of the database.

I realize this thread is two years old… but I’m going to restate the question in different terms

I have (or will have) an iPad app with an SQLite database, and I have (already) a XOJO app with the exact same database (same schema etc). Now to do what I need to do I realize I will need to make some changes in the database structure, but I’m willing and able to do that once I have a solid foundation as to what those changes should be.

Right now the basic record structure is [RecordID] [some data fields] with [RecordID] being a simple unique Integer value, that is used in some cases as a join key between some of the tables.

What I need to be able to do is present TWO copies of this data (one from the iPad, the other from Desktop) and perform a two-way synch. And here is what I am thinking… and would love to hear comments, suggestions etc.

Modify the basic structure to change [RecordID] to a GUUID value so it will always be unique between the two instances
Add a new hidden field that would be [RecordStatus]

[RecordStatus] would be

  • [A] - record was just added to the instance (obviously this record would not even exist in the other database instance, yet)
  • [D] - a delete transaction was performed (the record would remain in the database, but be excluded from any displays)
  • [M] - record existed but was modified (one or more fields were changed)
  • [X] - record has not be touched since the last sync operation

Did I leave out any potential record status?

Then a sync app would address both database instances

  • If GUUID exists in iOS but not in Desktop, then inserted into Desktop, set [RecordStatus] to [X] in both instances
  • If GUUID exists in OSX but not in iOS, same thing but other direction, these would both be [A] status to start with
  • iOS=[D] OSX=[M] - Delete from both, set status to [X] in both… Delete trumps Modify?
  • iOS=[D] OSX=[X] - Delete from both, set status to [X] in both
  • iOS=[D] OSX=[D] - Delete from both, set status to [X] in both
  • iOS=[M] OSX=[M] - WHO WINS???
  • iOS=[M] OSX=[X] - Copy record from iOS to OSX , set status to [X] in both
  • iOS=[M] OSX=[D] - Delete from both, set status to [X] in both… Delete trumps Modify?
  • iOS=[X] OSX=[M] - Copy record from OSX to iOS , set status to [X] in both
  • iOS=[X] OSX=[X] - Do nothing as nothing was changed.
  • iOS=[X] OSX=[D] - Delete from both, set status to [X] in both

So basically, should a DELETE trump a MODIFY? (or should it then be based on timestamp???)
Same if both platforms independly modified the same record (it would be difficult to do field matching)

Now with all that being said… How would one manange the physical part of a sync? As in how to make both instances available so a synch program can read/write them both?

One “simple” idea I had was to forget about “synch”, and just have the user decide that one or the other instance was “golden” and copy it over the other instance. This however would mean that you could not be changing both platforms at the same “time”

Ideas? Thoughts? am I off base a little? or a lot?

Hi Dave -
For what it is worth - I have a program with an SQL database that lives on both desktop and server (cloud). Every time the database gets modified, I save the latest mod date. My app is basically one user per database, so an easy premise for me is the user is either using the desktop app or the cloud app, but not both at the same time (but I do accommodate a bit of back and forth). When the desktop loads it checks the cloud last mod date, and it decides which is the golden copy - and the good news is that the database is on the order of 1MB so downloads/uploads very quickly. When user updates file on the desktop it will, when idle, send the latest database version up to the cloud - keeping the cloud version always current.

So, my method which has been working very well for months is more like your “golden” version method.

A last change date stamp might be useful
That way if you delete it on one but modify it on the other and the modification is newer than the delete then keep the item and push it to the other

In spots where you have a record modified on both targets this would be useful as well since one will be newer than the other and could be used to determine which direction to push

I once tried to use a last changed/modified date for data sync and was admonished by our architect who told me that there should only ever be one “source of truth” for dates, e.g. a web service that provides you with a value for ticks; that you shouldn’t rely on comparing a date column from different sources unless the comparison was only ever “is it different” and not “is it older/newer” because you can’t rely on system dates being identical on different clients.

system times can be notoriously off

a service like nist would be good but isn’t always reachable

you have to take what you can get

even if all you did was determine two rows had different types of changes and use the date to suggest which direction the sync should go you’d be better off than doing nothing

Dont think I’d sync and deal with conflicts without user intervention

Thanks… I have a lot of thinking still to do on this obviously…
Its not meant to be a program that is used by lots of people… for the most part one, maybe a family… but it consists of 47 tables (as of now, with about 1/2 the tables being data related to different things (passwords, web domains, software registrations, medical records, home inventory etc), and the other 1/2 being things like screen layouts, report layouts etc. I wrote the desktop version a few years ago, and use it to track all of the above items including my twice daily glucose readings (my doc loves the graphs). So I decided to take a shot at an iPad version (iPhone doesn’t have enough screen real-estate)

http://www.rdS.com/ds_lockbox/index.html

I’m playing at the moment with some ideas, they may not help at all, but they are a little different.

I’m working on a similar problem and I’ve taken a page from the NOSql databases.
I have a ID field as a guid.
I have another field which is a REVISION (REV) field, this is also a guid.
And I have a change counter integer field.
I also have a DELETEARCHIVE field, which is 0 for active and 1 for deleted. We never actually delete a record, just change its status.

Every time a change is made on the client or the master a new unique guid is generated and replaces the REV.
and the change counter increments by 1.

The idea is each client database synchs against the remote master by comparing the REV field.
If the clients REV is the same as the masters. No change.
If the REV is different a change has occurred. This assumption is supported by a larger change counter.

A deletion is an update with a new REV value and counter increment.
If a new record is added on the client, it’s ID can’t be found on the master so it’s obviously new.

If the master has a different REV value it is the latest change because the client got its record originally from the master. So the clients data is now in question. So the clients value is updated to the masters value.
If the clients REV is different it updates the master.
These are thoughts in a prototype app I’m working on.
I’m sure I haven’t thought it all the way through, but it may give you something to think about.

Sounds like I’m on the right track then :slight_smile:

Now the problem is how to get the sync program to SEE both databases.
a) do it over WiFI?
b) move the iOS database somehow to the OSX platform, sync it, and move it back?
c) something else?

I have make my won sync app on my server that chk the table and sync one by one.

I have a vps and it works.

IOs is the problem i guest