How to update SQLite DB on iPhone app update?

Question

I currently have an iPhone app in the iTunes app store that uses a SQLite database as a datastore. The user can sync the app against a web service and data that is returned is then stored in the SQLite database. The user can also insert new items into the database and sync their changes back up to the web service.

When a connection is opened to the app's local database, the code checks to confirm that the database file exists in the "Documents" folder, if it does not the database is created by being copied from the "Resources" folder to the "Documents" folder.

I have to release an update to the app. The update contains database schema changes (new tables, columns, etc.). From what I have read, files in the "Documents" directory will persisted on an app update, meaning the existing database will still be intact after the update.

My question is, how do I replace the existing database with my updated database and is there a way to do so without losing the data in the existing database? Is there some sort of "first run after update" event I can work with to do the database update, or do I have to do some sort of check on the existing database (look for a new column or table) and if my check fails manually replace/update the database?

Thanks!

Answer 1

You should store the current version number of the app somewhere persistent -- in the database or better yet in the Defaults database. On startup, your app will compare it's own version number with the persisted version number. If they differ, then this is the first run after an update. If the persisted version number isn't there, then obviously also this is the first run after an update.

As for updating your database, if it's in place you would use the usual SQL ALTER commands to update your schema, and do any database data migration at the same time.

Answer 2

We check for updates by querying the server for a hash value in the header of a plist that is spit out via a php file that queries the server database. You can store that hash locally and compare it to the last time the app ran. That way the phone knows if its version is out of date. Then we download the new plist in the background and update the database on the phone.

EDIT:

At the end of our php, we get an MD5 of the XML output of the plist we are generating on the server like this:

header("MD5-Hash: ". md5($xml_output));
echo $xml_output;

Then we get the hash of the plist on the iPhone from userDefaults like so:

NSUserDefaults* defaults  = [NSUserDefaults standardUserDefaults];
curHash = [defaults stringForKey:kUpdateUserDefault];

And the server's hash from the NSURLRequest, like so:

NSString *hash = [[[res allHeaderFields] objectForKey:kUpdateHeaderField] retain];

Then we compare the two and start the download only if the hashes don't match:

    if (![curHash isEqualToString:hash]) {
            [self performSelector:@selector(sendUpdateStarted) onThread:[NSThread mainThread] withObject:nil waitUntilDone:NO];
            ... download the file and save it as the new iPhone's plist
    }

This code is written by my very capable partner, Oliver Rice.