public void RemoveMediaItemAtIndex(int index)
{
if (PlaylistId == 0)
{
return;
}
ISQLiteConnection conn = null;
try
{
conn = Injection.Kernel.Get <IDatabase>().GetSqliteConnection();
conn.BeginTransaction();
conn.ExecuteLogged("DELETE FROM PlaylistItem WHERE PlaylistId = ? AND ItemPosition = ?", PlaylistId, index);
conn.ExecuteLogged("UPDATE PlaylistItem SET ItemPosition = ItemPosition - 1 WHERE PlaylistId = ? AND ItemPosition > ?", PlaylistId, index);
conn.Commit();
}
catch (Exception e)
{
if (!ReferenceEquals(conn, null))
{
conn.Rollback();
}
logger.Error(e);
}
finally
{
Injection.Kernel.Get <IDatabase>().CloseSqliteConnection(conn);
}
}
public void MoveMediaItem(int fromIndex, int toIndex)
{
// make sure the input is within bounds and is not null
if (fromIndex >= PlaylistCount || fromIndex < 0 ||
toIndex < 0 || toIndex == fromIndex)
{
return;
}
logger.IfInfo("Moving media item");
ISQLiteConnection conn = null;
try {
// to do - better way of knowing whether or not a query has been successfully completed.
conn = Injection.Kernel.Get <IDatabase>().GetSqliteConnection();
conn.BeginTransaction();
// Get the item out of the way to prevent constraint violations
conn.ExecuteLogged("UPDATE PlaylistItem SET ItemPosition = " + Int32.MaxValue + " WHERE PlaylistId = ? AND ItemPosition = ?", PlaylistId, fromIndex);
if (fromIndex > toIndex)
{
// Do this as a reversed loop because a single update statement can have a constraint violation
for (int position = fromIndex - 1; position >= toIndex; position--)
{
logger.IfInfo("Updating position " + position + " to " + (position + 1));
conn.ExecuteLogged("UPDATE PlaylistItem SET ItemPosition = ItemPosition + 1 WHERE PlaylistId = ? AND ItemPosition = ?", PlaylistId, position);
}
// conditional rollback here
}
else
{
// Do this as a reversed loop because a single update statement can have a constraint violation
for (int position = fromIndex + 1; position <= toIndex; position++)
{
logger.IfInfo("Updating position " + position + " to " + (position + 1));
conn.ExecuteLogged("UPDATE PlaylistItem SET ItemPosition = ItemPosition - 1 WHERE PlaylistId = ? AND ItemPosition = ?", PlaylistId, position);
}
// conditional rollback here
}
conn.ExecuteLogged("UPDATE PlaylistItem SET ItemPosition = ? WHERE PlaylistId = ? AND ItemPosition = ?", toIndex, PlaylistId, Int32.MaxValue);
// conditional rollback here
conn.Commit();
} catch (Exception e) {
if (!ReferenceEquals(conn, null))
{
conn.Rollback();
}
logger.Error(e);
} finally {
Injection.Kernel.Get <IDatabase>().CloseSqliteConnection(conn);
}
}
public void DeletePlaylist()
{
if (ReferenceEquals(PlaylistId, null))
{
return;
}
ISQLiteConnection conn = null;
try
{
// Delete the entry from the Playlist table
conn = Injection.Kernel.Get <IDatabase>().GetSqliteConnection();
conn.ExecuteLogged("DELETE FROM Playlist WHERE PlaylistId = ?", PlaylistId);
// Clear the playlist items
ClearPlaylist();
}
catch (Exception e)
{
logger.Error(e);
}
finally
{
Injection.Kernel.Get <IDatabase>().CloseSqliteConnection(conn);
}
}
// Execute a query on the database, with optional logging
private static int InternalExecuteQuery(this IDatabase database, bool logging, string query, params object[] args)
{
ISQLiteConnection conn = null;
try {
// Get database connection, execute and log query
conn = database.GetSqliteConnection();
if (logging)
{
return(conn.ExecuteLogged(query, args));
}
else
{
return(conn.Execute(query, args));
}
} catch (Exception e) {
logger.Error("execute failed: " + query);
logger.Error(e);
} finally {
database.CloseSqliteConnection(conn);
}
// Return 0 on exception, no rows affected
return(0);
}
public int?GenerateItemId(ItemType itemType)
{
ISQLiteConnection conn = null;
try {
conn = database.GetSqliteConnection();
int affected = conn.ExecuteLogged("INSERT INTO Item (ItemType, Timestamp) VALUES (?, ?)", itemType, DateTime.UtcNow.ToUnixTime());
if (affected >= 1)
{
try {
int rowId = conn.ExecuteScalar <int>("SELECT last_insert_rowid()");
if (rowId != 0)
{
return(rowId);
}
} catch (Exception e) {
logger.Error(e);
}
}
} catch (Exception e) {
logger.Error("GenerateItemId ERROR: ", e);
} finally {
database.CloseSqliteConnection(conn);
}
return(null);
}
public void RemoveMediaItemAtIndexes(IList <int> indices)
{
ISQLiteConnection conn = null;
try
{
conn = Injection.Kernel.Get <IDatabase>().GetSqliteConnection();
conn.BeginTransaction();
// delete the items at the indicated indices
foreach (int index in indices)
{
logger.IfInfo("Deleting row at ItemPosition: " + index);
conn.ExecuteLogged("DELETE FROM PlaylistItem WHERE PlaylistId = ? AND ItemPosition = ?", PlaylistId, index);
}
// select the id of all members of the playlist
var result = conn.Query <PlaylistItem>("SELECT * FROM PlaylistItem WHERE PlaylistId = ? ORDER BY ItemPosition", PlaylistId);
// update the values of each index in the array to be the new index
for (int i = 0; i < result.Count; i++)
{
var item = result[i];
conn.ExecuteLogged("UPDATE PlaylistItem SET ItemPosition = ? WHERE PlaylistItemId = ? AND PlaylistId = ?", i, item.PlaylistItemId, PlaylistId);
}
conn.Commit();
}
catch (Exception e)
{
if (!ReferenceEquals(conn, null))
{
conn.Rollback();
}
logger.Error(e);
}
finally
{
Injection.Kernel.Get <IDatabase>().CloseSqliteConnection(conn);
}
}
public void UpdateDatabase()
{
ISQLiteConnection conn = null;
try
{
conn = Injection.Kernel.Get <IDatabase>().GetSqliteConnection();
if (PlaylistId == null)
{
int?itemId = Injection.Kernel.Get <IItemRepository>().GenerateItemId(ItemType.Playlist);
if (itemId == null)
{
return;
}
PlaylistId = itemId;
PlaylistCount = 0;
PlaylistDuration = 0;
LastUpdateTime = DateTime.UtcNow.ToUnixTime();
Md5Hash = CalculateHash();
conn.ExecuteLogged("INSERT INTO Playlist (PlaylistId, PlaylistName, PlaylistCount, PlaylistDuration, Md5Hash, LastUpdateTime) " +
"VALUES (?, ?, ?, ?, ?, ?)", PlaylistId, PlaylistName == null ? "" : PlaylistName, PlaylistCount, PlaylistDuration, Md5Hash, LastUpdateTime);
}
else
{
LastUpdateTime = DateTime.UtcNow.ToUnixTime();
Md5Hash = CalculateHash();
conn.ExecuteLogged("UPDATE Playlist SET PlaylistName = ?, PlaylistCount = ?, PlaylistDuration = ?, Md5Hash = ?, LastUpdateTime = ? " +
"WHERE PlaylistId = ?", PlaylistName == null ? "" : PlaylistName, PlaylistCount, PlaylistDuration, Md5Hash, LastUpdateTime, PlaylistId);
}
}
catch (Exception e)
{
logger.Error(e);
}
finally
{
Injection.Kernel.Get <IDatabase>().CloseSqliteConnection(conn);
}
}
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);
}
}
public void ClearPlaylist()
{
ISQLiteConnection conn = null;
try {
conn = Injection.Kernel.Get <IDatabase>().GetSqliteConnection();
conn.ExecuteLogged("DELETE FROM PlaylistItem WHERE PlaylistId = ?", PlaylistId);
} catch (Exception e) {
logger.Error(e);
} finally {
Injection.Kernel.Get <IDatabase>().CloseSqliteConnection(conn);
}
PlaylistCount = 0;
PlaylistDuration = 0;
UpdateDatabase();
}
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);
}
}
public void InsertMediaItem(IMediaItem item, int index)
{
// make sure the input is within bounds and is not null
if (ReferenceEquals(item, null) || index > PlaylistCount || index < 0 || ReferenceEquals(PlaylistId, null))
{
return;
}
ISQLiteConnection conn = null;
try
{
int?id = Injection.Kernel.Get <IItemRepository>().GenerateItemId(ItemType.PlaylistItem);
if (!ReferenceEquals(id, null))
{
// to do - better way of knowing whether or not a query has been successfully completed.
conn = Injection.Kernel.Get <IDatabase>().GetSqliteConnection();
conn.BeginTransaction();
for (int position = (int)PlaylistCount - 1; position >= index; position--)
{
logger.IfInfo("Updating position " + position + " to " + (position + 1));
conn.ExecuteLogged("UPDATE PlaylistItem SET ItemPosition = ItemPosition + 1 WHERE PlaylistId = ? AND ItemPosition = ?", PlaylistId, position);
}
// conditional rollback here
// Insert the new item
var playlistItem = new PlaylistItem();
playlistItem.PlaylistItemId = id;
playlistItem.PlaylistId = PlaylistId;
playlistItem.ItemType = item.ItemType;
playlistItem.ItemId = item.ItemId;
playlistItem.ItemPosition = index;
int affected = conn.Insert(playlistItem);
// conditional rollback here
if (affected > 0)
{
PlaylistCount++;
PlaylistDuration += (int)item.Duration;
LastUpdateTime = DateTime.UtcNow.ToUnixTime();
Md5Hash = CalculateHash();
conn.ExecuteLogged("UPDATE Playlist SET PlaylistName = ?, PlaylistCount = ?, PlaylistDuration = ?, Md5Hash = ?, LastUpdateTime = ? " +
"WHERE PlaylistId = ?", PlaylistName == null ? "" : PlaylistName, PlaylistCount, PlaylistDuration, Md5Hash, LastUpdateTime, PlaylistId);
conn.Commit();
}
else
{
conn.Rollback();
}
}
}
catch (Exception e)
{
if (!ReferenceEquals(conn, null))
{
conn.Rollback();
}
logger.Error(e);
}
finally
{
Injection.Kernel.Get <IDatabase>().CloseSqliteConnection(conn);
}
}