public void AppendForeignKeyConstraintFromReference(AST.Table.AstTableNode table, string refNameOverride, string refName, AST.Table.AstTableNode refTable) { TemplatePlatformEmitter tpe = new TemplatePlatformEmitter("ForeignKeyConstraintTemplate"); tpe.Map("TableName", table.SchemaQualifiedName); string constraintName; if (!String.IsNullOrEmpty(refNameOverride)) { constraintName = String.Format(CultureInfo.InvariantCulture, "[{0}]", refNameOverride); } else { constraintName = String.Format( CultureInfo.InvariantCulture, "[FK_{0}_{1}_{2}_{3}]", table.Name, refName, refTable.Name, refTable.PreferredKey.Columns[0].Column.Name); } if (constraintName.Length >= 128) { constraintName = String.Format(CultureInfo.InvariantCulture,"[{0}]",constraintName.Substring(0, 127)); } tpe.Map("ConstraintName", constraintName); tpe.Map("Column", refName); tpe.Map("ForeignKeyTable", refTable.SchemaQualifiedName); tpe.Map("ForeignKeyColumn", refTable.PreferredKey.Columns[0].Column.Name); _foreignKeyBuilder.Append(tpe.Emit()); _foreignKeyBuilder.Append("\n\n"); }
public string Emit() { var tpe = new TemplatePlatformEmitter("StoredProc"); tpe.Map("Name", _name); tpe.Map("Columns", _columnsBuilder.ToString()); tpe.Map("Body", _body); return tpe.Emit(); }
public StaticSourceTSqlEmitter(AstTableNode table) { _table = table; _defaultValueBuilder = new StringBuilder(); _defaultValueEmitter = new TemplatePlatformEmitter("SimpleInsert"); _columnNames = new List<string>(); _defaultValues = new List<string>(); }
private void AppendConstraintBase(AST.Table.AstTableKeyBaseNode constraint, string primaryKeyString, string unique) { string clustered = constraint.Clustered ? "CLUSTERED" : "NONCLUSTERED"; string ignoreDupKey = constraint.IgnoreDupKey ? "IGNORE_DUP_KEY = ON" : "IGNORE_DUP_KEY = OFF"; string padIndex = constraint.PadIndex ? "PAD_INDEX = ON" : "PAD_INDEX = OFF"; string keys = BuildKeys(constraint); var te = new TemplatePlatformEmitter("ConstraintTemplate", String.Format(CultureInfo.InvariantCulture,"[{0}]",constraint.Name), unique + clustered, keys, "WITH(" + padIndex + "," + ignoreDupKey + ")", primaryKeyString); _constraintKeyBuilder.Append("," + te.Emit()); _constraintKeyBuilder.AppendFormat(CultureInfo.InvariantCulture, "\n"); }
// TODO: We've made the call to just use the staging node as the lowered container rather than creating a new one and copying everything over public static void ProcessContainers(SymbolTable symbolTable) { var snapshotSymbolTable = new List<IReferenceableItem>(symbolTable); foreach (var astNamedNode in snapshotSymbolTable) { var stagingNode = astNamedNode as AstStagingContainerTaskNode; if (stagingNode != null && astNamedNode.FirstThisOrParent<ITemplate>() == null) { var stagingCreateContainer = new AstContainerTaskNode(stagingNode) { Name = String.Format(CultureInfo.InvariantCulture, Properties.Resources.CreateStaging, stagingNode.Name), Log = false, }; var stagingDropContainer = new AstContainerTaskNode(stagingNode) { Name = String.Format(CultureInfo.InvariantCulture, Properties.Resources.DropStaging, stagingNode.Name), Log = false, }; stagingNode.Tasks.Insert(0, stagingCreateContainer); stagingNode.Tasks.Add(stagingDropContainer); foreach (var baseTable in stagingNode.Tables) { var table = baseTable as AstTableNode; if (table != null) { TableLowerer.LowerTable( stagingCreateContainer, table, String.Format(CultureInfo.InvariantCulture, Properties.Resources.CreateStagingTable, table.Name), stagingNode.ExecuteDuringDesignTime); var dropStagingTemplate = new TemplatePlatformEmitter("DropStagingTable", table.SchemaQualifiedName); var dropTableExecuteSqlTask = new AstExecuteSqlTaskNode(stagingNode) { Name = StringManipulation.NameCleanerAndUniqifier(String.Format(CultureInfo.InvariantCulture, Properties.Resources.DropStagingTable, table.Name)), Connection = table.Connection, ExecuteDuringDesignTime = stagingNode.ExecuteDuringDesignTime, }; dropTableExecuteSqlTask.Query = new AstExecuteSqlQueryNode(dropTableExecuteSqlTask) { QueryType = QueryType.Standard, Body = dropStagingTemplate.Emit() }; stagingDropContainer.Tasks.Add(dropTableExecuteSqlTask); } else { throw new System.NotSupportedException("AstLowering - StagingContainer - a Table Template node was found when lowering staging containers and I don't know what to do with it."); } } } } }
public string Emit() { var tableBuilder = new StringBuilder(); var tpe = new TemplatePlatformEmitter("CreateTable"); tpe.Map("Name", _name); tpe.Map("CompressionType", _compressionType); tpe.Map("Columns", ColumnsEmitter.ColumnsDdl); tpe.Map("Constraints", ConstraintsEmitter.CKConstraints); tableBuilder.AppendLine(tpe.Emit()); tableBuilder.AppendLine(ConstraintsEmitter.ForeignConstraints); tableBuilder.AppendLine(_constraintEmitter.Indexes); return tableBuilder.ToString(); }
public string Emit() { var tableBuilder = new StringBuilder(); var tpe = new TemplatePlatformEmitter("CreateTable"); tpe.Map("Name", _name); tpe.Map("CompressionType", _compressionType); tpe.Map("Columns", ColumnsEmitter.ColumnsDdl); tpe.Map("Constraints", ConstraintsEmitter.CKConstraints); tableBuilder.AppendLine(tpe.Emit()); tableBuilder.AppendLine(ConstraintsEmitter.ForeignConstraints); tableBuilder.AppendLine(_constraintEmitter.Indexes); return(tableBuilder.ToString()); }
public void AppendIndex(string tableName, AST.Table.AstTableIndexNode index) { string unique = index.Unique ? "UNIQUE" : String.Empty; string clustered = index.Clustered ? "CLUSTERED" : "NONCLUSTERED"; string dropExisting = index.DropExisting ? "DROP_EXISTING = ON" : "DROP_EXISTING = OFF"; string ignoreDupKey = index.IgnoreDupKey ? "IGNORE_DUP_KEY = ON" : "IGNORE_DUP_KEY = OFF"; string online = index.Online ? "ONLINE = ON" : "ONLINE = OFF"; string padIndex = index.Online ? "PAD_INDEX = ON" : "PAD_INDEX = OFF"; string sortInTempdb = index.SortInTempDB ? "SORT_IN_TEMPDB = ON" : "SORT_IN_TEMPDB = OFF"; string properties = string.Format(CultureInfo.InvariantCulture, "{0},\n{1},\n{2},\n{3},\n{4}", padIndex, sortInTempdb, dropExisting, ignoreDupKey, online); string keys = BuildKeys(index.Columns); var te = new TemplatePlatformEmitter("CreateIndex", unique, clustered, String.Format(CultureInfo.InvariantCulture, "[{0}]", index.Name), tableName, keys, properties, string.Empty); _indexBuilder.Append(te.Emit()); _indexBuilder.Append("\n"); }
public static void ProcessPrincipals(AstRootNode astRootNode) { if (astRootNode.Principals.Count > 0) { var package = new AstPackageNode(astRootNode) { Name = "PrincipalsInitializer", Emit = true, PackageType = "Principal" }; astRootNode.Packages.Add(package); foreach (AstPrincipalNode principal in astRootNode.Principals) { TemplatePlatformEmitter principalTemplate; switch (principal.PrincipalType) { case PrincipalType.ApplicationRole: principalTemplate = new TemplatePlatformEmitter("CreatePrincipalApplicationRole", principal.Name); break; case PrincipalType.DBRole: principalTemplate = new TemplatePlatformEmitter("CreatePrincipalDatabaseRole", principal.Name); break; case PrincipalType.SqlUser: principalTemplate = new TemplatePlatformEmitter("CreatePrincipalSqlUser", principal.Name); break; case PrincipalType.WindowsGroup: principalTemplate = new TemplatePlatformEmitter("CreatePrincipalWindowsUser", principal.Name); break; case PrincipalType.WindowsUser: principalTemplate = new TemplatePlatformEmitter("CreatePrincipalWindowsUser", principal.Name); break; default: MessageEngine.Trace(principal, Severity.Error, "V0139", "Unknown Principal Type {0} in principal {1}", principal.PrincipalType.ToString(), principal.Name); return; } var executeSqlTask = new AstExecuteSqlTaskNode(package) { Name = Utility.NameCleanerAndUniqifier(String.Format(CultureInfo.InvariantCulture, "PrincipalConfig_{0}", principal.Name)), Connection = principal.Connection }; executeSqlTask.Query = new AstExecuteSqlQueryNode(executeSqlTask) { Body = principalTemplate.Emit(), QueryType = QueryType.Standard }; package.Tasks.Add(executeSqlTask); } } }
// TODO: Is this the right approach for events and precedence constraints? Should we have a utility method to handle them? // TODO: It would be good to find an approach to unify permissions and move some of this under the securables lowerer. public static void ProcessStoredProcedure(AstStoredProcNode storedProcNode) { var executeSqlNode = new AstExecuteSqlTaskNode(storedProcNode.ParentItem) { Name = storedProcNode.Name, ExecuteDuringDesignTime = storedProcNode.ExecuteDuringDesignTime, Connection = storedProcNode.Connection, ResultSet = ExecuteSqlResultSet.None }; executeSqlNode.Query = new AstExecuteSqlQueryNode(executeSqlNode) { QueryType = QueryType.Standard }; var queryBuilder = new StringBuilder(new StoredProcTSqlEmitter(storedProcNode).Emit()); foreach (var permission in storedProcNode.Permissions) { var template = new TemplatePlatformEmitter("CreateStoredProcedurePermission", permission.Action.ToString(), permission.Target.ToString(), storedProcNode.Name, permission.Principal.Name); queryBuilder.AppendLine(template.Emit()); } executeSqlNode.Query.Body = queryBuilder.ToString(); executeSqlNode.PrecedenceConstraints = storedProcNode.PrecedenceConstraints; if (executeSqlNode.PrecedenceConstraints != null) { executeSqlNode.PrecedenceConstraints.ParentItem = executeSqlNode; } foreach (var eventHandler in storedProcNode.Events) { executeSqlNode.Events.Add(eventHandler); eventHandler.ParentItem = executeSqlNode; } var parentContainer = storedProcNode.ParentItem as AstContainerTaskNode; if (parentContainer != null) { parentContainer.Tasks.Replace(storedProcNode, executeSqlNode); } }
public string Emit() { // BUGBUG - Currently ColumnName is a string in AstMergeColumnNode. I dont want to change the binding as it would impact // the 2.0 to 2.5 customer ports since it would require a qualified name. var columnUsageMapping = new Dictionary <string, AST.Task.MergeColumnUsage>(); foreach (AST.Task.AstMergeColumnNode mergeColumn in _mergeTask.Columns) { columnUsageMapping[mergeColumn.ColumnName.ToUpperInvariant()] = mergeColumn.ColumnUsage; } var targetTable = (AST.Table.AstTableNode)_mergeTask.TargetConstraint.ParentItem; var joinBuilder = new StringBuilder(); var notEqualBuilder = new StringBuilder(); var updateBuilder = new StringBuilder(); var insertParamBuilder = new StringBuilder(); var insertValueBuilder = new StringBuilder(); bool hasIdentityColumn = false; 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.AppendLine("\nAND"); } } foreach (AST.Table.AstTableColumnBaseNode column in targetTable.Columns) { if (!columnUsageMapping.ContainsKey(column.Name.ToUpperInvariant())) { // - Identity Columns, unless explicitly handled in the Usage Mapping, should be excluded from merge. if (column.IsIdentityColumn) { columnUsageMapping[column.Name.ToUpperInvariant()] = AST.Task.MergeColumnUsage.Exclude; } else { columnUsageMapping[column.Name.ToUpperInvariant()] = _mergeTask.UnspecifiedColumnDefaultUsageType; } } if (column.IsIdentityColumn) { switch (columnUsageMapping[column.Name.ToUpperInvariant()]) { case MergeColumnUsage.CompareInsert: hasIdentityColumn = true; break; case MergeColumnUsage.CompareUpdateInsert: hasIdentityColumn = true; break; case MergeColumnUsage.Insert: hasIdentityColumn = true; break; case MergeColumnUsage.UpdateInsert: hasIdentityColumn = true; break; default: break; } } if (column.IsAssignable || column.IsIdentityColumn) { if (columnUsageMapping.ContainsKey(column.ToString().ToUpperInvariant())) { ApplyColumnUsageMapping(columnUsageMapping, column, notEqualBuilder, updateBuilder, insertParamBuilder, insertValueBuilder); } else { MessageEngine.Trace(_mergeTask, AstFramework.Severity.Error, "SSISMT001", "Column {0} does not exist in the target table and could not be mapped.", column.Name); } } } // Complete NotEqualBuilder string notEqualString = notEqualBuilder.Length > 0 ? String.Format(CultureInfo.InvariantCulture, "\nAND\n(\n{0}\n)", notEqualBuilder.ToString()) : string.Empty; string queryHints = _mergeTask.TableLock ? "WITH(TABLOCK)" : String.Empty; TemplatePlatformEmitter te; if (updateTemplateRequired) { te = new TemplatePlatformEmitter("Merge", _mergeTask.SourceTable.SchemaQualifiedName, targetTable.SchemaQualifiedName, joinBuilder.ToString(), notEqualString, updateBuilder.ToString(), insertParamBuilder.ToString(), insertValueBuilder.ToString(), queryHints); } else { te = new TemplatePlatformEmitter("MergeWithoutUpdate", _mergeTask.SourceTable.SchemaQualifiedName, targetTable.SchemaQualifiedName, joinBuilder.ToString(), insertParamBuilder.ToString(), insertValueBuilder.ToString(), queryHints); } var finalBuilder = new StringBuilder(); if (hasIdentityColumn) { finalBuilder.AppendFormat("\nSET IDENTITY_INSERT {0} ON\n", targetTable.SchemaQualifiedName); } finalBuilder.AppendLine(te.Emit()); if (hasIdentityColumn) { finalBuilder.AppendFormat("\nSET IDENTITY_INSERT {0} OFF\n", targetTable.SchemaQualifiedName); } return(finalBuilder.ToString()); }
public string Emit() { // BUGBUG - Currently ColumnName is a string in AstMergeColumnNode. I dont want to change the binding as it would impact // the 2.0 to 2.5 customer ports since it would require a qualified name. var columnUsageMapping = new Dictionary<string, AST.Task.MergeColumnUsage>(); foreach (AST.Task.AstMergeColumnNode mergeColumn in _mergeTask.Columns) { columnUsageMapping[mergeColumn.ColumnName.ToUpperInvariant()] = mergeColumn.ColumnUsage; } var targetTable = (AST.Table.AstTableNode)_mergeTask.TargetConstraint.ParentItem; var joinBuilder = new StringBuilder(); var notEqualBuilder = new StringBuilder(); var updateBuilder = new StringBuilder(); var insertParamBuilder = new StringBuilder(); var insertValueBuilder = new StringBuilder(); bool hasIdentityColumn = false; 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.AppendLine("\nAND"); } } foreach (AST.Table.AstTableColumnBaseNode column in targetTable.Columns) { if (!columnUsageMapping.ContainsKey(column.Name.ToUpperInvariant())) { // - Identity Columns, unless explicitly handled in the Usage Mapping, should be excluded from merge. if (column.IsIdentityColumn) { columnUsageMapping[column.Name.ToUpperInvariant()] = AST.Task.MergeColumnUsage.Exclude; } else { columnUsageMapping[column.Name.ToUpperInvariant()] = _mergeTask.UnspecifiedColumnDefaultUsageType; } } if (column.IsIdentityColumn) { switch (columnUsageMapping[column.Name.ToUpperInvariant()]) { case MergeColumnUsage.CompareInsert: hasIdentityColumn = true; break; case MergeColumnUsage.CompareUpdateInsert: hasIdentityColumn = true; break; case MergeColumnUsage.Insert: hasIdentityColumn = true; break; case MergeColumnUsage.UpdateInsert: hasIdentityColumn = true; break; default: break; } } if (column.IsAssignable || column.IsIdentityColumn) { if (columnUsageMapping.ContainsKey(column.ToString().ToUpperInvariant())) { ApplyColumnUsageMapping(columnUsageMapping, column, notEqualBuilder, updateBuilder, insertParamBuilder, insertValueBuilder); } else { MessageEngine.Trace(_mergeTask, AstFramework.Severity.Error, "SSISMT001", "Column {0} does not exist in the target table and could not be mapped.", column.Name); } } } // Complete NotEqualBuilder string notEqualString = notEqualBuilder.Length > 0 ? String.Format(CultureInfo.InvariantCulture, "\nAND\n(\n{0}\n)", notEqualBuilder.ToString()) : string.Empty; string queryHints = _mergeTask.TableLock ? "WITH(TABLOCK)" : String.Empty; TemplatePlatformEmitter te; if (updateTemplateRequired) { te = new TemplatePlatformEmitter("Merge", _mergeTask.SourceTable.SchemaQualifiedName, targetTable.SchemaQualifiedName, joinBuilder.ToString(), notEqualString, updateBuilder.ToString(), insertParamBuilder.ToString(), insertValueBuilder.ToString(),queryHints); } else { te = new TemplatePlatformEmitter("MergeWithoutUpdate", _mergeTask.SourceTable.SchemaQualifiedName, targetTable.SchemaQualifiedName, joinBuilder.ToString(), insertParamBuilder.ToString(), insertValueBuilder.ToString(),queryHints); } var finalBuilder = new StringBuilder(); if (hasIdentityColumn) { finalBuilder.AppendFormat("\nSET IDENTITY_INSERT {0} ON\n", targetTable.SchemaQualifiedName); } finalBuilder.AppendLine(te.Emit()); if (hasIdentityColumn) { finalBuilder.AppendFormat("\nSET IDENTITY_INSERT {0} OFF\n", targetTable.SchemaQualifiedName); } return finalBuilder.ToString(); }
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."); }
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()); }
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 void AppendIndex(string tableName, AST.Table.AstTableIndexNode index) { string unique = index.Unique ? "UNIQUE" : String.Empty; string clustered = index.Clustered ? "CLUSTERED" : "NONCLUSTERED"; string dropExisting = index.DropExisting ? "DROP_EXISTING = ON" : "DROP_EXISTING = OFF"; string ignoreDupKey = index.IgnoreDupKey ? "IGNORE_DUP_KEY = ON" : "IGNORE_DUP_KEY = OFF"; string online = index.Online ? "ONLINE = ON" : "ONLINE = OFF"; string padIndex = index.Online ? "PAD_INDEX = ON" : "PAD_INDEX = OFF"; string sortInTempdb = index.SortInTempDB ? "SORT_IN_TEMPDB = ON" : "SORT_IN_TEMPDB = OFF"; string properties = string.Format(CultureInfo.InvariantCulture, "{0},\n{1},\n{2},\n{3},\n{4}", padIndex, sortInTempdb, dropExisting, ignoreDupKey, online); string keys = BuildKeys(index.Columns); var te = new TemplatePlatformEmitter("CreateIndex", unique, clustered, String.Format(CultureInfo.InvariantCulture,"[{0}]",index.Name), tableName, keys, properties, string.Empty); _indexBuilder.Append(te.Emit()); _indexBuilder.Append("\n"); }