For about a while now, I have been trying to figure out the best way to sync client's on-device offline database and off-device master database. In this process, one of the first things that I did was to look into creating my own implementation by syncing Android SQLite database with MySQL database. Up until this point, when ever client sync was required, I would simply clear out the content adapter's list, remove all elements, get all elements from master server and refill the adapter. This allowed for easy implementation. However, even if nothing was changed in the client database, whenever the client hit the sync button or pulled to refresh, the process would get repeated. My question was,
"Is it faster and less expensive to individually assess for updates by comparing updated_date and id or to simply truncate and refill?"
When to add, update or remove?
Add: If id is not present in SQLite but is present in MySQL.
Update: If id is present in SQLite but updated_date in SQLite is greater than updated_date in MySQL
Remove: If id is present in SQLite but not in MySQL
General sync algorithm
Get a list of all SQLite ids
Get json array from server
Loop json array for each object
Remove id from list if exist
Follow add or update (basically remove and add)
Delete all remaining ids' in list
Again, this should (hypothesis) take more time and memory compared to simply truncating and refilling. The sync process should also update local id's to server id's.
To test this I simply used this previous project. It is a basic Laravel web application that allows users to create their own priority list and category list as dictionaries and use those dictionaries while creating todo tasks.
Some screenshots of Android implementation:
The app uses loopJ Async library to make requests and pull to refresh interface to submit the sync request. The "M" represents category initial and the color blue and grey represents priority severity. All of these are dynamically assigned by the user.
Now, an AlarmManager with service could also be implemented for the sync to happen in the background. While this was my next step, I stumbled upon a magical backend tool named Firebase.
Firebase, in most basic sense is a json and noSQL based backend database service, which pings the client on data changes rather than the client pinging to the server to check for updates. Also, it comes with a data persistence support which requires being fine with not implementing any offline database. However, since a complex relational structure is not something NoSQL likes, a workaround was required to do the same project in Firebase.
Change in one device will trigger changes in all while skipping the traditional sync buttons or UI's.
Final Screenshot: Querying on children to receive undone tasks first.