public static string getColumnSql(AstTableColumnBaseNode column)
{
string returnValue = null;
returnValue = ",[" + column.Name + "] " + getBiml2DBType(convertDBType2Biml(column.DataType.ToString()));
if (column.Length > 0)
{
if (column.DataType == DbType.String)
{
returnValue += " (" + column.Length + ")";
}
else if (column.DataType == DbType.AnsiString)
{
returnValue += " (" + column.Length + ")";
}
else if (column.DataType == DbType.StringFixedLength)
{
returnValue += " (" + column.Length + ")";
}
else if (column.DataType == DbType.AnsiStringFixedLength)
{
returnValue += " (" + column.Length + ")";
}
}
else if (column.Length < 0)
{
if (column.DataType == DbType.String || column.DataType == DbType.AnsiString)
{
returnValue += " (max)";
}
}
else if (column.DataType == DbType.Decimal)
{
returnValue += " (" + column.Precision + "," + column.Scale + ")";
}
if (column.IsNullable)
{
returnValue += " null";
}
else
{
returnValue += " not null";
}
return(returnValue);
}
private void ApplyColumnUsageMapping(Dictionary <string, MergeColumnUsage> columnUsageMapping, AstTableColumnBaseNode column, StringBuilder notEqualBuilder, StringBuilder updateBuilder, StringBuilder insertParamBuilder, StringBuilder insertValueBuilder)
{
var usage = columnUsageMapping[column.ToString().ToUpperInvariant()];
bool compare = false;
bool update = false;
bool insert = false;
switch (usage)
{
// Explicit truth table for safety
case VulcanEngine.IR.Ast.Task.MergeColumnUsage.Compare:
compare = true;
update = false;
insert = false;
break;
case VulcanEngine.IR.Ast.Task.MergeColumnUsage.CompareInsert:
compare = true;
update = false;
insert = true;
break;
case VulcanEngine.IR.Ast.Task.MergeColumnUsage.CompareUpdate:
compare = true;
update = true;
insert = false;
break;
case VulcanEngine.IR.Ast.Task.MergeColumnUsage.CompareUpdateInsert:
compare = true;
update = true;
insert = true;
break;
case VulcanEngine.IR.Ast.Task.MergeColumnUsage.Insert:
compare = false;
update = false;
insert = true;
break;
case VulcanEngine.IR.Ast.Task.MergeColumnUsage.Update:
compare = false;
update = true;
insert = false;
break;
case VulcanEngine.IR.Ast.Task.MergeColumnUsage.UpdateInsert:
compare = false;
update = true;
insert = true;
break;
case VulcanEngine.IR.Ast.Task.MergeColumnUsage.Exclude:
compare = false;
update = false;
insert = false;
break;
default:
MessageEngine.Trace(_mergeTask, Severity.Error, "SSISMT02", "MergeColumnUsage: {0} is not supported.", usage);
break;
}
if (compare)
{
AppendNotEqual(column, notEqualBuilder);
}
if (update)
{
AppendUpdate(column, updateBuilder);
}
if (insert)
{
AppendInsertValue(column, insertParamBuilder, insertValueBuilder);
}
}
// 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);
}
