Android Question Background sync for database?

It's not possible to do what you want assuming your local database is SQLite. What is possible in Android is to have an App with a sticky service module to do the syncing. Your sticky service has to handle all database transactions from your app as well as the sync functionality. Take a look at the CallSubDelayed as to how to do this. The reason you have to do it this way is SQLite only supports one connection to a database. Concurrent writing from two connections has a very high chance of corrupting your database.

You should also ensure your database has Write Ahead Logging enabled and look at using AddNonQueryToBatch, ExecNonQueryBatch and ExecQueryAsync to perform all your CRUD operations on your DB.

Thanks,
I had a feeling that having the 2 apps use the database wouldn't work, so that helps me a lot.
I will review what you noted here.
Thanks again.

You can start a service with "StartAt" (f.e. every x minutes - any time will do). After that it's all about identifying the data. Each row MUST have it's own unique identifier (f.e. IMEI plus full timestamp for the device's db and SERVER1 plus full timestamp on the Server side. This is good because you see who first inserted the row) a timestamp of "last change" for every row plus "last sync" stored by the service. After that it's easy:

- Select all rows which have a "last change" > "last sync" (only those haven't been synced yet) and then on the other side:

Does this row exist? If no: Insert it, If yes: Update it

Do the same Select on the remote db

You can use this same procedure on x devices. At the first start set "last sync" to 1.1.1900 (as a full timestamp) so all rows are new and all will be inserted.

The device user has the liberty to change his Device's date and time at his will. So if the device user has a wrong datetime on his device, whenever an update or insert is done in the DEVICE's table, it is going to store incorrect timestamp and finally you may end up in a big mess while comparing the timestamp between the data on the device with the data in the remote DB for the Sync.

You need to consider this. So how do you ensure that the date and time on the device is having the right/correct date time ? Obviously for each insert or update on the device you need to pick the DateTime stamp from some Internet time server, which in-turn it will put you in a situation that Internet access is required on the device to do this. If you already have internet connectivity on the device, then why can't you store the data on the remote DB itself. Why these complexities ?

Any other ideas are welcome.

Lot of complexities are involved in this. I am also looking for a simple solution.

This was just ONE idea. Of course you've got to do more than this 6 lines of code to have a solid solution.

I only have one remote db on my servers. I never need to sync. But that wasn't the question here. Of course one can change the date but why should he? For that unlikely case I would store a "last run" timestamp and just stop the app with a message. By the way: Try to change the date on your WIN-PC to 1970 and see what happens Switch off the time-sync before.

If you need to use a remote db, use RDC or follow my examples with php/MySQL (see signature).

Anser said:

Lot of complexities are involved in this. I am also looking for a simple solution.

Click to expand...

There isn't one. Using a full RDBMS then one popular way of syncing data is to use hashing. SQL Server has CHECKSUM, BINARY_CHECKSUM and HASHBYTES; MySQL has MD5 checksum. So each row in a table has a unique(ish) checksum column. So you can now compare checksum columns between two tables to identify differences.

Say you have 1 million records in your table. You would still need need to transfer 15mb of data to the server if you were storing an MD5 hash in a column (128 bytes per row). So as well a having a hash per row you use an aggregate hash function as well. So to sync from the app:

1. Generate an MD5 hash for groups of 10000 rows in your table.
2. Send your 128k of hash data to the server.
3. Server generates it's hashes for it's rows in groups of 10000.
4. Server compares it's hashes with hashes from the app.
5. Server sends response to App telling it which hashes are different.

Lets assume you only have one group which has a different hash.

5. Generate another hash for each group of 1000 rows in the ten thousand grouping.
6. Send result to the server.
7. Server generate it's hashes for it's groups of 1000 rows.
8. Server sends response to App telling it which hashes are different.

Again assume you only have one group which has a different hash

6. Generate another hash for each group of 100 rows in the one thousand grouping.
7. Send result to the server.
8. Server generate it's hashes for it's groups of 100 rows.
9. Server sends response to App telling it which hashes are different.

Again assume you only have one group which has a different hash

8. Generate another hash for each group of 10 rows in the one hundred grouping.
9. Send result to the server.
10. Server generate it's hashes for it's groups of 10 rows.
11. Server sends response to App telling it which hashes are different.

Again assume you only have one group which has a different hash

12. Send the checksum column for each of the 10 rows to the server.
13. Server compares the 10 hashes from the app with it's 10 checksum columns.
14. Server sends response to App telling it which rows are different.
15. App sends row which are different to server.


This is a very simplified version compared to what you might actually implement but hopefully explains the approach. In an actual implementation you may chose to use a weighted grouping of the rows. For example on the basis that older rows are less likely to be changed than newer rows.

One of the big issues is that SQLite has no hash functions built in. However Spatialite does have a MD5 and a MD5 aggregate function but unfortunately the version wrapped for B4A doesn't. If I was looking at doing a project like this using SQLite and the hashing approach I would be asking @warwound very nicely if he could possibly update his library to wrap version 4.03

Check CloudKVS: https://www.b4x.com/android/forum/threads/63536/#content