SQLite, iPhone and Versioning

Question

I want to add an updated SQLite database with a new version of the application. My application copies the database file to the Documents directory at startup. What is the best way to execute this kind of version (other than using Core Data)?

I assume that either a special “version” table in the SQLite file, or a small text file with a version number is the way to go, but I would like to get other people's opinions.

Answer 1

After trying several methods, I eventually added a table to my database for meta-information and placed a timestamp column. Each time I update my application, I check the time stamp of the package database for the time stamp of the copied database (that is, in the Documents directory). This means that I have to remember to change the timestamp value when updating, but it just works.

The use of file timestamps did not work, since it is possible for the user to load the application into the application’s viewing time window and ending with the copied database with a newer timestamp than the one in the package.

Answer 2

. SQLite , user_version. SQLite , .

:

#pragma user_version;

:

#pragma user_version=1;

Answer 3

, , - . SQLite DB .app , ​​ , , .app. , .

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

ES_CHECK - , ... :

#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)