Android SQLite Upgrade without losing data

Question

I have created a SQLite database successfully and it works fine. However when the onUpgrade method is called, I'd like to do so without losing data. The app I'm developing is a quiz app. Simply, when the onCreate method is called I create and prepopulate a database with questions, answers etc. The last column is whether they have set the question as a favourite or not. What I would like to do is that when the onUpgrade method is called, I'd like to temporarily save that one column, drop the whole database, recreate it with any edits I've made to old questions and add any new questions then re-add back the questions that they set as favourites.

So one option I tried was the following:

db.execSQL("ALTER TABLE quiz RENAME TO temp_quiz");
onCreate(db);
db.execSQL("INSERT INTO quiz (favouries) SELECT favourites FROM temp_quiz");
db.execSQL("DROP TABLE IF EXISTS temp_quiz");

However this doesn't work owing to the fact INSERT INTO just adds new rows rather than replacing the existing rows. I have also tried REPLACE INTO, INSERT OR REPLACE INTO and

db.execSQL("INSERT INTO quiz (_id, favouries) SELECT _id, favourites FROM temp_quiz");

of which none work.

Currently I do have it set up to work by altering the name of the table, calling the onCreate(db) method and then setting up a cursor which reads each row and uses the db.update() method as shown below:

int place = 1;
int TOTAL_NUMBER_OF_ROWS = 500;

while (place < TOTAL_NUMBER_OF_ROWS) {

String[] columns = new String[] { "_id", ..........., "FAVOURITES" };
// not included all the middle columns

Cursor c = db.query("temp_quiz", columns, "_id=" + place, null, null, null, null);

c.moveToFirst(); 

String s = c.getString(10);
// gets the value from the FAVOURITES column


ContentValues values = new ContentValues(); 
values.put(KEY_FLAG, s);

String where = KEY_ROWID + "=" + place;

db.update(DATABASE_TABLE, values, where, null);

place++;

c.close();

}

However whilst this works it is extremely slow and will only get worse as my number of questions increases. Is there a quick way to do all this?

Thank you! P.S. Ideally it should only update the row if the row is present. So if in an upgrade I decide to remove a question, it should take this into account and not add a new row if the row doesn't contain any other data. It might be easier to get it to remove rows that don't have question data rather than prevent them being added.

Catheycathi

Answer 1

changed it to:

db.execSQL("UPDATE new_quiz SET favourites = ( SELECT old_quiz.favourites 
FROM old_quiz WHERE new_quiz._id = old_quiz._id) WHERE EXISTS 
( SELECT old_quiz.favourites FROM old_quiz WHERE new_quiz._id = old_quiz._id)");

Which works :D

Catheycathi

i think you meant "Update new_quiz", not old_quiz =) – Footwear

Sorry, that's what I meant. Thank you :D – Catheycathi

@AndroidPenguin, modified as you told in your comment. – Killian

Answer 2

public class DataHelper extends SQLiteOpenHelper {

    private static final String dbName="dbName"; 
    private Context context;
    private  SQLiteDatabase db;
    private final static int version = 1;

    public  static final String SurveyTbl = "CREATE TABLE SurveyTbl (SurveyId TEXT PRIMARY KEY, Idref TEXT, SurveyDate TEXT)";

    public DataHelper(Context context) {

        super(context, dbName, null, version);
        this.db = getWritableDatabase();
        this.context = context;
        Log.i("", "********************DatabaseHelper(Context context)");
    }



    @Override
    public void onCreate(SQLiteDatabase db) {

        try {

        db.execSQL(SurveyTbl);

        } catch (Exception e) {
            Log.i("", "*******************onCreate");
        }
    }



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

        try {
            db.execSQL("ALTER TABLE HandpumpSurveyTbl ADD COLUMN NalYozna TEXT");



        } catch (Exception e) {
            Log.i("", ""+e);
        }


         onCreate(db);


    }
}

Parson

Catching exceptions in onCreate and onUpdate is a bad idea since the returned values from these methods are used to determine whether to rollback the transactions they were executed in – Tonsil

Answer 3

I didn't get to see your Quiz table schema, but I assume it has fields like "question", "answer", "favorites", and some kind of a unique primary key to identify each question, which I will just call rowId for now.

// after renaming the old table and adding the new table
db.execSQL("UPDATE new_quiz SET new_quiz.favorites = old_quiz.favorites where new_quiz.rowId = old_quiz.rowId");

That will update only the rows of the new quiz table that match the old quiz table, and set the favorites value from the old quiz table.

I assume you have some kind of a unique identifier to identify each question, so instead of the rowId above, you'll use that (question number or something).

Footwear

that didn't work :/ kept throwing errors with new_quiz.favourites due to the period. Changed that then to just favourites where it still didn't understand. Eventually adapted your answer with my select statements and got it to work :D – Catheycathi

ah I forgot sqlite doesn't support JOIN on UPDATE =( – Footwear

Answer 4

For who don't know yet how to upgrade the version of the SQLite when upgrading the database schema for example, use the method needUpgrade(int newVersion)!

My code:

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

     if(newVersion>oldVersion){

         db.execSQL(scriptUpdate);
         db.needUpgrade(newVersion);
     }

}

Bombshell

ononUpgrade() is always called when newVersion's value is above oldVersion, so there's no need for that if statement. – Flatboat

Answer 5

ALTER TABLE mytable ADD COLUMN mycolumn TEXT

In your onUpgrade method, it would look something like this:

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    String upgradeQuery = "ALTER TABLE mytable ADD COLUMN mycolumn TEXT";
    if (newVersion>oldVersion)
         db.execSQL(upgradeQuery);
}

Lefthand

Answer 6

Example, how to drop a table and create a new table without losing data by using a temporary table:

db.execSQL("CREATE TEMPORARY TABLE temp_table (_id INTEGER PRIMARY KEY AUTOINCREMENT, col_1 TEXT, col_2 TEXT);");
db.execSQL("INSERT INTO temp_table SELECT _id, col_1, col_2 FROM old_table");

db.execSQL("CREATE TABLE new_table (_id INTEGER PRIMARY KEY AUTOINCREMENT, col_1 TEXT, col_2 TEXT, col_3 TEXT);");
db.execSQL("INSERT INTO new_table SELECT _id, col_1, col_2, null FROM temp_table");

db.execSQL("DROP TABLE old_table");
db.execSQL("DROP TABLE temp_table");

Jampack