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