SQLite - Can't copy table to another database if table includes PRIMARY KEY

Question

Level 1

0 points

SQLite - Can't copy table to another database if table includes PRIMARY KEY

Hello all,

I am working on an update to our iPhone application, and am trying to copy a large table of data from the application bundle's SQLite database to the user's SQLite database (saved in their Documents folder). I can create the table in the user's db without a problem. However, when I try to copy the actual data from the bundle's table to the user's newly created table, I get an SQLITE_CANTOPEN result code. I only get this result code if I declare a primary key when creating the table, so...

Copying the data to this table doesn't result in SQLITE_CANTOPEN:

CREATE TABLE customer(
name TEXT,
phone INTEGER,
zip INTEGER,
age INTEGER,
customer sinceyear INTEGER,
email TEXT);

But copying the data to this table does result in SQLITE_CANTOPEN:

CREATE TABLE customer(
name TEXT,
phone INTEGER,
zip INTEGER,
age INTEGER,
customer sinceyear INTEGER,
email TEXT,
PRIMARY KEY(name, phone, zip));

I copy the data using the following statement (the bundle database is attached to the user database as bundle_db):

INSERT INTO main.table SELECT * FROM bundle_db.table;

I also execute BEGIN TRANSACTION before executing the statement above. If I remove the BEGIN TRANSACTION statement (and the corresponding COMMIT TRANSACTION statement), I don't get the SQLITE_CANTOPEN result code.

Any help would be much appreciated.

Thanks,

Ryan

MacBook, Mac OS X (10.5.4)

Posted on Nov 15, 2008 5:43 PM

Reply

Answer 1

xnav

Level 5

7,089 points

Nov 16, 2008 10:48 AM in response to RyanIPete

Did you try:

create table main.customer as select * from bundle_db.customer;

?

Reply

Answer 2

xnav

Level 5

7,089 points

Nov 16, 2008 11:14 AM in response to xnav

BTW you would have to follow the 'create table' with:

CREATE UNIQUE INDEX prime ON main.customer(name, phone, zip);

This from the sqlite doc:

Specifying a PRIMARY KEY normally just creates a UNIQUE index on the corresponding columns.

Message was edited by: xnav

Reply

Answer 3

RyanIPete Author

Level 1

0 points

Nov 16, 2008 3:02 PM in response to xnav

Thanks for the advice.
I replaced the separate CREATE and INSERT statements with:
CREATE TABLE main.customer AS SELECT * FROM default_db.customer;
as you suggested. No issues.

When I try to create the unique index on this table, however, I get the same SQLITE_CANTOPEN result code. I had to remove the 'main.' prefix because I was getting a syntax error.

Another thing I've noticed is that I don't get this result code when running on the simulator, only on the device.

I've included code below. Each of these methods is in a custom class. I begin the update from the app delegate by creating an instance of the custom class and sending it performUpdate101 w/ the database as an argument. Could this be what's causing my problem?


- (void)performUpdate101:(sqlite3 *)db {
 database = db;
 
 [self attachDefaultDBToUserDB]; 
 [self beginTransaction];
 [self copyDataToCustomerTable];
 [self createUniqueIndexOnCustomerTable];
 [self commitTransaction];
 [self detachDefaultDBFromUserDB];
}
- (void)attachDefaultDBToUserDB {
 // PREPARE
 if (attachdefaultDB_to_userDBstmt == nil) {
 // Get path to default database
 NSString *defaultDBPath = [[NSBundle mainBundle] resourcePath];
 defaultDBPath = [defaultDBPath stringByAppendingPathComponent:kDB_Filename];
 NSString *tempString = [NSString stringWithFormat:@"ATTACH DATABASE '%@' AS db_default", defaultDBPath];
 
 const char *sql = (char *)[tempString UTF8String]; 
 if (sqlite3preparev2(database, sql, -1, &attachdefaultDB_to_userDBstmt, NULL) != SQLITE_OK) {
 NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
 }
 }
 
 // STEP
 NSInteger rc = sqlite3step(attach_defaultDB_to_userDBstmt);
 if(rc != SQLITE_DONE) {
 NSAssert1(0, @"Error: failed to step through statement with message '%@'.", sqlite3_errmsg(database));
 }
 
 // RESET
 sqlite3reset(attach_defaultDB_to_userDBstmt);
}
- (void)beginTransaction {
 
 // PREPARE
 if (begintransactionstmt == nil) {
 const char *sql = "BEGIN TRANSACTION";
 if (sqlite3preparev2(database, sql, -1, &begintransactionstmt, NULL) != SQLITE_OK) {
 NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
 }
 }
 
 // STEP
 NSInteger rc = sqlite3step(begin_transactionstmt);
 if(rc != SQLITE_DONE) {
 NSAssert1(0, @"Error: failed to step through statement with message '%s'.", sqlite3_errmsg(database)); 
 }
 
 // RESET
 sqlite3reset(begin_transactionstmt);
}
- (void)copyDataToCustomerTable {
 
 // PREPARE
 if (copydata_to_customer_tablestmt == nil) {
 const char *sql = "CREATE TABLE main.customer AS SELECT * FROM db_default.customer";
 if (sqlite3preparev2(database, sql, -1, &copydata_to_customer_tablestmt, NULL) != SQLITE_OK) {
 NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
 }
 }
 
 // STEP
 NSInteger rc = sqlite3step(copy_data_to_customer_tablestmt);
 if(rc != SQLITE_DONE) {
 NSAssert1(0, @"Error: failed to step through statement with message '%s'.", sqlite3_errmsg(database)); 
 }
 // RESET
 sqlite3reset(copy_data_to_customer_tablestmt);
 
}
- (void)createUniqueIndexOnCustomerTable {
 // PREPARE
 if (createunique_index_on_customer_tablestmt == nil) {
 
 const char *sql = "CREATE UNIQUE INDEX prime ON customer(name, phone, zip)";
 if (sqlite3preparev2(database, sql, -1, &createunique_index_on_customer_tablestmt, NULL) != SQLITE_OK) {
 NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
 }
 }
 
 // STEP
 // This is where I get SQLITE_CANTOPEN (rc = 14) 
 NSInteger rc = sqlite3step(create_unique_index_on_customer_tablestmt);
 if(rc != SQLITE_DONE) {
 NSAssert1(0, @"Error: failed to step through statement with message '%s'.", sqlite3_errmsg(database));
 }
 
 // RESET
 sqlite3reset(create_unique_index_on_customer_tablestmt);
 
}

