How to migrate exsiting Sqlite database with new one that has changed COLUMN name ? Android

Question

Hi I have a notepad application on play store and has users data ... the app without those new changes can update , export& import backup and runs perfectly with no problem. but when I changed my old SQLiteOpenHelper arrangement , now it doesn't do anything that I mentioned above except if I rechanged back those new changes to the old arrangement .

old SQLiteOpenHelper arrangement

    static final String DATABASE_NAME = "note.db";
    static final String TABLE_NOTE = "tb_note";
    static final String KEY_ID_NOTE = "id";
    private static final String KEY_TITLE_NOTE = "title";
    private static final String KEY_CONTENT_NOTE = "content";
    private static final String KEY_LAST_MODIFIED_NOTE = "last_modified";


    private static final String CREATE_TABLE_NOTE =
            "CREATE TABLE " + TABLE_NOTE + "(" +
                    KEY_ID_NOTE + " INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL" +
                    ", " + KEY_TITLE_NOTE + " TEXT NOT NULL" +
                    "," + KEY_CONTENT_NOTE + " TEXT NOT NULL" +
                    "," + KEY_LAST_MODIFIED_NOTE + " TEXT DEFAULT \'\'" +
                    ")";


    private static final int DATA_VERSION = 2;

But I changed one COLUMN name in the app and my new SQLiteOpenHelper became like this which everything is the same except I changed those lines in bold below this code

    static final String DATABASE_NAME = "note.db";
    static final String TABLE_NOTE = "tb_note";
    static final String KEY_ID_NOTE = "id";
    private static final String KEY_TITLE_NOTE = "title";
    private static final String KEY_CONTENT_NOTE = "content";
    private static final String KEY_UPDATE_TIME = "update_time";


    private static final String CREATE_TABLE_NOTE =
            "CREATE TABLE " + TABLE_NOTE + "(" +
                    KEY_ID_NOTE + " INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL" +
                    ", " + KEY_TITLE_NOTE + " TEXT NOT NULL" +
                    "," + KEY_CONTENT_NOTE + " TEXT NOT NULL" +
                    "," + KEY_UPDATE_TIME + " TEXT NOT NULL" +
                    ")";


    private static final int DATA_VERSION = 2;

they were like this

private static final String KEY_LAST_MODIFIED_NOTE = "last_modified";

"," + KEY_LAST_MODIFIED_NOTE + " TEXT DEFAULT \'\'" +

but now

private static final String KEY_UPDATE_TIME = "update_time";

"," + KEY_UPDATE_TIME + " TEXT NOT NULL" +

finally here is my onupgrade code

 @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // update database for database version < 2
        if (oldVersion < 2) {
            db.execSQL("ALTER TABLE " + TABLE_NOTE + " ADD COLUMN " + KEY_UPDATE_TIME + " TEXT DEFAULT \'\'");
        }
    }

Now How to migrate this exsiting Sqlite database with the new one without losing any data?

Answer 1

First, you rename the old table and then create a new table with the old table name and updated column name, then copy first table content into the newly created table and delete old table.

    @Override 
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
       // change old table name 
        db.execSQL("ALTER TABLE "+orig_table_name +" RENAME "+ " TO "+ tmp_table_name;
     // create new table with old table name and updated coulmn
         db.execSQL( "CREATE TABLE " + TABLE_NOTE + "(" +
                    KEY_ID_NOTE + " INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL" +
                    ", " + KEY_TITLE_NOTE + " TEXT NOT NULL" +
                    "," + KEY_CONTENT_NOTE + " TEXT NOT NULL" +
                    "," + KEY_UPDATE_TIME + " TEXT NOT NULL" +
                    ")");
// then insert old table data into new one
db.execSQL(""INSERT INTO " + NEW_TABLE NAME + " SELECT * FROM " + OLD_TABLE_NAME");
//and finally delete old table
 db.execSQL("DROP TABLE "+OLD_TABLE_NAME); 
    }

And use sqlite browser to check all above query executed successfully.