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?

Possible duplicate of How to VACUUM a Core Data SQLite db?

OrangeDog
– OrangeDog

2016-09-19 15:57:38 +00:00
Commented Sep 19, 2016 at 15:57 — OrangeDog
– OrangeDog, Commented Sep 19, 2016 at 15:57

Jody Hagins · Accepted Answer · 2012-08-05 23:50:06Z

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

Thank Jody! As Scott said below, Looks like I would have to reopen and close before sending via email.
If you use the core data interface, not necessarily. You could use multiple persistent store coordinators. However, the other would probably be locked out until the vacuum is complete. FWIW, how is that a blocker? Do you really think it would be better to run a pure SQL command underneath an open and running core data stack?

Scott Ahten · Accepted Answer · 2012-08-06 00:11:23Z

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.

Thanks Scott! I am removing the persistent store from the Core Data stack before running the code.

Collectives™ on Stack Overflow

Using Sqlite3 VACUUM command on Core Data SQLite Persistent store

2 Answers 2

2 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Linked

Related