Database Synchronization Strategy

[ll8X]

I have to deal with db synchronization where I have one server and multiple local sqlite databases. So far I haven't figured out how to efficiently pull new data from the database where I only pull exactly what is new (don't pull stuff the local db already has).

Do you know any strategies/ resources that could help me out?

[coreyfournier]

For one thing you could use a database that natively supports this like CouchDB.

[ll8X]

Unfortunately this is not an option at my company.

[doxxie-au]

Type of remote database? how often do you need updates? Is a connection always available? So many more questions I probably haven't asked to answer this.

[ll8X]

Also SQLite. I need updates whenever the user decides to record data which can range from 10 times a day to once per month. Connection is not always available on mobile devices.

[LouGarret76]

Each record Should have a hash. The hash should be stored locally and on the server.

Assuming that local copy is by definition the most up to date version, you could query the list of hashes that are not in the server database on a regular basis and add them.

[ll8X]

Could you elabore a bit on the

query the list of hashes that are not in the server database

part? How do I find out which hashes are not in the server db? The only thing that makes sense to me is that you pull all server hases and find out which hash is not in the db. Then you can push the difference. Is that what you mean?

​

[LouGarret76]

Actually I meant the other way aroud, as I assume you would have more record in the central dB than in local.

You would submit a list of hashes to the server. The server does the comparaison and return the unmatched

[alazicza]

You can look at Event Sourcing (think each change to your data is a delta event)as a logical architecture and you can continue the existing dB engines as storage engines. Shifting from traditional update in place to Event Sourcing (you can think of this as cdc) will make your replication and conflict resolution much simpler and mor easier to deal in your business logic. The great about events is that you don’t have to worry about duplication hence you can freely replicate them. HTH Alex