If you're new here, you may want to subscribe to my RSS feed or follow me on Twitter. Thanks for visiting!
Failed Banks Preloaded Data with Core Data
This is the second part of a three-part series to help get you up to speed with the basics of Core Data quickly.
In the first part of the series, we created a visual data model for our objects, ran a quick and dirty test to make sure it works, and hooked it up to a table view so we could see a list of our objects.
In this part of the series, we’re going to discuss how to import or preload existing data into Core Data so that we have some good default data when our app starts up.
In the final part of the series, we’re going to discuss how we can optimize our app by using NSFetchedResultsController, to reduce memory overhead and improve response time.
Preloading / Importing Existing Data
So how do we preload data into a Core Data store, anyway? Well, there are two popular solutions to this problem:
- Fill in Core Data on startup from external source. For this the app can start up, notice that the database hasn’t been imported yet, and start reading in data from an external source (such as an SQLite database or XML file) and then start inserting the data into Core Data.
- Provide pre-filled in SQLite database. For this we’ll let Core Data create the database structure for us based on the model, and then we populate the database with a utility app. The utility app could be a Mac or iPhone app that uses Core Data to populate the database via Core Data APIs, or some kind of program that fills in the SQLite database directly. Once the database is populated, just include it with the app and make the app use it as the default database if no database already exists.
We’re going to go with option 2 because it’s simple and more efficient. To populate the database, we’ll just extend our Python script a bit since we have that working already.
Note that by using a Python script to import the data rather than working with a utility app that uses Core Data APIs, it’s more likely to break in the future because we’re kind of going under the hood here… but for this tutorial I thought a) it’s a better learning experience since we just covered SQLite and it shows how things are working more clearly, and b) it is simpler!
So, let’s grab a copy of the sqlite database that was generated by our project. The easiest way to find the file is to set a breakpoint inside the application delegate, inside the persistentStoreCoordinator function, below the storeUrl line. You can examine the storeUrl variable to see a full path to where the sqlite backing file resides. So find this and copy it to the directory your Python script is in.
Once you have the database, use sqlite3 to take a peek at how the database looks:
sqlite3 FailedBanksCD.sqlite
sqlite3> .schema
CREATE TABLE ZFAILEDBANKDETAILS ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER,
Z_OPT INTEGER, ZZIP INTEGER, ZINFO INTEGER, ZUPDATEDDATE TIMESTAMP,
ZCLOSEDATE TIMESTAMP );
CREATE TABLE ZFAILEDBANKINFO ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER,
Z_OPT INTEGER, ZDETAILS INTEGER, ZNAME VARCHAR, ZSTATE VARCHAR,
ZCITY VARCHAR );
CREATE TABLE Z_METADATA (Z_VERSION INTEGER PRIMARY KEY,
Z_UUID VARCHAR(255), Z_PLIST BLOB);
CREATE TABLE Z_PRIMARYKEY (Z_ENT INTEGER PRIMARY KEY, Z_NAME VARCHAR,
Z_SUPER INTEGER, Z_MAX INTEGER);
CREATE INDEX ZFAILEDBANKDETAILS_ZINFO_INDEX ON ZFAILEDBANKDETAILS (ZINFO);
CREATE INDEX ZFAILEDBANKINFO_ZDETAILS_INDEX ON ZFAILEDBANKINFO (ZDETAILS);
sqlite> select * from ZFAILEDBANKINFO;
1|2|1|1|Test Bank|Testland|Testville
2|2|1|2|Test Bank|Testland|Testville
3|2|1|3|Test Bank|Testland|Testville
sqlite> select * from ZFAILEDBANKDETAILS;
1|1|1|12345|1|292794835.855615|292794835.679693
2|1|1|12345|2|292794875.943392|292794875.768675
3|1|1|12345|3|292795809.375025|292795809.215297
sqlite> select * from Z_PRIMARYKEY;
1|FailedBankDetails|0|3
2|FailedBankInfo|0|3
Here’s a quick description of what’s in here. Z_METADATA contains some information about the Model that Core Data needs to function. Z_PRIMARYKEY contains (among other things) information on the max key that is currently used by each entity.
As for ZFAILEDBANKINFO and ZFAILEDBANKDETAILS, those are our main data tables! Z_PK is the unique id for each, Z_ENT is their entity id (same as what’s listed in the Z_PRIMARYKEY table), and finally there are our normal fields.
Now, let’s create a Python script to populate this database by reading in the contents of our old database, and creating the appropriate rows in the new database. Create a Python script like the following:
import sqlite3; from datetime import datetime, date; import time inConn = sqlite3.connect('banklist.sqlite3') outConn = sqlite3.connect('FailedBanksCD.sqlite') inCursor = inConn.cursor() outCursor = outConn.cursor() outConn.execute("DELETE FROM ZFAILEDBANKINFO") outConn.execute("DELETE FROM ZFAILEDBANKDETAILS") maxId = 0 inCursor.execute("select * from failed_banks") for row in inCursor: closeDate = datetime.strptime(row[5], "%Y-%m-%d %H:%M:%S") updatedDate = datetime.strptime(row[6], "%Y-%m-%d %H:%M:%S") closeDateSecs = time.mktime(closeDate.timetuple()) updatedDateSecs = time.mktime(updatedDate.timetuple()) # Convert time references secs to NSDate reference deltaSecs = time.mktime((2001, 1, 1, 0, 0, 0, 0, 0, 0)) closeDateSecs = closeDateSecs - deltaSecs updatedDateSecs = updatedDateSecs - deltaSecs if row[0] > maxId: maxId = row[0] # Create ZFAILEDBANKINFO entry vals = [] vals.append(row[0]) # Z_PK vals.append(2) # Z_ENT vals.append(1) # Z_OPT vals.append(row[0]) # ZDETAILS vals.append(row[1]) # ZNAME vals.append(row[3]) # ZSTATE vals.append(row[2]) # ZCITY outConn.execute("insert into ZFAILEDBANKINFO values(?, ?, ?, ?, ?, ?, ?)", vals) # Create ZFAILEDBANKDETAILS entry vals = [] vals.append(row[0]) # Z_PK vals.append(1) # Z_ENT vals.append(1) # Z_OPT vals.append(row[4]) # ZZIP vals.append(row[0]) # ZINFO vals.append(closeDateSecs) # ZUPDATEDATE vals.append(updatedDateSecs) # ZCLOSEDATE outConn.execute("insert into ZFAILEDBANKDETAILS values(?, ?, ?, ?, ?, ?, ?)", vals) outConn.execute("update Z_PRIMARYKEY set Z_MAX=?", [maxId]) outConn.commit() |
This should be pretty straightforward. The only tricky thing we needed to do here was to convert the dates from the format they were stored in our old database (a string) to the format they are stored in the new databaes (epoch seconds). Update: Jeff R. pointed out that NSDate uses a difference reference date than Python does so we have to convert that – see the comments section for more information.
Give the Python script a whirl and if all works well you should be able to see your DB populated with data:
python coreData.py sqlite3 FailedBanksCD.sqlite sqlite> select * from ZFAILEDBANKINFO limit 3; 1|2|1|1|Desert Hills Bank|AZ|Phoenix 2|2|1|2|Unity National Bank|GA|Cartersville 3|2|1|3|Key West Bank|FL|Key West sqlite> select * from ZFAILEDBANKDETAILS limit 3; 1|1|1|57060|1|1269576000|1269576000 2|1|1|34678|2|1269576000|1269576000 3|1|1|34684|3|1269576000|1269576000
Once you’re satisfied the data is in the database correctly, drag the populated database file into your XCode project in the Resources folder. Then open up FailedBanksCDAppDelegate.m, navigate to the persistentStoreCoordinator function, and replace the storeUrl line with the following:
NSString *storePath = [[self applicationDocumentsDirectory] stringByAppendingPathComponent: @"FailedBanksCD.sqlite"]; NSURL *storeUrl = [NSURL fileURLWithPath:storePath]; // Put down default db if it doesn't already exist NSFileManager *fileManager = [NSFileManager defaultManager]; if (![fileManager fileExistsAtPath:storePath]) { NSString *defaultStorePath = [[NSBundle mainBundle] pathForResource:@"FailedBanksCD" ofType:@"sqlite"]; if (defaultStorePath) { [fileManager copyItemAtPath:defaultStorePath toPath:storePath error:NULL]; } } |
All this does is check to see if the sqlite file already exists in the documents directory, and if it doesn’t (i.e. it’s the first time the app has run) it copies the included database from the bundle into the documents directory. Then Core Data will start using the preloaded data and we’re good to go!
Delete the old SQLITE3 file from the iPhone simulator directory (or click iPhone Simulator\Reset Contents and Settings to clear everything), and re-run your app. If all works, you should now see the prepopulated list of banks!
Where to Go From Here?
At this point, we have a detail view that works pretty much as efficiently as the way it did in our SQLite example. However, with Core Data, with just a couple more steps we can make it even more efficient. So in the next tutorial we cover how to do that by using NSFetchedResultsController!
Category: iPhone
Loading ...
Thank you so much for this tutorial! I’ve spent the last couple of days driving myself crazy trying to get a prepoulated sqlite db into a core data persistent store for an iPhone app I’m working on. This is just what I was looking for. Again, thank you :)
I think you forgot to include these two lines when you replace the storeURL line:
NSString *storePath = [[self applicationDocumentsDirectory] stringByAppendingPathComponent: @”FailedBanksCD.sqlite”];
NSURL *storeUrl = [NSURL fileURLWithPath:storePath];
@Jason: Awesome, nice catch. I’ve updated the tutorial to include those missing lines. Thanks!
I was reading through your article..
http://www.raywenderlich.com/980/core-data-tutorial-how-to-preloadimport-existing-data
I am also using this approach, however, I ran into problems when I have to change the schema for the database. If I make any changes to the schema, it invalidate the existing SQLite database and force me to reload the data from a new SQLite database. Any way to get around this problem?
@Eugene: Core Data does support a concept called data migrations, where you can tell Core Data how to populate the new database format from an old structure. Try reading up on lightweight and standard migrations in Core Data. I haven’t tested it out to see how it relates with preloaded data myself yet though.
However, if you’re loading the data from a script of some sort, it should be easy to just update your script! :]
How would you pre-populate ZFAILEDBANKINFO if state was actually another entity rather than a text field?
@joo_p23: It would be similar to the way the example has the failed bank info table pointing to the failed bank details by ID. In our example, the IDs match up 1-to-1, but if they don’t all you need to do is put the appropriate ID for the entity in the state field.
Hello Ray,
I’m curious how to append entity in coredata, after preload sql data. It means, Say I have preloaded data. Now, I want to add or modify table. Instead of let xcode to autogenerate the .sqlite file (this way I losses manually typed preload data values in table), how could I append other new table list in it which does correspond to xcdatamodel file? I know it is confuse but please help me if you can. Thanks.
@Yoon: If I understand your question correctly, you have preloaded info into your database, and now want to change the database schema. In that case, AFAIK the only thing you can do is to change the schema, then modify your script to re-load the data into the new schema appropriately.
Hey, Ray. Thanks for the GREAT tutorials! I am new to iPhone development and this will really help me a lot! I was hoping you could provide me with a little direction, though. Your tutorial contemplates using a Python script to populate the database. However, my app will be extremely data intensive. Do you know of any good tools that I could use that would, say, allow me to import a spreadsheet filled with data into the SQLite database? I have hundreds or thousands of records that I need to add to my database app.
Also, could you post a tutorial on performing database searches with Core Data?
@Jonathan: You should be able to write a Python script to import from a spreadsheet just as well. But if you don’t want to use Python for some reason, you could use any language that has an interface to SQLite – or you could even write a Mac app that communicates to Core Data directly to import your data.
I’ve added your idea of Core Data & db searches tutorial to the idea list!
Ray, thanks a lot for this tutorial. I found another sample from Apple that takes the same approch of copying sqlite file from the bundle to the document directory. I was wondering if there was any reason not to use the database file in the bundle directly.
If it contains static information that is not updated this would have several benefits like saving storage, the ability to update the database content with an app update and the consistency of the schema with the app. Of course, user data would have to go to a second database in the documents directory. What do you think about that?
@Peter: Good point! I put it in there that way because I was thinking of extending the tutorial at some point to add updating capability, and the most common case is to want to be able to modify the data.
But if the data is indeed read-only, that would save space and provide the other benefits you mentioned!
Haven’t tried it myself tho, so not sure if Core Data does anything unexpected like try writing to the store to add some bookkeeping info or something behind the scenes.
I use the Sqlite Manger add on to FireFox. Then I import CSV files created in Excel. The only problem is sometimes accented characters come in “goofy.”
https://addons.mozilla.org/en-US/firefox/addon/5817/
@Bob: Cool thanks for sharing that link, it looks useful!
Any chance of a tutorial where you create a desktop app to prepopulate an iOS sqlite DB with an image using an image well? Then retrieve that data from an app? All your tutorials rock!
@Mike: Added to the idea list, thanks!
Hi Ray,
As you know, I really appreciated your tutorial. I think you have one error, though. The python code to pre-populate the database with dates needs to use the number of seconds since 1/1/2001 (objective c’s ReferenceDate) rather than seconds since epoch. Note the sqlite query you did at the top:
1|1|1|12345|1|292794835.855615|292794835.679693
compared to the one from your pre-build database:
1|1|1|57060|1|1269576000|1269576000
and you can see the date numbers are about 31 years larger in your pre-built database compared to the one created by objective c’s NSDate attribute.
Let me know if I’m off base on this; I’ve adjusted my code to subtract the following from what mktime returns to adjust from 1/1/1970 to 1/1/2001 on all the dates I pre-populate with python.
deltaSecs = time.mktime((2001, 1, 1, 0, 0, 0, 0, 0, 0))
I have no idea why core data would use the 2001 reference date rather than epoch (which is standard in native sqlite), but it seems they do.
Jeff
How easy is it to use this system for section headers (ie: Rescued banks and failed banks, etc) — this means some sort of relationship being reinforced with your Core Data models? Am I right?
The python script doesn’t work — it keeps complaining that there is no such table as “failed_banks” — even in your schema output above there is no table called “failed_banks” — so where are you getting it from?
@Jeff: Very nice catch, I hadn’t noticed that! You are correct, NSDate uses a different reference date than the standard 1970, see this discussion:
http://stackoverflow.com/questions/2615833/objective-c-setting-nsdate-to-current-utc
I’ve updated the Python snippet to include this. Thanks!
@Amarjit: The failed_banks table comes from banklist.sqlite3, which we created in this tutorial:
http://www.raywenderlich.com/902/sqlite-101-for-iphone-developers-creating-and-scripting
For section headers, you could have a relationship from the Bank to a “Category” for example, and set up the fetched results controller to group by category pretty easily.
Hi Ray,
I have a live app with a DB preloaded with static data (using method 2) as well as user-generated data. In the next version I want to make updates and additions to the static data without compromising what the user has entered.
Do you have any advice?
Ray,
I think I’ve got a good one for you. I’ve got a CD database that will be taking two forms: unencrypted and encrypted. The iPhone app that I am building will have a lite version that stores unencrypted data, and a for-pay version that encrypts the data.
My dilemma is as follows: I need to be able to convert an unencrypted db over to an encrypted one as soon as the user upgrades. I think I have figured out how to do this, but it requires two “pre-loaded” dbs with lookup data–encrypted and unencrypted. When the user first starts with the unencrypted data, I’ll use the pre-loaded db with unencrypted lookup data, and they’ll save their data to that db. When they upgrade, I’ll open the unencrypted, load the data, then save it out to the encrypted pre-loaded db. I think that part will work.
However, I’d like to be able to build the dbs in a separate project, and then copy them over to the iPhone app project for building the bundle. The problem is in mirroring the CD database xcdatamodel. Is there a way to import a data model from one project to another?
Also, is there an easy way to have CD inside of Xcode write to two different underlying stores? I.e., can I programmatically flip from writing to one sqlite store to another mid-stream?
I realize these are pretty deep questions. I’m hoping you either have some answers, or know of some resources that will give me some guidance.
@Andrew: Good question. I guess you’d need to write some code to run the first time you run the updated code, that would do the following:
* Check to see if the user already has a DB down on disk
* If so, loop through all of the entries in the “new DB”, and for each:
* Update the corresponding entry in the user’s DB, or add it if it’s not already there
@NukemHill: Take this advice with a grain of salt as I haven’t tried it myself BUT –
For your first question, I don’t see why you wouldn’t be able to simply copy the .xcodedatamodeId from one project to another.
For your second question, you should be able to create a persistent store coordinator and context for each DB, and then write some code to duplicate each object from the source context to the destination context, and write out the destination DB.
I don’t have any sample code for that to show though. There is a book by Marcus Zarra on Core Data that may help though! I haven’t checked it out myself yet though.
I have just finished Part 2 of this Core Data tutorial and it has helped a great deal. Thank you very much. One thing that is evading me at the moment (and it might be so obvious that I just can’t see it) is where and how is the physical data store used in the tutorial mapped to a SQLite database? Data could be stored in flat files as far as I know but its not so where does is say “..use SQLite..”?
Please ignore my question of Sept 30th 8:04 – I’ve figured it out… but now I am having trouble getting rid of the compiler warnings for the gettters/setters. For some reason xcode does not give me a ‘Managed Data Object’ as a choice when I do File->New File… so I hand crafted a sample .h/.m for one of the data classes but no difference – same old compiler warnings…
Ray,
Thank you very much creating for these tutorials — I’ve gone through the whole series and they are very helpful.
Question: Do you know what Z_OPT is?
@Andrew: If you’re not seeing it as a choice, make sure that you have your xcdatamodel selected before you go to File\New File. If you’re still having warnings, what are they?
@Jeffrey: Thanks! According to this post, Z_OPT is the number of changes made to the data:
http://ablogontech.wordpress.com/2009/07/13/using-a-pre-populated-sqlite-database-with-core-data-on-iphone-os-3-0/
Hi ray,
Can u help me …I wanna wired in a core data to XML conversion… that means i would like to create xml file programmatically according to my core data for the back up and restore purpose. But i dont know the right way for write xml and read xml … please guide me or if possible than send a small sample code which help to me…
thanks in advance.
@Piyush: I have a tutorial on how to read and write XML here:
http://www.raywenderlich.com/725/how-to-read-and-write-xml-documents-with-gdataxml
Ray-
Great tutorial! You saved me a ton of time.
Ray,
I just want to post here an error on my part in case anyone ran into the same difficulty. I noticed that when I ran the project after following your tutorial I got the state showing up in the title of the cell where the name of the bank should be and the bank name and city where the city and state should be.
I looked at the data on the command line sqlite utility and got the same output as yours, “1|2|1|1|Desert Hills Bank|AZ|Phoenix” but looked at it in the firefox plugin and found that Z_CITY has the bank name and Z_NAME has the state abbreviation.
The data in banklist.sqlite3 seems correct, the structure is 0=id, 1=name, 2=city, 3=state, 4=zip, 5=close_date, 6=updated_date. However, FailedBanksCD.sqlite has this structure; 0=Z_PK, 1=Z_ENT, 2=Z_OPT, 3=ZDetails, 4=ZCITY, 5=ZNAME, 6=ZSTATE.
I used your python script verbatim… any idea how these got switched around and how I can set it to rights.
Thanks for all your work on these tutorials. PayPal donation on it’s way (come on folks.. show Ray how much you appreciate his work).
I just noticed your schema shows:
“CREATE TABLE ZFAILEDBANKINFO ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER,
Z_OPT INTEGER, ZDETAILS INTEGER, ZNAME VARCHAR, ZSTATE VARCHAR,
ZCITY VARCHAR );”
Whereas mine shows:
“CREATE TABLE ZFAILEDBANKINFO ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, Z_OPT INTEGER, ZDETAILS INTEGER, ZCITY VARCHAR, ZNAME VARCHAR, ZSTATE VARCHAR );”
I wonder why?