public void GetDeleteSqlTest() { var id = HelperObjectFactory.GetRandomInt(0, 100); var value = new IdentifyingInfo { Id = id }; var parameters = new List <SQLiteParameter>(); var table = new AlbumTable(); var expected = $"DELETE FROM {TableFactory<Album>.GetTable<AlbumTable>().TableName} WHERE {AlbumTable.IdColumnName} = @{AlbumTable.IdColumnName};"; Assert.AreEqual(expected, table.GetDeleteSql(value, ref parameters)); Assert.AreEqual(1, parameters.Count); parameters.Clear(); value = new IdentifyingInfo { Name = StringExtensions.GetRandomStringAsync(25).Result }; expected = $"DELETE FROM {TableFactory<Album>.GetTable<AlbumTable>().TableName} WHERE {AlbumTable.NameColumnName} = @{AlbumTable.NameColumnName};"; Assert.AreEqual(expected, table.GetDeleteSql(value, ref parameters)); Assert.AreEqual(1, parameters.Count); }
public async Task <List <Album> > GetAlbumsAsync(int id) { return(await Task.Run(async() => { List <Album> items = null; if (Table is ArtistTable artistTable) { items = new List <Album>(); var albumController = new AlbumController(DataAccess); var albumTable = new AlbumTable(); var parameters = new List <SQLiteParameter>(); var sql = artistTable.GetAlbumsByArtistSql(new IdentifyingInfo(id), ref parameters); using (var cursor = await DataAccess.ExecuteQueryWithParametersAsync(sql, parameters)) { if (!Cursor.IsNullOrEmpty(cursor)) { var indexMap = albumTable.GetColumnNameIndexMap(); var keys = indexMap.Keys.ToList(); foreach (var key in keys) { indexMap[key] = cursor.GetColumnIndex(key); } do { items.Add(await albumController.MakeItemAsync(cursor, indexMap)); } while (cursor.MoveToNextRow()); } } } return items; })); }
public void GetCreateSqlTest() { var table = new AlbumTable(); var expected = $"CREATE TABLE {TableFactory<Album>.GetTable<AlbumTable>().TableName} ({AlbumTable.IdColumnName} INTEGER NOT NULL PRIMARY KEY, {AlbumTable.NameColumnName} TEXT UNIQUE NOT NULL COLLATE NOCASE, {AlbumTable.YearColumnName} INTEGER NOT NULL, {AlbumTable.ArtistIdColumnName} INTEGER NOT NULL, {AlbumTable.GenreIdColumnName} INTEGER NOT NULL, FOREIGN KEY({AlbumTable.ArtistIdColumnName}) REFERENCES {TableFactory<Artist>.GetTable<ArtistTable>().TableName}({ArtistTable.IdColumnName}), FOREIGN KEY({AlbumTable.GenreIdColumnName}) REFERENCES {TableFactory<Genre>.GetTable<GenreTable>().TableName}({GenreTable.IdColumnName})) WITHOUT ROWID;"; Assert.AreEqual(expected, table.GetCreateSql()); }
public void ConstructorTest() { var boolNullable = new AlbumTable(); Assert.AreEqual("albums", TableFactory <Album> .GetTable <AlbumTable>().TableName); Assert.IsNotNull(boolNullable); }
public void GetUpdateSqlTest() { var id = HelperObjectFactory.GetRandomInt(0, 100); var value = new Album(id, id, id, StringExtensions.GetRandomStringAsync(23).Result, 2002); var parameters = new List <SQLiteParameter>(); var table = new AlbumTable(); var expected = $"UPDATE {TableFactory<Album>.GetTable<AlbumTable>().TableName} SET {AlbumTable.ArtistIdColumnName} = @{AlbumTable.ArtistIdColumnName}, {AlbumTable.GenreIdColumnName} = @{AlbumTable.GenreIdColumnName}, {AlbumTable.NameColumnName} = @{AlbumTable.NameColumnName}, {AlbumTable.YearColumnName} = @{AlbumTable.YearColumnName} WHERE {AlbumTable.IdColumnName} = @{AlbumTable.IdColumnName};"; Assert.AreEqual(expected, table.GetUpdateSql(value, ref parameters)); Assert.AreEqual(5, parameters.Count); }
public void GetInsertSqlTest() { var id = HelperObjectFactory.GetRandomInt(0, 100); var value = new Album(id, id, id, StringExtensions.GetRandomStringAsync(23).Result, 2002); var parameters = new List <SQLiteParameter>(); var table = new AlbumTable(); var expected = $"INSERT OR IGNORE INTO {TableFactory<Album>.GetTable<AlbumTable>().TableName} ({AlbumTable.IdColumnName}, {AlbumTable.ArtistIdColumnName}, {AlbumTable.GenreIdColumnName}, {AlbumTable.NameColumnName}, {AlbumTable.YearColumnName}) VALUES(@{AlbumTable.IdColumnName}, @{AlbumTable.ArtistIdColumnName}, @{AlbumTable.GenreIdColumnName}, @{AlbumTable.NameColumnName}, @{AlbumTable.YearColumnName});"; Assert.AreEqual(expected, table.GetInsertSql(value, ref parameters)); Assert.AreEqual(5, parameters.Count); }
public void AlbumSearch(String ArtistID, String ArtistName) { //https://musicbrainz.org/ws/2/release?release-group=3d00fb45-f8ab-3436-a8e1-b4bfc4d66913 //http://musicbrainz.org/ws/1/release/03e4ebe1-0a44-411c-8e19-78e0768603f8?type=xml&inc=tracks var Albumrequest = (HttpWebRequest)WebRequest.Create("https://musicbrainz.org/ws/2/release-group?artist=" + ArtistID); //var Albumrequest = (HttpWebRequest)WebRequest.Create("http://musicbrainz.org/ws/1/artist/" + ArtistID + "?type=xml&inc=sa-Official+release-events"); Albumrequest.Method = "GET"; Albumrequest.UserAgent = "DesktopApp1/1.1.1 ( [email protected] )"; Albumrequest.Credentials = new NetworkCredential("sdvpl2011", "musicbrainz"); var Albumresponse = (HttpWebResponse)Albumrequest.GetResponse(); String Aname = ArtistName; String Albumname = ""; String ReleaseDate = ""; String AlbumID1 = ""; XmlReader reader = XmlReader.Create(Albumresponse.GetResponseStream()); while (reader.Read()) { if ((reader.NodeType == XmlNodeType.Element) && (reader.Name == "release-group")) { AlbumID1 = reader.GetAttribute("id"); } if ((reader.NodeType == XmlNodeType.Element) && (reader.Name == "title")) { Albumname = reader.ReadElementContentAsString(); } if ((reader.NodeType == XmlNodeType.Element) && (reader.Name == "first-release-date")) { ReleaseDate = reader.ReadElementContentAsString(); String[] row1 = { Aname, Albumname, ReleaseDate }; System.Diagnostics.Debug.WriteLine("ArtistName = " + Aname); System.Diagnostics.Debug.WriteLine("ReleaseName = " + Albumname); System.Diagnostics.Debug.WriteLine("ReleaseDate = " + ReleaseDate); AlbumTable at = new AlbumTable { ArtistName = Aname, ReleaseName = Albumname, ReleaseDate = ReleaseDate, AlbumID = AlbumID1 }; db.AlbumTableEntry.Add(at); db.SaveChanges(); } } Albumresponse.Close(); }
// Unlike LookupAlbumByName, returns null if it cannot find such an album instead of creating one public AlbumModel SearchAlbumByName(string albumName, int albumArtistId) { AlbumTable albumTable = DatabaseManager.Current.LookupAlbum(albumName, albumArtistId); if (albumTable == null) { return(null); } else { return(LookupAlbumById(albumTable.AlbumId)); } }
public void GetDeleteSqlExceptionTest() { var value = new IdentifyingInfo { Id = null, Name = null }; var parameters = new List <SQLiteParameter>(); var table = new AlbumTable(); Assert.That(() => table.GetDeleteSql(value, ref parameters), Throws.ArgumentException); }
public static TrackInfo TrackInfoFromRowId(int rowId) { try { PlayQueueEntryTable playQueueEntry = DatabaseManager.Current.LookupPlayQueueEntryById(rowId); if (playQueueEntry != null) { SongTable songTable = DatabaseManager.Current.LookupSongById(playQueueEntry.SongId); if (songTable != null) { AlbumTable albumTable = DatabaseManager.Current.LookupAlbumById(songTable.AlbumId); ArtistTable artistTable = DatabaseManager.Current.LookupArtistById(songTable.ArtistId); if (albumTable != null && artistTable != null) { ArtistTable albumArtistTable = DatabaseManager.Current.LookupArtistById(albumTable.ArtistId); if (albumArtistTable != null) { return(new TrackInfo(songTable.Name, artistTable.Name, albumTable.Name, albumArtistTable.Name, songTable.Source, albumTable.AlbumArt, rowId, playQueueEntry.NextId, playQueueEntry.PrevId, playQueueEntry.SongId)); } else { Logger.Current.Log(new CallerInfo(), LogLevel.Warning, "Couldn't play row {0}, no artistEntry {1} matches!", rowId, albumTable.ArtistId); } } else { Logger.Current.Log(new CallerInfo(), LogLevel.Warning, "Couldn't play row {0}, no albumEntry {1} or artistEntry {2} matches ({3} {4})!", rowId, songTable.AlbumId, songTable.ArtistId, albumTable != null, artistTable != null); } } else { Logger.Current.Log(new CallerInfo(), LogLevel.Warning, "Couldn't play row {0}, no songEntry for {1} matches!", rowId, playQueueEntry.SongId); } } else { Logger.Current.Log(new CallerInfo(), LogLevel.Warning, "Couldn't play row {0}, no playQueueEntry matches!", rowId); } } catch (SQLiteException ex) { Logger.Current.Log(new CallerInfo(), LogLevel.Error, "Couldn't play row {0}, got an exception {1}!", rowId, ex.Message); } return(null); }
public async Task InitializeDatabaseNonUniqueIdsExceptionTest() { using (var container = Substitute.For <UnityContainer>()) { using (var cursor = Substitute.For <ICursor>()) { cursor.GetInt(0).Returns(1); var artistTable = new ArtistTable(); var albumTable = new AlbumTable(); var genreTable = new GenreTable(); var artistParameters = new List <SQLiteParameter>(); var albumParameters = new List <SQLiteParameter>(); var genreParameters = new List <SQLiteParameter>(); var artistSql = artistTable.GetInsertSql(new Artist(0, "Michael Jackson"), ref artistParameters); var genreSql = genreTable.GetInsertSql(new Genre(0, "Pop"), ref genreParameters); var albumSql = albumTable.GetInsertSql(new Album(0, 0, 0, "Thriller", 1982), ref albumParameters); var dataAccess = Substitute.For <IDataAccess>(); dataAccess.OpenOrCreateDatabaseAsync().Returns(DatabaseState.New); dataAccess.ExecuteQueryWithParametersAsync(Arg.Any <string>(), Arg.Any <List <SQLiteParameter> >()).Returns(cursor); dataAccess.ExecuteSqlWithParametersAsync(artistSql, Arg.Any <List <SQLiteParameter> >()).Returns(1); dataAccess.ExecuteSqlWithParametersAsync(genreSql, Arg.Any <List <SQLiteParameter> >()).Returns(0); dataAccess.ExecuteSqlWithParametersAsync(albumSql, Arg.Any <List <SQLiteParameter> >()).Returns(1); container.RegisterInstance(Resources.DataAccessObjectName, dataAccess, new ContainerControlledLifetimeManager()); container.RegisterType <AlbumController>(new InjectionFactory(s => new AlbumController(dataAccess))); container.RegisterType <ArtistController>(new InjectionFactory(s => new ArtistController(dataAccess))); container.RegisterType <GenreController>(new InjectionFactory(s => new GenreController(dataAccess))); container.RegisterType <StatisticsController>(new InjectionFactory(s => new StatisticsController(dataAccess))); try { await DatabaseHelpers.InitializeDatabase(container); } catch (Exception ex) { Assert.IsInstanceOf <SQLiteException>(ex); Assert.AreEqual($"unknown error\r\n{Resources.ErrorAddingItemMessage}", ex.Message); } } } }
public AlbumModel LookupAlbumByName(string albumName, int albumArtistId) { AlbumTable albumTable = DatabaseManager.Current.LookupAlbum(albumName, albumArtistId); if (albumTable == null) { AlbumTable newAlbum = new AlbumTable(string.Empty, albumArtistId, albumName, 0); DatabaseManager.Current.AddAlbum(newAlbum); AlbumModel albumModel = new AlbumModel(newAlbum); albumLookupDictionary.Add(albumModel.AlbumId, albumModel); RaiseAlbumCreated(albumModel); return(albumModel); } else { return(LookupAlbumById(albumTable.AlbumId)); } }
public AlbumModel LookupAlbumById(int albumId) { if (albumLookupDictionary.ContainsKey(albumId)) { return(albumLookupDictionary[albumId]); } else { AlbumTable albumTable = DatabaseManager.Current.LookupAlbumById(albumId); if (albumTable == null) { return(null); } else { AlbumModel albumModel = new AlbumModel(albumTable); albumLookupDictionary.Add(albumModel.AlbumId, albumModel); return(albumModel); } } }
public AlbumModel LookupAlbumByName(string albumName, int albumArtistId) { AlbumTable albumTable = DatabaseManager.Current.LookupAlbum(albumName, albumArtistId); if (albumTable == null) { AlbumTable newAlbum = new AlbumTable(string.Empty, albumArtistId, albumName, 0); DatabaseManager.Current.AddAlbum(newAlbum); AlbumModel albumModel = new AlbumModel(newAlbum); albumLookupDictionary.Add(albumModel.AlbumId, albumModel); RaiseAlbumCreated(albumModel); return albumModel; } else { return LookupAlbumById(albumTable.AlbumId); } }
internal int AddAlbum(AlbumTable albumTable) { sqlConnection.Insert(albumTable); return albumTable.AlbumId; }