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