public string CalculateHash()
{
StringBuilder itemIds = new StringBuilder();
ISQLiteConnection conn = null;
try
{
conn = Injection.Kernel.Get <IDatabase>().GetSqliteConnection();
var result = conn.DeferredQuery <PlaylistItem>("SELECT ItemId FROM PlaylistItem WHERE PlaylistId = ?", PlaylistId);
foreach (PlaylistItem playlistItem in result)
{
itemIds.Append(playlistItem.ItemId);
itemIds.Append("|");
}
}
catch (Exception e)
{
logger.Error(e);
}
finally
{
Injection.Kernel.Get <IDatabase>().CloseSqliteConnection(conn);
}
return(itemIds.ToString().MD5().Replace("-", string.Empty));
}
public IList <IMediaItem> ListOfMediaItems()
{
ISQLiteConnection conn = null;
try
{
conn = Injection.Kernel.Get <IDatabase>().GetSqliteConnection();
var result = conn.DeferredQuery <PlaylistItem>("SELECT * FROM PlaylistItem WHERE PlaylistId = ? ORDER BY ItemPosition", PlaylistId);
IList <IMediaItem> items = new List <IMediaItem>();
foreach (PlaylistItem playlistItem in result)
{
if (!ReferenceEquals(playlistItem.ItemId, null))
{
IMediaItem item = Injection.Kernel.Get <IMediaItemRepository>().MediaItemForId((int)playlistItem.ItemId);
if (!ReferenceEquals(item, null))
{
items.Add(item);
}
}
}
return(items);
}
catch (Exception e)
{
logger.Error(e);
}
finally
{
Injection.Kernel.Get <IDatabase>().CloseSqliteConnection(conn);
}
return(new List <IMediaItem>());
}
public IMediaItem MediaItemAtIndex(int index)
{
ISQLiteConnection conn = null;
try
{
conn = Injection.Kernel.Get <IDatabase>().GetSqliteConnection();
var result = conn.DeferredQuery <PlaylistItem>("SELECT * FROM PlaylistItem WHERE PlaylistId = ? AND ItemPosition = ? LIMIT 1", PlaylistId, index);
foreach (PlaylistItem playlistItem in result)
{
switch (playlistItem.ItemType)
{
case ItemType.Song:
return(Injection.Kernel.Get <ISongRepository>().SongForId((int)playlistItem.ItemId));
case ItemType.Video:
return(Injection.Kernel.Get <IVideoRepository>().VideoForId((int)playlistItem.ItemId));
default:
break;
}
}
}
catch (Exception e)
{
logger.Error(e);
}
finally
{
Injection.Kernel.Get <IDatabase>().CloseSqliteConnection(conn);
}
return(new MediaItem());
}
// Note: this extension class exists so that we may provide more concise methods for accessing objects
// using simple database queries, without having to complicate the interface.
// Retrieve a single object of type T using query string and prepared arguments
public static T GetSingle <T>(this IDatabase database, string query, params object[] args) where T : new()
{
ISQLiteConnection conn = null;
try
{
// Get database connection, use query to generate object of type T
conn = database.GetSqliteConnection();
var result = conn.DeferredQuery <T>(query, args);
// If result found, return it
foreach (T obj in result)
{
return(obj);
}
}
catch (Exception e)
{
logger.Error("query: " + query);
logger.Error(e);
}
finally
{
database.CloseSqliteConnection(conn);
}
// If no result, return blank instance
return(new T());
}
private bool VideoNeedsUpdating(string filePath, int?folderId, out bool isNew, out int?itemId)
{
string fileName = Path.GetFileName(filePath);
long lastModified = System.IO.File.GetLastWriteTime(filePath).ToUnixTime();
bool needsUpdating = true;
isNew = true;
itemId = null;
ISQLiteConnection conn = null;
try {
conn = Injection.Kernel.Get <IDatabase>().GetSqliteConnection();
var result = conn.DeferredQuery <Video>("SELECT * FROM Video WHERE FolderId = ? AND FileName = ?", folderId, fileName);
foreach (Video video in result)
{
isNew = false;
itemId = video.ItemId;
if (video.LastModified == lastModified)
{
needsUpdating = false;
}
break;
}
} catch (Exception e) {
logger.Error(e);
} finally {
Injection.Kernel.Get <IDatabase>().CloseSqliteConnection(conn);
}
return(needsUpdating);
}
private void CheckSongs()
{
if (isRestart)
{
return;
}
ArrayList orphanSongIds = new ArrayList();
ISQLiteConnection conn = null;
try
{
conn = Injection.Kernel.Get <IDatabase>().GetSqliteConnection();
// Find the orphaned songs
var result = conn.DeferredQuery <Song>("SELECT * FROM Song");
foreach (Song song in result)
{
long timestamp = DateTime.UtcNow.ToUnixTime();
bool exists = File.Exists(song.FilePath());
totalExistsTime += DateTime.UtcNow.ToUnixTime() - timestamp;
if (!exists)
{
orphanSongIds.Add(song.ItemId);
}
}
// Remove them
foreach (int itemId in orphanSongIds)
{
try
{
conn.ExecuteLogged("DELETE FROM Song WHERE ItemId = ?", itemId);
logger.IfInfo("Song " + itemId + " deleted");
}
catch (Exception e)
{
logger.Error("Failed deleting orphan songs : " + e);
}
}
}
catch (Exception e)
{
logger.Error("Failed checking for orphan songs " + e);
}
finally
{
Injection.Kernel.Get <IDatabase>().CloseSqliteConnection(conn);
}
}
private void CheckVideos()
{
if (isRestart)
{
return;
}
ArrayList orphanVideoIds = new ArrayList();
ISQLiteConnection conn = null;
try
{
// Check for videos which don't have a folder path, meaning that they're orphaned
conn = Injection.Kernel.Get <IDatabase>().GetSqliteConnection();
var result = conn.DeferredQuery <Video>("SELECT Video.ItemId FROM Video " +
"LEFT JOIN Folder ON Video.FolderId = Folder.FolderId " +
"WHERE Folder.FolderPath IS NULL");
foreach (Video video in result)
{
orphanVideoIds.Add(video.ItemId);
}
// Remove them
foreach (int itemId in orphanVideoIds)
{
try
{
conn.ExecuteLogged("DELETE FROM Video WHERE ItemId = ?", itemId);
logger.IfInfo(" - Video " + itemId + " deleted");
}
catch (Exception e)
{
logger.Error("Failed deleting orphan videos : " + e);
}
}
}
catch (Exception e)
{
logger.Error("Failed checking for orphan videos " + e);
}
finally
{
Injection.Kernel.Get <IDatabase>().CloseSqliteConnection(conn);
}
}
private void CheckAlbumArtists()
{
if (isRestart)
{
return;
}
ArrayList orphanAlbumArtistIds = new ArrayList();
ISQLiteConnection conn = null;
try
{
conn = Injection.Kernel.Get <IDatabase>().GetSqliteConnection();
// Find the orphaned album artists
var result = conn.DeferredQuery <AlbumArtist>("SELECT AlbumArtist.AlbumArtistId FROM AlbumArtist " +
"LEFT JOIN Song ON AlbumArtist.AlbumArtistId = Song.AlbumArtistId " +
"WHERE Song.AlbumArtistId IS NULL");
foreach (AlbumArtist albumArtist in result)
{
orphanAlbumArtistIds.Add(albumArtist.AlbumArtistId);
}
// Remove them
foreach (int albumArtistId in orphanAlbumArtistIds)
{
try
{
conn.ExecuteLogged("DELETE FROM AlbumArtist WHERE AlbumArtistId = ?", albumArtistId);
logger.IfInfo("AlbumArtist " + albumArtistId + " deleted");
}
catch (Exception e)
{
logger.Error("Failed deleting orphan album artists" + e);
}
}
}
catch (Exception e)
{
logger.Error("Failed checking for orphan album artists : " + e);
}
finally
{
Injection.Kernel.Get <IDatabase>().CloseSqliteConnection(conn);
}
}
private void CheckGenres()
{
if (isRestart)
{
return;
}
ArrayList orphanGenreIds = new ArrayList();
ISQLiteConnection conn = null;
try
{
// Find orphaned genres
conn = Injection.Kernel.Get <IDatabase>().GetSqliteConnection();
var result = conn.DeferredQuery <Genre>("SELECT Genre.GenreId FROM Genre " +
"LEFT JOIN Song ON Genre.GenreId = Song.GenreId " +
"WHERE Song.GenreId IS NULL");
foreach (Genre genre in result)
{
orphanGenreIds.Add(genre.GenreId);
}
// Remove them
foreach (int genreId in orphanGenreIds)
{
try
{
conn.ExecuteLogged("DELETE FROM Genre WHERE GenreId = ?", genreId);
logger.IfInfo("Genre " + genreId + " deleted");
}
catch (Exception e)
{
logger.Error("Failed deleting orphan genre " + genreId + ": " + e);
}
}
}
catch (Exception e)
{
logger.Error("Failed checking for orphan genres: " + e);
}
finally
{
Injection.Kernel.Get <IDatabase>().CloseSqliteConnection(conn);
}
}
private void CheckFolders()
{
if (isRestart)
{
return;
}
ArrayList mediaFolderIds = new ArrayList();
ArrayList orphanFolderIds = new ArrayList();
foreach (Folder mediaFolder in Injection.Kernel.Get <IFolderRepository>().MediaFolders())
{
mediaFolderIds.Add(mediaFolder.FolderId);
}
ISQLiteConnection conn = null;
try
{
conn = Injection.Kernel.Get <IDatabase>().GetSqliteConnection();
// Find the orphaned folders
var result = conn.DeferredQuery <Folder>("SELECT * FROM Folder");
foreach (Folder folder in result)
{
if (folder.MediaFolderId != null)
{
if (!mediaFolderIds.Contains(folder.MediaFolderId) || !Directory.Exists(folder.FolderPath))
{
logger.IfInfo(folder.FolderId + " is orphaned");
orphanFolderIds.Add(folder.FolderId);
}
}
// Alternatively, if folder was or is a root media folder, it won't have a media folder ID.
else
{
// Check if it's in the list of root media folders. If not, it's an orphan
bool success = false;
foreach (Folder f in Injection.Kernel.Get <IFolderRepository>().MediaFolders())
{
if (f.FolderPath == folder.FolderPath)
{
success = true;
break;
}
}
// Add any orphan folders to purge list
if (!success)
{
orphanFolderIds.Add(folder.FolderId);
}
}
}
// Remove them
foreach (int folderId in orphanFolderIds)
{
try
{
conn.ExecuteLogged("DELETE FROM Folder WHERE FolderId = ?", folderId);
logger.IfInfo(" - Folder " + folderId + " deleted");
}
catch (Exception e)
{
logger.Error("Failed to delete orphan " + folderId + " : " + e);
}
try
{
conn.ExecuteLogged("DELETE FROM Song WHERE FolderId = ?", folderId);
}
catch (Exception e)
{
logger.Error("Failed to delete songs for orphan " + folderId + " : " + e);
}
}
}
catch (Exception e)
{
logger.Error("Failed to delete orphan items : " + e);
}
finally
{
Injection.Kernel.Get <IDatabase>().CloseSqliteConnection(conn);
}
}