/// <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 async Task <bool> CreateStoredProceduresAsync(IScopeInfo scopeInfo, string tableName, string schemaName = null, bool overwrite = false, DbConnection connection = default, DbTransaction transaction = default, CancellationToken cancellationToken = default, IProgress <ProgressArgs> progress = null)
{
var context = new SyncContext(Guid.NewGuid(), scopeInfo.Name);
try
{
if (scopeInfo.Schema == null || !scopeInfo.Schema.HasTables || !scopeInfo.Schema.HasColumns)
{
return(false);
}
var syncTable = scopeInfo.Schema.Tables[tableName, schemaName];
if (syncTable == null)
{
return(false);
}
await using var runner = await this.GetConnectionAsync(context, SyncMode.Writing, SyncStage.Provisioning, connection, transaction, cancellationToken, progress).ConfigureAwait(false);
// Get table builder
var tableBuilder = this.GetTableBuilder(syncTable, scopeInfo);
bool isCreated;
(context, isCreated) = await InternalCreateStoredProceduresAsync(scopeInfo, context, overwrite, tableBuilder, runner.Connection, runner.Transaction, cancellationToken, progress).ConfigureAwait(false);
await runner.CommitAsync().ConfigureAwait(false);
return(isCreated);
}
catch (Exception ex)
{
throw GetSyncError(context, ex);
}
}
private async Task <Version> UpgdrateTo801Async(IScopeInfo serverScopeInfo, SyncContext context, DbConnection connection, DbTransaction transaction,
CancellationToken cancellationToken, IProgress <ProgressArgs> progress)
{
var newVersion = new Version(0, 8, 1);
// Sorting tables based on dependencies between them
var schemaTables = serverScopeInfo.Schema.Tables
.SortByDependencies(tab => tab.GetRelations()
.Select(r => r.GetParentTable()));
var message = $"Upgrade to {newVersion}:";
await this.InterceptAsync(new UpgradeProgressArgs(context, message, newVersion, connection, transaction), progress, cancellationToken);
foreach (var schemaTable in schemaTables)
{
var tableBuilder = this.GetTableBuilder(schemaTable, serverScopeInfo);
await InternalCreateStoredProceduresAsync(serverScopeInfo, context, true, tableBuilder, connection, transaction, cancellationToken, progress).ConfigureAwait(false);
await this.InterceptAsync(new UpgradeProgressArgs(context, $"ALL Stored procedures for table {tableBuilder.TableDescription.GetFullName()} updated", newVersion, connection, transaction), progress, cancellationToken).ConfigureAwait(false);
}
message = $"Upgrade to {newVersion} for scope {serverScopeInfo.Name}.";
await this.InterceptAsync(new UpgradeProgressArgs(context, message, newVersion, connection, transaction), progress, cancellationToken);
return(newVersion);
}
private async Task <Version> UpgdrateTo602Async(IScopeInfo serverScopeInfo, SyncContext context, DbConnection connection, DbTransaction transaction, CancellationToken cancellationToken, IProgress <ProgressArgs> progress)
{
var newVersion = new Version(0, 6, 2);
// Sorting tables based on dependencies between them
var schemaTables = serverScopeInfo.Schema.Tables
.SortByDependencies(tab => tab.GetRelations()
.Select(r => r.GetParentTable()));
foreach (var schemaTable in schemaTables)
{
var tableBuilder = this.GetTableBuilder(schemaTable, serverScopeInfo);
// Upgrade Select Initial Changes
bool exists;
(context, exists) = await InternalExistsTriggerAsync(serverScopeInfo, context, tableBuilder, DbTriggerType.Update, connection, transaction, cancellationToken, progress).ConfigureAwait(false);
if (exists)
{
(context, _) = await InternalDropTriggerAsync(serverScopeInfo, context, tableBuilder, DbTriggerType.Update, connection, transaction, cancellationToken, progress).ConfigureAwait(false);
}
(context, _) = await InternalCreateTriggerAsync(serverScopeInfo, context, tableBuilder, DbTriggerType.Update, connection, transaction, cancellationToken, progress).ConfigureAwait(false);
await this.InterceptAsync(new UpgradeProgressArgs(context, $"Update Trigger for table {tableBuilder.TableDescription.GetFullName()} updated", newVersion, connection, transaction), progress, cancellationToken).ConfigureAwait(false);
}
var message = $"Upgrade to {newVersion} for scope {serverScopeInfo.Name}.";
await this.InterceptAsync(new UpgradeProgressArgs(context, message, newVersion, connection, transaction), progress, cancellationToken);
return(newVersion);
}
/// <summary>
/// Create a tracking table
/// </summary>
/// <param name="table">A table from your Setup instance you want to create</param>
public async Task <bool> CreateTrackingTablesAsync(IScopeInfo scopeInfo, bool overwrite = false, DbConnection connection = default, DbTransaction transaction = default, CancellationToken cancellationToken = default, IProgress <ProgressArgs> progress = null)
{
var context = new SyncContext(Guid.NewGuid(), scopeInfo.Name);
try
{
if (scopeInfo.Schema == null || !scopeInfo.Schema.HasTables || !scopeInfo.Schema.HasColumns)
{
return(false);
}
await using var runner = await this.GetConnectionAsync(context, SyncMode.Writing, SyncStage.Provisioning, connection, transaction, cancellationToken, progress).ConfigureAwait(false);
var atLeastOneHasBeenCreated = false;
foreach (var schemaTable in scopeInfo.Schema.Tables)
{
// Get table builder
var tableBuilder = this.GetTableBuilder(schemaTable, scopeInfo);
bool schemaExists;
(context, schemaExists) = await InternalExistsSchemaAsync(scopeInfo, context, tableBuilder, runner.Connection, runner.Transaction, cancellationToken, progress).ConfigureAwait(false);
if (!schemaExists)
{
(context, _) = await InternalCreateSchemaAsync(scopeInfo, context, tableBuilder, runner.Connection, runner.Transaction, cancellationToken, progress).ConfigureAwait(false);
}
bool exists;
(context, exists) = await InternalExistsTrackingTableAsync(scopeInfo, context, tableBuilder, runner.Connection, runner.Transaction, cancellationToken, progress).ConfigureAwait(false);
// should create only if not exists OR if overwrite has been set
var shouldCreate = !exists || overwrite;
if (shouldCreate)
{
// Drop if already exists and we need to overwrite
if (exists && overwrite)
{
(context, _) = await InternalDropTrackingTableAsync(scopeInfo, context, tableBuilder, runner.Connection, runner.Transaction, cancellationToken, progress).ConfigureAwait(false);
}
bool hasBeenCreated;
(context, hasBeenCreated) = await InternalCreateTrackingTableAsync(scopeInfo, context, tableBuilder, runner.Connection, runner.Transaction, cancellationToken, progress).ConfigureAwait(false);
if (hasBeenCreated)
{
atLeastOneHasBeenCreated = true;
}
}
}
await runner.CommitAsync().ConfigureAwait(false);
return(atLeastOneHasBeenCreated);
}
catch (Exception ex)
{
throw GetSyncError(context, ex);
}
}
private async Task <Version> UpgdrateTo096Async(IScopeInfo scopeInfo, SyncContext context, DbConnection connection, DbTransaction transaction,
CancellationToken cancellationToken, IProgress <ProgressArgs> progress)
{
var newVersion = new Version(0, 9, 9);
await this.InterceptAsync(new UpgradeProgressArgs(context, $"Upgrade to {newVersion}:", newVersion, connection, transaction), progress, cancellationToken).ConfigureAwait(false);
// get scope info table name
var scopeInfoTableName = ParserName.Parse(this.Options.ScopeInfoTableName);
var tableName = $"{scopeInfoTableName.Unquoted().Normalized().ToString()}";
var historyTableName = $"{tableName}_history";
var syncTable = new SyncTable(historyTableName);
var historyTableBuilder = this.GetTableBuilder(syncTable, scopeInfo);
if (this.Provider.GetProviderTypeName().Contains("Dotmim.Sync.SqlServer.SqlSyncProvider"))
{
var commandText = @$ "IF NOT EXISTS(SELECT col.name AS name FROM sys.columns as col
INNER join sys.tables as tbl on tbl.object_id = col.object_id
WHERE tbl.name = '{historyTableName}' and col.name = 'scope_properties')
ALTER TABLE {historyTableName} ADD scope_properties nvarchar(MAX) NULL;";
var command = connection.CreateCommand();
command.CommandText = commandText;
command.Transaction = transaction;
await command.ExecuteNonQueryAsync();
await this.InterceptAsync(new UpgradeProgressArgs(context, $"{historyTableName} primary keys updated on SQL Server", newVersion, connection, transaction), progress, cancellationToken).ConfigureAwait(false);
}
/// <summary>
/// Internal drop trigger routine
/// </summary>
internal async Task <(SyncContext context, bool dropped)> InternalDropTriggerAsync(
IScopeInfo scopeInfo, SyncContext context, DbTableBuilder tableBuilder, DbTriggerType triggerType, DbConnection connection, DbTransaction transaction, CancellationToken cancellationToken, IProgress <ProgressArgs> progress)
{
using var command = await tableBuilder.GetDropTriggerCommandAsync(triggerType, connection, transaction).ConfigureAwait(false);
if (command == null)
{
return(context, false);
}
var action = await this.InterceptAsync(new TriggerDroppingArgs(context, tableBuilder.TableDescription, triggerType, command, connection, transaction), progress, cancellationToken).ConfigureAwait(false);
if (action.Cancel || action.Command == null)
{
return(context, false);
}
await this.InterceptAsync(new DbCommandArgs(context, action.Command, connection, transaction), progress, cancellationToken).ConfigureAwait(false);
await action.Command.ExecuteNonQueryAsync();
await this.InterceptAsync(new TriggerDroppedArgs(context, tableBuilder.TableDescription, triggerType, connection, transaction), progress, cancellationToken).ConfigureAwait(false);
action.Command.Dispose();
return(context, true);
}
/// <summary>
/// Internal drop triggers routine
/// </summary>
internal async Task <(SyncContext context, bool dropped)> InternalDropTriggersAsync(
IScopeInfo scopeInfo, SyncContext context, DbTableBuilder tableBuilder, DbConnection connection, DbTransaction transaction, CancellationToken cancellationToken, IProgress <ProgressArgs> progress)
{
var hasDroppeAtLeastOneTrigger = false;
var listTriggerType = Enum.GetValues(typeof(DbTriggerType));
foreach (DbTriggerType triggerType in listTriggerType)
{
bool exists;
(context, exists) = await InternalExistsTriggerAsync(scopeInfo, context, tableBuilder, triggerType, connection, transaction, cancellationToken, progress).ConfigureAwait(false);
if (!exists)
{
continue;
}
bool dropped;
(context, dropped) = await InternalDropTriggerAsync(scopeInfo, context, tableBuilder, triggerType, connection, transaction, cancellationToken, progress).ConfigureAwait(false);
if (dropped)
{
hasDroppeAtLeastOneTrigger = true;
}
}
return(context, hasDroppeAtLeastOneTrigger);
}
/// <summary>
/// Internal drop tracking table routine
/// </summary>
internal async Task <(SyncContext context, bool dropped)> InternalDropTrackingTableAsync(
IScopeInfo scopeInfo, SyncContext context, DbTableBuilder tableBuilder, DbConnection connection, DbTransaction transaction, CancellationToken cancellationToken, IProgress <ProgressArgs> progress)
{
await using var runner = await this.GetConnectionAsync(context, SyncMode.Writing, SyncStage.Deprovisioning, connection, transaction, cancellationToken, progress).ConfigureAwait(false);
using var command = await tableBuilder.GetDropTrackingTableCommandAsync(connection, transaction).ConfigureAwait(false);
if (command == null)
{
return(context, false);
}
var(_, trackingTableName) = this.Provider.GetParsers(tableBuilder.TableDescription, scopeInfo.Setup);
var action = await this.InterceptAsync(new TrackingTableDroppingArgs(context, tableBuilder.TableDescription, trackingTableName, command, connection, transaction), progress, cancellationToken).ConfigureAwait(false);
if (action.Cancel || action.Command == null)
{
return(context, false);
}
await this.InterceptAsync(new DbCommandArgs(context, action.Command, connection, transaction), progress, cancellationToken).ConfigureAwait(false);
await action.Command.ExecuteNonQueryAsync().ConfigureAwait(false);
await this.InterceptAsync(new TrackingTableDroppedArgs(context, tableBuilder.TableDescription, trackingTableName, connection, transaction), progress, cancellationToken).ConfigureAwait(false);
await runner.CommitAsync().ConfigureAwait(false);
action.Command.Dispose();
return(context, true);
}
/// <summary>
/// Check if a tracking table exists
/// </summary>
/// <param name="table">A table from your Setup instance, you want to check if the corresponding tracking table exists</param>
public async Task <bool> ExistTrackingTableAsync(IScopeInfo scopeInfo, string tableName, string schemaName = default, DbConnection connection = default, DbTransaction transaction = default, CancellationToken cancellationToken = default, IProgress <ProgressArgs> progress = null)
{
var context = new SyncContext(Guid.NewGuid(), scopeInfo.Name);
try
{
if (scopeInfo.Schema == null || !scopeInfo.Schema.HasTables || !scopeInfo.Schema.HasColumns)
{
return(false);
}
var schemaTable = scopeInfo.Schema.Tables[tableName, schemaName];
if (schemaTable == null)
{
return(false);
}
await using var runner = await this.GetConnectionAsync(context, SyncMode.Reading, SyncStage.None, connection, transaction, cancellationToken, progress).ConfigureAwait(false);
// Get table builder
var tableBuilder = this.GetTableBuilder(schemaTable, scopeInfo);
bool exists;
(context, exists) = await InternalExistsTrackingTableAsync(scopeInfo, context, tableBuilder, runner.Connection, runner.Transaction, cancellationToken, progress).ConfigureAwait(false);
return(exists);
}
catch (Exception ex)
{
throw GetSyncError(context, ex);
}
}
/// <summary>
/// Drop all triggers
/// </summary>
/// <param name="table">A table from your Setup instance, where you want to drop all the triggers</param>
public async Task <bool> DropTriggersAsync(IScopeInfo scopeInfo, string tableName, string schemaName = null, DbConnection connection = default, DbTransaction transaction = default, CancellationToken cancellationToken = default, IProgress <ProgressArgs> progress = null)
{
var context = new SyncContext(Guid.NewGuid(), scopeInfo.Name);
try
{
if (scopeInfo.Schema == null || scopeInfo.Schema.Tables == null || scopeInfo.Schema.Tables.Count <= 0 || !scopeInfo.Schema.HasColumns)
{
return(false);
}
var schemaTable = scopeInfo.Schema.Tables[tableName, schemaName];
if (schemaTable == null)
{
return(false);
}
await using var runner = await this.GetConnectionAsync(context, SyncMode.Writing, SyncStage.Deprovisioning, connection, transaction, cancellationToken, progress).ConfigureAwait(false);
// Get table builder
var tableBuilder = this.GetTableBuilder(schemaTable, scopeInfo);
bool dropped;
(context, dropped) = await this.InternalDropTriggersAsync(scopeInfo, context, tableBuilder, runner.Connection, runner.Transaction, cancellationToken, progress);
await runner.CommitAsync().ConfigureAwait(false);
return(dropped);
}
catch (Exception ex)
{
throw GetSyncError(context, ex);
}
}
/// <summary>
/// Internal drop storedProcedures routine
/// </summary>
internal async Task <(SyncContext context, bool dropped)> InternalDropStoredProceduresAsync(
IScopeInfo scopeInfo, SyncContext context, DbTableBuilder tableBuilder, DbConnection connection, DbTransaction transaction, CancellationToken cancellationToken, IProgress <ProgressArgs> progress)
{
// check bulk before
var hasDroppedAtLeastOneStoredProcedure = false;
var listStoredProcedureType = Enum.GetValues(typeof(DbStoredProcedureType)).Cast <DbStoredProcedureType>().OrderBy(sp => (int)sp);
foreach (DbStoredProcedureType storedProcedureType in Enum.GetValues(typeof(DbStoredProcedureType)))
{
bool exists;
(context, exists) = await InternalExistsStoredProcedureAsync(scopeInfo, context, tableBuilder, storedProcedureType, connection, transaction, cancellationToken, progress).ConfigureAwait(false);
if (exists)
{
bool dropped;
(context, dropped) = await InternalDropStoredProcedureAsync(scopeInfo, context, tableBuilder, storedProcedureType, connection, transaction, cancellationToken, progress).ConfigureAwait(false);
// If at least one stored proc has been dropped, we're good to return true;
if (dropped)
{
hasDroppedAtLeastOneStoredProcedure = true;
}
}
}
return(context, hasDroppedAtLeastOneStoredProcedure);
}
/// <summary>
/// Disabling constraints on one table
/// </summary>
public virtual async Task <SyncContext> DisableConstraintsAsync(IScopeInfo scopeInfo, string tableName, string schemaName = null, DbConnection connection = default, DbTransaction transaction = default, CancellationToken cancellationToken = default, IProgress <ProgressArgs> progress = null)
{
var context = new SyncContext(Guid.NewGuid(), scopeInfo.Name);
try
{
if (scopeInfo.Schema == null || !scopeInfo.Schema.HasTables || !scopeInfo.Schema.HasColumns)
{
return(context);
}
var schemaTable = scopeInfo.Schema.Tables[tableName, schemaName];
if (schemaTable == null)
{
return(context);
}
await using var runner = await this.GetConnectionAsync(context, SyncMode.Writing, SyncStage.None, connection, transaction, cancellationToken, progress).ConfigureAwait(false);
var syncAdapter = this.GetSyncAdapter(schemaTable, scopeInfo);
context = await this.InternalDisableConstraintsAsync(scopeInfo, context, syncAdapter, runner.Connection, runner.Transaction).ConfigureAwait(false);
await runner.CommitAsync().ConfigureAwait(false);
return(context);
}
catch (Exception ex)
{
throw GetSyncError(context, ex);
}
}
private async Task <Version> AutoUpgdrateToNewVersionAsync(IScopeInfo scopeInfo, SyncContext context, Version newVersion, DbConnection connection, DbTransaction transaction, CancellationToken cancellationToken, IProgress <ProgressArgs> progress)
{
var message = $"Upgrade to {newVersion} for scope {scopeInfo.Name}.";
await this.InterceptAsync(new UpgradeProgressArgs(context, message, newVersion, connection, transaction), progress, cancellationToken);
return(newVersion);
}
InternalReadSyncTableChangesAsync(
IScopeInfo scopeInfo, SyncContext context, Guid?excludintScopeId, SyncTable syncTable,
BatchInfo batchInfo, bool isNew, long?lastTimestamp,
DbConnection connection, DbTransaction transaction,
CancellationToken cancellationToken, IProgress <ProgressArgs> progress)
{
if (cancellationToken.IsCancellationRequested)
{
return(default);
/// <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 async Task <bool> CreateStoredProcedureAsync(IScopeInfo scopeInfo, string tableName, string schemaName = null,
DbStoredProcedureType storedProcedureType = default, bool overwrite = false,
DbConnection connection = default, DbTransaction transaction = default, CancellationToken cancellationToken = default, IProgress <ProgressArgs> progress = null)
{
var context = new SyncContext(Guid.NewGuid(), scopeInfo.Name);
try
{
if (scopeInfo.Schema == null || !scopeInfo.Schema.HasTables || !scopeInfo.Schema.HasColumns)
{
return(false);
}
var syncTable = scopeInfo.Schema.Tables[tableName, schemaName];
if (syncTable == null)
{
return(false);
}
await using var runner = await this.GetConnectionAsync(context, SyncMode.Writing, SyncStage.Provisioning, connection, transaction, cancellationToken, progress).ConfigureAwait(false);
bool hasBeenCreated = false;
// Get table builder
var tableBuilder = this.GetTableBuilder(syncTable, scopeInfo);
bool exists;
(context, exists) = await InternalExistsStoredProcedureAsync(scopeInfo, context, tableBuilder, storedProcedureType, runner.Connection, runner.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)
{
(context, _) = await InternalDropStoredProcedureAsync(scopeInfo, context, tableBuilder, storedProcedureType, runner.Connection, runner.Transaction, cancellationToken, progress).ConfigureAwait(false);
}
(context, hasBeenCreated) = await InternalCreateStoredProcedureAsync(scopeInfo, context, tableBuilder, storedProcedureType, runner.Connection, runner.Transaction, cancellationToken, progress).ConfigureAwait(false);
}
await runner.CommitAsync().ConfigureAwait(false);
return(hasBeenCreated);
}
catch (Exception ex)
{
throw GetSyncError(context, ex);
}
}
private async Task <Version> UpgdrateTo601Async(IScopeInfo scopeInfo, SyncContext context, DbConnection connection, DbTransaction transaction,
CancellationToken cancellationToken, IProgress <ProgressArgs> progress)
{
var newVersion = new Version(0, 6, 1);
// Sorting tables based on dependencies between them
var schemaTables = scopeInfo.Schema.Tables
.SortByDependencies(tab => tab.GetRelations()
.Select(r => r.GetParentTable()));
var message = $"Upgrade to {newVersion}:";
await this.InterceptAsync(new UpgradeProgressArgs(context, message, newVersion, connection, transaction), progress, cancellationToken).ConfigureAwait(false);
foreach (var schemaTable in schemaTables)
{
var tableBuilder = this.GetTableBuilder(schemaTable, scopeInfo);
// Upgrade Select Initial Changes
bool exists;
(context, exists) = await InternalExistsStoredProcedureAsync(scopeInfo, context, tableBuilder, DbStoredProcedureType.SelectInitializedChanges, connection, transaction, cancellationToken, progress).ConfigureAwait(false);
if (exists)
{
await InternalDropStoredProcedureAsync(scopeInfo, context, tableBuilder, DbStoredProcedureType.SelectInitializedChanges, connection, transaction, cancellationToken, progress).ConfigureAwait(false);
}
await InternalCreateStoredProcedureAsync(scopeInfo, context, tableBuilder, DbStoredProcedureType.SelectInitializedChanges, connection, transaction, cancellationToken, progress).ConfigureAwait(false);
await this.InterceptAsync(new UpgradeProgressArgs(context, $"SelectInitializedChanges stored procedure for table {tableBuilder.TableDescription.GetFullName()} updated", newVersion, connection, transaction), progress, cancellationToken).ConfigureAwait(false);
// Upgrade Select Initial Changes With Filter
if (tableBuilder.TableDescription.GetFilter() != null)
{
bool existsWithFilter;
(context, existsWithFilter) = await InternalExistsStoredProcedureAsync(scopeInfo, context, tableBuilder, DbStoredProcedureType.SelectInitializedChangesWithFilters, connection, transaction, cancellationToken, progress).ConfigureAwait(false);
if (existsWithFilter)
{
await InternalDropStoredProcedureAsync(scopeInfo, context, tableBuilder, DbStoredProcedureType.SelectInitializedChangesWithFilters, connection, transaction, cancellationToken, progress).ConfigureAwait(false);
}
await InternalCreateStoredProcedureAsync(scopeInfo, context, tableBuilder, DbStoredProcedureType.SelectInitializedChangesWithFilters, connection, transaction, cancellationToken, progress).ConfigureAwait(false);
await this.InterceptAsync(new UpgradeProgressArgs(context, $"SelectInitializedChangesWithFilters stored procedure for table {tableBuilder.TableDescription.GetFullName()} updated", newVersion, connection, transaction), progress, cancellationToken).ConfigureAwait(false);
}
}
return(newVersion);
}
/// <summary>
/// Reset a table, deleting rows from table and tracking_table
/// </summary>
internal async Task <SyncContext> InternalResetTableAsync(IScopeInfo scopeInfo, SyncContext context, DbSyncAdapter syncAdapter, DbConnection connection, DbTransaction transaction)
{
var(command, _) = await syncAdapter.GetCommandAsync(DbCommandType.Reset, connection, transaction);
if (command != null)
{
await this.InterceptAsync(new DbCommandArgs(context, command, connection, transaction)).ConfigureAwait(false);
await command.ExecuteNonQueryAsync().ConfigureAwait(false);
command.Dispose();
}
return(context);
}
/// <summary>
/// Rename a tracking table
/// </summary>
/// <param name="table">A table from your Setup instance you want to rename the tracking table</param>
//public async Task<bool> RenameTrackingTableAsync(SyncTable syncTable, ParserName oldTrackingTableName, DbConnection connection = default, DbTransaction transaction = default, CancellationToken cancellationToken = default, IProgress<ProgressArgs> progress = null)
//{
// try
// {
// await using var runner = await this.GetConnectionAsync(scopeName, SyncMode.Writing, SyncStage.Provisioning, connection, transaction, cancellationToken, progress).ConfigureAwait(false);
// var tableBuilder = this.GetTableBuilder(syncTable, this.Setup);
// await InternalRenameTrackingTableAsync(this.GetContext(), this.Setup, oldTrackingTableName, tableBuilder, runner.Connection, runner.Transaction, cancellationToken, progress).ConfigureAwait(false);
// await runner.CommitAsync().ConfigureAwait(false);
// return true;
// }
// catch (Exception ex)
// {
// throw GetSyncError(ex);
// }
//}
/// <summary>
/// Internal create tracking table routine
/// </summary>
internal async Task <(SyncContext context, bool crated)> InternalCreateTrackingTableAsync(
IScopeInfo scopeInfo, SyncContext context, DbTableBuilder tableBuilder, DbConnection connection, DbTransaction transaction, CancellationToken cancellationToken, IProgress <ProgressArgs> progress)
{
if (Provider == null)
{
throw new MissingProviderException(nameof(InternalCreateTrackingTableAsync));
}
await using var runner = await this.GetConnectionAsync(context, SyncMode.Writing, SyncStage.Provisioning, connection, transaction, cancellationToken, progress).ConfigureAwait(false);
if (tableBuilder.TableDescription.Columns.Count <= 0)
{
throw new MissingsColumnException(tableBuilder.TableDescription.GetFullName());
}
if (tableBuilder.TableDescription.PrimaryKeys.Count <= 0)
{
throw new MissingPrimaryKeyException(tableBuilder.TableDescription.GetFullName());
}
using var command = await tableBuilder.GetCreateTrackingTableCommandAsync(connection, transaction).ConfigureAwait(false);
if (command == null)
{
return(context, false);
}
var(_, trackingTableName) = this.Provider.GetParsers(tableBuilder.TableDescription, scopeInfo.Setup);
var action = await this.InterceptAsync(new TrackingTableCreatingArgs(context, tableBuilder.TableDescription, trackingTableName, command, connection, transaction), progress, cancellationToken).ConfigureAwait(false);
if (action.Cancel || action.Command == null)
{
return(context, false);
}
await this.InterceptAsync(new DbCommandArgs(context, action.Command, connection, transaction), progress, cancellationToken).ConfigureAwait(false);
await action.Command.ExecuteNonQueryAsync().ConfigureAwait(false);
await this.InterceptAsync(new TrackingTableCreatedArgs(context, tableBuilder.TableDescription, trackingTableName, connection, transaction), progress, cancellationToken).ConfigureAwait(false);
await runner.CommitAsync().ConfigureAwait(false);
action.Command.Dispose();
return(context, true);
}
internal IScopeInfo InternalCreateScopeInfo(string scopeName, DbScopeType scopeType)
{
// create a new scope id for the current owner (could be server or client as well)
IScopeInfo scope = scopeType switch
{
DbScopeType.Client => new ClientScopeInfo {
Id = Guid.NewGuid(), Name = scopeName, IsNewScope = true, LastSync = null, Version = SyncVersion.Current.ToString()
},
DbScopeType.Server => new ServerScopeInfo {
Name = scopeName, LastCleanupTimestamp = 0, IsNewScope = true, Version = SyncVersion.Current.ToString()
},
_ => throw new NotImplementedException($"Type of scope {scopeName} is not implemented when trying to get a single instance")
};
return(scope);
}
}
/// <summary>
/// Drop a Stored Procedure
/// </summary>
/// <param name="table">A table from your Setup instance, where you want to drop the Stored Procedure</param>
/// <param name="storedProcedureType">Stored Procedure type</param>
public async Task <bool> DropStoredProcedureAsync(IScopeInfo scopeInfo, string tableName, string schemaName = null, DbStoredProcedureType storedProcedureType = default, DbConnection connection = default, DbTransaction transaction = default, CancellationToken cancellationToken = default, IProgress <ProgressArgs> progress = null)
{
var context = new SyncContext(Guid.NewGuid(), scopeInfo.Name);
try
{
if (scopeInfo.Schema == null || scopeInfo.Schema.Tables == null || scopeInfo.Schema.Tables.Count <= 0 || !scopeInfo.Schema.HasColumns)
{
return(false);
}
var schemaTable = scopeInfo.Schema.Tables[tableName, schemaName];
if (schemaTable == null)
{
return(false);
}
await using var runner = await this.GetConnectionAsync(context, SyncMode.Writing, SyncStage.Deprovisioning, connection, transaction, cancellationToken, progress).ConfigureAwait(false);
bool hasBeenDropped = false;
var tableBuilder = this.GetTableBuilder(schemaTable, scopeInfo);
bool existsAndCanBeDeleted;
(context, existsAndCanBeDeleted) = await InternalExistsStoredProcedureAsync(scopeInfo, context, tableBuilder, storedProcedureType, runner.Connection, runner.Transaction, cancellationToken, progress).ConfigureAwait(false);
if (existsAndCanBeDeleted)
{
(context, hasBeenDropped) = await InternalDropStoredProcedureAsync(scopeInfo, context, tableBuilder, storedProcedureType, runner.Connection, runner.Transaction, cancellationToken, progress).ConfigureAwait(false);
}
// Removing cached commands
var syncAdapter = this.GetSyncAdapter(schemaTable, scopeInfo);
syncAdapter.RemoveCommands();
await runner.CommitAsync().ConfigureAwait(false);
return(hasBeenDropped);
}
catch (Exception ex)
{
throw GetSyncError(context, ex);
}
}
/// <summary>
/// Internal exists trigger procedure routine
/// </summary>
internal async Task <(SyncContext context, bool exists)> InternalExistsTriggerAsync(
IScopeInfo scopeInfo, SyncContext context, DbTableBuilder tableBuilder, DbTriggerType triggerType, DbConnection connection, DbTransaction transaction, CancellationToken cancellationToken, IProgress <ProgressArgs> progress)
{
// Get exists command
using var existsCommand = await tableBuilder.GetExistsTriggerCommandAsync(triggerType, connection, transaction).ConfigureAwait(false);
if (existsCommand == null)
{
return(context, false);
}
await this.InterceptAsync(new DbCommandArgs(context, existsCommand, connection, transaction), progress, cancellationToken).ConfigureAwait(false);
var existsResultObject = await existsCommand.ExecuteScalarAsync().ConfigureAwait(false);
var exists = Convert.ToInt32(existsResultObject) > 0;
return(context, exists);
}
/// <summary>
/// Is a variable declared in the given scope visible at the current level?
/// </summary>
/// <param name="scope">Scope to see if still visible</param>
/// <returns></returns>
/// <remarks>Check the booking scope - anywhere on the stack?</remarks>
public bool InScopeNow(IScopeInfo scope)
{
var s = scope as CurrentScopeInfo;
var bs = CurrentDeclarationScopePointer;
while (bs != null)
{
if (bs == s.BookingScope)
{
return(true);
}
var p = bs.Parent;
while (p != null && !(p is IBookingStatementBlock))
{
p = p.Parent;
}
bs = p as IBookingStatementBlock;
}
return(false);
}
/// <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 async Task <bool> DropStoredProceduresAsync(IScopeInfo scopeInfo, string tableName, string schemaName = null, DbConnection connection = default, DbTransaction transaction = default, CancellationToken cancellationToken = default, IProgress <ProgressArgs> progress = null)
{
var context = new SyncContext(Guid.NewGuid(), scopeInfo.Name);
try
{
if (scopeInfo.Schema == null || scopeInfo.Schema.Tables == null || scopeInfo.Schema.Tables.Count <= 0 || !scopeInfo.Schema.HasColumns)
{
return(false);
}
var schemaTable = scopeInfo.Schema.Tables[tableName, schemaName];
if (schemaTable == null)
{
return(false);
}
await using var runner = await this.GetConnectionAsync(context, SyncMode.Writing, SyncStage.Deprovisioning, connection, transaction, cancellationToken, progress).ConfigureAwait(false);
var hasDroppedAtLeastOneStoredProcedure = false;
// using a fake SyncTable based on SetupTable, since we don't need columns
var tableBuilder = this.GetTableBuilder(schemaTable, scopeInfo);
// check bulk before
(context, hasDroppedAtLeastOneStoredProcedure) = await InternalDropStoredProceduresAsync(scopeInfo, context, tableBuilder, runner.Connection, runner.Transaction, cancellationToken, progress).ConfigureAwait(false);
// Removing cached commands
var syncAdapter = this.GetSyncAdapter(schemaTable, scopeInfo);
syncAdapter.RemoveCommands();
await runner.CommitAsync().ConfigureAwait(false);
return(hasDroppedAtLeastOneStoredProcedure);
}
catch (Exception ex)
{
throw GetSyncError(context, ex);
}
}
/// <summary>
/// Drop all tables, declared in the Setup instance
/// </summary>
public async Task <bool> DropTablesAsync(IScopeInfo scopeInfo, DbConnection connection = default, DbTransaction transaction = default, CancellationToken cancellationToken = default, IProgress <ProgressArgs> progress = null)
{
var context = new SyncContext(Guid.NewGuid(), scopeInfo.Name);
try
{
if (scopeInfo.Schema == null || !scopeInfo.Schema.HasTables || !scopeInfo.Schema.HasColumns)
{
return(false);
}
await using var runner = await this.GetConnectionAsync(context, SyncMode.Writing, SyncStage.Deprovisioning, connection, transaction, cancellationToken, progress).ConfigureAwait(false);
bool atLeastOneTableHasBeenDropped = false;
// Sorting tables based on dependencies between them
var schemaTables = scopeInfo.Schema.Tables.SortByDependencies(tab => tab.GetRelations().Select(r => r.GetParentTable()));
foreach (var schemaTable in schemaTables.Reverse())
{
// Get table builder
var tableBuilder = this.GetTableBuilder(schemaTable, scopeInfo);
bool exists;
(context, exists) = await InternalExistsTableAsync(scopeInfo, context, tableBuilder, runner.Connection, runner.Transaction, cancellationToken, progress).ConfigureAwait(false);
if (exists)
{
(context, atLeastOneTableHasBeenDropped) = await InternalDropTableAsync(scopeInfo, context, tableBuilder, runner.Connection, runner.Transaction, cancellationToken, progress).ConfigureAwait(false);
}
}
await runner.CommitAsync().ConfigureAwait(false);
return(atLeastOneTableHasBeenDropped);
}
catch (Exception ex)
{
throw GetSyncError(context, ex);
}
}
/// <summary>
/// Internal create trigger routine
/// </summary>
internal async Task <(SyncContext context, bool created)> InternalCreateTriggerAsync(
IScopeInfo scopeInfo, SyncContext context, DbTableBuilder tableBuilder, DbTriggerType triggerType, DbConnection connection, DbTransaction transaction, CancellationToken cancellationToken, IProgress <ProgressArgs> progress)
{
if (tableBuilder.TableDescription.Columns.Count <= 0)
{
throw new MissingsColumnException(tableBuilder.TableDescription.GetFullName());
}
if (tableBuilder.TableDescription.PrimaryKeys.Count <= 0)
{
throw new MissingPrimaryKeyException(tableBuilder.TableDescription.GetFullName());
}
using var command = await tableBuilder.GetCreateTriggerCommandAsync(triggerType, connection, transaction).ConfigureAwait(false);
if (command == null)
{
return(context, false);
}
var action = await this.InterceptAsync(new TriggerCreatingArgs(context, tableBuilder.TableDescription, triggerType, command, connection, transaction), progress, cancellationToken).ConfigureAwait(false);
if (action.Cancel || action.Command == null)
{
return(context, false);
}
await this.InterceptAsync(new DbCommandArgs(context, action.Command, connection, transaction), progress, cancellationToken).ConfigureAwait(false);
await action.Command.ExecuteNonQueryAsync();
await this.InterceptAsync(new TriggerCreatedArgs(context, tableBuilder.TableDescription, triggerType, connection, transaction), progress, cancellationToken).ConfigureAwait(false);
action.Command.Dispose();
return(context, true);
}
/// <summary>
/// Internal create triggers routine
/// </summary>
internal async Task <(SyncContext context, bool created)> InternalCreateTriggersAsync(
IScopeInfo scopeInfo, SyncContext context, bool overwrite, DbTableBuilder tableBuilder, DbConnection connection, DbTransaction transaction, CancellationToken cancellationToken, IProgress <ProgressArgs> progress)
{
var hasCreatedAtLeastOneTrigger = false;
var listTriggerType = Enum.GetValues(typeof(DbTriggerType));
foreach (DbTriggerType triggerType in listTriggerType)
{
bool exists;
(context, exists) = await InternalExistsTriggerAsync(scopeInfo, context, tableBuilder, triggerType, connection, transaction, cancellationToken, progress).ConfigureAwait(false);
// Drop trigger if already exists
if (exists && overwrite)
{
(context, _) = await InternalDropTriggerAsync(scopeInfo, context, tableBuilder, triggerType, connection, transaction, cancellationToken, progress).ConfigureAwait(false);
}
var shouldCreate = !exists || overwrite;
if (!shouldCreate)
{
continue;
}
bool hasBeenCreated;
(context, hasBeenCreated) = await InternalCreateTriggerAsync(scopeInfo, context, tableBuilder, triggerType, connection, transaction, cancellationToken, progress).ConfigureAwait(false);
if (hasBeenCreated)
{
hasCreatedAtLeastOneTrigger = true;
}
}
return(context, hasCreatedAtLeastOneTrigger);
}
private async Task <Version> UpgdrateTo094Async(IScopeInfo serverScopeInfo, SyncContext context, DbConnection connection, DbTransaction transaction,
CancellationToken cancellationToken, IProgress <ProgressArgs> progress)
{
var newVersion = new Version(0, 9, 4);
// Sorting tables based on dependencies between them
var schemaTables = serverScopeInfo.Schema.Tables
.SortByDependencies(tab => tab.GetRelations()
.Select(r => r.GetParentTable()));
await this.InterceptAsync(new UpgradeProgressArgs(context, $"Upgrade to {newVersion}:", newVersion, connection, transaction), progress, cancellationToken).ConfigureAwait(false);
var provision = SyncProvision.StoredProcedures | SyncProvision.Triggers;
await this.DeprovisionAsync(serverScopeInfo.Name, provision, connection, transaction, cancellationToken, progress).ConfigureAwait(false);
await this.ProvisionAsync(serverScopeInfo.Name, provision, false, connection, transaction, cancellationToken, progress).ConfigureAwait(false);
var message = $"Upgrade to {newVersion} for scope {serverScopeInfo.Name}.";
await this.InterceptAsync(new UpgradeProgressArgs(context, message, newVersion, connection, transaction), progress, cancellationToken);
return(newVersion);
}
/// <summary>
/// Apply a single update in the current datasource. if forceWrite, override conflict situation and force the update
/// </summary>
private async Task <(SyncContext, bool)> InternalApplyConflictUpdateAsync(IScopeInfo scopeInfo, SyncContext context, DbSyncAdapter syncAdapter, SyncRow row, long?lastTimestamp, Guid?senderScopeId, bool forceWrite, DbConnection connection, DbTransaction transaction)
{
if (row.SchemaTable == null)
{
throw new ArgumentException("Schema table is not present in the row");
}
var(command, _) = await syncAdapter.GetCommandAsync(DbCommandType.UpdateRow, connection, transaction);
if (command == null)
{
return(context, false);
}
// Set the parameters value from row
syncAdapter.SetColumnParametersValues(command, row);
// Set the special parameters for update
syncAdapter.AddScopeParametersValues(command, senderScopeId, lastTimestamp, false, forceWrite);
await this.InterceptAsync(new DbCommandArgs(context, command, connection, transaction)).ConfigureAwait(false);
var rowUpdatedCount = await command.ExecuteNonQueryAsync().ConfigureAwait(false);
// Check if we have a return value instead
var syncRowCountParam = DbSyncAdapter.GetParameter(command, "sync_row_count");
if (syncRowCountParam != null)
{
rowUpdatedCount = (int)syncRowCountParam.Value;
}
command.Dispose();
return(context, rowUpdatedCount > 0);
}
/// <summary>
/// Internal update untracked rows routine
/// </summary>
internal async Task <(SyncContext context, int updated)> InternalUpdateUntrackedRowsAsync(IScopeInfo scopeInfo, SyncContext context, DbSyncAdapter syncAdapter, DbConnection connection, DbTransaction transaction, CancellationToken cancellationToken = default, IProgress <ProgressArgs> progress = null)
{
// Get table builder
var tableBuilder = this.GetTableBuilder(syncAdapter.TableDescription, scopeInfo);
// Check if tracking table exists
bool trackingTableExists;
(context, trackingTableExists) = await this.InternalExistsTrackingTableAsync(scopeInfo, context, tableBuilder, connection, transaction, CancellationToken.None, null).ConfigureAwait(false);
if (!trackingTableExists)
{
throw new MissingTrackingTableException(tableBuilder.TableDescription.GetFullName());
}
// Get correct Select incremental changes command
var(command, _) = await syncAdapter.GetCommandAsync(DbCommandType.UpdateUntrackedRows, connection, transaction);
if (command == null)
{
return(context, 0);
}
await this.InterceptAsync(new DbCommandArgs(context, command, connection, transaction), progress, cancellationToken).ConfigureAwait(false);
// Execute
var rowAffected = await command.ExecuteNonQueryAsync().ConfigureAwait(false);
// Check if we have a return value instead
var syncRowCountParam = DbSyncAdapter.GetParameter(command, "sync_row_count");
if (syncRowCountParam != null)
{
rowAffected = (int)syncRowCountParam.Value;
}
command.Dispose();
return(context, rowAffected);
}
InternalApplyThenGetChangesAsync(ClientScopeInfo clientScope, SyncContext context, BatchInfo clientBatchInfo, DbConnection connection = default, DbTransaction transaction = default, CancellationToken cancellationToken = default, IProgress <ProgressArgs> progress = null)
{
try
{
if (Provider == null)
{
throw new MissingProviderException(nameof(InternalApplyThenGetChangesAsync));
}
long remoteClientTimestamp = 0L;
DatabaseChangesSelected serverChangesSelected = null;
DatabaseChangesApplied clientChangesApplied = null;
BatchInfo serverBatchInfo = null;
IScopeInfo serverClientScopeInfo = null;
// Create two transactions
// First one to commit changes
// Second one to get changes now that everything is commited
await using (var runner = await this.GetConnectionAsync(context, SyncMode.Writing, SyncStage.ChangesApplying, connection, transaction, cancellationToken, progress).ConfigureAwait(false))
{
//Direction set to Upload
context.SyncWay = SyncWay.Upload;
// Getting server scope assumes we have already created the schema on server
// Scope name is the scope name coming from client
// Since server can have multiples scopes
(context, serverClientScopeInfo) = await this.InternalLoadServerScopeInfoAsync(context, runner.Connection, runner.Transaction, runner.CancellationToken, runner.Progress).ConfigureAwait(false);
// Should we ?
if (serverClientScopeInfo == null || serverClientScopeInfo.Schema == null)
{
throw new MissingRemoteOrchestratorSchemaException();
}
// Deserialiaze schema
var schema = serverClientScopeInfo.Schema;
// Create message containing everything we need to apply on server side
var applyChanges = new MessageApplyChanges(Guid.Empty, clientScope.Id, false, clientScope.LastServerSyncTimestamp, schema, this.Options.ConflictResolutionPolicy,
this.Options.DisableConstraintsOnApplyChanges, this.Options.CleanMetadatas, this.Options.CleanFolder, false, clientBatchInfo);
// Call provider to apply changes
(context, clientChangesApplied) = await this.InternalApplyChangesAsync(serverClientScopeInfo, context, applyChanges, runner.Connection, runner.Transaction, runner.CancellationToken, runner.Progress).ConfigureAwait(false);
await this.InterceptAsync(new TransactionCommitArgs(context, runner.Connection, runner.Transaction), runner.Progress, runner.CancellationToken).ConfigureAwait(false);
// commit first transaction
await runner.CommitAsync().ConfigureAwait(false);
}
await using (var runner = await this.GetConnectionAsync(context, SyncMode.Reading, SyncStage.ChangesSelecting, connection, transaction, cancellationToken, progress).ConfigureAwait(false))
{
context.ProgressPercentage = 0.55;
//Direction set to Download
context.SyncWay = SyncWay.Download;
// JUST Before get changes, get the timestamp, to be sure to
// get rows inserted / updated elsewhere since the sync is not over
(context, remoteClientTimestamp) = await this.InternalGetLocalTimestampAsync(context, runner.Connection, runner.Transaction, runner.CancellationToken, runner.Progress);
// Get if we need to get all rows from the datasource
var fromScratch = clientScope.IsNewScope || context.SyncType == SyncType.Reinitialize || context.SyncType == SyncType.ReinitializeWithUpload;
// When we get the chnages from server, we create the batches if it's requested by the client
// the batch decision comes from batchsize from client
(context, serverBatchInfo, serverChangesSelected) =
await this.InternalGetChangesAsync(serverClientScopeInfo, context, fromScratch, clientScope.LastServerSyncTimestamp, remoteClientTimestamp, clientScope.Id,
this.Provider.SupportsMultipleActiveResultSets,
this.Options.BatchDirectory, null, runner.Connection, runner.Transaction, runner.CancellationToken, runner.Progress).ConfigureAwait(false);
if (runner.CancellationToken.IsCancellationRequested)
{
runner.CancellationToken.ThrowIfCancellationRequested();
}
// generate the new scope item
this.CompleteTime = DateTime.UtcNow;
var scopeHistory = new ServerHistoryScopeInfo
{
Id = clientScope.Id,
Name = clientScope.Name,
LastSyncTimestamp = remoteClientTimestamp,
LastSync = this.CompleteTime,
LastSyncDuration = this.CompleteTime.Value.Subtract(context.StartTime).Ticks,
};
// Write scopes locally
await this.InternalSaveServerHistoryScopeAsync(scopeHistory, context, runner.Connection, runner.Transaction, runner.CancellationToken, runner.Progress).ConfigureAwait(false);
// Commit second transaction for getting changes
await this.InterceptAsync(new TransactionCommitArgs(context, runner.Connection, runner.Transaction), runner.Progress, runner.CancellationToken).ConfigureAwait(false);
await runner.CommitAsync().ConfigureAwait(false);
}
var serverSyncChanges = new ServerSyncChanges(remoteClientTimestamp, serverBatchInfo, serverChangesSelected);
return(context, serverSyncChanges, clientChangesApplied, this.Options.ConflictResolutionPolicy);
}
catch (Exception ex)
{
throw GetSyncError(context, ex);
}
}
/// <summary>
/// Is a variable declared in the given scope visible at the current level?
/// </summary>
/// <param name="scope">Scope to see if still visible</param>
/// <returns></returns>
/// <remarks>Check the booking scope - anywhere on the stack?</remarks>
public bool InScopeNow(IScopeInfo scope)
{
var s = scope as CurrentScopeInfo;
var bs = CurrentDeclarationScopePointer;
while (bs != null)
{
if (bs == s.BookingScope)
return true;
var p = bs.Parent;
while (p != null && !(p is IBookingStatementBlock))
p = p.Parent;
bs = p as IBookingStatementBlock;
}
return false;
}
