sql - Synchronizing client-server databases -

i'm looking general strategies synchronizing data on central server client applications not online.

in particular case, have android phone application sqlite database , php web application mysql database.

users able add , edit information on phone application , on web application. need make sure changes made 1 place reflected everywhere when phone not able communicate server.

i not concerned how transfer data phone server or vice versa. i'm mentioning particular technologies because cannot use, example, replication features available mysql.

i know client-server data synchronization problem has been around long, long time , information - articles, books, advice, etc - patterns handling problem. i'd know general strategies dealing synchronization compare strengths, weaknesses , trade-offs.

the first thing have decide general policy side considered "authoritative" in case of conflicting changes.

i.e.: suppose record #125 changed on server on january 5th @ 10pm , same record changed on 1 of phones (let's call client a) on january 5th @ 11pm. last synch on jan 3rd. user reconnects on, say, january 8th.

identifying needs changed "easy" in sense both client , server know date of last synch, created or updated (see below more on this) since last synch needs reconciled.

so, suppose changed record #125. either decide 1 of 2 automatically "wins" , overwrites other, or need support reconcile phase user can decide version (server or client) correct one, overwriting other.

this decision extremely important , must weight "role" of clients. if there potential conflict not between client , server, in case different clients can change same record(s).

[assuming #125 can modified second client (client b) there chance client b, hasn't synched yet, provide yet version of same record, making previous conflict resolution moot]

regarding "created or updated" point above... how can identify record if has been originated on 1 of clients (assuming makes sense in problem domain)? let's suppose app manages list of business contacts. if client says have add newly created john smith, , server has john smith created yesterday client d... create 2 records because cannot aren't different persons? ask user reconcile conflict too?

do clients have "ownership" of subset of data? i.e. if client b setup "authority" on data area #5 can client modify/create records area #5 or not? (this make conflict resolution easier, may prove unfeasible situation).

to sum main problems are:

• how define "identity" considering detached clients may not have accessed server before creating new record.
• the previous situation, no matter how sophisticated solution, may result in data duplication, must foresee how periodically solve these , how inform clients considered "record #675" has been merged with/superseded record #543
• decide if conflicts resolved fiat (e.g. "the server version trumps client's if former has been updated since last synch") or manual intervention
• in case of fiat, if decide client takes precedence, must take care of how deal other, not-yet-synched clients may have more changes coming.
• the previous items don't take in account granularity of data (in order make things simpler describe). suffice instead of reasoning @ "record" level, in example, may find more appropriate record change @ field level, instead. or work on set of records (e.g. person record + address record + contacts record) @ time treating aggregate sort of "meta record".

bibliography:

• more on this, of course, on wikipedia.

• a simple synchronization algorithm author of vdirsyncer

• objc article on data synch

• syncml®: synchronizing , managing mobile data (book on o'reilly safari)

• conflict-free replicated data types

• optimistic replication yasushi saito (hp laboratories) , marc shapiro (microsoft research ltd.) - acm computing surveys, vol. v, no. n, 3 2005.

• alexander traud, juergen nagler-ihlein, frank kargl, , michael weber. 2008. cyclic data synchronization through reusing syncml. in proceedings of the ninth international conference on mobile data management (mdm '08). ieee computer society, washington, dc, usa, 165-172. doi=10.1109/mdm.2008.10 http://dx.doi.org/10.1109/mdm.2008.10

• lam, f., lam, n., , wong, r. 2002. efficient synchronization mobile xml data. in proceedings of eleventh international conference on information , knowledge management (mclean, virginia, usa, november 04 - 09, 2002). cikm '02. acm, new york, ny, 153-160. doi= http://doi.acm.org/10.1145/584792.584820

• cunha, p. r. , maibaum, t. s. 1981. resource &equil; abstract data type + synchronization - methodology message oriented programming -. in proceedings of 5th international conference on software engineering (san diego, california, united states, march 09 - 12, 1981). international conference on software engineering. ieee press, piscataway, nj, 263-272.

(the last 3 acm digital library, no idea if member or if can through other channels).

from dr.dobbs site:

• creating apps sql server ce , sql rda bill wagner may 19, 2004 (best practices designing application both desktop , mobile pc - windows/.net)

from arxiv.org:

• a conflict-free replicated json datatype - paper describes json crdt implementation (conflict-free replicated datatypes - crdts - family of data structures support concurrent modification , guarantee convergence of such concurrent updates).