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>
A thousand words 