In roughing out the early data framework for Daily Value, one of the early considerations was data persistence – particularly user created data. The app’s requirements included supporting user entered food data that could be treated “on par” with shipped food data.
Looking around at what the iPhone SDK supported, a couple choices quickly presented themselves: archiving, property lists, and SQLite. While the temptation was to leap right into a relational database solution, some consideration was made for the other choices and in truth, Daily Value employs all of these techniques for various pieces of data.
There’s a nice write up in “Beginning iPhone Development” by Dave Mark and Jeff LaMarche so I won’t dive too deeply into it except to say property lists and archiving were discarded. Although writing data in a binary format with archiving had some appeal for data obfuscation purposes, it would have also meant writing a bunch of “database-like” functionality that didn’t seem a good technical investment in time or resources. So, SQLite was what I went with.
SQLite offers a lot of upside:
- The API is written in C and is easy to navigate.
- Storage and manipulation of the data was offloaded to the database framework.
- There are a number of free tools that can be used to browse and quickly inspect the data (ex. SQLite Manager for FireFox)
- Creation can be automated
- Good performance on complex queries
The biggest drawback is an apparent lack of authentication or way to protect the data placed into the database. Since our data was publicly available, this wasn’t much of an issue but it might be for some other apps.
The initial design called for all the data – both shipped and user generated – to be stored in a single database file. The thought being that it’d be easier to code queries against and manage. User data would contain some differentiator (either a column identifier or field) to denote it as “editable” (you don’t want folks mucking about with your data).
Apps that use persistent data, or write back cannot do so to files stored within their application bundle or else it breaks the code signing and things stop working. So, when the app initially loads, the source data is copied to the Documents directory and opened from there. Of course, the app checks first to see if the file already exists so it doesn’t overwrite an existing file.
Test rigs built for the simulator seemed to indicate that this was viable, but putting the app on the actual device proved otherwise. Apple requires that apps fully launch quickly after no more than 5 seconds. The first database prototypes were nearly 4 MB. The bulk initial copy caused startup to be unacceptable taking over 10 seconds!
Trimming the database helped. With a little bit of effort in making tables more relational and dropping columns that weren’t strictly required, the size got squeezed down to just under 2 MB. Even so, the copy took too long – so what to do?
After digging about through the SQLite documentation, I came across the SQL “attach” command. One neat feature in SQLite is that it supports the notion of opening multiple databases and treating them (generally) as a single entity. Care was taken to create a “template” user database with pre-created tables that were different than the app supplied database. While not strictly necessary since SQLite will allow you to have duplicate table names, it seemed prudent and an easy “design time” step.
When the app launches, if it doesn’t already exist, the “empty” user database (all 12 kb of it) is copied from the main bundle to the Documents directory and opened. Then, the app “attaches” to the large bundle database with the restriction that none of the bundled data can be modified. Startup times were acceptable and the additional complexity was very minimal due to the SQLite abstraction support.
Here’s a bit of code to get the job done – assuming that
dbHandle is a member variable of your data class.
NSFileManager *fm = [NSFileManager defaultManager];
NSArray *docPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *docDir = [docPath objectAtIndex:0];
NSString *writePath = [docDir stringByAppendingPathComponent:@”user.db”];
BOOL success = [fm fileExistsAtPath:writePath];
NSString *dbPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:”user.db”];
success = [fm copyItemAtPath:dbPath toPath:writePath error:&error];
NSAssert1(0, @”failed to create writable database with msg ‘%@’”, [error localizedDescription]);
NSArray *path = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *docDir = [path objectAtIndex:0];
NSString *openPath = [docDir stringByAppendingPathComponent:”user.db”];
if (sqlite3_open([openPath UTF8String], &dbHandle) == SQLITE_OK)
NSString *sysDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:”main.db”];
NSString *sqlString = [NSString stringWithFormat:@”ATTACH DATABASE ‘%@’ AS main”, sysDBPath];
const char *sql = [sqlString UTF8String];
int success = 0;
success = sqlite3_prepare_v2(dbHandle, sql, -1, &statement, NULL);
NSAssert1(success == SQLITE_OK, @”Failed to prepare ATTACH with msg ‘%s’”, sqlite3_errmsg(dbHandle));
success = sqlite3_step(statement);
NSAssert1(success == SQLITE_DONE, @”Failed to execute ATTACH with msg ‘%s’”, sqlite3_errmsg(dbHandle));
NSAssert1(0, @”Failed to open database with msg ‘%@’”, sqlite3_errmsg(dbHandle));