Offline cache on iOS application sqlite3 becomes corrupted

Question

I've an sqlite3 database for an iOs application, in order to provide an offline support to the system.

It works perfectly. But sometimes the .db file becomes corrupted. And doesn't return results.

If I check the SELECT instruction throught the command line I get the next Error message:

sqllite Error: database disk image is malformed

Although is not desirable it becomes corrupt. The database is just an auxiliary system, would be enough to being able to detect from iOS application that the file is corrupted and restart the file.

But using the sqlite3 statements I didn't get any exception. The code looks like:

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, simply returns an empty array, no exception is thrown.

Anybody knows how to check from the sqlite3 statements the .db file status?

Maybe is better to user other storage system. Any recommendation?

Answer 1

Finally I decided to check from the application the database status, and if the database has become corrupted, the just replace for a 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 was defined in order of the expected response on the sqlite3 documentation.

Thank you,

Ivan