/// Searches all the tables in a datalink for a particular columnKey
public DexihDatalinkColumn GetDatalinkColumn(DexihHub hub, DexihDatalink hubDatalink, long?datalinkColumnKey)
{
if (datalinkColumnKey == null || hubDatalink == null)
{
return(null);
}
DexihDatalinkColumn column;
column = hubDatalink.SourceDatalinkTable.DexihDatalinkColumns.SingleOrDefault(c => c.Key == (long)datalinkColumnKey);
if (column != null)
{
return(column);
}
foreach (var datalinkTransform in hubDatalink.DexihDatalinkTransforms)
{
foreach (var item in datalinkTransform.DexihDatalinkTransformItems)
{
if (item.SourceDatalinkColumnKey == datalinkColumnKey)
{
return(item.SourceDatalinkColumn);
}
if (item.TargetDatalinkColumnKey == datalinkColumnKey)
{
return(item.TargetDatalinkColumn);
}
foreach (var param in item.DexihFunctionParameters)
{
if (param.DatalinkColumnKey == datalinkColumnKey)
{
return(param.DatalinkColumn);
}
}
}
if (datalinkTransform.JoinDatalinkTableKey != null)
{
column = datalinkTransform.JoinDatalinkTable.DexihDatalinkColumns.SingleOrDefault(c => c.Key == (long)datalinkColumnKey);
if (column != null)
{
return(column);
}
}
}
return(null);
}
public async Task NewDatalink()
{
//test 1, create initial table
using (var repositoryManager = await CreateRepositoryWithSourceTable())
{
var dbSourceConnection = await repositoryManager.DbContext.DexihConnections.SingleOrDefaultAsync(c => c.Name == "source" && c.IsValid);
var dbManagedConnection = await repositoryManager.DbContext.DexihConnections.SingleOrDefaultAsync(c => c.Name == "managed" && c.IsValid);
var dbSourceTable = await repositoryManager.DbContext.DexihTables.FirstAsync(c => c.Name == "table1" && c.IsValid);
var newDatalinks = await repositoryManager.NewDatalinks(
dbSourceConnection.HubKey,
"test",
EDatalinkType.Stage,
dbManagedConnection.Key,
new[] { dbSourceTable.Key },
null,
null,
dbManagedConnection.Key,
true,
new [] { EDeltaType.AutoIncrement, EDeltaType.CreateDate, EDeltaType.UpdateDate, EDeltaType.CreateAuditKey, EDeltaType.IsCurrentField, EDeltaType.UpdateAuditKey, EDeltaType.ValidFromDate, EDeltaType.ValidToDate },
new NamingStandards(), CancellationToken.None
);
Assert.Single(newDatalinks);
var newDatalink = newDatalinks[0];
Assert.NotNull(newDatalink);
var cacheManager = new CacheManager(dbSourceConnection.HubKey, _loggerFactory.CreateLogger("test"));
var dbDatalink = await cacheManager.GetDatalink(newDatalink.Key, repositoryManager.DbContext);
// check the datalink properties are set.
Assert.False(dbDatalink.AddDefaultRow);
Assert.Equal(dbManagedConnection.Key, dbDatalink.AuditConnectionKey);
Assert.Equal(EDatalinkType.Stage, dbDatalink.DatalinkType);
Assert.Equal(1, dbDatalink.DexihDatalinkTransforms.Count);
Assert.Equal(dbSourceConnection.HubKey, dbDatalink.HubKey);
Assert.False(dbDatalink.IsShared);
Assert.True(dbDatalink.IsValid);
Assert.False(dbDatalink.IsQuery);
Assert.False(dbDatalink.RollbackOnFail);
Assert.Null(dbDatalink.SourceDatalinkTable.SourceDatalinkKey);
Assert.Equal(dbSourceTable.Key, dbDatalink.SourceDatalinkTable.SourceTableKey);
Assert.Equal(ESourceType.Table, dbDatalink.SourceDatalinkTable.SourceType);
Assert.True(dbDatalink.DexihDatalinkTargets.Count > 0);
Assert.Equal(EUpdateStrategy.AppendUpdateDelete, dbDatalink.UpdateStrategy);
// check target table created properly.
var dbTable = await repositoryManager.GetTable(newDatalink.HubKey, (long)newDatalink.DexihDatalinkTargets.First().TableKey, true, CancellationToken.None);
Assert.Equal("stgtable1", dbTable.Name);
Assert.Equal("table1", dbTable.BaseTableName);
//should be no sourcesurrgate key, as source table has no surrogate key
Assert.Null(dbTable.DexihTableColumns.SingleOrDefault(c => c.DeltaType == EDeltaType.SourceSurrogateKey));
Assert.NotNull(dbTable.DexihTableColumns.SingleOrDefault(c => c.DeltaType == EDeltaType.AutoIncrement));
Assert.NotNull(dbTable.DexihTableColumns.SingleOrDefault(c => c.DeltaType == EDeltaType.CreateAuditKey));
Assert.NotNull(dbTable.DexihTableColumns.SingleOrDefault(c => c.DeltaType == EDeltaType.UpdateAuditKey));
Assert.NotNull(dbTable.DexihTableColumns.SingleOrDefault(c => c.DeltaType == EDeltaType.CreateDate));
Assert.NotNull(dbTable.DexihTableColumns.SingleOrDefault(c => c.DeltaType == EDeltaType.UpdateDate));
Assert.NotNull(dbTable.DexihTableColumns.SingleOrDefault(c => c.DeltaType == EDeltaType.IsCurrentField));
// check target columns were created
foreach (var col in dbSourceTable.DexihTableColumns)
{
// var name = dbManagedConnection.DatabaseType.RemoveUnsupportedCharacaters(col.Name);
var name = col.Name;
if (col.DeltaType == EDeltaType.IgnoreField)
{
Assert.Null(dbTable.DexihTableColumns.SingleOrDefault(c => c.Name == name));
}
else
{
var targetColumn = dbTable.DexihTableColumns.SingleOrDefault(c => c.Name == name);
Assert.NotNull(targetColumn);
//if the target column name is cleaned and not the same, then a mapping should be created.
if (name != col.Name)
{
var datalinkTransform = dbDatalink.DexihDatalinkTransforms.SingleOrDefault(c => c.TransformType == ETransformType.Mapping);
Assert.NotNull(datalinkTransform);
var mapping = datalinkTransform.DexihDatalinkTransformItems.SingleOrDefault(c => c.SourceDatalinkColumn.Name == col.Name);
Assert.NotNull(mapping);
Assert.Equal(targetColumn.Name, mapping.TargetDatalinkColumn.Name);
}
}
}
dbDatalink.Name = "new name";
await repositoryManager.SaveDatalinks(dbSourceConnection.HubKey, new[] { dbDatalink }, false, CancellationToken.None);
var dbDatalink2 = await cacheManager.GetDatalink(dbDatalink.Key, repositoryManager.DbContext);
Assert.Equal("new name", dbDatalink2.Name);
var dbDatalink3 = new DexihDatalink();
dbDatalink2.CopyProperties(dbDatalink3);
dbDatalink3.Name = "newer name";
await repositoryManager.SaveDatalinks(dbSourceConnection.HubKey, new[] { dbDatalink3 }, false, CancellationToken.None);
var dbDatalink4 = await cacheManager.GetDatalink(dbDatalink.Key, repositoryManager.DbContext);
Assert.Equal("newer name", dbDatalink4.Name);
}
}
// [HttpPost("[action]")]
public async Task <string> OpenTableQuery([FromBody] OpenTableQueryModel parameters, CancellationToken cancellationToken)
{
try
{
var hub = await IsAuthorizedHub(parameters.HubName, null, cancellationToken);
var repositoryManager = _operations.RepositoryManager;
// var remoteAgent = await _remoteAgents.GetRemoteAgent(parameters.InstanceId, hub.HubKey, _operations.RepositoryManager);
var cache = new CacheManager(hub.HubKey, hub.EncryptionKey);
await cache.InitHub(DbContext);
DexihDatalink dbDatalink;
if (parameters.TableSchema == DatalinkSchema)
{
var dbDatalinkObject = await DbContext.DexihDatalinks.SingleOrDefaultAsync(c =>
c.IsValid &&
c.HubKey == hub.HubKey &&
c.Name == parameters.TableName &&
c.IsShared, cancellationToken : cancellationToken);
if (dbDatalinkObject == null)
{
throw new ReaderControllerException($"A datalink with the name {parameters.TableName} was not found.");
}
dbDatalink = await cache.GetDatalink(dbDatalinkObject.Key, DbContext);
await cache.LoadDatalinkDependencies(dbDatalink, true, DbContext);
}
else
{
// get a reference to the source connection
var dbSourceConnection = await DbContext.DexihConnections.SingleOrDefaultAsync(c =>
c.IsValid &&
c.Name == parameters.SourceConnectionName &&
c.HubKey == hub.HubKey, cancellationToken : cancellationToken);
if (dbSourceConnection == null)
{
throw new ReaderControllerException($"A connection with the name {parameters.SourceConnectionName} was not found.");
}
var dbTable = await DbContext.DexihTables.SingleOrDefaultAsync(c =>
c.IsValid &&
c.HubKey == hub.HubKey &&
c.Name == parameters.TableName &&
c.Schema == parameters.TableSchema &&
c.ConnectionKey == dbSourceConnection.Key &&
c.IsShared, cancellationToken : cancellationToken);
if (dbTable == null)
{
throw new ReaderControllerException($"A table with the name {parameters.TableName} for connection {parameters.SourceConnectionName} was not found or is not shared.");
}
var dbColumns = await DbContext.DexihTableColumns
.Where(c => c.TableKey == dbTable.Key && c.IsValid).ToArrayAsync(cancellationToken: cancellationToken);
// create a temporary datalink that pulls data from the source table.
dbDatalink = new DexihDatalink
{
SourceDatalinkTable = new DexihDatalinkTable
{
SourceType = ESourceType.Table,
SourceTable = dbTable,
SourceTableKey = dbTable.Key,
DexihDatalinkColumns = dbColumns.OrderBy(c => c.Position).Select(c =>
{
var column = new DexihDatalinkColumn();
c.CopyProperties(column, true);
return(column);
}).ToArray()
},
};
await cache.AddTables(new[] { dbTable.Key }, DbContext);
}
dbDatalink.AuditConnectionKey = 0;
cache.Hub.DexihDatalinks.Add(dbDatalink);
var value = new
{
cache,
DatalinkKey = dbDatalink.Key,
SelectQuery = parameters.Query,
parameters.DownloadUrl
};
var result = await _remoteAgents.SendRemoteCommand(parameters.InstanceId, hub.HubKey, parameters.DownloadUrl, nameof(RemoteOperations.GetReaderData), value, repositoryManager, cancellationToken);
// returns a pointer to the download url.
return(result);
}
catch (Exception ex)
{
throw new ReaderControllerException("OpenTableQuery Error: " + ex.Message, ex);
}
}
public (Transform sourceTransform, Table sourceTable) CreateRunPlan(DexihHub hub, DexihDatalink hubDatalink, InputColumn[] inputColumns, long?maxDatalinkTransformKey, object maxIncrementalValue, TransformWriterOptions transformWriterOptions) //Last datatransform key is used to preview the output of a specific transform in the series.
{
try
{
_logger?.LogTrace($"CreateRunPlan {hubDatalink.Name} started.");
var timer = Stopwatch.StartNew();
if (transformWriterOptions == null)
{
transformWriterOptions = new TransformWriterOptions();
}
var primaryTransformResult = GetSourceTransform(hub, hubDatalink.SourceDatalinkTable, inputColumns, transformWriterOptions);
var primaryTransform = primaryTransformResult.sourceTransform;
var sourceTable = primaryTransformResult.sourceTable;
var updateStrategy = hubDatalink.UpdateStrategy;
//add a filter for the incremental column (if there is one)
TableColumn incrementalCol = null;
if (updateStrategy == EUpdateStrategy.AppendUpdateDeletePreserve || updateStrategy == EUpdateStrategy.AppendUpdatePreserve)
{
incrementalCol = primaryTransform.CacheTable?.GetColumn(EDeltaType.ValidFromDate);
}
else
{
incrementalCol = primaryTransform.CacheTable?.Columns.SingleOrDefault(c => c.IsIncrementalUpdate);
}
if (transformWriterOptions.ResetIncremental)
{
maxIncrementalValue = transformWriterOptions.ResetIncrementalValue;
}
if (maxDatalinkTransformKey == null &&
transformWriterOptions.IsEmptyTarget() == false &&
!(updateStrategy == EUpdateStrategy.Reload || updateStrategy == EUpdateStrategy.AppendUpdateDelete || updateStrategy == EUpdateStrategy.AppendUpdateDeletePreserve) &&
incrementalCol != null &&
maxIncrementalValue != null &&
maxIncrementalValue.ToString() != "")
{
var mappings = new Mappings()
{
new MapFilter(incrementalCol, maxIncrementalValue, ECompare.GreaterThan)
};
var filterTransform = new TransformFilter(primaryTransform, mappings)
{
Name = $"Prefilter maxIncremental {maxIncrementalValue}"
};
filterTransform.SetInTransform(primaryTransform);
primaryTransform = filterTransform;
}
DexihTable targetTable = null;
var target = hubDatalink.DexihDatalinkTargets.FirstOrDefault(c => c.NodeDatalinkColumnKey == null);
if (target != null)
{
targetTable = hub.GetTableFromKey(target.TableKey);
}
_logger?.LogTrace($"CreateRunPlan {hubDatalink.Name}. Added incremental filter. Elapsed: {timer.Elapsed}");
//loop through the transforms to create the chain.
foreach (var datalinkTransform in hubDatalink.DexihDatalinkTransforms.OrderBy(c => c.Position).Where(c => c.IsValid))
{
//if this is an empty transform, then ignore it.
if (datalinkTransform.DexihDatalinkTransformItems.Count == 0)
{
if (datalinkTransform.TransformType == ETransformType.Filter || datalinkTransform.TransformType == ETransformType.Mapping && datalinkTransform.PassThroughColumns)
{
if (datalinkTransform.Key == maxDatalinkTransformKey)
{
break;
}
continue;
}
}
//if contains a join table, then add it in.
Transform referenceTransform = null;
if (datalinkTransform.JoinDatalinkTable != null)
{
var joinTransformResult = GetSourceTransform(hub, datalinkTransform.JoinDatalinkTable, null, transformWriterOptions);
referenceTransform = joinTransformResult.sourceTransform;
}
var transform = datalinkTransform.GetTransform(hub, hubDatalink, transformWriterOptions.GlobalSettings, _transformSettings, primaryTransform, referenceTransform, targetTable, _logger);
_logger?.LogTrace($"CreateRunPlan {hubDatalink.Name}, adding transform {datalinkTransform.Name}. Elapsed: {timer.Elapsed}");
primaryTransform = transform;
if (datalinkTransform.Key == maxDatalinkTransformKey)
{
break;
}
}
//if the maxDatalinkTransformKey is null (i.e. we are not doing a preview), and there are profiles add a profile transform.
if (maxDatalinkTransformKey == null && hubDatalink.DexihDatalinkProfiles != null && hubDatalink.DexihDatalinkProfiles.Count > 0 && targetTable != null)
{
var profileRules = new Mappings();
foreach (var profile in hubDatalink.DexihDatalinkProfiles)
{
foreach (var column in targetTable.DexihTableColumns.Where(c => c.IsSourceColumn))
{
var profileFunction = GetProfileFunction(profile.FunctionAssemblyName, profile.FunctionClassName, profile.FunctionMethodName, column.Name, profile.DetailedResults, transformWriterOptions.GlobalSettings);
profileRules.Add(profileFunction);
}
}
var transform = new TransformProfile(primaryTransform, profileRules)
{
Name = "User defined profiles"
};
primaryTransform = transform;
_logger?.LogTrace($"CreateRunPlan {hubDatalink.Name}, adding profiling. Elapsed: {timer.Elapsed}");
}
if (transformWriterOptions.SelectQuery != null)
{
var transform = new TransformQuery(primaryTransform, transformWriterOptions.SelectQuery)
{
Name = "Select Query Filter"
};
primaryTransform = transform;
}
_logger?.LogTrace($"CreateRunPlan {hubDatalink.Name}, completed. Elapsed: {timer.Elapsed}");
return(primaryTransform, sourceTable);
}
catch (Exception ex)
{
throw new TransformManagerException($"Create run plan failed. {ex.Message}", ex);
}
}
