5

My goal is to create a sqlite database and fill it with data (probably 1000+ rows) and use this database to pre load core data, and fill a table view.

So far I have the table view working with Core Data, Managed Object Models. I can add items and remove items - basically I have the Master-Detail template working but with my own model.

The Question: How do I create a sqlite database in the correct format for use with Core Data?

I have successfully done this using provided sql databases and tutorials from books. What I noticed is that the table 'fugitive' (for example) in my tutorial is written 'ZFUGITIVE' in the provided sqlite database. All of its columns have the 'Z' appended to the front ('ZFUGITIVEID', 'ZNAME'...) and three extra columns ('Z_PK', 'Z_ENT', 'Z_OPT') exist that are not included in the managed object model (.xcdatamodeld). Besides the 'Z' all caps names and extra columns I also see (in this .sqlite db that came with a tutorial) that there are two extra tables 'Z_PRIMARYKEY' and 'Z_METADATA'.

Im familiar with the sqlite terminal commands and can create a database but it does not have the correct formatting, extra rows or extra tables with metadata. What I had done as an experiment is take the above Fugitive sqlite database table and added another table to it. At first I called it table 'thing' with columns 'thingID' and 'title'. I created the managed object for it and connected it to my table view. I could not for the life of me get the data to preload in core data / my table view. I received No error messages, it just didn't pre load data (It did let me add and remove new items just like the Master-Detail template).

So I renamed table 'thing' to 'ZTHING' with columns ('Z_PK', 'Z_ENT', 'Z_OPT', 'ZTHINGID' and 'ZTITLE') and now I get the following error message. I know that its telling me I created my table improperly however I cant find documentation on how to create it correctly. Please help.

Here is the big honking Error that basically states the version number recorded in table Z_METADATA (column Z_UUID) is incorrect: [Or so I think]

Unresolved error Error Domain=NSCocoaErrorDomain Code=134100 "The operation couldn’t be completed. (Cocoa error 134100.)" UserInfo=0x5b489c0 {metadata=<CFBasicHash 0x5b4e960 [0x2651380]>{type = immutable dict, count = 7,
entries =>
    2 : <CFString 0x5b4ea40 [0x2651380]>{contents = "NSStoreModelVersionIdentifiers"} = <CFArray 0x5b4eb10 [0x2651380]>{type = immutable, count = 0, values = ()}
    4 : <CFString 0x5b4ea90 [0x2651380]>{contents = "NSPersistenceFrameworkVersion"} = <CFNumber 0x5b4e530 [0x2651380]>{value = +248, type = kCFNumberSInt64Type}
    6 : <CFString 0x5b4eac0 [0x2651380]>{contents = "NSStoreModelVersionHashes"} = <CFBasicHash 0x5b4eba0 [0x2651380]>{type = immutable dict, count = 1,
entries =>
    1 : <CFString 0x5b4eb30 [0x2651380]>{contents = "Fugitive"} = <CFData 0x5b4eb50 [0x2651380]>{length = 32, capacity = 32, bytes = 0xe33370b6e7ca3101f91d25951e8bfe01 ... 9e50237bb313d390}
}

    7 : <CFString 0x1ee464 [0x2651380]>{contents = "NSStoreUUID"} = <CFString 0x5b4e850 [0x2651380]>{contents = "E711F65F-3C5A-4889-872B-6541E4B2863A"}
    8 : <CFString 0x1ee324 [0x2651380]>{contents = "NSStoreType"} = <CFString 0x1ee2e4 [0x2651380]>{contents = "SQLite"}
    9 : <CFString 0x5b4ea10 [0x2651380]>{contents = "NSStoreModelVersionHashesVersion"} = <CFNumber 0x5d0b520 [0x2651380]>{value = +3, type = kCFNumberSInt32Type}
    10 : <CFString 0x5b4eaf0 [0x2651380]>{contents = "_NSAutoVacuumLevel"} = <CFString 0x5b4ebf0 [0x2651380]>{contents = "2"}
}
, reason=The model used to open the store is incompatible with the one used to create the store}, {
    metadata =     {
        NSPersistenceFrameworkVersion = 248;
        NSStoreModelVersionHashes =         {
            Fugitive = <e33370b6 e7ca3101 f91d2595 1e8bfe01 3e7fb4de 6ef2a31d 9e50237b b313d390>;
        };
        NSStoreModelVersionHashesVersion = 3;
        NSStoreModelVersionIdentifiers =         (
        );
        NSStoreType = SQLite;
        NSStoreUUID = "E711F65F-3C5A-4889-872B-6541E4B2863A";
        "_NSAutoVacuumLevel" = 2;
    };
    reason = "The model used to open the store is incompatible with the one used to create the store";
}

Thank you so much for all help you can give me

Improve this question
4
  • Thank you so much for in the future not adding bolded-all-caps segments in your posts. Commented Dec 13, 2011 at 16:50
  • Fugitive... That sounds like the book Head First iPhone Development. Sounds to me like the confusion is due to the fact that with Core Data you don't need to create the DB yourself, it gets created automatically if missing. The actual DB schema including the naming is all down to Core Data. Commented Dec 13, 2011 at 16:59
  • Richard - I didn't realize one sentence was so distracting. I will refrain in the future. Does it flag the moderator? Commented Dec 13, 2011 at 18:23
  • Clafou - Yes thats the book where I got the example from, unfortunately it was using an older version of xcode so I wasn't sure if it was exactly right. Also it provides the .sqlite file for you to preload the data (which is the issue I am dealing with) and does not explain how to create your own .sqlite file to use to preload core data models. Commented Dec 13, 2011 at 18:27

