SQLite, iPhone and versioning

Question

I want to include an updated SQLite database with a new version of an app. My app copies the database file into the Documents directory on startup. What is the best way to do this kind of versioning (besides using Core Data)?

I'm assuming that either a special 'version' table in the SQLite file or a small text file with the version number is the way to go, but I'd like to get other peoples opinions.

Answer 1

The way I do this is by looking at filestamps. If the modification date of the SQLite DB file in the .app bundle is more recent than the one in the local documents directory, then I copy the one from the .app bundle over... Here's the code I use.

sqlite3 *dbh;           // Underlying database handle
NSString *name;         // Database name (this is the basename part, without the extension)
NSString *pathBundle;   // Path to SQLite DB in the .app folder
NSString *pathLocal;    // Path to SQLite DB in the documents folder on the device

- (BOOL)automaticallyCopyDatabase {                             // Automatically copy DB from .app bundle to device document folder if needed
    ES_CHECK(!dbh, NO, @"Can't autoCopy an already open DB")
    ES_CHECK(name!=nil, NO, @"No DB name specified")
    ES_CHECK(pathBundle!=nil, NO, @"No .app bundle path found, this is a cache DB")
    ES_CHECK(pathLocal!=nil, NO, @"No local document path found, this is a read-only DB")
    NSFileManager *fileManager = [NSFileManager defaultManager];
    NSDictionary *localAttr = [fileManager fileAttributesAtPath:pathLocal traverseLink:YES];
    BOOL needsCopy = NO;
    if (localAttr == nil) {
        needsCopy = YES;
    } else {
        NSDate *localDate;
        NSDate *appDBDate;
        if (localDate = [localAttr objectForKey:NSFileModificationDate]) {
            ES_CHECK([fileManager fileExistsAtPath:pathBundle], NO, @"Internal error: file '%@' does not exist in .app bundle", pathBundle)
            NSDictionary *appDBAttr = [fileManager fileAttributesAtPath:pathBundle traverseLink:YES];
            ES_CHECK(appDBAttr!=nil, NO, @"Internal error: can't get attributes for '%@'", pathBundle)
            appDBDate = [appDBAttr objectForKey:NSFileModificationDate];
            ES_CHECK(appDBDate!=nil, NO, @"Internal error: can't get last modification date for '%@'", pathBundle)
            needsCopy = [appDBDate compare:localDate] == NSOrderedDescending;
        } else {
            needsCopy = YES;
        }
    }
    if (needsCopy) {
        NSError *error;
        BOOL success;
        if (localAttr != nil) {
            success = [fileManager removeItemAtPath:pathLocal error:&error];
            ES_CHECK(success, NO, @"Can't delete file '%@'" ,pathLocal)
        }
        success = [fileManager copyItemAtPath:pathBundle toPath:pathLocal error:&error];
        ES_CHECK(success, NO, @"Can't copy database '%@' to '%@': %@", pathBundle, pathLocal, [error localizedDescription])
        ES_TRACE(@"Copied DB '%@' to '%@'", pathBundle, pathLocal)
        return success;
    }
    return YES;
}

The ES_CHECK things are just macros that expand to nothing in release mode, and raise an exception in debug mode... They look like this:

#if ES_DEBUG
#define ES_ASSERT(cond) assert(cond);
#define ES_LOG(msg...) NSLog(msg);
#define ES_TRACE(msg...) NSLog(msg);
#else
#define ES_ASSERT(cond)
#define ES_LOG(msg...)
#define ES_TRACE(msg...)
#endif
#define ES_CHECK(cond, ret, msg...) if (!(cond)) { ES_LOG(msg) ES_ASSERT(cond) return (ret); }      // Check with specified return value (when condition fails)

Answer 2

No need for a specialized table. SQLite has a pragma for this, called user_version. SQLite doesn't use this value for anything, it's left entirely to the application.

To read the version:

#pragma user_version

To set the version:

#pragma user_version 1