0

This probably has an answer already, I have checked but I'm not sure what to look for "Android SQLite onUpgrade do work"? I did some reading a few weeks ago and kind of put off the problem.

Basically I need to do some work when the database is upgraded! Shock horror right (going from the number of questions with onUpgrade in them I am far from alone) but in my case this means spawning various activities, it could be rather a large job.

I have learned though it is often best to go "with the grain" of Android stuff.

I don't like onUpgrade and onDowngrade These assume that upgrading is a function that has an inverse, and also that the work has a low complexity as to not take much time regardless of database size. You do not get a chance to report back to the activity to signal it to do something like "Show that spinny loading thing", you are stuck on the GUI thread (Main thread?).

I also don't think it's a big enough system to be helpful, especially if you are using database tables as an array of structures type thing. I'll expand on this if requested.

SO: Where do I do work? What is the grain I am struggling to go with regarding onUpgrade?

My solution: I plan to scrap onUpgrade and co, instead storing a revision number in my settings table (key value pairs basically), I will then compare this stored number to a constant containing the latest version, and either throw, letting the activity catch, or check to see if it is ready.

This means I can upgrade how I like, but it does mean I have to wrap SQLite in my own little layer, this isn't a bad thing, I have wrapped tables already, I don't think one ought to deal with cursors directly, but that's another thing.

Is there something I'm missing or should I just take upgrading into my own hands.

I ask not because it's a huge task, because as you can see it's not, but why does onUpgrade exist? Am I missing something?

Thanks.

Addendum: I tried going "full android" Correctly using onUpgrade (and content providers) to handle updates without blocking the main thread, are `Loader`s pointless? here but backed off because ContentProviders were overkill but that question is poorly phrased, just for the sake of full disclosure.

Addendum 2

Suppose the upgrading process requires user input, and the upgrade handler will want to start an activity, I cannot return from onUpgrade until the database is at the target version. How do I go about doing this?

Community
  • 1
  • 1
Alec Teal
  • 5,770
  • 3
  • 23
  • 50
  • If the app has a lot of data in a database and you're changing the schema of the database, you could use onUpgrade to migrate the data to the new database, rather than dropping all the tables and downloading all of it. If you need to do a lot of work, you could push it all off in an AsyncTask to get off the GUI thread – JustinDanielson Sep 20 '13 at 23:32
  • @JustinDanielson but if I push it off to a handler thread the GUI one will return from getWritableDb (or whatever that function is called) with a SQLite* class I do not control, yet the DB will be in flux. It's also not being downloaded, the problem is migrating from the old to the new. I like my second solution though. I'm planning it right now. – Alec Teal Sep 20 '13 at 23:34
  • Have you thought about using an ORM solution, like ORMLite? – Christopher Perry Sep 20 '13 at 23:38
  • @ChristopherPerry I'll have to look into that, the problem is not with SQLite though it's crossing the "Hello world gulf" as I call it, examples are great, real world is different however. – Alec Teal Sep 20 '13 at 23:40

1 Answers1

0

I use onUpgrade in my applications and think it works well. I do take great care to ensure that the changes I'm implementing in onUpgrade are very simple though, like table creation or altering. I would keep all structural changes to the database in onUpgrade and handle changing the data via a different method. This separates the stability of your SQL from the content the user wants displayed, which you may want the user to change that data at any point.

A simple onUpgrade for me might look like this:

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    if (oldVersion < 3) {
        createTranslationTable(db);
    }
    if (oldVersion < 4) {
        createStreamingColumns(db);
    }
}

If the users old version of the DB is 2 then both updates to the db will ran.

It sounds like you need more than a simple "Upgrading, Please Wait" on your splash screen while data downloads, so you would simply push the user off to another activity to gather your required input and then initiate the upgrade. You can still keep structural changes in onUpgrade but do your other work outside of the standard upgrade flow.

There are always problems that will require a more complex solution and cause you to go against the grain, but this is the standard approach I try to use.

JustinMorris
  • 7,259
  • 3
  • 30
  • 36