new or revised data made to a local sqLite are not appearing in app

Question

My app is a quiz that has 33 stages and 25 categories, quiz data are in a LOCAL sqlite database that has three tables (Stages + Categories + Questions), users scores are saved to the Stage/Category tables in the database upon successful completion of each level.

I need from time to time to revise the data OR add new stages, categories and questions; I do so in the sqlite database itself, then I copy the database and paste it the Asset folder thus replacing old one, and the incitement version number.

These changes appear to new users after first installation BUT DO NOT appear to existing users after update.

I would like to reflect all changes and STILL PRESERVE user data (scores) that are already stored in the database.

I have tried the following: 1- copyDataBase onUpgrade - RESULT: changes and not reflected and user data is preserved. 2- Create a new_sqlite_database, copyDataBase onUpgrade using the new_sqlite_database - RESULT: changes are reflected, app crashes onclicklistner of a stage only once and works thereafter, user data is not preserved. 3- Alter Table with temp names, Drop old tables, create new tables with same old names, copyDataBase, update new tables records using temp tables - RESULT: Blank screens.

QuizDbHelper

class QuizDbHelper extends SQLiteOpenHelper {

    private static final String DATABASE_NAME = "qa_sqlite.db";
    private static String DATABASE_LOCATION = "/data/data/packagename/databases/";
    private static final int DATABSE_VERSION = 12;
    private final Context mContext;
    private SQLiteDatabase mDatabase;
    private final Handler handler = new Handler();

    public QuizDbHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABSE_VERSION);

        if (android.os.Build.VERSION.SDK_INT >= 17)
            DATABASE_LOCATION = context.getApplicationInfo().dataDir + "/databases/";
        else
            DATABASE_LOCATION = "/data/data/" + context.getPackageName() + "/databases/";
            this.mContext = context;

        copyDataBase();
        this.getReadableDatabase();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        copyDataBase();
        Log.w("POSITIVE ... ","onCreate Called - Database Copied");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if (newVersion > oldVersion) {
            copyDataBase();
            Log.w("POSITIVE ... ", "onUpgrade Called - Database Copied");
        }
    }


    private boolean checkDataBase() {
        File dbFile = new File(DATABASE_LOCATION + DATABASE_NAME);
        return dbFile.exists();
    }


    private void copyDataBase() {
        if (!checkDataBase()) {
            this.getReadableDatabase();
            this.close();
            try {
                copyDBFile();
            } catch (IOException mIOException) {
                throw new Error("ُCopy Database Failed");
            }
        }
    }


    private void copyDBFile() throws IOException {

        InputStream mInput = mContext.getAssets().open(DATABASE_NAME);
        OutputStream mOutput = new FileOutputStream(DATABASE_LOCATION + DATABASE_NAME);
        byte[] mBuffer = new byte[1024];
        int mLength;
        while ((mLength = mInput.read(mBuffer)) > 0)
            mOutput.write(mBuffer, 0, mLength);
        mOutput.flush();
        mOutput.close();
        mInput.close();
    }

 private void openDatabase() {
        String dbPath = mContext.getDatabasePath(DATABASE_NAME).getPath();

        if (mDatabase != null && mDatabase.isOpen()) {
            return;
        }
        mDatabase = SQLiteDatabase.openDatabase(dbPath, null, SQLiteDatabase.OPEN_READWRITE);
    }

    private void closeDatabase() {
        if (mDatabase != null) {
            mDatabase.close();
        }
    }
}

Database should be created or updated using onCreate or onUpgrade in QuizDbHelper after calling mDBHelper.getReadableDatabase(); onCreate in Mainavtivity.java ... example: Mainavtivity.java

protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        QuizDbHelper mDBHelper = new QuizDbHelper(this);
        mDBHelper.getReadableDatabase();

       SOME OTHER CODE
    }

I NEED Two thing:: 1. Reflect all changes after users update the app 2. PRESERVE user's data (scores) that are already stored in the database.

Answer 1

I managed to achieve the first objective reflecting all changes after app upgrade ... here is what I have done:

**// 1. Check database version:**

private Boolean checkDataBaseVersion() {
    Boolean Upgrade = false;

    SQLiteDatabase db = mContext.openOrCreateDatabase(DATABASE_NAME, Context.MODE_PRIVATE, null);
    Integer CurrentdbVersion = db.getVersion();

    if (CurrentdbVersion < DATABSE_VERSION) {
        Upgrade = true;
    } else {
        Upgrade = false;
    }

    Log.w("POSITIVE ...", "checkDataBase - Database Upgrade = " + Upgrade + " ... CurrentdbVersion: "
    + CurrentdbVersion + " DATABSE_VERSION: " + DATABSE_VERSION);

    db.close();
    return Upgrade;
}



**// 2. change copy database code:**
    public void copyDataBase() {
        if (!checkDataBase()) {
            this.getReadableDatabase();
            this.close();
            try {
                copyDBFile();
                Log.w("POSITIVE ...", "copyDataBase - Database does not exists ... db copied");
            } catch (IOException mIOException) {
                throw new Error("حدث خطأ في نسخ قاعدة البانات من الملف");
            }
        } else if (checkDataBaseVersion()) {
            //this.getReadableDatabase();
            //this.close();
            try {
                copyDBFile();
                Log.w("POSITIVE ...", "copyDataBase - Database exists - new version exists ... db copied ");
            } catch (IOException mIOException) {
                throw new Error("حدث خطأ في نسخ قاعدة البانات من الملف");
            }
        } else {
            Log.w("POSITIVE ...", "copyDataBase - Database exists - no new version ... nothing done ");
        }
    }

    private boolean checkDataBase() {
        File dbFile = new File(DATABASE_LOCATION + DATABASE_NAME);
        Log.w("POSITIVE ...", "checkDataBase - Database exists = " + dbFile.exists());

        close(); /// NEW
        return dbFile.exists();
    }


**// 3. remove copy database code from public QuizDbHelper(Context context) and place it where it is needed:**

  public QuizDbHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABSE_VERSION);

        if (android.os.Build.VERSION.SDK_INT >= 17)
            DATABASE_LOCATION = context.getApplicationInfo().dataDir + "/databases/";
        else
            DATABASE_LOCATION = "/data/data/" + context.getPackageName() + "/databases/";

        this.mContext = context;
}

Answer 2

I haven't ever had to do this for one of my apps, but if I did, I would have a method to merge the new version of the database with the old one. The basic flow would be to see if the local database were present, if not, copy the new database to the local folder. If it does exist, check the db version. If the db version is less than the new database version, then copy the new database to the local folder with a temp name, then call doMerge().

doMerge would open the new database and select all rows from one of the tables into a cursor. Then, it would iterate through the cursor and do a select query on the old database to see if that record already exists in the old database, if so, skip it. Say, the new database has a cursor of all of the Stages records, you take each record's Name or Description value and "SELECT * FROM old.stages WHERE Name = '" + newName + "'"

If not found, insert the values from the new database into the old database. Repeat this for all three tables.

The trick will be managing the primary keys in each table. What if the user has inserted 50 records in one of the tables, and those keys match the primary keys in the new rows in the new database? If you have primary/foreign keys in your other tables, you would have to sync them, which would get hairy.