public IList<Fingerprint> ReadFingerprintsByTrackId(int trackId, int numberOfFingerprintsToRead)
{
var fingerprints = new List<Fingerprint>();
IDbCommand dbcmd;
lock (dbcon) {
dbcmd = dbcon.CreateCommand();
}
dbcmd.CommandText = "SELECT id, songorder, signature FROM [fingerprints] WHERE [trackid] = @trackid LIMIT @limit";
dbcmd.Parameters.Add(new SQLiteParameter("@trackid") { Value = trackId });
dbcmd.Parameters.Add(new SQLiteParameter("@limit") { Value = numberOfFingerprintsToRead });
dbcmd.CommandType = CommandType.Text;
dbcmd.Prepare();
IDataReader reader = dbcmd.ExecuteReader();
while (reader.Read()) {
Fingerprint fingerprint = new Fingerprint();
fingerprint.Id = reader.GetInt32(0);
fingerprint.TrackId = trackId;
fingerprint.SongOrder = reader.GetInt32(1);
fingerprint.Signature = ByteToBool((byte[]) reader.GetValue(2));
fingerprints.Add(fingerprint);
}
reader.Close();
dbcmd.Dispose();
return fingerprints;
}
public IDictionary<int, IList<Fingerprint>> ReadFingerprintsByMultipleTrackId(
IEnumerable<Track> tracks, int numberOfFingerprintsToRead)
{
var result = new Dictionary<int, IList<Fingerprint>>();
var fingerprints = new List<Fingerprint>();
String statementValueTags = String.Join(",", tracks.Select(x => x.Id));
IDbCommand dbcmd;
lock (dbcon) {
dbcmd = dbcon.CreateCommand();
}
String query = String.Format("SELECT id, trackid, songorder, signature FROM [fingerprints] WHERE (trackid IN ({0})) LIMIT {0};", statementValueTags, numberOfFingerprintsToRead);
dbcmd.CommandText = query;
dbcmd.CommandType = CommandType.Text;
dbcmd.Prepare();
IDataReader reader = dbcmd.ExecuteReader();
int lastTrackId = -1;
while (reader.Read()) {
Fingerprint fingerprint = new Fingerprint();
fingerprint.Id = reader.GetInt32(0);
fingerprint.TrackId = reader.GetInt32(1);
fingerprint.SongOrder = reader.GetInt32(2);
fingerprint.Signature = ByteToBool((byte[]) reader.GetValue(3));
if (lastTrackId == -1 || lastTrackId == fingerprint.TrackId) {
// still processing same track
} else {
// new track
// add fingerprints to dictionary and then reset fingerprints
result.Add(lastTrackId, fingerprints);
fingerprints.Clear();
fingerprints.Add(fingerprint);
}
lastTrackId = fingerprint.TrackId;
}
if (lastTrackId != -1) {
// add last fingerprints
result.Add(lastTrackId, fingerprints);
}
reader.Close();
dbcmd.Dispose();
return result;
//return fingerprintDao.ReadFingerprintsByMultipleTrackId(tracks, numberOfFingerprintsToRead);
}
public void InsertFingerprint(Fingerprint fingerprint)
{
IDbDataParameter dbTrackIdParam = new SQLiteParameter("@trackid", DbType.Int32);
IDbDataParameter dbSongOrderParam = new SQLiteParameter("@songorder", DbType.Int32);
IDbDataParameter dbTotalFingerprintsParam = new SQLiteParameter("@totalfingerprints", DbType.Int32);
IDbDataParameter dbSignatureParam = new SQLiteParameter("@signature", DbType.Binary);
IDbCommand dbcmd;
lock (dbcon) {
dbcmd = dbcon.CreateCommand();
}
dbcmd.CommandText = "INSERT INTO fingerprints (trackid, songorder, totalfingerprints, signature) " +
"VALUES (@trackid, @songorder, @totalfingerprints, @signature); SELECT last_insert_rowid();";
dbcmd.Parameters.Add(dbTrackIdParam);
dbcmd.Parameters.Add(dbSongOrderParam);
dbcmd.Parameters.Add(dbTotalFingerprintsParam);
dbcmd.Parameters.Add(dbSignatureParam);
dbTrackIdParam.Value = fingerprint.TrackId;
dbSongOrderParam.Value = fingerprint.SongOrder;
dbTotalFingerprintsParam.Value = fingerprint.TotalFingerprintsPerTrack;
dbSignatureParam.Value = fingerprint.Signature;
try {
dbcmd.Prepare();
//dbcmd.ExecuteNonQuery();
fingerprint.Id = Convert.ToInt32(dbcmd.ExecuteScalar());
dbcmd.Dispose();
} catch (Exception e) {
throw e;
}
}
public IList<Fingerprint> ReadFingerprints()
{
var fingerprints = new List<Fingerprint>();
IDbCommand dbcmd;
lock (dbcon) {
dbcmd = dbcon.CreateCommand();
}
dbcmd.CommandText = "SELECT id, trackid, songorder, signature FROM [fingerprints]";
dbcmd.CommandType = CommandType.Text;
IDataReader reader = dbcmd.ExecuteReader();
while (reader.Read()) {
Fingerprint fingerprint = new Fingerprint();
fingerprint.Id = reader.GetInt32(0);
fingerprint.TrackId = reader.GetInt32(1);
fingerprint.SongOrder = reader.GetInt32(2);
fingerprint.Signature = ByteToBool((byte[]) reader.GetValue(3));
fingerprints.Add(fingerprint);
}
reader.Close();
dbcmd.Dispose();
return fingerprints;
}
public void InsertReadFingerprintTest()
{
string name = MethodBase.GetCurrentMethod().Name;
DaoGateway manager = dalManager;
Album album = new Album(0, name, 1986);
manager.InsertAlbum(album);
Track track = new Track(0, name, name, album.Id, 360);
manager.InsertTrack(track);
Fingerprint f = new Fingerprint(0, GenericFingerprint, track.Id, 0);
manager.InsertFingerprint(f);
List<Fingerprint> allFingerprints = manager.ReadFingerprints();
List<int> lGuid = allFingerprints.Select(temp => temp.Id).ToList();
Assert.AreEqual(true, lGuid.Contains(f.Id));
List<Fingerprint> addList = new List<Fingerprint>();
for (int i = 0; i < 10; i++)
{
addList.Add(new Fingerprint(0, GenericFingerprint, track.Id, 0));
}
manager.InsertFingerprint(addList);
allFingerprints = manager.ReadFingerprints();
lGuid.Clear();
lGuid.AddRange(allFingerprints.Select(temp => temp.Id));
foreach (Fingerprint finger in addList)
{
Assert.AreEqual(true, lGuid.Contains(finger.Id));
}
}
public void ReadTrackByFingerprintTest()
{
string name = MethodBase.GetCurrentMethod().Name;
DaoGateway manager = dalManager;
Album album = new Album(int.MinValue, name, 1986);
manager.InsertAlbum(album);
Track track = new Track(int.MinValue, name, name, album.Id, 360);
manager.InsertTrack(track);
const int FakeId = int.MinValue;
Fingerprint f = new Fingerprint(FakeId, GenericFingerprint, track.Id, int.MinValue);
manager.InsertFingerprint(f);
Assert.AreNotEqual(FakeId, f.Id);
List<Track> list = manager.ReadTrackByFingerprint(f.Id);
Track readT = list.FirstOrDefault(temp => temp.Id == track.Id);
Assert.AreNotEqual(null, readT);
Assert.AreEqual(track.Id, readT.Id);
Assert.AreEqual(track.AlbumId, readT.AlbumId);
Assert.AreEqual(track.Artist, readT.Artist);
Assert.AreEqual(track.Title, readT.Title);
Assert.AreEqual(track.TrackLength, readT.TrackLength);
}
public void ReadFingerprintByTrackIdTest()
{
string name = MethodBase.GetCurrentMethod().Name;
DaoGateway manager = dalManager;
Album album = new Album(0, name, 1986);
manager.InsertAlbum(album);
Track track = new Track(0, name, name, album.Id, 360);
manager.InsertTrack(track);
Fingerprint f = new Fingerprint(0, GenericFingerprint, track.Id, 0);
manager.InsertFingerprint(f);
List<Fingerprint> list = manager.ReadFingerprintsByTrackId(track.Id, 0);
Fingerprint readF = list.FirstOrDefault(temp => temp.Id == f.Id);
Assert.AreNotEqual(null, readF);
Assert.AreEqual(f.Id, readF.Id);
Assert.AreEqual(f.Signature.Length, readF.Signature.Length);
for (int i = 0; i < f.Signature.Length; i++)
{
Assert.AreEqual(f.Signature[i], readF.Signature[i]);
}
Assert.AreEqual(f.TrackId, readF.TrackId);
}
public void GetInsertFingerprintCommandTest()
{
string name = MethodBase.GetCurrentMethod().Name;
DaoStoredProcedureBuilder target = new DaoStoredProcedureBuilder();
// TODO: Initialize to an appropriate value
Fingerprint fingerprint = new Fingerprint(0, GenericFingerprint, 0, 64, 100);
DbConnection connection = Dbf.CreateConnection();
IDbCommand actual = target.GetInsertFingerprintCommand(fingerprint, connection);
Assert.AreEqual(SP_INSERT_FINGERPRINT, actual.CommandText);
Assert.AreEqual(CommandType.StoredProcedure, actual.CommandType);
Assert.IsNotNull(actual.Connection);
Assert.AreEqual(ConnectionState.Closed, actual.Connection.State);
Assert.AreEqual(4, actual.Parameters.Count);
}
public void ReadFingerprintByMultipleTrackIdTest()
{
string name = MethodBase.GetCurrentMethod().Name;
DaoGateway manager = dalManager;
Album album = new Album(0, name, 1986);
manager.InsertAlbum(album);
Track track0 = new Track(0, name, name, album.Id, 360);
manager.InsertTrack(track0);
Track track1 = new Track(0, name, name, album.Id, 360);
manager.InsertTrack(track1);
Track track2 = new Track(0, name, name, album.Id, 360);
manager.InsertTrack(track2);
Fingerprint f0 = new Fingerprint(0, GenericFingerprint, track0.Id, 0);
manager.InsertFingerprint(f0);
Fingerprint f1 = new Fingerprint(0, GenericFingerprint, track0.Id, 1);
manager.InsertFingerprint(f1);
Fingerprint f2 = new Fingerprint(0, GenericFingerprint, track1.Id, 2);
manager.InsertFingerprint(f2);
Fingerprint f3 = new Fingerprint(0, GenericFingerprint, track1.Id, 3);
manager.InsertFingerprint(f3);
Fingerprint f4 = new Fingerprint(0, GenericFingerprint, track2.Id, 4);
manager.InsertFingerprint(f4);
Fingerprint f5 = new Fingerprint(0, GenericFingerprint, track2.Id, 5);
manager.InsertFingerprint(f5);
Fingerprint f6 = new Fingerprint(0, GenericFingerprint, track0.Id, 6);
manager.InsertFingerprint(f6);
Fingerprint f7 = new Fingerprint(0, GenericFingerprint, track1.Id, 7);
manager.InsertFingerprint(f7);
Fingerprint f8 = new Fingerprint(0, GenericFingerprint, track2.Id, 8);
manager.InsertFingerprint(f8);
Dictionary<int, List<Fingerprint>> dict =
manager.ReadFingerprintsByMultipleTrackId(new List<Track> { track0, track1, track2 }, 0);
Assert.AreNotEqual(null, dict);
Assert.AreEqual(3, dict.Keys.Count);
foreach (KeyValuePair<int, List<Fingerprint>> item in dict)
{
Assert.AreEqual(3, item.Value.Count);
}
Assert.AreEqual(true, dict.ContainsKey(track0.Id));
Assert.AreEqual(true, dict.ContainsKey(track1.Id));
Assert.AreEqual(true, dict.ContainsKey(track2.Id));
foreach (KeyValuePair<int, List<Fingerprint>> pair in dict)
{
Assert.AreEqual(3, pair.Value.Count);
}
}
public void ReadFingerprintByMultipleTrackIdTest1()
{
string name = MethodBase.GetCurrentMethod().Name;
DaoGateway manager = dalManager;
Album album = new Album(0, name, 1986);
manager.InsertAlbum(album);
const int NumberOfTracks = 1153;
const int NumberOfFingerprintsPerTrack = 10;
List<Track> listTrack = new List<Track>();
List<Fingerprint> listOfFingerprints = new List<Fingerprint>();
for (int i = 0; i < NumberOfTracks; i++)
{
Track track0 = new Track(0, name, name, album.Id, 360);
listTrack.Add(track0);
manager.InsertTrack(track0);
for (int j = 0; j < NumberOfFingerprintsPerTrack; j++)
{
Fingerprint f0 = new Fingerprint(0, GenericFingerprint, track0.Id, 0);
listOfFingerprints.Add(f0);
}
}
manager.InsertFingerprint(listOfFingerprints);
Dictionary<int, List<Fingerprint>> dict = manager.ReadFingerprintsByMultipleTrackId(listTrack, 0);
Assert.AreNotEqual(null, dict);
Assert.AreEqual(NumberOfTracks, dict.Keys.Count);
foreach (Track track in listTrack)
{
Assert.AreEqual(true, dict.ContainsKey(track.Id));
Assert.AreEqual(NumberOfFingerprintsPerTrack, dict[track.Id].Count);
}
}
public void ReadFingerprintByIdTest()
{
string name = MethodBase.GetCurrentMethod().Name;
DaoGateway manager = dalManager;
const int fakeId = int.MinValue;
Album album = new Album(fakeId, name, 1986);
manager.InsertAlbum(album);
Assert.AreNotEqual(fakeId, album);
Track track = new Track(fakeId, name, name, album.Id, 360);
manager.InsertTrack(track);
Assert.AreNotEqual(fakeId, track.Id);
Fingerprint f = new Fingerprint(fakeId, GenericFingerprint, track.Id, 0);
manager.InsertFingerprint(f);
Assert.AreNotEqual(fakeId, f.Id);
Fingerprint readF = manager.ReadFingerprintById(f.Id);
Assert.AreEqual(f.Id, readF.Id);
Assert.AreEqual(f.Signature.Length, readF.Signature.Length);
for (int i = 0; i < f.Signature.Length; i++)
{
Assert.AreEqual(f.Signature[i], readF.Signature[i]);
}
Assert.AreEqual(f.TrackId, readF.TrackId);
}
public void InsertReadHashBinNeuralHashesTest()
{
string name = MethodBase.GetCurrentMethod().Name;
Album album = dalManager.ReadUnknownAlbum();
Track track = new Track(0, name, name, album.Id);
dalManager.InsertTrack(track);
Fingerprint finger = new Fingerprint(0, GenericFingerprint, track.Id, 0);
dalManager.InsertFingerprint(finger);
const long Hashbin = 100000;
const int Hashtable = 20;
List<HashBinNeuralHasher> list = new List<HashBinNeuralHasher>();
const int Count = 20;
for (int i = 0; i < Count; i++)
{
HashBinNeuralHasher hashbinminhash = new HashBinNeuralHasher(0, Hashbin, Hashtable, track.Id);
list.Add(hashbinminhash);
}
dalManager.InsertHashBin(list);
List<int> result = dalManager.ReadTrackIdByHashBinAndHashTableNeuralHasher(Hashbin, Hashtable);
Assert.IsNotNull(result);
Assert.AreEqual(Count, result.Count);
dalManager.DeleteTrack(track.Id);
}
public void InsertReadHashBinMinHashTest()
{
string name = MethodBase.GetCurrentMethod().Name;
Album album = dalManager.ReadUnknownAlbum();
Track track = new Track(0, name, name, album.Id);
dalManager.InsertTrack(track);
Fingerprint finger = new Fingerprint(0, GenericFingerprint, track.Id, 0);
dalManager.InsertFingerprint(finger);
const long Hashbin = 100000;
const int Hashtable = 20;
HashBinMinHash hashbinminhash = new HashBinMinHash(0, Hashbin, Hashtable, track.Id, finger.Id);
dalManager.InsertHashBin(hashbinminhash);
Dictionary<int, List<HashBinMinHash>> result =
dalManager.ReadFingerprintsByHashBucketAndHashTableLSH(new[] { Hashbin }, new[] { Hashtable });
Assert.IsNotNull(result);
Assert.AreEqual(1, result.Count);
Assert.AreEqual(true, result.ContainsKey(finger.Id));
Assert.AreEqual(1, result[finger.Id].Count);
Assert.AreEqual(hashbinminhash.Id, result[finger.Id][0].Id);
dalManager.DeleteTrack(track.Id);
}
public Fingerprint ReadFingerprintById(int id)
{
IDbCommand dbcmd;
lock (dbcon) {
dbcmd = dbcon.CreateCommand();
}
dbcmd.CommandText = "SELECT trackid, songorder, totalfingerprints, signature FROM [fingerprints] WHERE [id] = @id";
dbcmd.Parameters.Add(new SQLiteParameter("@id") { Value = id });
dbcmd.CommandType = CommandType.Text;
dbcmd.Prepare();
IDataReader reader = dbcmd.ExecuteReader();
if (!reader.Read()) {
return null;
}
Fingerprint fingerprint = new Fingerprint();
fingerprint.Id = id;
fingerprint.TrackId = reader.GetInt32(0);
fingerprint.SongOrder = reader.GetInt32(1);
fingerprint.TotalFingerprintsPerTrack = reader.GetInt32(2);
fingerprint.Signature = ByteToBool((byte[]) reader.GetValue(3));
reader.Close();
dbcmd.Dispose();
return fingerprint;
}
public void ReadFingerprintsTest()
{
string name = MethodBase.GetCurrentMethod().Name;
DaoGateway target = new DaoGateway(connectionstring);
Album album = target.ReadUnknownAlbum();
Track t = new Track(0, name, name, album.Id);
target.InsertTrack(t);
Fingerprint f = new Fingerprint(0, GenericFingerprint, t.Id, 10);
target.InsertFingerprint(f);
List<Fingerprint> actual = target.ReadFingerprints();
Assert.IsTrue(actual.Count >= 1);
}
public IList<Fingerprint> ReadFingerprintById(IEnumerable<int> ids)
{
var fingerprints = new List<Fingerprint>();
IDbCommand dbcmd;
lock (dbcon) {
dbcmd = dbcon.CreateCommand();
}
String statementValueTags = String.Join(",", ids);
String query = String.Format("SELECT id, trackid, songorder, totalfingerprints, signature FROM [fingerprints] WHERE (id IN ({0}));", statementValueTags);
dbcmd.CommandText = query;
dbcmd.CommandType = CommandType.Text;
dbcmd.Prepare();
IDataReader reader = dbcmd.ExecuteReader();
while (reader.Read()) {
Fingerprint fingerprint = new Fingerprint();
fingerprint.Id = reader.GetInt32(0);
fingerprint.TrackId = reader.GetInt32(1);
fingerprint.SongOrder = reader.GetInt32(2);
fingerprint.TotalFingerprintsPerTrack = reader.GetInt32(3);
fingerprint.Signature = ByteToBool((byte[]) reader.GetValue(4));
fingerprints.Add(fingerprint);
}
reader.Close();
dbcmd.Dispose();
return fingerprints;
}
public void ReadTrackByFingerprintInexistantIdTest()
{
string name = MethodBase.GetCurrentMethod().Name;
DaoGateway manager = dalManager;
const int FakeId = int.MinValue;
Album album = new Album(FakeId, name, 1986);
manager.InsertAlbum(album);
Track track = new Track(FakeId, name, name, album.Id, 360);
manager.InsertTrack(track);
Fingerprint f = new Fingerprint(FakeId, GenericFingerprint, track.Id, 0);
List<Track> list = manager.ReadTrackByFingerprint(f.Id);
Assert.AreEqual(null, list);
}
/// <summary>
/// Get a command to inset a fingerprint into the database
/// </summary>
/// <param name = "fingerprint">Fingerprint to be inserted</param>
/// <param name = "connection">Connection used to insert the fingerprint</param>
/// <returns>Command to be executed</returns>
public DbCommand GetInsertFingerprintCommand(Fingerprint fingerprint, DbConnection connection)
{
DbCommand command = GetStoredProcedureCommand(SP_INSERT_FINGERPRINT, connection);
DbParameter param = command.CreateParameter();
param.ParameterName = PARAM_FINGERPRINT_FINGERPRINT;
param.DbType = DbType.Binary;
param.Value = ArrayUtils.GetByteArrayFromBool(fingerprint.Signature);
command.Parameters.Add(param);
param = command.CreateParameter();
param.ParameterName = PARAM_FINGERPRINT_TRACK_ID;
param.DbType = DbType.Int32;
param.Value = fingerprint.TrackId;
command.Parameters.Add(param);
param = command.CreateParameter();
param.ParameterName = PARAM_FINGERPRINT_SONG_ORDER;
param.DbType = DbType.Int32;
param.Value = fingerprint.SongOrder;
command.Parameters.Add(param);
param = command.CreateParameter();
param.ParameterName = PARAM_FINGERPRINT_TOTAL_FINGERPRINTS_PER_TRACK;
param.DbType = DbType.Int32;
param.Value = fingerprint.TotalFingerprintsPerTrack;
command.Parameters.Add(param);
return command;
}
public void InsertFingerprintWithBadTrackIdForeignKeyreference()
{
string name = MethodBase.GetCurrentMethod().Name;
Album a = new Album(0, name, 1990);
Track track = new Track(0, name, name, a.Id);
Fingerprint f = new Fingerprint(0, GenericFingerprint, track.Id, 0);
dalManager.InsertFingerprint(f);
}
