/// <summary> /// Returns the those block hashes from the phone's database which do not match the existing ones. /// This version is threaded, allows for memory-based sqlite tables, and can process phone blocks /// that aren't actually in the database. /// </summary> /// <param name="phoneId">The unique identification for this phone in the database.</param> /// <param name="sql">The handler for the database of hashes.</param> /// <returns>A list of block hashes that did not match other existing ones in the database.</returns> private List<UnfilterdBlockResult> HashGetUnfilteredBlocks7a(int phoneId, SQLiteConnection sql) { // On 32-bit systems it's easy to run out of memory. Limit threads. int maxThreads = (Environment.Is64BitProcess) ? 8 : 2; const int minListLen = 100000; try { (new SQLiteCommand("PRAGMA temp_store=2", sql)).ExecuteNonQuery(); } catch { } // Select the Run Id which is used to identify which hashes are ours. int hashRunId = -1; if (phoneId > (Int32.MaxValue - 100)) { // Special large phone id indicates the hashes weren't stored in the // DB. The unfiltered hashes should have been passed to us. if (_storedBlocks != null) { hashRunId = Int32.MaxValue - 1; // dummy hashRunId } } else { try { const string stmt = "SELECT hashRunId " + "FROM tbl_HashRun " + "WHERE slideAmount=@SA AND " + "blockSizeBytes=@BS AND " + "phoneId=@PID AND " + "memoryId=@MID " + "ORDER BY hashRunId DESC " + "LIMIT 1"; SQLiteCommand cmd = new SQLiteCommand(stmt, sql); cmd.Parameters.AddWithValue("@SA", _slideAmount); cmd.Parameters.AddWithValue("@BS", _blockSize); cmd.Parameters.AddWithValue("@PID", phoneId); cmd.Parameters.AddWithValue("@MID", _memoryId); SQLiteDataReader rdr = null; try { rdr = cmd.ExecuteReader(); if (rdr.Read()) { hashRunId = Convert.ToInt32(rdr["hashRunId"]); } } finally { if (rdr != null) { rdr.Close(); } } } catch { } } if (hashRunId < 0) { return new List<UnfilterdBlockResult>(); } try { // Use ArrayList rather than List so that the threaded version can // efficiently index into the list. ArrayList tempResults = new ArrayList(); int dropped = 0; if (_storedBlocks != null) { // We were passed the blocks stored in the DB. No need to // read them out. Also, we can assume there are no duplicate // blocks. foreach (UnfilterdBlockResult r in _storedBlocks) { tempResults.Add(r); } _storedBlocks.Clear(); _storedBlocks = null; } else { // Tests have shown that it's quickest to get a list of hashes and their // block index first. Trying to perform filtering in the query tends to // be slower. SQLiteDataReader rdr = null; SQLiteCommand cmd = null; DateTime started = DateTime.Now; const string stmt2 = "SELECT hash, blockIndex " + "FROM tbl_Hash " + "WHERE hashRunId=@HRID " + "ORDER BY blockIndex ASC"; cmd = new SQLiteCommand(stmt2, sql); cmd.Parameters.AddWithValue("@HRID", hashRunId); try { HashSet<string> skip = new HashSet<string>(); rdr = cmd.ExecuteReader(); while (rdr.Read()) { UnfilterdBlockResult result = new UnfilterdBlockResult() { hash = Convert.ToString(rdr["hash"]), blockIndexFirst = Convert.ToInt32(rdr["blockIndex"]) }; // Do not keep the block if it's got a hash that we've previously // seen. // TODO: If we don't save duplicate hashes then we don't need to // perform this check. Dec0de behaves this way now, but we can't // yet rely on the database not having duplicates. if (!skip.Contains(result.hash)) { tempResults.Add(result); skip.Add(result.hash); } else { dropped++; } } } catch (Exception e) { System.Console.WriteLine("SQL exception: " + e.Message); return new List<UnfilterdBlockResult>(); } finally { if (rdr != null) { rdr.Close(); } } DateTime finished = DateTime.Now; System.Console.WriteLine("It took " + (finished - started).TotalSeconds + " seconds to read hashes"); System.Console.WriteLine("Skipped " + dropped + " blocks"); } List<UnfilterdBlockResult> results = null; dropped = 0; if (_noFilter) { results = new List<UnfilterdBlockResult>(); foreach (Object r in tempResults) { results.Add((UnfilterdBlockResult) r); } } else { // Eliminate blocks whose hashes match those of other phones. Here we // assume constant block hashes are in tbl_Hashes rather than in the // deprecated tbl_Constants. // Our tests have shown that testing each individual hash is quicker // then doing it in a single query. The good news though is that we // can perform explicit threading. int nThreads = (tempResults.Count + minListLen - 1) / minListLen; if (nThreads > maxThreads) nThreads = maxThreads; if (nThreads <= 1) { SQLiteDataReader rdr = null; SQLiteCommand cmd = null; // No need to create a thread. results = new List<UnfilterdBlockResult>(); foreach (UnfilterdBlockResult result in tempResults) { rdr = null; try { const string stmt = "SELECT hash FROM tbl_Hash WHERE hashRunId<>@HRID AND hash=@HASH LIMIT 1"; cmd = new SQLiteCommand(stmt, sql); cmd.Parameters.AddWithValue("@HRID", hashRunId); cmd.Parameters.AddWithValue("@HASH", result.hash); rdr = cmd.ExecuteReader(); if (!rdr.HasRows) { results.Add(result); } else { dropped++; } } catch (Exception e) { System.Console.WriteLine("SQL exception: " + e.Message); } finally { if (rdr != null) { rdr.Close(); } } } } else { // Perform the query in threads. results = ThreadedBlockHashFilter(nThreads, hashRunId, tempResults, sql); } tempResults.Clear(); } System.Console.WriteLine("Skipped " + dropped + " blocks"); return results; } catch (Exception ex) { System.Console.WriteLine("SQL exception: " + ex.Message); return new List<UnfilterdBlockResult>(); } }
private List<UnfilterdBlockResult> HashGetUnfilteredBlocks8(int phoneId, SQLiteConnection sql) { try { (new SQLiteCommand("PRAGMA temp_store=2", sql)).ExecuteNonQuery(); } catch { } try { // Default is 2000 1K pages (new SQLiteCommand("PRAGMA cache_size=8000", sql)).ExecuteNonQuery(); } catch { } int hashRunId = -1; try { const string stmt = "SELECT hashRunId " + "FROM tbl_HashRun " + "WHERE slideAmount=@SA AND " + "blockSizeBytes=@BS AND " + "phoneId=@PID AND " + "memoryId=@MID " + "ORDER BY hashRunId DESC " + "LIMIT 1"; SQLiteCommand cmd = new SQLiteCommand(stmt, sql); cmd.Parameters.AddWithValue("@SA", _slideAmount); cmd.Parameters.AddWithValue("@BS", _blockSize); cmd.Parameters.AddWithValue("@PID", phoneId); cmd.Parameters.AddWithValue("@MID", _memoryId); SQLiteDataReader rdr = null; try { rdr = cmd.ExecuteReader(); if (rdr.Read()) { hashRunId = Convert.ToInt32(rdr["hashRunId"]); } } finally { if (rdr != null) { rdr.Close(); } } } catch { } if (hashRunId < 0) { return new List<UnfilterdBlockResult>(); } try { DateTime started = DateTime.Now; SQLiteCommand cmd = null; const string stmt2 = "SELECT hash, MIN(blockIndex) AS blockIndexFirst " + "FROM tbl_Hash " + "WHERE hashRunId=@HRID " + "GROUP BY hash " + "ORDER BY blockIndexFirst ASC"; cmd = new SQLiteCommand(stmt2, sql); cmd.Parameters.AddWithValue("@HRID", hashRunId); SQLiteDataReader rdr = null; List<UnfilterdBlockResult> tempResults = new List<UnfilterdBlockResult>(); try { rdr = cmd.ExecuteReader(); while (rdr.Read()) { UnfilterdBlockResult result = new UnfilterdBlockResult() { hash = Convert.ToString(rdr["hash"]), blockIndexFirst = Convert.ToInt32(rdr["blockIndexFirst"]) }; tempResults.Add(result); } } catch (Exception e) { System.Console.WriteLine("SQL exception: " + e.Message); return new List<UnfilterdBlockResult>(); } finally { if (rdr != null) { rdr.Close(); } } DateTime finished = DateTime.Now; System.Console.WriteLine("It took " + (finished - started).TotalSeconds + " seconds to read hashes"); List<UnfilterdBlockResult> results = null; int dropped = 0; if (_noFilter) { results = tempResults; } else { results = new List<UnfilterdBlockResult>(); foreach (UnfilterdBlockResult result in tempResults) { rdr = null; try { const string stmt = "SELECT hash FROM tbl_Hash WHERE hashRunId<>@HRID AND hash=@HASH LIMIT 1"; cmd = new SQLiteCommand(stmt, sql); cmd.Parameters.AddWithValue("@HRID", hashRunId); cmd.Parameters.AddWithValue("@HASH", result.hash); rdr = cmd.ExecuteReader(); if (!rdr.HasRows) { results.Add(result); } else { dropped++; } } catch (Exception e) { System.Console.WriteLine("SQL exception: " + e.Message); } finally { if (rdr != null) { rdr.Close(); } } } tempResults.Clear(); } System.Console.WriteLine("Skipped " + dropped + " blocks"); return results; } catch (Exception ex) { System.Console.WriteLine("SQL exception: " + ex.Message); return new List<UnfilterdBlockResult>(); } }
private List<UnfilterdBlockResult> HashGetUnfilteredBlocks6(int phoneId, SQLiteConnection sql) { try { // Default is 2000 1K pages (new SQLiteCommand("PRAGMA cache_size=8000", sql)).ExecuteNonQuery(); } catch { } int hashRunId = -1; try { const string stmt = "SELECT hashRunId " + "FROM tbl_HashRun " + "WHERE slideAmount=@SA AND " + "blockSizeBytes=@BS AND " + "phoneId=@PID AND " + "memoryId=@MID " + "ORDER BY hashRunId DESC " + "LIMIT 1"; SQLiteCommand cmd = new SQLiteCommand(stmt, sql); cmd.Parameters.AddWithValue("@SA", _slideAmount); cmd.Parameters.AddWithValue("@BS", _blockSize); cmd.Parameters.AddWithValue("@PID", phoneId); cmd.Parameters.AddWithValue("@MID", _memoryId); SQLiteDataReader rdr = null; try { rdr = cmd.ExecuteReader(); if (rdr.Read()) { hashRunId = Convert.ToInt32(rdr["hashRunId"]); } } finally { if (rdr != null) { rdr.Close(); } } } catch { } if (hashRunId < 0) { return new List<UnfilterdBlockResult>(); } try { SQLiteCommand cmd = null; if (_noFilter) { const string stmt2 = "SELECT hash, MIN(blockIndex) AS blockIndexFirst " + "FROM tbl_Hash " + "WHERE hashRunId=@HRID " + "GROUP BY hash " + "ORDER BY blockIndexFirst ASC"; cmd = new SQLiteCommand(stmt2, sql); cmd.Parameters.AddWithValue("@HRID", hashRunId); } else { const string stmt2 = "SELECT hash, MIN(blockIndex) AS blockIndexFirst " + "FROM tbl_Hash " + "WHERE hashRunId=@HRID1 " + "AND hash NOT IN (SELECT hash FROM tbl_Hash WHERE hashRunId<>@HRID2) " + "GROUP BY hash " + "ORDER BY blockIndexFirst ASC"; cmd = new SQLiteCommand(stmt2, sql); cmd.Parameters.AddWithValue("@HRID1", hashRunId); cmd.Parameters.AddWithValue("@HRID2", hashRunId); } SQLiteDataReader rdr = null; List<UnfilterdBlockResult> results = new List<UnfilterdBlockResult>(); try { rdr = cmd.ExecuteReader(); while (rdr.Read()) { UnfilterdBlockResult result = new UnfilterdBlockResult() { hash = Convert.ToString(rdr["hash"]), blockIndexFirst = Convert.ToInt32(rdr["blockIndexFirst"]) }; results.Add(result); } return results; } catch (Exception e) { System.Console.WriteLine("SQL exception: " + e.Message); return new List<UnfilterdBlockResult>(); } finally { if (rdr != null) { rdr.Close(); } } } catch (Exception ex) { System.Console.WriteLine("SQL exception: " + ex.Message); return new List<UnfilterdBlockResult>(); } }