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