Using a Pre-Populated SQLite Database with Core Data on iPhone OS 3.0

I was recently re-writing a yet to be published iPhone app to use Core Data with a pre-populated SQLite database. I was able to get the app to function with a new database using the CoreDataBooks sample code as a reference but could not get the app to read a pre-populated database added to the project.

After many hours of troubleshooting, I discovered differences in the pre-populated database and a Core Data created SQLite database. The pre-populated database consisted of just my simple 1 table structure and the Core Data created SQLite database consisted of “extra ‘Z’ tables’ For example: Z_METADATA, I searched the Internet and Apple docs but could not find a utility or any other way to convert or prep a pre-populated SQLite database for use with Core Data.

Right or wrong. Here’s what I finally did (NOTE: All the ‘coding’ is based on the CoreDataBooks sampe):

In XCode:

1. Ensure the CoreData framework is added to your project.

image

** If creating a new project, check the ‘Use Core Data for storage’ option when selecting a template.

image

2. Create the Data Model file (xcdatamodel) if not already created.

a. Click File | New File.

b. Choose iPhone OS | Resource | Data Model

image

c. Click Next, name the file and click Next again.

d. If the model class is already created, select it from   the  next window. You can create it later if it isn’t created already.

image

2. Add an Entity (table name) and bind it to the class.

a. Click Design | Data Model | Add Entity

b. Rename Entity to the table name you wish to create and change the Class to the desired class name.

image

3. Create the Attributes (columns).

a. Click Design | Data Model | Add Attribute

b. Rename newAttribute to the desired column name and set the type.

image

c. Repeat step b for each additional attribute.

image

4. Set the name of the SQLite database name and location in the AppDelegate.m file (Please refer to the CoreDataBooks sample code for further info).

- (NSPersistentStoreCoordinator *)persistentStoreCoordinator {
if (persistentStoreCoordinator != nil) {
return persistentStoreCoordinator;
}
NSString *storePath = [[self applicationDocumentsDirectory] stringByAppendingPathComponent: @”CoreData.sqlite”];
/*
Set up the store.
For the sake of illustration, provide a pre-populated default store.
*/
NSFileManager *fileManager = [NSFileManager defaultManager];
// If the expected store doesn’t exist, copy the default store.
if (![fileManager fileExistsAtPath:storePath]) {
NSString *defaultStorePath = [[NSBundle mainBundle] pathForResource:@”CoreData” ofType:@”sqlite”];
if (defaultStorePath) {
[fileManager copyItemAtPath:defaultStorePath toPath:storePath error:NULL];
}
}

NSURL *storeUrl = [NSURL fileURLWithPath:storePath];
NSDictionary *options = [NSDictionary dictionaryWithObjectsAndKeys:[NSNumber numberWithBool:YES], NSMigratePersistentStoresAutomaticallyOption, [NSNumber numberWithBool:YES], NSInferMappingModelAutomaticallyOption, nil];
persistentStoreCoordinator = [[NSPersistentStoreCoordinator alloc] initWithManagedObjectModel: [self managedObjectModel]];

NSError *error;
if (![persistentStoreCoordinator addPersistentStoreWithType:NSSQLiteStoreType configuration:nil URL:storeUrl options:options error:&error]) {
// Update to handle the error appropriately.
NSLog(@”Unresolved error %@, %@”, error, [error userInfo]);
exit(-1);  // Fail
}
return persistentStoreCoordinator;
}

5. Build and Run the app.

In Finder:

1. Use Spotlight to search for the SQLite database created by the app in the iPhone Simulator. This is /Users/<Username>/Library/Application Support/iPhone Simulator/User/Application/<Application GUID>/Documents/<database name.sqlite>

2. Copy that database to a working folder.

3. Populate that database.

4. Add the populated database to the project.

I hope this helps! If anyone has a better or preferred way of formatting a SQLite database for use with Core Data, please let me know.

About these ads
Explore posts in the same categories: Uncategorized

Tags: , , , , ,

You can comment below, or link to this permanent URL from your own site.

