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.
** If creating a new project, check the ‘Use Core Data for storage’ option when selecting a template.
2. Create the Data Model file (xcdatamodel) if not already created.
a. Click File | New File.
b. Choose iPhone OS | Resource | Data Model
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.
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.
3. Create the Attributes (columns).
a. Click Design | Data Model | Add Attribute
b. Rename newAttribute to the desired column name and set the type.
c. Repeat step b for each additional attribute.
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.
Explore posts in the same categories: UncategorizedTags: Core Data, Dev, iPhone, iPhone OS 3.0, Pre-Populated, SQLite
You can comment below, or link to this permanent URL from your own site.
July 14, 2009 at 9:19 pm
this great thanks mate.appreciate your efforts.
July 16, 2009 at 3:55 pm
Do you know what is the column Z_OPT?
and Thank you it was really helpful
July 21, 2009 at 9:44 am
Thanks a lot. This really saved my day.
July 28, 2009 at 4:49 pm
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!
July 28, 2009 at 8:32 pm
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?
February 20, 2010 at 11:52 am
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…
July 31, 2009 at 4:16 am
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
July 31, 2009 at 8:57 am
Chris,
Thanks for the information!
September 29, 2010 at 1:58 am
I too was wondering what Z_OPT meant. I guess any arbitrary number would to when pre-populating the database.
Thanks for confirming this.
August 3, 2009 at 5:23 pm
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
August 3, 2009 at 7:00 pm
Thanks Pierre!
February 17, 2010 at 4:28 am
psivel thank you for the info but i have a question !
How do you handle the relationships? Because in Recipes some are created as integers and some do not exist at all?!!?
someone please help!
August 7, 2009 at 8:58 am
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
February 17, 2010 at 4:30 am
Hello Sharma!
I used your code and an SQLite was created but it was empty…
any other ideas that could help me?
Thank you!
September 21, 2010 at 5:58 pm
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.
August 21, 2009 at 8:30 am
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
August 21, 2009 at 8:52 am
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.
September 2, 2009 at 9:53 pm
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)
October 30, 2009 at 2:44 pm
You need to fetch the data ( a query if you want) so that the sqlite database is generated with all the required tables structure.
October 2, 2009 at 11:12 am
Really helpful – exactly what I was looking for, thank you!
November 26, 2009 at 2:00 am
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)
January 15, 2010 at 12:33 pm
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?
January 16, 2010 at 1:32 pm
@ krye: that’s precisely what i am wondering, too. anyone??
January 29, 2010 at 11:24 pm
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.
February 13, 2010 at 1:12 pm
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 !
February 15, 2010 at 6:41 pm
Great tip! Thank you.
February 25, 2010 at 6:12 pm
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.
March 10, 2010 at 11:01 pm
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?
March 13, 2010 at 6:54 am
Great job! I was crazy to read the article on the iphone dev to import data in the CoreData system.
Thank you very much!
March 21, 2010 at 7:53 am
Hi there,
what about Z_ENT column… anybody knows what is it about?
Thanks
March 23, 2010 at 9:23 am
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
March 25, 2010 at 6:03 pm
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?
April 8, 2010 at 9:19 am
Thanks a lot
Great work
April 23, 2010 at 4:34 am
Really Good.
Thank you.
May 19, 2010 at 1:42 pm
Hi , let me ask you something , I need to create my database.sqlite first ?
May 19, 2010 at 8:53 pm
Ricardo, yes, in this example I did create the pre-populated sqlite db first and added it to my project.
July 26, 2010 at 3:00 pm
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,
July 31, 2010 at 11:04 am
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!
August 10, 2010 at 8:54 am
thanks man. you saved me a bunch of work!!! great tutorial!
November 1, 2010 at 4:06 pm
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?
November 9, 2010 at 5:38 am
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?
November 9, 2010 at 10:25 pm
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.
November 13, 2010 at 12:40 pm
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
April 26, 2011 at 2:05 pm
“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.
June 1, 2011 at 8:42 am
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.
August 29, 2011 at 3:34 pm
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
September 7, 2011 at 10:16 am
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.
February 15, 2012 at 2:45 am
Could you please provide a Sample Code.
Thanks
April 18, 2012 at 9:30 pm
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!
June 4, 2012 at 6:56 am
Many thanks! This solution works for me perfectly 🙂
June 5, 2013 at 9:33 am
Reblogged this on iOS Reblogged.
June 9, 2015 at 5:31 pm
Hi, can you convert the code to swift?
Thanks,
Ujjwal
July 20, 2018 at 3:45 am
[…] À l'aide d'un Pré-rempli de Base de données SQLite avec les Données de Base de l'iPhone OS 3.0 […]
February 16, 2019 at 1:32 am
[…] suivi les instructions de ce blog mais ne trouve pas le fichier sqlite, sur l'emplacement du répertoire spécifié […]
June 16, 2019 at 5:41 am
[…] seguito le istruzioni su questo blog, ma non ha trovato il file sqlite oltre la posizione directory specificata […]