Implementing database upgrades

Migration utilities

SquidDatabase includes a wide variety of built in methods to help you implement database migrations. Most of these methods begin with "try" and return true if the operation was successful or false if something went wrong. Available utilities include:

• tryCreateTable() & tryDropTable()
• tryCreateView() & tryDropView()
• tryAddColumn()
• tryCreateIndex() & tryDropIndex()
• tryExecStatement()
• tryExecSql()

Most of these methods operate on SquiDB objects. For example, tryCreateTable() and tryDropTable() operate on the Table objects declared in model classes, while tryAddColumn() operates on Property objects.

Of particular note is tryExecStatement(). This takes as an argument one of the SQLite grammar objects for when you need to do a data migration that isn't just changing the database schema but instead changes the actual data stored.

Generally, you will use these utilities in the migration method you define in your subclass of SquidDatabase.

Overriding the upgrade methods

The subclass of SquidDatabase you set up for your app is responsible for managing database upgrades. The two relevant methods are getVersion() and onUpgrade().

When you release a new version of your app and want to change the database schema, you should increment the value returned by getVersion(). You'll probably want to implement the database upgrades using a fall-through switch statement.

Version 1:

public class MyDatabase extends SquidDatabase {

    /* ... other methods ... */

    @Override
    protected int getVersion() {
        return 1;
    }

    @Override
    protected boolean onUpgrade(ISQLiteDatabase db, int oldVersion, int newVersion) {
        // Nothing to upgrade in v1
        return true;
    }
}

Version 2:

public class MyDatabase extends SquidDatabase {

    /* ... other methods ... */

    @Override
    protected int getVersion() {
        return 2;
    }

    @Override
    protected boolean onUpgrade(ISQLiteDatabase db, int oldVersion, int newVersion) {
        switch(oldVersion) {
        case 1:
            // This column was added in v2
            tryAddColumn(User.BIRTHDAY);
        }
        return true;
    }
}

Version 3:

public class MyDatabase extends SquidDatabase {

    /* ... other methods ... */

    @Override
    protected int getVersion() {
        return 3;
    }

    @Override
    protected boolean onUpgrade(ISQLiteDatabase db, int oldVersion, int newVersion) {
        // The fall-through style means that if a user upgrades from v1
        // to v3, and skips v2, all migrations from v1 onward will still
        // be applied in order
        switch(oldVersion) {
        case 1:
            tryAddColumn(User.BIRTHDAY);
        case 2:
            tryAddColumn(User.PROFILE_IMAGE_ID);
            tryCreateTable(ProfileImage.TABLE);
        }
        return true;
    }
}

Some people just want to watch the world burn

You may have noticed that onUpgrade() returns a boolean value. This indicates whether or not the upgrade completed successfully. If you determine that an upgrade could not complete successfully, you can return false (you can check the return values of the "try" methods if you're extra paranoid). Additionally, if the code in onUpgrade() throws any exceptions, this will be caught and be treated as if onUpgrade() returned false.

When an unsuccessful upgrade occurs, two things happen:

1. The onError() method is called. By default this method simply logs the error to Logcat. You should not take any corrective action here.
2. A call to onMigrationFailed() will occur. Inside this method you can take drastic corrective action, such as deleting the database and opening a fresh one with the recreate() method.

Note that it is safe to call recreate() during onUpgrade() as well, but it is NOT safe to call recreate() from arbitrary places in your code. Only call recreate() if you are 100% sure that no other threads might be accessing the database at the same time (e.g. via open cursors). If you violate this rule, horrible crashes may occur.

---

See also:

• Setting up a database for your app
• Other SQLite grammar objects