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 former has some limitations: we could get data via web service, but note we still need to manipulate the database if we need to add a column or table (this is same for both approaches). The latter is faster in terms of development. So I decided to do it. I did some research on stackoverflow and found this post as my reference. Here is my implementation, if you are interested. The following is done in viewDidLoad.

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];