Jump to content

- - - - -

Converting a sqlite database so it can be used by Core Data.

  • Please log in to reply
2 replies to this topic

#1 David DelMonte

David DelMonte

    Forum Master

  • STV 4.0 Pro
  • PipPipPipPipPipPipPip
  • 527 posts
Reputation: 41

Posted 31 July 2011 - 05:11 AM

As I understand it, the construct of a sqlite database used natively in Cocoa is different from that used by core data.

At Tarek's suggestion, I wanted to use Core Data rather than sqlite for my project. So I had to convert the data base. I found this tutorial. Using the tutorial as a guide, I used the following python script to convert the one to the other:

import sqlite3;
inConn = sqlite3.connect('dictold.sqlite')
outConn = sqlite3.connect('dictnew.sqlite')
inCursor = inConn.cursor()
outCursor = outConn.cursor()

maxId = 0
inCursor.execute("select * from lexicon")
for row in inCursor:

	if row[0] > maxId:
		maxId = row[0]
	# Create ZLEXICONENTITY entry
	vals = []
	vals.append(row[3]) # Z_PK
	vals.append(1) # Z_OPT
	vals.append(2) # Z_ENT  
	vals.append(row[0]) # ZIND
	vals.append(row[1]) # ZLANG1
	vals.append(row[2]) # ZLANG2
	outConn.execute("insert into ZLEXICONENTITY values(?, ?, ?, ?, ?, ?)", vals)
outConn.execute("update Z_PRIMARYKEY set Z_MAX=?", [maxId])

It worked!

I thought I would post this here as maybe others have this need.

To execute this script:

1. Make backups of your old database, as well as the app..
2. Create your CoreData Model.
3. Run your app without a db. xCode will give an error, but it will make an empty database that you can use for core data.
4. Save that empty database and use a copy for the next steps.
5. Go to
6. "python" (or whatever you named your script).
7. review the new database however you want (it took me a few attempts to get the columns and rows correct).
8. Add the new db into your app.

Hope this helps


#2 David DelMonte

David DelMonte

    Forum Master

  • STV 4.0 Pro
  • PipPipPipPipPipPipPip
  • 527 posts
Reputation: 41

Posted 04 August 2011 - 04:26 AM

I had to make an additional step to get the app to work consistently.

Once I had the database built, I had a problem connecting it to the Core Data model. I got a message that the model did not match the database.

The solution is to let Core Data create the empty data base from the model, and then have Core Data import the data itself.

I first exported my sqlite database to sql calling the file db.sql (imaginative!). I only exported the data table and the primary key table, not the metadata table. I used an app called SQLiteManager for this. You can also do it at the command line.

All the code is stock stuff except for handling the Persistent Store Controller..

That code is as follows:

- (NSPersistentStoreCoordinator *)persistentStoreCoordinator {
	if (persistentStoreCoordinator_ != nil) {
		return persistentStoreCoordinator_;
	NSString *storePath = [[self applicationDocumentsDirectory] stringByAppendingPathComponent:@"db.sql"];
	// set up the backing 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:@"dict" ofType:@"sqlite"];
		if (defaultStorePath) {
			[fileManager copyItemAtPath:defaultStorePath toPath:storePath error:NULL];
	NSURL *storeURL = [NSURL fileURLWithPath:storePath];
	NSError *error = nil;
	persistentStoreCoordinator_ = [[NSPersistentStoreCoordinator alloc] initWithManagedObjectModel:[self managedObjectModel]];
	if (![persistentStoreCoordinator_ addPersistentStoreWithType:NSSQLiteStoreType configuration:nil URL:storeURL options:nil error:&error]) {
		NSLog(@"Unresolved error %@, %@", error, [error userInfo]);
	return persistentStoreCoordinator_;

I hope this helps.. It seems to work for me..


#3 Tarek


    Forum Admin

  • Administrators
  • 3670 posts
Reputation: 452

Posted 04 August 2011 - 01:04 PM

This is definitely very helpful! Thank you very much David for all your valuable feedback.

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users