Cacher is the code snippet organizer for pro developers

static NSString *cacheDatabaseName = @"ApplicationCache.db";
static NSString *cacheGroupTable = @"CacheGroups";
static NSString *cacheGroupTableManifestURLColums = @"manifestURL";
static NSString *cacheTable = @"Caches";
static NSString *cacheTableCacheGroupId = @"cacheGroup";

/**
 Clears the cached resources associated to a cache group.
 
 @param manifestURLs An array of `NSString` containing the URLs of the cache manifests for which you want to clear the resources.
 */
- (void)clearCacheForCacheManifestURLs:(NSArray *)manifestURLs {
    
    sqlite3 *newDBconnection;
    
    /*Check that the db is created, if not we return as sqlite3_open would create
     an empty database and webkit will crash on us when accessing this empty database*/
    if (![[NSFileManager defaultManager] fileExistsAtPath:[self cacheDatabasePath]]) {
        NSLog(@"The cache manifest db has not been created by Webkit yet");
        return;
    }
    
    if (sqlite3_open([[self cacheDatabasePath]  UTF8String], &newDBconnection) == SQLITE_OK) {
        
        if (sqlite3_exec(newDBconnection, "BEGIN EXCLUSIVE TRANSACTION", 0, 0, 0) != SQLITE_OK) {
            NSLog(@"SQL Error: %s",sqlite3_errmsg(newDBconnection));
        }
        else {
            /*Get the cache group IDs associated to the cache manifests' URLs*/
            NSArray *cacheGroupIds = [self getCacheGroupIdForURLsIn:manifestURLs usingDBConnection:newDBconnection];
            /*Remove the corresponding entries in the Caches and CacheGroups tables*/
            [self deleteCacheResourcesInCacheGroups:cacheGroupIds usingDBConnection:newDBconnection];
            [self deleteCacheGroups:cacheGroupIds usingDBConnection:newDBconnection];
            if (sqlite3_exec(newDBconnection, "COMMIT TRANSACTION", 0, 0, 0) != SQLITE_OK) NSLog(@"SQL Error: %s",sqlite3_errmsg(newDBconnection));
        }
        
        sqlite3_close(newDBconnection);
        
    } else {
        NSLog(@"Error opening the database located at: %@", [self cacheDatabasePath]);
        newDBconnection = NULL;
    }
    
}

/**
 Get the Cache group IDs associated to cache manifests URLs
 
 @param urls The URLs of the cache manifests.
 @param db The connection to the database.
 */
- (NSArray *)getCacheGroupIdForURLsIn:(NSArray *)urls usingDBConnection:(sqlite3 *)db {
    NSMutableArray *result = [NSMutableArray arrayWithCapacity:0];
    sqlite3_stmt    *statement;
    NSString *queryString = [NSString stringWithFormat:@"SELECT id FROM %@ WHERE %@ IN (%@)", cacheGroupTable,cacheGroupTableManifestURLColums, [self commaSeparatedValuesFromArray:urls]];
    const char *query = [queryString UTF8String];
    
    if (sqlite3_prepare_v2(db, query, -1, &statement, NULL) == SQLITE_OK)
    {
        while (sqlite3_step(statement) == SQLITE_ROW) {
            int id = sqlite3_column_int(statement, 0);
            [result addObject:[NSNumber numberWithInt:id]];
        }
    }
    else {
        NSLog(@"SQL Error: %s",sqlite3_errmsg(db));
    }
    sqlite3_finalize(statement);
    return result;
}

/**
 Delete the rows in the CacheGroups table associated to the cache groups we want to delete.
 
 @param cacheGroupIds An array of `NSNumbers` corresponding to the cache groups you want cleared.
 @param db The connection to the database.
 */
- (void)deleteCacheGroups:(NSArray *)cacheGroupsIds usingDBConnection:(sqlite3 *)db {
    sqlite3_stmt    *statement;
    NSString *queryString = [NSString stringWithFormat:@"DELETE FROM %@ WHERE id IN (%@)", cacheGroupTable,[self commaSeparatedValuesFromArray:cacheGroupsIds]];
    const char *query = [queryString UTF8String];
    if (sqlite3_prepare_v2(db, query, -1, &statement, NULL) == SQLITE_OK)
    {
        sqlite3_step(statement);
    }
    else {
        NSLog(@"SQL Error: %s",sqlite3_errmsg(db));
    }
    sqlite3_finalize(statement);
}

/**
 Delete the rows in the Caches table associated to the cache groups we want to delete.
 Deleting a row in the Caches table triggers a cascade delete in all the linked tables, most importantly
 it deletes the cached data associated to the cache group.
 
 @param cacheGroupIds An array of `NSNumbers` corresponding to the cache groups you want cleared.
 @param db The connection to the database
 */
- (void)deleteCacheResourcesInCacheGroups:(NSArray *)cacheGroupsIds usingDBConnection:(sqlite3 *)db {
    sqlite3_stmt    *statement;
    NSString *queryString = [NSString stringWithFormat:@"DELETE FROM %@ WHERE %@ IN (%@)", cacheTable,cacheTableCacheGroupId, [self commaSeparatedValuesFromArray:cacheGroupsIds]];
    const char *query = [queryString UTF8String];
    if (sqlite3_prepare_v2(db, query, -1, &statement, NULL) == SQLITE_OK)
    {
        sqlite3_step(statement);
    }
    else {
        NSLog(@"SQL Error: %s",sqlite3_errmsg(db));
    }
    sqlite3_finalize(statement);
}

/**
 Retrieves the path of the ApplicationCache sqite db. The db is located in `Library/Caches/your.bundle.id/ApplicationCache.db`
 
 @return The path of the db
 */
- (NSString *)cacheDatabasePath {
    NSArray *pathsList = NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES);
    NSString *pathSuffix = [NSString stringWithFormat:@"%@/%@", [[NSBundle mainBundle] bundleIdentifier], cacheDatabaseName];
    NSString *path = [(NSString *)pathsList[0] stringByAppendingPathComponent:pathSuffix];
    
    return path;
}

/**
 Helper to transform an `NSArray` in a comma separated string we can use in our queries.
 
 @return The comma separated string
 */
- (NSString *)commaSeparatedValuesFromArray:(NSArray *)valuesArray {
    NSMutableString *result = [NSMutableString stringWithCapacity:0];
    [valuesArray enumerateObjectsUsingBlock:^(id obj, NSUInteger idx, BOOL *stop) {
        if ([obj isKindOfClass:[NSNumber class]]) {
            [result appendFormat:@"%d", [(NSNumber *)obj intValue]];
        }
        else {
            [result appendFormat:@"'%@'", obj];
        }
        if (idx != valuesArray.count-1) {
            [result appendString:@", "];
        }
    }];
    return result;
}