1

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?

hopper
  • 13,060
  • 7
  • 49
  • 53
oOo
  • 21
  • 3

2 Answers2

2

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.

haventchecked
  • 1,916
  • 1
  • 21
  • 24
  • 1
    SQLite's file format is portable; you could just let the server generate the database file itself (compressing it might be useful). For copying the updates over, [ATTACH](http://www.sqlite.org/lang_attach.html) the update DB to the actual DB. – CL. Aug 06 '13 at 20:22
  • 1
    I wrote up a way to import a `.db` file in Android that you may find useful: http://stackoverflow.com/questions/6540906/android-simple-export-import-of-sqlite-database/6542214#6542214 – Austyn Mahoney Aug 06 '13 at 22:12
  • Thanks for the responses. The comments of CL. and Androidy look promising. An answer to the following question will resolve my problem, I hope: What would be the proper way to create an SQLite database from Oracle database, that I can create periodically on Oracle server side and download to Android device to use directly? – oOo Aug 07 '13 at 16:03
  • Kerli, you may still run into the problem of having a long-running update operation. 5MB can take anywhere from 5 seconds to a few minutes depending on the user's mobile network quality. An HTTP request for the changes only can be as fast as just a few hundred milliseconds depending on the size of the payload. For your approach, write a server-side application to read the contents of the oracle database, create the schema in a new sqlite database, and then insert the records. If I were trying to accomplish this I'd first try using system calls to create the db and then JDBC to execute SQL. – haventchecked Aug 07 '13 at 17:07
  • Thanks, haventchecked. Using HTTP request is an option that I already implemented. The reason of my being after other options is that we have to keep the application alive even if there is no data connection at all. As for using the JDBC, I have no clue, and should do some readings about it. I'll update this page with my solution. – oOo Aug 08 '13 at 00:51
  • I only suggest using JDBC because I'm assuming you have Java experience working on your android app. You really just need a database driver in any language to bulk insert records into the SQLite db. – haventchecked Aug 08 '13 at 11:05
0

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

oOo
  • 21
  • 3