Is the onUpgrade method ever called?

Question

Is the onUpgrade method of SQLiteOpenHelper ever called? If so, when is it called and by what? If it is not called by the developers, then why is it there? What really happens with that function? I have seen examples where it drops all the tables, but then a comment says that dropping all the tables is not what you should do. Any suggestions?

Answer 1

For those of you who would like to know the exact moment when onUpgrade() gets called, it is during a call to either getReadableDatabase() or getWriteableDatabase().

To those who are not clear how it ensure it gets triggered, the answer is: It is triggered when the database version provided to the constructor of SqLiteOpenHelper is updated. Here is a example

public class dbSchemaHelper extends SQLiteOpenHelper {

private String sql;
private final String D_TAG = "FundExpense";
//update this to get onUpgrade() method of sqliteopenhelper class called
static final int DB_VERSION = 2; 
static final String DB_NAME = "fundExpenseManager";

public dbSchemaHelper(Context context) {
    super(context, DB_NAME, null, DB_VERSION);
    // TODO Auto-generated constructor stub
}

now to...onUpgrade()

@Override
public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
    sql = "ALTER TABLE " + fundExpenseSchema.Expense.TABLE_NAME + " ADD COLUMN " + fundExpenseSchema.Expense.FUNDID + " INTEGER";
    arg0.execSQL(sql);
}

Answer 2

if your are using the SQLiteOpenHelper the onUpgrade will be called whenever you change the DB version. There is an additional requirement for this to work. The db name has to remain the same.

Old Version:
dbName = "mydb.db"
dbVersion = 1

New Version:
dbName = "mydb.db"
dbVersion = 2

in the onCreate of the content provider you create an instance of the SQLiteOpenHelper that takes these params. Your SQLiteOpenHelper implementation would look like this:

public static final class MySQLiteOpenHelper extends SQLiteOpenHelper {

        public MySQLiteOpenHelper(Context context, int dbVersion, String dbName) {
            super(context, dbName, null, dbVersion);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            //Code to create your db here
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            // Code to upgrade your db here
        }

}

Answer 3

It is called when you construct a SQLiteOpenHelper with version newer than the version of the opened database. What to do depends on the changes in the database that are made between the old and new versions. The only case when you don't drop a changed table is when the change is noting more than an added column. Then you can use ALTER TABLE statement to add the new column to the table signature.

Answer 4

Reviewing all of the posts and running debug code it still was not clear to me when I would see onUpgrade getting called. I was starting to think that Android had a serious flaw..

The info on this page led me to my final resolution. Thanks a bunch to all contributors!

This solved it for me...

public class DatabaseHelper extends SQLiteOpenHelper {
    public static String TAG = DatabaseHelper.class.getName();
    private static final int DATABASE_VERSION = 42;
    private static final String DATABASE_NAME = "app_database";
    private static final String OLD_TABLE = "old_and_useless";

    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion ) {
        if( newVersion > oldVersion) {
            Log.d( TAG, "cool! you noticed." );

            db.execSQL( "DROP TABLE IF EXISTS " + OLD_TABLE );
            // other calls like onCreate if necessary

        } else {
            Log.d( TAG, "Hey! didn't you see me?" );
        }

    }

    public void checkDatabaseVersion() {
        SQLiteDatabase db = this.getWritableDatabase();

        // if the DATABASE_VERSION is newer
        //    onUpgrade is called before this is reached
    }


    // other code removed for readability...
}

It's true that getWritableDatabase() and getReadableDatabase() does result in the onUpgrade call. I didn't check other methods since these fit the bill for my needs.

Keep reading, the kicker is coming...

This code in my initial Activity enlightened me when I finally realized that the db version was updating during my debugging... ugh!

DatabaseHelper dbHelper = new DatabaseHelper( this );
dbHelper.checkDatabaseVersion();

NOTE: calling the DatabaseHelper constructor updates the db version

After the constructor call, the db was tagged with the new version. Kill the app before a call to getWritableDatabase() or getReadableDatabase() and you're on the new version. Thereafter new executions never call the onUpgrade method until DATABASE_VERSION is increased again. (sigh! now it seems ridiculously obvious :)

