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