4 Answers 4

Reset to default
15

OK, so I agree with the above comments. Use Core Data to build your SQL database. If you modify the database directly, all bets are off in terms of what apple might do to the format; However, Apple does have to ensure backwards compatibility with Core Data, so their APIs MUST forward migrate anything that is in any valid recent format.

So, to answer your question with real information (that is correct as of Dec, 2011 and iOS 4), here is a technique that will work:

  1. Have core data create the database to begin with. This is your template, and will contain the proper metadata, and properties. You should never use DML on this (no alter tables, etc).

  2. The Z_ENT column is the entity id, used for tracking the primary key of the object in the ZPRIMARYKEY table. If you examine the primary key table, it is tracking the max ID already used on each entity (for generation of Z_ID). It also tracks inheritance, since core data uses a single table for all classes that have an inheritance relationship. E.g. Manager is a subclass of Employee. There will be a single table (zemployee), and the primary key table will have Z_SUPER set to point to the real row that tracks IDs for the table. If you have sublasses, then Z_ENT will indicate the proper type for that row. E.g. if Employee is 1 and Manager is 2 in ZPRIMARYKEY, then there will be rows in ZEMPLOYEE with Z_ENT of 1 and 2.

  3. The Z_OPT column is a count of the times a row has been written to (including insert...so it starts at 1). It is almost certainly used for consistency (e.g. optimistic transaction concurrency handling). Anyway, you should increment it when you make a write to a row.

Of course, you should also keep the MAX field up to date in the primary key table. Make sure you do your insert and PK table update together in transactions so that you don't corrupt things!

And finally, of course, you shouldn't be doing this in the first place :)

I needed to know this because I need a non-OSX sync server to deal with files generated by core data...and I therefore cannot use core data API directly.

Update: Sept 2012

A quick note on inheritance. If you have FK relations from sub-classes in your core data model, Core Data uses a single-table inheritance model (look that up in Hibernate docs for a better idea of what is going on). The single table will have Z_ENT set to the proper class type, and foreign keys may need more than one column to handle alternatives present in the classes. Your best bet to ensure you get this right is to generate a small data set in Objective-C with instances of each class, then dump out the data using sqlite3 from the command line. It will be obvious which data type uses which columns.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

"Apple does have to ensure backwards compatibility" hahahaha, good joke.
6

You cannot find documentation for how to create it correctly because from Apple's perspective you should not be creating it yourself in the first place. If you want a core data model, you should use core data to create the tables rather than creating it yourself.

If you want to pre-populate the table with data, you can do so by writing code to do so. That code can either run within the startup code for your app, or you can write a separate standalone app to populate a database to a file system, and then import that database file into your "real" project.

However you approach it, you should assume that using core data with an SQlite persistence store means that you should be letting core data take care of the table creation. If you want to write a SQlite-based app you are free to do so, but then Core Data is not the solution for you.

Improve this answer

3 Comments

Ok this makes much more sense now. Correct me if Im wrong but I should be able to make my models in xcode (.xcdatamodeld), use the persistentStoreCoordinator to create a new .sqlite file and then in another function add rows to the new .sqlite file. I know I read that you can import data using straight sqlite from a .sql or .csv file is this possible with core data because I dont really want to hard code thousands of entries into my program. Finally as the last step after adding data I would need to save the .sqlite file for later use in an iPhone app. Does that sound correct?
If you already have data in a sqlite database, then your best way to get them into a core data model is probably to write Objective-C code that queries your sqlite database and then inserts the appropriate entities for that data into your core data model. I have done something similar, except my data source is a web service rather than a sqlite DB. For me I have placed this in a standalone app that I run and store the DB to my file system. I then import the core data DB file into my iPhone app's project.
@RachelD: have you had any luck with accessing the data you already have? If so, do you have a link or anything you could provide concerning what you did? I was under the impression that Core Data was the intended (and only) way to query a sqlite database (pre-existing or not), but Tim Dean's comments seem to be suggesting otherwise.
4

Here's how I do it

  1. Create your database in any DBMS you'd like (I use MySQL)
  2. Create your entity in Core Data, make sure you run your application at least once so the tables are generated
  3. Export your data using CSV
  4. Import them into Excel (yes, Excel - I've never thought Excel would be so useful in such a task)
  5. Alter column names in Excel to match Core Data versions (you can inspect the database file to learn the column names, using a SQLite DBMS. I use a Firefox plugin named SQLite Manager. The database file resides in Library/Application Support/iPhone Simulator/version/Application ID/Documents/file_name.sqlite) But roughly, you have Z_PK, Z_ENT and Z_OPT in each table as the first columns. Then you have your own columns, upper case and Z appended. So col1 would be ZCOL1
  6. Add Z_ENT and Z_OPT columns. Set Z_OPT to 1, set Z_ENT to the value specified in Z_PRIMARYKEY table. (Z_PK is automatically added during import so you don't need to do it manually)
  7. Export CSV from Excel
  8. Import this CSV file into the database file (as specified in step 5) using your SQLite DBMS.

Hope it helps

Improve this answer

Comments

2

I would either write a desktop command line app that uses core data (and the same model as your phone app) and populates the core data persistant store, then add the files to your iOS app...

...or make another target in your iOS app to do it (you can run that on the device if you find any byte order issues, but you won't get a command line, just insert from code).

Improve this answer

1 Comment

Thank you for the help Stripes Im going to try this out.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.