Android pre-packaged database update

Question

I'm developing a quiz-type application for android, and aiming to be able to expand the question bank over time. To do this, I'm pre-packaging the SQLite database, and checking for updates and overwriting it on upgrade:

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    if(newVersion>oldVersion) {
        InputStream inputStream = null;
        OutputStream outStream = null;
        String dbFilePath = DATABASE_PATH + DATABASE_NAME;
        try {
            inputStream = myContext.getAssets().open("questions.db");
            outStream = new FileOutputStream(dbFilePath);
            byte[] buffer = new byte[1024];
            int length;
            while ((length = inputStream.read(buffer))>0) {
                outStream.write(buffer, 0, length);
            }

            outStream.flush();
            outStream.close();
            inputStream.close();

        } catch(IOException e) {
            throw new Error("problem copying database from resource file");
        }
    }
}

What I'm unsure of is how to save whether a question has been attempted (and prevent that from being overwritten on upgrade). It seems my options are:

1) Save a boolean shared preference for every question id (potentially a LOT of sharedprefs)

2) Create a separate database to hold the data on which questions have been answered (seems like overkill)

3) Keep the results data in a separate table of the same database, and then ensure that only the questions table gets overwritten on upgrade

4) Keep the user data in its own column within the questions table, but prevent that column from being overwritten.

Of the above, 4 seems the most elegant (though not sure if possible). What is the best way of going about it (and if it's option 3 or 4, how do I go about modifying the code?!)

Much appreciated!

The above code overwrites the whole database every time the app is upgraded. The problem is that this will also overwrite anything I save in that database from the app. My question is how to only replace certain tables or columns, instead of wiping the whole thing. — cg5572, Jun 07 '12 at 21:26
Are you looking for this? http://stackoverflow.com/questions/9109438/using-already-created-database-with-android/9109728#9109728 — Yaqub Ahmad, Jun 08 '12 at 02:33

score 0 · Answer 1 · answered Jun 07 '12 at 21:31

0

If you want to preserve data in the old database, use SQL to parse/read your records for both database. Only insert new questions in your old database thus getting a new database will all the data via a "INSERT OR UPDATE" statement.

answered Jun 07 '12 at 21:31

pjulien

1,369
10
14

score 0 · Answer 2 · answered Jun 07 '12 at 21:33

0

no way! because of structure of SQLite database you cant change its file yourself

but maybe you have to alternative:

read all of database, change your desire table then write it.
store your more rewritable table separate. (this solution is dummy, I know)

answered Jun 07 '12 at 21:33

Maziar Aboualizadehbehbahani

1,976
17
37

Android pre-packaged database update

2 Answers2