public void Setup(SyncRole role, TaskConfig config)
{
this.role = role;
this.config = config;
foreach (var target in config.Targets)
{
var dataAccess = new SqlDataAccess(target.ConnectionString);
foreach (var mapping in config.Mappings)
{
MappingInitializer.Initialize(mapping, dataAccess);
}
dataAccesses.TryAdd(target.Id, dataAccess);
}
}
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
}
}
