public IEnumerable <dynamic> GetTargetRecords(ITableDetails table, IEnumerable <IKeys> keys)
{
using (var connection = _targetDatabase.GetOpenConnection())
{
return(GetChangedRecords(connection, null, table, keys).Result.ToList());
}
}
public static Keys GetPrimaryKeys(IDictionary <string, object> record, ITableDetails table)
{
var keys = new List <long>();
foreach (var key in table.PrimaryKeys)
{
var sourceId = record[key];
if (sourceId == null)
{
throw new FatalException($"Unknown column (may be case sensitive) or null value for {key} on {table.Name}");
}
if (sourceId is long)
{
keys.Add((long)sourceId);
}
else if (sourceId is int)
{
keys.Add((long)(int)sourceId);
}
else if (sourceId is short)
{
keys.Add((long)(short)sourceId);
}
else
{
throw new FatalException($"Unknown type {sourceId.GetType()}");
}
}
return(new Keys(keys));
}
public void BulkUpdate(ITableDetails table, IEnumerable <IDictionary <string, object> > records)
{
if (!records.Any())
{
return;
}
var tempTable = "_UpdateTemp_" + table.Name;
var columnTypes = GetColumnTypes(records);
var nonNullColumnTypes = columnTypes.Where(c => c.Value != null);
using (var connection = (SqlConnection)_targetDatabase.GetOpenConnection())
{
CreateAndInsertIntoTemp(table, columnTypes, tempTable, connection, records);
try
{
UpdateFromTempInOneGo(table, columnTypes, tempTable, connection);
}
catch (Exception ex)
{
_log.Warn($"Error updating record. Therefore updating what can be before continuing. Error was {ex.Message}");
var errors = UpdateFromTempOneAtATime(table, columnTypes, tempTable, connection);
if (errors.Any())
{
throw new Exception($"Errors updating {table.Name}:\n{GetErrorText(errors)}");
}
}
}
}
private string GetColumnAssignments(ITableDetails table, IDictionary <String, Type> columnTypes)
{
return(string.Join(", ",
columnTypes
.Where(c => !table.PrimaryKeys.Contains(c.Key))
.Select(c => (c.Value == null) ? $"[{c.Key}] = NULL" : $"[{c.Key}] = temp.[{c.Key}]")
));
}
public async Task <IEnumerable <dynamic> > GetSourceRecordsAsync(ITableDetails table, long startFirstId, long endFirstId)
{
using (var conn = _sourceDatabase.GetOpenConnection())
{
return(await conn.QueryAsync <dynamic>($"SELECT * FROM {table.Name} WHERE {table.PrimaryKeys.First()} BETWEEN @StartId AND @EndId ORDER BY {table.PrimaryKeys.First()}", new { StartId = startFirstId, EndId = endFirstId }));
}
// return Task.FromResult(_sourceDatabase.QueryProgressive<dynamic>($"SELECT * FROM {table.Name} WHERE {table.PrimaryKey} BETWEEN @StartId AND @EndId ORDER BY {table.PrimaryKey}", new { StartId = startId, EndId = endId }));
}
private string GetSetIdentityInsertSql(ITableDetails table)
{
return($@"
BEGIN TRY
SET IDENTITY_INSERT {table.Name} ON;
END TRY
BEGIN CATCH
END CATCH");
}
private void DeleteFromTempInOneGo(ITableDetails table, IDictionary <string, Type> primaryKeysWithTypes, string tempTable, IDbConnection connection)
{
connection.Execute($@"
DELETE target
FROM {table.Name} target
JOIN {tempTable} temp
ON {string.Join(" AND ", primaryKeysWithTypes.Keys.Select(k => $"temp.{k} = target.{k}"))}
");
}
private void UpdateFromTempInOneGo(ITableDetails table, IDictionary <string, Type> columnTypes, string tempTable, IDbConnection connection)
{
connection.Execute($@"
UPDATE target
SET {GetColumnAssignments(table, columnTypes)}
FROM {table.Name} target
JOIN {tempTable} temp
ON {string.Join(" AND ", table.PrimaryKeys.Select(k => $"temp.{k} = target.{k}"))};
");
}
public MutateTarget(ILogService log, IGenericSyncRespository syncRepository, int maxBatchSize, ITableDetails tableDetails) : base(log, tableDetails)
{
_syncRepository = syncRepository;
_maxBatchSize = maxBatchSize;
_tableDetails = tableDetails;
_toInsert = new List <dynamic>();
_toUpdate = new List <dynamic>();
_toDelete = new List <Keys>();
}
public BaseMutateTarget(ILogService log, ITableDetails tableDetails)
{
_log = log;
_tableDetails = tableDetails;
NumberOfInserts = 0;
NumberOfUpdates = 0;
NumberOfDeletes = 0;
NumberUnchanged = 0;
}
private void InsertFromTemp(ITableDetails table, IDictionary <String, Type> columnTypes, string tempTable, IDbConnection connection)
{
if (table.IdentityInsert)
{
connection.Execute($@"{GetSetIdentityInsertSql(table)};{GetInsertSql(table, columnTypes, tempTable)};");
}
else
{
connection.Execute($@"{GetInsertSql(table, columnTypes, tempTable)};");
}
}
private string GetInsertSql(ITableDetails table, IDictionary <String, Type> columnTypes, string tempTable, string indent = "")
{
return($@"
INSERT INTO {table.Name}
({string.Join(", ", columnTypes.Select(c => c.Key))})
SELECT {string.Join(", ", columnTypes.Select(c => (c.Value == null) ? "NULL" : c.Key))}
FROM {tempTable}"
.Replace(@"
", $@"
{indent}"));
}
public IEnumerable <IChangeTableRow> GetChangesForTable(ITableDetails table)
{
long?minValidVersion = _sourceConnection.Query <long?>($"SELECT CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('{table.Name}'))", transaction: _sourceTransaction, buffered: false).Single();
if (minValidVersion == null)
{
throw new Exception($"Change tracking not enabled, unknown table or insufficient permission on {table.Name}");
}
if (minValidVersion > _lastSyncVersion)
{
throw new FullScanRequiredException();
}
// Note: The ordering of the results from CHANGETABLE is undefined and therefore it is difficult to do them in batches. Accordingly ideally the results would not be buffered.
// However, without enabling SQL Server MARS, this will not allow other queries to proceed at the same time.
var result = _sourceConnection.Query <dynamic>($"SELECT * FROM CHANGETABLE(CHANGES {table.Name}, @lastSyncVersion) AS Dummy", new { lastSyncVersion = _lastSyncVersion }, transaction: _sourceTransaction);
return(result.Select(row => new ChangeTableRow(row, table)));
}
private IEnumerable <dynamic> InsertFromTempOneAtATime(ITableDetails table, IDictionary <String, Type> columnTypes, string tempTable, IDbConnection connection)
{
string primaryKeyList = $"{string.Join(", ", table.PrimaryKeys)}";
string errorKeyList = $"{string.Join(", ", table.ErrorKeys)}";
var identityInsert = GetSetIdentityInsertSql(table) + ";";
if (!table.IdentityInsert)
{
primaryKeyList = errorKeyList;
identityInsert = "";
}
var sql = $@"
DECLARE @totalRecords INT = (SELECT COUNT(*) FROM {tempTable});
DECLARE @thisRecord INT = 0;
CREATE TABLE #Errors ({string.Join(", ", table.ErrorKeys.Select(k => new KeyValuePair<string, Type>(k, columnTypes[k])).Select(col => $"{col.Key} {SqlTypeFor(col.Value)}"))}, Error NVARCHAR(MAX))
{identityInsert}
WHILE @thisRecord < @totalRecords
BEGIN
BEGIN TRY
{GetInsertSql(table, columnTypes, tempTable, " ")}
ORDER BY {primaryKeyList}
OFFSET (@thisRecord) ROWS FETCH NEXT (1) ROWS ONLY;
END TRY
BEGIN CATCH
INSERT INTO #Errors
SELECT {errorKeyList}, ERROR_MESSAGE()
FROM {tempTable}
ORDER BY {errorKeyList}
OFFSET (@thisRecord) ROWS FETCH NEXT (1) ROWS ONLY;
END CATCH
SET @thisRecord = @thisRecord + 1;
END
SELECT * FROM #Errors
";
return(connection.Query <dynamic>(sql));
}
private IEnumerable <dynamic> UpdateFromTempOneAtATime(ITableDetails table, IDictionary <string, Type> columnTypes, string tempTable, IDbConnection connection)
{
string primaryKeyList = $"{string.Join(", ", table.PrimaryKeys)}";
var sql = $@"
DECLARE @totalRecords INT = (SELECT COUNT(*) FROM {tempTable});
DECLARE @thisRecord INT = 0;
CREATE TABLE #Errors ({string.Join(", ", table.PrimaryKeys.Select(k => new KeyValuePair<string, Type>(k, columnTypes[k])).Select(col => $"{col.Key} {SqlTypeFor(col.Value)}"))}, Error NVARCHAR(MAX))
{GetSetIdentityInsertSql(table)};
WHILE @thisRecord < @totalRecords
BEGIN
BEGIN TRY
UPDATE target
SET {GetColumnAssignments(table, columnTypes)}
FROM {table.Name} target
JOIN (
SELECT * FROM {tempTable}
ORDER BY {primaryKeyList}
OFFSET (@thisRecord) ROWS FETCH NEXT (1) ROWS ONLY
) temp
ON {string.Join(" AND ", table.PrimaryKeys.Select(k => $"temp.{k} = target.{k}"))};
END TRY
BEGIN CATCH
INSERT INTO #Errors
SELECT {primaryKeyList}, ERROR_MESSAGE()
FROM {tempTable}
ORDER BY {primaryKeyList}
OFFSET (@thisRecord) ROWS FETCH NEXT (1) ROWS ONLY;
END CATCH
SET @thisRecord = @thisRecord + 1;
END
SELECT * FROM #Errors
";
return(connection.Query <dynamic>(sql));
}
private void CreateAndInsertIntoTemp(ITableDetails table, IDictionary <string, Type> columnTypes, string tempTable, SqlConnection connection, IEnumerable <IDictionary <string, object> > records)
{
var nonNullColumnTypes = columnTypes.Where(c => c.Value != null);
var columnsWithSqlTypes = nonNullColumnTypes.Select(col => $"{col.Key} {SqlTypeFor(col.Value)}");
var sql = $"CREATE TABLE {tempTable} ({string.Join(", ", columnsWithSqlTypes)})";
if (!tempTable.StartsWith("#"))
{
sql = $@"
BEGIN TRY
DROP TABLE {tempTable}
END TRY
BEGIN CATCH
END CATCH
{sql}
";
}
connection.Execute(sql);
BulkInsert(connection, tempTable, records, nonNullColumnTypes);
}
public ChangeTableRow(dynamic data, ITableDetails tableDetails)
{
PrimaryKeys = Keys.GetPrimaryKeys(data, tableDetails);
ChangeVersion = data.SYS_CHANGE_VERSION;
CreationVersion = data.SYS_CHANGE_CREATION_VERSION;
switch ((string)data.SYS_CHANGE_OPERATION)
{
case "I":
Operation = Operation.Insert;
break;
case "U":
Operation = Operation.Update;
break;
case "D":
Operation = Operation.Delete;
break;
default:
throw new Exception($"Unknown change {data.SYS_CHANGE_OPERATION} for keys {PrimaryKeys}");
}
}
public void BulkDelete(ITableDetails table, IEnumerable <Keys> keys)
{
var tempTable = "_DeleteTemp_" + table.Name;
var primaryKeysWithTypes = table.PrimaryKeys.ToDictionary(k => k, k => typeof(long));
var allRecords = keys.Select(k => k.ToDictionary(r => table.PrimaryKeys.First(), r => (object)r)).ToList(); // TODO: Only works if one primary key
using (var connection = (SqlConnection)_targetDatabase.GetOpenConnection())
{
const int batchSize = 1000;
int thisBatchSize = batchSize;
for (int firstRecord = 0; allRecords.Count != 0 && thisBatchSize == batchSize; firstRecord += batchSize)
{
var records = allRecords.Skip(firstRecord).Take(batchSize).ToList();
thisBatchSize = records.Count();
_log.Info($"Deleting {thisBatchSize} records {firstRecord * 100L / allRecords.Count}% done");
CreateAndInsertIntoTemp(table, primaryKeysWithTypes, tempTable, connection, records);
try
{
DeleteFromTempInOneGo(table, primaryKeysWithTypes, tempTable, connection);
}
catch (Exception ex)
{
_log.Warn($"Error deleting record. Therefore deleting what can be before continuing. Error was {ex.Message}");
var errors = DeleteFromTempOneAtATime(table, primaryKeysWithTypes, tempTable, connection);
if (errors.Any())
{
throw new Exception($"Errors deleting from {table.Name}:\n{GetErrorText(errors)}");
}
}
}
}
}
public void DoDummy(ITableDetails table)
{
Thread.Sleep(4000);
}
public DummyMutateTarget(ILogService log, ITableDetails table) : base(log, table)
{
}
public IEnumerable <dynamic> GetTargetRecords(ITableDetails table, long startFirstId, long endFirstId)
{
return(_targetDatabase.Query <dynamic>($"SELECT * FROM {table.Name} WHERE {table.PrimaryKeys.First()} BETWEEN @StartId AND @EndId ORDER BY {table.PrimaryKeys.First()}", new { StartId = startFirstId, EndId = endFirstId }));
}
public long GetMaxFirstId(ITableDetails table)
{
return(_sourceDatabase.Query <long>($"SELECT MAX({table.PrimaryKeys.First()}) FROM {table.Name}").Single());
}
private static Task <IEnumerable <dynamic> > GetChangedRecords(IDbConnection connection, IDbTransaction transaction, ITableDetails table, IEnumerable <IKeys> keys)
{
const string tempTable = "#GetChangedRecordsTemp";
var dataTable = new DataTable();
foreach (var columnName in table.PrimaryKeys)
{
dataTable.Columns.Add(columnName, typeof(long));
}
foreach (var row in keys)
{
var dataRow = dataTable.NewRow();
var keyEnum = row.GetEnumerator();
foreach (var columnName in table.PrimaryKeys)
{
keyEnum.MoveNext();
dataRow[columnName] = (object)keyEnum.Current ?? DBNull.Value;
}
dataTable.Rows.Add(dataRow);
}
var columnsWithSqlTypes = table.PrimaryKeys.Select(name => $"{name} BIGINT");
connection.Execute($"CREATE TABLE {tempTable} ({string.Join(", ", columnsWithSqlTypes)})", transaction: transaction);
using (var bulkCopy = new SqlBulkCopy((SqlConnection)connection, SqlBulkCopyOptions.Default, (SqlTransaction)transaction)) // TODO: Nasty casts
{
bulkCopy.DestinationTableName = tempTable;
bulkCopy.WriteToServer(dataTable);
}
return(Task.FromResult(connection.Query <dynamic>($@"
SELECT a.*
FROM {table.Name} a
JOIN {tempTable} b
ON {string.Join(" AND ", table.PrimaryKeys.Select(k => $"b.{k} = a.{k}"))}
ORDER BY {string.Join(", ", table.PrimaryKeys.Select(name => $"a.{name}"))}
DROP TABLE {tempTable}
", transaction: transaction)));
}
public Task <IEnumerable <dynamic> > GetSourceRecordsAsync(ITableDetails table, IEnumerable <IKeys> keys)
{
return(GetChangedRecords(_sourceConnection, _sourceTransaction, table, keys));
}
public void BulkInsert(ITableDetails table, IEnumerable <IDictionary <string, object> > records)
{
if (!records.Any())
{
return;
}
var tempTable = "_InsertTemp_" + table.Name;
var columnTypes = GetColumnTypes(records);
if (!table.IdentityInsert)
{
foreach (var primaryKey in table.PrimaryKeys)
{
columnTypes.Remove(primaryKey);
}
}
using (var connection = (SqlConnection)_targetDatabase.GetOpenConnection())
{
if (tempTable == null)
{
try
{
// Bulk insert straight into destination table
BulkInsert(connection, table.Name, records, columnTypes.Where(c => c.Value != null), SqlBulkCopyOptions.CheckConstraints | SqlBulkCopyOptions.KeepIdentity);
}
catch (Exception ex)
{
_log.Warn($"Error inserting record. This may be due to more data having been inserted since the parent table was processed. Therefore inserting what can be before continuing. Error was {ex.Message}");
CreateAndInsertIntoTemp(table, columnTypes, tempTable, connection, records);
var errors = InsertFromTempOneAtATime(table, columnTypes, tempTable, connection);
if (errors.Any())
{
throw new Exception($"Errors inserting into {table.Name}:\n{GetErrorText(errors)}");
}
}
}
else
{
// Insert via temp table.
// This is sometimes required where there is a nullable foreign key due to a bug in SqlBulkCopy whereby inserts with NULL values are rejected
CreateAndInsertIntoTemp(table, columnTypes, tempTable, connection, records);
try
{
InsertFromTemp(table, columnTypes, tempTable, connection);
}
catch (Exception ex)
{
_log.Warn($"Error inserting record. This may be due to more data having been inserted since the parent table was processed. Therefore inserting what can be before continuing. Error was {ex.Message}");
CreateAndInsertIntoTemp(table, columnTypes, tempTable, connection, records);
var errors = InsertFromTempOneAtATime(table, columnTypes, tempTable, connection);
if (errors.Any())
{
throw new Exception($"Errors inserting into {table.Name}:\n{GetErrorText(errors)}");
}
}
}
}
}
