I am working on updating the database on a Mobile device which is using SQLITE db, which should get updated as server updates it's database i.e wamp server.
Can anyone suggests me any ideas on how to achieve this.
I don't want to read the whole server database as it would increase the Data Usage while reading the whole database just for a single update or for multiple update.
Update is done in the product table and only the price field is updated by server side.
-
To sync data between client and server RESTful API is a common way to achieve it. You can cache your updates for another user requesting same update. Anyway what's wrong with reading database?biegleux– biegleux2012-07-14 06:58:58 +00:00Commented Jul 14, 2012 at 6:58
-
Reading the database will create data usage if I am able to read a value of a single field it will use much less data usage comparativelySumit– Sumit2012-07-14 07:01:28 +00:00Commented Jul 14, 2012 at 7:01
-
I am thinking of a creating a separate table which will maintain the update details and user will read only those details for updating his database.. what do you think is this a good practice?Sumit– Sumit2012-07-14 07:04:07 +00:00Commented Jul 14, 2012 at 7:04
-
1It's duplicating of your data. You can hold ids of updated items in a separate table and after requesting an update reading updated data from main table, ids are indexed so it won't read whole database. But you have to be aware there may be various users with various out-of-date databases in their devices.biegleux– biegleux2012-07-14 07:39:29 +00:00Commented Jul 14, 2012 at 7:39
-
1Another option is to add new column to your table like update_time as unix timestamp and create index on it. User will keep it's local last upadete time and when requesting synchronization, server will response with items with newer update_time. Again if column is indexed, server won't traverse whole tablebiegleux– biegleux2012-07-14 07:42:51 +00:00Commented Jul 14, 2012 at 7:42
|
Show 1 more comment
1 Answer
You can define a service in your app that periodically asks if there are updated data in your server db. On the server side you can implement a web-service that will receive a json object in which you will put the current date, the table name you want to check for updates and other info based on your purposes.
I'll explaine myself better with an example:
1) when the app starts a background service will also start. This service will query (for instance every 3 minutes) your webservice to see if there are new updates for a specified table.
2) The webservice will receive the table name you want to check and the datetime preferably in unix timestamp. For instance you want want to see if there are new records for the table "products" after 2012-08-20 22:00:00 . You can create a json object and a http request with this information within your app and pass it to the server side.
3) Your web-service will respond to you giving a json array with all the data that are being added or modified after 2012-08-20 22:00:00. Of course in the server db you have to store this information (basically each record will have a field with the datetime of first insertion / last modification)
4) You can then update your local sqlite database.
Probably this is not very efficient but it works.
Andrea
Comments
