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.
-
When
onCreateandonUpgrademethods called-
onCreatecalled when app newly installed. -
onUpgradecalled when app updated.
-
Organizing Database versions I manage versions in a class methods. Create implementation of interface Migration. E.g. For first version create
MigrationV1class, second version createMigrationV1ToV2(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.
}
}
- 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 inonCreate