Standalone cache in iOS sqlite3 app gets damaged

Question

I have a sqlite3 database for an iOs application to provide offline system support.

It works great. But sometimes the .db file gets corrupted. And does not return results.

If I check the SELECT statement on the command line, I get the following error message:

sqllite Error: database disk image is malformed 

Although undesirable, it becomes corrupt. A database is just an auxiliary system, which would be enough to detect in the iOS application that the file is damaged and restart the file.

But using sqlite3 statements, I did not get any exceptions. The code looks like this:

 sqlRaw = @"SELECT ... "; const char *sql = [sqlRaw cStringUsingEncoding:NSUTF8StringEncoding]; if (sqlite3_prepare_v2(database, sql, -1, &date_statement, NULL) != SQLITE_OK) { NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database)); } NSMutableArray *entities = [[NSMutableArray alloc] initWithCapacity:0]; while (sqlite3_step(date_statement) == SQLITE_ROW) { NSData *entityXml = [[NSData alloc] initWithBytes:sqlite3_column_blob(date_statement, 0) length:sqlite3_column_bytes(date_statement, 0)]; [entities addObject:entityXml]; } sqlite3_finalize(date_statement); 

When the application executes the previous code, it simply returns an empty array, no exception is thrown.

Does anyone know how to check the status of a .db file from sqlite3 statements?

Perhaps a different storage system is better for the user. Any recommendations?

Answer 1

Finally, I decided to check the database status from the application, and if the database is damaged, just replace the new empty database.

 - (BOOL) isDatabaseCorrupted { NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES); NSString *documentsDirectory = [paths objectAtIndex:0]; NSString *path = [documentsDirectory stringByAppendingPathComponent:cache_final_path]; BOOL bResult = FALSE; if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) { @try { NSString *sqlRaw = @"PRAGMA integrity_check;"; const char *sql = [sqlRaw cStringUsingEncoding:NSUTF8StringEncoding]; if (sqlite3_prepare_v2(database, sql, -1, &check_statement, NULL) == SQLITE_OK) { int success = sqlite3_step(check_statement); NSLog(@"SQL integrity_check result is %d", success); NSString *response = nil; switch (success) { case SQLITE_ERROR: bResult = TRUE; break; case SQLITE_DONE: NSLog(@"Result is simple DONE of the sqllite3 on isDatabaseCorrupted"); break; case SQLITE_BUSY: NSLog(@"Result is simple BUSY of the sqllite3 on isDatabaseCorrupted"); break; case SQLITE_MISUSE: NSLog(@"Bad utilization of the sqllite3 on isDatabaseCorrupted"); break; case SQLITE_ROW: response = [NSString stringWithUTF8String:(char *)sqlite3_column_text(check_statement, 0)]; if ([[[response lowercaseString] stringByTrimmingCharactersInSet:[NSCharacterSet whitespaceAndNewlineCharacterSet]] isEqualToString:@"ok"]){ bResult = FALSE; } else { NSLog(@"ATTENTION: integrity_check response %@", response); bResult = TRUE; } break; default: break; } sqlite3_finalize(check_statement); } } @catch (NSException *exception) { [TSLogger log:[NSString stringWithFormat:@"Exception %@", [exception description]] withSeverity:severity_error]; return TRUE; } } sqlite3_close(database); return bResult; } 

The results were determined in the order of the expected response to the sqlite3 documentation.

Thanks,

Ivan