public static void ProcessLateArrivingTable(AstTableNode astTableNode)
{
var lateArrivedColumn = new AstTableColumnNode(astTableNode) { Name = "_LateArrived", IsNullable = true, ColumnType = ColumnType.DateTime2, IsAutoGenerated = true };
var markLateColumn = new AstTableColumnNode(astTableNode) { Name = "_IsLate", IsNullable = true, ColumnType = ColumnType.Boolean, IsAutoGenerated = true, Computed = "_LateArrived IS NOT NULL PERSISTED NOT NULL" };
astTableNode.Columns.Add(lateArrivedColumn);
astTableNode.Columns.Add(markLateColumn);
}
public static void ValidateScdTable(AstTableNode table)
{
// TODO: Can we use identity or primary (or just primary)
// TODO: Need to add check - For implicit invocation, the destination can not be an OLEDB destination. A compile-time error will be emitted if so.
bool hasIdentity = table.Keys.Any(item => item is AstTableIdentityNode);
bool hasPrimaryKey = table.Keys.Any(item => item is AstTablePrimaryKeyNode);
if (!(hasIdentity ^ hasPrimaryKey))
{
MessageEngine.Trace(table, Severity.Error, "V0133", "To support Scd Columns, table {0} must provide either identity or primary key", table.Name);
}
// TODO: Should we be overwriting these? And do we need to recheck the table for Scd columns after rewriting?
foreach (var keyColumn in table.PreferredKey.Columns)
{
keyColumn.Column.ScdType = ScdType.Key;
}
foreach (AstTableColumnBaseNode column in table.Columns)
{
if (column.IsAssignable && column.ScdType == ScdType.Update && !column.IsNullable && String.IsNullOrEmpty(column.Default))
{
MessageEngine.Trace(table, Severity.Error, "V0134", "Non-nullable ScdType.Update columns in table {0} with Error or Historical columns must have default values. Provide a default value for column {1}", table.Name, column.Name);
}
}
}
public static void ValidateLateArrivingTable(AstTableNode table)
{
bool hasIdentity = table.Keys.Any(item => item is AstTableIdentityNode);
bool hasPrimaryKey = table.Keys.Any(item => item is AstTablePrimaryKeyNode);
if (!hasIdentity ^ hasPrimaryKey)
{
MessageEngine.Trace(table, Severity.Error, "V0130", "To support Late Arriving, table {0} must provide either identity or primary key", table.Name);
}
bool foundEligibleKey = false;
foreach (AstTableKeyBaseNode key in table.Keys)
{
if (key.Columns.Any(keyColumn => !keyColumn.Column.IsComputed))
{
if (foundEligibleKey)
{
MessageEngine.Trace(table, Severity.Error, "V0131", "There can be at most one (primary, identity, unique) key that is based on non-computed columns for Late Arriving Table {0}", table.Name);
}
foundEligibleKey = true;
}
}
foreach (AstTableColumnBaseNode column in table.Columns)
{
if (!column.IsNullable && String.IsNullOrEmpty(column.Default))
{
MessageEngine.Trace(table, Severity.Error, "V0132", "Late Arriving Table {0} must have default values for all columns that are non-nullable.", table.Name);
}
}
}
public StaticSourceTSqlEmitter(AstTableNode table)
{
_table = table;
_defaultValueBuilder = new StringBuilder();
_defaultValueEmitter = new TemplatePlatformEmitter("SimpleInsert");
_columnNames = new List<string>();
_defaultValues = new List<string>();
}
public static IEnumerable<AstPackageNode> ProcessTableStaticSource(AstTableNode astTableNode)
{
var packageList = new List<AstPackageNode>();
foreach (var source in astTableNode.Sources)
{
var staticSource = source as AstTableStaticSourceNode;
if (staticSource != null && staticSource.Rows.Count > 0)
{
var table = staticSource.ParentItem as AstTableNode;
if (table != null && staticSource.EmitMergePackage)
{
if (table.PreferredKey == null)
{
MessageEngine.Trace(table, Severity.Error, "L0110", "Table {0} does not contain a primary key and therefore cannot use Static Source Merging. Add a primary key or set EmitMergePackage to false.", table.Name);
}
var package = new AstPackageNode(table.ParentItem)
{
Emit = table.Emit,
Log = false,
Name = staticSource.Name,
PackageFolderSubpath = table.Name,
PackageType = GetPackageType(table)
};
// Staging Container
var staging = new AstStagingContainerTaskNode(package)
{
Log = false,
Name = Utility.NameCleanerAndUniqifier(source.Name + "_Stg"),
};
var stagingCloneTable = new AstTableCloneNode(staging)
{
Name =
String.Format(CultureInfo.InvariantCulture, "__{0}_Static", table.Name),
Connection = table.Connection,
Table = table
};
staging.Tables.Add(stagingCloneTable);
CloneTableLowerer.LowerCloneTable(stagingCloneTable);
staging.Tasks.Add(CreateInsertExecuteSql(staticSource, staging, stagingCloneTable));
staging.Tasks.Add(CreateMergeTask(table, staging, stagingCloneTable));
package.Tasks.Add(staging);
packageList.Add(package);
staticSource.LoweredPackage = package;
}
}
}
return packageList;
}
public static string GetPackageType(AstTableNode tableNode)
{
if (tableNode is AstDimensionNode)
{
return "Dimension";
}
if (tableNode is AstFactNode)
{
return "FactTable";
}
return "Table";
}
public static void ProcessTableScdColumns(AstTableNode astTableNode)
{
AstLowererValidation.ValidateScdTable(astTableNode);
bool emitScdColumns = astTableNode.Columns.Any(column => column.ScdType == ScdType.Historical);
if (emitScdColumns)
{
var scdFromColumn = new AstTableColumnNode(astTableNode) { Name = "_scdFrom", ColumnType = ColumnType.DateTime2, IsAutoGenerated = true };
astTableNode.Columns.Add(scdFromColumn);
var scdToColumn = new AstTableColumnNode(astTableNode) { Name = "_scdTo", ColumnType = ColumnType.DateTime2, IsAutoGenerated = true };
astTableNode.Columns.Add(scdToColumn);
}
}
private static string GetInsertStatements(AstTableNode table, AstTableStaticSourceNode staticSource)
{
var staticSourceEmitter = new StaticSourceTSqlEmitter(table);
foreach (AstStaticSourceRowNode row in staticSource.Rows)
{
foreach (AstStaticSourceColumnValueNode columnValue in row.ColumnValues)
{
staticSourceEmitter.AddDefaultValue(columnValue.Column.Name, columnValue.Value);
}
staticSourceEmitter.CompleteRow();
}
return staticSourceEmitter.Emit();
}
public static AstPackageNode ProcessTable(AstTableNode tableNode)
{
string packageTypeName = "Table";
if (tableNode is AstDimensionNode)
{
packageTypeName = "Dimension";
}
else if (tableNode is AstFactNode)
{
packageTypeName = "FactTable";
}
// TODO: Fix this null parent node
var packageNode = new AstPackageNode(tableNode.ParentItem);
packageNode.Name = tableNode.Name;
packageNode.PackageType = packageTypeName;
packageNode.Emit = tableNode.Emit;
LowerTable(packageNode, tableNode, tableNode.SchemaQualifiedName, false);
return packageNode;
}
public static AstExecuteSqlTaskNode CreateInsertExecuteSql(AstTableStaticSourceNode staticSource, IFrameworkItem insertParent, AstTableNode insertTargetTable)
{
var executeSql = new AstExecuteSqlTaskNode(insertParent)
{
Connection = insertTargetTable.Connection,
ExecuteDuringDesignTime = false,
Name = Utility.NameCleanerAndUniqifier(insertTargetTable + "_Insert"),
};
executeSql.Query = new AstExecuteSqlQueryNode(executeSql)
{
Body = GetInsertStatements(insertTargetTable, staticSource),
QueryType = QueryType.Standard
};
return executeSql;
}
private static void BuildLateArrivingPath(AstTableNode targetTable, IFrameworkItem parentItem, List<AstTransformationNode> workflowFragment, AstDataflowOutputPathNode scdLateArrivingPath)
{
var inferredCommand = new AstOleDBCommandNode(parentItem)
{
Name = Utility.NameCleanerAndUniqifier(targetTable.Name + "_InferredCommand"),
Connection = targetTable.Connection
};
inferredCommand.Query = new AstTransformationMappedQueryNode(inferredCommand);
int inferredIndex = 0;
var inferredSetColumnValueMappings = new List<TableColumnValueMapping>();
foreach (var column in targetTable.Columns)
{
if (column.IsAssignable && !column.IsAutoGenerated)
{
inferredCommand.Query.Mappings.Add(new AstDataflowColumnMappingNode(inferredCommand) { SourceName = column.Name, TargetName = String.Format(CultureInfo.InvariantCulture, "Param_{0}", inferredIndex++) });
inferredSetColumnValueMappings.Add(new TableColumnValueMapping(column.Name, "?", MappingOperator.Assign));
}
}
if (targetTable.LateArriving)
{
inferredSetColumnValueMappings.Add(new TableColumnValueMapping("_LateArrived", "NULL", MappingOperator.Assign));
}
var inferredWhereColumnValueMappings = new List<TableColumnValueMapping>();
foreach (var keyColumn in targetTable.PreferredKey.Columns)
{
inferredCommand.Query.Mappings.Add(new AstDataflowColumnMappingNode(inferredCommand) { SourceName = keyColumn.Column.Name, TargetName = String.Format(CultureInfo.InvariantCulture, "Param_{0}", inferredIndex++) });
inferredWhereColumnValueMappings.Add(new TableColumnValueMapping(keyColumn.Column.Name, "?", MappingOperator.CompareEqual));
}
if (targetTable.LateArriving)
{
inferredWhereColumnValueMappings.Add(new TableColumnValueMapping("_LateArrived", "NULL", MappingOperator.CompareIsNot));
}
inferredCommand.Query.Body = TableLowerer.EmitUpdateStatement(targetTable, inferredSetColumnValueMappings, inferredWhereColumnValueMappings);
inferredCommand.ValidateExternalMetadata = false;
inferredCommand.InputPath = new AstDataflowMappedInputPathNode(inferredCommand) { OutputPath = scdLateArrivingPath };
workflowFragment.Add(inferredCommand);
}
internal static void LowerTable(AstContainerTaskNode containerNode, AstTableNode tableNode, string executeSqlTaskName, bool executeDuringDesignTime)
{
var tableEmitter = new TableTSqlEmitter(tableNode.SchemaQualifiedName, tableNode.CompressionType.ToString().ToUpper(CultureInfo.InvariantCulture));
foreach (AstTableColumnBaseNode columnBase in tableNode.Columns)
{
ProcessAstTableColumnBaseNode(tableNode, tableEmitter.ColumnsEmitter, columnBase);
var tableReference = columnBase as AstTableColumnTableReferenceNode;
var dimReference = columnBase as AstTableColumnDimensionReferenceNode;
if (tableReference != null && tableReference.EnforceForeignKeyConstraint)
{
tableEmitter.ConstraintsEmitter.AppendForeignKeyConstraintFromReference(tableNode, tableReference.ForeignKeyNameOverride, tableReference.Name, tableReference.Table);
}
if (dimReference != null && dimReference.EnforceForeignKeyConstraint)
{
tableEmitter.ConstraintsEmitter.AppendForeignKeyConstraintFromReference(tableNode, dimReference.ForeignKeyNameOverride, dimReference.Name, dimReference.Dimension);
}
}
foreach (AstTableKeyBaseNode keyBase in tableNode.Keys)
{
tableEmitter.ConstraintsEmitter.AppendConstraint(keyBase);
}
foreach (AstTableIndexNode index in tableNode.Indexes)
{
tableEmitter.ConstraintsEmitter.AppendIndex(tableNode.SchemaQualifiedName, index);
}
// TODO: Fix this null parent node
var createTableExecuteSqlTaskNode = new AstExecuteSqlTaskNode(containerNode)
{
Name = StringManipulation.NameCleanerAndUniqifier(executeSqlTaskName),
ResultSet = ExecuteSqlResultSet.None,
Connection = tableNode.Connection,
ExecuteDuringDesignTime = executeDuringDesignTime
};
createTableExecuteSqlTaskNode.Query = new AstExecuteSqlQueryNode(createTableExecuteSqlTaskNode) { QueryType = QueryType.Standard, Body = tableEmitter.Emit() };
containerNode.Tasks.Add(createTableExecuteSqlTaskNode);
bool hasPermissions = false;
var permissionBuilder = new StringBuilder();
foreach (var permission in tableNode.Permissions)
{
hasPermissions = true;
permissionBuilder.AppendLine(PermissionsLowerer.ProcessPermission(tableNode, permission));
}
foreach (var column in tableNode.Columns)
{
foreach (var permission in column.Permissions)
{
hasPermissions = true;
permissionBuilder.AppendLine(PermissionsLowerer.ProcessPermission(column, permission));
}
}
if (hasPermissions)
{
var permissionsExecuteSqlTask = new AstExecuteSqlTaskNode(containerNode)
{
Name = "__SetPermissions",
Connection = tableNode.Connection,
};
permissionsExecuteSqlTask.Query = new AstExecuteSqlQueryNode(permissionsExecuteSqlTask)
{
Body = permissionBuilder.ToString(),
QueryType = QueryType.Standard
};
containerNode.Tasks.Add(permissionsExecuteSqlTask);
}
if (tableNode.CustomExtensions != null)
{
containerNode.Tasks.Add(tableNode.CustomExtensions);
}
foreach (var source in tableNode.Sources)
{
var staticSource = source as AstTableStaticSourceNode;
if (staticSource != null && staticSource.Rows.Count > 0)
{
if (staticSource.EmitMergePackage)
{
// TODO: This is nasty - we need a way to reference packages and emit paths at lowering time
var executeMergePackage = new AstExecutePackageTaskNode(containerNode);
executeMergePackage.Name = "__ExecuteMergePackage";
executeMergePackage.Package = staticSource.LoweredPackage;
containerNode.Tasks.Add(executeMergePackage);
}
else
{
containerNode.Tasks.Add(StaticSourcesLowerer.CreateInsertExecuteSql(staticSource, containerNode, tableNode));
}
}
}
}
public static string EmitInsertDefaultRowStatement(AstTableNode astTableNode)
{
string columnNames = FlattenStringList(EmitColumnList(astTableNode, true));
string columnDefaultValues = FlattenStringList(EmitDefaultValueList(astTableNode));
if (columnDefaultValues.Length > 0)
{
var insertTemplate = new TemplatePlatformEmitter("SimpleInsert", astTableNode.SchemaQualifiedName, columnNames, columnDefaultValues);
return insertTemplate.Emit();
}
MessageEngine.Trace(astTableNode, Severity.Error, "V0142", "No assignable columns detected on table {0}", astTableNode.Name);
throw new InvalidOperationException("No Assignable Columns Detected.");
}
private static void BuildInsertPath(AstTableNode targetTable, IFrameworkItem parentItem, List<AstTransformationNode> workflowFragment, AstDataflowOutputPathNode scdInsertPath)
{
var insertTransform = new AstDerivedColumnListNode(parentItem) { Name = Utility.NameCleanerAndUniqifier(targetTable.Name + "_InsertDerivedColumns") };
insertTransform.InputPath = new AstDataflowMappedInputPathNode(insertTransform) { OutputPath = scdInsertPath };
insertTransform.Columns.Add(new AstDerivedColumnNode(insertTransform)
{
Name = "_scdFrom",
Expression = "(DT_DBTIMESTAMP2,7)(@[System::StartTime])",
Scale = 7,
DerivedColumnType = ColumnType.DateTime2,
ReplaceExisting = false
});
insertTransform.Columns.Add(new AstDerivedColumnNode(insertTransform)
{
Name = "_scdTo",
Expression = "NULL(DT_DBTIMESTAMP2,7)",
Scale = 7,
DerivedColumnType = ColumnType.DateTime2,
ReplaceExisting = false
});
workflowFragment.Add(insertTransform);
var insertDestination = new AstDestinationNode(parentItem);
insertDestination.Table = targetTable;
insertDestination.DisableScd = true;
insertDestination.ValidateExternalMetadata = false;
insertDestination.Name = Utility.NameCleanerAndUniqifier(targetTable.Name + "_InsertDestination");
workflowFragment.Add(insertDestination);
}
public static string EmitSelectAllStatement(AstTableNode astTableNode, IEnumerable<string> columnNames)
{
var selectTemplate = new TemplatePlatformEmitter("SimpleSelect");
selectTemplate.Map("Table", astTableNode.SchemaQualifiedName);
selectTemplate.Map("Columns", FlattenStringList(columnNames));
return selectTemplate.Emit();
}
private static void BuildChangePath(AstTableNode targetTable, IFrameworkItem parentItem, List<AstTransformationNode> workflowFragment, AstDataflowOutputPathNode scdChangePath)
{
// Change Path should only be built if we actually have a column that takes a Type-1 Update
bool doUpdate = false;
foreach (var column in targetTable.Columns)
{
if (column.IsAssignable && column.ScdType == ScdType.Update && !column.IsAutoGenerated)
{
doUpdate = true;
break;
}
}
if (doUpdate)
{
var changeCommand = new AstOleDBCommandNode(parentItem)
{
Name = Utility.NameCleanerAndUniqifier(targetTable.Name + "_ChangeCommand"),
Connection = targetTable.Connection
};
changeCommand.Query = new AstTransformationMappedQueryNode(changeCommand);
int changeIndex = 0;
var changeSetColumnValueMappings = new List<TableColumnValueMapping>();
foreach (var column in targetTable.Columns)
{
if (column.IsAssignable && column.ScdType == ScdType.Update && !column.IsAutoGenerated)
{
changeCommand.Query.Mappings.Add(new AstDataflowColumnMappingNode(changeCommand) { SourceName = column.Name, TargetName = String.Format(CultureInfo.InvariantCulture, "Param_{0}", changeIndex++) });
changeSetColumnValueMappings.Add(new TableColumnValueMapping(column.Name, "?", MappingOperator.Assign));
}
}
var changeWhereColumnValueMappings = new List<TableColumnValueMapping>();
foreach (var keyColumn in targetTable.PreferredKey.Columns)
{
changeCommand.Query.Mappings.Add(new AstDataflowColumnMappingNode(changeCommand) { SourceName = keyColumn.Column.Name, TargetName = String.Format(CultureInfo.InvariantCulture, "Param_{0}", changeIndex++) });
changeWhereColumnValueMappings.Add(new TableColumnValueMapping(keyColumn.Column.Name, "?", MappingOperator.CompareEqual));
}
changeWhereColumnValueMappings.Add(new TableColumnValueMapping("_scdTo", "NULL", MappingOperator.CompareIs));
changeCommand.Query.Body = TableLowerer.EmitUpdateStatement(targetTable, changeSetColumnValueMappings, changeWhereColumnValueMappings);
changeCommand.ValidateExternalMetadata = false;
changeCommand.InputPath = new AstDataflowMappedInputPathNode(changeCommand) { OutputPath = scdChangePath };
workflowFragment.Add(changeCommand);
}
}
private static List<string> EmitColumnList(AstTableNode astTableNode, bool emitOnlyAssignable)
{
var columnNames = new List<string>();
foreach (AstTableColumnBaseNode column in astTableNode.Columns)
{
if (!emitOnlyAssignable || column.IsAssignable)
{
columnNames.Add(column.Name);
}
}
return columnNames;
}
private static void BindTableReference(AstTableNode tableNode, string refName, AstTableNode refTable, bool nullable, ColumnsTSqlEmitter columnsEmitter)
{
// TODO: This is wrong in general. We need to ensure that this is always a single column constraint
AstTableKeyBaseNode primaryKey = refTable.PreferredKey;
if (primaryKey == null)
{
MessageEngine.Trace(tableNode, Severity.Warning, "V0243", "Table {0} lacks a primary, identity, or unique key", tableNode.Name);
return;
}
// TODO: We currently support only a single primary key column. This should be fixed :)
foreach (AstTableKeyColumnNode keyCol in primaryKey.Columns)
{
string type = TSqlTypeTranslator.Translate(
keyCol.Column.ColumnType,
keyCol.Column.Length,
keyCol.Column.Precision,
keyCol.Column.Scale,
keyCol.Column.CustomType);
columnsEmitter.AddColumn(refName, type, false, 0, 0, nullable, keyCol.Column.Default, false, String.Empty);
}
}
// This needs its own emitter
private static void ProcessAstTableColumnBaseNode(AstTableNode tableNode, ColumnsTSqlEmitter columnsEmitter, AstTableColumnBaseNode columnBase)
{
var tableReference = columnBase as AstTableColumnTableReferenceNode;
var dimReference = columnBase as AstTableColumnDimensionReferenceNode;
var hashKey = columnBase as AstTableHashedKeyColumnNode;
if (hashKey != null)
{
var hashBytesBuilder = new StringBuilder();
foreach (AstTableKeyColumnNode keyColumn in hashKey.Constraint.Columns)
{
string expression = "+ HASHBYTES('SHA1',{0})";
switch (keyColumn.Column.ColumnType)
{
case ColumnType.AnsiString:
case ColumnType.AnsiStringFixedLength:
case ColumnType.String:
case ColumnType.StringFixedLength:
expression = String.Format(CultureInfo.InvariantCulture, expression, String.Format(CultureInfo.InvariantCulture, "UPPER(RTRIM(LTRIM([{0}])))", keyColumn.Column.Name));
break;
case ColumnType.Int16:
case ColumnType.Int32:
case ColumnType.Int64:
case ColumnType.UInt16:
case ColumnType.UInt32:
case ColumnType.UInt64:
expression = String.Format(CultureInfo.InvariantCulture, expression, String.Format(CultureInfo.InvariantCulture, "CONVERT(binary varying(64),{0})", keyColumn.Column.Name));
break;
default:
expression = String.Format(CultureInfo.InvariantCulture, expression, keyColumn.Column.Name);
break;
}
hashBytesBuilder.Append(expression);
}
string hashExpression = String.Format(CultureInfo.InvariantCulture, "(CONVERT(varbinary(32),HASHBYTES('SHA1',{0})))", hashBytesBuilder.ToString().Substring(1));
hashExpression = String.Format(CultureInfo.InvariantCulture, "{0} PERSISTED NOT NULL UNIQUE", hashExpression);
columnsEmitter.AddColumn(hashKey.Name, null, false, 0, 0, true, string.Empty, true, hashExpression);
}
else if (tableReference != null)
{
BindTableReference(tableNode, tableReference.Name, tableReference.Table, tableReference.IsNullable, columnsEmitter);
}
else if (dimReference != null)
{
BindTableReference(tableNode, dimReference.Name, dimReference.Dimension, dimReference.IsNullable, columnsEmitter);
}
else if (columnBase != null)
{
string type = TSqlTypeTranslator.Translate(columnBase.ColumnType, columnBase.Length, columnBase.Precision, columnBase.Scale, columnBase.CustomType);
bool identity = false;
int seed = 1;
int increment = 1;
foreach (AstTableKeyBaseNode keyBase in tableNode.Keys)
{
var identityNode = keyBase as AstTableIdentityNode;
if (identityNode != null)
{
foreach (AstTableKeyColumnNode keyColNode in identityNode.Columns)
{
if (keyColNode.Column.Name.Equals(columnBase.Name, StringComparison.OrdinalIgnoreCase))
{
identity = true;
seed = identityNode.Seed;
increment = identityNode.Increment;
}
}
}
}
columnsEmitter.AddColumn(columnBase.Name, type, identity, seed, increment, columnBase.IsNullable, columnBase.Default, columnBase.IsComputed, columnBase.Computed);
}
}
private static List<AstTransformationNode> CreateScdWorkflowFragment(AstTableNode targetTable, IFrameworkItem parentItem, AstDataflowOutputPathNode outputPath)
{
var workflowFragment = new List<AstTransformationNode>();
AstTableColumnBaseNode lateArrivingStatusColumn = targetTable.Columns.FirstOrDefault(item => item.Name == "_IsLate");
var scd = new AstSlowlyChangingDimensionNode(parentItem);
scd.Name = Utility.NameCleanerAndUniqifier(targetTable.Name + "_scd");
scd.Connection = targetTable.Connection;
scd.CurrentRowWhere = "[_scdFrom] IS NOT NULL AND [_scdFrom] IS NULL";
scd.EnableInferredMember = targetTable.LateArriving;
scd.FailOnFixedAttributeChange = true;
scd.FailOnLookupFailure = false;
scd.IncomingRowChangeType = 1;
scd.InferredMemberIndicator = lateArrivingStatusColumn;
// TODO:
foreach (var column in targetTable.Columns)
{
if (column.IsAssignable && !column.IsAutoGenerated)
{
ScdColumnMappingType mappingType;
switch (column.ScdType)
{
case ScdType.Error:
mappingType = ScdColumnMappingType.FixedAttribute;
break;
case ScdType.Historical:
mappingType = ScdColumnMappingType.HistoricalAttribute;
break;
case ScdType.Key:
mappingType = ScdColumnMappingType.Key;
break;
case ScdType.Other:
mappingType = ScdColumnMappingType.Other;
break;
case ScdType.Update:
mappingType = ScdColumnMappingType.ChangingAttribute;
break;
default:
mappingType = ScdColumnMappingType.Other;
break;
}
scd.Mappings.Add(new AstScdTypeColumnMappingNode(scd) { MappingType = mappingType, QueryColumnName = column.Name });
}
}
scd.Query = TableLowerer.EmitSelectAllStatement(targetTable);
if (outputPath != null)
{
scd.InputPath = new AstDataflowMappedInputPathNode(scd) { OutputPath = outputPath };
}
workflowFragment.Add(scd);
// Late Arriving Path
if (targetTable.LateArriving)
{
BuildLateArrivingPath(targetTable, parentItem, workflowFragment, scd.InferredMemberPath);
}
// Change Path
BuildChangePath(targetTable, parentItem, workflowFragment, scd.ChangingAttributePath);
// Historical Path
var historicalOutput = BuildHistoricalSubpath(targetTable, parentItem, workflowFragment, scd.HistoricalAttributePath);
// Union Historical and New Paths
var insertUnionAll = new AstUnionAllNode(parentItem) { Name = Utility.NameCleanerAndUniqifier(targetTable.Name + "_InsertUnionAll") };
insertUnionAll.InputPaths.Add(new AstDataflowMappedInputPathNode(insertUnionAll) { OutputPath = scd.NewPath });
insertUnionAll.InputPaths.Add(new AstDataflowMappedInputPathNode(insertUnionAll) { OutputPath = historicalOutput });
workflowFragment.Add(insertUnionAll);
// Insert Path
BuildInsertPath(targetTable, parentItem, workflowFragment, insertUnionAll.OutputPath);
return workflowFragment;
}
private static AstMergeTaskNode CreateMergeTask(AstTableNode table, AstStagingContainerTaskNode staging, AstTableCloneNode stagingCloneTable)
{
var merge = new AstMergeTaskNode(staging)
{
Name = Utility.NameCleanerAndUniqifier(table + "_SSMerge"),
SourceTable = stagingCloneTable,
TargetConstraint = stagingCloneTable.Table.PreferredKey,
UnspecifiedColumnDefaultUsageType = MergeColumnUsage.CompareUpdateInsert
};
// Detect identity keys, if so add an insert- merge-column-usage for said key
foreach (var columnRef in stagingCloneTable.Table.PreferredKey.Columns)
{
if (columnRef.Column.IsIdentityColumn)
{
merge.Columns.Add(new AstMergeColumnNode(merge) { ColumnName = columnRef.Column.Name, ColumnUsage = MergeColumnUsage.Insert });
}
}
return merge;
}
public static string EmitSelectAllStatement(AstTableNode astTableNode)
{
var columnNames = EmitColumnList(astTableNode, false);
return EmitSelectAllStatement(astTableNode, columnNames);
}
public static string EmitUpdateStatement(AstTableNode astTableNode, IEnumerable<TableColumnValueMapping> assignments, IEnumerable<TableColumnValueMapping> conditions)
{
var updateTemplate = new TemplatePlatformEmitter("SimpleUpdate");
updateTemplate.Map("Table", astTableNode.SchemaQualifiedName);
var assignmentStrings = new List<string>();
foreach (var assignmentPair in assignments)
{
assignmentStrings.Add(String.Format(CultureInfo.InvariantCulture, "{0} {1} {2}", assignmentPair.ColumnName, assignmentPair.OperatorString, assignmentPair.ColumnValue));
}
updateTemplate.Map("ColumnValuePairs", FlattenStringList(assignmentStrings));
var whereTemplate = new TemplatePlatformEmitter("SimpleWhere");
var whereStrings = new List<string>();
foreach (var wherePair in conditions)
{
whereStrings.Add(String.Format(CultureInfo.InvariantCulture, "{0} {1} {2}", wherePair.ColumnName, wherePair.OperatorString, wherePair.ColumnValue));
}
whereTemplate.Map("ColumnValuePairs", FlattenStringList(whereStrings, " AND "));
return String.Format(CultureInfo.InvariantCulture, "{0} {1}", updateTemplate.Emit(), whereTemplate.Emit());
}
private static List<string> EmitDefaultValueList(AstTableNode astTableNode)
{
var columnValues = new List<string>();
foreach (AstTableColumnBaseNode column in astTableNode.Columns)
{
if (column.IsAssignable)
{
if (!String.IsNullOrEmpty(column.Default))
{
columnValues.Add(column.Default);
}
else if (column.IsNullable)
{
columnValues.Add("NULL");
}
else
{
columnValues.Add(column.DefaultValue);
}
}
}
return columnValues;
}
private static AstDataflowOutputPathNode BuildHistoricalSubpath(AstTableNode targetTable, IFrameworkItem parentItem, List<AstTransformationNode> workflowFragment, AstDataflowOutputPathNode scdHistoricalPath)
{
var historicalTransform = new AstDerivedColumnListNode(parentItem);
historicalTransform.InputPath = new AstDataflowMappedInputPathNode(historicalTransform) { OutputPath = scdHistoricalPath };
historicalTransform.Name = Utility.NameCleanerAndUniqifier(targetTable.Name + "_HistoricalDerivedColumns");
historicalTransform.Columns.Add(new AstDerivedColumnNode(historicalTransform)
{
Name = "_scdTo",
Expression = "(DT_DBTIMESTAMP2,7)(@[System::StartTime])",
Scale = 7,
DerivedColumnType = ColumnType.DateTime2,
ReplaceExisting = false
});
workflowFragment.Add(historicalTransform);
var historicalCommand = new AstOleDBCommandNode(parentItem)
{
Name = Utility.NameCleanerAndUniqifier(targetTable.Name + "_HistoricalCommand"),
Connection = targetTable.Connection
};
historicalCommand.Query = new AstTransformationMappedQueryNode(historicalCommand);
var historicalSetColumnValueMappings = new List<TableColumnValueMapping>();
var historicalWhereColumnValueMappings = new List<TableColumnValueMapping>();
historicalCommand.Query.Mappings.Add(new AstDataflowColumnMappingNode(historicalCommand) { SourceName = "_scdTo", TargetName = "Param_0" });
historicalSetColumnValueMappings.Add(new TableColumnValueMapping("_scdTo", "?", MappingOperator.Assign));
historicalWhereColumnValueMappings.Add(new TableColumnValueMapping("_scdTo", "NULL", MappingOperator.CompareIs));
int historicalIndex = 1;
foreach (var keyColumn in targetTable.PreferredKey.Columns)
{
historicalCommand.Query.Mappings.Add(new AstDataflowColumnMappingNode(historicalCommand) { SourceName = keyColumn.Column.Name, TargetName = String.Format(CultureInfo.InvariantCulture, "Param_{0}", historicalIndex++) });
historicalWhereColumnValueMappings.Add(new TableColumnValueMapping(keyColumn.Column.Name, "?", MappingOperator.CompareEqual));
}
historicalCommand.Query.Body = TableLowerer.EmitUpdateStatement(targetTable, historicalSetColumnValueMappings, historicalWhereColumnValueMappings);
historicalCommand.ValidateExternalMetadata = false;
workflowFragment.Add(historicalCommand);
return historicalCommand.OutputPath;
}