将Android设备上的数据库同步到远程主数据库

Question

I have a SQLite database on an Android Device and a MySQL remote master database. The Android device user can modify the database (i.e add, delete, modify) records. What I want is to automatically sync the database on the Android device to the master database whenever the Android device is online. After syncing the records on the master database should reflect all the addition, deletion or in some cases modification that has taken place.

Okay so here's what Im thinking of doing. In the Databasehelper class that I've created, I am going to add code in each method so that each time a method is called and the corresponding query executed in the local android database, that query string which was executed gets stored in a simple text file. So changes can keep happening in the local database and query strings will keep getting stored in the file. It'll basically create a SQL Dump (right?). Now when I write my JDBC MySQL connection code on the client side, I'll get it to read one line at a time from that file and execute the queries on the master MySQL server. How does it sound? I guess it takes care of deletion too.

But the following doubts emerge:

1) Will JDBC Connector for MySql work on an Android device? I've browsed on the net and found conflicting answers. Some say it doesn't work. Some says you just have to add the external jar file mysql-connector-java-3.0.17-ga-bin to the build properties of the project. Really confused about this. Have no idea of PHP so I wish this works.

2) After connecting, I don't think I need any results back from the MySql master database. I just need to execute the sql dump ive stored in the text file on the master database. Sounds easy enough right?

3) Now for the other way sync. The good thing I found on Sqlite is that even they accept SQL queries. So for the other way sync, I'll need to get the Android device to download a Sql Dump file in a particular folder and then get the database adapter ive created to read from the downloaded file and execute the queries. This is sounding far tougher. Any ideas to do this? One thing is sure that the SQLite database will not allow connection to a remote server.

Some of the doubts may seem poor but I really need your help guys. As you can see I'm a noob in Databases.

Answer 1

One approach you might try (thinking loudly here) is to:

• include a client id in each of your tables to track which client device they below to and making it part of the unique key (assuming you've got multiple devices supported to sync against the server)
• you should also include a client sync status on each database row that allows you tracking if updates to the server were successful
• you also need to track if your client rows were updated after initial transfer
• could you then select all updated client rows to the server upon connection

All this will get much more complicated if

• you want to support deletion
• you want bidirectional replication
• you've got harder performance requirements