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 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 string Emit()
{
var tpe = new TemplatePlatformEmitter("StoredProc");
tpe.Map("Name", _name);
tpe.Map("Columns", _columnsBuilder.ToString());
tpe.Map("Body", _body);
return(tpe.Emit());
}
public void CompleteRow()
{
// TODO: Add validation logic here
_defaultValueEmitter.Map("Table", _table.SchemaQualifiedName);
_defaultValueEmitter.Map("Columns", FlattenStringList(_columnNames));
_defaultValueEmitter.Map("Values", FlattenStringList(_defaultValues));
_defaultValueBuilder.AppendLine(_defaultValueEmitter.Emit());
_columnNames.Clear();
_defaultValues.Clear();
}
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");
}
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 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");
}