Best way to reference image through out app?

Question

My application is database driven. Each row contains the main content column I display in a UIWebView. Most of the rows (the content column) have a reference to image1 and some to image2. I convert these to base64 and add the image string into the row. However, if either image changes, it means I have to go back through all the rows and update the base64 string.

I can provide a unique string in the row content such as {image1}. It means I'll have to search through the entire content for that row and replace with the base64 version of the image. These images are also always at the bottom of the row content. Not sure how having to go through all content first before replacing will affect performance. Is there a better way to do this?

Answer 1

Why not have the images in a table, with image_ID (a unique integer) and image_data (a blob)? Then in your main table, store just the image_ID, and do a join if you need the actual image?

On an alternative interpretation of your question (if that answer doesn't mnake sense to you) why not break the content into three fields: stuff before the image, the image, and the stuff after. Store the image_ID for the middle part (not the data--get that with an sql JOIN on the image table). Then build the final content with concatenation.

Answer 2

I hope I am understanding your question correctly.

If the images are not very large, then it is probably OK to just use the like keyword as in:

sqlite3_stmt *statement = nil;
if(statement == nil)
{
 const char *sql = [[NSString stringWithFormat:@"SELECT imageContent FROM imageDatabase WHERE imageContent LIKE '%@%@%@'", @"%", imageValue, @"%"] UTF8String];
 if (sqlite3_prepare_v2(db, sql, -1, &statement, NULL) != SQLITE_OK) {
  //NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(db));
  return;
 }
}
while (sqlite3_step(statement) == SQLITE_ROW) {
 // query was successful
 // perform some action on the resulting data
}
sqlite3_finalize(statement);
statement = nil;

If you set imageValue = image1, image2, or whatever, that will give you the item you are looking for from the database without having to do string manipulation in code. I am assuming you know SQL, so sorry if this is redundant information, but the above will search your imageDatabase for anything that contains the image1, image2 imageValue. Once you find the row, you can update it, or you can use the WHERE clause with the UPDATE SQL statement, but I find that to be a bit dangerous due to the possibility of inadvertently updating multiple rows without checking the content first to make sure it is what you want.

Also, if you are doing database updates with this, you will find a major performance boost by wrapping your inserts and updates with transactions like:

const char *sql = "BEGIN TRANSACTION;";
 char *errMsg;
 sqlite3_exec(db, sql, nil, 0, &errMsg);

const char *commit = "COMMIT;";
 sqlite3_exec(db, commit, nil, 0, &errMsg);

It prepares and optimizes your query before executing it. I have seen insert and update queries get twice as fast with transactions.

If the database is very large, this will have a significant performance hit, but doing the string manipulation in memory will have a large memory cost. If you use the SQLite LIKE method, the string search is done in a serial fashion on disk and has less of a memory hit.

Once you have found the specific item you can do the regular expression search and replace on just that particular string keeping your code's memory footprint smaller.