Android how to update but save database information

Question

I have an application that uses a SQLite database. When an application is currently being updated, it completely overwrites the database.

The problem occurs when I want to transfer the current user move to a new update. Table db contains rows of questions. Each line contains 1 question, answers, the reason for the correct answer, whether the user answered it and whether the user answered correctly.

In the update, the question may be deleted and / or others. The only data that needs to be saved is the question that was answered, and it was answered correctly. Is there a better way to pass data other than comparing a unique number or row from each row of the old db to the new db?

With over 100 lines, it looks like it will be very resource intensive. Although I do not see another way to solve this problem.

Any advice and help would be greatly appreciated.

Answer 1

I agree with @Yashwanth Kumar that another design may be longer-term, but 100 lines are not massive.

Embed the logic in DBhelper.onUpgrade () .

You might want to take a look at saving answers in general preferences during the update operation so that it is killed by the system / user.

Answer 2

First, highlight each table with a unique identifier - Primary Key (PK)

Question table - many-to-one relationship with user
Answer table - one to one relationship with the question
User table - one to many relationship with a 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 application loads, the user can be discreetly registered using the UUID of the device. A central database will need to track these and answers to questions, and not destroy it all and start over. 100 lines are not many, but users can potentially run 1000 or more. It is not relevant in the update that it can be slow to repeat the local database on the phone (although it will not necessarily be slow with this many rows, it will take time for a database with millions of rows), since the update is expected to take time.

If the user changes the device, this information is not transferred to the new device. Each device is considered as a new user. I believe that this works well if you do not want people to register, but want to save data during updates or if the application was deleted and reinstalled on the same device. It has its limitations, as well as asking people to register. If users want to start a new game using the same device, you can always provide the "Reset statistics" option and then erase this data.

General preferences can also be used to save user settings for the application, I think this may be redundant for a hundred questions, it is better suited for storing this information in a SQLite database; information is stored on the server. You cannot erase the data every time there is an update, you must save the current records of the progress of work with the consumer. You cannot rely on a consumer device to store information. If there is any information that you want to track , you should take responsibility .

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

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

Answer 3

I could offer an alternative in your design, support 2 tables, one for the correct answer, and the other for relaxation. if the question is answered correctly, pass the question from one table to the answer table.

therefore, when you update, you simply discard the unanswered question table and fill it with fresh ones. Answers to questions are unharmed.