CODEWITHSUNDEEP
phpandroidmysqljsonclient-server
tim
tim

Reputation: 10176

Android: How to synchronize database entries between Client and server

I have an android app, which needs to fetch new data from a server running a MySQL database. I add data to the database via a Panel which I access online on my domain.com/mypanel/.

What would be the best way to fetch the data on the client to reduce the overhead, but keep the programming effort as small as possible. It's not necessary for the client to get the latest database changes right after they have been updated, i.e. it would be okay if the client is updated some hours after the update.

Currently I thought of the following:

  1. Add a column timestamp to the database-tables so that I know which changes have been made
  2. Run some sort of background service on the client (in the app) which runs every X hours and then checks for the latest updates since the last successfull server-client synchronization
  3. Send the time-gap to the server in which there haven't been any updates on the client anymore, using HTTP-POST
  4. On the server, there will be some sort of MySQL SELECT-statement which considers the sent time-gap (if there is no time-gap sent from the client, just SELECT everything, e.g. in case of the first synchronization (full-sync)) --> JSON-Encode the Arrays -> Sent the JSON Response to the Client
  5. On the client, take the data, loop row by row and insert into the local database file

My question would be:

Is there something you would rather do differently? Or would you maybe send the database changes as a whole package/sql-file instead of the raw-data as array?

What would happen, when the internet connection aborts during the synchronization? I thought of the following to avoid any conflicts in this sort of process: Only after successfull retrieve of the complete server-response (i.e. the complete JSON-array), ONLY then insert the rows into the local database and update the local update timestamp to the actual time. If I've retrieved only some of the JSON rows and the internet connection gets interrupted inbetween (or app is being killed), I would NOT have inserted ANY of the retrieved rows into my local app-database, which means that the next time the background service is running, there will hopefully be no conflicts.

Thank you very much

Upvotes: 1

Views: 1226

Answers (1)

blue
blue

Reputation: 1949

You've mentioned database on client and my guess is that database is SQLite.

SQLite fully supports transaction, which means that you could wrap your inserts in BEGIN TRANSACTION and END TRANSACTION statements. A successful transaction would mean that all your inserts/updates/deletes are fine.

Choosing JSON has a lot of ups and a few downs - its easy for both client and server side. A downside I've been struggling in the past is with big JSONs (a few Mb). The client device have to download all the string and parse it at once, so it may run out of memory while converting the string to JSONObject. I've been there, so just keep that in mind as a possibility. That could be solved by splitting your update into pieces and marking each piece with its number and total number of pieces. Then the client device should know that it'd make a few requests to get all the pieces.

Another option you have is the good old CSV. You won't need the JSON includes, which will save your app some space. An upside is that you may parse and process the data line by line, so the memory impact would be very low. The obvious downside here is that you'll have to parse the data, which might be a problem, depending on your data.

I should also mention XML as an option. My personal opinion is that I'd use only if I really have to.

Upvotes: 1

Related Questions