public bool NeedToCreateForeignKeyConstraints(SyncRelation relation) { string tableName = relation.GetTable().TableName; var relationName = NormalizeRelationName(relation.RelationName); bool alreadyOpened = this.connection.State == ConnectionState.Open; try { if (!alreadyOpened) { this.connection.Open(); } var relations = MySqlManagementUtils.RelationsForTable(this.connection, this.transaction, tableName); var foreignKeyExist = relations.Rows.Any(r => string.Equals(r["ForeignKey"].ToString(), relationName, SyncGlobalization.DataSourceStringComparison)); return(!foreignKeyExist); } catch (Exception ex) { Debug.WriteLine($"Error during checking foreign keys: {ex}"); throw; } finally { if (!alreadyOpened && this.connection.State != ConnectionState.Closed) { this.connection.Close(); } } }
public async Task CreateForeignKeyConstraintsAsync(SyncRelation constraint, DbConnection connection, DbTransaction transaction) { using (var command = BuildForeignKeyConstraintsCommand(constraint, connection, transaction)) { await command.ExecuteNonQueryAsync().ConfigureAwait(false); } }
public async Task <bool> NeedToCreateForeignKeyConstraintsAsync(SyncRelation relation, DbConnection connection, DbTransaction transaction) { string tableName = relation.GetTable().TableName; var relationName = NormalizeRelationName(relation.RelationName); var relations = await MySqlManagementUtils.GetRelationsForTableAsync((MySqlConnection)connection, (MySqlTransaction)transaction, tableName).ConfigureAwait(false); var foreignKeyExist = relations.Rows.Any(r => string.Equals(r["ForeignKey"].ToString(), relationName, SyncGlobalization.DataSourceStringComparison)); return(!foreignKeyExist); }
private MySqlCommand BuildForeignKeyConstraintsCommand(SyncRelation constraint, DbConnection connection, DbTransaction transaction) { var command = new MySqlCommand((MySqlConnection)connection, (MySqlTransaction)transaction); var tableName = ParserName.Parse(constraint.GetTable(), "`").Quoted().ToString(); var parentTableName = ParserName.Parse(constraint.GetParentTable(), "`").Quoted().ToString(); var relationName = NormalizeRelationName(constraint.RelationName); var keyColumns = constraint.Keys; var referencesColumns = constraint.ParentKeys; var stringBuilder = new StringBuilder(); stringBuilder.Append("SET FOREIGN_KEY_CHECKS=0;"); stringBuilder.Append("ALTER TABLE "); stringBuilder.AppendLine(tableName); stringBuilder.Append("ADD CONSTRAINT "); stringBuilder.AppendLine($"`{relationName}`"); stringBuilder.Append("FOREIGN KEY ("); string empty = string.Empty; foreach (var keyColumn in keyColumns) { var foreignKeyColumnName = ParserName.Parse(keyColumn.ColumnName, "`").Quoted().ToString(); stringBuilder.Append($"{empty} {foreignKeyColumnName}"); empty = ", "; } stringBuilder.AppendLine(" )"); stringBuilder.Append("REFERENCES "); stringBuilder.Append(parentTableName).Append(" ("); empty = string.Empty; foreach (var referencesColumn in referencesColumns) { var referencesColumnName = ParserName.Parse(referencesColumn.ColumnName, "`").Quoted().ToString(); stringBuilder.Append($"{empty} {referencesColumnName}"); empty = ", "; } stringBuilder.AppendLine(" );"); stringBuilder.AppendLine("SET FOREIGN_KEY_CHECKS=1;"); command.CommandText = stringBuilder.ToString(); return(command); }
public async Task <bool> NeedToCreateForeignKeyConstraintsAsync(SyncRelation relation, DbConnection connection, DbTransaction transaction) { // Don't want foreign key on same table since it could be a problem on first // sync. We are not sure that parent row will be inserted in first position //if (relation.GetParentTable() == relation.GetTable()) // return false; string tableName = relation.GetTable().TableName; string schemaName = relation.GetTable().SchemaName; string fullName = string.IsNullOrEmpty(schemaName) ? tableName : $"{schemaName}.{tableName}"; var relationName = NormalizeRelationName(relation.RelationName); var syncTable = await SqlManagementUtils.GetRelationsForTableAsync((SqlConnection)connection, (SqlTransaction)transaction, tableName, schemaName).ConfigureAwait(false); var foreignKeyExist = syncTable.Rows.Any(r => string.Equals(r["ForeignKey"].ToString(), relationName, SyncGlobalization.DataSourceStringComparison)); return(!foreignKeyExist); }
private SqlCommand BuildForeignKeyConstraintsCommand(SyncRelation constraint, DbConnection connection, DbTransaction transaction) { var sqlCommand = new SqlCommand(); sqlCommand.Connection = (SqlConnection)connection; sqlCommand.Transaction = (SqlTransaction)transaction; var tableName = ParserName.Parse(constraint.GetTable()).Quoted().Schema().ToString(); var parentTableName = ParserName.Parse(constraint.GetParentTable()).Quoted().Schema().ToString(); var relationName = NormalizeRelationName(constraint.RelationName); var stringBuilder = new StringBuilder(); stringBuilder.Append("ALTER TABLE "); stringBuilder.Append(tableName); stringBuilder.AppendLine(" WITH NOCHECK"); stringBuilder.Append("ADD CONSTRAINT "); stringBuilder.AppendLine($"[{relationName}]"); stringBuilder.Append("FOREIGN KEY ("); string empty = string.Empty; foreach (var column in constraint.Keys) { var childColumnName = ParserName.Parse(column.ColumnName).Quoted().ToString(); stringBuilder.Append($"{empty} {childColumnName}"); empty = ", "; } stringBuilder.AppendLine(" )"); stringBuilder.Append("REFERENCES "); stringBuilder.Append(parentTableName).Append(" ("); empty = string.Empty; foreach (var parentdColumn in constraint.ParentKeys) { var parentColumnName = ParserName.Parse(parentdColumn.ColumnName).Quoted().ToString(); stringBuilder.Append($"{empty} {parentColumnName}"); empty = ", "; } stringBuilder.Append(" ) "); sqlCommand.CommandText = stringBuilder.ToString(); return(sqlCommand); }
public async Task <bool> NeedToCreateForeignKeyConstraintsAsync(SyncRelation relation) { // Don't want foreign key on same table since it could be a problem on first // sync. We are not sure that parent row will be inserted in first position //if (relation.GetParentTable() == relation.GetTable()) // return false; string tableName = relation.GetTable().TableName; string schemaName = relation.GetTable().SchemaName; string fullName = string.IsNullOrEmpty(schemaName) ? tableName : $"{schemaName}.{tableName}"; var relationName = NormalizeRelationName(relation.RelationName); bool alreadyOpened = connection.State == ConnectionState.Open; try { if (!alreadyOpened) { await connection.OpenAsync().ConfigureAwait(false); } var syncTable = await NpgsqlManagementUtils.GetRelationsForTableAsync(connection, transaction, tableName, schemaName).ConfigureAwait(false); var foreignKeyExist = syncTable.Rows.Any(r => string.Equals(r["ForeignKey"].ToString(), relationName, SyncGlobalization.DataSourceStringComparison)); return(!foreignKeyExist); } catch (Exception ex) { Debug.WriteLine($"Error during checking foreign keys: {ex}"); throw; } finally { if (!alreadyOpened && connection.State != ConnectionState.Closed) { connection.Close(); } } }
public async Task CreateForeignKeyConstraintsAsync(SyncRelation constraint) { bool alreadyOpened = connection.State == ConnectionState.Open; try { if (!alreadyOpened) { await connection.OpenAsync().ConfigureAwait(false); } using (var command = BuildForeignKeyConstraintsCommand(constraint)) { command.Connection = connection; if (transaction != null) { command.Transaction = transaction; } await command.ExecuteNonQueryAsync().ConfigureAwait(false); } } catch (Exception ex) { Debug.WriteLine($"Error during CreateForeignKeyConstraints : {ex}"); throw; } finally { if (!alreadyOpened && connection.State != ConnectionState.Closed) { connection.Close(); } } }
public void CreateForeignKeyConstraints(SyncRelation constraint) { bool alreadyOpened = this.connection.State == ConnectionState.Open; try { if (!alreadyOpened) { this.connection.Open(); } using (var command = this.BuildForeignKeyConstraintsCommand(constraint)) { command.Connection = this.connection; if (this.transaction != null) { command.Transaction = this.transaction; } command.ExecuteNonQuery(); } } catch (Exception ex) { Debug.WriteLine($"Error during CreateForeignKeyConstraints : {ex}"); throw; } finally { if (!alreadyOpened && this.connection.State != ConnectionState.Closed) { this.connection.Close(); } } }
public Task <bool> NeedToCreateForeignKeyConstraintsAsync(SyncRelation constraint, DbConnection connection, DbTransaction transaction) => Task.FromResult(true);
public Task CreateForeignKeyConstraintsAsync(SyncRelation constraint, DbConnection connection, DbTransaction transaction) => Task.CompletedTask;
private static SyncSet CreateSchema() { var set = new SyncSet(); set.StoredProceduresPrefix = "spp"; set.StoredProceduresSuffix = "sps"; set.TrackingTablesPrefix = "ttp"; set.TrackingTablesSuffix = "tts"; set.TriggersPrefix = "tp"; set.TriggersSuffix = "ts"; var tbl = new SyncTable("ServiceTickets", null); tbl.OriginalProvider = "SqlServerProvider"; tbl.SyncDirection = Enumerations.SyncDirection.Bidirectional; set.Tables.Add(tbl); var c = SyncColumn.Create <int>("ServiceTicketID"); c.DbType = 8; c.AllowDBNull = true; c.IsAutoIncrement = true; c.AutoIncrementStep = 1; c.AutoIncrementSeed = 10; c.IsCompute = false; c.IsReadOnly = true; tbl.Columns.Add(c); tbl.Columns.Add(SyncColumn.Create <string>("Title")); tbl.Columns.Add(SyncColumn.Create <string>("Description")); tbl.Columns.Add(SyncColumn.Create <int>("StatusValue")); tbl.Columns.Add(SyncColumn.Create <int>("EscalationLevel")); tbl.Columns.Add(SyncColumn.Create <DateTime>("Opened")); tbl.Columns.Add(SyncColumn.Create <DateTime>("Closed")); tbl.Columns.Add(SyncColumn.Create <int>("CustomerID")); tbl.PrimaryKeys.Add("ServiceTicketID"); // Add Second tables var tbl2 = new SyncTable("Product", "SalesLT"); tbl2.SyncDirection = SyncDirection.UploadOnly; tbl2.Columns.Add(SyncColumn.Create <int>("Id")); tbl2.Columns.Add(SyncColumn.Create <string>("Title")); tbl2.PrimaryKeys.Add("Id"); set.Tables.Add(tbl2); // Add Filters var sf = new SyncFilter("Product", "SalesLT"); sf.Parameters.Add(new SyncFilterParameter { Name = "Title", DbType = DbType.String, MaxLength = 20, DefaultValue = "'Bikes'" }); sf.Parameters.Add(new SyncFilterParameter { Name = "LastName", TableName = "Customer", SchemaName = "SalesLT", AllowNull = true }); sf.Wheres.Add(new SyncFilterWhereSideItem { ColumnName = "Title", ParameterName = "Title", SchemaName = "SalesLT", TableName = "Product" }); sf.Joins.Add(new SyncFilterJoin { JoinEnum = Join.Right, TableName = "SalesLT.ProductCategory", LeftColumnName = "LCN", LeftTableName = "SalesLT.Product", RightColumnName = "RCN", RightTableName = "SalesLT.ProductCategory" }); sf.CustomWheres.Add("1 = 1"); set.Filters.Add(sf); // Add Relations var keys = new[] { new SyncColumnIdentifier("ProductId", "ServiceTickets") }; var parentKeys = new[] { new SyncColumnIdentifier("ProductId", "Product", "SalesLT") }; var rel = new SyncRelation("AdventureWorks_Product_ServiceTickets", keys, parentKeys); set.Relations.Add(rel); return(set); }
public void CreateForeignKeyConstraints(SyncRelation constraint) { return; }
public bool NeedToCreateForeignKeyConstraints(SyncRelation constraint) => false;
public Task CreateForeignKeyConstraintsAsync(SyncRelation constraint) => Task.CompletedTask;
public Task <bool> NeedToCreateForeignKeyConstraintsAsync(SyncRelation constraint) => Task.FromResult(false);