/// <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>();
}
}
