/// <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>
/// 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>
/// 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>
/// 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);
/// <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(setupTable.GetFullName(), column.ColumnName, column.OriginalTypeName, this.Provider.GetProviderTypeName());
}
var columnNameLower = column.ColumnName.ToLowerInvariant();
if (columnNameLower == "sync_scope_id" ||
columnNameLower == "changeTable" ||
columnNameLower == "sync_scope_name" ||
columnNameLower == "sync_min_timestamp" ||
columnNameLower == "sync_row_count" ||
columnNameLower == "sync_force_write" ||
columnNameLower == "sync_update_scope_id" ||
columnNameLower == "sync_timestamp" ||
columnNameLower == "sync_row_is_tombstone"
)
{
throw new UnsupportedColumnNameException(setupTable.GetFullName(), column.ColumnName, column.OriginalTypeName, this.Provider.GetProviderTypeName());
}
// Gets the max length
column.MaxLength = this.Provider.GetMetadata().GetMaxLength(column);
// 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 = this.Provider.GetMetadata().GetOwnerDbType(column).ToString();
// get the downgraded DbType
column.DbType = (int)this.Provider.GetMetadata().GetDbType(column);
// Gets the column readonly's propertye
column.IsReadOnly = this.Provider.GetMetadata().IsReadonly(column);
// set position ordinal
column.Ordinal = ordinal;
ordinal++;
// Validate the precision and scale properties
if (this.Provider.GetMetadata().IsNumericType(column))
{
if (this.Provider.GetMetadata().IsSupportingScale(column))
{
var(p, s) = this.Provider.GetMetadata().GetPrecisionAndScale(column);
column.Precision = p;
column.PrecisionIsSpecified = true;
column.Scale = s;
column.ScaleIsSpecified = true;
}
else
{
column.Precision = this.Provider.GetMetadata().GetPrecision(column);
column.PrecisionIsSpecified = true;
column.ScaleIsSpecified = false;
}
}
// Get the managed type
// Important to set it at the end, because we are altering column.DataType here
column.SetType(this.Provider.GetMetadata().GetType(column));
// 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($"In table {setupTable.GetFullName()}, column {column.ColumnName} is not part of your setup. But it seems this columns is mandatory in your data source.");
}
}
}
