/// <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 }