/// <summary>
/// SQLite bulk insert uses EF Extensions BulkIndex.
/// This would also work for SQLServer.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="collection"></param>
/// <param name="itemsToSave"></param>
/// <returns></returns>
public async Task <bool> BulkInsert <T>(BaseDBModel db, DbSet <T> collection, List <T> itemsToSave) where T : class
{
if (BaseDBModel.ReadOnly)
{
Logging.LogVerbose("Read-only mode - no data will be inserted.");
return(true);
}
bool success = false;
try
{
var bulkConfig = new BulkConfig {
SetOutputIdentity = true, BatchSize = 500
};
await db.BulkInsertAsync(itemsToSave, bulkConfig);
success = true;
}
catch (Exception ex)
{
Logging.LogError($"Exception during bulk insert: {ex}");
}
return(success);
}
/// <summary>
/// SQLite bulk update uses EF Extensions BulkUpdate.
/// This would also work for SQLServer.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="collection"></param>
/// <param name="itemsToSave"></param>
/// <returns></returns>
public async Task <bool> BulkUpdate <T>(BaseDBModel db, DbSet <T> collection, List <T> itemsToSave) where T : class
{
// TODO make this method protected and then move this check to the base class
if (BaseDBModel.ReadOnly)
{
Logging.LogVerbose("Read-only mode - no data will be updated.");
return(true);
}
bool success = false;
try
{
//collection.UpdateRange(itemsToSave);
//await db.SaveChangesAsync();
await db.BulkUpdateAsync(itemsToSave);
success = true;
}
catch (Exception ex)
{
Logging.LogError($"Exception during bulk update: {ex}");
}
return(success);
}
/// <summary>
/// SQLite bulk delete uses EF Extensions BulkDelete.
/// This would also work for SQLServer.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="collection"></param>
/// <param name="itemsToDelete"></param>
/// <returns></returns>
public async Task <bool> BulkDelete <T>(BaseDBModel db, DbSet <T> collection, List <T> itemsToDelete) where T : class
{
if (BaseDBModel.ReadOnly)
{
Logging.LogVerbose("Read-only mode - no data will be deleted.");
return(true);
}
bool success = false;
try
{
//collection.RemoveRange(itemsToDelete);
//await db.SaveChangesAsync();
await db.BulkDeleteAsync(itemsToDelete);
success = true;
}
catch (Exception ex)
{
Logging.LogError($"Exception during bulk delete: {ex}");
}
return(success);
}
/// <summary>
/// Enable SQLite performance improvements
/// </summary>
/// <param name="db"></param>
private void IncreasePerformance(BaseDBModel db)
{
// Increase the timeout from the default (which I think is 30s)
// To help concurrency.
db.Database.SetCommandTimeout(60);
// Enable journal mode - this will also improve
// concurrent acces
ExecutePragma(db, "PRAGMA journal_mode=WAL;");
// Turn off Synchronous mode. This means that writes aren't
// sync'd to disk every single time.
ExecutePragma(db, "PRAGMA synchronous=OFF;");
// Increate the cache page size TODO: check this value
ExecutePragma(db, "PRAGMA cache_size=10000;");
// Use a shared cache - good for multi-threaded access
ExecutePragma(db, "PRAGMA cache=shared;");
// Allow reading from the cache. Means we might get stale
// data, but in most cases that's fine and concurrency will
// be improved.
ExecutePragma(db, "PRAGMA read_uncommitted=true;");
// Store temporary tables in memory
ExecutePragma(db, "PRAGMA temp_store=MEMORY;");
// Massive hack....
Logging.LogTrace("Deleting corrupt ImageMetaData entries");
db.Database.ExecuteSqlRaw("delete from imagemetadata where Lastupdated = 1998;");
Logging.Log("Running Sqlite DB optimisation...");
db.Database.ExecuteSqlRaw("VACUUM;");
Logging.Log("DB optimisation complete.");
RebuildFreeText(db);
}
public static string GetTableWith(BaseDBModel dBModel)
{
if (string.IsNullOrEmpty(dBModel.GetTableWith()))
{
return(string.Empty);
}
return($" with({dBModel.GetTableWith()}) ");
}
public JoinQueryInfo(BaseDBModel dBModel, string joinStr, int joinTableCount, Dictionary <string, string> map, string where, DynamicParameters parameters)
{
JoinStr = joinStr;
JoinTableCount = joinTableCount;
Map = map;
Where = where;
Param = parameters;
DBModel = dBModel;
}
/// <summary>
/// Postgres bulk delete uses EF Extensions BulkDelete.
/// This would also work for SQLServer.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="collection"></param>
/// <param name="itemsToDelete"></param>
/// <returns></returns>
public async Task <bool> BulkDelete <T>(BaseDBModel db, DbSet <T> collection, List <T> itemsToDelete) where T : class
{
// TODO make this method protected and then move this check to the base class
if (BaseDBModel.ReadOnly)
{
Logging.LogVerbose("Read-only mode - no data will be deleted.");
return(true);
}
collection.RemoveRange(itemsToDelete);
int ret = await db.SaveChangesAsync("BulkSave");
return(ret == itemsToDelete.Count);
}
/// <summary>
/// SQLite pragma execution.
/// </summary>
/// <param name="db"></param>
/// <param name="pragmaCommand"></param>
private void ExecutePragma(BaseDBModel db, string pragmaCommand)
{
try
{
var connection = db.Database.GetDbConnection();
connection.Open();
using var command = connection.CreateCommand();
command.CommandText = pragmaCommand;
command.ExecuteNonQuery();
}
catch (Exception ex)
{
Logging.LogWarning($"Unable to execute pragma command {pragmaCommand}: {ex.Message}");
}
}
private void RebuildFreeText(BaseDBModel db)
{
const string delete = @"DELETE from FTSKeywords; DELETE from FTSImages; DELETE from FTSNames;";
const string insertTags = @"INSERT INTO FTSKeywords (TagId, Keyword) SELECT t.TagId, t.Keyword FROM Tags t;";
const string insertPeople = @"INSERT INTO FTSNames (PersonID, Name) SELECT PersonId, Name FROM people p where p.State = 1;";
const string insertImages = @"INSERT INTO FTSImages ( ImageId, Caption, Description, Copyright, Credit )
SELECT i.ImageId, i.Caption, i.Description, i.CopyRight, i.Credit FROM imagemetadata i
WHERE (coalesce(i.Caption, '') <> '' OR coalesce(i.Description, '') <> ''
OR coalesce(i.Copyright, '') <> '' OR coalesce(i.Credit, '') <> '');";
string sql = $"{delete} {insertTags} {insertPeople} {insertImages}";
Logging.LogVerbose("Rebuilding Free Text Index.");
db.Database.ExecuteSqlRaw(sql);
Logging.Log("Full-text search index rebuilt.");
}
public void Init(BaseDBModel db)
{
try
{
Logging.Log("Running MySql DB migrations...");
// TODO MySQL doesn't support migrations?! - remove this big hammer
db.Database.EnsureDeleted();
db.Database.EnsureCreated();
}
catch (Exception ex)
{
Logging.LogWarning("Migrations failed - creating DB. Exception: {0}", ex.Message);
db.Database.EnsureCreated();
}
}
/// <summary>
/// Postgres bulk insert uses EF Extensions BulkIndex.
/// This would also work for SQLServer.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="collection"></param>
/// <param name="itemsToSave"></param>
/// <returns></returns>
public async Task <bool> BulkInsert <T>(BaseDBModel db, DbSet <T> collection, List <T> itemsToSave) where T : class
{
// TODO make this method protected and then move this check to the base class
if (BaseDBModel.ReadOnly)
{
Logging.LogVerbose("Read-only mode - no data will be updated.");
return(true);
}
collection.AddRange(itemsToSave);
// TODO: Set output identity here.
int ret = await db.SaveChangesAsync("BulkSave");
return(ret == itemsToSave.Count);
}
private static string GetSql(BaseDBModel obj, ICollection <string> keys, string addPre, string tableAlias = "")
{
if (!string.IsNullOrEmpty(tableAlias))
{
tableAlias += ".";
}
StringBuilder sql = new StringBuilder();
foreach (var k in keys)
{
if (sql.Length > 0)
{
sql.Append($" {addPre} ");
}
sql.Append($"{tableAlias}{obj.GetDBModel_SqlProvider().GetProviderOption().OpenQuote}{k}{obj.GetDBModel_SqlProvider().GetProviderOption().CloseQuote}=@{k}");
}
return(sql.ToString());
}
/// <summary>
/// Basic implementation that inserts or updates based on whether the key/ID is zero or not.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="db"></param>
/// <param name="collection"></param>
/// <param name="itemsToSave"></param>
/// <param name="getKey"></param>
/// <returns></returns>
public async Task <bool> BulkInsertOrUpdate <T>(BaseDBModel db, DbSet <T> collection, List <T> itemsToSave, Func <T, bool> isNew) where T : class
{
var result = false;
itemsToSave.ForEach(x => { if (isNew(x))
{
collection.Add(x);
}
else
{
collection.Update(x);
} });
if (await db.SaveChangesAsync("BulkInsertOrUpdate") > 0)
{
result = true;
}
return(result);
}
/// <summary>
/// SQLite specific initialisation. Run the migrations, and
/// always run a VACUUM to optimise the DB at startup.
/// </summary>
/// <param name="db"></param>
public void Init(BaseDBModel db)
{
try
{
Logging.Log("Running Sqlite DB migrations...");
db.Database.Migrate();
}
catch (Exception ex)
{
Logging.LogWarning($"Migrations failed with exception: {ex}");
if (ex.InnerException != null)
{
Logging.LogWarning($"InnerException: {ex.InnerException}");
}
Logging.Log($"Creating DB.");
db.Database.EnsureCreated();
}
IncreasePerformance(db);
}
/// <summary>
/// Postgres specific initialisation. Run the migrations, and
/// always run a VACUUM to optimise the DB at startup.
/// </summary>
/// <param name="db"></param>
public void Init(BaseDBModel db)
{
try
{
Logging.Log("Running Postgres DB migrations...");
db.Database.Migrate();
}
catch (Exception ex)
{
Logging.LogWarning("Migrations failed - creating DB. Exception: {0}", ex.Message);
try
{
db.Database.EnsureCreated();
}
catch (Exception ex2)
{
Logging.LogError("Database creation failed. Exception: {0}", ex2.Message);
}
}
IncreasePerformance(db);
}
/// <summary>
/// Enable Postgres performance improvements
/// </summary>
/// <param name="db"></param>
private void IncreasePerformance(BaseDBModel db)
{
// Nothing yet for postgres
}
/// <summary>
/// Use the EF BulkExtensions to implement this.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="db"></param>
/// <param name="collection"></param>
/// <param name="itemsToSave"></param>
/// <param name="getKey"></param>
/// <returns></returns>
public Task <bool> BulkInsertOrUpdate <T>(BaseDBModel db, DbSet <T> collection, List <T> itemsToSave, Func <T, bool> getKey) where T : class
{
throw new NotImplementedException();
}
/// <summary>
/// Process the startup args and initialise the logging.
/// </summary>
/// <param name="o"></param>
/// <param name="args"></param>
private static void Startup(DamselflyOptions o, string[] args)
{
Logging.Verbose = o.Verbose;
Logging.Trace = o.Trace;
if (Directory.Exists(o.SourceDirectory))
{
if (!Directory.Exists(o.ConfigPath))
{
Directory.CreateDirectory(o.ConfigPath);
}
Logging.LogFolder = Path.Combine(o.ConfigPath, "logs");
Log.Logger = Logging.InitLogs();
if (o.ReadOnly)
{
o.NoEnableIndexing = true;
o.NoGenerateThumbnails = true;
}
// TODO: Do away with static members here. We should pass this
// through to the config service and pick them up via DI
IndexingService.EnableIndexing = !o.NoEnableIndexing;
IndexingService.RootFolder = o.SourceDirectory;
ThumbnailService.PicturesRoot = o.SourceDirectory;
ThumbnailService.Synology = o.Synology;
ThumbnailService.SetThumbnailRoot(o.ThumbPath);
ThumbnailService.EnableThumbnailGeneration = !o.NoGenerateThumbnails;
var tieredPGO = System.Environment.GetEnvironmentVariable("DOTNET_TieredPGO") == "1";
Logging.Log("Startup State:");
Logging.Log($" Damselfly Ver: {Assembly.GetExecutingAssembly().GetName().Version}");
Logging.Log($" CLR Ver: {Environment.Version}");
Logging.Log($" OS: {Environment.OSVersion}");
Logging.Log($" CPU Arch: {RuntimeInformation.ProcessArchitecture}");
Logging.Log($" Processor Count: {Environment.ProcessorCount}");
Logging.Log($" Read-only mode: {o.ReadOnly}");
Logging.Log($" Synology = {o.Synology}");
Logging.Log($" Indexing = {!o.NoEnableIndexing}");
Logging.Log($" ThumbGen = {!o.NoGenerateThumbnails}");
Logging.Log($" Images Root set as {o.SourceDirectory}");
Logging.Log($" TieredPGO Enabled={tieredPGO}");
IDataBase dbType = null;
if (!o.UsePostgresDB)
{
string dbFolder = Path.Combine(o.ConfigPath, "db");
if (!Directory.Exists(dbFolder))
{
Logging.Log(" Created DB folder: {0}", dbFolder);
Directory.CreateDirectory(dbFolder);
}
string dbPath = Path.Combine(dbFolder, "damselfly.db");
dbType = new SqlLiteModel(dbPath);
Logging.Log(" Sqlite Database location: {0}", dbPath);
}
else // Postgres
{
// READ Postgres config json
dbType = PostgresModel.ReadSettings("settings.json");
Logging.Log(" Postgres Database location: {0}");
}
// TODO: https://docs.microsoft.com/en-us/ef/core/managing-schemas/migrations/providers?tabs=dotnet-core-cli
BaseDBModel.InitDB <ImageContext>(dbType, o.ReadOnly);
// Make ourselves low-priority.
System.Diagnostics.Process.GetCurrentProcess().PriorityClass = System.Diagnostics.ProcessPriorityClass.Idle;
StartWebServer(o.Port, args);
Logging.Log("Shutting down.");
}
else
{
Console.WriteLine("Folder {0} did not exist. Exiting.", o.SourceDirectory);
}
}
public static string GetTableName(BaseDBModel model)
{
return(string.Format(model.GetDBModel_TableName(), model.GetShuffledModel().TableId));
}
public void FlushDBWriteCache(BaseDBModel db)
{
ExecutePragma(db, "PRAGMA schema.wal_checkpoint;");
}
public void FlushDBWriteCache(BaseDBModel db)
{
//
}
public static void Main(string[] args)
{
Parser.Default.ParseArguments <DamselflyOptions>(args).WithParsed(o =>
{
Logging.Verbose = o.Verbose;
Logging.Trace = o.Trace;
if (Directory.Exists(o.SourceDirectory))
{
if (!Directory.Exists(o.ConfigPath))
{
Directory.CreateDirectory(o.ConfigPath);
}
Logging.LogFolder = Path.Combine(o.ConfigPath, "logs");
Log.Logger = Logging.InitLogs();
if (o.ReadOnly)
{
o.NoEnableIndexing = true;
o.NoGenerateThumbnails = true;
}
ImageProcessService.UseImageSharp = o.ImageSharp;
IndexingService.EnableIndexing = !o.NoEnableIndexing;
IndexingService.EnableThumbnailGeneration = !o.NoGenerateThumbnails;
IndexingService.RootFolder = o.SourceDirectory;
ThumbnailService.PicturesRoot = o.SourceDirectory;
ThumbnailService.Synology = o.Synology;
ThumbnailService.SetThumbnailRoot(o.ThumbPath);
Logging.Log("Startup State:");
Logging.Log($" Damselfly Ver: {Assembly.GetExecutingAssembly().GetName().Version}");
Logging.Log($" CLR Ver: {Environment.Version}");
Logging.Log($" OS: {Environment.OSVersion}");
Logging.Log($" CPU Arch: {RuntimeInformation.ProcessArchitecture}");
Logging.Log($" Processor Count: {Environment.ProcessorCount}");
Logging.Log($" Read-only mode: {o.ReadOnly}");
Logging.Log($" Synology = {o.Synology}");
Logging.Log($" Indexing = {!o.NoEnableIndexing}");
Logging.Log($" ThumbGen = {!o.NoGenerateThumbnails}");
Logging.Log($" Images Root set as {o.SourceDirectory}");
IDataBase dbType = null;
//if (true) // SQLite Check
{
string dbFolder = Path.Combine(o.ConfigPath, "db");
if (!Directory.Exists(dbFolder))
{
Logging.Log(" Created DB folder: {0}", dbFolder);
Directory.CreateDirectory(dbFolder);
}
string dbPath = Path.Combine(dbFolder, "damselfly.db");
Logging.Log(" Sqlite Database location: {0}", dbPath);
dbType = new SqlLiteModel(dbPath);
}
//else // MySql
//{
// dbType = new MySqlModel();
//}
BaseDBModel.InitDB <ImageContext>(dbType, o.ReadOnly);
StartWebServer(o.Port, args);
Logging.Log("Shutting down.");
}
else
{
Logging.Log("Folder {0} did not exist. Exiting.", o.SourceDirectory);
}
});
}