public void InsertHashBin(HashBinMinHash hashBin)
{
IDbDataParameter dbHashBinParam = new SQLiteParameter("@hashbin", DbType.Int64);
IDbDataParameter dbHashTableParam = new SQLiteParameter("@hashtable", DbType.Int32);
IDbDataParameter dbTrackIdParam = new SQLiteParameter("@trackid", DbType.Int32);
IDbDataParameter dbFingerprintIdParam = new SQLiteParameter("@fingerprintid", DbType.Int32);
IDbCommand dbcmd;
lock (dbcon) {
dbcmd = dbcon.CreateCommand();
}
dbcmd.CommandText = "INSERT INTO hashbins (hashbin, hashtable, trackid, fingerprintid) " +
"VALUES (@hashbin, @hashtable, @trackid, @fingerprintid)";
dbcmd.Parameters.Add(dbHashBinParam);
dbcmd.Parameters.Add(dbHashTableParam);
dbcmd.Parameters.Add(dbTrackIdParam);
dbcmd.Parameters.Add(dbFingerprintIdParam);
dbHashBinParam.Value = hashBin.Bin;
dbHashTableParam.Value = hashBin.HashTable;
dbTrackIdParam.Value = hashBin.TrackId;
dbFingerprintIdParam.Value = hashBin.FingerprintId;
try {
dbcmd.Prepare();
dbcmd.ExecuteNonQuery();
dbcmd.Dispose();
} catch (Exception e) {
throw e;
}
}
/// <summary>
/// Find fingerprints using hash-buckets (e.g. HashBins)
/// </summary>
/// <param name="hashBuckets"></param>
/// <returns>Return dictionary with fingerprintids as keys and the corresponding hashbins as values</returns>
public IDictionary <int, IList <HashBinMinHash> > ReadFingerprintsByHashBucketLshSlow(long[] hashBuckets)
{
IDbDataParameter dbHashBinParam = new SQLiteParameter("@hashbin", DbType.Int64);
IDbCommand dbcmd;
lock (dbcon) {
dbcmd = dbcon.CreateCommand();
}
dbcmd.CommandText = "SELECT id, hashbin, hashtable, trackid, fingerprintid FROM hashbins WHERE hashbin = @hashbin";
dbcmd.Parameters.Add(dbHashBinParam);
dbcmd.CommandType = CommandType.Text;
dbcmd.Prepare();
IDictionary <int, IList <HashBinMinHash> > result = new Dictionary <int, IList <HashBinMinHash> >();
foreach (long hashBin in hashBuckets)
{
dbHashBinParam.Value = hashBin;
IDataReader reader = dbcmd.ExecuteReader();
var resultPerHashBucket = new Dictionary <int, HashBinMinHash>();
while (reader.Read())
{
int hashId = reader.GetInt32(0);
long hashBin2 = reader.GetInt32(1);
int hashTable = reader.GetInt32(2);
int trackId = reader.GetInt32(3);
int fingerprintId = reader.GetInt32(4);
HashBinMinHash hash = new HashBinMinHash(hashId, hashBin2, hashTable, trackId, fingerprintId);
resultPerHashBucket.Add(fingerprintId, hash);
}
reader.Close();
foreach (var pair in resultPerHashBucket)
{
if (result.ContainsKey(pair.Key))
{
result[pair.Key].Add(pair.Value);
}
else
{
result.Add(pair.Key, new List <HashBinMinHash>(new[] { pair.Value }));
}
}
}
return(result);
}
/// <summary>
/// Hash Fingerprints using Min-Hash algorithm
/// </summary>
/// <param name="listOfFingerprintsToHash">List of fingerprints already inserted in the database</param>
/// <param name="track">Track of the corresponding fingerprints</param>
/// <param name="hashTables">Number of hash tables (e.g. 25)</param>
/// <param name="hashKeys">Number of hash keys (e.g. 4)</param>
private bool HashFingerprintsUsingMinHash(IEnumerable <Fingerprint> listOfFingerprintsToHash, Track track, int hashTables, int hashKeys)
{
List <HashBinMinHash> listToInsert = new List <HashBinMinHash>();
foreach (Fingerprint fingerprint in listOfFingerprintsToHash)
{
int[] hashBins = minHash.ComputeMinHashSignature(fingerprint.Signature); //Compute Min Hashes
Dictionary <int, long> hashTable = minHash.GroupMinHashToLSHBuckets(hashBins, hashTables, hashKeys);
foreach (KeyValuePair <int, long> item in hashTable)
{
HashBinMinHash hash = new HashBinMinHash(-1, item.Value, item.Key, track.Id, fingerprint.Id);
listToInsert.Add(hash);
}
}
return(dbService.InsertHashBin(listToInsert));
}
/// <summary>
/// Find fingerprints using hash-buckets (e.g. HashBins)
/// </summary>
/// <param name="hashBuckets"></param>
/// <returns>Return dictionary with fingerprintids as keys and the corresponding hashbins as values</returns>
public IDictionary <int, IList <HashBinMinHash> > ReadFingerprintsByHashBucketLsh(long[] hashBuckets)
{
IDictionary <int, IList <HashBinMinHash> > result = new Dictionary <int, IList <HashBinMinHash> >();
String statementValueTags = String.Join(",", hashBuckets);
IDbCommand dbcmd;
lock (dbcon) {
dbcmd = dbcon.CreateCommand();
}
String query = String.Format("SELECT id, hashbin, hashtable, trackid, fingerprintid FROM hashbins WHERE (hashbin IN ({0}))", statementValueTags);
dbcmd.CommandText = query;
dbcmd.CommandType = CommandType.Text;
dbcmd.Prepare();
IDataReader reader = dbcmd.ExecuteReader();
while (reader.Read())
{
HashBinMinHash hash = new HashBinMinHash();
hash.Id = reader.GetInt32(0);
hash.Bin = reader.GetInt64(1);
hash.HashTable = reader.GetInt32(2);
hash.TrackId = reader.GetInt32(3);
hash.FingerprintId = reader.GetInt32(4);
if (result.ContainsKey(hash.FingerprintId))
{
result[hash.FingerprintId].Add(hash);
}
else
{
result.Add(hash.FingerprintId, new List <HashBinMinHash>(new[] { hash }));
}
}
reader.Close();
return(result);
}
/// <summary>
/// Hash Fingerprints using Min-Hash algorithm
/// </summary>
/// <param name="listOfFingerprintsToHash">List of fingerprints already inserted in the database</param>
/// <param name="track">Track of the corresponding fingerprints</param>
/// <param name="hashTables">Number of hash tables (e.g. 25)</param>
/// <param name="hashKeys">Number of hash keys (e.g. 4)</param>
private bool HashFingerprintsUsingMinHash(IEnumerable<Fingerprint> listOfFingerprintsToHash, Track track, int hashTables, int hashKeys)
{
List<HashBinMinHash> listToInsert = new List<HashBinMinHash>();
foreach (Fingerprint fingerprint in listOfFingerprintsToHash)
{
int[] hashBins = minHash.ComputeMinHashSignature(fingerprint.Signature); //Compute Min Hashes
Dictionary<int, long> hashTable = minHash.GroupMinHashToLSHBuckets(hashBins, hashTables, hashKeys);
foreach (KeyValuePair<int, long> item in hashTable)
{
HashBinMinHash hash = new HashBinMinHash(-1, item.Value, item.Key, track.Id, fingerprint.Id);
listToInsert.Add(hash);
}
}
return dbService.InsertHashBin(listToInsert);
}
/// <summary>
/// Read all fingerprints ignoring the hash-buckets (e.g. HashBins)
/// </summary>
/// <returns>Return dictionary with fingerprintids as keys and the corresponding hashbins as values</returns>
public IDictionary<int, IList<HashBinMinHash>> ReadAllFingerprints() {
IDictionary<int, IList<HashBinMinHash>> result = new Dictionary<int, IList<HashBinMinHash>>();
IDbCommand dbcmd;
lock (dbcon) {
dbcmd = dbcon.CreateCommand();
}
String query = String.Format("SELECT id, hashbin, hashtable, trackid, fingerprintid FROM hashbins");
dbcmd.CommandText = query;
dbcmd.CommandType = CommandType.Text;
dbcmd.Prepare();
IDataReader reader = dbcmd.ExecuteReader();
while (reader.Read()) {
HashBinMinHash hash = new HashBinMinHash();
hash.Id = reader.GetInt32(0);
hash.Bin = reader.GetInt64(1);
hash.HashTable = reader.GetInt32(2);
hash.TrackId = reader.GetInt32(3);
hash.FingerprintId = reader.GetInt32(4);
if (result.ContainsKey(hash.FingerprintId))
{
result[hash.FingerprintId].Add(hash);
}
else
{
result.Add(hash.FingerprintId, new List<HashBinMinHash>(new[] { hash }));
}
}
reader.Close();
return result;
}
/// <summary>
/// Find fingerprints using hash-buckets (e.g. HashBins)
/// </summary>
/// <param name="hashBuckets"></param>
/// <returns>Return dictionary with fingerprintids as keys and the corresponding hashbins as values</returns>
public IDictionary<int, IList<HashBinMinHash>> ReadFingerprintsByHashBucketLshSlow(long[] hashBuckets)
{
IDbDataParameter dbHashBinParam = new SQLiteParameter("@hashbin", DbType.Int64);
IDbCommand dbcmd;
lock (dbcon) {
dbcmd = dbcon.CreateCommand();
}
dbcmd.CommandText = "SELECT id, hashbin, hashtable, trackid, fingerprintid FROM hashbins WHERE hashbin = @hashbin";
dbcmd.Parameters.Add(dbHashBinParam);
dbcmd.CommandType = CommandType.Text;
dbcmd.Prepare();
IDictionary<int, IList<HashBinMinHash>> result = new Dictionary<int, IList<HashBinMinHash>>();
foreach (long hashBin in hashBuckets)
{
dbHashBinParam.Value = hashBin;
IDataReader reader = dbcmd.ExecuteReader();
var resultPerHashBucket = new Dictionary<int, HashBinMinHash>();
while (reader.Read()) {
int hashId = reader.GetInt32(0);
long hashBin2 = reader.GetInt32(1);
int hashTable = reader.GetInt32(2);
int trackId = reader.GetInt32(3);
int fingerprintId = reader.GetInt32(4);
HashBinMinHash hash = new HashBinMinHash(hashId, hashBin2, hashTable, trackId, fingerprintId);
resultPerHashBucket.Add(fingerprintId, hash);
}
reader.Close();
foreach (var pair in resultPerHashBucket)
{
if (result.ContainsKey(pair.Key))
{
result[pair.Key].Add(pair.Value);
}
else
{
result.Add(pair.Key, new List<HashBinMinHash>(new[] { pair.Value }));
}
}
}
return result;
}
public void InsertHashBin(HashBinMinHash hashBin)
{
IDbDataParameter dbHashBinParam = new SQLiteParameter("@hashbin", DbType.Int64);
IDbDataParameter dbHashTableParam = new SQLiteParameter("@hashtable", DbType.Int32);
IDbDataParameter dbTrackIdParam = new SQLiteParameter("@trackid", DbType.Int32);
IDbDataParameter dbFingerprintIdParam = new SQLiteParameter("@fingerprintid", DbType.Int32);
IDbCommand dbcmd;
lock (dbcon) {
dbcmd = dbcon.CreateCommand();
}
dbcmd.CommandText = "INSERT INTO hashbins (hashbin, hashtable, trackid, fingerprintid) " +
"VALUES (@hashbin, @hashtable, @trackid, @fingerprintid)";
dbcmd.Parameters.Add(dbHashBinParam);
dbcmd.Parameters.Add(dbHashTableParam);
dbcmd.Parameters.Add(dbTrackIdParam);
dbcmd.Parameters.Add(dbFingerprintIdParam);
dbHashBinParam.Value = hashBin.Bin;
dbHashTableParam.Value = hashBin.HashTable;
dbTrackIdParam.Value = hashBin.TrackId;
dbFingerprintIdParam.Value = hashBin.FingerprintId;
try {
dbcmd.Prepare();
dbcmd.ExecuteNonQuery();
dbcmd.Dispose();
} catch (Exception e) {
throw e;
}
}
