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()); }