How to upgrade sqlite database?

Question

I have an sqLite database in my app which store high scores. I recently added a new column(levels) and need to upgrade the database, otherwise the app crashes. If I delete manually the .db file works fine. I tried to increase the database version but didn't work. So how to do it?

Here is my sqlite helper class:

public class DataBaseHelper extends SQLiteOpenHelper{

public static final String TABLE_HISCORES = "hiScores";
static final String RANK = "_id";
static final String NAME = "name";
static final String SCORE = "score";
static final String LEVEL = "level";
private static final String DATABASE_NAME = "scoresdb.db";
private static final int DATABASE_VERSION = 1;
SQLiteDatabase db;

private static final String DATABASE_CREATE = "CREATE TABLE " + TABLE_HISCORES + " ("
        + RANK + " INTEGER PRIMARY KEY AUTOINCREMENT, "
        + NAME + " TEXT, "
        + LEVEL + " TEXT, "
        + SCORE + " INTEGER );";
public DataBaseHelper(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
  }

@Override
public void onCreate(SQLiteDatabase database) {
  database.execSQL(DATABASE_CREATE);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // TODO Auto-generated method stub
    Log.w(DataBaseHelper.class.getName(),
            "Upgrading database from version " + oldVersion + " to "
                + newVersion + ", which will destroy all old data");
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_HISCORES);
        onCreate(db);


}

}

What is needed to add/change?

It should work. Did you called `getWriteableDatabase`? See http://stackoverflow.com/questions/8627075/updating-sqlite-database-versions — svenkapudija, May 12 '13 at 10:08
I have an open() method on another class: public void open() throws SQLException { database = dbHelper.getWritableDatabase(); } and I call this method on button' s onClick, so I called it. — dancer_69, May 12 '13 at 10:15
Seems that needed the getWritableDatabase() to be on constactor. Works ok now. Thanks — dancer_69, May 12 '13 at 12:15

score 0 · Answer 1 · answered May 12 '13 at 10:11

0

You can open the SqLite database in SQLite query browser(http://sourceforge.net/projects/sqlitebrowser/) and add the column there. After adding the column there you can replace .db file you are using in your project with the one in which you have added the new column.

Hope it helps!

answered May 12 '13 at 10:11

Vipul J

6,641
9
46
61

The problem is that I've published the app, so I want this to be done automatic when the new version is installed. – dancer_69 May 12 '13 at 10:18
Then in the newer version, you can make a check if the app is being installed for the first time, if it is only then you can add a new column for high scores programatically. Or you can check if a column named 'levels' exist in database, it it isn't, you can add that column aromatically. – Vipul J May 12 '13 at 10:20
Maybe I didn't understand well, but If the app installed for first time there isn't any problem and works fine. The problem if there is already a previous version installed. Then I get the crash and I need to delete the previous file, so to recreate it. – dancer_69 May 12 '13 at 10:24
Yes, but now in this case you don't have to recreate the database, you just have to add one column programatically. – Vipul J May 12 '13 at 10:26

How to upgrade sqlite database?

1 Answers1