Core Data debugging with SQLite

Core Data is designed to be a black box. Under the hood on iOS it (normally) uses a SQLite database for data persistance. The use of SQLite is an implementation detail and not one we are encouraged to worry about. However, in practice this detail can be very convenient and accessing the SQLite database directly can be a handy tool for debugging & testing. This post will describe how to do that.


Sample App


All the example code in this post can be found in a demo app, introduced in the previous post, Organising Core Data for iOS.


Locating the Store


In the app delegate class look for the method that creates the NSPersistentStoreCoordinator. Apple's template has it in the persistentStoreCoordinator method.  This is where the store path is passed to the NSPersistentStoreCoordinator (as a URL). Add an NSLog() statement to output the path to the console.


For example, here is a snapshot of the persistentStoreCoordinator method as generated by Apple's template; I added the NSLog() line:

- (NSPersistentStoreCoordinator *)persistentStoreCoordinator
{
    if (__persistentStoreCoordinator != nil)
    {
        return __persistentStoreCoordinator;
    }
    
    NSURL *storeURL = [[self applicationDocumentsDirectory] URLByAppendingPathComponent:@"OrganisingCoreData.sqlite"];
    NSLog(@"Core Data store path = \"%@\"", [storeURL path]);

    NSError *error = nil;
    __persistentStoreCoordinator = [[NSPersistentStoreCoordinator alloc] initWithManagedObjectModel:[self managedObjectModel]];
    if (![__persistentStoreCoordinator addPersistentStoreWithType:NSSQLiteStoreType configuration:nil URL:storeURL options:nil error:&error])
    {
    ...

In the simulator you'll get output something like:


2011-07-01 22:13:25.971 OrganisingCoreData[14217:207] Core Data store path = "/Users/chris/Library/Application Support/iPhone Simulator/4.3.2/Applications/22CD429E-ADD2-4AAA-9C9E-5E57828A6FF8/Documents/OrganisingCoreData.sqlite"


SQLite


Now you've got the Core Data store path, you can hand it to a SQLite client and poke around. Grab a GUI SQLite client if you like, but I recommend the command-line client as it is built-in and easy to use. Open Terminal and run "sqlite3" pasting in the store path as argument.

$ sqlite3 "/Users/chris/Library/Application Support/iPhone Simulator/4.3.2/Applications/22CD429E-ADD2-4AAA-9C9E-5E57828A6FF8/Documents/OrganisingCoreData.sqlite"
-- Loading resources from /Users/chris/.sqliterc
SQLite version 3.6.12
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables
ZDVD          ZPERSON       Z_METADATA    Z_PRIMARYKEY

You can view all the database tables with the ".tables" command. You will see each Core Data entity represented by a table, named after the entity but prefixed with "Z" and all caps. My example app contains a Core Data model with two entities, DVD and Person (see model layout image below). In SQLite we see a table for each entity, "ZDVD" and "ZPERSON". You will also see two extra tables, "Z_METADATA" and "Z_PRIMARYKEY". These are used by Core Data for administration.

You can examine the schema of each table with the ".schema" command. SQLite shows the "CREATE" command for each. This can be useful to see the types that Core Data picks for each field as well as how any indexes are configured.

sqlite> .schema ZPERSON
CREATE TABLE ZPERSON ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, Z_OPT INTEGER, ZUSERNAME VARCHAR, ZNAME VARCHAR );
CREATE INDEX ZPERSON_ZUSERNAME_INDEX ON ZPERSON (ZUSERNAME);
sqlite> .schema ZDVD
CREATE TABLE ZDVD ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, Z_OPT INTEGER, ZOWNER INTEGER, ZPURCHASEDATE TIMESTAMP, ZTITLE VARCHAR );
CREATE INDEX ZDVD_ZOWNER_INDEX ON ZDVD (ZOWNER);

Tables contain a column for each entity attribute, names based on the attribute names, all caps and prefixed with "Z". Some extra administrative tables are also present, each prefixed with "Z_". You may also notice that all tables automatically get a primary key as "Z_PK".


Any one-to-one or one-to-many relationships are tracked in a column, like "ZOWNER" in the example. Many-to-many relationships are tracked in separate tables, automatically managed by Core Data.


You can query the data using all the usual SQL commands.

sqlite> SELECT * FROM ZPERSON;
Z_PK        Z_ENT       Z_OPT       ZUSERNAME   ZNAME      
----------  ----------  ----------  ----------  -----------
1           2           3           chris       Chris Miles

sqlite> SELECT ZUSERNAME,ZPURCHASEDATE,ZTITLE FROM ZDVD LEFT JOIN ZPERSON ON ZPERSON.Z_PK=ZDVD.ZOWNER WHERE ZUSERNAME='chris';
ZUSERNAME   ZPURCHASEDATE  ZTITLE      
----------  -------------  ------------
chris       279979769      The Hangover
chris       -61041031      Terminator 2

You can also modify the data, if care is taken not to invalidate any of the Core Data administrative fields, or create broken relationships or other broken states.


Recently I added a weekly progress graph to an iPhone project and part of the testing required data that was generated over the period of many weeks. Waiting a few weeks until real data was collected was out of the question, so I simulated accelerated use by repeatedly advancing the date field values by a few days at a time and restarting the app each time.


For example, in my demo app I set a purchased date of 2009-11-15 for "The Hangover", which is stored as 279979769 (seconds since a reference date).  By accessing the Core Data store directly I can advance this date by one week with the SQL query:

sqlite> UPDATE ZDVD SET ZPURCHASEDATE=ZPURCHASEDATE+(60*60*24*7) WHERE Z_PK=1;

After relaunching the app the purchased date is now shown as "2009-11-22".


Core Data Faulting


In my example above I mentioned relaunching the app after modifying the SQLite store directly. Modifying the store while the app is not running is the safest way to ensure changes will persist. However, it is not always necessary if you understand how and when Core Data reads and writes to the store.


Core Data tracks entity objects in memory but lazy loads the actual attribute values. The act of lazy loading is called faulting. If you modify an attribute value directly in the store and then a fault is fired for the corresponding managed object, the changed values will be loaded into memory. Managed objects will then persist in memory until no longer needed. Core Data assumes it has control of the store and will not attempt to load the managed object attribute values again until the object is invalidated and another fault is fired for it. So if you change a value after a managed object has already been populated for the record, the changes won't immediately appear. You will need to understand your managed object behaviour and lifetimes to work out when to expect the changes to be represented in memory.


Also be cautious not to make changes directly to the store while an app is running and then interact with the app in a way that commits changes to the database. If Core Data needs to update the record that you had modified, it will assume (rightly so under normal conditions) that it has exclusive control and will overwrite your sneaky changes with data from the in-memory managed objects.


Device Testing


Accessing the Core Data SQLite store directly is easiest when developing in the Simulator, as demonstrated above. However, it is still possible to access the store file on the device. The simplest way is to enable iTunes file sharing for the app. Then you can copy the sqlite file out to the desktop to interact with directly. You can also modify it and copy it back to the device.

Enable iTunes file sharing for the app by editing the Info.plist for the app and adding "Application supports iTunes file sharing" (aka UIFileSharingEnabled) with a boolean value of YES. Re-install the app and then connect to iTunes to get access to SQLite store file.


Summary


The use of SQLite as an underlying data store for Core Data is an implementation detail that can be used to our advantage when debugging and testing Core Data driven iOS apps.


Related


Also see:

• Organising Core Data for iOS
• OrganisingCoreData sample app on github