My suggestion is to add some sort of "checkDatabaseVersion()" to the early stages of your app. Alternately, if you create a SQLiteOpenHelper object make sure you call one of the methods (getWritableDatabase(), getReadableDatabase(), etc.) before your app dies..

I hope this saves someone else the same head scratching!... :p

Answer 5

Looking into the SqliteOpenHelper source code, we can know onCreate(),onUpgrade() and onDowngrade get called in getWritableDatabase() or getReadableDatabase() method.

public SQLiteDatabase getWritableDatabase() {
    synchronized (this) {
        return getDatabaseLocked(true);
    }
}
public SQLiteDatabase getReadableDatabase() {
    synchronized (this) {
        return getDatabaseLocked(false);
    }
}

private SQLiteDatabase getDatabaseLocked(boolean writable) {
    if (mDatabase != null) {
        if (!mDatabase.isOpen()) {
            // Darn!  The user closed the database by calling mDatabase.close().
            mDatabase = null;
        } else if (!writable || !mDatabase.isReadOnly()) {
            // The database is already open for business.
            return mDatabase;
        }
    }
          . . . . . .  

        final int version = db.getVersion();
        if (version != mNewVersion) {
            if (db.isReadOnly()) {
                throw new SQLiteException("Can't upgrade read-only database from version " +
                        db.getVersion() + " to " + mNewVersion + ": " + mName);
            }

            db.beginTransaction();
            try {
                if (version == 0) {
                    onCreate(db);
                } else {
                    if (version > mNewVersion) {
                        onDowngrade(db, version, mNewVersion);
                    } else {
                        onUpgrade(db, version, mNewVersion);
                    }
                }
                db.setVersion(mNewVersion);
                db.setTransactionSuccessful();
            } finally {
                db.endTransaction();
            }
        }

        onOpen(db);

        if (db.isReadOnly()) {
            Log.w(TAG, "Opened " + mName + " in read-only mode");
        }

        mDatabase = db;
        return db;
    } finally {
        mIsInitializing = false;
        if (db != null && db != mDatabase) {
            db.close();
        }
    }
}

Answer 6

It is actually called when you call getReadableDatabase or getWritableDatabase.

Deep dive:

You pass version number in the constructor of SQLiteOpenHelper which is stored in a variable called mNewVersion. That's it. Nothing happens at this point.

Everytime you call getReadableDatabase or getWritableDatabase, it will call a method called getDatabaseLocked. This method will get the existing version number of the database and compare it with the mNewVersion.

1. If the database with the given name doesn't exist it will call onCreate
2. If the new version is greater than old version it will call onUpgrade.
3. If the new version is lower than existing version, an exception will be thrown.
4. If they are equal it will go ahead and open the database.

What should I write in onCreate and onUpgrade ?

onCreate should contain the code that creates a schema for the first time.

You can leave onUpgrade empty first time since it won't be called the first time. When you want to change the table structure at later stage, that code should go in here.

SQLiteOpenHelper.java(Source code)

public SQLiteDatabase getWritableDatabase() {
    synchronized (this) {
        return getDatabaseLocked(true);
    }
}

 public SQLiteDatabase getReadableDatabase() {
    synchronized (this) {
        return getDatabaseLocked(false);
    }
}

private SQLiteDatabase getDatabaseLocked(boolean writable) {
   .
   .

     final int version = db.getVersion();

        if (version != mNewVersion) {
            if (db.isReadOnly()) {
                throw new SQLiteException("Can't upgrade read-only database from version " +
                        db.getVersion() + " to " + mNewVersion + ": " + mName);
            }

            db.beginTransaction();
            try {
                if (version == 0) {
                    onCreate(db);
                } else {
                    if (version > mNewVersion) {
                        onDowngrade(db, version, mNewVersion);
                    } else {
                        onUpgrade(db, version, mNewVersion);
                    }
                }
                db.setVersion(mNewVersion);
                db.setTransactionSuccessful();
            } finally {
                db.endTransaction();
            }
       }

       onOpen(db);
 }