Changing sqlite3 database data on upgrade

Question

I'm writing an Android app and extending the SqliteOpenHelper class in order to access, create and upgrade the database. In older versions of our app we were saving a domain name to the database. In future versions we want to make a change so that we save a fully qualified url instead of a domain name. (e.g. used to save "example" now want to save "www.example.com"). One requirement is that we not drop any tables during the upgrade so dropping the table that holds this value and then writing the new value as a fully qualified url after the upgrade is not an option.

I know how to alter tables and such on database upgrade, but I'm wondering if there's a way to upgrade the value of data in the database (even better if it can be done conditionally) during an upgrade.

I searched through Sqlite documentation but couldn't find what I was looking for.

One idea I had was to do a query in the onUpgrade() method of SqliteOpenHelper. So something like:

    @Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    for (int i = oldVersion; i < newVersion; i++) {
        if (oldVersion == VERSION_SAVING_DATA_AS_DOMAIN) {
            //Query database for domain
            //Convert domain to fully qualified url
            //Write fully qualified url to database
            //Do database upgrade
        }
    }
}

Not sure if the strategy shown above is bad practice or if there is a more obvious way of fixing this that I'm not thinking of.

Instead of a for loop in there IMO it would be better to have a switch statement with fall through cases for updating everything — tyczj, Oct 27 '17 at 13:55

Kuffs · Accepted Answer · 2017-10-27T13:59:10.197

This would work in the exact same way as upgrading schema. You are only running SQL statements. Simply use Update statements instead of Alter Table etc

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
  if (oldVersion<2) {
    // do upgrade from 1 to 2
  }

  if (oldVersion<3) {
    // do upgrade from 2 to 3, which will also cover 1->3,
    // since you just upgraded 1->2
  }

  // and so on
}

You can use Switch or If statements to check the old version. I personally preferIf as I think it is more readable and the fall through is much more obvious.

Reference: Confusion: How does SQLiteOpenHelper onUpgrade() behave? And together with import of an old database backup?

score 1 · Answer 2 · answered Oct 27 '17 at 14:04

I don't know why you want to use a for loop. You can upgrade your database like in the below example:

// Database Version
private static final int DATABASE_VERSION = 5;
// Database Name
private static final String DATABASE_NAME = "exampleDB";


private DataBaseHandler(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
    this.context = context;
}
[..]

@Override
public void onUpgrade(final SQLiteDatabase db, int oldVersion, int newVersion) {
    if(oldVersion < 3) {
        //code for upgrading to database version 3 and higher
        [..]
    }
    if(oldversion < 5){
        //code for upgrading to database version 5 and higher
        upgradeToFullyQualifiedUrls(db);
    }
}

If your current version is for example 4 and you want to change the urls in your database you set DATABASE_VERSION to 5 and add the SQL UPDATE queries to change the urls after if(oldversion < 5). All versions of your app with a database version lower than 5 will get the new url's and the version will be automatically changed to 5 after onUpgrade.

score 0 · Answer 3 · answered Oct 30 '17 at 20:33

The answers above led me to a solution for my exact problem which I'll post here.

The issue was that I'd written an arbitrary subdomain name to database in a previous database version and wanted to be able to transform every arbitrary subdomain name in the database to a string of the form: "https://arbitrarysubdomain.example.com" Other complications were that in previous database upgrades I'd started writing "https://arbitrarysubdomain.example.com" instead of "arbitrarysubdomain" like I had in the oldest database versions. Finally, it was possible for the existing value to be null or an empty string in which case I wanted to transform the value to null.

Here's the sqlite statement that solved my problem and satisfied the constraints:

UPDATE preferences
    SET subdomain = CASE WHEN ((SELECT subdomain FROM preferences WHERE subdomain LIKE '%.com') IS NOT NULL) THEN
                             (SELECT subdomain FROM preferences WHERE subdomain LIKE '%.com')
                         WHEN ((SELECT subdomain FROM preferences) IS NOT NULL AND (SELECT subdomain FROM preferences) IS NOT '') THEN
                             'https://' || (SELECT subdomain FROM preferences) || '.example.com'
                         ELSE
                             NULL
                    END;

So now when onUpgrade is run and I'm upgrading from the last version that was affected to the new version where I add my fix I execute this statement.

Another statement that I'd want to write after this one would rename my preference table's subdomain column to something else more descriptive.

Changing sqlite3 database data on upgrade

3 Answers3