How do I upgrade a database without removing the data that the user input in the previous database?

Question

I am creating an app that needs a database. I created it using sqlite database browser, which means the app I created, imports the database I created into the phone.

The app that I create requires that the user enter data into the database. When upgrading the database, I hope to retain the data that the user had input.

My database helper code is below:

public class DatabaseHelper extends SQLiteOpenHelper {

//The Android's default system path of your application database.
private static String DB_PATH = "/data/data/test.test/databases/";

private static String DB_NAME = "TestDatabase";

private static final int DB_VERSION = 1;

private SQLiteDatabase myDatabase; 

private final Context myContext;

/**
 *  # Constructor #
 * Takes and keeps a reference of the passed context in order to access to the application assets and resources.
 * @param context
 */
public DatabaseHelper(Context context) {

    super(context, DB_NAME, null, DB_VERSION);
    this.myContext = context;
}//constructor  

/**
 *  # Create Database #
 * Creates a empty database on the system and rewrites it with your own database.
 */
public void createDatabase() throws IOException {

    boolean dbExist = checkDatabase();

    if(dbExist)
    {
        //do nothing - database already exist
    }//if

    else
    {
        //By calling this method and empty database will be created into the default system path
           //of your application so we are gonna be able to overwrite that database with our database.
        this.getReadableDatabase();

        try 
        {
            copyDatabase();

        } catch (IOException e) {

            throw new Error("Error copying database");

        }//catch
    }//else

}//createDatabase

private boolean checkDatabase() {

    SQLiteDatabase checkDB = null;

    try
    {
        String myPath = DB_PATH + DB_NAME;
        checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);

    } catch(SQLiteException e) {

        //database does't exist yet.

    }//catch

    if(checkDB != null) 
    { 
        checkDB.close();

    }//if

    return checkDB != null ? true : false;

}//checkDatabase


private void copyDatabase() throws IOException {

    //Open your local db as the input stream
    InputStream myInput = myContext.getAssets().open(DB_NAME);

    // Path to the just created empty db
    String outFileName = DB_PATH + DB_NAME;

    //Open the empty db as the output stream
    OutputStream myOutput = new FileOutputStream(outFileName);

    //transfer bytes from the inputfile to the outputfile
    byte[] buffer = new byte[1024];
    int length;

    while ((length = myInput.read(buffer))>0)
    {
        myOutput.write(buffer, 0, length);
    }

    //Close the streams
    myOutput.flush();
    myOutput.close();
    myInput.close();

}//copyDatabase

// # open database #
public void openDatabase() throws SQLException {

    //Open the database
    String myPath = DB_PATH + DB_NAME;
    myDatabase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);

}//openDatabase

@Override
public synchronized void close() 
{
    if(myDatabase != null)
        myDatabase.close();

    super.close();

}//close

@Override
public void onCreate(SQLiteDatabase db) {

}

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

}

public List<String> selectData
    (String tableName, String [] columns, String selection, String[] selectionArgs,
            String groupBy, String having, String orderBy) {

    List<String> list = new ArrayList<String>();

    Cursor cursor = this.myDatabase.query(tableName, columns, selection, selectionArgs, groupBy, having, orderBy);

    if (cursor.moveToFirst()) 
    {
        do 
        {
            list.add(cursor.getString(0));
        }

        while (cursor.moveToNext());
    }

    if (cursor != null && !cursor.isClosed()) 
    {
        cursor.close();
    }
    return list;

}//selectData

public void insertData (String tableName, String nullColumnHack, ContentValues values) {

    try
    {
        myDatabase.insert(tableName, nullColumnHack, values);
    } catch (Exception e) {
        Log.e("Error :","unable to insert data");
    }//catch

}//insertData

//edit row
public void updateData (String tableName, ContentValues values, String whereClause, String[] whereArgs) {

    try
    {
        myDatabase.update(tableName, values, whereClause, whereArgs);
    } catch (Exception e) {
        Log.e("Error :","unable to update data");
    }//catch
}//updateData

public void deleteRow (String tableName, String whereClause, String[] whereArgs) {

    try
    {
        myDatabase.delete(tableName, whereClause, whereArgs);
    } catch (Exception e) {
        Log.e("Error :","unable to delete row");
    }//catch
}//deleteRow
}

*note: My database consist of more than one table. Two tables require user input. The others don't.

I hope that real answer could be given, instead of giving website that is not in the exact situation that I have, as I get confused easily.

Answer 1

You should add some code into the onUpgrade method. With that, you can check the oldVersion and the newVersion and do the proper ALTER TABLE statements. As you can see, the current version is 23 and the check code checks what is the old version. If version 22 it does just the v22 statements, but if version 21 it does both v21 AND v22 statements. This is part of the Google I/O app:

private static final int VER_LAUNCH = 21;
private static final int VER_SESSION_FEEDBACK_URL = 22;
private static final int VER_SESSION_NOTES_URL_SLUG = 23;

private static final int DATABASE_VERSION = VER_SESSION_NOTES_URL_SLUG;

...

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

    // NOTE: This switch statement is designed to handle cascading database
    // updates, starting at the current version and falling through to all
    // future upgrade cases. Only use "break;" when you want to drop and
    // recreate the entire database.
    int version = oldVersion;

    switch (version) {
        case VER_LAUNCH:
            // Version 22 added column for session feedback URL.
            db.execSQL("ALTER TABLE " + Tables.SESSIONS + " ADD COLUMN "
                    + SessionsColumns.SESSION_FEEDBACK_URL + " TEXT");
            version = VER_SESSION_FEEDBACK_URL;

        case VER_SESSION_FEEDBACK_URL:
            // Version 23 added columns for session official notes URL and slug.
            db.execSQL("ALTER TABLE " + Tables.SESSIONS + " ADD COLUMN "
                    + SessionsColumns.SESSION_NOTES_URL + " TEXT");
            db.execSQL("ALTER TABLE " + Tables.SESSIONS + " ADD COLUMN "
                    + SessionsColumns.SESSION_SLUG + " TEXT");
            version = VER_SESSION_NOTES_URL_SLUG;
    }

    Log.d(TAG, "after upgrade logic, at version " + version);
    if (version != DATABASE_VERSION) {
        Log.w(TAG, "Destroying old data during upgrade");

        db.execSQL("DROP TABLE IF EXISTS " + Tables.BLOCKS);

        // ... delete all your tables ...

        onCreate(db);
     }
 }