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