private void ComputeForeignKeyConstraintsToDisable(ChangeInfo changeInfo)
{
var changes = changeInfo.Changes;
for (int i = 0; i < changes.Count; i++)
{
var change = changes[i];
if (change.CreationVersion < change.Version) // was inserted then later updated
{
for (int j = i + 1; j < changes.Count; j++)
{
var intermediateChange = changes[j];
if (intermediateChange.CreationVersion > change.Version) // created later than last update to change
{
break;
}
if (intermediateChange.Operation != 'I')
{
continue;
}
// let's look at intermediateChange if it collides with change
foreach (var fk in change.Table.ForeignKeyConstraints.Where(f => f.ReferencedTableName == intermediateChange.Table.Name))
{
var val = change.GetValue(fk.ColumnName);
var refVal = intermediateChange.GetValue(fk.ReferencedColumnName);
if (val != null && val.Equals(refVal))
{
// this foreign key constraint needs to be disabled
Log.Info($"Foreign key constraint {fk.ForeignKeyName} needs to be disabled for change #{i + 1} from version {change.CreationVersion} until version {intermediateChange.CreationVersion}");
change.ForeignKeyConstraintsToDisable[fk] = intermediateChange.CreationVersion;
}
}
}
}
}
}
private ChangeInfo RetrieveChanges(DatabaseInfo source, IGrouping <long, DatabaseInfo> destinations, IList <TableInfo> tables)
{
var destinationVersion = destinations.Key;
var changeInfo = new ChangeInfo();
var changes = new List <Change>();
using (var db = GetDatabase(source.ConnectionString, DatabaseType.SqlServer2008))
{
var snapshotIsolationEnabled = db.ExecuteScalar <int>("select snapshot_isolation_state from sys.databases where name = DB_NAME()") == 1;
if (snapshotIsolationEnabled)
{
Log.Info($"Snapshot isolation is enabled in database {source.Name}");
db.BeginTransaction(System.Data.IsolationLevel.Snapshot);
}
else
{
Log.Info($"Snapshot isolation is not enabled in database {source.Name}, ignoring all changes above current version");
}
changeInfo.Version = db.ExecuteScalar <long>("select CHANGE_TRACKING_CURRENT_VERSION()");
Log.Info($"Current version of database {source.Name} is {changeInfo.Version}");
foreach (var table in tables)
{
var tableName = table.Name;
var minVersion = db.ExecuteScalar <long?>("select CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(@0))", tableName);
Log.Info($"Minimum version of table {tableName} in database {source.Name} is {minVersion}");
if (minVersion > destinationVersion)
{
Log.Error($"Cannot replicate table {tableName} to {"destination".ToQuantity(destinations.Count(), ShowQuantityAs.None)} {string.Join(", ", destinations.Select(d => d.Name))} because minimum source version {minVersion} is greater than destination version {destinationVersion}");
Error = true;
return(null);
}
var sql = $@"select c.SYS_CHANGE_OPERATION, c.SYS_CHANGE_VERSION, c.SYS_CHANGE_CREATION_VERSION,
{string.Join(", ", table.KeyColumns.Select(c => "c." + c).Concat(table.OtherColumns.Select(c => "t." + c)))}
from CHANGETABLE (CHANGES {tableName}, @0) c
left outer join {tableName} t on ";
sql += string.Join(" and ", table.KeyColumns.Select(k => $"c.{k} = t.{k}"));
sql += " order by coalesce(c.SYS_CHANGE_CREATION_VERSION, c.SYS_CHANGE_VERSION)";
Log.Debug($"Retrieving changes for table {tableName}: {sql}");
db.OpenSharedConnection();
var cmd = db.CreateCommand(db.Connection, System.Data.CommandType.Text, sql, destinationVersion);
using (var reader = cmd.ExecuteReader())
{
var numChanges = 0;
while (reader.Read())
{
var col = 0;
var change = new Change {
Operation = ((string)reader[col])[0], Table = table
};
col++;
var version = reader.GetInt64(col);
change.Version = version;
col++;
var creationVersion = reader.IsDBNull(col) ? version : reader.GetInt64(col);
change.CreationVersion = creationVersion;
col++;
if (!snapshotIsolationEnabled && Math.Min(version, creationVersion) > changeInfo.Version)
{
Log.Warn($"Ignoring change version {Math.Min(version, creationVersion)}");
continue;
}
for (int i = 0; i < table.KeyColumns.Count; i++, col++)
{
change.Keys[table.KeyColumns[i]] = reader.GetValue(col);
}
for (int i = 0; i < table.OtherColumns.Count; i++, col++)
{
change.Others[table.OtherColumns[i]] = reader.GetValue(col);
}
changes.Add(change);
numChanges++;
}
Log.Info($"Table {tableName} has {"change".ToQuantity(numChanges)}");
}
}
if (snapshotIsolationEnabled)
{
db.CompleteTransaction();
}
}
changeInfo.Changes.AddRange(changes.OrderBy(c => c.CreationVersion).ThenBy(c => c.Table.Name));
ComputeForeignKeyConstraintsToDisable(changeInfo);
return(changeInfo);
}