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