Reply

Answer 4

xnav

Level 5

7,089 points

Nov 16, 2008 4:00 PM in response to RyanIPete

Does it work if you take away the beginTransaction/commit wrapper? In fact, I'm not sure a 'transaction' buys you anything here anyway.

Reply

Answer 5

xnav

Level 5

7,089 points

Nov 16, 2008 4:18 PM in response to RyanIPete

Thinking about it more, that's probably the whole issue. Sqlite probably can't handle a Data Definition command against a table while the table is mid transaction.

Reply

Answer 6

xnav

Level 5

7,089 points

Nov 16, 2008 4:51 PM in response to RyanIPete

Well, I'm sorry, it appears I've lead you down a dead end. The 'create index' does not really give you the same thing as doing a 'create table' with a primary key specified, and the 'primary key' and 'as select' are mutually exclusive. However, I still think the issue has to do with the transaction paradigm and you need to consider if it's really needed while creating the db copy, what would you roll back to, no database?

Reply

Answer 7

RyanIPete Author

Level 1

0 points

Nov 16, 2008 6:22 PM in response to xnav

Thanks again for all the input, xnav.
The copy does complete successfully if I remove the transaction.
I was using the transaction in the hope that it would speed up the data copy.
To your point, I don't know whether it does much good when the statement is


INSERT INTO...SELECT * FROM...

Removing the transaction does cost me a few seconds, but I guess I'll survive.

I'm still confused about why SQLite won't let me add data to a table with a PRIMARY KEY defined if I use a transaction. If I move the CREATE statement above the BEGIN TRANSACTION statement, I still get the error. Maybe delaying COMMIT prevents the database from verifying that each row has a unique primary key?

Reply

Answer 8

RyanIPete Author

Level 1

0 points

Nov 16, 2008 6:48 PM in response to RyanIPete

To clarify my post above, I've gone back to separating CREATE and INSERT into two statements. This is because I want the PRIMARY KEY declaration that is lost if I use


CREATE TABLE main.customer AS SELECT * FROM bundle_db.customer;

So, I can move BEGIN TRANSACTION below the CREATE statement, but above the INSERT statement, and I still get the SQLITE_CANTOPEN result code.

Reply

Answer 9

xnav

Level 5

7,089 points

Nov 16, 2008 7:25 PM in response to RyanIPete

I suspect that it does have something to do with verifying uniqueness of the keys. Perhaps this is why the SQLiteBooks example code just does a file copy of the DB (see AppDelegate.m of that project).

Reply

Answer 10

xnav

Level 5

7,089 points

Nov 17, 2008 7:34 AM in response to RyanIPete

Perhaps this is a race condition that only exists if sqlite must process for uniqueness. The time difference you see when using transaction somehow doesn't account for index processing. This idea is reinforced by the fact that it works on the simulator which has access to more processor (speed and multiple cpu's). I've done some more reading and this from the 'create index' doc leads me to the conclusion that it really does behave like primary key after all:

The text of each CREATE INDEX statement is stored in the sqlite_master or sqlite tempmaster table, depending on whether the table being indexed is temporary. Every time the database is opened, all CREATE INDEX statements are read from the sqlite_master table and used to regenerate SQLite's internal representation of the index layout.

Perhaps the 'create table as' followed by the 'create index' without 'transaction' is the most efficient code.
BTW, I reread you OP and understand why you can't just use a file copy.

Reply