/// <summary> /// Assigns permissions to an entity for multiple group/permission entries /// </summary> /// <param name="permissionSet"> /// </param> /// <remarks> /// This will first clear the permissions for this entity then re-create them /// </remarks> public void ReplaceEntityPermissions(EntityPermissionSet permissionSet) { IUmbracoDatabase db = AmbientScope.Database; db.Execute("DELETE FROM umbracoUserGroup2Node WHERE nodeId = @nodeId", new { nodeId = permissionSet.EntityId }); db.Execute("DELETE FROM umbracoUserGroup2NodePermission WHERE nodeId = @nodeId", new { nodeId = permissionSet.EntityId }); var toInsert = new List <UserGroup2NodeDto>(); var toInsertPermissions = new List <UserGroup2NodePermissionDto>(); foreach (EntityPermission entityPermission in permissionSet.PermissionsSet) { toInsert.Add(new UserGroup2NodeDto { NodeId = permissionSet.EntityId, UserGroupId = entityPermission.UserGroupId }); foreach (var permission in entityPermission.AssignedPermissions) { toInsertPermissions.Add(new UserGroup2NodePermissionDto { NodeId = permissionSet.EntityId, Permission = permission, UserGroupId = entityPermission.UserGroupId }); } } db.BulkInsertRecords(toInsert); db.BulkInsertRecords(toInsertPermissions); }
/// <summary> /// Assigns the same permission set for a single group to any number of entities /// </summary> /// <param name="groupId"></param> /// <param name="permissions"></param> /// <param name="entityIds"></param> /// <remarks> /// This will first clear the permissions for this user and entities and recreate them /// </remarks> public void ReplacePermissions(int groupId, IEnumerable <char> permissions, params int[] entityIds) { if (entityIds.Length == 0) { return; } IUmbracoDatabase db = AmbientScope.Database; var sql = "DELETE FROM umbracoUserGroup2NodePermission WHERE userGroupId = @groupId AND nodeId in (@nodeIds)"; foreach (IEnumerable <int> group in entityIds.InGroupsOf(Constants.Sql.MaxParameterCount)) { db.Execute(sql, new { groupId, nodeIds = group }); } var toInsert = new List <UserGroup2NodePermissionDto>(); foreach (var p in permissions) { foreach (var e in entityIds) { toInsert.Add(new UserGroup2NodePermissionDto { NodeId = e, Permission = p.ToString(CultureInfo.InvariantCulture), UserGroupId = groupId }); } } db.BulkInsertRecords(toInsert); }
/// <summary> /// Bulk-insert records using SqlServer BulkCopy method. /// </summary> /// <typeparam name="T">The type of the records.</typeparam> /// <param name="database">The database.</param> /// <param name="pocoData">The PocoData object corresponding to the record's type.</param> /// <param name="records">The records.</param> /// <returns>The number of records that were inserted.</returns> internal static int BulkInsertRecordsSqlServer <T>(IUmbracoDatabase database, PocoData pocoData, IEnumerable <T> records) { // create command against the original database.Connection using (var command = database.CreateCommand(database.Connection, CommandType.Text, string.Empty)) { // use typed connection and transactionf or SqlBulkCopy var tConnection = GetTypedConnection <SqlConnection>(database.Connection); var tTransaction = GetTypedTransaction <SqlTransaction>(command.Transaction); var tableName = pocoData.TableInfo.TableName; var syntax = database.SqlContext.SqlSyntax as SqlServerSyntaxProvider; if (syntax == null) { throw new NotSupportedException("SqlSyntax must be SqlServerSyntaxProvider."); } using (var copy = new SqlBulkCopy(tConnection, SqlBulkCopyOptions.Default, tTransaction) { BulkCopyTimeout = 10000, DestinationTableName = tableName }) using (var bulkReader = new PocoDataDataReader <T, SqlServerSyntaxProvider>(records, pocoData, syntax)) { copy.WriteToServer(bulkReader); return(bulkReader.RecordsAffected); } } }
/// <summary> /// Assigns one permission to an entity for multiple groups /// </summary> /// <param name="entity"></param> /// <param name="permission"></param> /// <param name="groupIds"></param> public void AssignEntityPermission(TEntity entity, char permission, IEnumerable <int> groupIds) { IUmbracoDatabase db = AmbientScope.Database; var groupIdsA = groupIds.ToArray(); db.Execute("DELETE FROM umbracoUserGroup2Node WHERE nodeId = @nodeId AND userGroupId in (@groupIds)", new { nodeId = entity.Id, groupIds = groupIdsA }); db.Execute("DELETE FROM umbracoUserGroup2NodePermission WHERE nodeId = @nodeId AND permission = @permission AND userGroupId in (@groupIds)", new { nodeId = entity.Id, permission = permission.ToString(CultureInfo.InvariantCulture), groupIds = groupIdsA }); UserGroup2NodePermissionDto[] actionsPermissions = groupIdsA.Select(id => new UserGroup2NodePermissionDto { NodeId = entity.Id, Permission = permission.ToString(CultureInfo.InvariantCulture), UserGroupId = id }).ToArray(); UserGroup2NodeDto[] actions = groupIdsA.Select(id => new UserGroup2NodeDto { NodeId = entity.Id, UserGroupId = id }).ToArray(); db.BulkInsertRecords(actions); db.BulkInsertRecords(actionsPermissions); }
/// <summary> /// Initializes a new instance of the <see cref="CreatedPackageSchemaRepository"/> class. /// </summary> public CreatedPackageSchemaRepository( IUmbracoDatabase umbracoDatabase, IHostingEnvironment hostingEnvironment, IOptions <GlobalSettings> globalSettings, FileSystems fileSystems, IEntityXmlSerializer serializer, IDataTypeService dataTypeService, ILocalizationService localizationService, IFileService fileService, IMediaService mediaService, IMediaTypeService mediaTypeService, IContentService contentService, MediaFileManager mediaFileManager, IMacroService macroService, IContentTypeService contentTypeService, string mediaFolderPath = null, string tempFolderPath = null) { _umbracoDatabase = umbracoDatabase; _hostingEnvironment = hostingEnvironment; _fileSystems = fileSystems; _serializer = serializer; _dataTypeService = dataTypeService; _localizationService = localizationService; _fileService = fileService; _mediaService = mediaService; _mediaTypeService = mediaTypeService; _contentService = contentService; _mediaFileManager = mediaFileManager; _macroService = macroService; _contentTypeService = contentTypeService; _xmlParser = new PackageDefinitionXmlParser(); _createdPackagesFolderPath = mediaFolderPath ?? Constants.SystemDirectories.CreatedPackages; _tempFolderPath = tempFolderPath ?? Constants.SystemDirectories.TempData + "/PackageFiles"; }
/// <summary> /// Bulk-insert records using SqlServer BulkCopy method. /// </summary> /// <typeparam name="T">The type of the records.</typeparam> /// <param name="database">The database.</param> /// <param name="pocoData">The PocoData object corresponding to the record's type.</param> /// <param name="records">The records.</param> /// <returns>The number of records that were inserted.</returns> internal static int BulkInsertRecordsSqlServer <T>(IUmbracoDatabase database, PocoData pocoData, IEnumerable <T> records) { // create command against the original database.Connection using (var command = database.CreateCommand(database.Connection, CommandType.Text, string.Empty)) { // use typed connection and transactionf or SqlBulkCopy var tConnection = GetTypedConnection <SqlConnection>(database.Connection); var tTransaction = GetTypedTransaction <SqlTransaction>(command.Transaction); var tableName = pocoData.TableInfo.TableName; var syntax = database.SqlContext.SqlSyntax as SqlServerSyntaxProvider; if (syntax == null) { throw new NotSupportedException("SqlSyntax must be SqlServerSyntaxProvider."); } using (var copy = new SqlBulkCopy(tConnection, SqlBulkCopyOptions.Default, tTransaction) { BulkCopyTimeout = 10000, DestinationTableName = tableName }) using (var bulkReader = new PocoDataDataReader <T, SqlServerSyntaxProvider>(records, pocoData, syntax)) { //we need to add column mappings here because otherwise columns will be matched by their order and if the order of them are different in the DB compared //to the order in which they are declared in the model then this will not work, so instead we will add column mappings by name so that this explicitly uses //the names instead of their ordering. foreach (var col in bulkReader.ColumnMappings) { copy.ColumnMappings.Add(col.DestinationColumn, col.DestinationColumn); } copy.WriteToServer(bulkReader); return(bulkReader.RecordsAffected); } } }
private void ObtainWriteLock() { IUmbracoDatabase db = _parent._scopeAccessor.Value.AmbientScope.Database; if (!db.InTransaction) { throw new InvalidOperationException("SqlServerDistributedLockingMechanism requires a transaction to function."); } if (db.Transaction.IsolationLevel < IsolationLevel.ReadCommitted) { throw new InvalidOperationException("A transaction with minimum ReadCommitted isolation level is required."); } const string query = @"UPDATE umbracoLock WITH (REPEATABLEREAD) SET value = (CASE WHEN (value=1) THEN -1 ELSE 1 END) WHERE id=@id"; db.Execute("SET LOCK_TIMEOUT " + _timeout.TotalMilliseconds + ";"); var i = db.Execute(query, new { id = LockId }); if (i == 0) { // ensure we are actually locking! throw new ArgumentException($"LockObject with id={LockId} does not exist."); } }
/// <summary> /// Initializes a new instance of the <see cref="MigrationContext"/> class. /// </summary> public MigrationContext(MigrationPlan plan, IUmbracoDatabase database, ILogger <MigrationContext> logger) { Plan = plan; Database = database ?? throw new ArgumentNullException(nameof(database)); Logger = logger ?? throw new ArgumentNullException(nameof(logger)); _postMigrations.AddRange(plan.PostMigrationTypes); }
// Only one writer is possible at a time // lock occurs for entire database as opposed to row/table. private void ObtainWriteLock() { IUmbracoDatabase db = _parent._scopeAccessor.Value.AmbientScope.Database; if (!db.InTransaction) { throw new InvalidOperationException("SqliteDistributedLockingMechanism requires a transaction to function."); } var query = @$ "UPDATE umbracoLock SET value = (CASE WHEN (value=1) THEN -1 ELSE 1 END) WHERE id = {LockId}"; DbCommand command = db.CreateCommand(db.Connection, CommandType.Text, query); // imagine there is an existing writer, whilst elapsed time is < command timeout sqlite will busy loop command.CommandTimeout = _timeout.Seconds; try { var i = command.ExecuteNonQuery(); if (i == 0) { // ensure we are actually locking! throw new ArgumentException($"LockObject with id={LockId} does not exist."); } } catch (SqliteException ex) when(ex.IsBusyOrLocked()) { throw new DistributedWriteLockTimeoutException(LockId); } }
/// <summary> /// Ensure that the last instruction that was processed is still in the database. /// </summary> /// <remarks> /// If the last instruction is not in the database anymore, then the messenger /// should not try to process any instructions, because some instructions might be lost, /// and it should instead cold-boot. /// However, if the last synced instruction id is '0' and there are '0' records, then this indicates /// that it's a fresh site and no user actions have taken place, in this circumstance we do not want to cold /// boot. See: http://issues.umbraco.org/issue/U4-8627 /// </remarks> private void EnsureInstructions(IUmbracoDatabase database) { if (_lastId == 0) { var sql = Sql().Select("COUNT(*)") .From <CacheInstructionDto>(); var count = database.ExecuteScalar <int>(sql); //if there are instructions but we haven't synced, then a cold boot is necessary if (count > 0) { _lastId = -1; } } else { var sql = Sql().SelectAll() .From <CacheInstructionDto>() .Where <CacheInstructionDto>(dto => dto.Id == _lastId); var dtos = database.Fetch <CacheInstructionDto>(sql); //if the last synced instruction is not found in the db, then a cold boot is necessary if (dtos.Count == 0) { _lastId = -1; } } }
// fixme is this needed? private void CloseDbConnections(IUmbracoDatabase database) { //Ensure that any database connections from a previous test is disposed. //This is really just double safety as its also done in the TearDown. database?.Dispose(); //SqlCeContextGuardian.CloseBackgroundConnection(); }
/// <summary> /// Safely inserts a record, or updates if it exists, based on a unique constraint. /// </summary> /// <param name="db"></param> /// <param name="poco"></param> /// <param name="updateArgs"></param> /// <param name="updateCommand">If the entity has a composite key they you need to specify the update command explicitly</param> /// <returns>The action that executed, either an insert or an update. If an insert occurred and a PK value got generated, the poco object /// passed in will contain the updated value.</returns> /// <remarks> /// <para>We cannot rely on database-specific options such as MySql ON DUPLICATE KEY UPDATE or MSSQL MERGE WHEN MATCHED because SQLCE /// does not support any of them. Ideally this should be achieved with proper transaction isolation levels but that would mean revisiting /// isolation levels globally. We want to keep it simple for the time being and manage it manually.</para> /// <para>We handle it by trying to update, then insert, etc. until something works, or we get bored.</para> /// <para>Note that with proper transactions, if T2 begins after T1 then we are sure that the database will contain T2's value /// once T1 and T2 have completed. Whereas here, it could contain T1's value.</para> /// </remarks> internal static RecordPersistenceType InsertOrUpdate <T>(this IUmbracoDatabase db, T poco, string updateCommand, object updateArgs) where T : class { if (poco == null) { throw new ArgumentNullException(nameof(poco)); } // fixme - NPoco has a Save method that works with the primary key // in any case, no point trying to update if there's no primary key! // try to update var rowCount = updateCommand.IsNullOrWhiteSpace() ? db.Update(poco) : db.Update <T>(updateCommand, updateArgs); if (rowCount > 0) { return(RecordPersistenceType.Update); } // failed: does not exist, need to insert // RC1 race cond here: another thread may insert a record with the same constraint var i = 0; while (i++ < 4) { try { // try to insert db.Insert(poco); return(RecordPersistenceType.Insert); } catch (SqlException) // assuming all db engines will throw that exception { // failed: exists (due to race cond RC1) // RC2 race cond here: another thread may remove the record // try to update rowCount = updateCommand.IsNullOrWhiteSpace() ? db.Update(poco) : db.Update <T>(updateCommand, updateArgs); if (rowCount > 0) { return(RecordPersistenceType.Update); } // failed: does not exist (due to race cond RC2), need to insert // loop } } // this can go on forever... have to break at some point and report an error. throw new DataException("Record could not be inserted or updated."); }
private void RemoveDatabaseFile(IUmbracoDatabase database, Action <Exception> onFail = null) { if (database != null) { CloseDbConnections(database); } RemoveDatabaseFile(onFail); }
public static void UpdateCache(IUmbracoDatabase database) { if (lastUpdate < DateTime.Now && lastUpdate <= DateTime.Now.AddMinutes(-5)) { Keys = new ApiKeysService(database).GetAll().Result.ToList(); lastUpdate = DateTime.Now; } }
private CreateBuilder GetBuilder(IUmbracoDatabase db) { var logger = GetRequiredService <ILogger <MigrationContext> >(); var ctx = new MigrationContext(new TestPlan(), db, logger); return(new CreateBuilder(ctx)); }
public async Task <bool> AcquireLockAsync(int millisecondsTimeout) { if (!_dbFactory.Configured) { // if we aren't configured then we're in an install state, in which case we have no choice but to assume we can acquire return(true); } _logger.LogDebug("Acquiring lock..."); var tempId = Guid.NewGuid().ToString(); IUmbracoDatabase db = null; try { db = _dbFactory.CreateDatabase(); _hasTable = db.HasTable(Cms.Core.Constants.DatabaseSchema.Tables.KeyValue); if (!_hasTable) { _logger.LogDebug("The DB does not contain the required table so we must be in an install state. We have no choice but to assume we can acquire."); _acquireWhenTablesNotAvailable = true; return(true); } db.BeginTransaction(IsolationLevel.Serializable); var result = InsertLockRecord(tempId, db); //we change the row to a random Id to signal other MainDom to shutdown if (result == RecordPersistenceType.Insert) { // if we've inserted, then there was no MainDom so we can instantly acquire InsertLockRecord(_lockId, db); // so update with our appdomain id _logger.LogDebug("Acquired with ID {LockId}", _lockId); return(true); } // if we've updated, this means there is an active MainDom, now we need to wait to // for the current MainDom to shutdown which also requires releasing our write lock } catch (Exception ex) { // unexpected _logger.LogError(ex, "Unexpected error, cannot acquire MainDom"); _errorDuringAcquiring = true; return(false); } finally { db?.CompleteTransaction(); db?.Dispose(); } return(await WaitForExistingAsync(tempId, millisecondsTimeout)); }
public List <UmbracoNode> GetNodesByName(string name) { using (IScope scope = _scopeProvider.CreateScope()) { IUmbracoDatabase database = scope.Database; var query = new Sql("SELECT Id FROM UmbracoNode WHERE text = @0", name); return(database.Fetch <UmbracoNode>(query)); } }
private void ResetDatabase() { if (_db.InTransaction) { _db.AbortTransaction(); } _db.Dispose(); _db = null; }
/// <summary> /// Inserts or updates the key/value row /// </summary> private RecordPersistenceType InsertLockRecord(string id, IUmbracoDatabase db) { return(db.InsertOrUpdate(new KeyValueDto { Key = MainDomKey, Value = id, Updated = DateTime.Now })); }
/// <summary> /// Bulk-insert records using commands. /// </summary> /// <typeparam name="T">The type of the records.</typeparam> /// <param name="database">The database.</param> /// <param name="records">The records.</param> /// <returns>The number of records that were inserted.</returns> private static int BulkInsertRecordsWithCommands <T>(IUmbracoDatabase database, T[] records) { foreach (var command in database.GenerateBulkInsertCommands(records)) { command.ExecuteNonQuery(); } return(records.Length); // what else? }
public static UmbracoDatabase AsUmbracoDatabase(this IUmbracoDatabase database) { if (database is not UmbracoDatabase asDatabase) { throw new Exception("oops: database."); } return(asDatabase); }
public int BulkInsertRecords <T>(IUmbracoDatabase database, IEnumerable <T> records) { if (!records.Any()) { return(0); } return(BulkInsertRecordsWithCommands(database, records.ToArray())); }
// Can always obtain a read lock (snapshot isolation in wal mode) // Mostly no-op just check that we didn't end up ReadUncommitted for real. private void ObtainReadLock() { IUmbracoDatabase db = _parent._scopeAccessor.Value.AmbientScope.Database; if (!db.InTransaction) { throw new InvalidOperationException("SqliteDistributedLockingMechanism requires a transaction to function."); } }
private bool?TryAcquire(IUmbracoDatabase db, string tempId, string updatedTempId) { using var transaction = db.GetTransaction(IsolationLevel.ReadCommitted); try { // get a read lock _sqlServerSyntax.ReadLock(db, Constants.Locks.MainDom); // the row var mainDomRows = db.Fetch <KeyValueDto>("SELECT * FROM umbracoKeyValue WHERE [key] = @key", new { key = MainDomKey }); if (mainDomRows.Count == 0 || mainDomRows[0].Value == updatedTempId) { // the other main dom has updated our record // Or the other maindom shutdown super fast and just deleted the record // which indicates that we // can acquire it and it has shutdown. _sqlServerSyntax.WriteLock(db, Constants.Locks.MainDom); // so now we update the row with our appdomain id InsertLockRecord(_lockId, db); _logger.Debug <SqlMainDomLock>("Acquired with ID {LockId}", _lockId); return(true); } else if (mainDomRows.Count == 1 && !mainDomRows[0].Value.StartsWith(tempId)) { // in this case, the prefixed ID is different which means // another new AppDomain has come online and is wanting to take over. In that case, we will not // acquire. _logger.Debug <SqlMainDomLock>("Cannot acquire, another booting application detected."); return(false); } } catch (Exception ex) { if (IsLockTimeoutException(ex as SqlException)) { _logger.Error <SqlMainDomLock>(ex, "Sql timeout occurred, waiting for existing MainDom is canceled."); _errorDuringAcquiring = true; return(false); } // unexpected _logger.Error <SqlMainDomLock>(ex, "Unexpected error, waiting for existing MainDom is canceled."); _errorDuringAcquiring = true; return(false); } finally { transaction.Complete(); } return(null); // continue }
public List <int> GetTrashedNodes() { using (IScope scope = _scopeProvider.CreateScope()) { IUmbracoDatabase database = scope.Database; var query = new Sql("SELECT Id FROM UmbracoNode WHERE trashed = 1"); var trashedIds = database.Fetch <int>(query); return(trashedIds); } }
public static UmbracoDatabase AsUmbracoDatabase(this IUmbracoDatabase database) { var asDatabase = database as UmbracoDatabase; if (asDatabase == null) { throw new Exception("oops: database."); } return(asDatabase); }
public DatabaseSchemaCreator(IUmbracoDatabase database, ILogger logger) { _database = database ?? throw new ArgumentNullException(nameof(database)); _logger = logger ?? throw new ArgumentNullException(nameof(logger)); if (_database?.SqlContext?.SqlSyntax == null) { throw new InvalidOperationException("No SqlContext has been assigned to the database"); } }
private IUmbracoDatabase GetDatabase() { if (_db != null) { return(_db); } _db = _dbFactory.CreateDatabase(); return(_db); }
public void Setup() { _logger = Mock.Of <ILogger>(); _sqlSyntax = new SqlCeSyntaxProvider(); var dbProviderFactory = DbProviderFactories.GetFactory(Constants.DbProviderNames.SqlServer); var sqlContext = new SqlContext(_sqlSyntax, DatabaseType.SqlServer2008, Mock.Of <IPocoDataFactory>()); _database = new UmbracoDatabase("cstr", sqlContext, dbProviderFactory, _logger); }
/// <summary> /// Creates bulk-insert commands. /// </summary> /// <typeparam name="T">The type of the records.</typeparam> /// <param name="database">The database.</param> /// <param name="records">The records.</param> /// <returns>The sql commands to execute.</returns> internal static IDbCommand[] GenerateBulkInsertCommands <T>(this IUmbracoDatabase database, T[] records) { if (database?.Connection == null) { throw new ArgumentException("Null database?.connection.", nameof(database)); } var pocoData = database.PocoDataFactory.ForType(typeof(T)); // get columns to include, = number of parameters per row var columns = pocoData.Columns.Where(c => IncludeColumn(pocoData, c)).ToArray(); var paramsPerRecord = columns.Length; // format columns to sql var tableName = database.DatabaseType.EscapeTableName(pocoData.TableInfo.TableName); var columnNames = string.Join(", ", columns.Select(c => tableName + "." + database.DatabaseType.EscapeSqlIdentifier(c.Key))); // example: // assume 4168 records, each record containing 8 fields, ie 8 command parameters // max 2100 parameter per command // Math.Floor(2100 / 8) = 262 record per command // 4168 / 262 = 15.908... = there will be 16 command in total // (if we have disabled db parameters, then all records will be included, in only one command) var recordsPerCommand = paramsPerRecord == 0 ? int.MaxValue : Convert.ToInt32(Math.Floor(2000.00 / paramsPerRecord)); var commandsCount = Convert.ToInt32(Math.Ceiling((double)records.Length / recordsPerCommand)); var commands = new IDbCommand[commandsCount]; var recordsIndex = 0; var recordsLeftToInsert = records.Length; var prefix = database.DatabaseType.GetParameterPrefix(database.ConnectionString); for (var commandIndex = 0; commandIndex < commandsCount; commandIndex++) { var command = database.CreateCommand(database.Connection, CommandType.Text, string.Empty); var parameterIndex = 0; var commandRecords = Math.Min(recordsPerCommand, recordsLeftToInsert); var recordsValues = new string[commandRecords]; for (var commandRecordIndex = 0; commandRecordIndex < commandRecords; commandRecordIndex++, recordsIndex++, recordsLeftToInsert--) { var record = records[recordsIndex]; var recordValues = new string[columns.Length]; for (var columnIndex = 0; columnIndex < columns.Length; columnIndex++) { database.AddParameter(command, columns[columnIndex].Value.GetValue(record)); recordValues[columnIndex] = prefix + parameterIndex++; } recordsValues[commandRecordIndex] = "(" + string.Join(",", recordValues) + ")"; } command.CommandText = $"INSERT INTO {tableName} ({columnNames}) VALUES {string.Join(", ", recordsValues)}"; commands[commandIndex] = command; } return(commands); }