public static Int64 CheckArtistInformations(MediaFile file, Transaction tran)
{
const String select =
"select a.LIBRARY_ID, b.PROPERTY_VALUE from MEDIA_LIBRARY a " +
"inner join MEDIA_LIBRARY_PROPERTY b on a.LIBRARY_ID = b.LIBRARY_ID " +
"where a.TYPE_ID = @ML_TYPE_ID and b.TYPE_ID = @TYPE_ID and b.PROPERTY_VALUE = @NAME";
Int64 returnId;
if (String.IsNullOrEmpty(file.ArtistName))
{
return getUnknownArtist(tran);
}
else
{
using (DataReader reader = tran.CallReader(select,
Static.Db.NewParameter("@ML_TYPE_ID", MediaLibraryType.Artist),
Static.Db.NewParameter("@TYPE_ID", MediaLibraryPropertyType.Name),
Static.Db.NewParameter("@NAME", file.ArtistName)))
{
if (reader.Read())
{
if (!file.ArtistName.Equals((String)reader["PROPERTY_VALUE"], StringComparison.InvariantCultureIgnoreCase))
tran.CallNonQuery(MediaLibrary.UpdateMediaLibraryPropertySql,
Static.Db.NewParameter("@LIBRARY_ID", reader["LIBRARY_ID"]),
Static.Db.NewParameter("@TYPE_ID", MediaLibraryPropertyType.Name),
Static.Db.NewParameter("@PROPERTY_VALUE", file.ArtistName),
Static.Db.NewParameter("@MOD_DATE", DateTime.Now));
returnId = reader["LIBRARY_ID"];
}
else
{
tran.CallNonQuery(MediaLibrary.InsertMediaLibrarySql,
Static.Db.NewParameter("@TYPE_ID", MediaLibraryType.Artist),
Static.Db.NewParameter("@MOD_DATE", DateTime.Now),
Static.Db.NewParameter("@PARENT_ID", DBNull.Value));
returnId = tran.CallValue(Static.GetIdentityStatement);
tran.CallNonQuery(MediaLibrary.InsertMediaLibraryPropertySql,
Static.Db.NewParameter("@LIBRARY_ID", returnId),
Static.Db.NewParameter("@TYPE_ID", MediaLibraryPropertyType.Name),
Static.Db.NewParameter("@PROPERTY_VALUE", file.ArtistName),
Static.Db.NewParameter("@MOD_DATE", DateTime.Now));
}
}
}
return returnId;
}
private static void checkImageInformations(MediaFile file, Int64 trackId, Transaction tran)
{
const String select =
"select count(*) from MEDIA_LIBRARY_IMAGE a " +
"where a.LIBRARY_ID = @LIBRARY_ID and MD5_HASH = @MD5_HASH";
const String insert =
"insert into MEDIA_LIBRARY_IMAGE " +
"( LIBRARY_ID, IMAGE_DATA, MD5_HASH, MOD_DATE, MOD_USER) values " +
"(@LIBRARY_ID,@IMAGE_DATA,@MD5_HASH,@MOD_DATE,@MOD_USER)";
if (file.Image != null)
{
String md5Hash = new Hex(MD5Stream.GetHash(file.Image));
Int64 exists = tran.CallValue(select,
Static.Db.NewParameter("@LIBRARY_ID", trackId),
Static.Db.NewParameter("@MD5_HASH", md5Hash));
if (exists == 0)
tran.CallNonQuery(insert,
Static.Db.NewParameter("@LIBRARY_ID", trackId),
Static.Db.NewParameter("@IMAGE_DATA", file.Image),
Static.Db.NewParameter("@MD5_HASH", md5Hash),
Static.Db.NewParameter("@MOD_DATE", DateTime.Now),
Static.Db.NewParameter("@MOD_USER", DBNull.Value));
}
}
private static long getUnknownArtist(Transaction tran)
{
const String select =
"select a.LIBRARY_ID from MEDIA_LIBRARY a " +
"inner join MEDIA_LIBRARY_PROPERTY b on a.LIBRARY_ID = b.LIBRARY_ID " +
"where b.TYPE_ID = @TYPE_ID and b.PROPERTY_VALUE = @NAME";
Int64 returnId;
using (DataReader reader = tran.CallReader(select,
Static.Db.NewParameter("@TYPE_ID", MediaLibraryPropertyType.Name),
Static.Db.NewParameter("@NAME", UnknownArtistName)))
{
if (reader.Read())
{
returnId = reader["LIBRARY_ID"];
}
else
{
tran.CallNonQuery(MediaLibrary.InsertMediaLibrarySql,
Static.Db.NewParameter("@TYPE_ID", MediaLibraryType.Artist),
Static.Db.NewParameter("@MOD_DATE", DateTime.Now),
Static.Db.NewParameter("@PARENT_ID", DBNull.Value));
returnId = tran.CallValue(Static.GetIdentityStatement);
tran.CallNonQuery(MediaLibrary.InsertMediaLibraryPropertySql,
Static.Db.NewParameter("@LIBRARY_ID", returnId),
Static.Db.NewParameter("@TYPE_ID", MediaLibraryPropertyType.Name),
Static.Db.NewParameter("@PROPERTY_VALUE", UnknownArtistName),
Static.Db.NewParameter("@MOD_DATE", DateTime.Now));
}
}
return returnId;
}
public static Int64 CheckTrackInformations(MediaFile file, Int64 albumId, Transaction tran)
{
const String select =
"select a.LIBRARY_ID,a.PARENT_ID, b.PROPERTY_VALUE as NAME, c.PROPERTY_VALUE as URL, c.PROPERTY_VALUE as MD5 from MEDIA_LIBRARY a " +
"left outer join MEDIA_LIBRARY_PROPERTY b on a.LIBRARY_ID = b.LIBRARY_ID and b.TYPE_ID = 1 " +
"left outer join MEDIA_LIBRARY_PROPERTY c on a.LIBRARY_ID = c.LIBRARY_ID and c.TYPE_ID = 2 " +
"left outer join MEDIA_LIBRARY_PROPERTY e on a.LIBRARY_ID = e.LIBRARY_ID and e.TYPE_ID = 3 " +
"where a.TYPE_ID = @ML_TYPE_ID " +
"and b.TYPE_ID = @TYPE_ID and b.PROPERTY_VALUE = @NAME " +
"and c.TYPE_ID = @URL_TYPE ";
Int64 returnId;
using (DataReader reader = tran.CallReader(select,
Static.Db.NewParameter("@ML_TYPE_ID", MediaLibraryType.Track),
Static.Db.NewParameter("@TYPE_ID", MediaLibraryPropertyType.Name),
Static.Db.NewParameter("@URL_TYPE", MediaLibraryPropertyType.FileUrl),
Static.Db.NewParameter("@NAME", file.TitleName)))
{
if (reader.Read())
{
if (albumId != reader["PARENT_ID"])
tran.CallNonQuery(MediaLibrary.UpdateMediaLibrarySql,
Static.Db.NewParameter("@LIBRARY_ID", reader["LIBRARY_ID"]),
Static.Db.NewParameter("@PARENT_ID", albumId),
Static.Db.NewParameter("@MOD_DATE", DateTime.Now));
if (!file.TitleName.Equals((String)reader["NAME"], StringComparison.InvariantCultureIgnoreCase))
tran.CallNonQuery(MediaLibrary.UpdateMediaLibraryPropertySql,
Static.Db.NewParameter("@LIBRARY_ID", reader["LIBRARY_ID"]),
Static.Db.NewParameter("@TYPE_ID", MediaLibraryPropertyType.Name),
Static.Db.NewParameter("@PROPERTY_VALUE", file.TitleName),
Static.Db.NewParameter("@MOD_DATE", DateTime.Now));
if (!file.md5HashString.Equals((String)reader["MD5"], StringComparison.InvariantCultureIgnoreCase))
tran.CallNonQuery(MediaLibrary.UpdateMediaLibraryPropertySql,
Static.Db.NewParameter("@LIBRARY_ID", reader["LIBRARY_ID"]),
Static.Db.NewParameter("@TYPE_ID", MediaLibraryPropertyType.Md5Hash),
Static.Db.NewParameter("@PROPERTY_VALUE", file.md5HashString),
Static.Db.NewParameter("@MOD_DATE", DateTime.Now));
if (!file.PathToFile.Equals((String)reader["URL"], StringComparison.InvariantCultureIgnoreCase))
tran.CallNonQuery(MediaLibrary.UpdateMediaLibraryPropertySql,
Static.Db.NewParameter("@LIBRARY_ID", reader["LIBRARY_ID"]),
Static.Db.NewParameter("@TYPE_ID", MediaLibraryPropertyType.FileUrl),
Static.Db.NewParameter("@PROPERTY_VALUE", file.PathToFile),
Static.Db.NewParameter("@MOD_DATE", DateTime.Now));
returnId = reader["LIBRARY_ID"];
}
else
{
tran.CallNonQuery(MediaLibrary.InsertMediaLibrarySql,
Static.Db.NewParameter("@TYPE_ID", MediaLibraryType.Track),
Static.Db.NewParameter("@MOD_DATE", DateTime.Now),
Static.Db.NewParameter("@PARENT_ID", albumId));
returnId = tran.CallValue(Static.GetIdentityStatement);
tran.CallNonQuery(MediaLibrary.InsertMediaLibraryPropertySql,
Static.Db.NewParameter("@LIBRARY_ID", returnId),
Static.Db.NewParameter("@TYPE_ID", MediaLibraryPropertyType.Name),
Static.Db.NewParameter("@PROPERTY_VALUE", String.IsNullOrEmpty(file.TitleName)? UnknownTrackName: file.TitleName),
Static.Db.NewParameter("@MOD_DATE", DateTime.Now));
tran.CallNonQuery(MediaLibrary.InsertMediaLibraryPropertySql,
Static.Db.NewParameter("@LIBRARY_ID", returnId),
Static.Db.NewParameter("@TYPE_ID", MediaLibraryPropertyType.FileUrl),
Static.Db.NewParameter("@PROPERTY_VALUE", file.PathToFile),
Static.Db.NewParameter("@MOD_DATE", DateTime.Now));
}
}
checkImageInformations(file, returnId, tran);
return returnId;
}