Exemplo n.º 1
0
        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);
        }
Exemplo n.º 2
0
        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;
            }
        }