Update sqlite database during iOS app update

Reading Time: 2 minutes

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

%d bloggers like this: