Android: Upgrading Db Version and Adding New Table

Android: upgrading DB version and adding new table

1. About onCreate() and onUpgrade()

onCreate(..) is called whenever the app is freshly installed. onUpgrade is called whenever the app is upgraded and launched and the database version is not the same.

2. Incrementing the db version

You need a constructor like:

MyOpenHelper(Context context) {
   super(context, "dbname", null, 2); // 2 is the database version
}

IMPORTANT: Incrementing the app version alone is not enough for onUpgrade to be called!

3. Don't forget your new users!

Don't forget to add

database.execSQL(DATABASE_CREATE_color);

to your onCreate() method as well or newly installed apps will lack the table.

4. How to deal with multiple database changes over time

When you have successive app upgrades, several of which have database upgrades, you want to be sure to check oldVersion:

onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
   switch(oldVersion) {
   case 1:
       db.execSQL(DATABASE_CREATE_color);
       // we want both updates, so no break statement here...
   case 2:
       db.execSQL(DATABASE_CREATE_someothertable); 
   }
}

This way when a user upgrades from version 1 to version 3, they get both updates. When a user upgrades from version 2 to 3, they just get the revision 3 update... After all, you can't count on 100% of your user base to upgrade each time you release an update. Sometimes they skip an update or 12 :)

5. Keeping your revision numbers under control while developing

And finally... calling

adb uninstall <yourpackagename>

totally uninstalls the app. When you install again, you are guaranteed to hit onCreate which keeps you from having to keep incrementing the database version into the stratosphere as you develop...

Android SQLite add new table on upgrade

If I read correctly the code you are intentionally calling the onCreate method from your onUpgrade.

Just before the method ends you call: this.onCreate(db)

Android : Change SQLite DB on upgrade

Are you using SQLiteOpenHelper ? If yes you must have a version you send to the super class from the constructor (see documentation)

Here is a code sample. The onUpgrade function will be called when the DATABASE_VERSION changes.

public class DatabaseHelper extends SQLiteOpenHelper {

    private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_NAME = "EJuiceData.db";

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

    @Override
    public void onCreate(SQLiteDatabase database) {
        database.execSQL("create table if not exists Inventory(_id integer primary key autoincrement, NAME TEXT NOT NULL, AMOUNT TEXT, PRICE TEXT, AMOUNTLEFT TEXT, WEIGHT TEXT);");
    }

    @Override
    public void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion) {
        if (newVersion > oldVersion) {
            db.execSQL("ALTER TABLE Recipe ADD COLUMN NOTES TEXT");
            onCreate(database);
        }
    }
}

EDIT

now you can get access your database using

DatabaseHelper helper = new DatabaseHelper(getContext());
helper.getReadableDatabase().query(...);
//or
helper.getWritableDatabase().insert(...);

Does creating a table after the database has already been created change the database version in SQLite?

When you extend your SQLiteOpenHelper class you define which database version to use. For instance, in the following way:

private static class DbWordsHelper extends SQLiteOpenHelper {

    private DbWordsHelper(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }

If you upgrade your database you should change DB_VERSION manually in your code and rebuild your application. New version has to be higher then previous one. When SQLiteOpenHelper detects that your version number has been changed it calls onUpgrade method where you should provide logic how to update your database:

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

        // Here in the future should be method that change the schema of the
        // database. Now we just delete
        try {
            db.execSQL(DROP_TABLE_WORDS);
        } catch (SQLException e) {
            Log.e(TAG, "Error while updating database" + TABLE_WORDS, e);
        }

        onCreate(db);

    }

Thus, even if you add new tables the version is not changed until you manually do this in the code.

releasing a new version of app that will update specific table in database-android

You need to take care about your user data and there is a onUpgrade() is there in SQliteOpenHelper class in which you can match your database version and if your database version is more then from your previous version then create new tables in onUpgrade() fucntion , And if you want to add new data to existing table then your need to write code to add new rows or update through inert or update query.
Also you can Add new columns to existing tables through alter query.

please find more information about version upgarde .

https://riggaroo.co.za/android-sqlite-database-use-onupgrade-correctly/

And if you are added your database file in Asset for initial data Then you need to change database pragma version thats is nothing but database version which we can pass in SQliteOpenHelper class.

see below sample of onUpgrade()

In our onUpgrade, we defined the following:

 @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.e(TAG, "Updating table from " + oldVersion + " to " + newVersion);

        //Added new column to book table - book rating 
        if (oldVersion < 2){
            db.execSQL(DROP + BookEntry.TABLE_NAME);
            db.execSQL(BookEntry.SQL_CREATE_BOOK_ENTRY_TABLE);
        }
        //Rename table to book_information - this is where things will start failing.
        if (oldVersion < 3){
            db.execSQL(DROP + BookEntry.TABLE_NAME);
            db.execSQL(BookEntry.SQL_CREATE_BOOK_ENTRY_TABLE);
        }
        // Add new column for a calculated value. By this time, if I am upgrading from version 2 to 
        // version 4, my table would already contain the new column I am trying to add below, 
        // which would result in a SQLException. These situations are sometimes difficult to spot, 
        // as you basically need to test from every different version of database to upgrade from. 
        // Some upgrades might work and some might fail with this method.
        // It is best to follow the other method that is on the master branch of this repo.
        if (oldVersion < 4){
            db.execSQL("ALTER TABLE " + BookEntry.TABLE_NAME  + " ADD COLUMN calculated_pages_times_rating INTEGER;");
        }
        //As you can probably imagine, this is a terrible way to do upgrades, Please DONT DO IT!!!!

     }


       @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.e(TAG, "Updating table from " + oldVersion + " to " + newVersion);

            //Added new column to book table - book rating 
            if (oldVersion < 2){
                db.execSQL(DROP + BookEntry.TABLE_NAME);
                db.execSQL(BookEntry.SQL_CREATE_BOOK_ENTRY_TABLE);
            }
            //Rename table to book_information - this is where things will start failing.
            if (oldVersion < 3){
                db.execSQL(DROP + BookEntry.TABLE_NAME);
                db.execSQL(BookEntry.SQL_CREATE_BOOK_ENTRY_TABLE);
            }
            // Add new column for a calculated value. By this time, if I am upgrading from version 2 to 
            // version 4, my table would already contain the new column I am trying to add below, 
            // which would result in a SQLException. These situations are sometimes difficult to spot, 
            // as you basically need to test from every different version of database to upgrade from. 
            // Some upgrades might work and some might fail with this method.
            // It is best to follow the other method that is on the master branch of this repo.
            if (oldVersion < 4){
                db.execSQL("ALTER TABLE " + BookEntry.TABLE_NAME  + " ADD COLUMN calculated_pages_times_rating INTEGER;");
            }
            //As you can probably imagine, this is a terrible way to do upgrades, Please DONT DO IT!!!!


        }

---

Related Topics