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.

answers should have a referential constraint on questions. answers table has questionid. Then you can't break it by changing the questions. — danny117, Oct 12 '16 at 20:25
@Anne did you try using the update with where clause to delete question that doesnt satisfy your creteria? — JAAD, Oct 19 '16 at 09:14

score 6 · Answer 1 · 2016-10-15T15:23:48.190

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.

"The accepted answer says to use database helper on Upgrade, this is only to be used if the database itself is being changed, not for an app update that doesn't include any database changes." -- that is incorrect. You are welcome to increment the schema version used by a `SQLiteOpenHelper` for any reason. — CommonsWare, Oct 15 '16 at 12:41
The point behind `onUpgrade()` is to affect changes to the database, in one spot, that the rest of the app's code will expect to see. Normally, that is just schema changes. But not always. For example, apps that package a database (e.g., via `SQLiteAssetHelper`) might replace a read-only database outright, without changing the schema, just to change the read-only data (e.g., a price list). In the case of this question, it's not unreasonable to use `onUpgrade()` to make non-schema changes to the data that the rest of the app will expect to have happened (e.g., question massaging). — CommonsWare, Oct 16 '16 at 11:51

score 3 · Accepted Answer · answered Nov 19 '11 at 17:10

3

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.

answered Nov 19 '11 at 17:10

scottyab

23,621
16
94
105

Thanks for the advice could you possibly provide me with any links on how to store data in the shared prefs. – Somk Nov 20 '11 at 02:28
here's a so question with some example code, http://stackoverflow.com/questions/3624280/how-to-use-sharedpreferences-in-android-to-store-fetch-and-edit-values but tbh google it – scottyab Nov 20 '11 at 08:51

score 0 · Answer 3 · answered Nov 19 '11 at 16:13

0

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.

answered Nov 19 '11 at 16:13

Yashwanth Kumar

28,931
15
65
69

Good plan only problem is that in an update I may change content from answered questions. Change the wording or edit a spelling mistake for example. Thats is why I have come up with this approach to drop everything and only transfer the user specific info. – Somk Nov 19 '11 at 16:26
that would save the problem of searching, which would compensate for your repopulating i guess. it's just the trade off between the above mentioned things. – Yashwanth Kumar Nov 19 '11 at 16:28

Android how to update, but retain database info

3 Answers3