Updating sqlite database without XML

Question

My app requires data from a sqlite database. It will ship with a version of this database, but I need to update it on a regular basis (most likely once a month). Typically I've been sending updates for other parts of my app as XML through a bunch of webservices I've set up, but this particular database I'm working on now is pretty large (about 20-30 MB), and I'm getting timeout errors when I try to send it that way.

I tried putting the database on my companies server, then downloading it into an NSData object. I then saved that data object to my app's Documents directory. When I restart my app, I get an error saying "File at path does not appear to be a SQLite database". Code for this is below.

// Download data
NSURL *url = [NSURL URLWithString:@"http://www.mysite.net/download/myDatabase.sqlite"];
NSData *fileData = [[NSData alloc] initWithContentsOfURL:url];
NSLog(@"%@",fileData); // Writes a bunch of 8 character (hex?) strings

// Delete old database
NSError *error;
NSURL *destination = [app applicationDocumentsDirectory];
destination = [destination URLByAppendingPathComponent:@"myDatabase"];
destination = [destination URLByAppendingPathExtension:@"sqlite"];
NSLog(@"destination = %@",destination);
[[NSFileManager defaultManager] removeItemAtPath:[destination path] error:&error];

// Save into correct location
[fileData writeToURL:destination atomically:YES];
//[NSFileManager defaultManager] createFileAtPath:[destination path] contents:fileData attributes:nil]; // I also tried this, it doesn't work either.

Is there a standard procedure for updating a large database that an app will use? I get the feeling that what I'm trying to do is incorrect and that there is a totally different way to do this, but I can't figure out what.

---

UPDATE: I've tried a couple things to try to find the problem, but nothing is getting me any closer. I put the database I'm trying to download from my server onto a USB drive and then onto the mac I'm developing on, and into the Documents folder for my app in the Simulator. When I run my app by transferring the database this way, it runs without any problem and I can see all my data.

Conversely, I completely removed my app from the simulator and re-ran it, so it would create my database from scratch. I transferred this newly-made database from my mac onto my server with the USB drive. Once the file was on my server, I tried to run my "download update" in the code block above, but it still crashes the next time I start my app with the "File at path does not appear to be a SQLite database" error message.

From these tests, I'm confident that the problem is not with the database I'm trying to download. Something in my "download update" code is corrupting the database, but I still haven't been able to figure out why, nor have I found an acceptable alternate method to get my updates out to my users once my app launches.

---

UPDATE 2: I've been doing some more searching, and I've learned that a user will need to put in a username and password to access any file on my server. I now believe that the NSData I'm getting back from my code above is actually an authentication challenge, or something related to that, and that's why when I save it with the NSFileManager it's not being recognized as a sqlite DB.

The simplest solution I've found to get through the authentication is here. When I try to do NSData *fileData = [NSData dataWithContentsOfURL:url options:NSDataReadingMapped error:&error]; (after changing my url as suggested in the link, of course), I get an error NSCocoaErrorDomain Code=256, which is just unknown error. My fileData is nil after doing this. I've noticed this post is quite old, so I don't know if it is still supported.

I also found a different solution to the authentication problem here using an NSURLConnection instead of dataWithContentsOfURL. This is much more recent, but there is some more advanced looking syntax used in that example that I haven't really seen before. I was able to pretty much copy-paste from the example, and my project will build without errors, but I don't know the proper syntax for using the fetchURL:withCompletion:failure: routine from my UIViewController. I tried

NSError *error;
NSData *fileData;
ExampleDelegate *download = [[ExampleDelegate alloc] init];
[download fetchURL:url withCompletion:fileData failure:&error];

but that gives a build error for sending an incompatible pointer. I think I can't pass a straight NSData and NSError as ExampleDelegateSuccess and ExampleDelegateFailure objects respectively, even though that what they are typedefed from. I probably have to do something to fileData and error to convert them, but that typedefing is one of the "advanced looking syntax" I was referring to above, and I don't really know what to do.

It's not included in his example, but I found another NSURLConnectionDelegate method called connection:didReceiveAuthenticationChallenge: here that I think I can just add into the code from the example, and that should solve my authentication issue. I feel if I just knew how to call fetchURL I'd be set. Does anyone have any advice on what I need to do to get fileData and error into that call?

Answer 1

I think your problem stems from requiring authentication with your server.

I recommend the popular AFNetworking library for help with making HTTP calls. You'd subclass AFHTTPClient in order to provide your authentication details, as in this answer.

Then in your case, you can use AFXMLRequestOperation to download your XML directly. You may then write it to disk. Note you will need to read over the iOS Data Storage Guidelines to see if the Documents folder is appropriate for your use (which I don't think it is; Caches would probably be better).

Now, the reason your ExampleDelegate code (which looks like it came from here) doesn't work is that ExampleDelegateSuccess and ExampleDelegateFailure are block types, so you can't pass your NSData and NSError * pointers. You need to provide block objects that conform to the signature of the respective types.

For example:

ExampleDelegate *download = [[ExampleDelegate alloc] init];
[download fetchURL:url 
          withCompletion:^(NSData *thedata) {
    NSLog(@"Success! Data length: %d", theData.length);

    // Delete old database
    NSError *error;

    // You need to declare 'app' here so you can use it in the line below.
    NSURL *destination = [app applicationDocumentsDirectory];
    destination = [destination URLByAppendingPathComponent:@"myDatabase"];
    destination = [destination URLByAppendingPathExtension:@"sqlite"];
    NSLog(@"destination = %@",destination);
    [[NSFileManager defaultManager] removeItemAtPath:[destination path] error:&error];

    // Save into correct location
    BOOL success = [fileData writeToURL:destination atomically:YES];
    NSLog(@"File written successfully? %d", success);
} 
          failure:^(NSError *theError){ 
    NSLog(@"Failure: %@", [theError localizedDescription]}
];

The blocks will provide you with the NSData and NSError instances as required, so you don't need to declare your own. You can learn more about blocks here.

Hope that helps :D

Answer 2

We have large databases that we regularly update. I'm not sure how "large" is large, but we are updating megabytes of data. We Update ours as JSON data. JSON is a little less overhead than XML, and there are lots of readily available libraries (like JSONKit). Some of our users have poor data connections, and sometimes difficulties ensue.

We have been testing different ways of updating. One method breaks the data into multiple JSON files. Each of the files are downloaded separately, and they are then stored separately. One advantage over using multiple files, is that if one fails, you only need to resend that particular file.

Also, we use multiple threads to process up to 5 requests/downloads at a time. This requires much more management, but helps us give a better experience to the user. AFHTTPClient is really good at dealing with this stuff. I'd hate to not use it.