/// <summary>
/// Polyfill to account for the fact that EF Extensions BulkIndex only
/// works with SQLite and SQL Server.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="collection"></param>
/// <param name="itemsToDelete"></param>
/// <returns></returns>
public bool BulkDelete <T>(BaseDBModel db, DbSet <T> collection, List <T> itemsToDelete) where T : class
{
collection.RemoveRange(itemsToDelete);
int ret = db.SaveChanges("BulkSave");
return(ret == itemsToDelete.Count);
}
/// <summary>
/// Polyfill to account for the fact that EF Extensions BulkIndex only
/// works with SQLite and SQL Server.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="collection"></param>
/// <param name="itemsToSave"></param>
/// <returns></returns>
public bool BulkInsert <T>(BaseDBModel db, DbSet <T> collection, List <T> itemsToSave) where T : class
{
collection.AddRange(itemsToSave);
int ret = db.SaveChanges("BulkSave");
return(ret == itemsToSave.Count);
}
/// <summary>
/// Bulk insert weapper for the database specialisation type.
/// </summary>
/// <typeparam name="T">Type of the object to insert</typeparam>
/// <param name="db">DB model</param>
/// <param name="collection">DbSet into which we're inserting the objects</param>
/// <param name="itemsToSave">Objects to insert</param>
/// <returns>True if the insert succeeded</returns>
public bool BulkInsert <T>(BaseDBModel db, DbSet <T> collection, List <T> itemsToSave) where T : class
{
try
{
db.BulkInsert(itemsToSave);
return(true);
}
catch (Exception ex)
{
Logging.LogError($"Exception bulk inserting {typeof(T)}: {ex.Message}");
return(false);
}
}
/// <summary>
/// Enable SQLite performance improvements
/// </summary>
/// <param name="db"></param>
private void IncreasePerformance(BaseDBModel db)
{
// 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;");
// Store temporary tables in memory
ExecutePragma(db, "PRAGMA temp_store=MEMORY;");
}
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>
/// 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}");
}
}
/// <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 - creating DB. Exception: {0}", ex.Message);
db.Database.EnsureCreated();
}
// Always rebuild the FTS table at startup
FullTextTags(true);
Logging.Log("Running Sqlite DB optimisation...");
db.Database.ExecuteSqlRaw("VACUUM;");
Logging.Log("DB optimisation complete.");
IncreasePerformance(db);
}
/// <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 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
{
db.BulkDelete(itemsToDelete);
success = true;
}
catch (Exception ex)
{
Logging.LogError("Exception during bulk delete: {0}", ex.Message);
}
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 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
{
db.BulkUpdate(itemsToSave);
success = true;
}
catch (Exception ex)
{
Logging.LogError("Exception during bulk update: {0}", ex.Message);
}
return(success);
}
/// <summary>
/// Bulk insert weapper for the database specialisation type.
/// </summary>
/// <typeparam name="T">Type of the object to insert</typeparam>
/// <param name="db">DB model</param>
/// <param name="collection">DbSet into which we're inserting the objects</param>
/// <param name="itemsToDelete">Objects to insert</param>
/// <returns>True if the insert succeeded</returns>
public bool BulkDelete <T>(BaseDBModel db, DbSet <T> collection, List <T> itemsToDelete) where T : class
{
return(DatabaseSpecialisation.BulkDelete(db, collection, itemsToDelete));
}
public void FlushDBWriteCache(BaseDBModel db)
{
ExecutePragma(db, "PRAGMA schema.wal_checkpoint;");
}
public void FlushDBWriteCache(BaseDBModel db)
{
// No-op
}
