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