Parsing SQL file in Objective-C

Question

Does anyone know of an Objective-C library that will easily allow developers to parse a SQL file? I am building an iPhone application that contains a SQLite database and I am attempting to come with a system to upgrade a user's writable database. After reading a lot of the questions and answers on this site it appears that the best way to perform both DDL and DML operations is to keep a list of SQL files that will perform the necessary database upgrades. So now that I've decided that I am going to read a SQL file in the application bundle and then execute the individual SQL statements I need a way to parse the actual file into executable statements. From what I have read, there does not seem to be an existing library that will read a file line by line let alone parse SQL specific content. Does anyone know of one?

My current plan is to read the entire file into an NSString using:

NSString *fileContents = [NSString stringWithContentsOfFile:filePath 
                                                   encoding:NSUTF8StringEncoding
                                                      error:&error];

From there I am going to parse out the statements by splitting the string on the semicolon using [NSScanner scanUpToString] or [NSString componentsSeparatedByString]. I keep telling myself that there has to be a better way to do this because by using this crude method, I am going to lose the ability to include comments in the scripts. Any suggestions?

Answer 1

There's no need to parse out a DDL to create a new writable database. Instead, ship with a new empty DB already created, back up the users current database, copy the new one in place and then transfer over as needed.

Also, you'd really be a lot better off at this point looking at CoreData which offers automatic DB migration for simple changes (like new fields and so on).

Answer 2

You might be over complicating the problem. Here's what I understood:

You have a sqlite database, and different versions of your schema. You need to update the schema from whatever version it's at to the latest version. Good so far?

You could use a file of SQL statements, in which case you can just read in your file, break the string up into statements (split on ; or \n or something), and then execute each statement yourself. (You are using a wrapper, right?)

Or, you can perform the upgrade in code. When I've done this in the past, I've added an extra table to my schema, and this table is basically a set of name-value pairs. One of the pairs is the current version of the schema (like "SCHEMA_VERSION" => "4", or something). Then, I have a method that basically does this:

- (void) upgradeDatabase {
  int schema_version = [myDatabase retrieveSchemaVersion];
  switch(schema_version) {
    case 4:
      //upgrade to version 5
    case 5:
      //upgrade to version 6
    case 6:
      //upgrade to version 7
    case default:
      break;
  }
}

Of course, you could just use Core Data, which does basic schema migration for you.