sqlite DB to-do during iphone app update

Question

I have some general questions about iphone app updates that involves sqlite db.

1. With the new update does the existing sqlite db get overwritten with a copy of the new one?

2. If the update doesn't involve any schema changes then the user should be able to reuse the existing database with their saved data, right? (if the existing database doesn't get overwritten from 1 above )

3. If there are some schema changes, what's the best way to transfer data from the old database into the new one? Can some one please give me guidelines and sample code?

Answer 1

1. Only files inside the app bundle are replaced. If the database file is in your app's Documents directory, it will not be replaced. (Note that if you change files inside your app bundle, the code signature will no longer be valid, and the app will not launch. So unless you are using a read-only database, it would have to be in the Documents directory.)

2. Yes.

3. What's best depends on the data. You're not going to find sample code for such a generic question. First, you need to detect that your app is running with an old DB version. Then you need to upgrade it.

To check versions:

• You could use a different file name for the new schema. If Version2.db does not exist but Version1.db does, do an upgrade.
• You could embed a schema version in your database. I have a table called metadata with a name and value column. I use that to store some general values, including a dataversion number. I check that number when I open the database, and if it is less than the current version, I do an upgrade.
• Instead of creating a table, you could also use sqlite's built-in user_version pragma to check and store a version number.
• You could check the table structure directly: look for the existence of a column or table.

To upgrade:

• You could upgrade in place by using a series of SQL commands. You could even store a SQL file inside your app bundle as a resource and simply pass it along to sqlite3_exec to do all the work. (Do this inside a transaction, in case there is a problem!)
• You could upgrade by copying data from one database file to a new one.

If your upgrade may run a long time (more than one second), you should display an upgrading screen, to explain to the user what is going on.