Android: upgrading DB version and adding new table

I've already created sqlite tables for my app, but now I want to add a new table to the database.

I changed the DB version as below

private static final int DATABASE_VERSION = 2;

and Added string to create table

private static final String DATABASE_CREATE_color = 
   "CREATE TABLE IF NOT EXISTS files(color text, incident_id text)";

onCreate and onUpgrade as below:

@Override
	public void onCreate(SQLiteDatabase database) {
		database.execSQL(DATABASE_CREATE_incident);
		database.execSQL(DATABASE_CREATE_audio);
		database.execSQL(DATABASE_CREATE_video);
		database.execSQL(DATABASE_CREATE_image);
		
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		//drop table and add new tables when version 2 released.
		db.execSQL(DATABASE_CREATE_color);
		
	}

But for some reason the new table is not being created. What am I doing wrong?

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...

Your code looks correct. My suggestion is that the database already thinks it's upgraded. If you executed the project after incrementing the version number, but before adding the execSQL call, the database on your test device/emulator may already believe it's at version 2.

A quick way to verify this would be to change the version number to 3 -- if it upgrades after that, you know it was just because your device believed it was already upgraded.

You can use SQLiteOpenHelper's onUpgrade method. In the onUpgrade method, you get the oldVersion as one of the parameters.

In the onUpgrade use a switch and in each of the cases use the version number to keep track of the current version of database.

It's best that you loop over from oldVersion to newVersion, incrementing version by 1 at a time and then upgrade the database step by step. This is very helpful when someone with database version 1 upgrades the app after a long time, to a version using database version 7 and the app starts crashing because of certain incompatible changes.

Then the updates in the database will be done step-wise, covering all possible cases, i.e. incorporating the changes in the database done for each new version and thereby preventing your application from crashing.

For example:

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    switch (oldVersion) {
    case 1:
        String sql = "ALTER TABLE " + TABLE_SECRET + " ADD COLUMN " + "name_of_column_to_be_added" + " INTEGER";
        db.execSQL(sql);
        break;

    case 2:
        String sql = "SOME_QUERY";
        db.execSQL(sql);
        break;
    }

}

@jkschneider's answer is right. However there is a better approach.

Write the needed changes in an sql file for each update as described in the link https://riggaroo.co.za/android-sqlite-database-use-onupgrade-correctly/

from_1_to_2.sql

ALTER TABLE books ADD COLUMN book_rating INTEGER;

from_2_to_3.sql

ALTER TABLE books RENAME TO book_information;

from_3_to_4.sql

ALTER TABLE book_information ADD COLUMN calculated_pages_times_rating INTEGER;
UPDATE book_information SET calculated_pages_times_rating = (book_pages * book_rating) ;

These .sql files will be executed in onUpgrade() method according to the version of the database.

DatabaseHelper.java

public class DatabaseHelper extends SQLiteOpenHelper {
 
	private static final int DATABASE_VERSION = 4;
 
	private static final String DATABASE_NAME = "database.db";
	private static final String TAG = DatabaseHelper.class.getName();
 
	private static DatabaseHelper mInstance = null;
	private final Context context;
 
	private DatabaseHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		this.context = context;
	}
 
	public static synchronized DatabaseHelper getInstance(Context ctx) {
		if (mInstance == null) {
			mInstance = new DatabaseHelper(ctx.getApplicationContext());
		}
		return mInstance;
	}
 
	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(BookEntry.SQL_CREATE_BOOK_ENTRY_TABLE);
		// The rest of your create scripts go here.
 
	}
 
 
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		Log.e(TAG, "Updating table from " + oldVersion + " to " + newVersion);
		// You will not need to modify this unless you need to do some android specific things.
		// When upgrading the database, all you need to do is add a file to the assets folder and name it:
		// from_1_to_2.sql with the version that you are upgrading to as the last version.
		try {
			for (int i = oldVersion; i < newVersion; ++i) {
				String migrationName = String.format("from_%d_to_%d.sql", i, (i + 1));
				Log.d(TAG, "Looking for migration file: " + migrationName);
				readAndExecuteSQLScript(db, context, migrationName);
			}
		} catch (Exception exception) {
			Log.e(TAG, "Exception running upgrade script:", exception);
		}
 
	}
 
	@Override
	public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
 
	}
 
	private void readAndExecuteSQLScript(SQLiteDatabase db, Context ctx, String fileName) {
		if (TextUtils.isEmpty(fileName)) {
			Log.d(TAG, "SQL script file name is empty");
			return;
		}
 
		Log.d(TAG, "Script found. Executing...");
		AssetManager assetManager = ctx.getAssets();
		BufferedReader reader = null;
 
		try {
			InputStream is = assetManager.open(fileName);
			InputStreamReader isr = new InputStreamReader(is);
			reader = new BufferedReader(isr);
			executeSQLScript(db, reader);
		} catch (IOException e) {
			Log.e(TAG, "IOException:", e);
		} finally {
			if (reader != null) {
				try {
					reader.close();
				} catch (IOException e) {
					Log.e(TAG, "IOException:", e);
				}
			}
		}
 
	}
 
	private void executeSQLScript(SQLiteDatabase db, BufferedReader reader) throws IOException {
		String line;
		StringBuilder statement = new StringBuilder();
		while ((line = reader.readLine()) != null) {
			statement.append(line);
			statement.append("\n");
			if (line.endsWith(";")) {
				db.execSQL(statement.toString());
				statement = new StringBuilder();
			}
		}
	}
}

