Android how to update, but retain database info

Question

I have an app that uses a SQLite database. When the app updates at the moment it completely overwrites the database.

The problem arises when I want to transfer the users current progress to the new update. The db table contains rows of questions. Each row contains 1 question, the answers, the reason for the correct answer, whether the user has answered it and if the user answered correctly.

In an update a question could have been deleted and/or others added. The only data that needs to be retained is if a question has been answered and it it is answered correctly. Is there a better method for transferring the data other than comparing a unique number or string from each row of the old db to the new db?

With over 100 rows this seems like it will be very resource intensive. Although I cannot see another way around this problem.

Any advice and help would be gratefully received.

Answer 1

First allocate each table a unique id - Primary Key (PK)

Question table - many to one relationship with User
Answer table - one to one relationship with Question
User table - one to many relationship with Question

Question
+--------------+------------+------------------+
| int          | Id         | PK               |
| varchar(max) | question   |                  |
| int          | userId     | FK (Foreign Key) |
| bool         | answered   |                  |
| bool         | correct    |                  |
+--------------+------------+------------------+

Answer
+--------------+------------+----+
| int          | Id         | PK |
| int          | questionId | FK |
| varchar(max) | reason     |    |
+--------------+------------+----+

User
+---------------+-------------+--------------------------------------------+
| int           | Id          | PK                                         |
| varchar (250) | deviceToken | (UUiD) // some unique identifier per phone |
+---------------+-------------+--------------------------------------------+
// other relevant stuff  

When the app is downloaded the user can be silently registered, using the device UUID. The central database will need to keep track of these and the questions that are answered, rather than wipe it all out and start again. 100 rows is not many but the users could potentially run into the 1000s or more. In an update it's not relevant that it may be slow to repopulate the local database in the phone (although it wouldn't necessarily be slow with this many rows, a database with millions of rows would take time) as it is expected that updates take time.

If the user changes device this information is not transferred to the new device. Each device is treated as a new user. I find that this works well if you are not wanting people to sign up but wanting to preserve data during updates, or if the app is uninstalled and reinstalled on the one device. It has its limitations as does asking people to sign up. If users want a fresh start on the game with the same device you can always provide an option "Reset statistics" and then wipe that data.

Shared preferences can also be used to save user settings for the app, I think that it may be overkill for a hundred questions, it would better be suited to store this information in a SQLite database; the info being kept on the server. You cannot wipe the data every time there is an update, you must keep current records of the consumer's progress. You cannot rely on the consumer's device to retain the information. If there is any information you want to keep track of, you must take responsibility for it.

This can be stored locally on the phone and synced with the server regularly.

In our apps, this is how we do it and the data survives updates and we have millions of rows. Feel free to ask more questions, however giving an actual tutorial (or the code) for how this all works is a bit broad an answer for Stack Overflow.

Answer 2

I agree with @Yashwanth Kumar a different design might be better long term, but 100 rows isn't massive.

Implement logic in DBhelper.onUpgrade().

You might want to look at storing the answers in shared preferences while the update operation is taking place encase it's killed by the system/user.

Answer 3

I could suggest an alternative in your design, maintain 2 tables, one for answered correctly , and the other for rest. when a question has been answered correctly, transfer the question from one table to answered questions table.

so when you are updating, you just drop the unanswered questions table and populate it with fresh ones. answered questions are unharmed in the way.