Read large file into sqlite table in objective-C on iPhone

Question

I have a 2 MB file, not too large, that I'd like to put into an sqlite database so that I can search it. There are about 30K entries that are in CSV format, with six fields per line. My understanding is that sqlite on the iPhone can handle a database of this size.

I have taken a few approaches but they have all been slow > 30 s. I've tried:

1) Using C code to read the file and parse the fields into arrays.

2) Using the following Objective-C code to parse the file and put it into directly into the sqlite database:

NSString *file_text = [NSString stringWithContentsOfFile: filePath usedEncoding: NULL error: NULL];

NSArray  *lineArray = [file_text componentsSeparatedByString:@"\n"];

for(int k = 0; k < [lineArray count]; k++){
 NSArray *parts = [[lineArray objectAtIndex:k] componentsSeparatedByString: @","];

 NSString *field0       = [parts objectAtIndex:0];
 NSString *field2       = [parts objectAtIndex:2];
 NSString *field3       = [parts objectAtIndex:3];

NSString *loadSQLi = [[NSString alloc] initWithFormat: @"INSERT INTO TABLE (TABLE, FIELD0, FIELD2, FIELD3) VALUES ('%@', '%@', '%@');",field0, field2, field3];


 if (sqlite3_exec (db_table, [loadSQLi  UTF8String], NULL, NULL, &errorMsg) != SQLITE_OK) {
  sqlite3_close(db_table);
  NSAssert1(0, @"Error loading table: %s", errorMsg);
 }

Am I missing something? Does anyone know of a fast way to get the file into a database?

Or is it possible to translate the file into a sqlite format that can be read directly into sqlite?

Or should I turn the file into a plist and load it into a Dictionary? Unfortunately I need to search on two of the fields, and I think a Dictionary can only have one key?

Jim

Answer 1

I think you are confused about waht sqlite3 is. Sqlite3 is not an in memory database, it is a persistent disk based database, if you are loading all your data into every time you are doing something wrong. SQLite is designed for persistence, after you enter all the data into it you can just close the db, and the next time you open it using sqlite3_open_v2(), no need to reload all the data. By default sqlite3 does all its commits atomically (which is part of why the inserts are slow), so you don't even technically need to close it (though you still should, just to be safe, or in case you want to try to improve performance by using transactions).

In fact, rather than including a the CSV file with the app you could generate the sqlite3 database from the CSV file as part of your build process and include that with the app.

Answer 2

Louis, thanks for your response.

I should have mentioned that I only want to write the data into the database once at the beginning of the app, and then later I only need to do reads of the database, no writes.

Can you tell me how to generate the sqlite3 database directly from the CSV file as part of the build process?

Answer 3

OK, I think I've got it. Starting from the Unix command prompt the following reads in the CSV file and writes out a SQL file:

 % sqlite3
 sqlite3> .mode csv
 sqlite3>  create table NEWTABLE (ITEM_ID INTEGER PRIMARY KEY, FIELD0 TEXT, FIELD1 TEXT, FIELD2 TEXT, FIELD3 TEXT, FIELD4 TEXT, FIELD5 TEXT);
 sqlite3> .import csvfile.csv NEWTABLE
 sqlite3> .output csvfile.sql
 sqlite3> .dump NEWTABLE

The part I still haven't figured out is if there is a quick way to read the sql file into sqlite3. The approach I currently have is to read each line of the sql file and execute on the iPhone. Is there a one line approach to reading the sql file into sqlite3?

Answer 4

Thanks a lot for posting your solutions James. I was going through a similar problem of loading a large database file into sqlite3. Loading directly from a csv file is so much easier.

Answer 5

Ugh... don't deal with the C API yourself. Especially not when there are nicer wrappers available: http://cocoaheads.byu.edu/resources/sqlite