I am going through the thouht process to implement sync between my Android's SQLite and my remote server's MySQL. My overall plan is to have a seperate "sync" table that tracks inserts, updates, and deletes. Overall I am fine, but I'm stumped with a particular "what if" scenario.
Scenario: My project is an Android app as well as having a web interface. The remote MySQL and local SQlite databases are synced. Then, my wife decides to UPDATE an entry via the web interface. Meanwhile, I'm in the middle of the Sahara Desert where I have no signal, but I decide I want to UPDATE the same entry on my phone, only 15 seconds after my wife. Later, I get back into civilization and I have internet access for my phone again, and the sync needs to happen.
I was originally thinking to use UTC as a "time last updated" row, but then I thought, "what if I had recently changed the time on my phone so I could cheat on a game and forgot to set it back to the real time?" My phone's time would be incorrect, thus the calculation to get UTC would be invalid.
I've looked through SO and Google and other various sites, and perhaps I just am missing the right keywords, but I haven't been able to find much in my limited research so far.
Is there any sure-fire way to know which entry was updated last, so that appropriate sync measures can be taken? I don't expect that there is a perfect system (but if there is, that'd be awesome too!), but any advice or wisdom would be helpful.
