How do I create a sqlite database for use with Core Data?

Question

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

Answer 1

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.