Update sqlite database during iOS app update

We all know updating sqlite database on an existing iOS app could be tricky, esp. with any schema (table) changes. In the good old days when I was developing in-house app, I found out the app would crash when I added a column to the sqlite database, after merely updating the app. The problem was, the old database won’t get updated if we don’t explicitly do it during app start-up (after app update). So what did I tell my user to do? Delete the app, and re-install the app. Not the most user-friendly way, and what if the user has some data on it that he/she wants to keep? What if this app have many users?

I had this updating issue in my mind for a while, because one of my apps needs to refresh its data. There are a few ways to do it: 1) Use a web service to get the data; 2) Just update the sqlite database. The latter is faster in terms of development. So I decided to do it. I did some research <a href=”http://stackoverflow.com/questions/2767849/how-to-update-sqlite-db-on-iphone-app-update”>on stackoverflow and found this post</a> as my reference. Here is my implementation, if you are interested. The following is done in viewDidLoad.

<code>
Database *dbAccess = [[Database alloc] init];

// do this instead…
// http://stackoverflow.com/questions/1601151/how-do-i-check-in-sqlite-whether-a-table-exists
// SELECT name FROM sqlite_master WHERE type=’table’ AND name=’version’;

BOOL doesVersionTableExist = [dbAccess doesVersionTableExist];
NSLog(@”doesVersionTableExist =%i”, doesVersionTableExist);

if (doesVersionTableExist == FALSE) {
// this only needs to be done once
BOOL createVersionTableSuccessful = [dbAccess createVersionTable];
NSLog(@”createVersionTableSuccessful =%i”, createVersionTableSuccessful);
}

// version =1.2.3; build =1.2.3
NSString *version_from_app = [[[NSBundle mainBundle] infoDictionary] objectForKey:@”CFBundleShortVersionString”];
NSString *build_from_app = [[[NSBundle mainBundle] infoDictionary] objectForKey:(NSString *)kCFBundleVersionKey];
NSLog(@”version_from_app =%@; build_from_app =%@”, version_from_app, build_from_app);

NSString *version_from_database;
NSString *build_from_database;
doesVersionTableExist = [dbAccess doesVersionTableExist];
NSLog(@”doesVersionTableExist =%i”, doesVersionTableExist);
if (doesVersionTableExist == TRUE )
{
// check buildNumber and version, theoritically we should update it after updating the database
NSMutableArray *buildNumberVersionArray = [dbAccess getBuildNumerAndVersion];
if (buildNumberVersionArray != NULL && [buildNumberVersionArray count]>0) {
BuildNumberVersion *buildNumberVersion = [buildNumberVersionArray objectAtIndex:0];
version_from_database = buildNumberVersion.version;
build_from_database = buildNumberVersion.buildNumber;
}

if ([build_from_database isEqualToString:@”1.2.4″] == FALSE || [version_from_database isEqualToString:@”1.2.4″] == FALSE) {
[self update1For2014:dbAccess];

NSLog(@”update build number and version: update1For2014″);
// insert or update, if we can it everytime during startup.
[dbAccess updateVersion:version_from_app withBuildNumber:build_from_app];
}

[self doWeNeedToRunUpdate2For2014:build_from_app version_from_app:version_from_app dbAccess:dbAccess version_from_database:version_from_database build_from_database:build_from_database];
}

// now I can use the refreshed database and tables

// Close the database because we are finished with it
[dbAccess closeDatabase];
</code>