51 Comments on “Using a Pre-Populated SQLite Database with Core Data on iPhone OS 3.0”

  1. Amiy Says:

    this great thanks mate.appreciate your efforts.

  2. Juan Says:

    Do you know what is the column Z_OPT?

    and Thank you it was really helpful


  3. Thanks a lot. This really saved my day.

  4. Darren Says:

    thanks for you work. I have a few questions:

    1. given that you have a pre-populated SQLite3 db do you consider the effort to use coredata on top of this worthwhile? Did you consider using the SQLite db directly?

    2. I have a similar scenario for an iPhone app but in my case I’m collecting data in MySQL (my ISP forces me to), then trying to convert to SQLIte3 for use in the app. The conversion is proving to be a nightmare (plenty of scripts on the web but they all need tweaking) – any advice here?

    thanks!

    • ablogontech Says:

      Thanks for your comments!

      I am fairly new to iPhone development and not sure I have the answers for you. I’m just trying to share any knowledge I get with others and hope it helps somebody.

      As for your questions:

      1. This is my first of 5 yet to be published apps and I was 95% done with the app and was using a SQLite db directly when iPhone OS 3.0 was released. After reviewing the Apple docs I realized Core Data is ‘preferred’ with OS 3.0 compliant apps. So without knowing if Apple would approve an OS 3.0 app with using SQLite directly I dove into a rewrite. I have read Core Data is more efficient in handling datastores such as SQLite, XML and in memory DBs. However, no real experience on which is better – direct or Core Data.

      2. Sorry, no experience here. Anybody else have experience with this?

      • paul Says:

        Have you thought of providing access to your data from your mysql db on the web to your iphone app using web services?

        I’ve just implemented this in my app Compare Buddy Pro and its pretty straightforward…

  5. Chris Says:

    Hey Everybody,

    after a couple of tests i figured out that Z_OPT is the amount of changes made to the data.

    Hope that helps

    Cheers
    Chris

  6. psivel Says:

    Hi Everybody,

    After having experienced the same problem with a pre-populated sqlite db added to my project (no Z_METADATA table) I tried your method above.

    I followed all the steps, created a brand new project, designed the .xcdatamodel and executed the sample code but it just created an empty file unrecognized by sqlite manager.

    So I tried the following:

    1. duplicate and rename the file Recipes.sqlite from Apple’s CoreDataRecipes sample
    2. opened the file in sqlite manager, dropped all the tables (except Z_METADATA and Z_PRIMARYKEY) and indexes
    3. created my own tables and indexes including in the tables the Z_PK, Z_ENT and Z_OPT columns
    4. updated Z_PRIMARYKEY table to my new tables
    5. and finally emptied the Z_METADATA table

    Then I inserted all my data in the database, added it to my project and designed the .xcdatamodel accordingly.

    It works fine for me.

    regards,

    Pierre Sivel

  7. Amit Sharma Says:

    Hi,

    In order to create tables in SQLIte you will need to execute atleast one fetch request on the DB.

    Put the following code in rootviewcontoller.viewdidload()

    //Provide dummy Fetch request in order to create tables in SQLite DB
    /*
    Fetch existing events.
    Create a fetch request; find the Event entity and assign it to the request; add a sort descriptor; then execute the fetch.
    */

    NSFetchRequest *request = [[NSFetchRequest alloc] init];
    //NSEntityDescription *entity = [NSEntityDescription entityForName:@"Event" inManagedObjectContext:managedObjectContext];
    NSEntityDescription *entity = [NSEntityDescription entityForName:@"Event" inManagedObjectContext:managedObjectContext];
    [request setEntity:entity];

    // Execute the fetch — create a mutable copy of the result.
    NSError *error = nil;
    NSMutableArray *mutableFetchResults = [[managedObjectContext executeFetchRequest:request error:&error] mutableCopy];
    if (mutableFetchResults == nil) {
    // Handle the error.
    }

    [request release];

    Thanks

    • ste Says:

      Hello Sharma!

      I used your code and an SQLite was created but it was empty…
      any other ideas that could help me?
      Thank you!

    • Igosuki Says:

      Wooow man I was trying for hours and then saw your code ! I was kinda thinking that the app didn’t attempt to create the db file because it didn’t try to access it and that confirmed it.
      Thanks man.

  8. Bixente Says:

    Thanks for the article above.

    I’ve done all that. My appliction works just fine in the simulator but my table view remains empty when I load my application onto my Iphone device.

    Does anyone have any idea why?

    Thanks in adavnce

  9. Bixente Says:

    Just found out what it was. The prepopulated database must be added with the option ‘Relative to Build Product’ from pop up window’s drop down list.

    If not an empty database is created.

  10. John Says:

    Hello,

    I do all the steps and when i build the project they generated a empty database (.sqlite) (no Z_**) you know what happens ?

    ps: i create the .xcdatamodel with my only one table (same as my future .sqlite)

    ps2: i use: [self managedObjectContext]; on my applicationDidFinishLaunching to start the Core Data persist

    best regards, and thanks to make a helful tutorial (sorry about my english)

    • Bixente Says:

      You need to fetch the data ( a query if you want) so that the sqlite database is generated with all the required tables structure.

  11. prairiedogg Says:

    Really helpful – exactly what I was looking for, thank you!

  12. bkester Says:

    hello, really appreciate your explanation here. however, i have one problem which has not been addressed here. i followed your steps exactly as they are. and indeed, a xxx.sqlite file is created. when i open it, however, my own table has a “Z” prepended to its name; same for the attribute names. in fact, in sqlite, i can only see, and populate, the entity using these “strange” names, starting with Z. i am surely doing something stupid, but i have been struggling with this for hours now… not a clue. sample codes i see have the same, by the way (coredata recipes, books)

  13. krye Says:

    What do you do if you have a prepopulated database and then add entries to it later? Take the Recipe one for example. Say you ship your app with a fixed set of 10 recipes. The user can’t add or remove them. But then later on you add 5 recipes.

    For the update, how would those 5 new recipes show up in their database? Because when the app is launched it checks to see if the database has been created in their Documents folder, if it’s there, it doesn’t pull anything over.

    How would I push those 5 new entries to their database?

  14. bkester Says:

    @ krye: that’s precisely what i am wondering, too. anyone??

  15. Steve Says:

    Not to be a naysayer (I commend all of you for hacking the Core Data sqlite schema) but I thought it would only be fair to mention that Apple considers the Core Data sqlite schema to be opaque and would rather developers not fiddle with it. I mention this because it’s possible they could change it in the future and break code that relies on the current implementation (FWIW). This is mentioned in the Apple Core Data Overview documentation.

  16. Chris Says:

    Hi i was wondering if you could help, i have followed all the steps but when i launch my app it crashes due to +entityForName could not locate an NSManagedObjectModel for entity name….

    any help would be appretiated, many thanks !

  17. Veejayus Says:

    Great tip! Thank you.

  18. smallpot Says:

    Thanks – having read various articles and posts about data pre-population, I was thinking of doing exactly as you have described. The fact that you have actually done it and documented it gives me more confidence in the idea :)

    Much appreciated, thanks again.

  19. Anna Says:

    I kind of can’t believe that prepopulating or seeding a core data schema hasn’t been built into the framework somehow. While I like the idea of hacking into the sqlite db, I’m working on creating a class method that will loop through and replace the database with arrays, when the user selects “reset”, or the app starts for the first time. I read on another blog about loading from a string file into an array and adding in a loop too. Still amazed this hasn’t been considered. The recipe option of creating a sqlite3 db and swapping it in makes sense, but seems less… programmatic?

  20. redsend Says:

    Great job! I was crazy to read the article on the iphone dev to import data in the CoreData system.

    Thank you very much!

  21. Ignacio Says:

    Hi there,
    what about Z_ENT column… anybody knows what is it about?
    Thanks

    • Matthew Says:

      The Z_ENT column identifies the Entity associated with the row. Each has a separate value in the Z_PRIMARYKEYS table.

      Disclaimer: I’m no expert, so you might want to verify this yourself.

      matthew

  22. SG Says:

    My datastores have values in them like Z_22IPOD, when the entity name itself is just iPod. Where is this 22 coming from? How can I account for it in a migration?

  23. thierry Says:

    Thanks a lot
    Great work

  24. LM Says:

    Really Good.
    Thank you.

  25. Ricardo Mola Says:

    Hi , let me ask you something , I need to create my database.sqlite first ?

    • ablogontech Says:

      Ricardo, yes, in this example I did create the pre-populated sqlite db first and added it to my project.

  26. wannamobile Says:

    I did just that. Submitted the app to the store and submitted the update version to the app store. But, I can’t get the new prepopulated database to replace the prepopulated database on the initial version. Does anyone know how to do this?

    thank you,

  27. Joe Says:

    In my case I want to provide a “sampler” database if none exists (similar to what I think you’re doing). That simple addition to the persistentStoreCoordinator “just works” beautifully on iOS 3 and 4. It even gracefully handles migrations (though I’ll likely update the default database each time).

    Thank you!

  28. mila Says:

    thanks man. you saved me a bunch of work!!! great tutorial!

  29. Pablo Says:

    Hi, I did all the steps of this post and their comments and it worked fine in the simulator but it doesn’t work on device, I think the exception is throwed here:
    15 libsqlite3.dylib 0x0000273c sqlite3_step + 76
    16 CoreData 0x0005c4cc -[NSSQLiteConnection rawIntegerRowsForSQL:] + 1120
    17 CoreData 0x0001523e -[NSSQLCore _ensureDatabaseMatchesModel] + 1470
    18 CoreData 0x00014ba8 -[NSSQLCore load:] + 68

    anyone had this error?

  30. rahulvyas Says:

    Does someone knows how to fill Z_OPT column when we are going to use pre-populated data? does any number works or is there some specific value needs to be filled?

  31. Craig Says:

    I think you guys are taking the wrong approach to this for several reasons:

    1. As someone else pointed out, Apple may change the schema in the future.
    2. It makes it difficult to do updates, especially if the user can modify the data.

    A better approach is to ship your app with the pre-populated data in an XML file and then import that data into the database. This approach is independent of the internal database schema and also allows you to mark data as new for the current release, making it easy to update an existing database.

    • Chris Says:

      I’m new with all of this so please bear with me. I actually found a template that uses XML to populate the data but for some reason I can’t get it to function properly. The database is created but the data never gets transfered into it. Can you possibly let me know what errors are commonly made during this process so I can hopefully fix it and move ahead with my application

  32. mmalc Crawford Says:

    “I searched the Internet and Apple docs but could not find a utility or any other way to convert or prep a pre-populated SQLite database for use with Core Data.”

    This is covered in documentation in the FAQ at http://developer.apple.com/library/mac/#documentation/Cocoa/Conceptual/CoreData/Articles/cdFAQ.html

    How do I use my existing SQLite database with Core Data?

    ** You don’t. ** Although Core Data supports SQLite as one of its persistent store types, the database format is private. You cannot create a SQLite database using native SQLite API and use it directly with Core Data (nor should you manipulate an existing Core Data SQLite store using native SQLite API). If you have an existing SQLite database, you need to import it into a Core Data store (see “Efficiently Importing Data”).


    Lest there is any confusion about this: Core Data is entirely responsible for the stores it manages. You should not attempt to “retrofit” an existing SQLite database to work with Core Data.

  33. Narinder Says:

    Hi
    I have an app using core data with default database. App is live on app store. It has info about 100 items. I have collected info about 50 more. i want to update the app with new items. How can do this using core data. No change is there in the data model or entities.

  34. Vyom Says:

    Good One, But i have done this. I was wandering that u must have done some thing special. Means u have shipped some data which is already in sqlite3 table format

  35. ideveloper Says:

    It’s amazing!!!! I was trying to fix core data error issue for last 10hrs but no luck. And now here it is. Thanks for your effort.

  36. izeeshan Says:

    Could you please provide a Sample Code.
    Thanks

  37. Sven Says:

    Eureka! So after spending about 6 hours at trying to preload my core date table with records in a csv file, I found the trick! As said above, run your application so that the sqlite database is created. Then open up that file in your simulator folder…. If you add in the sqllite add-in for firefox, you can load a csv file into the table of interest. Just ensure that you increment the primary key and then in then in the primary key table, set the zmax to the maximum key.

    Voila!

  38. Marek Says:

    Many thanks! This solution works for me perfectly :)


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Follow

Get every new post delivered to your Inbox.

%d bloggers like this: