Android Local Database Update

Question

I am currently having problems with a SQLite database problem on Android.

My application has a local database, which is located inside the apk file. When the application starts, it checks for a new version and loads a completely new database, if available (although there are very few changes between the two versions of the database). But the database is now too large. So it takes a lot of time when a new database is available. So, any solution for this problem?

Answer 1

main idea

This is how I do it. I assume that the client application does not make changes to the local database (unless it downloads the new version), so there are only a few possible versions of the database (one for each made a change on the server).

• Add a column to each table named LastModified with a default value of NOW() . This means that every time you add something to your main copy, it gets an updated LastModified setting. You will need to make sure that your updates (and not inserts) also modify the LastModified field.
• Store somewhere in the database (a Settings table or something else) a field that tracks the publication date of this version of the database on the server (name it PublishDate ).
• When a client wants to check the new version, he sends it to PublishDate to the server. The server then checks each table and finds each row where LastModified appears after PublishDate . It sends SQL to the client to insert or update these rows on the client. It also sends a new PublishDate so that the client can update it in its local copy.

This applies to inserts and updates. This does not apply to deletion. Perhaps this is not a problem in your case; if they:

4. Add a separate log deletion table, where you also track LastModified so you can tell the client which rows to delete; or it is preferable to have a setting in which you never delete any lines, but simply update them so that they are marked as “deleted”.

Finally, this will not handle schema changes. Again, I hope this is not a problem in your case: I hope you have a stable circuit. But if you need to add or delete tables or indexes or something else, you need to do this separately:

5. Create a SchemaChanges table on your host and whenever you make structural changes, put the appropriate data in the SchemaChanges table along with the LastModified date LastModified that you can send it to the client on request too. If you do this, first you will want to first send the schema changes to the client, because they can affect the meaning of the other changes.

Now the good thing is to do this so that you can pre-process everything on the server (because there are only a few versions). For each old version, you can calculate the changes (based on the details above) that will use this old version until the new version, and then save the resulting SQL on the server. If you do this, you avoid the need to generate SQL on the fly: when the client sends PublishDate , you simply look at the SQL that you have already calculated, which will convert the version from this PublishDate to the latest version.

Alternative implementation

There is a good and easy way to push the changes that the above diagram gives you, even with a little simplification that does not require LastModified times, as well as no changes to your existing structure. On the server, where you already have the old version (because you have all the old versions) and the new version, you dump SQL for both databases, and then run diff on top of them to create a patch file that you can send client attachment. The client application will use the same Java library to dump the old version of SQL and then apply the diff patch to it to create a full SQL dump for the new version. At this point, he can delete the old database and create a new one from the SQL dump.

This will be very effective if the changes are not optional changes (in this case, you can simply click on the new .db file).

This is fairly easy to do with a SQLite binary call to dump. You will need to slightly change the approach for Android in accordance with this way of executing an external command.

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

Answer 2

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

Answer 3

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

When the application connects to your server, it sends the last timestamp so that your server knows what new data needs to be downloaded.

Timestamps are not set on the device depending on the real time, you also need to download it to avoid problems with the time zone.

You can use consecutive versions of numbers if you want instead of timestamps

Answer 4

I am working on a similar problem when I have to update the local database every time any changes occur on the server. The trick is to track the last updated time in the local database in Android and send this time to get only updates from the server in JSON format. Here you will need to write server-side code that accepts input as the last updated time and returns all updates made to the server database in JSON format.

After receiving the JSON data, you need to convert the JSON data into SQL queries and insert or update in the local database.

Check out this answer. And also see the Google IO 2014 code here .

I ended up using ContentProvider with schemas and contract classes, as described here .

Then I created a java object for each table in my database and used the gson library to convert incoming JSON updates to a java object. After that, you need to write the inser / update / delete requests specified in the documentation .

Therefore, you must create classes for contracts, databases, and a provider for database processing. And the java classes are one for each table in your database, and you have code to retrieve JSON updates from the server, convert the JSON string to a java object, and then insert / update / delete in the database. The demo code is available in the Google IO 2014 app.

Answer 5

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

Answer 6

From your question it is not clear if you want

• bidirectional synchronization (client and server can make data changes) or only
• unidirectional synchronization (only the server or client can make changes).

For me, it looks like you want unidirectional synchronization from server to client. This is exactly what Google I / O does with conference data. You can find a detailed blog entry on how this works here:
“Synchronizing conference data” and “GCM” in the Google I / O application , just look at the “Conference data download performance” chapter for manifest.json , sessions.json and speakers.json . If the data schema changes, just specify a new application that performs the schema change in the standard way of android and creates your json parsing procedure so that it ignores additional fields. As a supplement:

• The blogentry that the Google App I / O App provides,
• Github code itself

If you want bi-directional synchronization, chiastic-security provided a good overview for database operations. The software part of the solution is missing:

• Presentation of Virgil Dobrzanski: Development of REST client applications for Android was presented at Google I / O 2010
• The presentation led to a lot of questions, because the code was not presented, see, for example, this discussion: You need to select a client REST client project that implements a model of REST implementation by Virgil Dobdzanski
• Nice picture from architecture (SyncAdapter approach): here
• and a very good blog post about write your own sync adapter
• and you will find some information in the Google blog entry (first link for unidirectional synchronization)
• and some code in this presentation is slide 31ff

Hope this helps.

Answer 7

You can do this through the Google Cloud Messaging API. This is a feature provided by Google developers that works on both Android and iOS devices. Google Cloud Messaging (GCM) is a service that helps developers send data between online servers and applications. Using this service, you can send data to your application when new data is available, and not for new server requests in a timely manner.

Great tutorial on using GCM this .

Hope this helps :)

Answer 8

Perhaps you can use services to change the version of the database. Thus, the user cannot notice this, and the user continues to use the application. When the service completes, the application will send a notification. Of course, there is only approval and advice.