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