Deploying sqlite DB on iPhone app upgrade

Question

I'm using sqlite as a datasource in an iPhone app. I have two questions regarding application upgrades.

1.) The data is all config/nontransactional. Meaning, it is readonly. When I update/add config data, I'll issue an upgrade for the app. When a user gets an updated iPhone app, does the original get uninstalled? If that is the case, I'm ok because the new db config data will be deployed. If that isn't the case, how do I replace data?

2.) The data is config and transactional. Meaning the user can save data into the db. When the user upgrades to a new version of the app, I want to maintain their existing data but replace all config data. I'm guessing I'd need to have INSERT and UPDATE scripts stored within the app to accomplish this. What is an efficient way to go about it?

Answer 1

When the user upgrades the app, the old app bundle is uninstalled and the new app bundle is installed, but the user data associated with the app bundle is intact.

So your choices are to a) leave your data in the app bundle (it'll be replaced automatically) or b) unilaterally copy it to the user data area on first run (so you'll intentionally replace it on upgrade).

I'll leave #2 to a sqlite-knowledgable person, but you may want to use the "sqlite" tag instead of the "mysql" tag if that's what you're actually doing.

Answer 2

cdespinosa has described scenario #1 well, so I'll tackle #2.

I haven't done this on the iPhone yet, but on a desktop environment the easiest way to handle this is to keep your configuration data in a separate database. You can attach to multiple databases rather easily. Start by opening your main database, which should probably be the database that can change. Then sqlite3_exec an ATTACH statement, which looks like this:

ATTACH 'filepath' AS config;

From then on, you can do something like this:

SELECT * FROM UserTableName;
SELECT * FROM config.ConfigurationTableName;

It's my understanding that if you write to the configuration database the application will fail a signature check and won't start, and the version of SQLite included with the iPhone is old enough to not support the read only flag. For this reason, you should copy your configuration database file into the sandbox and open that copy instead of the one in your bundle.

(You can, of course, use SQL and copy the values from one database to another. But if you're already copying the entire configuration database to your sandbox... and you should be... then that's just an extra step. Just attach.)

I hope someone else can provide you with more details. :)