Update local Android database

Question

I'm currently in trouble with a SQLite database problem on Android.

My application have an local database which inside an apk file. When the application starts it will check for the new version, and download entirely new database if available (although between two database version, changes are very little). But the database is too large now. So it takes very long time when new database available. So any solution for this problem?

With AsyncTask you can do on background large operations and recover the progress to show it to the customer or show a notification when the task is done. http://developer.android.com/reference/android/os/AsyncTask.html — CaptainTeemo, Sep 22 '14 at 08:13
Implement an API which takes the time of the last sync into consideration and only downloads the updated/new values? Dont need the drop table if exists in onUpgrade, just call the api and ask? — David Olsson, Sep 25 '14 at 09:37
[Try this](https://github.com/jgilfelt/android-sqlite-asset-helper) — Nadeem Iqbal, Sep 28 '14 at 07:57
Yeah, that what i'm thinking about but any tools on mac os or java lib support it? — Cao Manh Dat, Sep 29 '14 at 09:42
Does the client app ever make changes to the local database (other than when it downloads a new version from the server)? — chiastic-security, Oct 01 '14 at 20:12

score 5 · Accepted Answer · edited May 23 '17 at 12:10

Basic idea

Here's how I'd do it. I'm assuming here that the client app doesn't make changes to the local database (except when it downloads a new version), so that there are only a few possible versions of the database in existence (one for every time you've made a change at the server end).

Add a column to every table called LastModified, with a default value of NOW(). That means that every time you add something to your master copy, it'll get an updated LastModified setting. You will have to make sure your updates (rather than inserts) change the LastModified field too.
Store somewhere in the database (a Settings table or something) a field that tracks the date that this version of the database was published on the server (call it PublishDate).
When the client wants to check for a new version, it sends its PublishDate to the server. The server then checks each table, and finds every row where LastModified comes after PublishDate. It sends SQL to the client to insert or update these rows on the client. It also sends the new PublishDate so that the client can update that in its local copy.

This deals with inserts and updates. It doesn't deal with deletions. It might be that they aren't an issue in your case; if they are:

Add either a separate table to log deletions, where you also track LastModified, so that you can tell the client which rows to delete; or preferably have a setup where you don't ever actually delete any rows, but just update them to be marked as "deleted".

Finally, this won't handle schema changes. Again, hopefully that isn't an issue in your case: hopefully you have a stable schema. But if you do need to add or drop tables or indexes or something, that will have to be done separately:

Create a SchemaChanges table on your master, and whenever you make structural changes, put the relevant details into the SchemaChanges table, along with a LastModified date, so that you can send this to the client on request too. If you're doing this, you'll want to send schema changes to the client first, because they might affect the meaning of other changes.

Now the nice thing about doing it this way is that you can pre-process everything on the server (because there are only a few versions in existence). For every old version, you can calculate the changes (based on the details above) that would take that old version up to the new version, and then store the resulting SQL on the server. If you do that, you avoid the need for generating the SQL on the fly: when the client sends the PublishDate, you just look up the SQL you've already calculated that transforms the version from that PublishDate to the latest version.

Alternative implementation

There is a nice and easy way of pushing the changes that the above scheme gives you, even with a slight simplification that doesn't require LastModified times, or indeed any changes to your existing structure. At the server end, where you already have the old version (because you have all the old versions) and the new version, you create an SQL dump of both databases, and then run diff over them to generate a patch file that you can send to the client app. The client app will use the same Java library to generate the SQL dump of the old version, and will then apply the diff patch to it to create a full SQL dump for the new version. At that point, it can drop the old database and create the new one from the SQL dump.

This will be very efficient if the changes aren't wholesale changes (in which case you might as well just push the new .db file).

It's fairly easy to do this by invoking the SQLite binary to create the dumps. You will need to modify the approach slightly for Android, according to this way of executing an external command.

You can use this Google library to calculate the diff patches at the server end and apply them at the client end.

Do you have any tool to generate sql file to transfer from old db to new db? — Cao Manh Dat, Oct 02 '14 at 07:39
@CaoManhDat I've added another section that talks about this, and simplifies things somewhat by using SQLite dumps and a Google `diff` library to calculate the difference. — chiastic-security, Oct 02 '14 at 08:49

score 4 · Answer 2 · answered Sep 28 '14 at 06:59

4

Instead of getting an entire new database file, get the changes. Changes may be in the form of an SQL script. Let server generate the change script for every update, and then you can download the SQL scripts and run them on local database in sequence.

answered Sep 28 '14 at 06:59

S.D.

29,290
3
79
130

Yeah, that what i'm thinking about but any tools on mac os or java lib support it? – Cao Manh Dat Sep 28 '14 at 12:48
You don't need any libs. All you need to do is to amend the SQL query to only get the changes. – Michele La Ferla Sep 30 '14 at 07:33
More specific please :( – Cao Manh Dat Sep 30 '14 at 09:21

score 3 · Answer 3 · answered Sep 29 '14 at 19:37

You need to create a timestamp on any changes you do to your database on your server.

When the app connects to your server, it sends the last timestamp downloaded so your server knows what is the new data to download.

Timestamps are not set on the device based on the real time, you need to download it as well to avoid timezone problems are different hour values.

You can use consecutive number versions if you prefer instead of timestamps

score 3 · Answer 4 · edited May 23 '17 at 10:27

I have been working on a similar problem where I have to update local database every time there are any changes on server. The trick is to keep track of last updated time in local database in android and send that time to get just the updates from server in JSON format. Here, you will have to write server side code that takes input as last updated time and returns all the updates made to server side database in JSON format.

After getting the JSON data, you have to convert JSON data to SQL queries and insert or update in local database.

Have a look at this answer. And also look at Google IO 2014 code here.

I ended up using ContentProvider with schema and contract classes as explained here.

Then I created java object for every table in my database and used gson library to convert the incoming JSON updates to the java object. After that, you have to write inser/update/delete queries as given in documentation.

So basically, you have to create contract, database and provider classes for database handling. And java classes one for each table in your database and you have write code for fetching JSON updates from server, converting JSON string into java object and then insert/update/delete in database. Demo code is available in the Google IO 2014 app.

score 3 · Answer 5 · edited May 23 '17 at 10:34

What isn't clear from your question is if you want

bidirectional sync (client and server can make data changes) or only
unidirectional sync (only server or client can make changes).

For me it sounds more like you want unidirectional sync from server to client. This is exactly what the Google I/O does with the conference data. You can found a detailed blog entry how this works here:
Conference Data Sync and GCM in the Google I/O App, just look at the chapter 'Downloading Conference Data Efficiently' which deals with manifest.json, sessions.json and speakers.json. If the data schema changes, just provide a new app which performs the schema change in the standard android way and make your json parsing routine in a way that it ignores additional fields. Just as addition:

a blogentry which provides Google I/O App Insights
The code itself on Github

If you want bidirectional sync then chiastic-security provided a good overview for the database operations. What is missing is the programmatically part of the solution:

a presentation was given at Google I/O 2010 by Virgil Dobjanschi: Developing Android REST client applications
The presentation lead to much questions since no code was presented, see for example this disscussion: Need sample Android REST Client project which implements Virgil Dobjanschi REST implementation pattern
A good picture from the architecture (SyncAdapter approach): here
and a very good blog entry about Write your own sync adapter
and you find some information in the Google blog entry (first link for the unidirectional sync)
and some code in this presentation - slide 31ff

Hope that helps.

score 2 · Answer 6 · answered Sep 29 '14 at 19:00

2

Using greenDao you can perform such Migrations, Check it here. Good luck.

answered Sep 29 '14 at 19:00

Eefret

4,724
4
30
46

score 1 · Answer 7 · answered Sep 30 '14 at 11:55

You can do this through Google Cloud Messaging API. It is a feature given to developers by Google which works on both Android and IOS devices. Google Cloud Messaging (GCM) is a service that helps developers send data between online servers and apps. Using this service you can send data to your application whenever new data is available instead of making new requests to the server in timely fashion.

A great tutorial for using GCM is this.

Hope it helps :)

score 1 · Answer 8 · answered Oct 02 '14 at 08:13

1

Maybe you can use services for to change database version. Thus user can't notice that and User continue to use the application. When service finished it work, Application send a notification. Of course there are only approeach and advice.

answered Oct 02 '14 at 08:13

Olkunmustafa

3,103
9
42
55

Update local Android database

8 Answers8

Basic idea

Alternative implementation