CODEWITHSUNDEEP
androiddatabasesqliteupdates
oOo
oOo

Reputation: 21

Updating a sqlite database from text file

I am new in database development, sorry if this is inconvenient to ask. I am developing an Android application, the database of which has a table with thousands of entries. This table is generated from a backend Oracle database and has to be updated with the changes in the backend database every time an update button is clicked on the Android app.

The procedure that I came up with is here:

  1. A text file is generated from the Oracle database by running an SQL script every three hours.

  2. Each time it is needed, the Android application downloads this text file, dumps the old table, and parses it into the new table.

My problem is that since the text file is very big with thousands of lines (around 5MB), downloading and parsing takes a very long time, but it needs to be (almost) instantaneous.

My questions are:

  1. Is there a better way to update the SQLite database from the Oracle backend efficiently?

  2. Since parsing takes too long, is there a way to setup SQLite to work with the text file, skipping parsing?

Upvotes: 1

Views: 2402

Answers (2)

oOo
oOo

Reputation: 21

I figured it out as follows: 1 - I run a script on the server-side that generates the tables in csv format which are used by the android app;
2 - and another script that imports the csv data and creates an SQLite database. 3 - The android app checks the server for a newer database. If it exists, it downloads it and makes it available to the app as it is explained here

Upvotes: 0

haventchecked
haventchecked

Reputation: 2016

If possible, you should expose a web service that connects to the database instead of downloading the entire contents each time. This web service should have a GET method that allows you to query for the rows that have updated within a specified amount of time. In your android app, you can keep the Date of each update and then the web service call will restrict the rows returned based on this WHERE clause. Something along the lines of this:

SELECT MT.* FROM MY_TABLE AS MT
WHERE MT.last_modified > last_requested_time;

This allows you to only download the updates. Once you have the updates in your android app, you can make SQL calls to the SQLite engine and update the necessary rows based on the primary key.

In regard to your second question, I don't think that file-based loading is the proper approach (after the initial load) because it causes a large amount of mobile data to be consumed potentially plus it creates a high CPU load on the phone which is not desirable for mobile.

Upvotes: 2

Related Questions