public List <LibraryItem> GetLibraryItems(string extension, List <string> fingerprints = null, int MaxRecordCount = 0) { List <LibraryItem> results = new List <LibraryItem>(); List <Exception> database_corruption = new List <Exception>(); try { lock (DBAccessLock.db_access_lock) { using (var connection = GetConnection()) { connection.Open(); string command_string = "SELECT fingerprint, extension, md5, data FROM LibraryItem WHERE 1=1 "; command_string += turnArgumentSetIntoQueryPart("fingerprint", fingerprints); command_string += turnArgumentIntoQueryPart("extension", extension); if (MaxRecordCount > 0) { // http://www.sqlitetutorial.net/sqlite-limit/ command_string += " LIMIT @maxnum"; } using (var command = new SQLiteCommand(command_string, connection)) { //turnArgumentIntoQueryParameter(command, "fingerprint", fingerprint); turnArgumentIntoQueryParameter(command, "extension", extension); if (MaxRecordCount > 0) { command.Parameters.AddWithValue("@maxnum", MaxRecordCount); } using (SQLiteDataReader reader = command.ExecuteReader()) { while (reader.Read()) { LibraryItem result = new LibraryItem(); int field_count = 0; // Read the record in 2-3 gangs, as there's some DBs out there which have the BLOB as NOT A BLOB but as a STRING type instead: // this is probably caused by manual editing (using SQLite CLI or other means) of the BLOB record. // gang 1: load the field count and header fields: these almost never fail. try { field_count = reader.FieldCount; result.fingerprint = reader.GetString(0); result.extension = reader.GetString(1); result.md5 = reader.GetString(2); } catch (Exception ex) { string msg = String.Format("LibraryDB::GetLibraryItems: Database record #{4} gang 1 decode failure for DB '{0}': fingerprint={1}, ext={2}, md5={3}, field_count={5}.", library_path, String.IsNullOrEmpty(result.fingerprint) ? "???" : result.fingerprint, String.IsNullOrEmpty(result.extension) ? "???" : result.extension, String.IsNullOrEmpty(result.md5) ? "???" : result.md5, reader.StepCount, // ~= results.Count + database_corruption.Count field_count ); Logging.Error(ex, "{0}", msg); Exception ex2 = new Exception(msg, ex); database_corruption.Add(ex2); // it's no use to try to decode the rest of the DB record: it is lost to us continue; } if (true) { Exception ex2 = null; long total_bytes = 0; // gang 2: get the BLOB try { total_bytes = reader.GetBytes(3, 0, null, 0, 0); result.data = new byte[total_bytes]; long total_bytes2 = reader.GetBytes(3, 0, result.data, 0, (int)total_bytes); if (total_bytes != total_bytes2) { throw new Exception("Error reading blob - blob size different on each occasion."); } results.Add(result); continue; } catch (Exception ex) { string msg = String.Format("LibraryDB::GetLibraryItems: Database record #{4} BLOB decode failure for DB '{0}': fingerprint={1}, ext={2}, md5={3}, BLOB length={5}.", library_path, String.IsNullOrEmpty(result.fingerprint) ? "???" : result.fingerprint, String.IsNullOrEmpty(result.extension) ? "???" : result.extension, String.IsNullOrEmpty(result.md5) ? "???" : result.md5, reader.StepCount, // ~= results.Count + database_corruption.Count total_bytes ); ex2 = new Exception(msg, ex); // gang 3: get at the BLOB-née-STRING in an indirect way object[] fields = new object[5]; try { reader.GetValues(fields); byte[] arr = fields[3] as byte[]; if (arr != null) { string blob = Encoding.UTF8.GetString(arr, 0, arr.Length); result.data = new byte[arr.Length]; Array.Copy(arr, result.data, arr.Length); results.Add(result); Logging.Warn("LibraryDB::GetLibraryItems: Database record #{0} BLOB field is instead decoded as UTF8 STRING, following this RESOLVED ERROR: {1}\n Decoded STRING content:\n{2}", reader.StepCount, // ~= results.Count + database_corruption.Count ex2.ToStringAllExceptionDetails(), blob); continue; } else { throw new Exception("Cannot extract BLOB field."); } } catch (Exception ex3) { Logging.Error(ex2); Logging.Error(ex3); database_corruption.Add(ex2); } } } } reader.Close(); } } connection.Close(); } // // see SO link above at the `DBAccessLock.db_access_lock` declaration. // // We keep this *inside* the critical section so that we know we'll be the only active SQLite // action which just transpired. // *This* is also the reason why I went with a *global* lock (singleton) for *all* databases, // even while *theoretically* this is *wrong* or rather: *unnecessary* as the databases // i.e. Qiqqa Libraries shouldn't bite one another. I, however, need to ensure that the // added `System.Data.SQLite.SQLiteConnection.ClearAllPools();` statements don't foul up // matters in library B while lib A I/O is getting cleaned up. // // In short: Yuck. + Cave canem. // SQLiteConnection.ClearAllPools(); } } catch (Exception ex) { Logging.Error(ex, "LibraryDB::GetLibraryItems: Database I/O failure for DB '{0}'.", library_path); LibraryDB.FurtherDiagnoseDBProblem(ex, database_corruption, library_path); throw; } if (database_corruption.Count > 0) { // report database corruption: the user may want to recover from this ASAP! if (MessageBoxes.AskErrorQuestion(true, "Library '{0}' has some data corruption. Do you want to abort the application to attempt recovery using external tools, e.g. a data restore from backup?\n\nWhen you answer NO, we will continue with what we could recover so far instead.\n\n\nConsult the Qiqqa logfiles to see the individual corruptions reported.", library_path)) { Logging.Warn("User chose to abort the application on database corruption report"); Environment.Exit(3); } } return(results); }
public void PutBlob(string fingerprint, string extension, byte[] data) { // Guard if (String.IsNullOrWhiteSpace(fingerprint)) { throw new Exception("Can't store in LibraryDB with null fingerprint."); } if (String.IsNullOrWhiteSpace(extension)) { throw new Exception("Can't store in LibraryDB with null extension."); } // Calculate the MD5 of this blobbiiiieeeeee string md5 = StreamMD5.FromBytes(data); try { lock (DBAccessLock.db_access_lock) { using (var connection = GetConnection()) { connection.Open(); using (var transaction = connection.BeginTransaction()) { bool managed_update = false; using (var command = new SQLiteCommand("UPDATE LibraryItem SET MD5=@md5, DATA=@data WHERE fingerprint=@fingerprint AND extension=@extension", connection, transaction)) { command.Parameters.AddWithValue("@md5", md5); command.Parameters.AddWithValue("@data", data); command.Parameters.AddWithValue("@fingerprint", fingerprint); command.Parameters.AddWithValue("@extension", extension); int num_rows_updated = command.ExecuteNonQuery(); if (1 == num_rows_updated) { managed_update = true; } } if (!managed_update) { using (var command = new SQLiteCommand("INSERT INTO LibraryItem(fingerprint, extension, md5, data) VALUES(@fingerprint, @extension, @md5, @data)", connection, transaction)) { command.Parameters.AddWithValue("@fingerprint", fingerprint); command.Parameters.AddWithValue("@extension", extension); command.Parameters.AddWithValue("@md5", md5); command.Parameters.AddWithValue("@data", data); command.ExecuteNonQuery(); } } transaction.Commit(); } connection.Close(); } // // see SO link above at the `DBAccessLock.db_access_lock` declaration. // // We keep this *inside* the critical section so that we know we'll be the only active SQLite // action which just transpired. // *This* is also the reason why I went with a *global* lock (singleton) for *all* databases, // even while *theoretically* this is *wrong* or rather: *unnecessary* as the databases // i.e. Qiqqa Libraries shouldn't bite one another. I, however, need to ensure that the // added `System.Data.SQLite.SQLiteConnection.ClearAllPools();` statements don't foul up // matters in library B while lib A I/O is getting cleaned up. // // In short: Yuck. + Cave canem. // SQLiteConnection.ClearAllPools(); } } catch (Exception ex) { Logging.Error(ex, "LibraryDB::PutBLOB: Database I/O failure for DB '{0}'.", library_path); LibraryDB.FurtherDiagnoseDBProblem(ex, null, library_path); throw; } }