Parsing SQL file in Objective-C
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?
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).
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.
精彩评论