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; }