private SqlDataType GetDataType(TSqlObject something)
{
TSqlObject dataType = something.GetReferenced(Column.DataType).SingleOrDefault();
if (dataType == null)
{
return SqlDataType.Unknown;
}
// Some data types don't cleanly convert
switch (dataType.Name.Parts.Last())
{
case "hierarchyid":
case "geometry":
case "geography":
return SqlDataType.Variant;
}
// Note: User Defined Data Types (UDDTs) are not supported during deployment of memory optimized tables.
// The code below handles UDDTs in order to show how properties of a UDDT should be accessed and because
// the model validation does not actually block this syntax at present there are tests that validate this behavior.
// User Defined Data Types and built in types are merged in the public model.
// We want to examine the built in type: for user defined data types this will be
// found by accessing the DataType.Type object, which will not exist for a built in type
TSqlObject builtInType = dataType.GetReferenced(DataType.Type).SingleOrDefault();
if (builtInType != null)
{
dataType = builtInType;
}
return dataType.GetProperty<SqlDataType>(DataType.SqlDataType);
}
private static bool IsIndexOnMemoryOptimizedTable(TSqlObject index)
{
TSqlObject targetTable = index.GetReferenced(Index.IndexedObject).SingleOrDefault();
return(targetTable != null &&
Table.TypeClass.Equals(targetTable.ObjectType) &&
targetTable.GetProperty <bool>(Table.MemoryOptimized));
}
/// <summary>
/// Get all columns from a table
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public static IEnumerable <TSqlObject> GetAllColumns(this TSqlObject table)
{
if (table == null)
{
throw new ArgumentNullException(nameof(table));
}
var columns = table.GetReferenced(Table.Columns);
return(columns);
}
public override IList <SqlRuleProblem> Analyze(SqlRuleExecutionContext ruleExecutionContext)
{
IList <SqlRuleProblem> problems = new List <SqlRuleProblem>();
TSqlObject modelElement = ruleExecutionContext.ModelElement;
string elementName = ruleExecutionContext.SchemaModel.DisplayServices.GetElementName(modelElement, ElementNameStyle.EscapedFullyQualifiedName);
RuleDescriptor ruleDescriptor = ruleExecutionContext.RuleDescriptor;
List <TSqlObject> columns = modelElement.GetReferenced(Table.Columns).ToList();
foreach (TSqlObject column in modelElement.GetReferenced(Table.Columns))
{
bool hasDescription = false;
// Check if it has an extended property
List <TSqlObject> extendedProperties = column.GetReferencing(ExtendedProperty.Host).ToList();
if (extendedProperties.Count > 0)
{
foreach (TSqlObject prop in extendedProperties)
{
if (ruleExecutionContext.SchemaModel.DisplayServices.GetElementName(prop, ElementNameStyle.SimpleName) == "MS_Description")
{
hasDescription = true;
break;
}
}
}
if (!hasDescription)
{
SqlRuleProblem problem = new SqlRuleProblem(
String.Format(
CultureInfo.CurrentCulture,
ruleDescriptor.DisplayDescription,
ruleExecutionContext.SchemaModel.DisplayServices.GetElementName(column, ElementNameStyle.EscapedFullyQualifiedName)),
column);
problems.Add(problem);
}
}
return(problems);
}
private void AddPropertiesForColumn(Panel panel, TSqlObject column)
{
var type = column.GetMetadata <ColumnType>(Column.ColumnType);
panel.Children.Add(GetPropertyLabel("Column MetaType: ", type == ColumnType.Column ? "Standard Column" : type.ToString()));
foreach (TSqlObject referenced in column.GetReferenced())
{
panel.Children.Add(GetPropertyLabel("Type: ", referenced.Name.ToString()));
}
}
protected override IEnumerable<SqlRuleProblem> OnAnalyze(string name, TSqlObject table)
{
//Get the columns of the table
var columns = table.GetReferenced(Table.Columns);
if (columns.Count() == 0)
yield return new SqlRuleProblem(string.Format("The link table {0} has no column", name), table);
//Ensure that one of them is effecively a DateId
var timeBasedColumn = columns.FirstOrDefault(i => i.Name.Parts.Last() == Configuration.TimeBased.Key);
if (timeBasedColumn == null)
yield return new SqlRuleProblem(string.Format("No column named '{0}' for link table {1}", Configuration.TimeBased.Key, name), table);
}
public static void ProcessTSqlObjectIntoDimensionScriptFiles(ParsedArgs parsedArgs, TSqlObject table, ModelRelationshipClass relationshipTypeColumns, ModelRelationshipClass relationshipTypeSchema)
{
string stagingSchemaName = GetSchemaName(table);
string templateDimCoreName = GetObjectName(table).Replace("_dimSrc_stg", "");
List <String> listOfColumns = new List <String>();
foreach (var col in table.GetReferenced(relationshipTypeColumns, DacQueryScopes.UserDefined))
{
String column = GetColumnName(col);
listOfColumns.Add(column);
}
GenerateDimension(listOfColumns, templateDimCoreName, parsedArgs, stagingSchemaName);
}
/// <summary>
/// Performs analysis and returns a list of problems detected
/// </summary>
/// <param name="ruleExecutionContext">Contains the schema model and model element to analyze</param>
/// <returns>
/// The problems detected by the rule in the given element
/// </returns>
public override IList <SqlRuleProblem> Analyze(SqlRuleExecutionContext ruleExecutionContext)
{
List <SqlRuleProblem> problems = new List <SqlRuleProblem>();
TSqlObject sqlObj = ruleExecutionContext.ModelElement;
if (sqlObj == null || sqlObj.IsWhiteListed())
{
return(problems);
}
var objName = sqlObj.Name.GetName();
var columns = sqlObj.GetReferenced(DacQueryScopes.All).Where(x => x.ObjectType == Column.TypeClass).ToList();
if (!columns.Any())
{
return(problems);
}
var keyColumn = columns.FirstOrDefault();
var dataType = keyColumn.GetReferenced(Column.DataType, DacQueryScopes.All).FirstOrDefault();
if (dataType == null || dataType.Name == null)
{
return(problems);
}
var dataTypeName = dataType.Name.Parts.Last();
if (_comparer.Equals(dataTypeName, "uniqueidentifier"))
{
problems.Add(new SqlRuleProblem(GuidMessage, sqlObj));
}
if (columns.Any(col =>
{
var len = col.GetProperty <int>(Column.Length);
dataTypeName = col.GetReferenced(Column.DataType).First().Name.Parts.Last();
return((_comparer.Equals(dataTypeName, "varchar") && len > 50) ||
(_comparer.Equals(dataTypeName, "nvarchar") && len > 100));
}))
{
problems.Add(new SqlRuleProblem(WideVarcharMessage, sqlObj));
}
return(problems);
}
private static ColumnInfo GetSchemaForColumn(TSqlObject model)
{
TSqlObject type = model.GetReferenced(Column.DataType).First();
string dataType = type.Name.Parts[0];
bool isNullable = model.GetProperty <bool>(Column.Nullable);
int length = model.GetProperty <int>(Column.Length);
return(new ColumnInfo
{
Name = model.Name.Parts[2],
FullName = model.Name.ToString(),
SqlDataType = dataType,
ClrType = GetTypeMapping(dataType, isNullable),
Nullable = isNullable,
Length = length
});
}
/// <summary>
/// Get primary key column(s) from a table
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public static IEnumerable <TSqlObject> GetPrimaryKeyColumns(this TSqlObject table)
{
if (table == null)
{
throw new ArgumentNullException(nameof(table));
}
TSqlObject pk = table.GetReferencing(PrimaryKeyConstraint.Host, DacQueryScopes.UserDefined).FirstOrDefault();
if (pk != null)
{
var columns = pk.GetReferenced(PrimaryKeyConstraint.Columns);
if (columns != null)
{
return(columns);
}
}
return(new TSqlObject[0]);
}
/// <summary>
/// For element-scoped rules the Analyze method is executed once for every matching object in the model.
/// </summary>
/// <param name="ruleExecutionContext">The context object contains the TSqlObject being analyzed, a TSqlFragment
/// that's the AST representation of the object, the current rule's descriptor, and a reference to the model being
/// analyzed.
/// </param>
/// <returns>A list of problems should be returned. These will be displayed in the Visual Studio error list</returns>
public override IList <SqlRuleProblem> Analyze(SqlRuleExecutionContext ruleExecutionContext)
{
IList <SqlRuleProblem> problems = new List <SqlRuleProblem>();
TSqlObject modelElement = ruleExecutionContext.ModelElement;
string elementName = ruleExecutionContext.SchemaModel.DisplayServices.GetElementName(modelElement, ElementNameStyle.EscapedFullyQualifiedName);
RuleDescriptor ruleDescriptor = ruleExecutionContext.RuleDescriptor;
bool hasDescription = false;
// Check if it has columns. Workaround for tables from referenced projects showing up here.
// Not interested in those. They should be checked in their own project.
List <TSqlObject> columns = modelElement.GetReferenced(Table.Columns).ToList();
if (columns.Count > 0)
{
// Check if it has an extended property
List <TSqlObject> extendedProperties = modelElement.GetReferencing(ExtendedProperty.Host).ToList();
if (extendedProperties.Count > 0)
{
foreach (TSqlObject prop in extendedProperties)
{
if (ruleExecutionContext.SchemaModel.DisplayServices.GetElementName(prop, ElementNameStyle.SimpleName) == "MS_Description")
{
hasDescription = true;
break;
}
}
}
if (!hasDescription)
{
SqlRuleProblem problem = new SqlRuleProblem(
String.Format(
CultureInfo.CurrentCulture,
ruleDescriptor.DisplayDescription,
elementName),
modelElement);
problems.Add(problem);
}
}
return(problems);
}
public DataTypeView(TSqlObject column)
{
if (column.ObjectType != Column.TypeClass)
{
throw new ArgumentException("Invalid object type for the parameter. Should be of type Column.", nameof(column));
}
Name = column.Name.Parts.Last();
Type = DataTypeViewType.Column;
var dataType = column.GetReferenced(Column.DataType).FirstOrDefault();
if (dataType != null)
{
var length = column.GetProperty <int>(Column.Length);
DataType = dataType.Name.Parts.First();
Length = length;
}
}
protected override IEnumerable<SqlRuleProblem> OnAnalyze(string name, TSqlObject table)
{
var indexes = table.GetReferencing(Index.IndexedObject);
var timeBasedIndexes = indexes.Where(i => i.GetReferenced(Index.Columns).First().Name.Parts.Last()
== Configuration.TimeBased.Key);
if (timeBasedIndexes.Count() == 0)
yield return new SqlRuleProblem(string.Format("No index where first column is '{0}' for link table {1}", Configuration.TimeBased.Key, name), table);
foreach (var tbIndex in timeBasedIndexes)
{
var unexpectedColumns = tbIndex.GetReferenced(Index.Columns).Where(c => c.Name.Parts.Last() != Configuration.TimeBased.Key);
if (unexpectedColumns.Count()>0)
{
yield return new SqlRuleProblem(
string.Format(
"The time-based index '{0}' for link table '{1}' contains additional columns. Unexpected column{2} '{3}'"
, tbIndex.Name
, name
, (unexpectedColumns.Count() == 1) ? " is " : "s are "
, string.Join("', '", unexpectedColumns.Select(c => c.Name.Parts.Last()))
), table);
}
var idColumns = table.GetReferenced(Table.Columns).Where(c => c.Name.Parts.Last() != Configuration.TimeBased.Key && c.Name.Parts.Last().EndsWith("Id"));
var includedColumns = tbIndex.GetReferenced(Index.IncludedColumns);
var missingColumns = idColumns.Except(includedColumns);
if (missingColumns.Count()>0)
{
yield return new SqlRuleProblem(
string.Format(
"The time-based index '{0}' for link table '{1}' doesn't include some Id columns. Missing column{2} '{3}'"
, tbIndex.Name
, name
, (missingColumns.Count() == 1) ? " is " : "s are "
, string.Join("', '", missingColumns.Select(c => c.Name.Parts.Last()))
), table);
}
}
}
/// <summary>
/// Get the data type and its size (if applicable) from a table column
/// </summary>
/// <param name="column"></param>
/// <returns></returns>
public static string GetColumnSqlDataType(this TSqlObject column, bool withLength = true)
{
if (column == null)
{
throw new ArgumentNullException(nameof(column));
}
SqlDataType sdt = column.GetReferenced(Column.DataType).First().GetProperty <SqlDataType>(DataType.SqlDataType);
if (withLength)
{
int length = column.GetProperty <int>(Column.Length);
bool isMax = column.GetProperty <bool>(Column.IsMax);
return(length == 0 && !isMax
? sdt.ToString().ToUpper()
: length == 0 ? sdt.ToString().ToUpper() + "(MAX)" : sdt.ToString().ToUpper() + "(" + length + ")");
}
else
{
return(sdt.ToString().ToUpper());
}
}
protected override IEnumerable<SqlRuleProblem> OnAnalyze(string name, TSqlObject table)
{
//Get the columns of the table
var columns = table.GetReferenced(Table.Columns);
if (columns.Count() == 0)
yield return new SqlRuleProblem(string.Format("The info table {0} has no column", name), table);
//Ensure that one of them is effecively an identity
var identityColumn = columns.FirstOrDefault(c => c.GetProperty<bool>(Column.IsIdentity));
if (identityColumn == null)
yield return new SqlRuleProblem(string.Format("No identity column for the info table {0}", name), table);
else
{
if (!string.IsNullOrEmpty(Configuration.Info.IdentityNamingConvention))
{
//Ensure that this column has correct naming convention
var actualIdentityColumnName = identityColumn.Name.Parts.Last();
var expectedIdentityColumnName = string.Format(Configuration.Info.IdentityNamingConvention, table.Name.Parts.Last());
if (string.Compare(actualIdentityColumnName, expectedIdentityColumnName, false) != 0)
yield return new SqlRuleProblem(string.Format("Identity column for the info table {0} doesn't follow the naming convention: '{1}' in place of '{2}'", name, actualIdentityColumnName, expectedIdentityColumnName), table);
}
}
}
private static TableInfo GetSchemaForTable(TSqlObject model)
{
TableInfo retVal = new TableInfo();
retVal.EntityName = _pluralizationService.Singularize(model.Name.Parts[1]);
retVal.ShortName = model.Name.Parts[1];
retVal.FullName = model.Name.ToString();
var columns = model.GetReferenced(Table.Columns).ToArray();
retVal.Columns = new ColumnInfo[columns.Length];
for (int i = 0; i < columns.Length; i++)
{
ColumnInfo column = GetSchemaForColumn(columns[i]);
retVal.Columns[i] = column;
if (columns[i].GetProperty <bool>(Column.IsIdentity))
{
retVal.IdentityColumn = column;
}
}
return(retVal);
}
private static ViewInfo GetSchemaForView(TSqlObject model)
{
ViewInfo retVal = new ViewInfo();
retVal.ShortName = model.Name.Parts[1];
retVal.FullName = model.Name.ToString();
var columns = model.GetReferenced(View.Columns).ToArray();
retVal.Columns = new ColumnInfo[columns.Length];
for (int i = 0; i < columns.Length; i++)
{
TSqlObject column = columns[i];
string dataType = "nvarchar";
bool isNullable = column.GetProperty <bool>(Column.Nullable);
int length = column.GetProperty <int>(Column.Length);
TSqlObject referencedColumn = column.GetReferenced().FirstOrDefault();
if (null != referencedColumn)
{
TSqlObject type = referencedColumn.GetReferenced(Column.DataType).First();
dataType = type.Name.Parts[0];
}
retVal.Columns[i] = new ColumnInfo
{
Name = column.Name.Parts[2],
FullName = column.Name.ToString(),
SqlDataType = dataType,
ClrType = GetTypeMapping(dataType, isNullable),
Nullable = isNullable,
Length = length
};
}
return(retVal);
}
public override void ExplicitVisit(ExecutableProcedureReference node)
{
// skip when unkown procedure. example: exec @pEventName @pParameter1, @pParameter2;
if (node.ProcedureReference.ProcedureReference == null)
{
return;
}
SchemaObjectName schemaObject = node.ProcedureReference.ProcedureReference.Name;
Identifier id = node.ProcedureReference.ProcedureReference.Name.SchemaIdentifier;
string schema;
if (schemaObject.SchemaIdentifier != null)
{
schema = schemaObject.SchemaIdentifier.Value;
}
else
{
// Assuming schema is dbo
schema = "dbo";
}
string procedure = node.ProcedureReference.ProcedureReference.Name.BaseIdentifier.Value;
TSqlObject procObject = model.GetObject(Procedure.TypeClass, new ObjectIdentifier(schema, procedure), DacQueryScopes.UserDefined);
if (procObject != null)
{
bool hasMissingMandatoryParams = false;
// Get the list of supplied parameters to match against.
IList <ExecuteParameter> suppliedParams = node.Parameters;
string missingParamString = "";
int paramIndex = 0;
// Get the parameters of the procedure
foreach (TSqlObject param in procObject.GetReferenced(Procedure.Parameters))
{
// Check if it has an extended property
object paramDefault = param.GetProperty(Parameter.DefaultExpression);
// Check if it is readonly
bool paramReadOnly = (bool)param.GetProperty(Parameter.ReadOnly);
if (paramDefault == null && !paramReadOnly)
{
// Check if we supplied it
bool suppliedMandatoryParam = false;
string paramName = param.Name.Parts[param.Name.Parts.Count - 1];
int paramsFound = 0;
foreach (ExecuteParameter suppliedParam in suppliedParams.ToList())
{
if (suppliedParam.Variable != null)
{
if (suppliedParam.Variable.Name == paramName)
{
suppliedMandatoryParam = true;
}
}
else
{
if (paramsFound == paramIndex)
{
suppliedMandatoryParam = true;
}
}
paramsFound++;
}
if (!suppliedMandatoryParam)
{
hasMissingMandatoryParams = true;
if (missingParamString == "")
{
missingParamString = paramName;
}
else
{
missingParamString += ", " + paramName;
}
}
}
paramIndex++;
}
if (hasMissingMandatoryParams)
{
ProcedureCalls.Add(node);
MissingParameters[node] = missingParamString;
}
}
}
public override IList <SqlRuleProblem> Analyze(SqlRuleExecutionContext ruleExecutionContext)
{
IList <SqlRuleProblem> problems = new List <SqlRuleProblem>();
TSqlObject modelElement = ruleExecutionContext.ModelElement;
string elementName = ruleExecutionContext.SchemaModel.DisplayServices.GetElementName(modelElement, ElementNameStyle.EscapedFullyQualifiedName);
RuleDescriptor ruleDescriptor = ruleExecutionContext.RuleDescriptor;
// Get columns of the foreign key
List <TSqlObject> fkColumns = modelElement.GetReferenced(ForeignKeyConstraint.Columns).ToList();
// Get table of the foreign key.
TSqlObject table = modelElement.GetReferenced(ForeignKeyConstraint.Host).SingleOrDefault();
bool foundMatch = false;
if (table != null)
{
// Check the indexes of the table
foreach (TSqlObject index in table.GetReferencing(Index.IndexedObject))
{
// Get columns of the index
List <TSqlObject> indexColumns = index.GetReferenced(Index.Columns).ToList();
foundMatch = CompareColumns(fkColumns, indexColumns, ruleExecutionContext);
if (foundMatch)
{
break;
}
}
if (!foundMatch)
{
// Check the primary keys as well
foreach (TSqlObject pk in table.GetReferencing(PrimaryKeyConstraint.Host))
{
// Get columns of the primary key
List <TSqlObject> pkColumns = pk.GetReferenced(PrimaryKeyConstraint.Columns).ToList();
foundMatch = CompareColumns(fkColumns, pkColumns, ruleExecutionContext);
if (foundMatch)
{
break;
}
}
}
if (!foundMatch)
{
// Check the unique constraints as well
foreach (TSqlObject uq in table.GetReferencing(UniqueConstraint.Host))
{
// Get columns of the primary key
List <TSqlObject> uqColumns = uq.GetReferenced(UniqueConstraint.Columns).ToList();
// Try to match all the columns in the foreign key to the index.
foundMatch = CompareColumns(fkColumns, uqColumns, ruleExecutionContext);
if (foundMatch)
{
break;
}
}
}
if (!foundMatch)
{
SqlRuleProblem problem = new SqlRuleProblem(
String.Format(
CultureInfo.CurrentCulture,
ruleDescriptor.DisplayDescription,
elementName,
ruleExecutionContext.SchemaModel.DisplayServices.GetElementName(table, ElementNameStyle.EscapedFullyQualifiedName)),
modelElement);
problems.Add(problem);
}
}
return(problems);
}
private static void AnalyzeColumns(
SqlRuleExecutionContext context,
TSqlObject index,
string defaultCollation,
IList<SqlRuleProblem> problems)
{
foreach (TSqlObject column in index.GetReferenced(Index.Columns)
.Where(column => IsCharacterColumn(column)))
{
// Fall back on the default project collation if none is defined for the specific column
string collation = column.GetProperty<string>(Column.Collation) ?? defaultCollation ?? string.Empty;
if (!collation.EndsWith(Bin2Ending, StringComparison.OrdinalIgnoreCase))
{
// Error looks liks "Index <name> on column <name> should have a BIN2 collation instead of <collation>"
// Choosing to add 1 problem per-column. This will cause more warnings in the error manager but is more precise
string errorMsg = string.Format(CultureInfo.CurrentCulture,
context.RuleDescriptor.DisplayDescription,
RuleUtils.GetElementName(index, context, ElementNameStyle.EscapedFullyQualifiedName),
RuleUtils.GetElementName(column, context, ElementNameStyle.EscapedFullyQualifiedName),
collation);
SqlRuleProblem problem = new SqlRuleProblem(errorMsg, index);
problems.Add(problem);
}
}
}
//protected string GetColumnDataType(ColumnReferenceExpression value, Dictionary<NamedTableView, IDictionary<string, DataTypeView>> columnDataTypes)
//{
// var columnName = value.MultiPartIdentifier.Identifiers.GetName().ToLower();
// var types = columnDataTypes.Where(t => t.Key.Name.ToLower().Contains(columnName));
// //so.... technically this could resolve to multiple columns, but I have no clue which one to pick as the column does not have any reference to the parent query.
// var typ = types.FirstOrDefault();
// if (typ.Key != null)
// {
// //return typ.Value..DataType.;
// }
// return null;
//}
/// <summary>
/// Gets the data type of the column.
/// </summary>
/// <param name="sqlObj">The SQL object.</param>
/// <param name="query">The query.</param>
/// <param name="column">The column.</param>
/// <param name="model">The model.</param>
/// <param name="variables">The variables.</param>
/// <returns></returns>
protected static string GetColumnDataType(TSqlObject sqlObj, QuerySpecification query, ColumnReferenceExpression column, TSqlModel model, IList <DataTypeView> variables)
{
TSqlObject referencedColumn = null;
var columnName = column.MultiPartIdentifier.Identifiers.Last().Value.ToLower();
var columns = sqlObj.GetReferenced(DacQueryScopes.All).Where(x =>
x.ObjectType == Column.TypeClass &&
x.Name.GetName().ToLower().Contains($"[{columnName}]")
).Distinct().ToList();
if (columns.Count == 0)
{
//we have an aliased column, probably from a cte, temp table, or sub-select. we need to try to find it
var visitor = new SelectScalarExpressionVisitor();
sqlObj.GetFragment().Accept(visitor); //sqlObj.GetFragment()
//try to find a select column where the alias matches the column name we are searching for
var selectColumns = visitor.Statements.Where(x => _comparer.Equals(x.ColumnName?.Value, columnName)).ToList();
//if we find more than one match, we have no way to determine which is the correct one.
if (selectColumns.Count == 1)
{
return(GetDataType(sqlObj, query, selectColumns.First().Expression, variables));
}
else
{
return(null);
}
}
else if (columns.Count > 1)
{
var tablesVisitor = new TableReferenceWithAliasVisitor();
if (column.MultiPartIdentifier.Identifiers.Count > 1)
{
sqlObj.GetFragment().Accept(tablesVisitor);
var columnTableAlias = column.MultiPartIdentifier.Identifiers.First().Value;
var tbls = tablesVisitor.Statements.Where(x => _comparer.Equals(x.Alias?.Value, columnTableAlias) || _comparer.Equals(x.GetName(), $"[{columnTableAlias}]"));
//if we find more than one table with the same alias, we have no idea which one it could be.
if (tbls.Count() == 1)
{
referencedColumn = GetReferencedColumn(tbls.FirstOrDefault(), columns, columnName);
}
else
{
foreach (var tbl in tbls)
{
referencedColumn = GetReferencedColumn(tbl, columns, columnName);
if (referencedColumn != null)
{
break;
}
}
}
}
else
{
query.Accept(tablesVisitor);
if (tablesVisitor.Count == 1)
{
referencedColumn = GetReferencedColumn(tablesVisitor.Statements.FirstOrDefault(), columns, columnName);
}
else
{
foreach (var tbl in tablesVisitor.Statements)
{
referencedColumn = GetReferencedColumn(tbl, columns, columnName);
if (referencedColumn != null)
{
break;
}
}
}
}
}
else
{
referencedColumn = columns.FirstOrDefault();
}
if (referencedColumn != null)
{
TSqlObject dataType = null;
//sometimes for some reason, I have to call getreferenced multiple times to get to the datatype. nfc why....
while (dataType == null && referencedColumn != null)
{
var colReferenced = referencedColumn.GetReferenced(DacQueryScopes.All);
dataType = colReferenced.FirstOrDefault(x => _comparer.Equals(x.ObjectType.Name, "DataType"));
if (dataType == null)
{
//try the next? referenced column.
referencedColumn = colReferenced.FirstOrDefault(x => x.ObjectType == Column.TypeClass);
}
else
{
break;
}
}
if (dataType != null)
{
return(dataType.Name.Parts.First());
}
}
return(null);
}
/// <summary>
/// For element-scoped rules the Analyze method is executed once for every matching object in the model.
/// </summary>
/// <param name="ruleExecutionContext">The context object contains the TSqlObject being analyzed, a TSqlFragment
/// that's the AST representation of the object, the current rule's descriptor, and a reference to the model being
/// analyzed.
/// </param>
/// <returns>A list of problems should be returned. These will be displayed in the Visual Studio error list</returns>
public override IList <SqlRuleProblem> Analyze(SqlRuleExecutionContext ruleExecutionContext)
{
IList <SqlRuleProblem> problems = new List <SqlRuleProblem>();
TSqlObject modelElement = ruleExecutionContext.ModelElement;
string elementName = ruleExecutionContext.SchemaModel.DisplayServices.GetElementName(modelElement, ElementNameStyle.EscapedFullyQualifiedName);
TSqlFragment fragment = ruleExecutionContext.ScriptFragment;
RuleDescriptor ruleDescriptor = ruleExecutionContext.RuleDescriptor;
// Get schema of the procedure.
TSqlObject schema = modelElement.GetReferenced(Procedure.Schema).SingleOrDefault();
if (schema != null && fragment.FragmentLength > 0)
{
CursorVisitor visitor = new CursorVisitor(true, false);
fragment.Accept(visitor);
foreach (var element in visitor.DeclareCursorStatementsMissingDeallocate)
{
string cursorName = element.Name.Value;
SqlRuleProblem problem = new SqlRuleProblem(
String.Format(
CultureInfo.CurrentCulture,
ruleDescriptor.DisplayDescription, "Missing deallocate",
cursorName,
elementName),
modelElement,
element.CursorDefinition);
problems.Add(problem);
}
foreach (var element in visitor.IncorrectDeallocateCursorStatements)
{
string cursorName = element.Cursor?.Name.Value ?? "NULL";
SqlRuleProblem problem = new SqlRuleProblem(
String.Format(
CultureInfo.CurrentCulture,
ruleDescriptor.DisplayDescription, "Invalid deallocate",
cursorName,
elementName),
modelElement,
element);
problems.Add(problem);
}
foreach (var element in visitor.DeclareCursorStatementsMissingOpen)
{
string cursorName = element.Name.Value;
SqlRuleProblem problem = new SqlRuleProblem(
String.Format(
CultureInfo.CurrentCulture,
ruleDescriptor.DisplayDescription, "Missing open",
cursorName,
elementName),
modelElement,
element.CursorDefinition);
problems.Add(problem);
}
foreach (var element in visitor.IncorrectOpenCursorStatements)
{
string cursorName = element.Cursor?.Name.Value ?? "NULL";
SqlRuleProblem problem = new SqlRuleProblem(
String.Format(
CultureInfo.CurrentCulture,
ruleDescriptor.DisplayDescription, "Invalid open",
cursorName,
elementName),
modelElement,
element);
problems.Add(problem);
}
}
return(problems);
}
private static bool IsCharacterColumn(TSqlObject column)
{
TSqlObject dataType = column.GetReferenced(Column.DataType).SingleOrDefault();
if (dataType == null)
{
return false;
}
// Note: User Defined Data Types (UDDTs) are not supported during deployment of memory optimized tables.
// The code below handles UDDTs in order to show how properties of a UDDT should be accessed and because
// the model validation does not actually block this syntax at present there are tests that validate this behavior.
// User Defined Data Types and built in types are merged in the public model.
// We want to examine the built in type: for user defined data types this will be
// found by accessing the DataType.Type object, which will not exist for a built in type
TSqlObject builtInType = dataType.GetReferenced(DataType.Type).SingleOrDefault();
if (builtInType != null)
{
dataType = builtInType;
}
SqlDataType sqlDataType = dataType.GetProperty<SqlDataType>(DataType.SqlDataType);
return CharacterDataTypes.Contains(sqlDataType);
}
private static ColumnInfo GetSchemaForColumn(TSqlObject model)
{
TSqlObject type = model.GetReferenced(Column.DataType).First();
string dataType = type.Name.Parts[0];
bool isNullable = model.GetProperty<bool>(Column.Nullable);
int length = model.GetProperty<int>(Column.Length);
return new ColumnInfo
{
Name = model.Name.Parts[2],
FullName = model.Name.ToString(),
SqlDataType = dataType,
ClrType = GetTypeMapping(dataType, isNullable),
Nullable = isNullable,
Length = length
};
}
private static ViewInfo GetSchemaForView(TSqlObject model)
{
ViewInfo retVal = new ViewInfo();
retVal.ShortName = model.Name.Parts[1];
retVal.FullName = model.Name.ToString();
var columns = model.GetReferenced(View.Columns).ToArray();
retVal.Columns = new ColumnInfo[columns.Length];
for (int i = 0; i < columns.Length; i++)
{
TSqlObject column = columns[i];
string dataType = "nvarchar";
bool isNullable = column.GetProperty<bool>(Column.Nullable);
int length = column.GetProperty<int>(Column.Length);
TSqlObject referencedColumn = column.GetReferenced().FirstOrDefault();
if (null != referencedColumn)
{
TSqlObject type = referencedColumn.GetReferenced(Column.DataType).First();
dataType = type.Name.Parts[0];
}
retVal.Columns[i] = new ColumnInfo
{
Name = column.Name.Parts[2],
FullName = column.Name.ToString(),
SqlDataType = dataType,
ClrType = GetTypeMapping(dataType, isNullable),
Nullable = isNullable,
Length = length
};
}
return retVal;
}
private static TableInfo GetSchemaForTable(TSqlObject model)
{
TableInfo retVal = new TableInfo();
retVal.EntityName = _pluralizationService.Singularize(model.Name.Parts[1]);
retVal.ShortName = model.Name.Parts[1];
retVal.FullName = model.Name.ToString();
var columns = model.GetReferenced(Table.Columns).ToArray();
retVal.Columns = new ColumnInfo[columns.Length];
for (int i = 0; i < columns.Length; i++)
{
ColumnInfo column = GetSchemaForColumn(columns[i]);
retVal.Columns[i] = column;
if (columns[i].GetProperty<bool>(Column.IsIdentity))
retVal.IdentityColumn = column;
}
return retVal;
}
private void AddPropertiesForColumn(Panel panel, TSqlObject column)
{
var type = column.GetMetadata<ColumnType>(Column.ColumnType);
panel.Children.Add(GetPropertyLabel("Column MetaType: ", type == ColumnType.Column ? "Standard Column" : type.ToString()));
foreach (TSqlObject referenced in column.GetReferenced())
{
panel.Children.Add(GetPropertyLabel("Type: ", referenced.Name.ToString()));
}
}
private static bool IsIndexOnMemoryOptimizedTable(TSqlObject index)
{
TSqlObject targetTable = index.GetReferenced(Index.IndexedObject).SingleOrDefault();
return targetTable != null
&& Table.TypeClass.Equals(targetTable.ObjectType)
&& targetTable.GetProperty<bool>(Table.MemoryOptimized);
}
public void Visit(Node <Nodes.Database> databaseNode, TSqlObject storedProcedure)
{
var storedProcedureNode = GetStoredProcedureNode(databaseNode, storedProcedure);
foreach (var table in storedProcedure.GetReferenced().Where(x => x.ObjectType == ModelSchema.Table))
{
try
{
var tableNode = GetTableNode(databaseNode, table);
if ((storedProcedureNode == null) || (tableNode == null))
{
continue;
}
if (RelationshipExists(storedProcedureNode.Reference, tableNode.Reference))
{
continue;
}
Console.WriteLine("Creating dependency between stored procedure {0} and table {1}", storedProcedureNode.Data.Id, tableNode.Data.Id);
_graphClient.CreateRelationship(storedProcedureNode.Reference, new StoredProcedureReferencesTable(tableNode.Reference));
}
catch (Exception ex)
{
Console.WriteLine("Error analyzing {0}: {1}", storedProcedure.Name.ToString(), ex);
}
}
foreach (var column in storedProcedure.GetReferenced().Where(x => x.ObjectType == ModelSchema.Column))
{
try
{
var columnNode = GetColumnNode(databaseNode, column);
if ((storedProcedureNode == null) || (columnNode == null))
{
continue;
}
if (RelationshipExists(storedProcedureNode.Reference, columnNode.Reference))
{
continue;
}
Console.WriteLine("Creating dependency between stored procedure {0} and column {1}", storedProcedureNode.Data.Id, columnNode.Data.Id);
_graphClient.CreateRelationship(storedProcedureNode.Reference, new StoredProcedureReferencesColumn(columnNode.Reference));
}
catch (Exception ex)
{
Console.WriteLine("Error analyzing {0}: {1}", storedProcedure.Name.ToString(), ex);
}
}
// Check for dynamic SQL
var script = storedProcedure.GetScript();
if (!script.Contains("EXEC"))
{
return;
}
foreach (Match match in FromRegex.Matches(script))
{
try
{
var tableNode = GetTableNode(databaseNode, string.Format("[dbo].[{0}]", match.Groups["table"].Value));
if ((storedProcedureNode == null) || (tableNode == null))
{
continue;
}
if (RelationshipExists(storedProcedureNode.Reference, tableNode.Reference))
{
continue;
}
Console.WriteLine("Creating dependency between stored procedure {0} and table {1} (dynamic SQL)", storedProcedureNode.Data.Id, tableNode.Data.Id);
_graphClient.CreateRelationship(storedProcedureNode.Reference, new StoredProcedureReferencesTable(tableNode.Reference));
}
catch (Exception ex)
{
Console.WriteLine("Error analyzing {0}: {1}", storedProcedure.Name.ToString(), ex);
}
}
foreach (Match match in JoinRegex.Matches(script))
{
try
{
var tableNode = GetTableNode(databaseNode, string.Format("[dbo].[{0}]", match.Groups["table"].Value));
if ((storedProcedureNode == null) || (tableNode == null))
{
continue;
}
if (RelationshipExists(storedProcedureNode.Reference, tableNode.Reference))
{
continue;
}
Console.WriteLine("Creating dependency between stored procedure {0} and table {1} (dynamic SQL)", storedProcedureNode.Data.Id, tableNode.Data.Id);
_graphClient.CreateRelationship(storedProcedureNode.Reference, new StoredProcedureReferencesTable(tableNode.Reference));
}
catch (Exception ex)
{
Console.WriteLine("Error analyzing {0}: {1}", storedProcedure.Name.ToString(), ex);
}
}
}
