private void Append(ForeignKeyConstraint constraint)
{
string strConstraintName;
if (String.IsNullOrEmpty(constraint.Name) || !constraint.Name.StartsWith("FK_"))
{
strConstraintName = String.Format(System.Globalization.CultureInfo.InvariantCulture, "FK_{0}_{1}", constraint.Parent.Name, constraint.Name);
}
else
{
strConstraintName = constraint.Name;
}
if (strConstraintName.Length >= 128)
{
strConstraintName = strConstraintName.Substring(0, 127);
}
TemplatePlatformEmitter te = new TemplatePlatformEmitter(
"ForeignKeyConstraintTemplate",
constraint.Parent.Name,
strConstraintName,
new KeysBuilder(constraint.LocalColumnList).BuildForeignKeys(),
constraint.Table,
new KeysBuilder(constraint.ForeignColumnList).BuildForeignKeys()
);
_stringBuilder.Append(te.Emit(constraint));
_stringBuilder.Append(NEWLINE);
}
public string Emit(Table table)
{
string sConstraints = new ConstraintsBuilder(table).BuildConstraints();
TemplatePlatformEmitter te = new TemplatePlatformEmitter("CreateTable",table.Name,new ColumnsBuilder(table, sConstraints.Length > 0).Build(),sConstraints);
StringBuilder sTable = new StringBuilder(te.Emit(table));
sTable.Append(new ConstraintsBuilder(table).BuildForeignKeyConstraints());
sTable.Append(new IndexesBuilder(table.Indexes).Build());
sTable.Append(new InsertDefaultValuesEmitter().Emit(table));
return sTable.ToString();
}
public string Emit(string tableName, string columns, string where)
{
TemplatePlatformEmitter teSelect = new TemplatePlatformEmitter("SimpleSelect", columns, tableName);
StringBuilder sSelect = new StringBuilder(teSelect.Emit(null));
if (!string.IsNullOrEmpty(where))
{
TemplatePlatformEmitter teWhere = new TemplatePlatformEmitter("SimpleWhere", where);
sSelect.Append(teWhere.Emit(null));
}
return sSelect.ToString();
}
private void Append(string tableName, Index index)
{
string unique = index.Unique ? "UNIQUE" : "";
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 sProperties = string.Format(System.Globalization.CultureInfo.InvariantCulture, "{0},\n{1},\n{2},\n{3},\n{4}", padIndex, sortInTempdb, dropExisting, ignoreDupKey, online);
string kName;
string keys = new KeysBuilder(index.Keys).Build(index.Name, out kName);
TemplatePlatformEmitter te = new TemplatePlatformEmitter("CreateIndex", unique, clustered, kName, tableName, keys, sProperties, string.Empty);
_stringBuilder.Append(te.Emit(index));
_stringBuilder.Append(NEWLINE);
}
public string LogStart(SequenceTask logContainer, string packageName, string taskName, string varScopeXML)
{
TemplatePlatformEmitter te = new TemplatePlatformEmitter("LogStart", packageName, taskName, varScopeXML);
return(te.Emit(logContainer));
}
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>
*/
}
public static PhysicalTask.SequenceTask Lower(this AstTask.AstStagingContainerTaskNode astNode)
{
if (astNode.AsClassOnly)
{
return null;
}
try
{
TSQLEmitter.TemplatePlatformEmitter createStagingTemplate =
new Ssis2008Emitter.Emitters.TSQL.PlatformEmitter.TemplatePlatformEmitter("CreateStagingTable", astNode.Table.Name, astNode.CreateAs);
IR.TSQL.Table table = astNode.Table.Lower();
table.Indexes.IndexList.Clear();
//table.ConstraintList.Clear();
if (astNode.UseStaticSource == false)
{
table.DefaultValues.DefaultValueList.Clear();
}
foreach (string s in astNode.DropConstraints)
{
for (int i = 0; i < table.ConstraintList.Count; i++)
{
if (String.Compare(s.Trim(), table.ConstraintList[i].Name.Trim(), true) == 0)
{
table.ConstraintList.RemoveAt(i);
break;
}
}
}
foreach (IR.TSQL.Constraint constraint in table.ConstraintList)
{
constraint.Name = String.Format("{0}_{1}", astNode.CreateAs, constraint.Name);
}
table.ForeignKeyConstraintList.Clear();
table.Name = astNode.CreateAs;
table.ConnectionConfiguration = astNode.StagingConnection.Lower();
AstTask.AstExecuteSQLTaskNode createStagingTask = new VulcanEngine.IR.Ast.Task.AstExecuteSQLTaskNode();
createStagingTask.ParentASTNode = astNode;
createStagingTask.Connection = astNode.StagingConnection;
AddConnection(astNode.StagingConnection);
createStagingTask.ExecuteDuringDesignTime = true;
createStagingTask.Name = astNode.Name;
//createStagingTask.Type = VulcanEngine.IR.Ast.Task.ExecuteSQLTaskType.Expression;
createStagingTask.Type = VulcanEngine.IR.Ast.Task.ExecuteSQLTaskType.File;
//createStagingTask.Body = String.Format("\"{0}\"", createStagingTemplate.Emit(null));
createStagingTask.Body = new TSQLEmitter.TablePlatformEmitter().Emit(table);
TSQLEmitter.TemplatePlatformEmitter dropStagingTemplate =
new Ssis2008Emitter.Emitters.TSQL.PlatformEmitter.TemplatePlatformEmitter("DropStagingTable", astNode.CreateAs);
AstTask.AstExecuteSQLTaskNode truncateStagingTask = new VulcanEngine.IR.Ast.Task.AstExecuteSQLTaskNode();
truncateStagingTask.ParentASTNode = astNode;
truncateStagingTask.Connection = astNode.StagingConnection;
truncateStagingTask.ExecuteDuringDesignTime = false;
truncateStagingTask.Name = astNode.Name;
truncateStagingTask.Type = VulcanEngine.IR.Ast.Task.ExecuteSQLTaskType.Expression;
truncateStagingTask.Body = String.Format("\"{0}\"", dropStagingTemplate.Emit(null));
astNode.Tasks.Insert(0, createStagingTask);
astNode.Tasks.Add(truncateStagingTask);
return ((AstTask.AstContainerTaskNode)astNode).Lower();
}
catch (Exception e)
{
throw new SSISEmitterException(astNode, e);
}
}
public string Emit(StoredProc storedProc)
{
TemplatePlatformEmitter te = new TemplatePlatformEmitter("StoredProc", storedProc.Name, new StoredProcColumnsBuilder(storedProc.Columns).Build(), storedProc.Body);
return te.Emit(storedProc);
}
public string LogSetValue(SequenceTask logContainer, string logIDName, string pathVariable, string nameVariable, string value)
{
TemplatePlatformEmitter te = new TemplatePlatformEmitter("LogSetValue", logIDName, pathVariable, nameVariable, value);
return(te.Emit(logContainer));
}
public string Emit(StoredProc storedProc)
{
TemplatePlatformEmitter te = new TemplatePlatformEmitter("StoredProc", storedProc.Name, new StoredProcColumnsBuilder(storedProc.Columns).Build(), storedProc.Body);
return(te.Emit(storedProc));
}
public string LogEnd(SequenceTask logContainer, string logIDName)
{
TemplatePlatformEmitter te = new TemplatePlatformEmitter("LogEnd", logIDName);
return te.Emit(logContainer);
}
public string LogSetValue(SequenceTask logContainer, string logIDName, string pathVariable, string nameVariable, string value)
{
TemplatePlatformEmitter te = new TemplatePlatformEmitter("LogSetValue", logIDName, pathVariable, nameVariable, value);
return te.Emit(logContainer);
}
public string LogPrepareToSetValue(SequenceTask logContainer)
{
TemplatePlatformEmitter te = new TemplatePlatformEmitter("LogPrepareToSetValue");
return te.Emit(logContainer);
}
public string LogGetPredefinedValues(SequenceTask logContainer)
{
TemplatePlatformEmitter te = new TemplatePlatformEmitter("LogGetPredefinedValues");
return te.Emit(logContainer);
}
public string LogStart(SequenceTask logContainer, string packageName, string taskName, string varScopeXML)
{
TemplatePlatformEmitter te = new TemplatePlatformEmitter("LogStart", packageName, taskName, varScopeXML);
return te.Emit(logContainer);
}
public string LogGetPredefinedValues(SequenceTask logContainer)
{
TemplatePlatformEmitter te = new TemplatePlatformEmitter("LogGetPredefinedValues");
return(te.Emit(logContainer));
}
public string LogPrepareToSetValue(SequenceTask logContainer)
{
TemplatePlatformEmitter te = new TemplatePlatformEmitter("LogPrepareToSetValue");
return(te.Emit(logContainer));
}
private void AppendConstraintBase(IndexBase 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 kName;
string keys = new KeysBuilder(constraint.Keys).Build(constraint.Name, out kName);
TemplatePlatformEmitter te = new TemplatePlatformEmitter("ConstraintTemplate", kName, unique + clustered, keys, "WITH(" + padIndex + "," + ignoreDupKey + ")", primaryKeyString);
_stringBuilder.Append(te.Emit(constraint));
}
public string LogEnd(SequenceTask logContainer, string logIDName)
{
TemplatePlatformEmitter te = new TemplatePlatformEmitter("LogEnd", logIDName);
return(te.Emit(logContainer));
}
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>
*/
}
