how to upgrade my database using SQLiteAssetHelper using onUpgrade() method?

Question

I am building a quiz app with preloaded database questions using the SQLiteAssetHelper class. my app is working fine with already 300 question quiz in the database. but I can't figure out the way how to upgrade the database( adding new quiz questions to the table and NOT changing the structure or deleting any saved user data inside the old table). I have installed SQLite compare as described in the documentation of the SQLiteAssetHelper in GitHub but it generated an empty SQL file for me, I don't know why! so I tried the following code and tried to insert two rows only for testing if the database will upgrade on my android from version 1 to 2 but unfortunately, it didn't help. I have looked everywhere in this form but the information related the SQLiteAsseHelper class is very few and not clear enough. I am finishing my Quiz app these days and I am thinking if I will not be able to update the app it will be useless then.Please advise me on how to insert new rows to the old database without changing the previously existing rows because I save the user answer in the table.

import android.content.ContentValues;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;

import com.readystatesoftware.sqliteasset.SQLiteAssetHelper;

public class DatabaseOpenHelper extends SQLiteAssetHelper {

    private static final String DATABASE_NAME = "quizMe_1.db";
    private static final int DATABASE_VERSION = 2;

    public DatabaseOpenHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }


@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    if (oldVersion < 2){
        insertValues(db,"من اكثر فريق عالمي فوزا بكأس العالم ؟","فرنسا","ألمانيا","أيطاليا","البرازيل","0","4","0");
        insertValues(db,"نبي سميت سورة من القران بأسمه ؟","عيسى","موسى","إبراهيم","نوح","0","3","0");
    }
}

private static void insertValues(SQLiteDatabase db, String question,String ans1, String ans2, String ans3, String ans4,
                                 String right, String userAsn, String field) {
    ContentValues questionValues = new ContentValues();
    questionValues.put("question", question);
    questionValues.put("answer_1", ans1);
    questionValues.put("answer_2", ans2);
    questionValues.put("answer_3", ans3);
    questionValues.put("answer_4", ans4);
    questionValues.put("right_answer", right);
    questionValues.put("user_answer", userAsn);
    questionValues.put("field", field);
    db.insert("quiz", null, questionValues);
}


}

Answer 1

I am not sure whether we can update the database file which is in assets folder, but somehow I hope I can give you a idea to debug the solution. Before trying the below solution we need to make sure whether the values in the database file can be updated or not. For that update a value in the table by code and test it. For Example: Change the answer for question 1 in your quiz app and check whether the value is updated or not by running the app. If the value is updated then we can insert the new values also then try the below one, else we cannot update the values in database file.

Hope you were aware that onUpgrade mehtod will be called only when we update the app.i.e., from lower version to latest version. I am not sure how did you tested you code, if you have tested in the below way ignore it else try it. First download and install the app from the playstore, then generate the signed apk for latest version (make sure the database version is updated) install the apk put a toast in onupgrade method and check whether it get's into the condition. If the toast appears then check your insert code, else change the condition as below and give a try

if(newVersion == 2)

Hope this helpful:)