An example project is provided in the same link also : https://github.com/riggaroo/AndroidDatabaseUpgrades

Handling database versions is very important part of application development. I assume that you already have class AppDbHelper extending SQLiteOpenHelper. When you extend it you will need to implement onCreate and onUpgrade method.

1. When onCreate and onUpgrade methods called

• onCreate called when app newly installed.
• onUpgrade called when app updated.

2. Organizing Database versions I manage versions in a class methods. Create implementation of interface Migration. E.g. For first version create MigrationV1 class, second version create MigrationV1ToV2 (these are my naming convention)

   
   public interface Migration {
   void run(SQLiteDatabase db);//create tables, alter tables
   }
   

Example migration:

public class MigrationV1ToV2 implements Migration{
      public void run(SQLiteDatabase db){
        //create new tables
        //alter existing tables(add column, add/remove constraint)
        //etc.
     }
   }

3. Using Migration classes

onCreate: Since onCreate will be called when application freshly installed, we also need to execute all migrations(database version updates). So onCreate will looks like this:

public void onCreate(SQLiteDatabase db){
        Migration mV1=new MigrationV1();
       //put your first database schema in this class
        mV1.run(db);
        Migration mV1ToV2=new MigrationV1ToV2();
        mV1ToV2.run(db);
        //other migration if any
  }

onUpgrade: This method will be called when application is already installed and it is updated to new application version. If application contains any database changes then put all database changes in new Migration class and increment database version.

For example, lets say user has installed application which has database version 1, and now database version is updated to 2(all schema updates kept in MigrationV1ToV2). Now when application upgraded, we need to upgrade database by applying database schema changes in MigrationV1ToV2 like this:

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    if (oldVersion < 2) {
        //means old version is 1
        Migration migration = new MigrationV1ToV2();
        migration.run(db);
    }
    if (oldVersion < 3) {
        //means old version is 2
    }
}

> Note: All upgrades (mentioned in onUpgrade) in to database schema should be executed in onCreate

in first db version ,table is as below:

   String createUserTable = "CREATE TABLE IF NOT EXISTS "+ user + " (" +
           USER_ID + " INTEGER PRIMARY KEY, " +
           USER_NAME + " TEXT NOT NULL, " +
           USER_FAMILY + " TEXT NOT NULL, " +
           USER_LIVING_STATUS + " INTEGER DEFAULT 0);";   

in second version on database we added a new column by name of color , that's why upgrade codes should be like below.

@Override   
public void onUpgrade(SQLiteDatabase mydb, int oldVersion, int newVersion) {
   
       if (newVersion > oldVersion) {
     
       /******************************** delete the last table and create new table with new columns  and copy    the former to new one *********************************/
       
       // here we added color column to new table
       mydb.execSQL("CREATE TABLE IF NOT EXISTS user_tmp ("
               + "user_id integer, user_name text, user_family text, user_color text,user_living_status integer);");
       mydb.execSQL("INSERT INTO user_tmp(user_id,user_name,user_family,user_living_status) SELECT * FROM user");
       mydb.execSQL("drop table user;");
       mydb.execSQL("ALTER TABLE user_tmp RENAME TO user");
       
   }
       }