0

I am creating an Android app for which I need to create a SQLite DB and pre-populate it with some values.

The Android documentation says this about what to do in "onCreate" of the SQLiteOpenHelper:

Called when the database is created for the first time. This is where the creation of tables and the initial population of the tables should happen.

Reference - http://developer.android.com/reference/android/database/sqlite/SQLiteOpenHelper.html#onCreate(android.database.sqlite.SQLiteDatabase)

I am doubtful about the following 2 things -

  • What is meant by "when database is created for the first time"? Is this done on the first launch of the app or only when the first DB request (read/write etc) is done.

    If it is the latter, I fear that it may take quite some time to create DB, pre-populate it with values (I have about 60 rows to be inserted into 1 table) and then read the DB to show it. Is this the best practice?

  • I have been doing all my DB operations in AsyncTasks. But I am doing the table creations in onCreate using "db.execSQL" statements. Is this fine (in terms of convention/ performance) or should I go for an AsyncTask here as well?

Any help is appreciated.

Community
  • 1
  • 1
magicmushroom
  • 41
  • 6
  • "What is meant by "when database is created for the first time"? Is this done on the first launch of the app or only when the first DB request (read/write etc) is done." -- it is when you call `getReadableDatabase()` or `getWriteableDatabase()` on the `SQLiteOpenHelper` and the database file does not already exist. – CommonsWare Oct 04 '14 at 18:51
  • Okay.. but in that case, will that not affect the performance? Alternatively, should I ship the app with a pre-populated DB? It seems to be quite a tricky task and I would like to avoid it I think. – magicmushroom Oct 04 '14 at 20:03
  • "will that not affect the performance?" -- well, you have to open the database *sometime*. 'Alternatively, should I ship the app with a pre-populated DB?" -- oh, if that's an option for your use case, I would definitely do that, [using `SQLiteAssetHelper`](https://github.com/jgilfelt/android-sqlite-asset-helper). "It seems to be quite a tricky task and I would like to avoid it I think" -- it's not that tough, IMHO. – CommonsWare Oct 04 '14 at 20:06
  • Thanks! Will try SQLiteAssetHelper – magicmushroom Oct 13 '14 at 18:18

3 Answers3

1

1) The later. It is done on the first read or write to the DB.

Your fear might be correct, this is why you can ship your app with a database that's already populated. Or you can launch an AsyncTask with a simple SELECT 1 FROM anytable query. More about shipping with DB here. (60 rows is nothing to fear about tho, and you can safely just keep using AsyncTasks).

2) Yes it is fine. The onCreate logic will run when you first read/write the DB, so it if you always use AsyncTasks onCreate will run in an AsyncTask also.

Community
  • 1
  • 1
VM4
  • 6,321
  • 5
  • 37
  • 51
0

What is meant by "when database is created for the first time"? Is this done on the first launch of the app or only when the first DB request (read/write etc) is done.

  • It happens when you first query from database in general term. After that only Upgrade method is called that too when you change the db version.

If it is the latter, I fear that it may take quite some time to create DB, pre-populate it with values (I have about 60 rows to be inserted into 1 table) and then read the DB to show it. Is this the best practice?

  • 60 rows insertion is not a big task. More you can read about beginTransaction(),commitTransaction and endTransaction for insertion. It will make your insertion task lighting fast.

I have been doing all my DB operations in AsyncTasks. But I am doing the table creations in onCreate using "db.execSQL" statements. Is this fine (in terms of convention/ performance) or should I go for an AsyncTask here as well?

  • It good you are doing you Db operation in AsyncTask and its completely fine.
Suhail Mehta
  • 5,514
  • 2
  • 23
  • 37
  • Yes, I am doing the initial population of DB using beginTransaction(), setTransactionSuccessful() and endTransaction() in a try-finally bracket. How does this fare compared to AsyncTask (using cursors etc) in terms of performance? – magicmushroom Oct 04 '14 at 20:00
  • Thanks for the "beginTransaction..." tip... markedly improved the speed! – magicmushroom Oct 13 '14 at 18:20
0

Speaking of DB operations:

Performing DB operations in AsyncTask is not a good approach, generally. As you might encounter a problem called "memory leak", and it might come as a silent assassin in the night.

There's lot written on this issue. Just google "asynctask leak context" and here you go.

So how to perform DB operations?

Using Loader API in conjunction with ContentProvider is considered good approach for querying database. Loader asynchronously queries your database and delivers the result to specified subscribers. Configuration changes or other sudden stuff does not bother it.

And it is really convenient to query your data using loader API once you know how to do it.

Single inserts/updates/deletes might be done directly from the main thread via ContentResolver. These calls will be blocking (synchronous), but I bet you user would never notice anything while the amount of data is not large.

If you're operating on a large dataset, and you fear you'll be significantly blocking UI thread, I'd suggest using IntentService or any custom Service capable of doing operations in background (note that by default Service operates on main UI thread and you have to specify background operation yourself or use IntentService)

Speaking of DB initialisation:

You might create a one-time IntentService, if you're initialising a large set of data. It will handle your request asynchronously and, for example, perform a broadcast that the application is set up and ready, so you might stop a "wait a sec, performing app initialisation" screen and show user your data.

There's also nothing wrong with shipping your database along with application, though it appears to be a bit hackish solution.

Either way, you choose what is more suitable for you.

Drew
  • 3,307
  • 22
  • 33