protected void OnRemoveBaseTableColumn(AstTableColumnBaseNode column) { if (_baseTableColumnsToCloneColumns.ContainsKey(column)) { Columns.Remove(_baseTableColumnsToCloneColumns[column]); _baseTableColumnsToCloneColumns.Remove(column); } }
private void AppendUpdate(AST.Table.AstTableColumnBaseNode column, StringBuilder updateBuilder) { updateTemplateRequired = true; if (updateBuilder.Length > 0) { updateBuilder.Append(","); } updateBuilder.AppendFormat(CultureInfo.InvariantCulture, "TARGET.[{0}] = SOURCE.[{0}]", column.Name); }
private void AppendInsertValue(AST.Table.AstTableColumnBaseNode column, StringBuilder insertParamBuilder, StringBuilder insertValueBuilder) { if (insertParamBuilder.Length > 0) { insertParamBuilder.AppendFormat(CultureInfo.InvariantCulture, ",\n"); } insertParamBuilder.AppendFormat(CultureInfo.InvariantCulture, "[{0}]", column.Name); if (insertValueBuilder.Length > 0) { insertValueBuilder.AppendFormat(CultureInfo.InvariantCulture, ",\n"); } insertValueBuilder.AppendFormat(CultureInfo.InvariantCulture, "SOURCE.[{0}]", column.Name); }
private void AppendNotEqual(AST.Table.AstTableColumnBaseNode column, StringBuilder notEqualBuilder) { if (notEqualBuilder.Length > 0) { notEqualBuilder.AppendFormat(CultureInfo.InvariantCulture, "\nOR\n"); } // Bug #3757, special handling for uniqueidentifier data type if (column.CustomType != null && column.CustomType.Equals("uniqueidentifier", StringComparison.OrdinalIgnoreCase)) { 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); } }
protected void OnAddBaseTableColumn(AstTableColumnBaseNode column) { if (!(column is AstTableHashedKeyColumnNode)) { var clonedColumn = (AstTableColumnBaseNode)column.Clone(this); if (NullClonedColumns) { clonedColumn.IsNullable = true; } else { clonedColumn.IsNullable = column.IsNullable; } Columns.Add(clonedColumn); _baseTableColumnsToCloneColumns.Add(column, clonedColumn); } }
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); } }