SQLite, iPhone and versioning

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 开发者_如何学Pythondo 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.

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;

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)

After trying a few techniques, I ended up adding a table to my database for meta-information and putting in a timestamp column. Each time I update my app, I check the timestamp of the bundle database against the timestamp of the copied database (i.e. in the Documents directory). It means I have to remember to change the timestamp value when I update, but it's simple and it works.

Using file timestamps didn't work, as there's a possibility of the user downloading the app in the App Review time window, and ending up with a copied database with a newer timestamp than the one in the bundle.