private async Task <DbOperationResult> ResetIssueStatus_R(int id)
{
var stopWatch = new Stopwatch();
stopWatch.Start();
try
{
using (var conn = DbConnection())
{
conn.Open();
id = (int)conn.Query <long>(
@"UPDATE MusicFiles
SET Error = 0,
ErrorReason = '',
UploadAttempts = 0,
LastUploadError = '0001-01-01 00:00:00',
LastUpload = '0001-01-01 00:00:00'
WHERE Id = @id;
SELECT last_insert_rowid()",
new { id }).First();
conn.Close();
}
stopWatch.Stop();
return(await Task.FromResult(DbOperationResult.Success(id, stopWatch.Elapsed)));
}
catch (Exception e)
{
stopWatch.Stop();
return(await Task.FromResult(DbOperationResult.Fail(e.Message, stopWatch.Elapsed)));
}
}
private async Task <DbOperationResult> Update_R(PlaylistFile playlistFile)
{
var stopWatch = new Stopwatch();
stopWatch.Start();
try
{
using (var conn = DbConnection())
{
conn.Open();
playlistFile.Id = (int)conn.Query <long>(
@"UPDATE PlaylistFiles
SET Title = @Title,
Description = @Description,
PlaylistId = @PlaylistId,
Path = @Path,
LastModifiedDate = @LastModifiedDate,
LastUpload = @LastUpload
WHERE Id = @Id;
SELECT last_insert_rowid()",
playlistFile).First();
conn.Close();
}
stopWatch.Stop();
return(await Task.FromResult(DbOperationResult.Success(playlistFile.Id, stopWatch.Elapsed)));
}
catch (Exception e)
{
stopWatch.Stop();
return(await Task.FromResult(DbOperationResult.Fail(e.Message, stopWatch.Elapsed)));
}
}
/// <summary>
/// Deletes logs older than a particular date from the databae
/// </summary>
/// <param name="dateTime">Delete before date</param>
/// <returns>DbOperationResult - Showing success or fail, with messages and stats</returns>
public async Task <DbOperationResult> DeleteOlderThan(DateTime dateTime)
{
var stopWatch = new Stopwatch();
stopWatch.Start();
try
{
using (var conn = DbConnection())
{
conn.Open();
conn.Execute(string.Format(
@"DELETE FROM Logs
WHERE Event < '{0}'",
dateTime.ToSQLDateTime()));
conn.Close();
}
stopWatch.Stop();
return(await Task.FromResult(DbOperationResult.Success(1, stopWatch.Elapsed)));
}
catch (Exception e)
{
stopWatch.Stop();
return(await Task.FromResult(DbOperationResult.Fail(e.Message, stopWatch.Elapsed)));
}
}
private async Task <DbOperationResult> Update_R(Settings settings)
{
var stopWatch = new Stopwatch();
stopWatch.Start();
try
{
using (var conn = DbConnection())
{
conn.Open();
conn.Execute(
@"UPDATE Settings
SET StartWithWindows = @StartWithWindows,
ThrottleSpeed = @ThrottleSpeed,
AuthenticationCookie = @AuthenticationCookie,
SendLogsToSource = @SendLogsToSource,
UploadPlaylists = @UploadPlaylists,
LastPlaylistUpload = @LastPlaylistUpload,
CurrentSessionPlaylistUploadCount = @CurrentSessionPlaylistUploadCount
WHERE Id = @Id",
settings);
conn.Close();
}
stopWatch.Stop();
return(await Task.FromResult(DbOperationResult.Success(1, stopWatch.Elapsed)));
}
catch (Exception e)
{
stopWatch.Stop();
return(await Task.FromResult(DbOperationResult.Fail(e.Message, stopWatch.Elapsed)));
}
}
private async Task <DbOperationResult> DeleteAll_R()
{
var stopWatch = new Stopwatch();
stopWatch.Start();
try
{
using (var conn = DbConnection())
{
conn.Open();
conn.Execute(
@"UPDATE MusicFiles
SET Removed = 1");
conn.Close();
}
stopWatch.Stop();
return(await Task.FromResult(DbOperationResult.Success(-1, stopWatch.Elapsed)));
}
catch (Exception e)
{
stopWatch.Stop();
return(await Task.FromResult(DbOperationResult.Fail(e.Message, stopWatch.Elapsed)));
}
}
/// <summary>
/// Performs a bulk delete of Music File entries who's path starts with a certain file path. I.e.
/// When bulk deleting because of the removal of a library watch folder
/// </summary>
/// <param name="path">Beginning folder path to filter for</param>
/// <returns>DbOperationResult - Showing success or fail, with messages and stats</returns>
public async Task <DbOperationResult> DeleteWatchFolder(string path)
{
Stopwatch stopWatch = new Stopwatch();
stopWatch.Start();
try
{
using (var conn = DbConnection())
{
conn.Open();
conn.Execute(
@"UPDATE MusicFiles
SET Removed = 1
WHERE Path LIKE @Path + '%'",
new { path });
}
stopWatch.Stop();
return(await Task.FromResult(DbOperationResult.Success(-1, stopWatch.Elapsed)));
}
catch (Exception e)
{
stopWatch.Stop();
return(await Task.FromResult(DbOperationResult.Fail(e.Message, stopWatch.Elapsed)));
}
}
private async Task <DbOperationResult> DeleteByEntityId_R(string entityId)
{
var stopWatch = new Stopwatch();
stopWatch.Start();
try
{
using (var conn = DbConnection())
{
conn.Open();
conn.Execute(
@"DELETE FROM MusicFiles
WHERE EntityID = @EntityId",
new { entityId });
conn.Close();
}
stopWatch.Stop();
return(await Task.FromResult(DbOperationResult.Success(-1, stopWatch.Elapsed)));
}
catch (Exception e)
{
stopWatch.Stop();
return(await Task.FromResult(DbOperationResult.Fail(e.Message, stopWatch.Elapsed)));
}
}
private async Task <DbOperationResult> Delete_R(int id)
{
var stopWatch = new Stopwatch();
stopWatch.Start();
try
{
using (var conn = DbConnection())
{
conn.Open();
conn.Execute(
@"DELETE FROM WatchFolders
WHERE Id = @Id",
new { id });
conn.Close();
}
stopWatch.Stop();
return(await Task.FromResult(DbOperationResult.Success(-1, stopWatch.Elapsed)));
}
catch (Exception e)
{
stopWatch.Stop();
return(await Task.FromResult(DbOperationResult.Fail(e.Message, stopWatch.Elapsed)));
}
}
/// <summary>
/// Updates the application settings data in the database
/// </summary>
/// <param name="settings">Settings model object</param>
/// <returns>DbOperationResult - Showing success or fail, with messages and stats</returns>
public async Task <DbOperationResult> Update(Settings settings)
{
Stopwatch stopWatch = new Stopwatch();
stopWatch.Start();
try
{
using (var conn = DbConnection())
{
conn.Open();
conn.Execute(
@"UPDATE Settings
SET StartWithWindows = @StartWithWindows,
ThrottleSpeed = @ThrottleSpeed,
AuthenticationCookie = @AuthenticationCookie
WHERE Id = @Id",
settings);
}
stopWatch.Stop();
return(await Task.FromResult(DbOperationResult.Success(1, stopWatch.Elapsed)));
}
catch (Exception e)
{
stopWatch.Stop();
return(await Task.FromResult(DbOperationResult.Fail(e.Message, stopWatch.Elapsed)));
}
}
private async Task <DbOperationResult> DeleteFromPath_R(string path)
{
var stopWatch = new Stopwatch();
stopWatch.Start();
try
{
using (var conn = DbConnection())
{
conn.Open();
conn.Execute(
@"DELETE FROM PlaylistFile
WHERE Path = @Path",
new { path });
conn.Close();
}
stopWatch.Stop();
return(await Task.FromResult(DbOperationResult.Success(-1, stopWatch.Elapsed)));
}
catch (Exception e)
{
stopWatch.Stop();
return(await Task.FromResult(DbOperationResult.Fail(e.Message, stopWatch.Elapsed)));
}
}
private async Task <DbOperationResult> ResetIssueStatusAll_R()
{
var stopWatch = new Stopwatch();
stopWatch.Start();
try
{
using (var conn = DbConnection())
{
conn.Open();
conn.Execute(
@"UPDATE MusicFiles
SET Error = 0,
ErrorReason = '',
UploadAttempts = 0,
LastUploadError = '0001-01-01 00:00:00',
LastUpload = '0001-01-01 00:00:00'");
conn.Close();
}
stopWatch.Stop();
return(await Task.FromResult(DbOperationResult.Success(-1, stopWatch.Elapsed)));
}
catch (Exception e)
{
stopWatch.Stop();
return(await Task.FromResult(DbOperationResult.Fail(e.Message, stopWatch.Elapsed)));
}
}
/// <summary>
/// Deletes a library Watch Folder entry from the database of a given WatchFolder database full directory path
/// </summary>
/// <param name="watchFolder">The given WatchFolder full directory path</param>
/// <returns>DbOperationResult - Showing success or fail, with messages and stats</returns>
public async Task <DbOperationResult> Delete(string path)
{
Stopwatch stopWatch = new Stopwatch();
stopWatch.Start();
try
{
using (var conn = DbConnection())
{
conn.Open();
conn.Execute(
@"DELETE FROM WatchFolders
WHERE Path = @Path",
new { path });
}
stopWatch.Stop();
return(await Task.FromResult(DbOperationResult.Success(-1, stopWatch.Elapsed)));
}
catch (Exception e)
{
stopWatch.Stop();
return(await Task.FromResult(DbOperationResult.Fail(e.Message, stopWatch.Elapsed)));
}
}
private async Task <DbOperationResult> Insert_R(MusicFile musicFile)
{
var stopWatch = new Stopwatch();
stopWatch.Start();
try
{
int existingMusicFileId = await GetMusicFileIdFromPath(musicFile.Path);
if (existingMusicFileId == -1)
{
using (var conn = DbConnection())
{
conn.Open();
musicFile.Id = (int)conn.Query <long>(
@"INSERT
INTO MusicFiles (
Path,
Hash,
MbId,
ReleaseMbId,
EntityId,
VideoId,
LastUpload,
Error,
ErrorReason,
UploadAttempts,
LastUploadError)
VALUES (@Path,
@Hash,
@MbId,
@ReleaseMbId,
@EntityId,
@VideoId,
@LastUpload,
@Error,
@ErrorReason,
@UploadAttempts,
@LastUploadError);
SELECT last_insert_rowid()",
musicFile).First();
conn.Close();
}
}
else
{
await RestoreMusicFile(existingMusicFileId);
}
stopWatch.Stop();
return(await Task.FromResult(DbOperationResult.Success(musicFile.Id, stopWatch.Elapsed)));
}
catch (Exception e)
{
stopWatch.Stop();
return(await Task.FromResult(DbOperationResult.Fail(e.Message, stopWatch.Elapsed)));
}
}
private async Task <DbOperationResult> Insert_R(PlaylistFile playlistFile)
{
var stopWatch = new Stopwatch();
stopWatch.Start();
try
{
using (var conn = DbConnection())
{
conn.Open();
playlistFile.Id = (int)conn.Query <long>(
@"INSERT
INTO PlaylisFiles (
Title,
Description,
PlaylistId,
Path,
LastModifiedDate,
LastUpload)
VALUES @Title,
@Description,
@PlaylistId,
@Path,
@LastModifiedDate,
@LastUpload);
SELECT last_insert_rowid()",
playlistFile).First();
conn.Close();
}
stopWatch.Stop();
return(await Task.FromResult(DbOperationResult.Success(playlistFile.Id, stopWatch.Elapsed)));
}
catch (Exception e)
{
stopWatch.Stop();
return(await Task.FromResult(DbOperationResult.Fail(e.Message, stopWatch.Elapsed)));
}
}
private async Task <DbOperationResult> Update_R(MusicFile musicFile)
{
var stopWatch = new Stopwatch();
stopWatch.Start();
try
{
using (var conn = DbConnection())
{
conn.Open();
musicFile.Id = (int)conn.Query <long>(
@"UPDATE MusicFiles
SET Path = @Path,
Hash = @Hash,
MbId = @MbId,
ReleaseMbId = @ReleaseMbId,
EntityId = @EntityId,
VideoId = @VideoId,
LastUpload = @LastUpload,
Error = @Error,
ErrorReason = @ErrorReason,
Removed = @Removed,
UploadAttempts = @UploadAttempts,
LastUploadError = @LastUploadError
WHERE Id = @Id;
SELECT last_insert_rowid()",
musicFile).First();
conn.Close();
}
stopWatch.Stop();
return(await Task.FromResult(DbOperationResult.Success(musicFile.Id, stopWatch.Elapsed)));
}
catch (Exception e)
{
stopWatch.Stop();
return(await Task.FromResult(DbOperationResult.Fail(e.Message, stopWatch.Elapsed)));
}
}
private async Task <DbOperationResult> Insert_R(WatchFolder watchFolder)
{
var stopWatch = new Stopwatch();
stopWatch.Start();
try
{
int existingMusicFileId = GetWatchFolderIdFromPath(watchFolder.Path).Result;
if (existingMusicFileId == -1)
{
using (var conn = DbConnection())
{
conn.Open();
watchFolder.Id = (int)conn.Query <long>(
@"INSERT
INTO WatchFolders (
Path)
VALUES (@Path);
SELECT last_insert_rowid()",
watchFolder).First();
conn.Close();
}
}
else
{
stopWatch.Stop();
return(await Task.FromResult(DbOperationResult.Success(existingMusicFileId, stopWatch.Elapsed)));
}
stopWatch.Stop();
return(await Task.FromResult(DbOperationResult.Success(watchFolder.Id, stopWatch.Elapsed)));
}
catch (Exception e)
{
stopWatch.Stop();
return(await Task.FromResult(DbOperationResult.Fail(e.Message, stopWatch.Elapsed)));
}
}
private async Task <DbOperationResult> Delete_R(int id, bool destroy = false)
{
var stopWatch = new Stopwatch();
stopWatch.Start();
try
{
using (var conn = DbConnection())
{
conn.Open();
if (destroy)
{
conn.Execute(
@"DELETE
FROM MusicFiles
WHERE Id = @Id",
new { id });
}
else
{
conn.Execute(
@"UPDATE MusicFiles
SET Removed = 1
WHERE Id = @Id",
new { id });
}
conn.Close();
}
stopWatch.Stop();
return(await Task.FromResult(DbOperationResult.Success(-1, stopWatch.Elapsed)));
}
catch (Exception e)
{
stopWatch.Stop();
return(await Task.FromResult(DbOperationResult.Fail(e.Message, stopWatch.Elapsed)));
}
}
/// <summary>
/// Adds the log to the databse
/// </summary>
/// <param name="log">Log object to record</param>
/// <returns>DbOperationResult - Showing success or fail, with messages and stats</returns>
public async Task <DbOperationResult> Add(Log log)
{
try
{
using (var conn = DbConnection())
{
conn.Open();
conn.Execute(
@"INSERT
INTO Logs (
Event,
LogTypeId,
Machine,
Source,
Message,
Version,
StackTrace)
VALUES (@Event,
@LogTypeId,
@Machine,
@Source,
@Message,
@Version,
@StackTrace);
SELECT last_insert_rowid()",
log);
conn.Close();
}
return(await Task.FromResult(DbOperationResult.Success(-1, new TimeSpan(0))));
}
catch (Exception e)
{
return(await Task.FromResult(DbOperationResult.Fail(e.Message, new TimeSpan(0))));
}
}
