Tuesday, July 12, 2011

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.

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:

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.

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.

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 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:

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.

Chris very good post! One quick question. I'm able in the terminal to view the content of my Core Data tables (entities). However it doesn't seem quite right or to have all the record. On the other, when I do a fetch and output the results to the console in the debugger, all the data is there correctly.

Do you by any chance know what could create that mismatch between the terminal and my console in the debugger? I'm not an expert in Core Data so I'm probably doing a very stupid thing!

Mart Coul: one possibility is that you are looking at an out of date sqlite file. For Simulator builds Xcode will sometimes install the app into a new directory, separate from previous builds of the app (but not deleting them) so your best bet is to log the full path of the sqlite database on app launch to make sure you are examining the correct file.

Hi man great article, i'm having some problem to put my app to work with CoreData. I manually created o .sqlite database and copied it to the app bundle, but when a try to use CoreData to add some info, i have th e following error.

SQLite error code:1, 'no such table: Z_METADATA'. It seams that the database should be created automatically by CoreData ?