Using Sqlite3 VACUUM command on Core Data SQLite Persistent store

Question

In our app, we are implementing sharing of partial Core Data SQLite database through network/email. In order to keep the file size small, I have implemented the below method to shrink the Core Data database.

    - (void) shrinkDB
    {
        sqlite3 * database;
        NSString * string = [shareStoreURL path];
        const char * filename = [string cStringUsingEncoding:[NSString defaultCStringEncoding]];
        char *errMsg;
        if (sqlite3_open(filename, &database) == SQLITE_OK)
        {
            NSLog(@"Shrinking...");
            if (sqlite3_exec(database, "VACUUM;", NULL, NULL, &errMsg) != SQLITE_OK)
            {
                NSLog(@"Failed execute VACUUM");
            }
            sqlite3_close(database);
         }
        }

QUESTION: The above code does shrink the database. But Apple says the implementation details of Core Data are subject to change any time. Do you think I would be safe using this method for foreseeable future? Or is there any other better solution?

Answer 1

The proper way to do this is by giving the NSSQLiteManualVacuumOption to the persistent store coordinator.

Snippet from documentation:

NSSQLiteManualVacuumOption

Option key to rebuild the store file, forcing a database wide defragmentation when the store is added to the coordinator. This invokes SQLite's VACUUM command. It is ignored by stores other than the SQLite store. Available in OS X v10.6 and later. Declared in NSPersistentStoreCoordinator.h.

See this: https://developer.apple.com/library/mac/#documentation/Cocoa/Reference/CoreDataFramework/Classes/NSPersistentStoreCoordinator_Class/NSPersistentStoreCoordinator.html

Answer 2

How Apple structures persistent data in an SQLite database is an implementation detail which is subject to change. However, the method by which SQLite manages deleted records is independent of Apple's implementation.

That being said, the process of vacuuming a SQLite database results in rebuilding the entire database, which may have negative effects if the sqlite file is in use by a CoreData NSPersistentStoreCoordinator.

In your case, it sounds like you want to vacuum after saving changes but before sending it via email. Using the NSSQLiteManualVacuumOption option appears to only vacuum the DB when the SQLite file is initially opened.

I'd either run the above code after the file is no longer associated with a NSPersistentStoreCoordinator or use the NSSQLiteManualVacuumOption then re-open and close the file before sending it via email.

Another option is to use an external SQLite tool, such as Base on OS X, to manually vacuum files. I've also used the Firefox SQLite manager extension in the past.