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