private void InitializeSyncTables()
{
var requestTableName = SqlServerUtils.NormalizeName(config.Source.RequestTable.Table);
var settingTableName = SqlServerUtils.NormalizeName(config.Source.SettingTable.Table);
var sql = $@"
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{RequestTable}') AND type in (N'U'))
CREATE TABLE {RequestTable}(
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[Operation] [int] NOT NULL,
[ResourceId] [bigint] NOT NULL,
[Type] [int] NOT NULL,
[CreatedDate] [datetime2](7) NOT NULL,
[FinishedDate] [datetime2](7) NULL,
CONSTRAINT [PK_{requestTableName}] PRIMARY KEY CLUSTERED
(
[Id] ASC
)
)
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{SettingTable}') AND type in (N'U'))
BEGIN
CREATE TABLE {SettingTable}(
[Code] [nvarchar](100) NOT NULL,
[Value] [nvarchar](500) NOT NULL,
[Description] [nvarchar](500) NULL,
CONSTRAINT [PK_{settingTableName}] PRIMARY KEY CLUSTERED
(
[Code] ASC
)
)
INSERT {SettingTable} ([Code], [Value], [Description]) VALUES (N'{SqlServerUtils.CodeHighWatermark}', N'0', NULL)
END";
dataAccess.Execute(sql);
}
public void ProcessHistory()
{
foreach (var mapping in config.Mappings)
{
var tableNormalName = SqlServerUtils.NormalizeName(mapping.Source);
var mappingTableName = $"{Constants.KeyMappingPrefix}_{tableNormalName}";
var indexColumns = SqlServerUtils.ConcatByComma(mapping.Source.Pks.Select(x => SqlServerUtils.QuoteName(x)));
var highWatermark = GetHighWatermak();
//keep most recent records for conflict policy verification
var affected = dataAccess.Execute($@"DELETE r
FROM {RequestTable} r
INNER JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY Type, ResourceId ORDER BY CreatedDate DESC) AS RowId, Id FROM {RequestTable} WHERE Id < @HighWatermark AND Type = @Type) h ON r.Id = h.Id
WHERE h.RowId > 1
DELETE m
FROM {mappingTableName} m
INNER JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY {indexColumns} ORDER BY {Constants.KeyMappingTableCreatedDate} DESC) AS RowId, {Constants.KeyMappingTablePk} FROM {mappingTableName} WHERE {Constants.KeyMappingTablePk} < @HighWatermark AND Type = @Type) h ON m.{Constants.KeyMappingTablePk} = h.{Constants.KeyMappingTablePk}
WHERE h.RowId > 1"
, new Dictionary <string, object> {
{ "@HighWatermark", highWatermark }, { "@Type", mapping.Type }
});
Interlocked.Add(ref processedUpdateCount, affected);
}
}
private void InitializeMappingTables()
{
foreach (var mapping in config.Mappings)
{
#region defaults
var tableName = SqlServerUtils.QuoteName(mapping.Source);
var tableNormalName = SqlServerUtils.NormalizeName(mapping.Source);
var(pkColumns, columns) = MappingInitializer.Initialize(mapping, dataAccess);
#endregion
#region table definition
var pk = $"{Constants.KeyMappingTablePk} bigint IDENTITY(1,1) PRIMARY KEY";
var createdDate = $"{Constants.KeyMappingTableCreatedDate} datetime2";
var columnList = GetPkColumnMappings(mapping).Concat(mapping.Columns).Select(x =>
{
var column = columns[SqlServerUtils.NormalizeNameWithSpace(x.Source)];
var maxLength = column.DataType == "decimal" ? $"({column.NumericPrecision})" : (column.MaxLength == 0 ? string.Empty : $"({column.MaxLength.ToString(CultureInfo.InvariantCulture)})");
var dataType = $"{SqlServerUtils.QuoteName(column.DataType)} {maxLength}";
var nullable = column.IsNullable ? "NULL" : "NOT NULL";
return($"{SqlServerUtils.QuoteName(x.Source)} {dataType} {nullable}");
});
var columnDefinitions = SqlServerUtils.ConcatByComma(new[] { pk, createdDate }.Concat(columnList));
var mappingTableName = $"{Constants.KeyMappingPrefix}_{tableNormalName}";
var mappingTableNameQuoted = $"{SqlServerUtils.QuoteName(SqlServerUtils.DefaultSchema)}.{SqlServerUtils.QuoteName(mappingTableName)}";
var indexColumns = SqlServerUtils.ConcatByComma(mapping.Source.Pks.Select(x => SqlServerUtils.QuoteName(x)));
var sql = $@"
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{mappingTableNameQuoted}') AND type in (N'U'))
BEGIN
CREATE TABLE {mappingTableNameQuoted} (
{columnDefinitions})
CREATE INDEX IX_{mappingTableName}_Keys ON {mappingTableNameQuoted} ({indexColumns}) INCLUDE ({Constants.KeyMappingTableCreatedDate})
END";
if (dataAccess.Execute(sql) != SqlServerUtils.NonDataAffected)
{
Progress?.Invoke(this, new ProgressEventArgs {
Message = $"Mapping table created: {mappingTableNameQuoted}"
});
}
#endregion
#region triggers
var triggerColumnList = mapping.Columns.Select(x => SqlServerUtils.QuoteName(x.Source));
var triggerColumns = SqlServerUtils.ConcatByComma(mapping.Source.Pks.Concat(triggerColumnList));
//update trigger
var updateTriggerName = $"{tableNormalName}_{Constants.KeyMappingPrefix}_Update";
var updateTrigger = $@"
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{updateTriggerName}') AND type in (N'TR'))
EXEC dbo.sp_executesql @statement = N'
CREATE TRIGGER {updateTriggerName}
ON {tableName}
AFTER UPDATE
AS
BEGIN
IF App_Name() = ''{Constants.AppName}''
RETURN
INSERT INTO {mappingTableName} ({triggerColumns}, {Constants.KeyMappingTableCreatedDate})
SELECT {triggerColumns}, SYSUTCDATETIME()
FROM INSERTED
{GetInsertRequestSql(Operation.Update, mapping.Type)}
END
'";
//insert trigger
var insertTriggerName = $"{tableNormalName}_{Constants.KeyMappingPrefix}_Insert";
var insertTrigger = $@"
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{insertTriggerName}') AND type in (N'TR'))
EXEC dbo.sp_executesql @statement = N'
CREATE TRIGGER {insertTriggerName}
ON {tableName}
AFTER INSERT
AS
BEGIN
IF App_Name() = ''{Constants.AppName}''
RETURN
INSERT INTO {mappingTableName} ({triggerColumns}, {Constants.KeyMappingTableCreatedDate})
SELECT {triggerColumns}, SYSUTCDATETIME()
FROM INSERTED
{GetInsertRequestSql(Operation.Insert, mapping.Type)}
END
'";
//delete trigger
var deleteTriggerName = $"{tableNormalName}_{Constants.KeyMappingPrefix}_Delete";
var deleteTrigger = $@"
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{deleteTriggerName}') AND type in (N'TR'))
EXEC dbo.sp_executesql @statement = N'
CREATE TRIGGER {deleteTriggerName}
ON {tableName}
AFTER DELETE
AS
BEGIN
IF App_Name() = ''{Constants.AppName}''
RETURN
{GetInsertRequestSql(Operation.Delete, mapping.Type)}
END
'";
if (dataAccess.Execute($@"{updateTrigger}
{insertTrigger}
{deleteTrigger}") != SqlServerUtils.NonDataAffected)
{
Progress?.Invoke(this, new ProgressEventArgs {
Message = $"Triggers for mapping table created: {mappingTableName}"
});
}
#endregion
}
}
private void Run(long highWatermark)
{
dataAccess.DoBiz($"SELECT TOP (@BufferSize) Id, Type, Operation, ResourceId, CreatedDate FROM {RequestTable} WHERE Id > @HighWatermark", x =>
{
using (var reader = x.ExecuteReader())
{
while (reader.Read())
{
if (processCancelled)
{
break;
}
Interlocked.Increment(ref requestCount);
SyncRequest request = null;
try
{
var id = (long)reader["Id"];
var type = (int)reader["Type"];
var operation = (Operation)(int)reader["Operation"];
var resourceId = (long)reader["ResourceId"];
var mapping = config.Mappings.First(m => m.Type == type);
var columns = SqlServerUtils.ConcatByComma(GetPkColumnMappings(mapping).Concat(new[] { new ColumnMapping {
Source = Constants.KeyMappingTableCreatedDate
} }).Concat(mapping.Columns).Select(c => SqlServerUtils.QuoteName(c.Source)));
var tableNormalName = $"{SqlServerUtils.NormalizeName(mapping.Source)}";
var mappingTableName = $"{Constants.KeyMappingPrefix}_{tableNormalName}";
var data = dataAccess.GetData($"SELECT {columns} FROM {mappingTableName} WHERE {Constants.KeyMappingTablePk} = @Pk", new Dictionary <string, object> {
{ "@Pk", resourceId }
});
if (data.Rows.Count > 0)
{
var createdDate = (DateTime)data.Rows[0][Constants.KeyMappingTableCreatedDate];
request = new SyncRequest {
Id = id, Type = type, Operation = operation, CreatedDate = createdDate, TaskType = config.Source.Type, TaskName = config.Name
};
Progress?.Invoke(this, new ProgressEventArgs {
Message = $"Request found", Request = request
});
request.ResourceIds.AddRange(mapping.Source.Pks.Select(pk => data.Rows[0][SqlServerUtils.NormalizeNameWithSpace(pk)]));
if (onRequest(request, data).GetAwaiter().GetResult())
{
Interlocked.Increment(ref processedCount);
switch (operation)
{
case Operation.Update:
Interlocked.Increment(ref processedUpdateCount);
break;
case Operation.Insert:
Interlocked.Increment(ref processedInsertCount);
break;
case Operation.Delete:
Interlocked.Increment(ref processedDeleteCount);
break;
}
string historySql;
switch (config.Source.HistoryStrategy.Type)
{
case HistoryStrategyType.ProcessAndDelete:
Interlocked.Increment(ref historyDeleteCount);
historySql = $@"DELETE FROM {mappingTableName} WHERE {Constants.KeyMappingTablePk} = @Pk
DELETE FROM {RequestTable} WHERE Id = @Id" ;
break;
case HistoryStrategyType.None:
historySql = $"UPDATE {RequestTable} SET FinishedDate = SYSUTCDATETIME() WHERE Id = @Id";
break;
default:
historySql = string.Empty;
break;
}
dataAccess.Execute($@"UPDATE {SettingTable} SET Value = @HighWatermark WHERE Code = @Code
{historySql}"
, new Dictionary <string, object> {
{ "@Id", id }, { "@Code", SqlServerUtils.CodeHighWatermark }, { "@HighWatermark", highWatermark }, { "@Pk", resourceId }
});
}
}
else
{
Progress?.Invoke(this, new ProgressEventArgs {
Message = $"Requested record not found", Request = request
});
}
LastHighWatermark = highWatermark;
}
catch (Exception ex)
{
Progress?.Invoke(this, new ProgressEventArgs {
Message = $"Exception encountered while processing request: {ex.Message}", Request = request
});
}
}
}
}, new Dictionary <string, object> {
{ "@BufferSize", config.BufferSize }, { "@HighWatermark", highWatermark }
});
}
private int ProcessRow(SyncRequest request, SyncTarget target, Mapping mapping, DataRow row, string pkConditions, string table)
{
var dataAccess = dataAccesses[target.Id];
var parameters = new Dictionary <string, object>(); //todo: what if pk is updated... WTF???
var affected = SqlServerUtils.NonDataAffected;
foreach (var pk in mapping.Target.Pks)
{
var columnName = SqlServerUtils.NormalizeNameWithSpace(pk);
parameters.Add($"@{columnName}", row[columnName]);
}
switch (request.Operation)
{
case Operation.Update:
var tableNormalName = $"{SqlServerUtils.NormalizeName(mapping.Source)}";
var mappingTableName = $"{SqlServerUtils.QuoteName(SqlServerUtils.DefaultSchema)}.{Constants.KeyMappingPrefix}_{tableNormalName}";
var mostRecentUpdatedDate = dataAccess.GetValue <DateTime?>($@"IF EXISTS (SELECT NULL FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @Table AND TABLE_SCHEMA = @Schema)
SELECT MAX(CreatedDate) FROM {mappingTableName} WHERE {pkConditions}
ELSE
SELECT NULL" , new Dictionary <string, object>(parameters)
{
{ "@Table", mappingTableName }, { "@Schema", SqlServerUtils.QuoteName(mapping.Source.Schema) }
});
var needToUpdate = false;
if (mostRecentUpdatedDate == null)
{
needToUpdate = true;
}
else if (request.CreatedDate > mostRecentUpdatedDate)
{
needToUpdate = true;
}
else if ((role == SyncRole.Hub && mapping.ConflictPolicy == ConflictPolicy.MemberWin) ||
(role == SyncRole.Member && mapping.ConflictPolicy == ConflictPolicy.HubWin))
{
needToUpdate = true;
}
if (needToUpdate)
{
var updateColumns = SqlServerUtils.ConcatByComma(mapping.Columns.Select(x => $"{SqlServerUtils.QuoteName(x.Target)} = @{SqlServerUtils.NormalizeNameWithSpace(x.Target)}"));
Progress?.Invoke(this, new ProgressEventArgs {
Message = $"Updating table {table}", Request = request
});
foreach (var item in mapping.Columns)
{
parameters.Add($"@{SqlServerUtils.NormalizeNameWithSpace(item.Target)}", row[SqlServerUtils.NormalizeNameWithSpace(item.Source)]);
}
affected = dataAccess.Execute($"UPDATE {table} SET {updateColumns} WHERE {pkConditions}", parameters);
}
else
{
Progress?.Invoke(this, new ProgressEventArgs {
Message = $"Update to table {table} ignored because of conflict", Request = request
});
}
Interlocked.Increment(ref processedUpdateCount);
if (affected != SqlServerUtils.NonDataAffected)
{
Interlocked.Increment(ref processedAndUpdatedCount);
}
break;
case Operation.Insert:
var pkColumns = mapping.Source.Pks.Select((x, i) => new ColumnMapping {
Target = mapping.Target.Pks[i], Source = x
});
var insertColumns = SqlServerUtils.ConcatByComma(pkColumns.Concat(mapping.Columns).Select(x => $"{SqlServerUtils.QuoteName(x.Target)}"));
var valueColumns = SqlServerUtils.ConcatByComma(pkColumns.Concat(mapping.Columns).Select(x => $"@{SqlServerUtils.NormalizeNameWithSpace(x.Target)}"));
Progress?.Invoke(this, new ProgressEventArgs {
Message = $"Inserting into table {table}", Request = request
});
foreach (var item in mapping.Columns)
{
parameters.Add($"@{SqlServerUtils.NormalizeNameWithSpace(item.Target)}", row[SqlServerUtils.NormalizeNameWithSpace(item.Source)]);
}
affected = dataAccess.Execute($"INSERT INTO {table} ({insertColumns}) VALUES ({valueColumns})", parameters);
Interlocked.Increment(ref processedInsertCount);
if (affected != SqlServerUtils.NonDataAffected)
{
Interlocked.Increment(ref processedAndInsertedCount);
}
break;
case Operation.Delete:
Progress?.Invoke(this, new ProgressEventArgs {
Message = $"Deleting from table {table}", Request = request
});
affected = dataAccess.Execute($"DELETE FROM {table} WHERE {pkConditions}", parameters);
Interlocked.Increment(ref processedDeleteCount);
if (affected != SqlServerUtils.NonDataAffected)
{
Interlocked.Increment(ref processedAndDeletedCount);
}
break;
}
return(affected);
}