On-The-Fly Database Updates

I love the fact that the iPhone comes with an installation of SQLite*. As someone who's worked with relational databases and the SQL language for his entire professional career, I happily use it to store the information I need for my iPhone app.

But with an embedded database comes the need to alter its structure on the fly as my data requirements change. I'm still in beta testing, so I could tell all my testers to nuke their data the next time they install my application, but I'd prefer to avoid that, and I certainly won't tell my future customers to do that.

Instead, I came up with a simple system for making the app run its own database updates. I have a class, DBAccess, that puts an object-oriented wrapper around the very procedural SQLite calls. I created a new method, installChangeScripts, that looks like this:

- (void) installChangeScripts {
    SEL changeScripts[CHANGE_SCRIPTS];
    
    changeScripts[0] = @selector(makeVersionTable);
    changeScripts[1] = @selector(addTaskDuration);
    
    int versionNum = [self dbVersionNumber];
    
    for (int changeScriptIndex = versionNum + 1; 
                changeScriptIndex < CHANGE_SCRIPTS; 
                changeScriptIndex++) {
        [self performSelector:changeScripts[changeScriptIndex]];
        NSString *versionUpdateQuery = 
              [[NSString alloc] 
                      initWithFormat:@"update app_version set version_num = %d",
                                                     changeScriptIndex];
        [self executeUpdate:versionUpdateQuery];
        [versionUpdateQuery release];
    }
}

The gist is straightforward: Make an array of method selectors (Objective C's way to find a method at runtime), iterate through them starting at the database's current version number + 1, and update the version number after the script has run. The only tricky part is that the version table doesn't exist in the default version of the database, so I have logic to handle the case where it doesn't exist:

- (int) dbVersionNumber {
    int versionNum = -1;
    // get the current version. handle this case differently since it drives the rest
    // of the logic
    NSArray *queryResult = [self executeQuery:@"select version_num from app_version"];
    if ([queryResult count] > 0) {
        // handle the result 
        NSDictionary *versionResult = [queryResult objectAtIndex:0];
        versionNum = [[versionResult objectForKey:@"version_num"] intValue];
    }
    [queryResult release];
    return versionNum;    
}

Originally, this logic was in the installChangeScripts method, but that was an easy refactor: self-contained logic that only produces a single value used by the rest of the method. See the Extract Method refactoring sequence.


Not surprisingly, the first change script creates the version table.

- (void) makeVersionTable {
    [self executeUpdate:@"create table app_version (version_num integer)"];
    [self executeUpdate:@"insert into app_version values (0)"];
}

There's at least one other good refactoring target here: The setup of the list of install scripts should be in its own method because it's a separate concept from actually running the scripts. I just need to remind myself how to deal with calloc, free, and other denizens of the pointer/array abyss.

* I also love the group's motto, "Fast. Small. Reliable. Choose any three." A nice spin on the various "choose two" sayings such as "Fast. Scalable. Cheap. Choose two."