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);
}
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 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 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());
}
public StaticSourceTSqlEmitter(AstTableNode table)
{
_table = table;
_defaultValueBuilder = new StringBuilder();
_defaultValueEmitter = new TemplatePlatformEmitter("SimpleInsert");
_columnNames = new List <string>();
_defaultValues = new List <string>();
}
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);
}
}
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);
}
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);
}
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);
}
private static void TopoVisit(AstTableNode node, List <AstTableNode> outputList, List <AstTableNode> visitedList)
{
if (!visitedList.Contains(node))
{
visitedList.Add(node);
foreach (var dependentTable in node.Columns.OfType <AstTableColumnTableReferenceNode>().Select(c => c.ForeignTable))
{
TopoVisit(dependentTable, outputList, visitedList);
}
outputList.Add(node);
}
}
public static string GetPackageType(AstTableNode tableNode)
{
if (tableNode is AstDimensionNode)
{
return("Dimension");
}
if (tableNode is AstFactNode)
{
return("FactTable");
}
return("Table");
}
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);
}
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 AstTableNode GetFileSchema()
{
//default the schema to "File"
AstSchemaNode astSchemaNode = new AstSchemaNode(null)
{
Name = "File",
ForceDisableIncrementalChangeTracking = true
};
AstTableNode astTableNode = new AstTableNode(null)
{
Name = this.Name,
Schema = astSchemaNode,
ForceDisableIncrementalChangeTracking = true
};
this.TableNode = astTableNode;
Interrogator i = new Interrogator();
List <DestinationColumn> DestinationObject = i.ProcessFile(
this.FilePath,
this.ColumnDelimiter,
this.FirstRowHeader,
this.HeaderRowsToSkip,
this.TextQualifier);
foreach (DestinationColumn col in DestinationObject)
{
AstTableColumnNode currentColumn = new AstTableColumnNode(astTableNode)
{
ForceDisableIncrementalChangeTracking = true
};
//set up the column
currentColumn.Name = col.Name;
if (col.MaxLength != null)
{
currentColumn.Length = int.Parse(col.MaxLength.ToString());
}
if (col.Precision != null)
{
currentColumn.Precision = int.Parse(col.Precision.ToString());
}
currentColumn.IsNullable = col.Nullable;
//add the column to the table
this.TableNode.Columns.Add(currentColumn);
}
return(this.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 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 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 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);
}
}
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);
}
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 AstTableNode ToAstTableNode(this DataTable dataTable)
{
var schema = new AstSchemaNode(null);
var table = new AstTableNode(null);
schema.Name = dataTable.Rows.OfType <DataRow>().Select(r => r["TABLE_SCHEMA"].ToString()).First();
table.Schema = schema;
table.Name = dataTable.Rows.OfType <DataRow>().Select(r => r["TABLE_NAME"].ToString()).First();
foreach (DataRow row in dataTable.Rows)
{
var column = new AstTableColumnNode(table)
{
Name = row["COLUMN_NAME"].ToString(),
DataType = row["DATA_TYPE"].ToString().GetDbType(),
IsNullable = Convert.ToBoolean(row["IS_NULLABLE"].ToString().Replace("YES", "true").Replace("NO", "false"))
};
if (!row.IsNull("CHARACTER_MAXIMUM_LENGTH"))
{
column.Length = Convert.ToInt32(row["CHARACTER_MAXIMUM_LENGTH"]);
}
if (!row.IsNull("NUMERIC_PRECISION"))
{
column.Precision = Convert.ToInt32(row["NUMERIC_PRECISION"]);
}
if (!row.IsNull("NUMERIC_SCALE"))
{
column.Precision = Convert.ToInt32(row["NUMERIC_SCALE"]);
}
table.Columns.Add(column);
}
return(table);
}
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);
}
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));
}
}
}
}
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);
}
public static string EmitSelectAllStatement(AstTableNode astTableNode)
{
var columnNames = EmitColumnList(astTableNode, false);
return(EmitSelectAllStatement(astTableNode, columnNames));
}
public string Emit(AstMergeTaskNode mergeTask)
{
StringBuilder joinBuilder = new StringBuilder();
for (int i = 0; i < mergeTask.TargetConstraint.Columns.Count; i++)
{
joinBuilder.AppendFormat("TARGET.{0} = SOURCE.{0}", mergeTask.TargetConstraint.Columns[i].Column.Name);
if (i < mergeTask.TargetConstraint.Columns.Count - 1)
{
joinBuilder.AppendFormat("\nAND\n");
}
}
StringBuilder notEqualBuilder = new StringBuilder();
StringBuilder updateBuilder = new StringBuilder();
StringBuilder insertParamBuilder = new StringBuilder();
StringBuilder insertValueBuilder = new StringBuilder();
AstTableNode targetTable = AstWalker.FirstParent <AstTableNode>(mergeTask.TargetConstraint);
Hashtable columnUsageMapping = new Hashtable();
foreach (AstTableColumnBaseNode column in targetTable.Columns)
{
if (column.IsAssignable)
{
foreach (AstMergeColumnNode mergeColumn in mergeTask.Columns)
{
if (mergeColumn.ColumnName.ToUpperInvariant() == column.Name.ToUpperInvariant())
{
columnUsageMapping.Add(column.Name, mergeColumn.ColumnUsage);
break;
}
}
if (!columnUsageMapping.ContainsKey(column.Name))
{
columnUsageMapping.Add(column.Name, mergeTask.UnspecifiedColumnDefaultUsageType);
}
}
}
bool firstNotEqual = true;
bool firstUpdate = true;
bool firstInsert = true;
foreach (AstTableColumnBaseNode column in targetTable.Columns)
{
if (column.IsAssignable)
{
if (columnUsageMapping[column.Name].ToString().ToUpperInvariant().Contains("COMPARE"))
{
if (firstNotEqual)
{
firstNotEqual = false;
}
else
{
notEqualBuilder.AppendFormat("\nOR\n");
}
// Bug #3757, special handling for uniqueidentifier data type
if (column.CustomType != null && column.CustomType.ToLowerInvariant().CompareTo("uniqueidentifier") == 0)
{
notEqualBuilder.AppendFormat("COALESCE(TARGET.[{0}],CONVERT(uniqueidentifier,'00000000-0000-0000-0000-000000000000')) <> COALESCE(SOURCE.[{0}],CONVERT(uniqueidentifier,'00000000-0000-0000-0000-000000000000'))", column.Name);
}
else
{
notEqualBuilder.AppendFormat("COALESCE(TARGET.[{0}],'') <> COALESCE(SOURCE.[{0}],'')", column.Name);
}
}
if (columnUsageMapping[column.Name].ToString().ToUpperInvariant().Contains("UPDATE"))
{
if (firstUpdate)
{
firstUpdate = false;
}
else
{
updateBuilder.AppendFormat(",");
}
updateBuilder.AppendFormat("TARGET.[{0}] = SOURCE.[{0}]", column.Name);
}
if (firstInsert)
{
firstInsert = false;
}
else
{
insertParamBuilder.AppendFormat(",\n");
insertValueBuilder.AppendFormat(",\n");
}
insertParamBuilder.AppendFormat("[{0}]", column.Name);
insertValueBuilder.AppendFormat("SOURCE.[{0}]", column.Name);
}
}
TemplatePlatformEmitter te;
if (mergeTask.UpdateTargetTable)
{
te = new TemplatePlatformEmitter("Merge", mergeTask.SourceName, targetTable.Name, joinBuilder.ToString(), notEqualBuilder.ToString(), updateBuilder.ToString(), insertParamBuilder.ToString(), insertValueBuilder.ToString());
}
else
{
te = new TemplatePlatformEmitter("MergeWithoutUpdate", mergeTask.SourceName, targetTable.Name, joinBuilder.ToString(), insertParamBuilder.ToString(), insertValueBuilder.ToString());
}
return(te.Emit(null));
/*
* <Template Name="Merge">
* <Map Source="Source" Index="0"/>
* <Map Source="Target" Index="1"/>
* <Map Source="Join" Index="2"/>
* <Map Source="NotEqualCheck" Index="3"/>
* <Map Source="Update" Index="4"/>
* <Map Source="Insert" Index="5"/>
* <TemplateData>
*/
}
// 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);
}
}