public async Task Trigger_Drop_One_Cancel()
{
var dbName = HelperDatabase.GetRandomName("tcp_lo_");
await HelperDatabase.CreateDatabaseAsync(ProviderType.Sql, dbName, true);
var cs = HelperDatabase.GetConnectionString(ProviderType.Sql, dbName);
var sqlProvider = new SqlSyncProvider(cs);
// Create default table
var ctx = new AdventureWorksContext((dbName, ProviderType.Sql, sqlProvider), true, false);
await ctx.Database.EnsureCreatedAsync();
var options = new SyncOptions();
var setup = new SyncSetup(new string[] { "SalesLT.Product" });
var localOrchestrator = new LocalOrchestrator(sqlProvider, options, setup);
var isCreated = await localOrchestrator.CreateTriggerAsync(setup.Tables["Product", "SalesLT"], DbTriggerType.Insert);
Assert.True(isCreated);
// Ensuring we have a clean new instance
localOrchestrator = new LocalOrchestrator(sqlProvider, options, setup);
var onCreating = 0;
var onCreated = 0;
var onDropping = 0;
var onDropped = 0;
localOrchestrator.OnTriggerCreating(tca => onCreating++);
localOrchestrator.OnTriggerCreated(tca => onCreated++);
localOrchestrator.OnTriggerDropping(tca =>
{
tca.Cancel = true;
onDropping++;
});
localOrchestrator.OnTriggerDropped(tca => onDropped++);
var isDropped = await localOrchestrator.DropTriggerAsync(setup.Tables["Product", "SalesLT"], DbTriggerType.Insert);
Assert.False(isDropped);
Assert.Equal(0, onCreating);
Assert.Equal(0, onCreated);
Assert.Equal(1, onDropping);
Assert.Equal(0, onDropped);
// Check
using (var c = new SqlConnection(cs))
{
await c.OpenAsync().ConfigureAwait(false);
var check = await SqlManagementUtils.GetTriggerAsync(c, null, "Product_insert_trigger", "SalesLT").ConfigureAwait(false);
Assert.Single(check.Rows);
c.Close();
}
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
private SqlCommand BuildDeleteTableCommand(DbConnection connection, DbTransaction transaction)
{
var tbl = tableName.ToString();
var schema = SqlManagementUtils.GetUnquotedSqlSchemaName(tableName);
var stringBuilder = new StringBuilder();
stringBuilder.AppendLine("IF EXISTS (SELECT t.name FROM sys.tables t JOIN sys.schemas s ON s.schema_id = t.schema_id WHERE t.name = @tableName AND s.name = @schemaName) ");
stringBuilder.AppendLine("BEGIN");
stringBuilder.AppendLine($"ALTER TABLE {tableName.Schema().Quoted().ToString()} NOCHECK CONSTRAINT ALL; DROP TABLE {tableName.Schema().Quoted().ToString()};");
stringBuilder.AppendLine("END");
var command = new SqlCommand(stringBuilder.ToString(), (SqlConnection)connection, (SqlTransaction)transaction);
SqlParameter sqlParameter = new SqlParameter()
{
ParameterName = "@tableName",
Value = tbl
};
command.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter()
{
ParameterName = "@schemaName",
Value = schema
};
command.Parameters.Add(sqlParameter);
return(command);
}
public async Task InitializeAndSync()
{
var session = await agent.SynchronizeAsync();
Assert.Equal(10, session.TotalChangesDownloaded);
Assert.Equal(0, session.TotalChangesUploaded);
DmTable dmColumnsListServer;
DmTable dmColumnsListClient;
// check if all types are correct
using (var sqlConnection = new SqlConnection(fixture.ServerConnectionString))
{
sqlConnection.Open();
dmColumnsListServer = SqlManagementUtils.ColumnsForTable(sqlConnection, null, "AllColumns");
sqlConnection.Close();
}
using (var sqlConnection = new SqlConnection(fixture.Client1ConnectionString))
{
sqlConnection.Open();
dmColumnsListClient = SqlManagementUtils.ColumnsForTable(sqlConnection, null, "AllColumns");
sqlConnection.Close();
}
// check if all columns are replicated
Assert.Equal(dmColumnsListServer.Rows.Count, dmColumnsListClient.Rows.Count);
// check if all types are correct
foreach (var serverRow in dmColumnsListServer.Rows.OrderBy(r => (int)r["column_id"]))
{
var name = serverRow["name"].ToString();
var ordinal = (int)serverRow["column_id"];
var typeString = serverRow["type"].ToString();
var maxLength = (Int16)serverRow["max_length"];
var precision = (byte)serverRow["precision"];
var scale = (byte)serverRow["scale"];
var isNullable = (bool)serverRow["is_nullable"];
var isIdentity = (bool)serverRow["is_identity"];
var clientRow = dmColumnsListClient.Rows.FirstOrDefault(cr => (int)cr["column_id"] == ordinal);
Assert.NotNull(clientRow);
// exception on numeric, check if it could be decimal
var cTypeString = clientRow["type"].ToString();
if (typeString.ToLowerInvariant() == "numeric" && cTypeString.ToLowerInvariant() == "decimal")
{
cTypeString = "numeric";
}
Assert.Equal(name, clientRow["name"].ToString());
Assert.Equal(ordinal, (int)clientRow["column_id"]);
Assert.Equal(typeString, cTypeString);
Assert.Equal(maxLength, (Int16)clientRow["max_length"]);
Assert.Equal(precision, (byte)clientRow["precision"]);
Assert.Equal(scale, (byte)clientRow["scale"]);
Assert.Equal(isNullable, (bool)clientRow["is_nullable"]);
Assert.Equal(isIdentity, (bool)clientRow["is_identity"]);
}
}
public async Task StoredProcedure_Drop_One()
{
var dbName = HelperDatabase.GetRandomName("tcp_lo_");
await HelperDatabase.CreateDatabaseAsync(ProviderType.Sql, dbName, true);
var cs = HelperDatabase.GetConnectionString(ProviderType.Sql, dbName);
var sqlProvider = new SqlSyncProvider(cs);
// Create default table
var ctx = new AdventureWorksContext((dbName, ProviderType.Sql, sqlProvider), true, false);
await ctx.Database.EnsureCreatedAsync();
var options = new SyncOptions();
var setup = new SyncSetup(new string[] { "SalesLT.Product" });
var localOrchestrator = new LocalOrchestrator(sqlProvider, options, setup);
var isCreated = await localOrchestrator.CreateStoredProcedureAsync(setup.Tables["Product", "SalesLT"], DbStoredProcedureType.SelectChanges);
Assert.True(isCreated);
// Ensuring we have a clean new instance
localOrchestrator = new LocalOrchestrator(sqlProvider, options, setup);
var onCreating = 0;
var onCreated = 0;
var onDropping = 0;
var onDropped = 0;
localOrchestrator.OnStoredProcedureCreating(tca => onCreating++);
localOrchestrator.OnStoredProcedureCreated(tca => onCreated++);
localOrchestrator.OnStoredProcedureDropping(tca => onDropping++);
localOrchestrator.OnStoredProcedureDropped(tca => onDropped++);
var isDropped = await localOrchestrator.DropStoredProcedureAsync(setup.Tables["Product", "SalesLT"], DbStoredProcedureType.SelectChanges);
Assert.True(isDropped);
Assert.Equal(0, onCreating);
Assert.Equal(0, onCreated);
Assert.Equal(1, onDropping);
Assert.Equal(1, onDropped);
// Check
using (var c = new SqlConnection(cs))
{
await c.OpenAsync().ConfigureAwait(false);
var check = await SqlManagementUtils.ProcedureExistsAsync(c, null, "SalesLT.Product_changes").ConfigureAwait(false);
Assert.False(check);
c.Close();
}
// try to delete a non existing one
isDropped = await localOrchestrator.DropStoredProcedureAsync(setup.Tables["Product", "SalesLT"], DbStoredProcedureType.SelectChangesWithFilters);
Assert.False(isDropped);
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public Task <DbCommand> GetExistsColumnCommandAsync(string columnName, DbConnection connection, DbTransaction transaction)
{
var tbl = tableName.ToString();
var schema = SqlManagementUtils.GetUnquotedSqlSchemaName(tableName);
var command = connection.CreateCommand();
command.Connection = connection;
command.Transaction = transaction;
command.CommandText = $"IF EXISTS (" +
$"SELECT col.* " +
$"FROM sys.columns as col " +
$"JOIN sys.tables as t on t.object_id = col.object_id " +
$"JOIN sys.schemas s ON s.schema_id = t.schema_id WHERE t.name = @tableName AND s.name = @schemaName and col.name=@columnName) SELECT 1 ELSE SELECT 0;";
var parameter = command.CreateParameter();
parameter.ParameterName = "@tableName";
parameter.Value = tbl;
command.Parameters.Add(parameter);
parameter = command.CreateParameter();
parameter.ParameterName = "@schemaName";
parameter.Value = schema;
command.Parameters.Add(parameter);
parameter = command.CreateParameter();
parameter.ParameterName = "@columnName";
parameter.Value = columnName;
command.Parameters.Add(parameter);
return(Task.FromResult(command));
}
private async Task CreateTriggerAsync(DbConnection connection, DbTransaction transaction, DbCommandType triggerType, string commandText)
{
var commandTriggerName = this.sqlObjectNames.GetCommandName(triggerType).name;
var triggerName = ParserName.Parse(commandTriggerName).ToString();
var triggerSchemaName = SqlManagementUtils.GetUnquotedSqlSchemaName(ParserName.Parse(commandTriggerName));
var stringBuilder = new StringBuilder();
stringBuilder.AppendLine("IF EXISTS (SELECT tr.name FROM sys.triggers tr JOIN sys.tables t ON tr.parent_id = t.object_id JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE tr.name = @triggerName and s.name = @schemaName)");
stringBuilder.AppendLine($"DROP TRIGGER {commandTriggerName};");
using var commandDrop = new SqlCommand(stringBuilder.ToString(), (SqlConnection)connection, (SqlTransaction)transaction);
commandDrop.Parameters.AddWithValue("@triggerName", triggerName);
commandDrop.Parameters.AddWithValue("@schemaName", triggerSchemaName);
await commandDrop.ExecuteNonQueryAsync().ConfigureAwait(false);
string triggerFor = triggerType == DbCommandType.DeleteTrigger ? "DELETE"
: triggerType == DbCommandType.UpdateTrigger ? "UPDATE"
: "INSERT";
stringBuilder = new StringBuilder();
stringBuilder.AppendLine($"CREATE TRIGGER {commandTriggerName} ON {tableName.Schema().Quoted().ToString()} FOR {triggerFor} AS");
stringBuilder.AppendLine(commandText);
using var commandCreate = new SqlCommand(stringBuilder.ToString(), (SqlConnection)connection, (SqlTransaction)transaction);
await commandCreate.ExecuteNonQueryAsync().ConfigureAwait(false);
}
private string UpdateTriggerBodyText(DmTable TableDescription)
{
(var tableName, var trackingName) = SqlBuilder.GetParsers(TableDescription);
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.AppendLine();
stringBuilder.AppendLine("UPDATE [side] ");
stringBuilder.AppendLine("SET \t[update_scope_id] = NULL -- since the update if from local, it's a NULL");
stringBuilder.AppendLine("\t,[update_timestamp] = @@DBTS+1");
stringBuilder.AppendLine("\t,[last_change_datetime] = GetDate()");
// Filter columns
if (this.FilterColumns != null)
{
for (int i = 0; i < this.FilterColumns.Count; i++)
{
var filterColumn = this.FilterColumns[i];
if (TableDescription.PrimaryKey.Columns.Any(c => c.ColumnName == filterColumn.ColumnName))
{
continue;
}
ObjectNameParser columnName = new ObjectNameParser(filterColumn.ColumnName);
stringBuilder.AppendLine($"\t,{columnName.QuotedString} = [i].{columnName.QuotedString}");
}
stringBuilder.AppendLine();
}
stringBuilder.AppendLine($"FROM {trackingName.QuotedString} [side]");
stringBuilder.Append($"JOIN INSERTED AS [i] ON ");
stringBuilder.AppendLine(SqlManagementUtils.JoinTwoTablesOnClause(TableDescription.PrimaryKey.Columns, "[side]", "[i]"));
return(stringBuilder.ToString());
}
public virtual async Task <bool> NeedToCreateTriggerAsync(DbTriggerType type)
{
var updTriggerName = this.sqlObjectNames.GetCommandName(DbCommandType.UpdateTrigger).name;
var delTriggerName = this.sqlObjectNames.GetCommandName(DbCommandType.DeleteTrigger).name;
var insTriggerName = this.sqlObjectNames.GetCommandName(DbCommandType.InsertTrigger).name;
string triggerName = string.Empty;
switch (type)
{
case DbTriggerType.Insert:
{
triggerName = insTriggerName;
break;
}
case DbTriggerType.Update:
{
triggerName = updTriggerName;
break;
}
case DbTriggerType.Delete:
{
triggerName = delTriggerName;
break;
}
}
return(!await SqlManagementUtils.TriggerExistsAsync(connection, transaction, triggerName).ConfigureAwait(false));
}
public Task <DbCommand> GetDropTrackingTableCommandAsync(DbConnection connection, DbTransaction transaction)
{
var tbl = trackingName.ToString();
var schema = SqlManagementUtils.GetUnquotedSqlSchemaName(trackingName);
var stringBuilder = new StringBuilder();
stringBuilder.AppendLine($"ALTER TABLE {trackingName.Schema().Quoted().ToString()} NOCHECK CONSTRAINT ALL; DROP TABLE {trackingName.Schema().Quoted().ToString()};");
var command = new SqlCommand(stringBuilder.ToString(), (SqlConnection)connection, (SqlTransaction)transaction);
SqlParameter sqlParameter = new SqlParameter()
{
ParameterName = "@tableName",
Value = tbl
};
command.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter()
{
ParameterName = "@schemaName",
Value = schema
};
command.Parameters.Add(sqlParameter);
return(Task.FromResult((DbCommand)command));
}
public bool NeedToCreateTrigger(DbTriggerType type)
{
var updTriggerName = this.sqlObjectNames.GetCommandName(DbCommandType.UpdateTrigger);
var delTriggerName = this.sqlObjectNames.GetCommandName(DbCommandType.DeleteTrigger);
var insTriggerName = this.sqlObjectNames.GetCommandName(DbCommandType.InsertTrigger);
string triggerName = string.Empty;
switch (type)
{
case DbTriggerType.Insert:
{
triggerName = insTriggerName;
break;
}
case DbTriggerType.Update:
{
triggerName = updTriggerName;
break;
}
case DbTriggerType.Delete:
{
triggerName = delTriggerName;
break;
}
}
return(!SqlManagementUtils.TriggerExists(connection, transaction, triggerName));
}
public async Task DropTableAsync(DbConnection connection, DbTransaction transaction)
{
var tbl = trackingName.ToString();
var schema = SqlManagementUtils.GetUnquotedSqlSchemaName(trackingName);
var stringBuilder = new StringBuilder();
stringBuilder.AppendLine("IF EXISTS (SELECT t.name FROM sys.tables t JOIN sys.schemas s ON s.schema_id = t.schema_id WHERE t.name = @tableName AND s.name = @schemaName) ");
stringBuilder.AppendLine("BEGIN");
stringBuilder.AppendLine($"ALTER TABLE {trackingName.Schema().Quoted().ToString()} NOCHECK CONSTRAINT ALL; DROP TABLE {trackingName.Schema().Quoted().ToString()};");
stringBuilder.AppendLine("END");
using (var command = new SqlCommand(stringBuilder.ToString(), (SqlConnection)connection, (SqlTransaction)transaction))
{
SqlParameter sqlParameter = new SqlParameter()
{
ParameterName = "@tableName",
Value = tbl
};
command.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter()
{
ParameterName = "@schemaName",
Value = schema
};
command.Parameters.Add(sqlParameter);
await command.ExecuteNonQueryAsync().ConfigureAwait(false);
}
}
public bool NeedToCreateForeignKeyConstraints(DmRelation foreignKey)
{
string parentTable = foreignKey.ParentTable.TableName;
string parentSchema = foreignKey.ParentTable.Schema;
string parentFullName = String.IsNullOrEmpty(parentSchema) ? parentTable : $"{parentSchema}.{parentTable}";
bool alreadyOpened = connection.State == ConnectionState.Open;
try
{
if (!alreadyOpened)
{
connection.Open();
}
var dmTable = SqlManagementUtils.RelationsForTable(connection, transaction, parentFullName);
var foreignKeyExist = dmTable.Rows.Any(r =>
dmTable.IsEqual(r["ForeignKey"].ToString(), foreignKey.RelationName));
return(!foreignKeyExist);
}
catch (Exception ex)
{
Debug.WriteLine($"Error during checking foreign keys: {ex}");
throw;
}
finally
{
if (!alreadyOpened && connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
internal async Task <IEnumerable <SyncColumn> > GetColumnsAsync(DbConnection connection, DbTransaction transaction)
{
var schema = SqlManagementUtils.GetUnquotedSqlSchemaName(tableName);
var columns = new List <SyncColumn>();
// Get the columns definition
var syncTableColumnsList = await SqlManagementUtils.GetColumnsForTableAsync((SqlConnection)connection, (SqlTransaction)transaction, this.tableName.ToString(), schema).ConfigureAwait(false);
foreach (var c in syncTableColumnsList.Rows.OrderBy(r => (int)r["column_id"]))
{
var typeName = c["type"].ToString();
var name = c["name"].ToString();
var maxLengthLong = Convert.ToInt64(c["max_length"]);
//// Gets the datastore owner dbType
//var datastoreDbType = (SqlDbType)sqlDbMetadata.ValidateOwnerDbType(typeName, false, false, maxLengthLong);
//// once we have the datastore type, we can have the managed type
//var columnType = sqlDbMetadata.ValidateType(datastoreDbType);
var sColumn = new SyncColumn(name)
{
OriginalDbType = typeName,
Ordinal = (int)c["column_id"],
OriginalTypeName = c["type"].ToString(),
MaxLength = maxLengthLong > int.MaxValue ? int.MaxValue : (int)maxLengthLong,
Precision = (byte)c["precision"],
Scale = (byte)c["scale"],
AllowDBNull = (bool)c["is_nullable"],
IsAutoIncrement = (bool)c["is_identity"],
IsUnique = c["is_unique"] != DBNull.Value ? (bool)c["is_unique"] : false,
IsCompute = (bool)c["is_computed"],
DefaultValue = c["defaultvalue"] != DBNull.Value ? c["defaultvalue"].ToString() : null
};
if (sColumn.IsAutoIncrement)
{
sColumn.AutoIncrementSeed = Convert.ToInt32(c["seed"]);
sColumn.AutoIncrementStep = Convert.ToInt32(c["step"]);
}
switch (sColumn.OriginalTypeName.ToLowerInvariant())
{
case "nchar":
case "nvarchar":
sColumn.IsUnicode = true;
break;
default:
sColumn.IsUnicode = false;
break;
}
// No unsigned type in SQL Server
sColumn.IsUnsigned = false;
columns.Add(sColumn);
}
return(columns);
}
private DbCommand CreateBulkDeleteCommand(DbConnection connection, DbTransaction transaction)
{
var procName = this.SqlObjectNames.GetStoredProcedureCommandName(DbStoredProcedureType.BulkDeleteRows);
StringBuilder stringBuilder = new StringBuilder(string.Concat("CREATE PROCEDURE ", procName));
string str = "\n\t";
var sqlParameterChangeTable = new SqlParameter("@changeTable", SqlDbType.Structured)
{
TypeName = this.SqlObjectNames.GetStoredProcedureCommandName(DbStoredProcedureType.BulkTableType)
};
stringBuilder.Append(string.Concat(str, SqlBuilderProcedure.CreateParameterDeclaration(sqlParameterChangeTable)));
stringBuilder.Append("\nAS\nBEGIN\n");
string joins = SqlManagementUtils.JoinTwoTablesOnClause(this.TableDescription.PrimaryKeys, "[changes]", "[base]");
stringBuilder.AppendLine($"DELETE {this.TableName.Schema().Quoted()}");
stringBuilder.AppendLine($"FROM {this.TableName.Quoted()} [base]");
stringBuilder.AppendLine($"JOIN @changeTable as [changes] ON {joins}");
stringBuilder.AppendLine("END");
var sqlCommand = new SqlCommand(stringBuilder.ToString(), (SqlConnection)connection, (SqlTransaction)transaction);
return(sqlCommand);
}
public Task <DbCommand> GetExistsTrackingTableCommandAsync(DbConnection connection, DbTransaction transaction)
{
var commandText = $"IF EXISTS (Select top 1 tbl.name as TableName, " +
$"sch.name as SchemaName " +
$" from sys.change_tracking_tables tr " +
$" Inner join sys.tables as tbl on tbl.object_id = tr.object_id " +
$" Inner join sys.schemas as sch on tbl.schema_id = sch.schema_id " +
$" Where tbl.name = @tableName and sch.name = @schemaName) SELECT 1 ELSE SELECT 0;";
var tbl = tableName.Unquoted().ToString();
var schema = SqlManagementUtils.GetUnquotedSqlSchemaName(tableName);
var command = connection.CreateCommand();
command.Connection = connection;
command.Transaction = transaction;
command.CommandText = commandText;
var parameter = command.CreateParameter();
parameter.ParameterName = "@tableName";
parameter.Value = tbl;
command.Parameters.Add(parameter);
parameter = command.CreateParameter();
parameter.ParameterName = "@schemaName";
parameter.Value = schema;
command.Parameters.Add(parameter);
return(Task.FromResult(command));
}
public virtual Task <DbCommand> GetExistsTriggerCommandAsync(DbTriggerType triggerType, DbConnection connection, DbTransaction transaction)
{
var commandTriggerName = this.sqlObjectNames.GetTriggerCommandName(triggerType);
var triggerName = ParserName.Parse(commandTriggerName).ToString();
var commandText = $@"IF EXISTS (SELECT tr.name FROM sys.triggers tr
JOIN sys.tables t ON tr.parent_id = t.object_id
JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE tr.name = @triggerName and s.name = @schemaName) SELECT 1 ELSE SELECT 0";
var command = connection.CreateCommand();
command.Connection = connection;
command.Transaction = transaction;
command.CommandText = commandText;
var p1 = command.CreateParameter();
p1.ParameterName = "@triggerName";
p1.Value = triggerName;
command.Parameters.Add(p1);
var p2 = command.CreateParameter();
p2.ParameterName = "@schemaName";
p2.Value = SqlManagementUtils.GetUnquotedSqlSchemaName(ParserName.Parse(commandTriggerName));
command.Parameters.Add(p2);
return(Task.FromResult(command));
}
public async Task RemoteOrchestrator_Provision_ShouldCreate_StoredProcedures_WithSpecificScopeName()
{
var dbName = HelperDatabase.GetRandomName("tcp_lo_");
await HelperDatabase.CreateDatabaseAsync(ProviderType.Sql, dbName, true);
var cs = HelperDatabase.GetConnectionString(ProviderType.Sql, dbName);
var sqlProvider = new SqlSyncProvider(cs);
// Create default table
var ctx = new AdventureWorksContext((dbName, ProviderType.Sql, sqlProvider), true, false);
await ctx.Database.EnsureCreatedAsync();
var scopeName = "scope";
var options = new SyncOptions();
var setup = new SyncSetup(new string[] { "SalesLT.Product" })
{
StoredProceduresPrefix = "s",
StoredProceduresSuffix = "proc"
};
// trackign table name is composed with prefix and suffix from setup
var bulkDelete = $"SalesLT.{setup.StoredProceduresPrefix}Product{setup.StoredProceduresSuffix}_{scopeName}_bulkdelete";
var bulkUpdate = $"SalesLT.{setup.StoredProceduresPrefix}Product{setup.StoredProceduresSuffix}_{scopeName}_bulkupdate";
var changes = $"SalesLT.{setup.StoredProceduresPrefix}Product{setup.StoredProceduresSuffix}_{scopeName}_changes";
var delete = $"SalesLT.{setup.StoredProceduresPrefix}Product{setup.StoredProceduresSuffix}_{scopeName}_delete";
var deletemetadata = $"SalesLT.{setup.StoredProceduresPrefix}Product{setup.StoredProceduresSuffix}_{scopeName}_deletemetadata";
var initialize = $"SalesLT.{setup.StoredProceduresPrefix}Product{setup.StoredProceduresSuffix}_{scopeName}_initialize";
var reset = $"SalesLT.{setup.StoredProceduresPrefix}Product{setup.StoredProceduresSuffix}_{scopeName}_reset";
var selectrow = $"SalesLT.{setup.StoredProceduresPrefix}Product{setup.StoredProceduresSuffix}_{scopeName}_selectrow";
var update = $"SalesLT.{setup.StoredProceduresPrefix}Product{setup.StoredProceduresSuffix}_{scopeName}_update";
var remoteOrchestrator = new RemoteOrchestrator(sqlProvider, options);
// Needs the tracking table to be able to create stored procedures
var provision = SyncProvision.TrackingTable | SyncProvision.StoredProcedures;
var scopeInfo = await remoteOrchestrator.GetServerScopeInfoAsync(scopeName, setup);
await remoteOrchestrator.ProvisionAsync(scopeInfo, provision);
using (var connection = new SqlConnection(cs))
{
await connection.OpenAsync().ConfigureAwait(false);
Assert.True(await SqlManagementUtils.ProcedureExistsAsync(connection, null, bulkDelete));
Assert.True(await SqlManagementUtils.ProcedureExistsAsync(connection, null, bulkUpdate));
Assert.True(await SqlManagementUtils.ProcedureExistsAsync(connection, null, changes));
Assert.True(await SqlManagementUtils.ProcedureExistsAsync(connection, null, delete));
Assert.True(await SqlManagementUtils.ProcedureExistsAsync(connection, null, deletemetadata));
Assert.True(await SqlManagementUtils.ProcedureExistsAsync(connection, null, initialize));
Assert.True(await SqlManagementUtils.ProcedureExistsAsync(connection, null, reset));
Assert.True(await SqlManagementUtils.ProcedureExistsAsync(connection, null, selectrow));
Assert.True(await SqlManagementUtils.ProcedureExistsAsync(connection, null, update));
connection.Close();
}
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public async Task Table_Create_One()
{
var dbName = HelperDatabase.GetRandomName("tcp_lo_");
await HelperDatabase.CreateDatabaseAsync(ProviderType.Sql, dbName, true);
var cs = HelperDatabase.GetConnectionString(ProviderType.Sql, dbName);
var sqlProvider = new SqlSyncProvider(cs);
var options = new SyncOptions();
var setup = new SyncSetup(new string[] { "SalesLT.Product" });
var table = new SyncTable("Product", "SalesLT");
var colID = new SyncColumn("ID", typeof(Guid));
var colName = new SyncColumn("Name", typeof(string));
table.Columns.Add(colID);
table.Columns.Add(colName);
table.Columns.Add("Number", typeof(int));
table.PrimaryKeys.Add("ID");
var localOrchestrator = new LocalOrchestrator(sqlProvider, options, setup);
var onCreating = false;
var onCreated = false;
localOrchestrator.OnTableCreating(ttca =>
{
var addingID = Environment.NewLine + $"ALTER TABLE {ttca.TableName.Schema().Quoted()} ADD internal_id int identity(1,1)";
ttca.Command.CommandText += addingID;
onCreating = true;
});
localOrchestrator.OnTableCreated(ttca =>
{
onCreated = true;
});
var isCreated = await localOrchestrator.CreateTableAsync(table);
Assert.True(isCreated);
Assert.True(onCreating);
Assert.True(onCreated);
// Check we have a new column in tracking table
using (var c = new SqlConnection(cs))
{
await c.OpenAsync().ConfigureAwait(false);
var cols = await SqlManagementUtils.GetColumnsForTableAsync(c, null, "Product", "SalesLT").ConfigureAwait(false);
Assert.Equal(4, cols.Rows.Count);
Assert.NotNull(cols.Rows.FirstOrDefault(r => r["name"].ToString() == "internal_id"));
c.Close();
}
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public async Task TrackingTable_Create_One()
{
var dbName = HelperDatabase.GetRandomName("tcp_lo_");
await HelperDatabase.CreateDatabaseAsync(ProviderType.Sql, dbName, true);
var cs = HelperDatabase.GetConnectionString(ProviderType.Sql, dbName);
var sqlProvider = new SqlSyncProvider(cs);
var ctx = new AdventureWorksContext((dbName, ProviderType.Sql, sqlProvider), true, false);
await ctx.Database.EnsureCreatedAsync();
var options = new SyncOptions();
var setup = new SyncSetup(new string[] { "SalesLT.Product" })
{
TrackingTablesPrefix = "t_",
TrackingTablesSuffix = "_t"
};
var remoteOrchestrator = new RemoteOrchestrator(sqlProvider, options);
var scopeInfo = await remoteOrchestrator.GetServerScopeInfoAsync(setup);
var onCreating = false;
var onCreated = false;
remoteOrchestrator.OnTrackingTableCreating(ttca =>
{
var addingID = $" ALTER TABLE {ttca.TrackingTableName.Schema().Quoted()} ADD internal_id int identity(1,1)";
ttca.Command.CommandText += addingID;
onCreating = true;
});
remoteOrchestrator.OnTrackingTableCreated(ttca =>
{
onCreated = true;
});
await remoteOrchestrator.CreateTrackingTableAsync(scopeInfo, "Product", "SalesLT");
Assert.True(onCreating);
Assert.True(onCreated);
// Check we have a new column in tracking table
using (var c = new SqlConnection(cs))
{
await c.OpenAsync().ConfigureAwait(false);
var cols = await SqlManagementUtils.GetColumnsForTableAsync(c, null, "t_Product_t", "SalesLT").ConfigureAwait(false);
Assert.Equal(7, cols.Rows.Count);
Assert.NotNull(cols.Rows.FirstOrDefault(r => r["name"].ToString() == "internal_id"));
c.Close();
}
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
/// <summary>
/// Check if we need to create the table in the current database
/// </summary>
public bool NeedToCreateSchema()
{
if (string.IsNullOrEmpty(tableName.SchemaName) || tableName.SchemaName.ToLowerInvariant() == "dbo")
{
return(false);
}
return(!SqlManagementUtils.SchemaExists(connection, transaction, tableName.SchemaName));
}
public async Task Trigger_Create_All()
{
var dbName = HelperDatabase.GetRandomName("tcp_lo_");
await HelperDatabase.CreateDatabaseAsync(ProviderType.Sql, dbName, true);
var cs = HelperDatabase.GetConnectionString(ProviderType.Sql, dbName);
var sqlProvider = new SqlSyncProvider(cs);
// Create default table
var ctx = new AdventureWorksContext((dbName, ProviderType.Sql, sqlProvider), true, false);
await ctx.Database.EnsureCreatedAsync();
var options = new SyncOptions();
var setup = new SyncSetup(new string[] { "SalesLT.Product" });
var remoteOrchestrator = new RemoteOrchestrator(sqlProvider, options);
var scopeInfo = await remoteOrchestrator.GetServerScopeInfoAsync(setup);
var onCreating = 0;
var onCreated = 0;
var onDropping = 0;
var onDropped = 0;
remoteOrchestrator.OnTriggerCreating(tca => onCreating++);
remoteOrchestrator.OnTriggerCreated(tca => onCreated++);
remoteOrchestrator.OnTriggerDropping(tca => onDropping++);
remoteOrchestrator.OnTriggerDropped(tca => onDropped++);
var isCreated = await remoteOrchestrator.CreateTriggersAsync(scopeInfo, "Product", "SalesLT");
Assert.True(isCreated);
Assert.Equal(3, onCreating);
Assert.Equal(3, onCreated);
Assert.Equal(0, onDropping);
Assert.Equal(0, onDropped);
// Check
using (var c = new SqlConnection(cs))
{
await c.OpenAsync().ConfigureAwait(false);
var check = await SqlManagementUtils.GetTriggerAsync(c, null, "Product_insert_trigger", "SalesLT").ConfigureAwait(false);
Assert.Single(check.Rows);
check = await SqlManagementUtils.GetTriggerAsync(c, null, "Product_update_trigger", "SalesLT").ConfigureAwait(false);
Assert.Single(check.Rows);
check = await SqlManagementUtils.GetTriggerAsync(c, null, "Product_delete_trigger", "SalesLT").ConfigureAwait(false);
Assert.Single(check.Rows);
c.Close();
}
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
/// <summary>
/// Check if we need to create the table in the current database
/// </summary>
public async Task <bool> NeedToCreateSchemaAsync()
{
if (string.IsNullOrEmpty(tableName.SchemaName) || tableName.SchemaName.ToLowerInvariant() == "dbo")
{
return(false);
}
return(!await SqlManagementUtils.SchemaExistsAsync(connection, transaction, tableName.SchemaName).ConfigureAwait(false));
}
/// <summary>
/// Check if we need to create the table in the current database
/// </summary>
public bool NeedToCreateTable(DbBuilderOption builderOptions)
{
if (builderOptions.HasFlag(DbBuilderOption.CreateOrUseExistingSchema))
{
return(!SqlManagementUtils.TableExists(connection, transaction, tableName.QuotedString));
}
return(false);
}
public async Task Trigger_Create_One()
{
var dbName = HelperDatabase.GetRandomName("tcp_lo_");
await HelperDatabase.CreateDatabaseAsync(ProviderType.Sql, dbName, true);
var cs = HelperDatabase.GetConnectionString(ProviderType.Sql, dbName);
var sqlProvider = new SqlSyncProvider(cs);
// Create default table
var ctx = new AdventureWorksContext((dbName, ProviderType.Sql, sqlProvider), true, false);
await ctx.Database.EnsureCreatedAsync();
var options = new SyncOptions();
var setup = new SyncSetup(new string[] { "SalesLT.Product" });
// 1) create a console logger
//var loggerFactory = LoggerFactory.Create(builder => { builder.AddDebug().SetMinimumLevel(LogLevel.Debug); });
//var logger = loggerFactory.CreateLogger("Dotmim.Sync");
var logger = new SyncLogger().AddDebug().SetMinimumLevel(LogLevel.Debug);
options.Logger = logger;
var remoteOrchestrator = new RemoteOrchestrator(sqlProvider, options);
var scopeInfo = await remoteOrchestrator.GetServerScopeInfoAsync(setup);
var onCreating = 0;
var onCreated = 0;
var onDropping = 0;
var onDropped = 0;
remoteOrchestrator.OnTriggerCreating(tca => onCreating++);
remoteOrchestrator.OnTriggerCreated(tca => onCreated++);
remoteOrchestrator.OnTriggerDropping(tca => onDropping++);
remoteOrchestrator.OnTriggerDropped(tca => onDropped++);
var isCreated = await remoteOrchestrator.CreateTriggerAsync(scopeInfo, "Product", "SalesLT", DbTriggerType.Insert);
Assert.True(isCreated);
Assert.Equal(1, onCreating);
Assert.Equal(1, onCreated);
Assert.Equal(0, onDropping);
Assert.Equal(0, onDropped);
// Check
using (var c = new SqlConnection(cs))
{
await c.OpenAsync().ConfigureAwait(false);
var check = await SqlManagementUtils.GetTriggerAsync(c, null, "Product_insert_trigger", "SalesLT").ConfigureAwait(false);
Assert.Single(check.Rows);
c.Close();
}
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public async Task BaseOrchestrator_Provision_ShouldCreate_TrackingTable()
{
var dbName = HelperDatabase.GetRandomName("tcp_lo_");
await HelperDatabase.CreateDatabaseAsync(ProviderType.Sql, dbName, true);
var cs = HelperDatabase.GetConnectionString(ProviderType.Sql, dbName);
var sqlProvider = new SqlSyncProvider(cs);
var scopeName = "scope";
var options = new SyncOptions();
var setup = new SyncSetup {
TrackingTablesSuffix = "sync", TrackingTablesPrefix = "trck"
};
var schema = new SyncSet();
var table = new SyncTable("Product", "SalesLT");
var colID = new SyncColumn("ID", typeof(Guid));
var colName = new SyncColumn("Name", typeof(string));
table.Columns.Add(colID);
table.Columns.Add(colName);
table.Columns.Add("Number", typeof(int));
table.PrimaryKeys.Add("ID");
//schema.TrackingTablesSuffix = "sync";
//schema.TrackingTablesPrefix = "trck";
schema.Tables.Add(table);
// trackign table name is composed with prefix and suffix from setup
var trackingTableName = $"{setup.TrackingTablesPrefix}{table.TableName}{setup.TrackingTablesSuffix}";
var localOrchestrator = new LocalOrchestrator(sqlProvider, options, setup, scopeName);
var provision = SyncProvision.TrackingTable;
await localOrchestrator.ProvisionAsync(schema, provision);
using (var c = new SqlConnection(cs))
{
await c.OpenAsync().ConfigureAwait(false);
var tbl = await SqlManagementUtils.GetTableAsync(c, null, trackingTableName, "SalesLT");
var tblName = tbl.Rows[0]["TableName"].ToString();
var schName = tbl.Rows[0]["SchemaName"].ToString();
Assert.Equal(trackingTableName, tblName);
Assert.Equal(table.SchemaName, schName);
c.Close();
}
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public virtual Task CreateInsertTriggerAsync(DbConnection connection, DbTransaction transaction)
{
var stringBuilder = new StringBuilder();
stringBuilder.AppendLine();
stringBuilder.AppendLine("SET NOCOUNT ON;");
stringBuilder.AppendLine();
stringBuilder.AppendLine("-- If row was deleted before, it already exists, so just make an update");
stringBuilder.AppendLine("UPDATE [side] ");
stringBuilder.AppendLine("SET [sync_row_is_tombstone] = 0");
stringBuilder.AppendLine("\t,[update_scope_id] = NULL -- scope id is always NULL when update is made locally");
stringBuilder.AppendLine("\t,[last_change_datetime] = GetUtcDate()");
stringBuilder.AppendLine($"FROM {trackingName.Schema().Quoted().ToString()} [side]");
stringBuilder.Append($"JOIN INSERTED AS [i] ON ");
stringBuilder.AppendLine(SqlManagementUtils.JoinTwoTablesOnClause(this.tableDescription.PrimaryKeys, "[side]", "[i]"));
stringBuilder.AppendLine();
stringBuilder.AppendLine($"INSERT INTO {trackingName.Schema().Quoted().ToString()} (");
var stringBuilderArguments = new StringBuilder();
var stringBuilderArguments2 = new StringBuilder();
var stringPkAreNull = new StringBuilder();
string argComma = " ";
string argAnd = string.Empty;
var primaryKeys = this.tableDescription.GetPrimaryKeysColumns();
foreach (var mutableColumn in primaryKeys.Where(c => !c.IsReadOnly))
{
var columnName = ParserName.Parse(mutableColumn).Quoted().ToString();
stringBuilderArguments.AppendLine($"\t{argComma}[i].{columnName}");
stringBuilderArguments2.AppendLine($"\t{argComma}{columnName}");
stringPkAreNull.Append($"{argAnd}[side].{columnName} IS NULL");
argComma = ",";
argAnd = " AND ";
}
stringBuilder.Append(stringBuilderArguments2.ToString());
stringBuilder.AppendLine("\t,[update_scope_id]");
stringBuilder.AppendLine("\t,[sync_row_is_tombstone]");
stringBuilder.AppendLine("\t,[last_change_datetime]");
stringBuilder.AppendLine(") ");
stringBuilder.AppendLine("SELECT");
stringBuilder.Append(stringBuilderArguments.ToString());
stringBuilder.AppendLine("\t,NULL");
stringBuilder.AppendLine("\t,0");
stringBuilder.AppendLine("\t,GetUtcDate()");
stringBuilder.AppendLine("FROM INSERTED [i]");
stringBuilder.Append($"LEFT JOIN {trackingName.Schema().Quoted().ToString()} [side] ON ");
stringBuilder.AppendLine(SqlManagementUtils.JoinTwoTablesOnClause(this.tableDescription.PrimaryKeys, "[i]", "[side]"));
stringBuilder.Append("WHERE ");
stringBuilder.AppendLine(stringPkAreNull.ToString());
return(CreateTriggerAsync(connection, transaction, DbCommandType.InsertTrigger, stringBuilder.ToString()));
}
public async Task TrackingTable_Drop_One_Cancel()
{
var dbName = HelperDatabase.GetRandomName("tcp_lo_");
await HelperDatabase.CreateDatabaseAsync(ProviderType.Sql, dbName, true);
var cs = HelperDatabase.GetConnectionString(ProviderType.Sql, dbName);
var sqlProvider = new SqlSyncProvider(cs);
var ctx = new AdventureWorksContext((dbName, ProviderType.Sql, sqlProvider), true, false);
await ctx.Database.EnsureCreatedAsync();
var options = new SyncOptions();
var setup = new SyncSetup(new string[] { "SalesLT.Product" });
setup.TrackingTablesPrefix = "t_";
setup.TrackingTablesSuffix = "_t";
var remoteOrchestrator = new RemoteOrchestrator(sqlProvider, options);
var scopeInfo = await remoteOrchestrator.GetServerScopeInfoAsync(setup);
var onDropping = false;
var onDropped = false;
remoteOrchestrator.OnTrackingTableDropping(ttca =>
{
ttca.Cancel = true;
onDropping = true;
});
remoteOrchestrator.OnTrackingTableDropped(ttca =>
{
onDropped = true;
});
await remoteOrchestrator.CreateTrackingTableAsync(scopeInfo, "Product", "SalesLT");
await remoteOrchestrator.DropTrackingTableAsync(scopeInfo, "Product", "SalesLT");
Assert.True(onDropping);
Assert.False(onDropped);
// Check we have a new column in tracking table
using (var c = new SqlConnection(cs))
{
await c.OpenAsync().ConfigureAwait(false);
var table = await SqlManagementUtils.GetTableAsync(c, null, "t_Product_t", "SalesLT").ConfigureAwait(false);
Assert.NotEmpty(table.Rows);
c.Close();
}
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public async Task BaseOrchestrator_Provision_ShouldCreate_Triggers()
{
var dbName = HelperDatabase.GetRandomName("tcp_lo_");
await HelperDatabase.CreateDatabaseAsync(ProviderType.Sql, dbName, true);
var cs = HelperDatabase.GetConnectionString(ProviderType.Sql, dbName);
var sqlProvider = new SqlSyncProvider(cs);
// Create default table
var ctx = new AdventureWorksContext((dbName, ProviderType.Sql, sqlProvider), true, false);
await ctx.Database.EnsureCreatedAsync();
var scopeName = "scope";
var options = new SyncOptions();
var setup = new SyncSetup(new string[] { "SalesLT.Product" });
setup.TrackingTablesSuffix = "sync";
setup.TrackingTablesPrefix = "trck";
setup.TriggersPrefix = "trg_";
setup.TriggersSuffix = "_trg";
// trackign table name is composed with prefix and suffix from setup
var triggerDelete = $"{setup.TriggersPrefix}Product{setup.TriggersSuffix}_delete_trigger";
var triggerInsert = $"{setup.TriggersPrefix}Product{setup.TriggersSuffix}_insert_trigger";
var triggerUpdate = $"{setup.TriggersPrefix}Product{setup.TriggersSuffix}_update_trigger";
var localOrchestrator = new LocalOrchestrator(sqlProvider, options, setup, scopeName);
// Needs the tracking table to be able to create triggers
var provision = SyncProvision.TrackingTable | SyncProvision.Triggers;
await localOrchestrator.ProvisionAsync(provision);
using (var c = new SqlConnection(cs))
{
await c.OpenAsync().ConfigureAwait(false);
var trigDel = await SqlManagementUtils.GetTriggerAsync(c, null, triggerDelete, "SalesLT");
Assert.Equal(triggerDelete, trigDel.Rows[0]["Name"].ToString());
var trigIns = await SqlManagementUtils.GetTriggerAsync(c, null, triggerInsert, "SalesLT");
Assert.Equal(triggerInsert, trigIns.Rows[0]["Name"].ToString());
var trigUdate = await SqlManagementUtils.GetTriggerAsync(c, null, triggerUpdate, "SalesLT");
Assert.Equal(triggerUpdate, trigUdate.Rows[0]["Name"].ToString());
c.Close();
}
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public override async Task EnsureDatabaseAsync(DbConnection connection, DbTransaction transaction = null)
{
// Chek if db exists
var exists = await SqlManagementUtils.DatabaseExistsAsync(connection as SqlConnection, transaction as SqlTransaction).ConfigureAwait(false);
if (!exists)
{
throw new MissingDatabaseException(connection.Database);
}
}
private string CreateDeleteTriggerAsync()
{
var stringBuilder = new StringBuilder();
stringBuilder.AppendLine();
stringBuilder.AppendLine("SET NOCOUNT ON;");
stringBuilder.AppendLine();
stringBuilder.AppendLine("UPDATE [side] ");
stringBuilder.AppendLine("SET [sync_row_is_tombstone] = 1");
stringBuilder.AppendLine("\t,[update_scope_id] = NULL -- scope id is always NULL when update is made locally");
stringBuilder.AppendLine("\t,[last_change_datetime] = GetUtcDate()");
stringBuilder.AppendLine($"FROM {trackingName.Schema().Quoted().ToString()} [side]");
stringBuilder.Append($"JOIN DELETED AS [d] ON ");
stringBuilder.AppendLine(SqlManagementUtils.JoinTwoTablesOnClause(this.tableDescription.PrimaryKeys, "[side]", "[d]"));
stringBuilder.AppendLine();
stringBuilder.AppendLine($"INSERT INTO {trackingName.Schema().Quoted().ToString()} (");
var stringBuilderArguments = new StringBuilder();
var stringBuilderArguments2 = new StringBuilder();
var stringPkAreNull = new StringBuilder();
string argComma = " ";
string argAnd = string.Empty;
var primaryKeys = this.tableDescription.GetPrimaryKeysColumns();
foreach (var mutableColumn in primaryKeys.Where(c => !c.IsReadOnly))
{
var columnName = ParserName.Parse(mutableColumn).Quoted().ToString();
stringBuilderArguments.AppendLine($"\t{argComma}[d].{columnName}");
stringBuilderArguments2.AppendLine($"\t{argComma}{columnName}");
stringPkAreNull.Append($"{argAnd}[side].{columnName} IS NULL");
argComma = ",";
argAnd = " AND ";
}
stringBuilder.Append(stringBuilderArguments2.ToString());
stringBuilder.AppendLine("\t,[update_scope_id]");
stringBuilder.AppendLine("\t,[sync_row_is_tombstone]");
stringBuilder.AppendLine("\t,[last_change_datetime]");
stringBuilder.AppendLine(") ");
stringBuilder.AppendLine("SELECT");
stringBuilder.Append(stringBuilderArguments.ToString());
stringBuilder.AppendLine("\t,NULL");
stringBuilder.AppendLine("\t,1");
stringBuilder.AppendLine("\t,GetUtcDate()");
stringBuilder.AppendLine("FROM DELETED [d]");
stringBuilder.Append($"LEFT JOIN {trackingName.Schema().Quoted().ToString()} [side] ON ");
stringBuilder.AppendLine(SqlManagementUtils.JoinTwoTablesOnClause(this.tableDescription.PrimaryKeys, "[d]", "[side]"));
stringBuilder.Append("WHERE ");
stringBuilder.AppendLine(stringPkAreNull.ToString());
return(stringBuilder.ToString());
}
