With todays vibrant world of iOS apps, there are usually many choices within the same category. So it’s easy to switch to another one. But how about the most important part of it: your data? Well, some developers offer a way to export or import, but unless you’re dealing with standard formats, it’s likely you have to enter all your data again. This blog post will look into how you can use your programming skills to import the data from one application to another.

Introduction

You know the drill: you’ve been using an app of your choice for a while now. You were ok with it, but perhaps annoyed with certain details. You might have wrote to developer, but nothing has changed for a long time (which is understandable - with software prices being so low, it can make it very difficult to justify spending time on a feature only a couple of people would need, but that’s another topic I guess). Then you find an alternative that suits you better, and you would be happy to switch, however there’s no way of importing months or even years worth of data.

Or is it? Turns out there is, but involves some hacking around, comparing, finding patterns and programming. And it’s totally 100% unsupported! But that’s also your developer job daily routine isn’t it? And while it won’t help pay your bills, you’ll at least get satisfaction of being able to use an app of your choice using your carefully entered data!

I used the approach described here several times already: for converting data between iOS apps or Mac to iOS, even between different databases (MySQL to SQLite) etc. I’m Objective C programmer, so I used Xcode and Objective C, but you could use any programming language you’re comfortable with - in this blog post I’ll provide only general tips, so you should be able to follow along. It’s not for the faint of heart, but any decent programmer should be able to use the technique to get her job done!

Steps in general

So how would you begin something like this? General steps I use are:

1. Get the data out of the existing app.
2. Prepare few example records in new app and get that data too.
3. Compare the two to determine how you could transfer data.
4. Program a tool that will convert the data.
5. Copy new database to your app and test it.

Let’s check them in more detail.

Get the data to your computer

There are several ways of getting data out of an application: for iOS you could get it from iTunes backup with a free tool called iPhone Backup Extractor. Or you could get it directly from iOS device - I use this approach with PhoneView because it lets me save modified data back to device in addition too. There are also alternatives, both free and payed - you could use iExplorer for example. And if you’re extracting from Mac app, simply copy the files from ~/Library/Application Support/<app name> folder (or with sandboxed app ~/Library/Containers/<app id>).

Which ever method you choose, you should get access to your app data folders. For an iOS app it usually consists of Documents and Library folders - your database should be stored in either Documents or Library/Application Support. There’s no predetermined way to know which file is the database you’re searching for, so you’ll need to be somewhat creative. But you should be able to figure it out, it may have .data, .database, .db extension or similar. And most of the time there’s only a single file there.

You should repeat the process with the application you’d like to import data to. Make sure you enter few records before that, so you could identify the format later on. You might also need to repeat this later on to get all types of data you’ll require. Note that this works best if your new application doesn’t contain lots of data, but this is usually true when you’re thinking of transfering it from your old one.

Compare the databases

After you identify the database file, it’s time to get the data. In most cases, apps use Core Data which ultimately saves to SQLite3 database. One way of looking at the data is using a database manipulation app. I played with Base and it seemed really nice. There are also free alternatives, such as SQLite Database Browser, but didn’t try them. As it’s most likely the application is using Core Data for storage, you might also consider using Core Data Editor. It makes it simpler to deal with relationships among other things, but it requires Core Data model files on top of the database itself - you should find these inside Resources folder of your application bundle. They have extension .mom or .momd. The later is actually a bundle containing one or more .mom files.

One way or the other, you should be able to get an idea of how the databases are structured and how to convert it (a little bit of knowledge of how relational databases work would be beneficial here). If you’re not comfortable in programming or not dealing with large amounts of data, you could even do it manually, by creating necessary records yourself and copy/paste the fields. But if there’s simply too much data or you want more reliable (and interesting/challenging :) way, you should find some way to exhcange the data between the databases. Perhaps you can get away with using an AppleScript or Automator, but in my cases, I had to rely on working with data on the “low level” - through representation that’s both, easily readable and writable by a program. And that’s SQLite dump: this is plain text file that contains both: the schema information and records data. And not only you can export the database to “dump” file, you can also import it back - and this gives us opportunity to modify the file in between and thus inject our data to it.

