Update/Insert content into an existing Sqlite db on an iPhone app update

Question

I've gone through other questions of the similar nature here but they are all related to replacing the previous sqlite db on app update. I've been doing that for all the previous apps, since they were referential in nature, and a simple replace sufficed.

This particular app I am working with is a quiz game, and the score needs to be maintained. There are 4 levels with around 15 questions at the moment, and on a later update these might increase. Since I have never done this before, I am curious and intend to do this the right way the first time. So here are my queries:

1. What is the preferred way to update the content that the Gurus of SO recommend? Is it to have a version number (perhaps in the DB itself) and upon first run of the new app, the new content is inserted in the table?
2. What is the preferred way to store the insert queries? Should they be hardcoded with in an implementation file?

PS. I copy the database to the Documents directory, so it persists on app updates.

EDIT: I should have added that the quiz app is sort of "logos quiz" replica, in which scores (or status of the question which would be answered/unanswered) of all the questions need to be maintained. So there are 60 questions in the first version, and their statuses are changed as the user answers them.

EDIT 2: This is structure of the Table I am most concerned about, the Questions Table:

_id (Integer, PRIMARY Key), levelId (Integer), QuestionImage (Varchar), CorrectAnswer(Varchar), boolAnsweredCorrectly(Integer).

The quiz is like Logos Quiz. There's an image and the user has to type the answer. If the answer typed matches the correctanswer in DB, the boolAnsweredCorrectly is set to true. My main concern is boolAnsweredCorrectly (which would be 0 or 1 depending on the result).

EDIT 3: When I speak of Inserting data on update of the app, I mean this. Suppose there were 60 questions in the first version. In the second version 40 new questions are added. So, when the user updates the app to the second version, the new 40 questions need to be inserted into the questions table. This is to be added in such a way that the previous 60 questions are not messed up and remain intact.

Answer 1

What is the preferred way to update the content that the Gurus of SO recommend? Is it to have a version number (perhaps in the DB itself) and upon first run of the new app, the new content is inserted in the table?

Depends upon whether you're using your database just to store the results of the person's answers (in which case, you probably don't need to do any "inserting" into the table other than storing the person's quiz responses as they happen) or whether you have some separate tables storing the original questions, too (which probably makes sense if you're not delivering new questions via some server interface).

I personally use a database_version field in a configuration table with the format of "major.minor.revision", where major or minor presuppose database changes that require database update, whereas revision changes don't). But I use this database version number system to know whether I want to recopy my bundle's database into Documents or not (but I do this for only structural changes to my database, and prefer to get new data via server interface, but this depends upon how your solution is designed). If you do programmatically determine that you want to copy the database again, if you have any data that needs to be preserved, though, to Retterdesdialogs' point, you might want to save any data from your old database into the new database.

What is the preferred way to store the insert queries? Should they be hardcoded with in an implementation file?

I don't think you should have a ton of hardcoded INSERT statements, anyway, so this wouldn't be an issue. I gather from your comment below that you anticipate trying to insert new question records based upon the what version the user previously had.

Personally, I'd be more inclined to

• put your latest and greatest questions in a database in your bundle
• have app check version of the Documents database version against what the app was expecting,
• if different (a) save the user's old answers; (b) copy the new database from the bundle to Documents; and (c) update the answers in the new database based upon the old database's answers.

You certainly could do something programmatically inserting records, but just imagine what life will be like when you get to the 20th release of your app and you'll have to have tons of conditional logic about what to insert based upon what the prior version of the app was (because you can't assume the user will always have the previous version ... they could be a few versions out of date). And it gets even hairier when you contemplate each version being a combination of adding, removing, and modifying of questions.

Bottom line, I'd lean towards a database of the current questions, copying it over to Documents if you have to, just making sure if the app has old database with old answers, then manually preserve them. (Or you could store the answers in a separate database.)

Alternatively, and I suspect that this is more than you want to bite off, but you might want to consider using Core Data, the preferred iOS persistent storage model. It appears that it has solved this migration issue as outlined in the Core Data Model Versioning and Data Migration. I've never use Core Data's migration/versioning stuff (I historically have always rolled my own), but it looks promising.

Answer 2

I would do something like this in pseudocode:

// if old database exists in documents directory
// get scores
// delete old database
// copy the new database from bundle to documents directory
// update scores in the new database

To check if old database is the old one, you can check the name or somthink like "is questions == 15"