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