Above mentioned tools may include commands for exporting/importing data to dump (I know Base has this). But you actually don’t need it, as you OS X installation (or perhaps you need to install Xcode and it’s command line support too) already contains command line tools for that! As the matter of fact, you may only use these tools to read the data in the first place, eliminating the need to buy and learn additional utilities (although they do provide better overview). As far as command line tools go, it’s as simple as:

sqlite3 "name of database file" .dump > example.txt

And there you have your plain text representation. It should look something like this:

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE ZLOGINGROUP ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, Z_OPT INTEGER, ZGROUPTYPE INTEGER, ZGROUPNAME VARCHAR );
INSERT INTO "ZLOGINGROUP" VALUES(1,2,94,0,'Application Items');
INSERT INTO "ZLOGINGROUP" VALUES(2,2,50,1,'Ignored Items');
...

Should be pretty self-explanatory so I won’t go into much details here but will cover some of caveats later on. Note that depending the amount of data, the dump file may be very long!

Create a tool for migration

This is the fun part - after painstaking analysis of source and destination databases, you should have identified the spots that your data will migrate from and to. Keep in mind that not all data may be copied: perhaps the new application only supports a subset, so you may need to merge or otherwise manipulate it during the process.

I’ve done my share of these tools, if none other, appledoc works pretty much the same way, and collected few common design decisions that make the process simpler. I more or less divide reponsibilites of the app into 3 distinct layers: importer, data store and exporter.

• Importer: I have one class (can be several that work together, depending the complexity) that imports the data from the old database. Normally I just extract data from dump file - most often using regular expressions (BTW - you should really check Patterns - it will make your regex life so much easier and faster, and give CoreRunner a look while there too!). All imported data is saved to data store.

• Data store: This is the connecting piece between importer and exporter. It’s a collection of imported data. I usually have a single “data store” class which contains “links” to actual classes that hold information. I tend to model data store classes to export format, find it easier to deal with them later on. I also have all relationships stored through arrays so I don’t have to deal with database keys and similar.

• Exporter: After all data is imported to data store, exporter class takes over and generates new dump file from collected pieces. Note that exporter only reads information from data store, it doesn’t deal with parsing source dump files - if there’s any source data you require, you should either collect it during import phase or create it on the fly!

Basically that’s it - with some luck, you’ll have valid dump file you can convert to SQLite database (Important: the file must not exist!):

sqlite3 "name of database file" < example.txt

If dump file doesn’t contain syntax errors (missing parenthesis or quotes are frequently the cause of this), the command should generate no output and result in a database file. Otherwise your best chance it to compare your generated file with one you got from actual database and determine what could be the reason - if you use Xcode, you can of course run your tool in debugger. You may also get some luck by manually deleting individual lines until you get it working to pinpoint the one that’s causing you problems.

I find it easier to run the tool over a subset of import data initially, until it works. When done, copy the database file to your device (replace original database and needless to say make a backup of it prior - you’ll likely require several attempts before you get it right!), hold your breath and start your app (I usually completely shut it down and relaunch). If you’re lucky, you should have your data over, otherwise back to code.

More words on Core Data SQLite dump files

Generally you should be able to understand the structure of the SQLite database - if not from schema, then by looking at the data - it’s your data after all. Here are some tips:

• Each table uses 3 fields before the actual data:

  • Z_PK INTEGER PRIMARY KEY: this is the primary key. It starts with 1 and should be incremented for each record.

    Note: while the values are incrementing, you may have gaps in between - these come from deleted records! So don’t rely on number of INSERT statements for a particular table - if you are adding records on top of existing ones, you should actually read the last value or use Z_PRIMARYKEY table described below. Or simply use hard coded starting values in code.

  • Z_ENT INTEGER: entity number. This is static for each table, so you should be just fine by repeating the value from example dump file. But you can also check it up in special Z_PRIMARYKEY table Core Data creates and is usually located at the end of the dump file.
  • Z_OPT INTEGER: this is (supposedly) the number of changes to the record. For importing purposes, you should be fine by using 1 here.

