public StoredProcedureDroppingArgs(SyncContext context, SyncTable table, DbStoredProcedureType StoredProcedureType, DbCommand command, DbConnection connection = null, DbTransaction transaction = null)
: base(context, connection, transaction)
{
this.Command = command;
this.Table = table;
this.StoredProcedureType = StoredProcedureType;
}
public Task <DbCommand> GetExistsStoredProcedureCommandAsync(DbStoredProcedureType storedProcedureType, SyncFilter filter, DbConnection connection, DbTransaction transaction)
{
if (filter == null && (storedProcedureType == DbStoredProcedureType.SelectChangesWithFilters || storedProcedureType == DbStoredProcedureType.SelectInitializedChangesWithFilters))
{
return(Task.FromResult <DbCommand>(null));
}
var quotedProcedureName = this.mySqlObjectNames.GetStoredProcedureCommandName(storedProcedureType, filter);
var procedureName = ParserName.Parse(quotedProcedureName, "`").ToString();
var command = connection.CreateCommand();
command.CommandText = @"select count(*) from information_schema.ROUTINES
where ROUTINE_TYPE = 'PROCEDURE'
and ROUTINE_SCHEMA = schema()
and ROUTINE_NAME = @procName limit 1";
command.Transaction = transaction;
var p = command.CreateParameter();
p.ParameterName = "@procName";
p.Value = procedureName;
command.Parameters.Add(p);
return(Task.FromResult(command));
}
/// <summary>
/// Check if a Stored Procedure exists
/// </summary>
/// <param name="table">A table from your Setup instance, where you want to check if the Stored Procedure exists</param>
/// <param name="storedProcedureType">StoredProcedure type</param>
public Task <bool> ExistStoredProcedureAsync(SetupTable table, DbStoredProcedureType storedProcedureType, DbConnection connection = default, DbTransaction transaction = default, CancellationToken cancellationToken = default, IProgress <ProgressArgs> progress = null)
=> RunInTransactionAsync(SyncStage.None, async(ctx, connection, transaction) =>
{
// using a fake SyncTable based on SetupTable, since we don't need columns
var schemaTable = new SyncTable(table.TableName, table.SchemaName);
// Create a temporary SyncSet for attaching to the schemaTable
var schema = new SyncSet();
// Add this table to schema
schema.Tables.Add(schemaTable);
schema.EnsureSchema();
// copy filters from setup
foreach (var filter in this.Setup.Filters)
{
schema.Filters.Add(filter);
}
var tableBuilder = this.GetTableBuilder(schemaTable, this.Setup);
var exists = await InternalExistsStoredProcedureAsync(ctx, tableBuilder, storedProcedureType, connection, transaction, cancellationToken, progress).ConfigureAwait(false);
return(exists);
}, connection, transaction, cancellationToken);
public void AddStoredProcedureName(DbStoredProcedureType objectType, string name)
{
if (storedProceduresNames.ContainsKey(objectType))
{
throw new Exception("Yous can't add an objectType multiple times");
}
storedProceduresNames.Add(objectType, name);
}
/// <summary>
/// Create a Stored Procedure
/// </summary>
/// <param name="table">A table from your Setup instance, where you want to create the Stored Procedure</param>
/// <param name="storedProcedureType">StoredProcedure type</param>
/// <param name="overwrite">If true, drop the existing stored procedure then create again</param>
public async Task <bool> CreateStoredProcedureAsync(IScopeInfo scopeInfo, string tableName, string schemaName = null,
DbStoredProcedureType storedProcedureType = default, bool overwrite = false,
DbConnection connection = default, DbTransaction transaction = default, CancellationToken cancellationToken = default, IProgress <ProgressArgs> progress = null)
{
var context = new SyncContext(Guid.NewGuid(), scopeInfo.Name);
try
{
if (scopeInfo.Schema == null || !scopeInfo.Schema.HasTables || !scopeInfo.Schema.HasColumns)
{
return(false);
}
var syncTable = scopeInfo.Schema.Tables[tableName, schemaName];
if (syncTable == null)
{
return(false);
}
await using var runner = await this.GetConnectionAsync(context, SyncMode.Writing, SyncStage.Provisioning, connection, transaction, cancellationToken, progress).ConfigureAwait(false);
bool hasBeenCreated = false;
// Get table builder
var tableBuilder = this.GetTableBuilder(syncTable, scopeInfo);
bool exists;
(context, exists) = await InternalExistsStoredProcedureAsync(scopeInfo, context, tableBuilder, storedProcedureType, runner.Connection, runner.Transaction, cancellationToken, progress).ConfigureAwait(false);
// should create only if not exists OR if overwrite has been set
var shouldCreate = !exists || overwrite;
if (shouldCreate)
{
// Drop storedProcedure if already exists
if (exists && overwrite)
{
(context, _) = await InternalDropStoredProcedureAsync(scopeInfo, context, tableBuilder, storedProcedureType, runner.Connection, runner.Transaction, cancellationToken, progress).ConfigureAwait(false);
}
(context, hasBeenCreated) = await InternalCreateStoredProcedureAsync(scopeInfo, context, tableBuilder, storedProcedureType, runner.Connection, runner.Transaction, cancellationToken, progress).ConfigureAwait(false);
}
await runner.CommitAsync().ConfigureAwait(false);
return(hasBeenCreated);
}
catch (Exception ex)
{
throw GetSyncError(context, ex);
}
}
public override Task <DbCommand> GetCreateStoredProcedureCommandAsync(DbStoredProcedureType storedProcedureType, SyncFilter filter, DbConnection connection, DbTransaction transaction)
{
var command = storedProcedureType switch
{
DbStoredProcedureType.BulkTableType => this.CreateBulkTableTypeCommand(connection, transaction),
DbStoredProcedureType.BulkUpdateRows => this.CreateBulkUpdateCommand(connection, transaction),
DbStoredProcedureType.BulkDeleteRows => this.CreateBulkDeleteCommand(connection, transaction),
DbStoredProcedureType.Reset => this.CreateResetCommand(connection, transaction),
_ => null
};
return(Task.FromResult(command));
}
/// <summary>
/// Create a Stored Procedure
/// </summary>
/// <param name="table">A table from your Setup instance, where you want to create the Stored Procedure</param>
/// <param name="storedProcedureType">StoredProcedure type</param>
/// <param name="overwrite">If true, drop the existing stored procedure then create again</param>
public Task <bool> CreateStoredProcedureAsync(SetupTable table, DbStoredProcedureType storedProcedureType, 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(schemaTable, _) = await this.InternalGetTableSchemaAsync(ctx, this.Setup, table, connection, transaction, cancellationToken, progress).ConfigureAwait(false);
if (schemaTable == null)
{
throw new MissingTableException(table.GetFullName());
}
// Create a temporary SyncSet for attaching to the schemaTable
var schema = new SyncSet();
// Add this table to schema
schema.Tables.Add(schemaTable);
schema.EnsureSchema();
// copy filters from setup
foreach (var filter in this.Setup.Filters)
{
schema.Filters.Add(filter);
}
// Get table builder
var tableBuilder = this.GetTableBuilder(schemaTable, this.Setup);
var exists = await InternalExistsStoredProcedureAsync(ctx, tableBuilder, storedProcedureType, 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 storedProcedure if already exists
if (exists && overwrite)
{
await InternalDropStoredProcedureAsync(ctx, tableBuilder, storedProcedureType, connection, transaction, cancellationToken, progress).ConfigureAwait(false);
}
hasBeenCreated = await InternalCreateStoredProcedureAsync(ctx, tableBuilder, storedProcedureType, connection, transaction, cancellationToken, progress).ConfigureAwait(false);
}
return(hasBeenCreated);
}, connection, transaction, cancellationToken);
public string GetStoredProcedureCommandName(DbStoredProcedureType storedProcedureType, SyncFilter filter = null)
{
if (!storedProceduresNames.ContainsKey(storedProcedureType))
{
throw new Exception("Yous should provide a value for all DbCommandName");
}
var commandName = storedProceduresNames[storedProcedureType];
// concat filter name
if (filter != null && (storedProcedureType == DbStoredProcedureType.SelectChangesWithFilters || storedProcedureType == DbStoredProcedureType.SelectInitializedChangesWithFilters))
{
commandName = string.Format(commandName, filter.GetFilterName());
}
return(commandName);
}
public Task <DbCommand> GetCreateStoredProcedureCommandAsync(DbStoredProcedureType storedProcedureType, SyncFilter filter, DbConnection connection, DbTransaction transaction)
{
var command = storedProcedureType switch
{
DbStoredProcedureType.SelectChanges => this.CreateSelectIncrementalChangesCommand(connection, transaction),
DbStoredProcedureType.SelectChangesWithFilters => this.CreateSelectIncrementalChangesWithFilterCommand(filter, connection, transaction),
DbStoredProcedureType.SelectInitializedChanges => this.CreateSelectInitializedChangesCommand(connection, transaction),
DbStoredProcedureType.SelectInitializedChangesWithFilters => this.CreateSelectInitializedChangesWithFilterCommand(filter, connection, transaction),
DbStoredProcedureType.SelectRow => this.CreateSelectRowCommand(connection, transaction),
DbStoredProcedureType.UpdateRow => this.CreateUpdateCommand(connection, transaction),
DbStoredProcedureType.DeleteRow => this.CreateDeleteCommand(connection, transaction),
DbStoredProcedureType.DeleteMetadata => this.CreateDeleteMetadataCommand(connection, transaction),
DbStoredProcedureType.Reset => this.CreateResetCommand(connection, transaction),
_ => throw new NotImplementedException($"this DbStoredProcedureType {storedProcedureType} has no command to create.")
};
return(Task.FromResult(command));
}
public Task <DbCommand> GetDropStoredProcedureCommandAsync(DbStoredProcedureType storedProcedureType, SyncFilter filter, DbConnection connection, DbTransaction transaction)
{
if (filter == null && (storedProcedureType == DbStoredProcedureType.SelectChangesWithFilters || storedProcedureType == DbStoredProcedureType.SelectInitializedChangesWithFilters))
{
return(Task.FromResult <DbCommand>(null));
}
var quotedProcedureName = this.mySqlObjectNames.GetStoredProcedureCommandName(storedProcedureType, filter);
var commandText = $"drop procedure {quotedProcedureName}";
var command = connection.CreateCommand();
command.Connection = connection;
command.Transaction = transaction;
command.CommandText = commandText;
return(Task.FromResult(command));
}
/// <summary>
/// Drop a Stored Procedure
/// </summary>
/// <param name="table">A table from your Setup instance, where you want to drop the Stored Procedure</param>
/// <param name="storedProcedureType">Stored Procedure type</param>
public Task <bool> DropStoredProcedureAsync(SetupTable table, DbStoredProcedureType storedProcedureType, DbConnection connection = default, DbTransaction transaction = default, CancellationToken cancellationToken = default, IProgress <ProgressArgs> progress = null)
=> RunInTransactionAsync(SyncStage.Deprovisioning, async(ctx, connection, transaction) =>
{
bool hasBeenDropped = false;
// using a fake SyncTable based on SetupTable, since we don't need columns
var schemaTable = new SyncTable(table.TableName, table.SchemaName);
// Create a temporary SyncSet for attaching to the schemaTable
var schema = new SyncSet();
// Add this table to schema
schema.Tables.Add(schemaTable);
schema.EnsureSchema();
// copy filters from setup
foreach (var filter in this.Setup.Filters)
{
schema.Filters.Add(filter);
}
var tableBuilder = this.GetTableBuilder(schemaTable, this.Setup);
var existsAndCanBeDeleted = await InternalExistsStoredProcedureAsync(ctx, tableBuilder, storedProcedureType, connection, transaction, cancellationToken, progress).ConfigureAwait(false);
if (existsAndCanBeDeleted)
{
hasBeenDropped = await InternalDropStoredProcedureAsync(ctx, tableBuilder, storedProcedureType, connection, transaction, cancellationToken, progress).ConfigureAwait(false);
}
// Removing cached commands
var syncAdapter = this.GetSyncAdapter(schemaTable, this.Setup);
syncAdapter.RemoveCommands();
return(hasBeenDropped);
}, connection, transaction, cancellationToken);
public override Task <DbCommand> GetDropStoredProcedureCommandAsync(DbStoredProcedureType storedProcedureType, SyncFilter filter, DbConnection connection, DbTransaction transaction) => this.sqlChangeTrackingBuilderProcedure.GetDropStoredProcedureCommandAsync(storedProcedureType, filter, connection, transaction);
/// <summary>
/// Internal exists storedProcedure procedure routine
/// </summary>
internal async Task <bool> InternalExistsStoredProcedureAsync(SyncContext ctx, DbTableBuilder tableBuilder, DbStoredProcedureType storedProcedureType, DbConnection connection, DbTransaction transaction, CancellationToken cancellationToken, IProgress <ProgressArgs> progress)
{
var filter = tableBuilder.TableDescription.GetFilter();
var existsCommand = await tableBuilder.GetExistsStoredProcedureCommandAsync(storedProcedureType, filter, connection, transaction).ConfigureAwait(false);
if (existsCommand == null)
{
return(false);
}
var existsResultObject = await existsCommand.ExecuteScalarAsync().ConfigureAwait(false);
var exists = Convert.ToInt32(existsResultObject) > 0;
return(exists);
}
/// <summary>
/// Internal drop storedProcedure routine
/// </summary>
internal async Task <bool> InternalDropStoredProcedureAsync(SyncContext ctx, DbTableBuilder tableBuilder, DbStoredProcedureType storedProcedureType, DbConnection connection, DbTransaction transaction, CancellationToken cancellationToken, IProgress <ProgressArgs> progress)
{
var filter = tableBuilder.TableDescription.GetFilter();
var command = await tableBuilder.GetDropStoredProcedureCommandAsync(storedProcedureType, filter, connection, transaction).ConfigureAwait(false);
if (command == null)
{
return(false);
}
var action = new StoredProcedureDroppingArgs(ctx, tableBuilder.TableDescription, storedProcedureType, command, connection, transaction);
await this.InterceptAsync(action, cancellationToken).ConfigureAwait(false);
if (action.Cancel || action.Command == null)
{
return(false);
}
await action.Command.ExecuteNonQueryAsync();
await this.InterceptAsync(new StoredProcedureDroppedArgs(ctx, tableBuilder.TableDescription, storedProcedureType, connection, transaction), cancellationToken).ConfigureAwait(false);
return(true);
}
/// <summary>
/// Check if a Stored Procedure exists
/// </summary>
/// <param name="table">A table from your Setup instance, where you want to check if the Stored Procedure exists</param>
/// <param name="storedProcedureType">StoredProcedure type</param>
public async Task <bool> ExistStoredProcedureAsync(IScopeInfo scopeInfo, string tableName, string schemaName = null, DbStoredProcedureType storedProcedureType = default, DbConnection connection = default, DbTransaction transaction = default, CancellationToken cancellationToken = default, IProgress <ProgressArgs> progress = null)
{
var context = new SyncContext(Guid.NewGuid(), scopeInfo.Name);
try
{
if (scopeInfo.Schema == null || scopeInfo.Schema.Tables == null || scopeInfo.Schema.Tables.Count <= 0 || !scopeInfo.Schema.HasColumns)
{
return(false);
}
var schemaTable = scopeInfo.Schema.Tables[tableName, schemaName];
if (schemaTable == null)
{
return(false);
}
await using var runner = await this.GetConnectionAsync(context, SyncMode.Reading, SyncStage.None, connection, transaction, cancellationToken, progress).ConfigureAwait(false);
var tableBuilder = this.GetTableBuilder(schemaTable, scopeInfo);
bool exists;
(context, exists) = await InternalExistsStoredProcedureAsync(scopeInfo, context, tableBuilder, storedProcedureType, runner.Connection, runner.Transaction, cancellationToken, progress).ConfigureAwait(false);
return(exists);
}
catch (Exception ex)
{
throw GetSyncError(context, ex);
}
}
public StoredProcedureDroppedArgs(SyncContext context, SyncTable table, DbStoredProcedureType StoredProcedureType, DbConnection connection = null, DbTransaction transaction = null)
: base(context, connection, transaction)
{
Table = table;
this.StoredProcedureType = StoredProcedureType;
}
public override Task <DbCommand> GetDropStoredProcedureCommandAsync(DbStoredProcedureType storedProcedureType, SyncFilter filter, DbConnection connection, DbTransaction transaction) => throw new NotImplementedException();
public abstract Task <DbCommand> GetDropStoredProcedureCommandAsync(DbStoredProcedureType storedProcedureType, SyncFilter filter, DbConnection connection, DbTransaction transaction);
/// <summary>
/// Internal exists storedProcedure procedure routine
/// </summary>
internal async Task <(SyncContext context, bool exists)> InternalExistsStoredProcedureAsync(IScopeInfo scopeInfo, SyncContext context, DbTableBuilder tableBuilder, DbStoredProcedureType storedProcedureType, DbConnection connection, DbTransaction transaction, CancellationToken cancellationToken, IProgress <ProgressArgs> progress)
{
var filter = tableBuilder.TableDescription.GetFilter();
var existsCommand = await tableBuilder.GetExistsStoredProcedureCommandAsync(storedProcedureType, filter, 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>
/// Internal create Stored Procedure routine
/// </summary>
internal async Task <bool> InternalCreateStoredProcedureAsync(SyncContext ctx, DbTableBuilder tableBuilder, DbStoredProcedureType storedProcedureType, 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());
}
var filter = tableBuilder.TableDescription.GetFilter();
var command = await tableBuilder.GetCreateStoredProcedureCommandAsync(storedProcedureType, filter, connection, transaction).ConfigureAwait(false);
if (command == null)
{
return(false);
}
var action = new StoredProcedureCreatingArgs(ctx, tableBuilder.TableDescription, storedProcedureType, command, connection, transaction);
await this.InterceptAsync(action, cancellationToken).ConfigureAwait(false);
if (action.Cancel || action.Command == null)
{
return(false);
}
await action.Command.ExecuteNonQueryAsync().ConfigureAwait(false);
await this.InterceptAsync(new StoredProcedureCreatedArgs(ctx, tableBuilder.TableDescription, storedProcedureType, connection, transaction), cancellationToken).ConfigureAwait(false);
return(true);
}
public override Task <DbCommand> GetDropStoredProcedureCommandAsync(DbStoredProcedureType storedProcedureType, SyncFilter filter, DbConnection connection, DbTransaction transaction) => Task.FromResult <DbCommand>(null);
/// <summary>
/// Drop a Stored Procedure
/// </summary>
/// <param name="table">A table from your Setup instance, where you want to drop the Stored Procedure</param>
/// <param name="storedProcedureType">Stored Procedure type</param>
public async Task <bool> DropStoredProcedureAsync(IScopeInfo scopeInfo, string tableName, string schemaName = null, DbStoredProcedureType storedProcedureType = default, DbConnection connection = default, DbTransaction transaction = default, CancellationToken cancellationToken = default, IProgress <ProgressArgs> progress = null)
{
var context = new SyncContext(Guid.NewGuid(), scopeInfo.Name);
try
{
if (scopeInfo.Schema == null || scopeInfo.Schema.Tables == null || scopeInfo.Schema.Tables.Count <= 0 || !scopeInfo.Schema.HasColumns)
{
return(false);
}
var schemaTable = scopeInfo.Schema.Tables[tableName, schemaName];
if (schemaTable == null)
{
return(false);
}
await using var runner = await this.GetConnectionAsync(context, SyncMode.Writing, SyncStage.Deprovisioning, connection, transaction, cancellationToken, progress).ConfigureAwait(false);
bool hasBeenDropped = false;
var tableBuilder = this.GetTableBuilder(schemaTable, scopeInfo);
bool existsAndCanBeDeleted;
(context, existsAndCanBeDeleted) = await InternalExistsStoredProcedureAsync(scopeInfo, context, tableBuilder, storedProcedureType, runner.Connection, runner.Transaction, cancellationToken, progress).ConfigureAwait(false);
if (existsAndCanBeDeleted)
{
(context, hasBeenDropped) = await InternalDropStoredProcedureAsync(scopeInfo, context, tableBuilder, storedProcedureType, runner.Connection, runner.Transaction, cancellationToken, progress).ConfigureAwait(false);
}
// Removing cached commands
var syncAdapter = this.GetSyncAdapter(schemaTable, scopeInfo);
syncAdapter.RemoveCommands();
await runner.CommitAsync().ConfigureAwait(false);
return(hasBeenDropped);
}
catch (Exception ex)
{
throw GetSyncError(context, ex);
}
}
