internal async Task <(SyncTable SyncTable, IEnumerable <DbRelationDefinition> Relations)> InternalGetTableSchemaAsync(
SyncContext context, SyncSetup setup, SetupTable setupTable, DbConnection connection, DbTransaction transaction,
CancellationToken cancellationToken, IProgress <ProgressArgs> progress)
{
// ensure table is compliante with name / schema with provider
var syncTable = await this.Provider.GetDatabaseBuilder().EnsureTableAsync(setupTable.TableName, setupTable.SchemaName, connection, transaction);
var tableBuilder = this.GetTableBuilder(syncTable, setup);
var exists = await InternalExistsTableAsync(context, tableBuilder, connection, transaction, cancellationToken, progress).ConfigureAwait(false);
if (!exists)
{
throw new MissingTableException(string.IsNullOrEmpty(setupTable.SchemaName) ? setupTable.TableName : setupTable.SchemaName + "." + setupTable.TableName);
}
// get columns list
var lstColumns = await tableBuilder.GetColumnsAsync(connection, transaction).ConfigureAwait(false);
// Validate the column list and get the dmTable configuration object.
this.FillSyncTableWithColumns(setupTable, syncTable, lstColumns);
// Check primary Keys
await SetPrimaryKeysAsync(syncTable, tableBuilder, connection, transaction).ConfigureAwait(false);
// get all relations
var tableRelations = await tableBuilder.GetRelationsAsync(connection, transaction).ConfigureAwait(false);
return(syncTable, tableRelations);
}
/// <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);
/// <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>
/// 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>
/// Drop a table
/// </summary>
/// <param name="table">A table from your Setup instance you want to drop</param>
public Task <bool> DropTableAsync(SetupTable table, DbConnection connection = default, DbTransaction transaction = default, CancellationToken cancellationToken = default, IProgress <ProgressArgs> progress = null)
=> RunInTransactionAsync(SyncStage.Deprovisioning, async(ctx, connection, transaction) =>
{
var hasBeenDropped = 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 InternalExistsTableAsync(ctx, tableBuilder, connection, transaction, cancellationToken, progress).ConfigureAwait(false);
if (exists)
{
hasBeenDropped = await InternalDropTableAsync(ctx, tableBuilder, connection, transaction, cancellationToken, progress).ConfigureAwait(false);
}
return(hasBeenDropped);
}, connection, transaction, cancellationToken);
/// <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>
/// 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>
/// 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, CancellationToken cancellationToken = default, IProgress <ProgressArgs> progress = null)
{
var setup = new SyncSetup();
setup.Tables.Add(table);
return(this.DeprovisionAsync(new SyncSet(setup), provision, cancellationToken, progress));
}
/// <summary>
/// Drop all triggers
/// </summary>
/// <param name="table">A table from your Setup instance, where you want to drop all the triggers</param>
public Task <bool> DropTriggersAsync(SetupTable table, DbConnection connection = default, DbTransaction transaction = default, CancellationToken cancellationToken = default, IProgress <ProgressArgs> progress = null)
=> RunInTransactionAsync(SyncStage.Deprovisioning, (ctx, connection, transaction) =>
{
// 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);
return(this.InternalDropTriggersAsync(ctx, tableBuilder, connection, transaction, cancellationToken, progress));
}, connection, transaction, cancellationToken);
/// <summary>
/// Enabling constraints on one table
/// </summary>
public Task <bool> EnableConstraintsAsync(SetupTable table, 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);
var syncAdapter = this.GetSyncAdapter(schemaTable, this.Setup);
await this.InternalEnableConstraintsAsync(ctx, syncAdapter, connection, transaction).ConfigureAwait(false);
return(true);
}, connection, transaction, cancellationToken);
/// <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);
/// <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>
/// Create a Stored Procedure
/// </summary>
/// <param name="table">A table from your Setup instance, where you want to create the Stored Procedures</param>
/// <param name="overwrite">If true, drop the existing Stored Procedures then create them all, again</param>
public Task <bool> CreateStoredProceduresAsync(SetupTable table, bool overwrite = false, DbConnection connection = default, DbTransaction transaction = default, CancellationToken cancellationToken = default, IProgress <ProgressArgs> progress = null)
=> RunInTransactionAsync(SyncStage.Provisioning, async(ctx, connection, transaction) =>
{
var schema = await this.InternalGetSchemaAsync(ctx, this.Setup, 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 r = await InternalCreateStoredProceduresAsync(ctx, overwrite, tableBuilder, connection, transaction, cancellationToken, progress).ConfigureAwait(false);
return(r);
}, connection, transaction, cancellationToken);
private async Task <(SyncTable SyncTable, IEnumerable <DbRelationDefinition> Relations)> InternalGetTableSchemaAsync
(SyncContext context, SetupTable setupTable, DbConnection connection, DbTransaction transaction,
CancellationToken cancellationToken, IProgress <ProgressArgs> progress)
{
if (Provider == null)
{
throw new MissingProviderException(nameof(InternalGetTableSchemaAsync));
}
// ensure table is compliante with name / schema with provider
var syncTable = await this.Provider.GetDatabaseBuilder().EnsureTableAsync(setupTable.TableName, setupTable.SchemaName, connection, transaction);
// tmp scope info
var scopeInfo = this.InternalCreateScopeInfo(context.ScopeName, DbScopeType.Client);
scopeInfo.Setup = new SyncSetup();
scopeInfo.Setup.Tables.Add(setupTable);
var tableBuilder = this.GetTableBuilder(syncTable, scopeInfo);
bool exists;
(context, exists) = await InternalExistsTableAsync(scopeInfo, context, tableBuilder, connection, transaction, cancellationToken, progress).ConfigureAwait(false);
if (!exists)
{
throw new MissingTableException(setupTable.TableName, setupTable.SchemaName, scopeInfo.Name);
}
// get columns list
var lstColumns = await tableBuilder.GetColumnsAsync(connection, transaction).ConfigureAwait(false);
// Validate the column list and get the dmTable configuration object.
this.FillSyncTableWithColumns(setupTable, syncTable, lstColumns);
// Check primary Keys
await SetPrimaryKeysAsync(syncTable, tableBuilder, connection, transaction).ConfigureAwait(false);
// get all relations
var tableRelations = await tableBuilder.GetRelationsAsync(connection, transaction).ConfigureAwait(false);
return(syncTable, tableRelations);
}
/// <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);
/// <summary>
/// Generate the DmTable configuration from a given columns list
/// Validate that all columns are currently supported by the provider
/// </summary>
private void FillSyncTableWithColumns(SetupTable setupTable, SyncTable schemaTable, IEnumerable <SyncColumn> columns)
{
schemaTable.OriginalProvider = this.Provider.GetProviderTypeName();
schemaTable.SyncDirection = setupTable.SyncDirection;
var ordinal = 0;
// Eventually, do not raise exception here, just we don't have any columns
if (columns == null || columns.Any() == false)
{
return;
}
// Delete all existing columns
if (schemaTable.PrimaryKeys.Count > 0)
{
schemaTable.PrimaryKeys.Clear();
}
if (schemaTable.Columns.Count > 0)
{
schemaTable.Columns.Clear();
}
IEnumerable <SyncColumn> lstColumns;
// Validate columns list from setup table if any
if (setupTable.Columns != null && setupTable.Columns.Count > 1)
{
lstColumns = new List <SyncColumn>();
foreach (var setupColumn in setupTable.Columns)
{
// Check if the columns list contains the column name we specified in the setup
var column = columns.FirstOrDefault(c => c.ColumnName.Equals(setupColumn, SyncGlobalization.DataSourceStringComparison));
if (column == null)
{
throw new MissingColumnException(setupColumn, schemaTable.TableName);
}
else
{
((List <SyncColumn>)lstColumns).Add(column);
}
}
}
else
{
lstColumns = columns;
}
foreach (var column in lstColumns.OrderBy(c => c.Ordinal))
{
// First of all validate if the column is currently supported
if (!this.Provider.GetMetadata().IsValid(column))
{
throw new UnsupportedColumnTypeException(column.ColumnName, column.OriginalTypeName, this.Provider.GetProviderTypeName());
}
var columnNameLower = column.ColumnName.ToLowerInvariant();
if (columnNameLower == "sync_scope_name" ||
columnNameLower == "scope_timestamp" ||
columnNameLower == "scope_is_local" ||
columnNameLower == "scope_last_sync" ||
columnNameLower == "create_scope_id" ||
columnNameLower == "update_scope_id" ||
columnNameLower == "create_timestamp" ||
columnNameLower == "update_timestamp" ||
columnNameLower == "timestamp" ||
columnNameLower == "sync_row_is_tombstone" ||
columnNameLower == "last_change_datetime" ||
columnNameLower == "sync_scope_name" ||
columnNameLower == "sync_scope_name"
)
{
throw new UnsupportedColumnNameException(column.ColumnName, column.OriginalTypeName, this.Provider.GetProviderTypeName());
}
// Validate max length
column.MaxLength = this.Provider.GetMetadata().ValidateMaxLength(column.OriginalTypeName, column.IsUnsigned, column.IsUnicode, column.MaxLength);
// Gets the datastore owner dbType (could be SqlDbtype, MySqlDbType, SqliteDbType, NpgsqlDbType & so on ...)
var datastoreDbType = this.Provider.GetMetadata().ValidateOwnerDbType(column.OriginalTypeName, column.IsUnsigned, column.IsUnicode, column.MaxLength);
// once we have the datastore type, we can have the managed type
var columnType = this.Provider.GetMetadata().ValidateType(datastoreDbType);
// and the DbType
column.DbType = (int)this.Provider.GetMetadata().ValidateDbType(column.OriginalTypeName, column.IsUnsigned, column.IsUnicode, column.MaxLength);
// Gets the owner dbtype (SqlDbType, OracleDbType, MySqlDbType, NpsqlDbType & so on ...)
// Sqlite does not have it's own type, so it's DbType too
column.OriginalDbType = datastoreDbType.ToString();
// Validate if column should be readonly
column.IsReadOnly = this.Provider.GetMetadata().ValidateIsReadonly(column);
// set position ordinal
column.Ordinal = ordinal;
ordinal++;
// Validate the precision and scale properties
if (this.Provider.GetMetadata().IsNumericType(column.OriginalTypeName))
{
if (this.Provider.GetMetadata().SupportScale(column.OriginalTypeName))
{
var(p, s) = this.Provider.GetMetadata().ValidatePrecisionAndScale(column);
column.Precision = p;
column.PrecisionSpecified = true;
column.Scale = s;
column.ScaleSpecified = true;
}
else
{
column.Precision = this.Provider.GetMetadata().ValidatePrecision(column);
column.PrecisionSpecified = true;
column.ScaleSpecified = false;
}
}
// if setup table has no columns, we add all columns from db
// otherwise check if columns exist in the data source
if (setupTable.Columns == null || setupTable.Columns.Count <= 0 || setupTable.Columns.Contains(column.ColumnName))
{
schemaTable.Columns.Add(column);
}
// If column does not allow null value and is not compute
// We will not be able to insert a row, so raise an error
else if (!column.AllowDBNull && !column.IsCompute && !column.IsReadOnly && string.IsNullOrEmpty(column.DefaultValue))
{
throw new Exception($"Column {column.ColumnName} is not part of your setup. But it seems this columns is mandatory in your data source.");
}
}
}
public MigrationSetupTable(SetupTable table)
{
this.SetupTable = table;
}
