How onUpgrade() works if anyone uninstall and cleared all data?

Question

I'm working on an android app for some days using SQLite as databse. Sometimes I had to add column. For this reason, I just change database version and write down the changes in onUpgrade() like this:

db.execSQL("ALTER TABLE "+TABLE_EMPLOYEE+" ADD "+COLUMN_EMPLOYEE_MOBILE+" TEXT");

It works for when the app is still installed in the mobile. But it shows error when the app is not installed before or uninstalled and cleared all the data and cache. Because when checking version, it got only one version. How to resolve this?

score 2 · Accepted Answer · answered Jun 17 '21 at 14:31

2

Your onCreate() should set up a new database appropriate for your current database version. onUpgrade() is only invoked when there's a database file around with an older version.

answered Jun 17 '21 at 14:31

laalto

150,114
66
286
303

score 1 · Answer 2 · answered Jun 17 '21 at 14:36

I recommend you to read this SQLite to understand how to setup a database.

From docs : You have this DbHelper and you have these overrides :

onCreate

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.

onUpgrade

Called when the database needs to be upgraded. The implementation should use this method to drop tables, add tables, or do anything else it needs to upgrade to the new schema version.

onDowngrade

Called when the database needs to be downgraded. This is strictly similar to onUpgrade(SQLiteDatabase, int, int) method, but is called whenever current version is newer than requested one. However, this method is not abstract, so it is not mandatory for a customer to implement it. If not overridden, default implementation will reject downgrade and throws SQLiteException

With these methods you'd be fine and don't worry about the old versions and new versions.

class FeedReaderDbHelper(context: Context) : SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {
    override fun onCreate(db: SQLiteDatabase) {
        db.execSQL(SQL_CREATE_ENTRIES)
    }
    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        // This database is only a cache for online data, so its upgrade policy is
        // to simply to discard the data and start over
        db.execSQL(SQL_DELETE_ENTRIES)
        onCreate(db)
    }
    override fun onDowngrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        onUpgrade(db, oldVersion, newVersion)
    }
    companion object {
        // If you change the database schema, you must increment the database version.
        const val DATABASE_VERSION = 1
        const val DATABASE_NAME = "FeedReader.db"
    }
}

So, I have to change in both onCreate() and onUpgrade(), right? If a database is already created before it will execute onUpgrade() and if there is no database, it execute onCreate()? — Riyad, Jun 17 '21 at 14:46

score 1 · Answer 3 · answered Jun 17 '21 at 15:14

In the onCreate() method you must have the code that creates the tables as you want them to be in the latest version of the database.

So when you deployed the 1st version it would look like:

@Override
public void onCreate(SQLiteDatabase db) {
    String CREATE_EMPLOYEE_TABLE = "CREATE TABLE " + 
        TABLE_EMPLOYEE + " ("+ 
        COLUMN_ID + " INTEGER PRIMARY KEY, " + 
        COLUMN_EMPLOYEE_NAME + " TEXT)";
    db.execSQL(CREATE_EMPLOYEE_TABLE);
}

but in the 2nd version, since you introduce a new column in the table it should be like:

@Override
public void onCreate(SQLiteDatabase db) {
    String CREATE_INPUT_TABLE = "CREATE TABLE " + 
        TABLE_EMPLOYEE + " ("+ 
        COLUMN_ID + " INTEGER PRIMARY KEY, " + 
        COLUMN_EMPLOYEE_NAME + " TEXT, " +
        COLUMN_EMPLOYEE_MOBILE + " TEXT)"; // new column
    db.execSQL(CREATE_INPUT_TABLE);
}

The onCreate() method will be called only if there is no database already installed.

So, when you deploy the 2nd version of the database, if the user has already installed the 1st version and the old database is still there, onCreate() will not be called but onUpgrade() will be called with the code that you have in your question and will alter the existing table.

If the user decides to uninstall the 1st version and then install the 2nd version, then onCreate() will be called, because there is no database installed, and it will create the table(s) as you want them in that version.

Not that, onCreate() or onUpgrade() are not called automatically when the user installs the app.
They are invoked by the first call to getReadableDatabase() or getWriteableDatabase().

How onUpgrade() works if anyone uninstall and cleared all data?

3 Answers3