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