public bool NeedToCreateTrigger(DbTriggerType type) { var updTriggerName = this.sqlObjectNames.GetCommandName(DbCommandType.UpdateTrigger); var delTriggerName = this.sqlObjectNames.GetCommandName(DbCommandType.DeleteTrigger); var insTriggerName = this.sqlObjectNames.GetCommandName(DbCommandType.InsertTrigger); string triggerName = string.Empty; switch (type) { case DbTriggerType.Insert: { triggerName = insTriggerName; break; } case DbTriggerType.Update: { triggerName = updTriggerName; break; } case DbTriggerType.Delete: { triggerName = delTriggerName; break; } } return(!SqlManagementUtils.TriggerExists(connection, transaction, triggerName)); }
public virtual async Task <bool> NeedToCreateTriggerAsync(DbTriggerType type) { var updTriggerName = this.sqlObjectNames.GetCommandName(DbCommandType.UpdateTrigger).name; var delTriggerName = this.sqlObjectNames.GetCommandName(DbCommandType.DeleteTrigger).name; var insTriggerName = this.sqlObjectNames.GetCommandName(DbCommandType.InsertTrigger).name; string triggerName = string.Empty; switch (type) { case DbTriggerType.Insert: { triggerName = insTriggerName; break; } case DbTriggerType.Update: { triggerName = updTriggerName; break; } case DbTriggerType.Delete: { triggerName = delTriggerName; break; } } return(!await SqlManagementUtils.TriggerExistsAsync(connection, transaction, triggerName).ConfigureAwait(false)); }
/// <summary> /// Create a trigger /// </summary> /// <param name="table">A table from your Setup instance, where you want to create the trigger</param> /// <param name="triggerType">Trigger type (Insert, Delete, Update)</param> /// <param name="overwrite">If true, drop the existing trriger then create again</param> public Task <bool> CreateTriggerAsync(SetupTable table, DbTriggerType triggerType, bool overwrite = false, DbConnection connection = default, DbTransaction transaction = default, CancellationToken cancellationToken = default, IProgress <ProgressArgs> progress = null) => RunInTransactionAsync(SyncStage.Provisioning, async(ctx, connection, transaction) => { bool hasBeenCreated = false; var schema = await this.InternalGetSchemaAsync(ctx, connection, transaction, cancellationToken, progress).ConfigureAwait(false); var schemaTable = schema.Tables[table.TableName, table.SchemaName]; if (schemaTable == null) { throw new MissingTableException(table.GetFullName()); } // Get table builder var tableBuilder = this.GetTableBuilder(schemaTable, this.Setup); var exists = await InternalExistsTriggerAsync(ctx, tableBuilder, triggerType, connection, transaction, cancellationToken, progress).ConfigureAwait(false); // should create only if not exists OR if overwrite has been set var shouldCreate = !exists || overwrite; if (shouldCreate) { // Drop trigger if already exists if (exists && overwrite) { await InternalDropTriggerAsync(ctx, tableBuilder, triggerType, connection, transaction, cancellationToken, progress).ConfigureAwait(false); } hasBeenCreated = await InternalCreateTriggerAsync(ctx, tableBuilder, triggerType, connection, transaction, cancellationToken, progress).ConfigureAwait(false); } return(hasBeenCreated); }, connection, transaction, cancellationToken);
public async Task <bool> NeedToCreateTriggerAsync(DbTriggerType type) { var updTriggerName = string.Format(this.sqliteObjectNames.GetCommandName(DbCommandType.UpdateTrigger), tableName.Unquoted().ToString()); var delTriggerName = string.Format(this.sqliteObjectNames.GetCommandName(DbCommandType.DeleteTrigger), tableName.Unquoted().ToString()); var insTriggerName = string.Format(this.sqliteObjectNames.GetCommandName(DbCommandType.InsertTrigger), tableName.Unquoted().ToString()); string triggerName = string.Empty; switch (type) { case DbTriggerType.Insert: { triggerName = insTriggerName; break; } case DbTriggerType.Update: { triggerName = updTriggerName; break; } case DbTriggerType.Delete: { triggerName = delTriggerName; break; } } return(!(await SqliteManagementUtils.TriggerExistsAsync(connection, transaction, triggerName).ConfigureAwait(false))); }
public TriggerDroppingArgs(SyncContext context, SyncTable table, DbTriggerType triggerType, DbCommand command, DbConnection connection = null, DbTransaction transaction = null) : base(context, connection, transaction) { this.Table = table; this.TriggerType = triggerType; this.Command = command; }
/// <summary> /// Internal drop trigger routine /// </summary> internal async Task <(SyncContext context, bool dropped)> InternalDropTriggerAsync( IScopeInfo scopeInfo, SyncContext context, DbTableBuilder tableBuilder, DbTriggerType triggerType, DbConnection connection, DbTransaction transaction, CancellationToken cancellationToken, IProgress <ProgressArgs> progress) { using var command = await tableBuilder.GetDropTriggerCommandAsync(triggerType, connection, transaction).ConfigureAwait(false); if (command == null) { return(context, false); } var action = await this.InterceptAsync(new TriggerDroppingArgs(context, tableBuilder.TableDescription, triggerType, command, connection, transaction), progress, cancellationToken).ConfigureAwait(false); if (action.Cancel || action.Command == null) { return(context, false); } await this.InterceptAsync(new DbCommandArgs(context, action.Command, connection, transaction), progress, cancellationToken).ConfigureAwait(false); await action.Command.ExecuteNonQueryAsync(); await this.InterceptAsync(new TriggerDroppedArgs(context, tableBuilder.TableDescription, triggerType, connection, transaction), progress, cancellationToken).ConfigureAwait(false); action.Command.Dispose(); return(context, true); }
public bool NeedToCreateTrigger(DbTriggerType type) { var updTriggerName = string.Format(this.mySqlObjectNames.GetCommandName(DbCommandType.UpdateTrigger).name, tableName.Unquoted().Normalized().ToString()); var delTriggerName = string.Format(this.mySqlObjectNames.GetCommandName(DbCommandType.DeleteTrigger).name, tableName.Unquoted().Normalized().ToString()); var insTriggerName = string.Format(this.mySqlObjectNames.GetCommandName(DbCommandType.InsertTrigger).name, tableName.Unquoted().Normalized().ToString()); string triggerName = string.Empty; switch (type) { case DbTriggerType.Insert: { triggerName = insTriggerName; break; } case DbTriggerType.Update: { triggerName = updTriggerName; break; } case DbTriggerType.Delete: { triggerName = delTriggerName; break; } } return(!MySqlManagementUtils.TriggerExists(connection, transaction, triggerName)); }
/// <summary> /// Check if trigger exists /// </summary> public async Task <bool> TriggerExistsAsync(DbTriggerType triggerType, DbConnection connection, DbTransaction transaction) { if (TableDescription.PrimaryKeys.Count <= 0) { throw new MissingPrimaryKeyException(TableDescription.TableName); } var alreadyOpened = connection.State != ConnectionState.Closed; if (!alreadyOpened) { await connection.OpenAsync().ConfigureAwait(false); } var triggerBuilder = CreateTriggerBuilder(); var exists = !await triggerBuilder.NeedToCreateTriggerAsync(triggerType, connection, transaction).ConfigureAwait(false); if (!alreadyOpened) { connection.Close(); } return(exists); }
public virtual Task <DbCommand> GetCreateTriggerCommandAsync(DbTriggerType triggerType, DbConnection connection, DbTransaction transaction) { var commandTriggerCommandString = triggerType switch { DbTriggerType.Delete => CreateDeleteTriggerAsync(), DbTriggerType.Insert => CreateInsertTriggerAsync(), DbTriggerType.Update => CreateUpdateTriggerAsync(), _ => throw new NotImplementedException() }; string triggerFor = triggerType == DbTriggerType.Delete ? "DELETE" : triggerType == DbTriggerType.Update ? "UPDATE" : "INSERT"; var commandTriggerName = this.sqlObjectNames.GetTriggerCommandName(triggerType); var stringBuilder = new StringBuilder(); stringBuilder.AppendLine($"CREATE TRIGGER {commandTriggerName} ON {tableName.Schema().Quoted().ToString()} FOR {triggerFor} AS"); stringBuilder.AppendLine(commandTriggerCommandString); var command = connection.CreateCommand(); command.Connection = connection; command.Transaction = transaction; command.CommandText = stringBuilder.ToString(); return(Task.FromResult(command)); } }
public override Task <DbCommand> GetExistsTriggerCommandAsync(DbTriggerType triggerType, DbConnection connection, DbTransaction transaction) { var commandText = $"IF EXISTS (Select top 1 tbl.name as TableName, " + $"sch.name as SchemaName " + $" from sys.change_tracking_tables tr " + $" Inner join sys.tables as tbl on tbl.object_id = tr.object_id " + $" Inner join sys.schemas as sch on tbl.schema_id = sch.schema_id " + $" Where tbl.name = @tableName and sch.name = @schemaName) SELECT 1 ELSE SELECT 0;"; var tbl = this.tableName.Unquoted().ToString(); var schema = SqlManagementUtils.GetUnquotedSqlSchemaName(this.tableName); var command = connection.CreateCommand(); command.Connection = connection; command.Transaction = transaction; command.CommandText = commandText; var parameter = command.CreateParameter(); parameter.ParameterName = "@tableName"; parameter.Value = tbl; command.Parameters.Add(parameter); parameter = command.CreateParameter(); parameter.ParameterName = "@schemaName"; parameter.Value = schema; command.Parameters.Add(parameter); return(Task.FromResult(command)); }
public virtual Task <DbCommand> GetExistsTriggerCommandAsync(DbTriggerType triggerType, DbConnection connection, DbTransaction transaction) { var commandTriggerName = this.sqlObjectNames.GetTriggerCommandName(triggerType); var triggerName = ParserName.Parse(commandTriggerName).ToString(); var commandText = $@"IF EXISTS (SELECT tr.name FROM sys.triggers tr JOIN sys.tables t ON tr.parent_id = t.object_id JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE tr.name = @triggerName and s.name = @schemaName) SELECT 1 ELSE SELECT 0"; var command = connection.CreateCommand(); command.Connection = connection; command.Transaction = transaction; command.CommandText = commandText; var p1 = command.CreateParameter(); p1.ParameterName = "@triggerName"; p1.Value = triggerName; command.Parameters.Add(p1); var p2 = command.CreateParameter(); p2.ParameterName = "@schemaName"; p2.Value = SqlManagementUtils.GetUnquotedSqlSchemaName(ParserName.Parse(commandTriggerName)); command.Parameters.Add(p2); return(Task.FromResult(command)); }
public bool NeedToCreateTrigger(DbTriggerType type) { var updTriggerName = string.Format(this.sqliteObjectNames.GetCommandName(DbCommandType.UpdateTrigger), tableName.UnquotedStringWithUnderScore); var delTriggerName = string.Format(this.sqliteObjectNames.GetCommandName(DbCommandType.DeleteTrigger), tableName.UnquotedStringWithUnderScore); var insTriggerName = string.Format(this.sqliteObjectNames.GetCommandName(DbCommandType.InsertTrigger), tableName.UnquotedStringWithUnderScore); string triggerName = string.Empty; switch (type) { case DbTriggerType.Insert: { triggerName = insTriggerName; break; } case DbTriggerType.Update: { triggerName = updTriggerName; break; } case DbTriggerType.Delete: { triggerName = delTriggerName; break; } } return(!SqliteManagementUtils.TriggerExists(connection, transaction, triggerName)); }
public void AddTriggerName(DbTriggerType objectType, string name) { if (triggersNames.ContainsKey(objectType)) { throw new Exception("Yous can't add an objectType multiple times"); } triggersNames.Add(objectType, name); }
public Task <DbCommand> GetCreateTriggerCommandAsync(DbTriggerType triggerType, DbConnection connection, DbTransaction transaction) { return(triggerType switch { DbTriggerType.Delete => Task.FromResult(CreateDeleteTriggerCommand(connection, transaction)), DbTriggerType.Insert => Task.FromResult(CreateInsertTriggerCommand(connection, transaction)), DbTriggerType.Update => Task.FromResult(CreateUpdateTriggerCommand(connection, transaction)), _ => throw new NotImplementedException() });
public override Task <DbCommand> GetCreateTriggerCommandAsync(DbTriggerType triggerType, DbConnection connection, DbTransaction transaction) { return(triggerType switch { DbTriggerType.Insert => Task.FromResult(CreateInsertTriggerCommand(connection, transaction)), DbTriggerType.Update => Task.FromResult(CreateUpdateTriggerCommand(connection, transaction)), DbTriggerType.Delete => Task.FromResult(CreateDeleteTriggerCommand(connection, transaction)), _ => throw new NotImplementedException("This trigger type is not supported when creating the sqlite trigger") });
/// <summary> /// Check if a trigger exists /// </summary> /// <param name="table">A table from your Setup instance, where you want to check if the trigger exists</param> /// <param name="triggerType">Trigger type (Insert, Delete, Update)</param> public Task <bool> ExistTriggerAsync(SetupTable table, DbTriggerType triggerType, DbConnection connection = default, DbTransaction transaction = default, CancellationToken cancellationToken = default, IProgress <ProgressArgs> progress = null) => RunInTransactionAsync(SyncStage.None, async(ctx, connection, transaction) => { // Fake sync table without column definitions. Not need for making a check exists call var schemaTable = new SyncTable(table.TableName, table.SchemaName); // Get table builder var tableBuilder = this.GetTableBuilder(schemaTable, this.Setup); var exists = await InternalExistsTriggerAsync(ctx, tableBuilder, triggerType, connection, transaction, cancellationToken, progress).ConfigureAwait(false); return(exists); }, connection, transaction, cancellationToken);
public string GetTriggerCommandName(DbTriggerType objectType, SyncFilter filter = null) { if (!triggersNames.ContainsKey(objectType)) { throw new Exception("Yous should provide a value for all DbCommandName"); } var commandName = triggersNames[objectType]; //// concat filter name //if (filter != null) // commandName = string.Format(commandName, filter.GetFilterName()); return(commandName); }
public virtual Task <DbCommand> GetDropTriggerCommandAsync(DbTriggerType triggerType, DbConnection connection, DbTransaction transaction) { var commandTriggerName = this.sqlObjectNames.GetTriggerCommandName(triggerType); var commandText = $@"DROP TRIGGER {commandTriggerName}"; var command = connection.CreateCommand(); command.Connection = connection; command.Transaction = transaction; command.CommandText = commandText; return(Task.FromResult(command)); }
/// <summary> /// Create a trigger if not exists already /// </summary> public async Task CreateTriggerAsync(DbTriggerType triggerType, DbConnection connection, DbTransaction transaction) { if (TableDescription.PrimaryKeys.Count <= 0) { throw new MissingPrimaryKeyException(TableDescription.TableName); } var triggerBuilder = CreateTriggerBuilder(); var alreadyOpened = connection.State != ConnectionState.Closed; if (!alreadyOpened) { await connection.OpenAsync().ConfigureAwait(false); } var needToCreateTrigger = await triggerBuilder.NeedToCreateTriggerAsync(triggerType, connection, transaction).ConfigureAwait(false); if (needToCreateTrigger) { switch (triggerType) { case DbTriggerType.Insert: await triggerBuilder.CreateInsertTriggerAsync(connection, transaction).ConfigureAwait(false); break; case DbTriggerType.Update: await triggerBuilder.CreateUpdateTriggerAsync(connection, transaction).ConfigureAwait(false); break; case DbTriggerType.Delete: await triggerBuilder.CreateDeleteTriggerAsync(connection, transaction).ConfigureAwait(false); break; default: break; } } if (!alreadyOpened) { connection.Close(); } }
public override Task <DbCommand> GetExistsTriggerCommandAsync(DbTriggerType triggerType, DbConnection connection, DbTransaction transaction) { var commandTriggerName = string.Format(this.sqliteObjectNames.GetTriggerCommandName(triggerType), TableName.Unquoted().ToString()); var triggerName = ParserName.Parse(commandTriggerName).ToString(); var command = connection.CreateCommand(); command.Connection = connection; command.Transaction = transaction; command.CommandText = "select count(*) from sqlite_master where name = @triggerName AND type='trigger'"; var parameter = command.CreateParameter(); parameter.ParameterName = "@triggerName"; parameter.Value = triggerName; command.Parameters.Add(parameter); return(Task.FromResult(command)); }
/// <summary> /// Internal exists trigger procedure routine /// </summary> internal async Task <(SyncContext context, bool exists)> InternalExistsTriggerAsync( IScopeInfo scopeInfo, SyncContext context, DbTableBuilder tableBuilder, DbTriggerType triggerType, DbConnection connection, DbTransaction transaction, CancellationToken cancellationToken, IProgress <ProgressArgs> progress) { // Get exists command using var existsCommand = await tableBuilder.GetExistsTriggerCommandAsync(triggerType, connection, transaction).ConfigureAwait(false); if (existsCommand == null) { return(context, false); } await this.InterceptAsync(new DbCommandArgs(context, existsCommand, connection, transaction), progress, cancellationToken).ConfigureAwait(false); var existsResultObject = await existsCommand.ExecuteScalarAsync().ConfigureAwait(false); var exists = Convert.ToInt32(existsResultObject) > 0; return(context, exists); }
/// <summary> /// Dropping a trigger /// </summary> /// <param name="table">A table from your Setup instance, where you want to drop the trigger</param> /// <param name="triggerType">Trigger type (Insert, Delete, Update)</param> public Task <bool> DropTriggerAsync(SetupTable table, DbTriggerType triggerType, DbConnection connection = default, DbTransaction transaction = default, CancellationToken cancellationToken = default, IProgress <ProgressArgs> progress = null) => RunInTransactionAsync(SyncStage.Deprovisioning, async(ctx, connection, transaction) => { bool hasBeenDropped = false; // Fake sync table without column definitions. Not need for making a drop call var schemaTable = new SyncTable(table.TableName, table.SchemaName); // Get table builder var tableBuilder = this.GetTableBuilder(schemaTable, this.Setup); var existsAndCanBeDeleted = await InternalExistsTriggerAsync(ctx, tableBuilder, triggerType, connection, transaction, cancellationToken, progress).ConfigureAwait(false); if (existsAndCanBeDeleted) { hasBeenDropped = await InternalDropTriggerAsync(ctx, tableBuilder, triggerType, connection, transaction, cancellationToken, progress).ConfigureAwait(false); } return(hasBeenDropped); }, connection, transaction, cancellationToken);
public Task <DbCommand> GetExistsTriggerCommandAsync(DbTriggerType triggerType, DbConnection connection, DbTransaction transaction) { var triggerNameString = string.Format(this.mySqlObjectNames.GetTriggerCommandName(triggerType), tableName.Unquoted().Normalized().ToString()); var triggerName = ParserName.Parse(triggerNameString, "`"); var command = connection.CreateCommand(); command.Connection = connection; command.Transaction = transaction; command.CommandText = "select count(*) from information_schema.TRIGGERS where trigger_name = @triggerName AND trigger_schema = schema() limit 1"; var parameter = command.CreateParameter(); parameter.ParameterName = "@triggerName"; parameter.Value = triggerName.Unquoted().ToString(); command.Parameters.Add(parameter); return(Task.FromResult(command)); }
public bool NeedToCreateTrigger(DbTriggerType type, DbBuilderOption option) { var updTriggerName = string.Format(this.sqliteObjectNames.GetCommandName(DbCommandType.UpdateTrigger), tableName.UnquotedStringWithUnderScore); var delTriggerName = string.Format(this.sqliteObjectNames.GetCommandName(DbCommandType.DeleteTrigger), tableName.UnquotedStringWithUnderScore); var insTriggerName = string.Format(this.sqliteObjectNames.GetCommandName(DbCommandType.InsertTrigger), tableName.UnquotedStringWithUnderScore); if (option.HasFlag(DbBuilderOption.CreateOrUseExistingSchema)) { string triggerName = string.Empty; switch (type) { case DbTriggerType.Insert: { triggerName = insTriggerName; break; } case DbTriggerType.Update: { triggerName = updTriggerName; break; } case DbTriggerType.Delete: { triggerName = delTriggerName; break; } } return(!MySqlManagementUtils.TriggerExists(connection, transaction, triggerName)); } if (option.HasFlag(DbBuilderOption.UseExistingSchema)) { return(false); } return(false); }
/// <summary> /// Internal create trigger routine /// </summary> internal async Task <(SyncContext context, bool created)> InternalCreateTriggerAsync( IScopeInfo scopeInfo, SyncContext context, DbTableBuilder tableBuilder, DbTriggerType triggerType, DbConnection connection, DbTransaction transaction, CancellationToken cancellationToken, IProgress <ProgressArgs> progress) { if (tableBuilder.TableDescription.Columns.Count <= 0) { throw new MissingsColumnException(tableBuilder.TableDescription.GetFullName()); } if (tableBuilder.TableDescription.PrimaryKeys.Count <= 0) { throw new MissingPrimaryKeyException(tableBuilder.TableDescription.GetFullName()); } using var command = await tableBuilder.GetCreateTriggerCommandAsync(triggerType, connection, transaction).ConfigureAwait(false); if (command == null) { return(context, false); } var action = await this.InterceptAsync(new TriggerCreatingArgs(context, tableBuilder.TableDescription, triggerType, command, connection, transaction), progress, cancellationToken).ConfigureAwait(false); if (action.Cancel || action.Command == null) { return(context, false); } await this.InterceptAsync(new DbCommandArgs(context, action.Command, connection, transaction), progress, cancellationToken).ConfigureAwait(false); await action.Command.ExecuteNonQueryAsync(); await this.InterceptAsync(new TriggerCreatedArgs(context, tableBuilder.TableDescription, triggerType, connection, transaction), progress, cancellationToken).ConfigureAwait(false); action.Command.Dispose(); return(context, true); }
public Task <bool> NeedToCreateTriggerAsync(DbTriggerType triggerType, DbConnection connection, DbTransaction transaction) => Task.FromResult(true);
public Task CreateTriggerAsync(DbTriggerType triggerType, DbConnection connection, DbTransaction transaction) => throw new NotImplementedException();
public override Task <DbCommand> GetExistsTriggerCommandAsync(DbTriggerType triggerType, DbConnection connection, DbTransaction transaction) => Task.FromResult <DbCommand>(null);
public Task <DbCommand> GetDropTriggerCommandAsync(DbTriggerType triggerType, DbConnection connection, DbTransaction transaction) => throw new NotImplementedException();
public TriggerCreatedArgs(SyncContext context, SyncTable table, DbTriggerType triggerType, DbConnection connection = null, DbTransaction transaction = null) : base(context, connection, transaction) { this.Table = table; this.TriggerType = triggerType; }