QuickImage – Rev 8
?pathlinks?
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using System.Drawing;
using System.Drawing.Imaging;
using System.Globalization;
using System.IO;
using System.IO.Compression;
using System.Linq;
using System.Runtime.CompilerServices;
using System.Security.Policy;
using System.Text.RegularExpressions;
using System.Threading;
using System.Threading.Tasks;
using System.Threading.Tasks.Dataflow;
using QuickImage.Utilities.Serialization.Comma_Separated_Values;
using Serilog;
using Shipwreck.Phash;
namespace QuickImage.Database
{
public class QuickImageDatabase
{
private const string CreateImageTableSql =
"CREATE TABLE IF NOT EXISTS \"Images\" (\"id\" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, \"File\" TEXT NOT NULL, \"Tags\" TEXT NOT NULL, \"Hash\" TEXT NOT NULL, \"Thumbnail\" BLOB, UNIQUE (\"File\") ON CONFLICT REPLACE)";
private const string RetrieveImageTagsSql =
"SELECT \"Tags\" FROM \"Images\" WHERE File = @file";
private const string RetrieveImageHashSql = "SELECT \"Hash\" FROM \"Images\" WHERE File = @file";
private const string UpdateImageTagsSql =
"UPDATE \"Images\" SET Tags = @tags WHERE File = @file";
private const string UpdateImageHashSql = "UPDATE \"Images\" SET Hash = @hash WHERE File = @file";
private const string UpdateFileHashSql = "UPDATE \"Images\" SET File = @file WHERE Hash = @hash";
private const string UpdateFileSql = "UPDATE \"Images\" SET File = @to WHERE File = @from";
private const string UpdateImageThumbnailSql = "UPDATE \"Images\" SET Thumbnail = zeroblob(@thumbnailLength) WHERE File = @file";
private const string CheckForFileSql = "SELECT COUNT(*) FROM \"Images\" WHERE File = @file";
private const string CountImagesSql = "SELECT COUNT(*) FROM \"Images\"";
private const string InsertImageSql = "INSERT INTO \"Images\" ( \"File\", \"Tags\", \"Hash\", \"Thumbnail\" ) VALUES ( @file, @tags, @hash, zeroblob(@thumbnailLength) )";
private const string RemoveImageSql = "DELETE FROM \"Images\" WHERE File = @file";
private const string RetrieveImagesSql = "SELECT * FROM \"Images\"";
private const string RetrieveImageSql = "SELECT * FROM \"Images\" WHERE File = @file";
private const string StripImageTagsSql = "UPDATE \"Images\" SET Tags = \"\" WHERE File = @file";
private const string SetAutoVacuumSql = "PRAGMA auto_vacuum = FULL";
private const string GetLastRowInsertSql = "SELECT last_insert_rowid()";
private static readonly string DatabaseConnectionString = $"Data Source={Constants.DatabaseFilePath};";
private readonly CancellationToken _cancellationToken;
private readonly SemaphoreSlim _databaseLock;
private QuickImageDatabase()
{
Directory.CreateDirectory(Constants.DatabaseDirectory);
_databaseLock = new SemaphoreSlim(1, 1);
}
public QuickImageDatabase(CancellationToken cancellationToken) : this()
{
_cancellationToken = cancellationToken;
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);
}
private async Task SetAutoVacuum(CancellationToken cancellationToken)
{
var connectionString = new SQLiteConnectionStringBuilder
{
ConnectionString = DatabaseConnectionString
};
try
{
await _databaseLock.WaitAsync(cancellationToken);
}
catch
{
return;
}
try
{
using var sqliteConnection =
new SQLiteConnection(connectionString.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)
{
var connectionString = new SQLiteConnectionStringBuilder
{
ConnectionString = DatabaseConnectionString
};
try
{
await _databaseLock.WaitAsync(cancellationToken);
}
catch
{
return;
}
try
{
using var sqliteConnection = new SQLiteConnection(connectionString.ConnectionString);
await sqliteConnection.OpenAsync(cancellationToken);
using var dbTransaction = sqliteConnection.BeginTransaction();
// Create the table if it does not exist.
using var sqliteCommand = new SQLiteCommand(CreateImageTableSql, sqliteConnection, dbTransaction);
try
{
await sqliteCommand.ExecuteNonQueryAsync(cancellationToken);
dbTransaction.Commit();
}
catch (Exception exception)
{
Log.Error(exception, "Could not create database table.");
dbTransaction.Rollback();
throw;
}
}
finally
{
_databaseLock.Release();
}
}
public bool Exists(string file, CancellationToken cancellationToken)
{
var connectionString = new SQLiteConnectionStringBuilder
{
ConnectionString = DatabaseConnectionString
};
try
{
_databaseLock.Wait(cancellationToken);
}
catch
{
return false;
}
try
{
using var sqliteConnection = new SQLiteConnection(connectionString.ConnectionString);
sqliteConnection.Open();
using var sqLiteCommand = new SQLiteCommand(CheckForFileSql, sqliteConnection);
sqLiteCommand.Parameters.AddRange(new[]
{
new SQLiteParameter("@file", file)
});
sqLiteCommand.Prepare();
using var sqlDataReader = sqLiteCommand.ExecuteReader();
long count = 0;
while (sqlDataReader.Read())
{
if (!(sqlDataReader[0] is long dbCount))
{
count = -1;
break;
}
count = dbCount;
}
return count != 0;
}
finally
{
_databaseLock.Release();
}
}
public async Task<bool> ExistsAsync(string file, CancellationToken cancellationToken)
{
var connectionString = new SQLiteConnectionStringBuilder
{
ConnectionString = DatabaseConnectionString
};
try
{
await _databaseLock.WaitAsync(cancellationToken);
}
catch
{
return false;
}
try
{
using var sqliteConnection = new SQLiteConnection(connectionString.ConnectionString);
await sqliteConnection.OpenAsync(cancellationToken);
using var sqLiteCommand = new SQLiteCommand(CheckForFileSql, sqliteConnection);
sqLiteCommand.Parameters.AddRange(new[]
{
new SQLiteParameter("@file", file)
});
sqLiteCommand.Prepare();
using var sqlDataReader = await sqLiteCommand.ExecuteReaderAsync(cancellationToken);
long count = 0;
while (await sqlDataReader.ReadAsync(cancellationToken))
{
if (!(sqlDataReader[0] is long dbCount))
{
count = -1;
break;
}
count = dbCount;
}
return count != 0;
}
finally
{
_databaseLock.Release();
}
}
public async Task<long> CountAsync(CancellationToken cancellationToken)
{
var connectionString = new SQLiteConnectionStringBuilder
{
ConnectionString = DatabaseConnectionString
};
try
{
await _databaseLock.WaitAsync(cancellationToken);
}
catch
{
return -1;
}
try
{
using var sqliteConnection = new SQLiteConnection(connectionString.ConnectionString);
await sqliteConnection.OpenAsync(cancellationToken);
using var sqLiteCommand = new SQLiteCommand(CountImagesSql, sqliteConnection);
sqLiteCommand.Prepare();
using var sqlDataReader = await sqLiteCommand.ExecuteReaderAsync(cancellationToken);
long count = 0;
while (await sqlDataReader.ReadAsync(cancellationToken))
{
if (!(sqlDataReader[0] is long dbCount))
{
count = -1;
break;
}
count = dbCount;
}
return count;
}
finally
{
_databaseLock.Release();
}
}
public async Task<bool> RemoveImageAsync(string file, CancellationToken cancellationToken)
{
var connectionString = new SQLiteConnectionStringBuilder
{
ConnectionString = DatabaseConnectionString
};
try
{
await _databaseLock.WaitAsync(cancellationToken);
}
catch
{
return false;
}
try
{
using var sqliteConnection = new SQLiteConnection(connectionString.ConnectionString);
await sqliteConnection.OpenAsync(cancellationToken);
using var dbTransaction = sqliteConnection.BeginTransaction();
using var sqLiteCommand = new SQLiteCommand(RemoveImageSql, sqliteConnection, dbTransaction);
sqLiteCommand.Parameters.AddRange(new[]
{
new SQLiteParameter("@file", file)
});
sqLiteCommand.Prepare();
try
{
await sqLiteCommand.ExecuteNonQueryAsync(cancellationToken);
dbTransaction.Commit();
return true;
}
catch (Exception exception)
{
Log.Error(exception, "Could not remove.");
dbTransaction.Rollback();
}
return false;
}
finally
{
_databaseLock.Release();
}
}
public async Task<bool> AddImageAsync(string file, Digest hash, IEnumerable<string> keywords, Bitmap thumbnail,
CancellationToken cancellationToken)
{
var connectionString = new SQLiteConnectionStringBuilder
{
ConnectionString = DatabaseConnectionString
};
try
{
await _databaseLock.WaitAsync(cancellationToken);
}
catch
{
return false;
}
try
{
using var bitmapMemoryStream = new MemoryStream();
using var bitmapZipStream =
new GZipStream(bitmapMemoryStream, CompressionMode.Compress,
true);
thumbnail.Save(bitmapZipStream, ImageFormat.Bmp);
bitmapZipStream.Close();
bitmapMemoryStream.Position = 9L;
using var sqliteConnection = new SQLiteConnection(connectionString.ConnectionString);
await sqliteConnection.OpenAsync(cancellationToken);
using var dbTransaction = sqliteConnection.BeginTransaction();
try
{
using (var sqLiteCommand = new SQLiteCommand(InsertImageSql, sqliteConnection, dbTransaction))
{
var words = new HashSet<string>();
foreach (var word in keywords)
{
if (string.IsNullOrEmpty(word))
{
continue;
}
words.Add(word);
}
sqLiteCommand.Parameters.AddRange(new[]
{
new SQLiteParameter("@file", file),
new SQLiteParameter("@tags", new Csv(words)),
new SQLiteParameter("@hash", Convert.ToBase64String(hash.Coefficients)),
new SQLiteParameter("@thumbnailLength", bitmapMemoryStream.Length)
});
sqLiteCommand.Prepare();
await sqLiteCommand.ExecuteNonQueryAsync(cancellationToken);
}
using (var getLastRowInsertCommand =
new SQLiteCommand(GetLastRowInsertSql, sqliteConnection, dbTransaction))
{
getLastRowInsertCommand.Prepare();
var rowId =
(long)await getLastRowInsertCommand.ExecuteScalarAsync(cancellationToken);
using var sqliteBlob =
SQLiteBlob.Create(sqliteConnection, "main", "Images", "Thumbnail", rowId, false);
var fileMemoryStreamData = bitmapMemoryStream.ToArray();
sqliteBlob.Write(fileMemoryStreamData,
fileMemoryStreamData.Length,
0);
}
dbTransaction.Commit();
return true;
}
catch (Exception exception)
{
Log.Error(exception, "Could not insert.");
dbTransaction.Rollback();
}
return false;
}
finally
{
_databaseLock.Release();
}
}
public async Task<bool> StripTagsAsync(string file, CancellationToken cancellationToken)
{
var connectionString = new SQLiteConnectionStringBuilder
{
ConnectionString = DatabaseConnectionString
};
try
{
await _databaseLock.WaitAsync(cancellationToken);
}
catch
{
return false;
}
try
{
using var sqliteConnection = new SQLiteConnection(connectionString.ConnectionString);
await sqliteConnection.OpenAsync(cancellationToken);
using var dbTransaction = sqliteConnection.BeginTransaction();
try
{
using var sqLiteCommand = new SQLiteCommand(StripImageTagsSql, sqliteConnection, dbTransaction);
sqLiteCommand.Parameters.AddRange(new[]
{
new SQLiteParameter("@file", file)
});
sqLiteCommand.Prepare();
await sqLiteCommand.ExecuteNonQueryAsync(cancellationToken);
dbTransaction.Commit();
return true;
}
catch (Exception exception)
{
Log.Error(exception, "Could not insert.");
dbTransaction.Rollback();
}
return false;
}
finally
{
_databaseLock.Release();
}
}
public async Task<bool> RemoveTagsAsync(string file, IEnumerable<string> keywords,
CancellationToken cancellationToken)
{
var connectionString = new SQLiteConnectionStringBuilder
{
ConnectionString = DatabaseConnectionString
};
try
{
await _databaseLock.WaitAsync(cancellationToken);
}
catch
{
return false;
}
try
{
using var sqliteConnection = new SQLiteConnection(connectionString.ConnectionString);
await sqliteConnection.OpenAsync(cancellationToken);
using var dbTransaction = sqliteConnection.BeginTransaction();
try
{
using var sqLiteCommand = new SQLiteCommand(RetrieveImageTagsSql, sqliteConnection, dbTransaction);
sqLiteCommand.Parameters.AddRange(new[]
{
new SQLiteParameter("@file", file)
});
sqLiteCommand.Prepare();
using var sqlDataReader = await sqLiteCommand.ExecuteReaderAsync(cancellationToken);
var tagWords = new HashSet<string>();
while (await sqlDataReader.ReadAsync(cancellationToken))
{
var tags = (string)sqlDataReader["Tags"];
if (string.IsNullOrEmpty(tags))
{
continue;
}
foreach (var word in new Csv(tags))
{
if (string.IsNullOrEmpty(word))
{
continue;
}
tagWords.Add(word);
}
}
sqlDataReader.Close();
var merged = new Csv(tagWords.Except(keywords));
using var updateTagsSqliteCommand =
new SQLiteCommand(UpdateImageTagsSql, sqliteConnection, dbTransaction);
updateTagsSqliteCommand.Parameters.AddRange(new[]
{
new SQLiteParameter("@file", file),
new SQLiteParameter("@tags", merged)
});
updateTagsSqliteCommand.Prepare();
await updateTagsSqliteCommand.ExecuteNonQueryAsync(cancellationToken);
dbTransaction.Commit();
return true;
}
catch (Exception exception)
{
Log.Error(exception, "Could not insert.");
dbTransaction.Rollback();
}
return false;
}
finally
{
_databaseLock.Release();
}
}
public async Task<bool> AddTagsAsync(string file, IEnumerable<string> keywords, CancellationToken cancellationToken)
{
var connectionString = new SQLiteConnectionStringBuilder
{
ConnectionString = DatabaseConnectionString
};
try
{
await _databaseLock.WaitAsync(cancellationToken);
}
catch
{
return false;
}
try
{
using var sqliteConnection = new SQLiteConnection(connectionString.ConnectionString);
await sqliteConnection.OpenAsync(cancellationToken);
using var dbTransaction = sqliteConnection.BeginTransaction();
try
{
using var sqLiteCommand = new SQLiteCommand(RetrieveImageTagsSql, sqliteConnection, dbTransaction);
sqLiteCommand.Parameters.AddRange(new[]
{
new SQLiteParameter("@file", file)
});
sqLiteCommand.Prepare();
using var sqlDataReader = await sqLiteCommand.ExecuteReaderAsync(cancellationToken);
var tagWords = new HashSet<string>();
while (await sqlDataReader.ReadAsync(cancellationToken))
{
var tags = (string)sqlDataReader["Tags"];
if (string.IsNullOrEmpty(tags))
{
continue;
}
var databaseWords = new List<string>();
foreach (var word in new Csv(tags))
{
if (string.IsNullOrEmpty(word))
{
continue;
}
databaseWords.Add(word);
}
tagWords.UnionWith(databaseWords);
}
sqlDataReader.Close();
var suppliedWords = new List<string>();
foreach (var word in keywords)
{
if (string.IsNullOrEmpty(word))
{
continue;
}
suppliedWords.Add(word);
}
tagWords.UnionWith(suppliedWords);
var merged = new Csv(tagWords);
using var updateTagsSqliteCommand =
new SQLiteCommand(UpdateImageTagsSql, sqliteConnection, dbTransaction);
updateTagsSqliteCommand.Parameters.AddRange(new[]
{
new SQLiteParameter("@file", file),
new SQLiteParameter("@tags", merged)
});
updateTagsSqliteCommand.Prepare();
await updateTagsSqliteCommand.ExecuteNonQueryAsync(cancellationToken);
dbTransaction.Commit();
return true;
}
catch (Exception exception)
{
Log.Error(exception, "Could not insert.");
dbTransaction.Rollback();
}
return false;
}
finally
{
_databaseLock.Release();
}
}
public async IAsyncEnumerable<QuickImage> Search(IEnumerable<string> keywords, QuickImageSearchType searchType,
QuickImageSearchParameters searchParameters,
[EnumeratorCancellation] CancellationToken cancellationToken)
{
var stringComparer = StringComparer.OrdinalIgnoreCase;
var stringComparison = StringComparison.OrdinalIgnoreCase;
if (searchParameters.HasFlag(QuickImageSearchParameters.CaseSensitive))
{
stringComparer = StringComparer.Ordinal;
stringComparison = StringComparison.Ordinal;
}
var connectionString = new SQLiteConnectionStringBuilder
{
ConnectionString = DatabaseConnectionString
};
try
{
await _databaseLock.WaitAsync(cancellationToken);
}
catch
{
yield break;
}
try
{
using var sqliteConnection = new SQLiteConnection(connectionString.ConnectionString);
await sqliteConnection.OpenAsync(cancellationToken);
using var retrieveTagsIdsSqLiteCommand = new SQLiteCommand(RetrieveImagesSql, sqliteConnection);
retrieveTagsIdsSqLiteCommand.Prepare();
using var sqlDataReader = await retrieveTagsIdsSqLiteCommand.ExecuteReaderAsync(cancellationToken);
var searchWords = new HashSet<string>(keywords, stringComparer);
if (searchParameters.HasFlag(QuickImageSearchParameters.Split))
{
foreach (var keyword in new HashSet<string>(searchWords))
{
var split = Regex.Split(keyword,@"\s+", RegexOptions.Compiled);
foreach (var word in split)
{
if (string.IsNullOrEmpty(word))
{
continue;
}
searchWords.Add(word);
}
}
}
while (await sqlDataReader.ReadAsync(cancellationToken))
{
var file = (string)sqlDataReader["File"];
var fileName = Path.GetFileNameWithoutExtension(file);
var tags = (string)sqlDataReader["Tags"];
if (string.IsNullOrEmpty(tags))
{
continue;
}
// Partial match.
var databaseTags = new Csv(tags);
var databaseWords = new HashSet<string>(databaseTags);
if (searchParameters.HasFlag(QuickImageSearchParameters.Split))
{
foreach (var keyword in new List<string>(databaseWords))
{
var split = keyword.Split(' ');
foreach (var word in split)
{
if (string.IsNullOrEmpty(word))
{
continue;
}
databaseWords.Add(word);
}
}
}
switch (searchType)
{
case QuickImageSearchType.Any:
if (!searchWords.Intersect(databaseWords, stringComparer).Any() &&
!(searchParameters.HasFlag(QuickImageSearchParameters.Metadata) && searchWords.Any(tag => fileName.IndexOf(tag, stringComparison) != -1)))
{
continue;
}
break;
case QuickImageSearchType.All:
if (!searchWords.Except(databaseWords, stringComparer).Any() &&
!(searchParameters.HasFlag(QuickImageSearchParameters.Metadata) && searchWords.All(tag => fileName.IndexOf(tag, stringComparison) != -1)))
{
continue;
}
break;
}
var digest = Convert.FromBase64String((string)sqlDataReader["Hash"]);
var digestHash = new Digest
{
Coefficients = digest
};
Bitmap thumbnail = null;
if (!(sqlDataReader["Thumbnail"] is DBNull))
{
using var readStream = sqlDataReader.GetStream(4);
readStream.Position = 0L;
using var zipStream = new GZipStream(readStream, CompressionMode.Decompress);
using var image = Image.FromStream(zipStream);
thumbnail = new Bitmap(image);
}
yield return new QuickImage(file, digestHash, databaseTags, thumbnail);
}
}
finally
{
_databaseLock.Release();
}
}
public async Task<QuickImage> GetImageAsync(string file, CancellationToken cancellationToken)
{
var connectionString = new SQLiteConnectionStringBuilder
{
ConnectionString = DatabaseConnectionString
};
try
{
await _databaseLock.WaitAsync(cancellationToken);
}
catch
{
return null;
}
try
{
using var sqliteConnection = new SQLiteConnection(connectionString.ConnectionString);
await sqliteConnection.OpenAsync(cancellationToken);
using var sqLiteCommand = new SQLiteCommand(RetrieveImageSql, sqliteConnection);
sqLiteCommand.Parameters.AddRange(new[]
{
new SQLiteParameter("@file", file)
});
sqLiteCommand.Prepare();
using var sqlDataReader = await sqLiteCommand.ExecuteReaderAsync(cancellationToken);
while (await sqlDataReader.ReadAsync(cancellationToken))
{
var imageFile = (string)sqlDataReader["File"];
var imageTags = (string)sqlDataReader["Tags"];
var imageHash = (string)sqlDataReader["Hash"];
var digest = Convert.FromBase64String(imageHash);
var hash = new Digest()
{
Coefficients = digest
};
var imageKeywords = new Csv();
if (!string.IsNullOrEmpty(imageTags))
{
imageKeywords = new Csv(imageTags);
}
Bitmap thumbnail = null;
if (!(sqlDataReader["Thumbnail"] is DBNull))
{
using var readStream = sqlDataReader.GetStream(4);
readStream.Position = 0L;
using var zipStream = new GZipStream(readStream, CompressionMode.Decompress);
using var image = (Bitmap)Image.FromStream(zipStream);
thumbnail = new Bitmap(image);
}
return new QuickImage(imageFile, hash, imageKeywords, thumbnail);
}
}
finally
{
_databaseLock.Release();
}
return null;
}
public async IAsyncEnumerable<QuickImage> GetAll([EnumeratorCancellation] CancellationToken cancellationToken)
{
var connectionString = new SQLiteConnectionStringBuilder
{
ConnectionString = DatabaseConnectionString
};
try
{
await _databaseLock.WaitAsync(cancellationToken);
}
catch
{
yield break;
}
try
{
using var sqliteConnection = new SQLiteConnection(connectionString.ConnectionString);
await sqliteConnection.OpenAsync(cancellationToken);
using var retrieveTagsIdsSqLiteCommand = new SQLiteCommand(RetrieveImagesSql, sqliteConnection);
retrieveTagsIdsSqLiteCommand.Prepare();
using var sqlDataReader = await retrieveTagsIdsSqLiteCommand.ExecuteReaderAsync(cancellationToken);
while (await sqlDataReader.ReadAsync(cancellationToken))
{
var imageFile = (string)sqlDataReader["File"];
var imageTags = (string)sqlDataReader["Tags"];
var imageHash = (string)sqlDataReader["Hash"];
var digest =Convert.FromBase64String(imageHash);
var hash = new Digest()
{
Coefficients = digest
};
var imageKeywords = new Csv();
if (!string.IsNullOrEmpty(imageTags))
{
imageKeywords = new Csv(imageTags);
}
Bitmap thumbnail = null;
if (!(sqlDataReader["Thumbnail"] is DBNull))
{
using var readStream = sqlDataReader.GetStream(4);
readStream.Position = 0L;
using var zipStream = new GZipStream(readStream, CompressionMode.Decompress);
using var image = (Bitmap)Image.FromStream(zipStream);
thumbnail = new Bitmap(image);
}
yield return new QuickImage(imageFile, hash, imageKeywords, thumbnail);
}
}
finally
{
_databaseLock.Release();
}
}
public async Task SetFile(string file, Digest hash, CancellationToken cancellationToken)
{
var connectionString = new SQLiteConnectionStringBuilder
{
ConnectionString = DatabaseConnectionString
};
try
{
await _databaseLock.WaitAsync(cancellationToken);
}
catch
{
return;
}
try
{
using var sqliteConnection = new SQLiteConnection(connectionString.ConnectionString);
await sqliteConnection.OpenAsync(cancellationToken);
using var dbTransaction = sqliteConnection.BeginTransaction();
using var sqLiteCommand = new SQLiteCommand(UpdateFileHashSql, sqliteConnection, dbTransaction);
sqLiteCommand.Parameters.AddRange(new[]
{
new SQLiteParameter("@file", file),
new SQLiteParameter("@hash", Convert.ToBase64String(hash.Coefficients))
});
sqLiteCommand.Prepare();
try
{
await sqLiteCommand.ExecuteNonQueryAsync(cancellationToken);
dbTransaction.Commit();
}
catch (Exception exception)
{
Log.Error(exception, "Could not update hash.");
dbTransaction.Rollback();
}
}
finally
{
_databaseLock.Release();
}
}
public async Task<bool> SetFile(string from, string to, CancellationToken cancellationToken)
{
var connectionString = new SQLiteConnectionStringBuilder
{
ConnectionString = DatabaseConnectionString
};
try
{
await _databaseLock.WaitAsync(cancellationToken);
}
catch
{
return false;
}
try
{
using var sqliteConnection = new SQLiteConnection(connectionString.ConnectionString);
await sqliteConnection.OpenAsync(cancellationToken);
using var dbTransaction = sqliteConnection.BeginTransaction();
using var sqLiteCommand = new SQLiteCommand(UpdateFileSql, sqliteConnection, dbTransaction);
sqLiteCommand.Parameters.AddRange(new[]
{
new SQLiteParameter("@from", from),
new SQLiteParameter("@to", to)
});
sqLiteCommand.Prepare();
try
{
await sqLiteCommand.ExecuteNonQueryAsync(cancellationToken);
dbTransaction.Commit();
return true;
}
catch (Exception exception)
{
Log.Error(exception, "Could not update file.");
dbTransaction.Rollback();
}
}
finally
{
_databaseLock.Release();
}
return false;
}
public async Task SetHash(string file, Digest hash, CancellationToken cancellationToken)
{
var connectionString = new SQLiteConnectionStringBuilder
{
ConnectionString = DatabaseConnectionString
};
try
{
await _databaseLock.WaitAsync(cancellationToken);
}
catch
{
return;
}
try
{
using var sqliteConnection = new SQLiteConnection(connectionString.ConnectionString);
await sqliteConnection.OpenAsync(cancellationToken);
using var dbTransaction = sqliteConnection.BeginTransaction();
using var sqLiteCommand = new SQLiteCommand(UpdateImageHashSql, sqliteConnection, dbTransaction);
sqLiteCommand.Parameters.AddRange(new[]
{
new SQLiteParameter("@file", file),
new SQLiteParameter("@hash", Convert.ToBase64String(hash.Coefficients))
});
sqLiteCommand.Prepare();
try
{
await sqLiteCommand.ExecuteNonQueryAsync(cancellationToken);
dbTransaction.Commit();
}
catch (Exception exception)
{
Log.Error(exception, "Could not update hash.");
dbTransaction.Rollback();
}
}
finally
{
_databaseLock.Release();
}
}
public async Task SetThumbnail(string file, Bitmap thumbnail, CancellationToken cancellationToken)
{
var connectionString = new SQLiteConnectionStringBuilder
{
ConnectionString = DatabaseConnectionString
};
try
{
await _databaseLock.WaitAsync(cancellationToken);
}
catch
{
return;
}
try
{
using var bitmapMemoryStream = new MemoryStream();
using var bitmapZipStream =
new GZipStream(bitmapMemoryStream, CompressionMode.Compress,
true);
thumbnail.Save(bitmapZipStream, ImageFormat.Bmp);
bitmapZipStream.Close();
bitmapMemoryStream.Position = 9L;
using var sqliteConnection = new SQLiteConnection(connectionString.ConnectionString);
await sqliteConnection.OpenAsync(cancellationToken);
using var dbTransaction = sqliteConnection.BeginTransaction();
try
{
using (var sqLiteCommand =
new SQLiteCommand(UpdateImageThumbnailSql, sqliteConnection, dbTransaction))
{
sqLiteCommand.Parameters.AddRange(new[]
{
new SQLiteParameter("@file", file),
new SQLiteParameter("@thumbnailLength", bitmapMemoryStream.Length)
});
sqLiteCommand.Prepare();
await sqLiteCommand.ExecuteNonQueryAsync(cancellationToken);
}
using (var getLastRowInsertCommand =
new SQLiteCommand(GetLastRowInsertSql, sqliteConnection, dbTransaction))
{
getLastRowInsertCommand.Prepare();
var rowId =
(long)await getLastRowInsertCommand.ExecuteScalarAsync(cancellationToken);
using var sqliteBlob =
SQLiteBlob.Create(sqliteConnection, "main", "Images", "Thumbnail", rowId, false);
var fileMemoryStreamData = bitmapMemoryStream.ToArray();
sqliteBlob.Write(fileMemoryStreamData,
fileMemoryStreamData.Length,
0);
}
dbTransaction.Commit();
}
catch (Exception exception)
{
Log.Error(exception, "Could not update hash.");
dbTransaction.Rollback();
}
}
finally
{
_databaseLock.Release();
}
}
public async Task<Digest> GetHash(string file, CancellationToken cancellationToken)
{
var connectionString = new SQLiteConnectionStringBuilder
{
ConnectionString = DatabaseConnectionString
};
try
{
await _databaseLock.WaitAsync(cancellationToken);
}
catch
{
return null;
}
try
{
using var sqliteConnection = new SQLiteConnection(connectionString.ConnectionString);
await sqliteConnection.OpenAsync(cancellationToken);
using var sqLiteCommand = new SQLiteCommand(RetrieveImageHashSql, sqliteConnection);
sqLiteCommand.Parameters.AddRange(new[]
{
new SQLiteParameter("@file", file)
});
sqLiteCommand.Prepare();
using var sqlDataReader = await sqLiteCommand.ExecuteReaderAsync(cancellationToken);
while (await sqlDataReader.ReadAsync(cancellationToken))
{
var imageHash = (string)sqlDataReader["Hash"];
var digest = Convert.FromBase64String(imageHash);
var hash = new Digest()
{
Coefficients = digest
};
return hash;
}
}
finally
{
_databaseLock.Release();
}
return null;
}
public async IAsyncEnumerable<string> GetTags(string file, [EnumeratorCancellation] CancellationToken cancellationToken)
{
var connectionString = new SQLiteConnectionStringBuilder
{
ConnectionString = DatabaseConnectionString
};
try
{
await _databaseLock.WaitAsync(cancellationToken);
}
catch
{
yield break;
}
try
{
using var sqliteConnection = new SQLiteConnection(connectionString.ConnectionString);
await sqliteConnection.OpenAsync(cancellationToken);
using var sqLiteCommand = new SQLiteCommand(RetrieveImageTagsSql, sqliteConnection);
sqLiteCommand.Parameters.AddRange(new[]
{
new SQLiteParameter("@file", file)
});
sqLiteCommand.Prepare();
using var sqlDataReader = await sqLiteCommand.ExecuteReaderAsync(cancellationToken);
while (await sqlDataReader.ReadAsync(cancellationToken))
{
var tags = (string)sqlDataReader["Tags"];
if (string.IsNullOrEmpty(tags))
{
continue;
}
foreach (var keyword in new Csv(tags))
{
yield return keyword;
}
}
}
finally
{
_databaseLock.Release();
}
}
public async Task<bool> AddImageAsync(QuickImage destinationImage, CancellationToken cancellationToken)
{
return await AddImageAsync(destinationImage.File, destinationImage.Hash,destinationImage.Tags, (Bitmap)destinationImage.Thumbnail,
cancellationToken);
}
public async Task<bool> RemoveImageAsync(QuickImage image, CancellationToken cancellationToken)
{
return await RemoveImageAsync(image.File, cancellationToken);
}
}
}