/// <summary> /// Drop all Stored Procedures /// </summary> /// <param name="table">A table from your Setup instance, where you want to drop all the Stored Procedures</param> public Task <bool> DropStoredProceduresAsync(SetupTable table, DbConnection connection = default, DbTransaction transaction = default, CancellationToken cancellationToken = default, IProgress <ProgressArgs> progress = null) => RunInTransactionAsync(SyncStage.Deprovisioning, async(ctx, connection, transaction) => { var hasDroppedAtLeastOneStoredProcedure = 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); } // using a fake SyncTable based on SetupTable, since we don't need columns var tableBuilder = this.GetTableBuilder(schemaTable, this.Setup); // check bulk before hasDroppedAtLeastOneStoredProcedure = await InternalDropStoredProceduresAsync(ctx, tableBuilder, connection, transaction, cancellationToken, progress).ConfigureAwait(false); // Removing cached commands var syncAdapter = this.GetSyncAdapter(schemaTable, this.Setup); syncAdapter.RemoveCommands(); return(hasDroppedAtLeastOneStoredProcedure); }, connection, transaction, cancellationToken);
/// <summary> /// Read the schema stored from the orchestrator database, through the provider. /// </summary> /// <returns>Schema containing tables, columns, relations, primary keys</returns> public virtual Task <SyncTable> GetTableSchemaAsync(SetupTable table, DbConnection connection = default, DbTransaction transaction = default, CancellationToken cancellationToken = default, IProgress <ProgressArgs> progress = null) => RunInTransactionAsync(SyncStage.SchemaReading, async(ctx, connection, transaction) => { 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); } return(schemaTable); }, connection, transaction, cancellationToken);
/// <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);
/// <summary> /// Clone the SyncSet schema (without data) /// </summary> public SyncSet Clone(bool includeTables = true) { var clone = new SyncSet(); if (!includeTables) { return(clone); } foreach (var f in this.Filters) { clone.Filters.Add(f.Clone()); } foreach (var r in this.Relations) { clone.Relations.Add(r.Clone()); } foreach (var t in this.Tables) { clone.Tables.Add(t.Clone()); } // Ensure all elements has the correct ref to its parent clone.EnsureSchema(); return(clone); }
/// <summary> /// Deprovision the orchestrator database based on the Setup table argument, and the provision enumeration /// </summary> /// <param name="provision">Provision enumeration to determine which components to deprovision</param> public virtual Task DeprovisionAsync(SetupTable table, SyncProvision provision, DbConnection connection = default, DbTransaction transaction = default, CancellationToken cancellationToken = default, IProgress <ProgressArgs> progress = null) { var setup = new SyncSetup(); setup.Tables.Add(table); // using a fake SyncTable based on oldSetup, since we don't need columns, but we need to have the filters var schemaTable = new SyncTable(table.TableName, table.SchemaName); // Create a temporary SyncSet for attaching to the schemaTable var tmpSchema = new SyncSet(); // Add this table to schema tmpSchema.Tables.Add(schemaTable); tmpSchema.EnsureSchema(); // copy filters from old setup foreach (var filter in this.Setup.Filters) { tmpSchema.Filters.Add(filter); } return(this.DeprovisionAsync(tmpSchema, provision, connection, transaction, cancellationToken, progress)); }
/// <summary> /// update configuration object with tables desc from server database /// </summary> internal async Task <SyncSet> InternalGetSchemaAsync(SyncContext context, SyncSetup setup, DbConnection connection, DbTransaction transaction, CancellationToken cancellationToken, IProgress <ProgressArgs> progress) { if (setup == null || setup.Tables.Count <= 0) { throw new MissingTablesException(); } await this.InterceptAsync(new SchemaLoadingArgs(context, setup, connection, transaction), cancellationToken).ConfigureAwait(false); // Create the schema var schema = new SyncSet(); // copy filters from setup foreach (var filter in setup.Filters) { schema.Filters.Add(filter); } var relations = new List <DbRelationDefinition>(20); foreach (var setupTable in setup.Tables) { var(syncTable, tableRelations) = await InternalGetTableSchemaAsync(context, setup, setupTable, connection, transaction, cancellationToken, progress); // Add this table to schema schema.Tables.Add(syncTable); // Since we are not sure of the order of reading tables // create a tmp relations list relations.AddRange(tableRelations); } // Parse and affect relations to schema SetRelations(relations, schema); // Ensure all objects have correct relations to schema schema.EnsureSchema(); var schemaArgs = new SchemaLoadedArgs(context, schema, connection); await this.InterceptAsync(schemaArgs, cancellationToken).ConfigureAwait(false); this.ReportProgress(context, progress, schemaArgs); return(schema); }
/// <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);
/// <summary> /// Deprovision the orchestrator database based on the provision enumeration /// </summary> /// <param name="provision">Provision enumeration to determine which components to deprovision</param> public virtual Task DeprovisionAsync(SyncProvision provision, ScopeInfo clientScopeInfo = null, DbConnection connection = default, DbTransaction transaction = default, CancellationToken cancellationToken = default, IProgress <ProgressArgs> progress = null) { // Create a temporary SyncSet for attaching to the schemaTable var tmpSchema = new SyncSet(); // Add this table to schema foreach (var table in this.Setup.Tables) { tmpSchema.Tables.Add(new SyncTable(table.TableName, table.SchemaName)); } tmpSchema.EnsureSchema(); // copy filters from old setup foreach (var filter in this.Setup.Filters) { tmpSchema.Filters.Add(filter); } return(this.DeprovisionAsync(tmpSchema, provision, clientScopeInfo, connection, transaction, cancellationToken, progress)); }
/// <summary> /// Deprovision the orchestrator database based on the schema argument, and the provision enumeration /// </summary> /// <param name="schema">Schema to be deprovisioned from the database managed by the orchestrator, through the provider.</param> /// <param name="provision">Provision enumeration to determine which components to deprovision</param> public virtual Task DeprovisionAsync(SyncProvision provision, ServerScopeInfo serverScopeInfo = null, DbConnection connection = default, DbTransaction transaction = default, CancellationToken cancellationToken = default, IProgress <ProgressArgs> progress = null) => RunInTransactionAsync(SyncStage.Deprovisioning, async(ctx, connection, transaction) => { // Get server scope if not supplied if (serverScopeInfo == null) { var scopeBuilder = this.GetScopeBuilder(this.Options.ScopeInfoTableName); var exists = await this.InternalExistsScopeInfoTableAsync(ctx, DbScopeType.Server, scopeBuilder, connection, transaction, cancellationToken, progress).ConfigureAwait(false); if (exists) { serverScopeInfo = await this.InternalGetScopeAsync <ServerScopeInfo>(ctx, DbScopeType.Server, this.ScopeName, scopeBuilder, connection, transaction, cancellationToken, progress).ConfigureAwait(false); } } // Create a temporary SyncSet for attaching to the schemaTable var tmpSchema = new SyncSet(); // Add this table to schema foreach (var table in this.Setup.Tables) { tmpSchema.Tables.Add(new SyncTable(table.TableName, table.SchemaName)); } tmpSchema.EnsureSchema(); // copy filters from old setup foreach (var filter in this.Setup.Filters) { tmpSchema.Filters.Add(filter); } var isDeprovisioned = await InternalDeprovisionAsync(ctx, tmpSchema, this.Setup, provision, serverScopeInfo, connection, transaction, cancellationToken, progress).ConfigureAwait(false); return(isDeprovisioned); }, connection, transaction, cancellationToken);
/// <summary> /// Migrate from a setup to another setup /// </summary> internal async Task <SyncContext> InternalMigrationAsync(SyncContext context, SyncSet schema, SyncSetup oldSetup, SyncSetup newSetup, DbConnection connection, DbTransaction transaction, CancellationToken cancellationToken, IProgress <ProgressArgs> progress) { // Create a new migration var migration = new Migration(newSetup, oldSetup); // get comparision results var migrationResults = migration.Compare(); // Launch InterceptAsync on Migrating await this.InterceptAsync(new MigratingArgs(context, schema, oldSetup, newSetup, migrationResults, connection, transaction), cancellationToken).ConfigureAwait(false); // Deprovision triggers stored procedures and tracking table if required foreach (var migrationTable in migrationResults.Tables) { // using a fake SyncTable based on oldSetup, since we don't need columns, but we need to have the filters var schemaTable = new SyncTable(migrationTable.SetupTable.TableName, migrationTable.SetupTable.SchemaName); // Create a temporary SyncSet for attaching to the schemaTable var tmpSchema = new SyncSet(); // Add this table to schema tmpSchema.Tables.Add(schemaTable); tmpSchema.EnsureSchema(); // copy filters from old setup foreach (var filter in oldSetup.Filters) { tmpSchema.Filters.Add(filter); } // using a fake Synctable, since we don't need columns to deprovision var tableBuilder = this.GetTableBuilder(schemaTable, oldSetup); // Deprovision stored procedures if (migrationTable.StoredProcedures == MigrationAction.Drop || migrationTable.StoredProcedures == MigrationAction.Create) { await InternalDropStoredProceduresAsync(context, tableBuilder, connection, transaction, cancellationToken, progress).ConfigureAwait(false); } // Deprovision triggers if (migrationTable.Triggers == MigrationAction.Drop || migrationTable.Triggers == MigrationAction.Create) { await InternalDropTriggersAsync(context, tableBuilder, connection, transaction, cancellationToken, progress).ConfigureAwait(false); } // Deprovision tracking table if (migrationTable.TrackingTable == MigrationAction.Drop || migrationTable.TrackingTable == MigrationAction.Create) { var exists = await InternalExistsTrackingTableAsync(context, tableBuilder, connection, transaction, cancellationToken, progress).ConfigureAwait(false); if (exists) { await InternalDropTrackingTableAsync(context, oldSetup, tableBuilder, connection, transaction, cancellationToken, progress).ConfigureAwait(false); } } // Removing cached commands var syncAdapter = this.GetSyncAdapter(schemaTable, oldSetup); syncAdapter.RemoveCommands(); } // Provision table (create or alter), tracking tables, stored procedures and triggers // Need the real SyncSet since we need columns definition foreach (var migrationTable in migrationResults.Tables) { var syncTable = schema.Tables[migrationTable.SetupTable.TableName, migrationTable.SetupTable.SchemaName]; var oldTable = oldSetup.Tables[migrationTable.SetupTable.TableName, migrationTable.SetupTable.SchemaName]; if (syncTable == null) { continue; } var tableBuilder = this.GetTableBuilder(syncTable, newSetup); // Re provision table if (migrationTable.Table == MigrationAction.Create) { // Check if we need to create a schema there var schemaExists = await InternalExistsSchemaAsync(context, tableBuilder, connection, transaction, cancellationToken, progress).ConfigureAwait(false); if (!schemaExists) { await InternalCreateSchemaAsync(context, newSetup, tableBuilder, connection, transaction, cancellationToken, progress).ConfigureAwait(false); } var exists = await InternalExistsTableAsync(context, tableBuilder, connection, transaction, cancellationToken, progress).ConfigureAwait(false); if (!exists) { await InternalCreateTableAsync(context, newSetup, tableBuilder, connection, transaction, cancellationToken, progress).ConfigureAwait(false); } } // Re provision table if (migrationTable.Table == MigrationAction.Alter) { var exists = await InternalExistsTableAsync(context, tableBuilder, connection, transaction, cancellationToken, progress).ConfigureAwait(false); if (!exists) { await InternalCreateTableAsync(context, newSetup, tableBuilder, connection, transaction, cancellationToken, progress).ConfigureAwait(false); } else if (oldTable != null) { //get new columns to add var newColumns = syncTable.Columns.Where(c => !oldTable.Columns.Any(oldC => string.Equals(oldC, c.ColumnName, SyncGlobalization.DataSourceStringComparison))); if (newColumns != null) { foreach (var newColumn in newColumns) { var columnExist = await InternalExistsColumnAsync(context, newColumn.ColumnName, tableBuilder, connection, transaction, cancellationToken, progress).ConfigureAwait(false); if (!columnExist) { await InternalAddColumnAsync(context, newSetup, newColumn.ColumnName, tableBuilder, connection, transaction, cancellationToken, progress).ConfigureAwait(false); } } } } } // Re provision tracking table if (migrationTable.TrackingTable == MigrationAction.Rename && oldTable != null) { var(_, oldTableName) = this.Provider.GetParsers(new SyncTable(oldTable.TableName, oldTable.SchemaName), oldSetup); await InternalRenameTrackingTableAsync(context, newSetup, oldTableName, tableBuilder, connection, transaction, cancellationToken, progress).ConfigureAwait(false); } else if (migrationTable.TrackingTable == MigrationAction.Create) { var exists = await InternalExistsTrackingTableAsync(context, tableBuilder, connection, transaction, cancellationToken, progress).ConfigureAwait(false); if (exists) { await InternalDropTrackingTableAsync(context, newSetup, tableBuilder, connection, transaction, cancellationToken, progress).ConfigureAwait(false); } await InternalCreateTrackingTableAsync(context, newSetup, tableBuilder, connection, transaction, cancellationToken, progress).ConfigureAwait(false); } // Re provision stored procedures if (migrationTable.StoredProcedures == MigrationAction.Create) { await InternalCreateStoredProceduresAsync(context, true, tableBuilder, connection, transaction, cancellationToken, progress).ConfigureAwait(false); } // Re provision triggers if (migrationTable.Triggers == MigrationAction.Create) { await InternalCreateTriggersAsync(context, true, tableBuilder, connection, transaction, cancellationToken, progress).ConfigureAwait(false); } } // InterceptAsync Migrated var args = new MigratedArgs(context, schema, newSetup, migrationResults, connection, transaction); await this.InterceptAsync(args, cancellationToken).ConfigureAwait(false); this.ReportProgress(context, progress, args); return(context); }
/// <summary> /// update configuration object with tables desc from server database /// </summary> public async Task <(SyncContext, SyncSet)> GetSchemaAsync(SyncContext context, SyncSetup setup, DbConnection connection, DbTransaction transaction, CancellationToken cancellationToken, IProgress <ProgressArgs> progress) { if (setup == null || setup.Tables.Count <= 0) { throw new MissingTablesException(); } // Create the schema var schema = new SyncSet(); // copy filters from setup foreach (var filter in setup.Filters) { schema.Filters.Add(filter); } var relations = new List <DbRelationDefinition>(20); foreach (var setupTable in setup.Tables) { this.Orchestrator.logger.LogDebug(SyncEventsId.GetSchema, setupTable); var builderTable = this.GetTableManagerFactory(setupTable.TableName, setupTable.SchemaName); var tblManager = builderTable.CreateManagerTable(connection, transaction); // Check if table exists var syncTable = await tblManager.GetTableAsync().ConfigureAwait(false); if (syncTable == null) { throw new MissingTableException(string.IsNullOrEmpty(setupTable.SchemaName) ? setupTable.TableName : setupTable.SchemaName + "." + setupTable.TableName); } // get columns list var lstColumns = await tblManager.GetColumnsAsync().ConfigureAwait(false); if (this.Orchestrator.logger.IsEnabled(LogLevel.Debug)) { foreach (var col in lstColumns) { this.Orchestrator.logger.LogDebug(SyncEventsId.GetSchema, col); } } // Validate the column list and get the dmTable configuration object. this.FillSyncTableWithColumns(setupTable, syncTable, lstColumns, tblManager); // Add this table to schema schema.Tables.Add(syncTable); // Check primary Keys await SetPrimaryKeysAsync(syncTable, tblManager).ConfigureAwait(false); // get all relations var tableRelations = await tblManager.GetRelationsAsync().ConfigureAwait(false); // Since we are not sure of the order of reading tables // create a tmp relations list relations.AddRange(tableRelations); } // Parse and affect relations to schema SetRelations(relations, schema); // Ensure all objects have correct relations to schema schema.EnsureSchema(); return(context, schema); }