Horizon – Rev 11
?pathlinks?
using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Drawing;
using System.Drawing.Imaging;
using System.Globalization;
using System.IO;
using System.IO.Compression;
using System.Runtime.CompilerServices;
using System.Security.Cryptography;
using System.Threading;
using System.Threading.Tasks;
using Horizon.Snapshots;
using Horizon.Utilities;
using Serilog;
namespace Horizon.Database
{
public class SnapshotDatabase : IDisposable
{
#region Static Fields and Constants
private const string CreateTableSql =
"CREATE TABLE IF NOT EXISTS \"Snapshots\" ( \"id\" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, \"Name\" TEXT NOT NULL, \"Path\" TEXT NOT NULL, \"Time\" TEXT NOT NULL, \"Hash\" TEXT NOT NULL, \"Data\" BLOB, \"Color\" INTEGER, \"Shot\" BLOB, \"Note\" TEXT, UNIQUE (\"Hash\") ON CONFLICT FAIL)";
private const string SetAutoVacuumSql = "PRAGMA auto_vacuum = FULL";
private const string SnapshotFileSql =
"INSERT INTO \"Snapshots\" ( \"Name\", \"Path\", \"Time\", \"Data\", \"Shot\", \"Color\", \"Hash\" ) VALUES ( @name, @path, @time, zeroblob(@dataLength), zeroblob(@shotLength), @color, @hash )";
private const string SnapshotFileNoScreenshotSql =
"INSERT INTO \"Snapshots\" ( \"Name\", \"Path\", \"Time\", \"Data\", \"Shot\", \"Color\", \"Hash\" ) VALUES ( @name, @path, @time, zeroblob(@dataLength), null, @color, @hash )";
private const string RetrieveSnapshotsSql =
"SELECT \"Name\", \"Path\", \"Time\", \"Color\", \"Hash\" FROM \"Snapshots\" ORDER BY datetime(\"Time\") DESC";
private const string RetrieveDataPathFromHashSql =
"SELECT \"id\", \"Path\", \"Data\" FROM \"Snapshots\" WHERE Hash = @hash";
private const string RetrieveDataFromHashSql =
"SELECT \"id\", \"Data\" FROM \"Snapshots\" WHERE Hash = @hash";
private const string UpdateFileSql =
"UPDATE \"Snapshots\" SET Data = zeroblob(@dataLength), Hash = @recomputedHash WHERE Hash = @hash";
private const string RemoveSnapshotFromHashSql =
"DELETE FROM \"Snapshots\" WHERE Hash = @hash";
private const string RemoveScreenshotFromHashSql =
"UPDATE \"Snapshots\" SET Shot = null WHERE Hash = @hash";
private const string UpdateColorFromHashSql =
"UPDATE \"Snapshots\" SET Color = @color WHERE Hash = @hash";
private const string UpdateNoteFromHashSql =
"UPDATE \"Snapshots\" SET Note = @note WHERE Hash = @hash";
private const string UpdateHashFromHashSql = "UPDATE \"Snapshots\" SET Hash = @to WHERE Hash = @from";
private const string RelocateFileFromHashSql =
"UPDATE \"Snapshots\" SET Path = @path WHERE Hash = @hash";
private const string RemoveColorFromHashSql =
"UPDATE \"Snapshots\" SET Color = null WHERE Hash = @hash";
private const string RetrievePreviewFromHashSql =
"SELECT \"id\", \"Note\", \"Shot\" FROM \"Snapshots\" WHERE Hash = @hash";
private const string CountSnapshotsSql = "SELECT COUNT(*) FROM \"Snapshots\"";
private const string GetLastRowInsertSql = "SELECT last_insert_rowid()";
private const string GetRowFromHashSql = "SELECT \"id\" FROM \"Snapshots\" WHERE Hash = @hash";
private const string RetrieveTimeFromHash = "SELECT \"Time\" FROM \"Snapshots\" WHERE Hash = @hash";
private const string UpdateTimeFromHash = "UPDATE \"Snapshots\" SET Time = @time WHERE Hash = @hash";
private static readonly string DatabaseConnectionString = $"Data Source={Constants.DatabaseFilePath};";
private static CancellationToken _cancellationToken;
#endregion
#region Public Events & Delegates
public event EventHandler<SnapshotDataUpdateEventArgs> SnapshotDataUpdate;
public event EventHandler<SnapshotNoteUpdateEventArgs> SnapshotNoteUpdate;
public event EventHandler<SnapshotCreateEventArgs> SnapshotCreate;
public event EventHandler<SnapshotRevertEventArgs> SnapshotRevert;
#endregion
#region Private Delegates, Events, Enums, Properties, Indexers and Fields
private readonly CancellationTokenSource _cancellationTokenSource;
private readonly SemaphoreSlim _databaseLock;
private readonly SQLiteConnectionStringBuilder _sqliteConnectionStringBuilder;
#endregion
#region Constructors, Destructors and Finalizers
private SnapshotDatabase()
{
Directory.CreateDirectory(Constants.DatabaseDirectory);
_databaseLock = new SemaphoreSlim(1, 1);
_sqliteConnectionStringBuilder = new SQLiteConnectionStringBuilder
{
ConnectionString = DatabaseConnectionString
};
}
public SnapshotDatabase(CancellationToken cancellationToken) : this()
{
_cancellationTokenSource = new CancellationTokenSource();
var localCancellationToken = _cancellationTokenSource.Token;
var combinedCancellationTokenSource =
CancellationTokenSource.CreateLinkedTokenSource(localCancellationToken, cancellationToken);
_cancellationToken = combinedCancellationTokenSource.Token;
CreateDatabase(_cancellationToken).ContinueWith(async createDatabaseTask =>
{
try
{
await createDatabaseTask;
try
{
await SetAutoVacuum(_cancellationToken);
}
catch (Exception exception)
{
Log.Error(exception, "Unable to set auto vacuum for database.");
}
}
catch (Exception exception)
{
Log.Error(exception, "Unable to create database;");
}
}).Wait(_cancellationToken);
}
public void Dispose()
{
_cancellationTokenSource.Cancel();
}
#endregion
#region Public Methods
public async Task DeleteScreenshotAsync(string hash, CancellationToken cancellationToken)
{
var connectionString = new SQLiteConnectionStringBuilder
{
ConnectionString = DatabaseConnectionString
};
await _databaseLock.WaitAsync(cancellationToken);
try
{
using (var sqliteConnection = new SQLiteConnection(connectionString.ConnectionString))
{
await sqliteConnection.OpenAsync(cancellationToken);
using (var dbTransaction = sqliteConnection.BeginTransaction())
{
// Insert the file change.
using (var sqliteCommand =
new SQLiteCommand(RemoveScreenshotFromHashSql, sqliteConnection, dbTransaction))
{
sqliteCommand.Parameters.AddRange(new[]
{
new SQLiteParameter("@hash", hash)
});
sqliteCommand.Prepare();
try
{
await sqliteCommand.ExecuteNonQueryAsync(cancellationToken);
dbTransaction.Commit();
}
catch
{
dbTransaction.Rollback();
throw;
}
}
}
}
}
finally
{
_databaseLock.Release();
}
}
public async Task NormalizeTimeAsync(string hash, CancellationToken cancellationToken)
{
await _databaseLock.WaitAsync(cancellationToken);
try
{
using (var sqliteConnection =
new SQLiteConnection(_sqliteConnectionStringBuilder.ConnectionString))
{
await sqliteConnection.OpenAsync(cancellationToken);
using (var readSQLiteCommand = new SQLiteCommand(RetrieveTimeFromHash, sqliteConnection))
{
readSQLiteCommand.Parameters.AddRange(new[]
{
new SQLiteParameter("@hash", hash)
});
readSQLiteCommand.Prepare();
using (var sqlDataReader = await readSQLiteCommand.ExecuteReaderAsync(cancellationToken))
{
using (var dbTransaction = sqliteConnection.BeginTransaction())
{
try
{
while (await sqlDataReader.ReadAsync(cancellationToken))
{
var time = (string)sqlDataReader["Time"];
// Skip if already ISO 8601
if (DateTime.TryParseExact(time,
"yyyy-MM-ddTHH:mm:ss.fff",
CultureInfo.InvariantCulture,
DateTimeStyles.None, out _))
{
continue;
}
if (!DateTime.TryParse(time, out var dateTime))
{
dateTime = DateTime.Now;
}
using (var writeSQLiteCommand =
new SQLiteCommand(UpdateTimeFromHash, sqliteConnection, dbTransaction))
{
writeSQLiteCommand.Parameters.AddRange(new[]
{
new SQLiteParameter("@time",
dateTime.ToString("yyyy-MM-ddTHH:mm:ss.fff")),
new SQLiteParameter("@hash", hash)
});
writeSQLiteCommand.Prepare();
await writeSQLiteCommand.ExecuteNonQueryAsync(cancellationToken);
}
}
dbTransaction.Commit();
}
catch
{
dbTransaction.Rollback();
throw;
}
}
}
}
}
}
finally
{
_databaseLock.Release();
}
}
public async Task<long> CountSnapshotsAsync(CancellationToken cancellationToken)
{
await _databaseLock.WaitAsync(cancellationToken);
try
{
using (var sqliteConnection = new SQLiteConnection(_sqliteConnectionStringBuilder.ConnectionString))
{
await sqliteConnection.OpenAsync(cancellationToken);
// Insert the file change.
using (var sqliteCommand = new SQLiteCommand(CountSnapshotsSql, sqliteConnection))
{
long count = 0;
sqliteCommand.Prepare();
using (var sqlDataReader = await sqliteCommand.ExecuteReaderAsync(cancellationToken))
{
while (await sqlDataReader.ReadAsync(cancellationToken))
{
if (!(sqlDataReader[0] is long dbCount))
{
count = -1;
break;
}
count = dbCount;
}
return count;
}
}
}
}
finally
{
_databaseLock.Release();
}
}
public async IAsyncEnumerable<Snapshot> LoadSnapshotsAsync([EnumeratorCancellation] CancellationToken cancellationToken)
{
await _databaseLock.WaitAsync(cancellationToken);
try
{
using (var sqliteConnection =
new SQLiteConnection(_sqliteConnectionStringBuilder.ConnectionString))
{
await sqliteConnection.OpenAsync(cancellationToken);
// Insert the file change.
using (var sqliteCommand = new SQLiteCommand(RetrieveSnapshotsSql, sqliteConnection))
{
sqliteCommand.Prepare();
using (var sqlDataReader = await sqliteCommand.ExecuteReaderAsync(cancellationToken))
{
//var snapshots = new List<Snapshot>();
while (await sqlDataReader.ReadAsync(cancellationToken))
{
var name = (string)sqlDataReader["Name"];
var path = (string)sqlDataReader["Path"];
var time = (string)sqlDataReader["Time"];
var hash = (string)sqlDataReader["Hash"];
var color = Color.Empty;
if (!(sqlDataReader["Color"] is DBNull))
{
var dbColor = Convert.ToInt32(sqlDataReader["Color"]);
switch (dbColor)
{
case 0:
color = Color.Empty;
break;
default:
color = Color.FromArgb(dbColor);
break;
}
}
yield return new Snapshot(name, path, time, hash, color);
}
}
}
}
}
finally
{
_databaseLock.Release();
}
}
public async Task CreateSnapshotAsync(string name, string path, Color color, CancellationToken cancellationToken)
{
await _databaseLock.WaitAsync(cancellationToken);
try
{
using (var sqliteConnection =
new SQLiteConnection(_sqliteConnectionStringBuilder.ConnectionString))
{
await sqliteConnection.OpenAsync(cancellationToken);
using (var dbTransaction = sqliteConnection.BeginTransaction())
{
try
{
using (var md5 = MD5.Create())
{
using (var hashMemoryStream = new MemoryStream())
{
using (var fileStream =
await Miscellaneous.GetFileStream(path, FileMode.Open, FileAccess.Read,
FileShare.Read,
cancellationToken))
{
fileStream.Position = 0L;
await fileStream.CopyToAsync(hashMemoryStream);
hashMemoryStream.Position = 0L;
var hash = md5.ComputeHash(hashMemoryStream);
var hashHex = BitConverter.ToString(hash).Replace("-", "")
.ToLowerInvariant();
using (var fileMemoryStream = new MemoryStream())
{
using (var fileZipStream =
new GZipStream(fileMemoryStream, CompressionMode.Compress, true))
{
fileStream.Position = 0L;
await fileStream.CopyToAsync(fileZipStream);
fileZipStream.Close();
var time = DateTime.Now.ToString("yyyy-MM-ddTHH:mm:ss.fff");
fileMemoryStream.Position = 0L;
// Insert the file change.
using (var sqliteCommand =
new SQLiteCommand(SnapshotFileNoScreenshotSql, sqliteConnection,
dbTransaction))
{
sqliteCommand.Parameters.AddRange(new[]
{
new SQLiteParameter("@name", name),
new SQLiteParameter("@time", time),
new SQLiteParameter("@path", path),
new SQLiteParameter("@dataLength",
fileMemoryStream.Length),
new SQLiteParameter("@hash", hashHex)
});
var numeric = color.ToArgb();
switch (numeric)
{
case 0:
sqliteCommand.Parameters.Add(
new SQLiteParameter("@color", null));
break;
default:
sqliteCommand.Parameters.Add(
new SQLiteParameter("@color", numeric));
break;
}
sqliteCommand.Prepare();
await sqliteCommand.ExecuteNonQueryAsync(cancellationToken);
}
// Insert the data blobs.
using (var sqliteCommand =
new SQLiteCommand(GetLastRowInsertSql, sqliteConnection,
dbTransaction))
{
sqliteCommand.Prepare();
var rowId =
(long)await sqliteCommand.ExecuteScalarAsync(cancellationToken);
using (var sqliteBlob =
SQLiteBlob.Create(sqliteConnection, "main", "Snapshots",
"Data",
rowId,
false))
{
var fileMemoryStreamData = fileMemoryStream.ToArray();
sqliteBlob.Write(fileMemoryStreamData,
fileMemoryStreamData.Length,
0);
}
}
dbTransaction.Commit();
SnapshotCreate?.Invoke(this,
new SnapshotCreateSuccessEventArgs(name, time, path, color,
hashHex));
}
}
}
}
}
}
catch (SQLiteException exception)
{
dbTransaction.Rollback();
if (exception.ResultCode != SQLiteErrorCode.Constraint)
{
SnapshotCreate?.Invoke(this,
new SnapshotCreateFailureEventArgs(name, path, color, exception));
}
throw;
}
catch (Exception exception)
{
dbTransaction.Rollback();
SnapshotCreate?.Invoke(this,
new SnapshotCreateFailureEventArgs(name, path, color, exception));
throw;
}
}
}
}
finally
{
_databaseLock.Release();
}
}
public async Task CreateSnapshotAsync(string name, string path,
Bitmap shot, Color color, CancellationToken cancellationToken)
{
await _databaseLock.WaitAsync(cancellationToken);
try
{
using (var sqliteConnection =
new SQLiteConnection(_sqliteConnectionStringBuilder.ConnectionString))
{
await sqliteConnection.OpenAsync(cancellationToken);
using (var dbTransaction = sqliteConnection.BeginTransaction())
{
try
{
using (var md5 = MD5.Create())
{
using (var hashMemoryStream = new MemoryStream())
{
using (var fileStream =
await Miscellaneous.GetFileStream(path, FileMode.Open, FileAccess.Read,
FileShare.Read,
cancellationToken))
{
fileStream.Position = 0L;
await fileStream.CopyToAsync(hashMemoryStream);
hashMemoryStream.Position = 0L;
var hash = md5.ComputeHash(hashMemoryStream);
var hashHex = BitConverter.ToString(hash).Replace("-", "")
.ToLowerInvariant();
using (var fileMemoryStream = new MemoryStream())
{
using (var fileZipStream =
new GZipStream(fileMemoryStream, CompressionMode.Compress, true))
{
fileStream.Position = 0L;
await fileStream.CopyToAsync(fileZipStream);
fileZipStream.Close();
using (var bitmapMemoryStream = new MemoryStream())
{
using (var bitmapZipStream =
new GZipStream(bitmapMemoryStream, CompressionMode.Compress,
true))
{
shot.Save(bitmapZipStream, ImageFormat.Bmp);
bitmapZipStream.Close();
var time = DateTime.Now.ToString("yyyy-MM-ddTHH:mm:ss.fff");
fileMemoryStream.Position = 0L;
bitmapMemoryStream.Position = 0L;
// Insert the file change.
using (var sqliteCommand =
new SQLiteCommand(SnapshotFileSql, sqliteConnection,
dbTransaction))
{
sqliteCommand.Parameters.AddRange(new[]
{
new SQLiteParameter("@name", name),
new SQLiteParameter("@time", time),
new SQLiteParameter("@path", path),
new SQLiteParameter("@shotLength",
bitmapMemoryStream.Length),
new SQLiteParameter("@dataLength",
fileMemoryStream.Length),
new SQLiteParameter("@hash", hashHex)
});
var numeric = color.ToArgb();
switch (numeric)
{
case 0:
sqliteCommand.Parameters.Add(
new SQLiteParameter("@color", null));
break;
default:
sqliteCommand.Parameters.Add(
new SQLiteParameter("@color", numeric));
break;
}
sqliteCommand.Prepare();
await sqliteCommand.ExecuteNonQueryAsync(cancellationToken);
}
// Insert the data blobs.
using (var sqliteCommand =
new SQLiteCommand(GetLastRowInsertSql, sqliteConnection,
dbTransaction))
{
sqliteCommand.Prepare();
var rowId =
(long)await sqliteCommand.ExecuteScalarAsync(
cancellationToken);
using (var sqliteBlob =
SQLiteBlob.Create(sqliteConnection, "main",
"Snapshots",
"Data",
rowId,
false))
{
var fileMemoryStreamData = fileMemoryStream.ToArray();
sqliteBlob.Write(fileMemoryStreamData,
fileMemoryStreamData.Length,
0);
}
using (var sqliteBlob =
SQLiteBlob.Create(sqliteConnection, "main",
"Snapshots",
"Shot",
rowId,
false))
{
var bitmapMemoryStreamData =
bitmapMemoryStream.ToArray();
sqliteBlob.Write(bitmapMemoryStreamData,
bitmapMemoryStreamData.Length,
0);
}
}
dbTransaction.Commit();
SnapshotCreate?.Invoke(this,
new SnapshotCreateSuccessEventArgs(name, time, path, color,
hashHex));
}
}
}
}
}
}
}
}
catch (SQLiteException exception)
{
dbTransaction.Rollback();
if (exception.ResultCode != SQLiteErrorCode.Constraint)
{
SnapshotCreate?.Invoke(this,
new SnapshotCreateFailureEventArgs(name, path, color, exception));
}
throw;
}
catch (Exception exception)
{
dbTransaction.Rollback();
SnapshotCreate?.Invoke(this,
new SnapshotCreateFailureEventArgs(name, path, color, exception));
throw;
}
}
}
}
finally
{
_databaseLock.Release();
}
}
public async Task SaveFileAsync(string path, string hash, CancellationToken cancellationToken)
{
await _databaseLock.WaitAsync(cancellationToken);
try
{
using (var sqliteConnection = new SQLiteConnection(_sqliteConnectionStringBuilder.ConnectionString))
{
await sqliteConnection.OpenAsync(cancellationToken);
// Insert the file change.
using (var sqliteCommand =
new SQLiteCommand(RetrieveDataPathFromHashSql, sqliteConnection))
{
sqliteCommand.Parameters.AddRange(new[]
{
new SQLiteParameter("@hash", hash)
});
sqliteCommand.Prepare();
using (var sqlDataReader = await sqliteCommand.ExecuteReaderAsync(cancellationToken))
{
while (await sqlDataReader.ReadAsync(cancellationToken))
{
// Create directories if they do not exist.
var dir = Path.GetDirectoryName(path);
if (dir != null && !Directory.Exists(dir))
{
Directory.CreateDirectory(dir);
}
using (var readStream = sqlDataReader.GetStream(2))
{
using (var fileStream =
await Miscellaneous.GetFileStream(path, FileMode.Create, FileAccess.Write,
FileShare.Write,
cancellationToken))
{
readStream.Position = 0L;
using (var zipStream = new GZipStream(readStream, CompressionMode.Decompress))
{
await zipStream.CopyToAsync(fileStream);
}
}
}
}
}
}
}
}
finally
{
_databaseLock.Release();
}
}
public async Task RevertFileAsync(string name, string hash, CancellationToken cancellationToken, bool atomic = true)
{
await _databaseLock.WaitAsync(cancellationToken);
try
{
using (var sqliteConnection = new SQLiteConnection(_sqliteConnectionStringBuilder.ConnectionString))
{
await sqliteConnection.OpenAsync(cancellationToken);
// Insert the file change.
using (var sqliteCommand =
new SQLiteCommand(RetrieveDataPathFromHashSql, sqliteConnection))
{
try
{
sqliteCommand.Parameters.AddRange(new[]
{
new SQLiteParameter("@hash", hash)
});
sqliteCommand.Prepare();
using (var sqlDataReader = await sqliteCommand.ExecuteReaderAsync(cancellationToken))
{
while (await sqlDataReader.ReadAsync(cancellationToken))
{
var path = (string)sqlDataReader["Path"];
// Create directories if they do not exist.
var dir = Path.GetDirectoryName(path);
if (dir != null && !Directory.Exists(dir))
{
Directory.CreateDirectory(dir);
}
switch (atomic)
{
case true:
// Atomic
var temp = Path.Combine(Path.GetDirectoryName(path),
$"{Path.GetFileName(path)}.temp");
using (var readStream = sqlDataReader.GetStream(2))
{
using (var fileStream = new FileStream(temp, FileMode.Create,
FileAccess.Write,
FileShare.None))
{
using (var zipStream =
new GZipStream(readStream, CompressionMode.Decompress))
{
zipStream.CopyTo(fileStream);
}
}
}
try
{
File.Replace(temp, path, null, true);
}
catch
{
try
{
File.Delete(temp);
}
catch (Exception exception)
{
// Suppress deletion errors of temporary file.
Log.Warning(exception, "Could not delete temporary file.", temp);
}
throw;
}
break;
default:
// Asynchronous
using (var readStream = sqlDataReader.GetStream(2))
{
using (var fileStream =
await Miscellaneous.GetFileStream(path, FileMode.Create,
FileAccess.Write,
FileShare.Write,
cancellationToken))
{
readStream.Position = 0L;
using (var zipStream =
new GZipStream(readStream, CompressionMode.Decompress))
{
await zipStream.CopyToAsync(fileStream);
}
}
}
break;
}
SnapshotRevert?.Invoke(this, new SnapshotRevertSuccessEventArgs(name));
}
}
}
catch
{
SnapshotRevert?.Invoke(this, new SnapshotRevertFailureEventArgs(name));
throw;
}
}
}
}
finally
{
_databaseLock.Release();
}
}
public async Task RemoveFileFastAsync(IEnumerable<string> hashes, CancellationToken cancellationToken)
{
await _databaseLock.WaitAsync(cancellationToken);
try
{
using (var sqliteConnection = new SQLiteConnection(_sqliteConnectionStringBuilder.ConnectionString))
{
await sqliteConnection.OpenAsync(cancellationToken);
using (var dbTransaction = sqliteConnection.BeginTransaction())
{
try
{
var transactionCommands = new List<Task>();
foreach (var hash in hashes)
{
// Insert the file change.
using (var sqliteCommand =
new SQLiteCommand(RemoveSnapshotFromHashSql, sqliteConnection, dbTransaction))
{
sqliteCommand.Parameters.AddRange(new[]
{
new SQLiteParameter("@hash", hash)
});
sqliteCommand.Prepare();
var command = sqliteCommand.ExecuteNonQueryAsync(cancellationToken);
transactionCommands.Add(command);
}
}
await Task.WhenAll(transactionCommands);
dbTransaction.Commit();
}
catch
{
dbTransaction.Rollback();
throw;
}
}
}
}
finally
{
_databaseLock.Release();
}
}
public async Task RemoveFileAsync(string hash, CancellationToken cancellationToken)
{
await _databaseLock.WaitAsync(cancellationToken);
try
{
using (var sqliteConnection = new SQLiteConnection(_sqliteConnectionStringBuilder.ConnectionString))
{
await sqliteConnection.OpenAsync(cancellationToken);
using (var dbTransaction = sqliteConnection.BeginTransaction())
{
// Insert the file change.
using (var sqliteCommand =
new SQLiteCommand(RemoveSnapshotFromHashSql, sqliteConnection, dbTransaction))
{
sqliteCommand.Parameters.AddRange(new[]
{
new SQLiteParameter("@hash", hash)
});
sqliteCommand.Prepare();
try
{
await sqliteCommand.ExecuteNonQueryAsync(cancellationToken);
dbTransaction.Commit();
}
catch
{
dbTransaction.Rollback();
throw;
}
}
}
}
}
finally
{
_databaseLock.Release();
}
}
public async Task UpdateColorAsync(string hash, Color color, CancellationToken cancellationToken)
{
await _databaseLock.WaitAsync(cancellationToken);
try
{
using (var sqliteConnection = new SQLiteConnection(_sqliteConnectionStringBuilder.ConnectionString))
{
await sqliteConnection.OpenAsync(cancellationToken);
using (var dbTransaction = sqliteConnection.BeginTransaction())
{
// Insert the file change.
using (var sqliteCommand =
new SQLiteCommand(UpdateColorFromHashSql, sqliteConnection, dbTransaction))
{
sqliteCommand.Parameters.AddRange(new[]
{
new SQLiteParameter("@hash", hash),
new SQLiteParameter("@color", color.ToArgb())
});
sqliteCommand.Prepare();
try
{
await sqliteCommand.ExecuteNonQueryAsync(cancellationToken);
dbTransaction.Commit();
}
catch
{
dbTransaction.Rollback();
throw;
}
}
}
}
}
finally
{
_databaseLock.Release();
}
}
public async Task RemoveColorAsync(string hash, CancellationToken cancellationToken)
{
await _databaseLock.WaitAsync(cancellationToken);
try
{
using (var sqliteConnection = new SQLiteConnection(_sqliteConnectionStringBuilder.ConnectionString))
{
await sqliteConnection.OpenAsync(cancellationToken);
using (var dbTransaction = sqliteConnection.BeginTransaction())
{
// Insert the file change.
using (var sqliteCommand =
new SQLiteCommand(RemoveColorFromHashSql, sqliteConnection, dbTransaction))
{
sqliteCommand.Parameters.AddRange(new[]
{
new SQLiteParameter("@hash", hash)
});
sqliteCommand.Prepare();
try
{
await sqliteCommand.ExecuteNonQueryAsync(cancellationToken);
dbTransaction.Commit();
}
catch
{
dbTransaction.Rollback();
throw;
}
}
}
}
}
finally
{
_databaseLock.Release();
}
}
public async Task<SnapshotPreview> RetrievePreviewAsync(string hash, CancellationToken cancellationToken)
{
await _databaseLock.WaitAsync(cancellationToken);
try
{
using (var sqliteConnection = new SQLiteConnection(_sqliteConnectionStringBuilder.ConnectionString))
{
await sqliteConnection.OpenAsync(cancellationToken);
// Insert the file change.
using (var sqliteCommand = new SQLiteCommand(RetrievePreviewFromHashSql, sqliteConnection))
{
sqliteCommand.Parameters.AddRange(new[]
{
new SQLiteParameter("@hash", hash)
});
var note = string.Empty;
sqliteCommand.Prepare();
using (var sqlDataReader = await sqliteCommand.ExecuteReaderAsync(cancellationToken))
{
while (await sqlDataReader.ReadAsync(cancellationToken))
{
if (!(sqlDataReader["Note"] is DBNull))
{
note = (string)sqlDataReader["Note"];
}
Bitmap shot = null;
if (!(sqlDataReader["Shot"] is DBNull))
{
var readStream = sqlDataReader.GetStream(2);
readStream.Position = 0L;
using (var zipStream = new GZipStream(readStream, CompressionMode.Decompress))
{
using (var image = Image.FromStream(zipStream))
{
shot = new Bitmap(image);
}
}
}
return new SnapshotPreview(hash, shot, note);
}
return null;
}
}
}
}
finally
{
_databaseLock.Release();
}
}
public async Task<MemoryStream> RetrieveFileStreamAsync(string hash, CancellationToken cancellationToken)
{
await _databaseLock.WaitAsync(cancellationToken);
try
{
using (var sqliteConnection = new SQLiteConnection(_sqliteConnectionStringBuilder.ConnectionString))
{
await sqliteConnection.OpenAsync(cancellationToken);
// Insert the file change.
using (var sqliteCommand = new SQLiteCommand(RetrieveDataFromHashSql, sqliteConnection))
{
sqliteCommand.Parameters.AddRange(new[]
{
new SQLiteParameter("@hash", hash)
});
sqliteCommand.Prepare();
using (var sqlDataReader = await sqliteCommand.ExecuteReaderAsync(cancellationToken))
{
while (await sqlDataReader.ReadAsync(cancellationToken))
{
using (var readStream = sqlDataReader.GetStream(1))
{
using (var memoryStream = new MemoryStream())
{
readStream.Position = 0L;
await readStream.CopyToAsync(memoryStream);
memoryStream.Position = 0L;
using (var zipStream = new GZipStream(memoryStream, CompressionMode.Decompress))
{
// Do not dispose the returned stream and leave it up to callers to dispose.
var outputStream = new MemoryStream();
await zipStream.CopyToAsync(outputStream);
outputStream.Position = 0L;
return outputStream;
}
}
}
}
return null;
}
}
}
}
finally
{
_databaseLock.Release();
}
}
public async Task RelocateFileAsync(string hash, string path, CancellationToken cancellationToken)
{
await _databaseLock.WaitAsync(cancellationToken);
try
{
using (var sqliteConnection = new SQLiteConnection(_sqliteConnectionStringBuilder.ConnectionString))
{
await sqliteConnection.OpenAsync(cancellationToken);
using (var dbTransaction = sqliteConnection.BeginTransaction())
{
// Insert the file change.
using (var sqliteCommand =
new SQLiteCommand(RelocateFileFromHashSql, sqliteConnection, dbTransaction))
{
sqliteCommand.Parameters.AddRange(new[]
{
new SQLiteParameter("@hash", hash),
new SQLiteParameter("@path", path)
});
sqliteCommand.Prepare();
try
{
await sqliteCommand.ExecuteNonQueryAsync(cancellationToken);
dbTransaction.Commit();
}
catch
{
dbTransaction.Rollback();
throw;
}
}
}
}
}
finally
{
_databaseLock.Release();
}
}
public async Task UpdateNoteAsync(string hash, string note, CancellationToken cancellationToken)
{
await _databaseLock.WaitAsync(cancellationToken);
try
{
using (var sqliteConnection = new SQLiteConnection(_sqliteConnectionStringBuilder.ConnectionString))
{
await sqliteConnection.OpenAsync(cancellationToken);
using (var dbTransaction = sqliteConnection.BeginTransaction())
{
// Insert the file change.
using (var sqliteCommand =
new SQLiteCommand(UpdateNoteFromHashSql, sqliteConnection, dbTransaction))
{
sqliteCommand.Parameters.AddRange(new[]
{
new SQLiteParameter("@hash", hash),
new SQLiteParameter("@note", note)
});
sqliteCommand.Prepare();
try
{
await sqliteCommand.ExecuteNonQueryAsync(cancellationToken);
dbTransaction.Commit();
SnapshotNoteUpdate?.Invoke(this, new SnapshotNoteUpdateSuccessEventArgs(note));
}
catch
{
dbTransaction.Rollback();
SnapshotNoteUpdate?.Invoke(this, new SnapshotNoteUpdateFailureEventArgs());
throw;
}
}
}
}
}
finally
{
_databaseLock.Release();
}
}
public async Task<string> UpdateFileAsync(string hash, byte[] data, CancellationToken cancellationToken)
{
await _databaseLock.WaitAsync(cancellationToken);
try
{
using (var dataMemoryStream = new MemoryStream(data))
{
using (var sqliteConnection = new SQLiteConnection(_sqliteConnectionStringBuilder.ConnectionString))
{
await sqliteConnection.OpenAsync(cancellationToken);
using (var dbTransaction = sqliteConnection.BeginTransaction())
{
try
{
using (var md5 = MD5.Create())
{
using (var hashMemoryStream = new MemoryStream())
{
dataMemoryStream.Position = 0L;
await dataMemoryStream.CopyToAsync(hashMemoryStream);
hashMemoryStream.Position = 0L;
var recomputedHash = md5.ComputeHash(hashMemoryStream);
var hashHex = BitConverter.ToString(recomputedHash).Replace("-", "")
.ToLowerInvariant();
using (var fileMemoryStream = new MemoryStream())
{
using (var fileZipStream =
new GZipStream(fileMemoryStream, CompressionMode.Compress, true))
{
dataMemoryStream.Position = 0L;
await dataMemoryStream.CopyToAsync(fileZipStream);
fileZipStream.Close();
fileMemoryStream.Position = 0L;
// Insert the file change.
using (var sqliteCommand =
new SQLiteCommand(UpdateFileSql, sqliteConnection,
dbTransaction))
{
sqliteCommand.Parameters.AddRange(new[]
{
new SQLiteParameter("@dataLength", fileMemoryStream.Length),
new SQLiteParameter("@recomputedHash", hashHex),
new SQLiteParameter("@hash", hash)
});
sqliteCommand.Prepare();
await sqliteCommand.ExecuteNonQueryAsync(cancellationToken);
}
using (var sqliteCommand =
new SQLiteCommand(GetRowFromHashSql, sqliteConnection,
dbTransaction))
{
sqliteCommand.Parameters.AddRange(new[]
{
new SQLiteParameter("@hash", hashHex)
});
sqliteCommand.Prepare();
using (var sqlDataReader =
await sqliteCommand.ExecuteReaderAsync(cancellationToken))
{
while (await sqlDataReader.ReadAsync(cancellationToken))
{
if (sqlDataReader["id"] is long rowId)
{
using (var sqliteBlob = SQLiteBlob.Create(
sqliteConnection,
"main",
"Snapshots",
"Data",
rowId, false))
{
var fileMemoryStreamData =
fileMemoryStream.ToArray();
sqliteBlob.Write(fileMemoryStreamData,
fileMemoryStreamData.Length,
0);
}
}
}
}
}
dbTransaction.Commit();
SnapshotDataUpdate?.Invoke(this,
new SnapshotDataUpdateSuccessEventArgs(hash, hashHex));
return hashHex;
}
}
}
}
}
catch
{
dbTransaction.Rollback();
SnapshotDataUpdate?.Invoke(this, new SnapshotDataUpdateFailureEventArgs(hash));
throw;
}
}
}
}
}
finally
{
_databaseLock.Release();
}
}
public async Task UpdateHashAsync(string from, string to, CancellationToken cancellationToken)
{
await _databaseLock.WaitAsync(cancellationToken);
try
{
using (var sqliteConnection = new SQLiteConnection(_sqliteConnectionStringBuilder.ConnectionString))
{
await sqliteConnection.OpenAsync(cancellationToken);
using (var dbTransaction = sqliteConnection.BeginTransaction())
{
// Insert the file change.
using (var sqliteCommand =
new SQLiteCommand(UpdateHashFromHashSql, sqliteConnection, dbTransaction))
{
sqliteCommand.Parameters.AddRange(new[]
{
new SQLiteParameter("@from", from),
new SQLiteParameter("@to", to)
});
sqliteCommand.Prepare();
try
{
await sqliteCommand.ExecuteNonQueryAsync(cancellationToken);
dbTransaction.Commit();
}
catch
{
dbTransaction.Rollback();
throw;
}
}
}
}
}
finally
{
_databaseLock.Release();
}
}
#endregion
#region Private Methods
private async Task SetAutoVacuum(CancellationToken cancellationToken)
{
await _databaseLock.WaitAsync(cancellationToken);
try
{
using (var sqliteConnection =
new SQLiteConnection(_sqliteConnectionStringBuilder.ConnectionString))
{
await sqliteConnection.OpenAsync(cancellationToken);
// Set auto vacuum.
using (var sqliteCommand = new SQLiteCommand(SetAutoVacuumSql, sqliteConnection))
{
await sqliteCommand.ExecuteNonQueryAsync(cancellationToken);
}
}
}
finally
{
_databaseLock.Release();
}
}
private async Task CreateDatabase(CancellationToken cancellationToken)
{
await _databaseLock.WaitAsync(cancellationToken);
try
{
using (var sqliteConnection = new SQLiteConnection(_sqliteConnectionStringBuilder.ConnectionString))
{
await sqliteConnection.OpenAsync(cancellationToken);
using (var dbTransaction = sqliteConnection.BeginTransaction())
{
// Create the table if it does not exist.
using (var sqliteCommand = new SQLiteCommand(CreateTableSql, sqliteConnection, dbTransaction))
{
try
{
await sqliteCommand.ExecuteNonQueryAsync(cancellationToken);
dbTransaction.Commit();
}
catch
{
dbTransaction.Rollback();
throw;
}
}
}
}
}
finally
{
_databaseLock.Release();
}
}
#endregion
}
}
Generated by GNU Enscript 1.6.5.90.