• One-to-one relationships are as simple as the rest of the fields: just use proper primary key on both sides (haven’t had many of these, so I’m writing from my memory, but it should be straightforward).

• One-to-many relationships are also simple: Core Data only stores the relationship on the “many” side, so you basically specify the primary key of your “parent” record and you’re done.

• Many-to-many relationships are slighlty more complicated, but logical from relational database perspective: you must use intermediate database that only holds parent/child primary key pairs. First you need to describe both records, remember their IDs and use them to create the intermediate table. You’ll identify these tables by their “3” prefix (i.e Z_3<first table><second table>, for example: Z_3GROUPSITEMS).

• In most of my conversions, the databases used plain values, but I’ve seen cases where developer was using raw data - BLOBs. Even here, you may be lucky and all records would just use the same value, but otherwise you’ll have a bit harder time. Most often you’ll not even understand what the data is used for. You could try and load the values into a NSData and try and interpret that as NSDictionary or NS/UIImage, but if that doesn’t work, your best bet would be to contact developer, explain the situation and ask if they can tell you what is expected for that field (it would be nice gesture if you can send them your solution later on, so other users can benefit also!)

• Important: Core data relies on information from the database file when creating new records, so you need to update it with last used primary key for all records you insert! You’ll find this information in Z_PRIMARYKEY table (it’s the same one I mentioned above when describing Z_ENT field). Here’s few example lines:

    CREATE TABLE Z_PRIMARYKEY (Z_ENT INTEGER PRIMARY KEY, Z_NAME VARCHAR, Z_SUPER INTEGER, Z_MAX INTEGER);
    INSERT INTO "Z_PRIMARYKEY" VALUES(1,'HotkeyConditionData',0,5);
    INSERT INTO "Z_PRIMARYKEY" VALUES(2,'LoginGroup',0,2);
    INSERT INTO "Z_PRIMARYKEY" VALUES(3,'LoginItem',0,46);
  

  In this example, the last primary key for HotkeyConditionData is 5, for LoginGroup 2 and LoginItem 46. So next keys Core Data will assign will be 6, 3 and 47 respectively.

• Core Data also uses Z_METADATA table with a hash value in it. But you don’t have to deal with this, just copy whatever you find in dump file you created from actual data (in destination format of course). Remember: you’re not changing the schema, you’re only manipulating records.

Conclusion

While this post is marked as programming, it doesn’t contain or link to actual source code: the subject is far too generic and each case different, so I though to rather provide you with some generic ideas and tips I successfully used in the past. So instead of a download link, perhaps just few quick notes:

• Your utility is only useful until the database schema changes - that should cover your needs, but don’t expect to make it bullet proof for all times (then again, you’re likely to only use it once then forget about it).
• The technique works best with “real” databases such as SQLite. If you’re dealing with binary data, you’ll most likely have harder time, check this blog post for an idea.
• While not strictly necessary, you’re probably much better off if the two apps offer similar functionality :)
• You can use similar techinque to batch add or modify data to the app of your choice, this would require less work as you only need to deal with a single database format, but may save you many hours of tedious repetition, not to mention possibility of making errors in between.

You may wonder: is it worth the effort? Surely it’ll take you a couple of days until you get a solution. These are all couple of days you could create code that would bring you some money at the end of the month. On the other hand, it may be a nice refresher over your daily routine where you can learn something new. Not to mention the satisfaction of changing to your favorite app while bringing all of your data with it! And you may benefit in other sense too: while many developers offer ways to export the data from their apps, some don’t - I felt reluctant to use such apps in the past - it’s MY data after all! But now I know I can not only get the data out, but can also convert it to any format I’d like, be it for use in another app, or simply for backup purposes.