protected override IEnumerable<SqlRuleProblem> OnAnalyze(string name, TSqlObject table)
{
//Get the indexes of the table
var indexes = table.GetReferencing(Index.IndexedObject);
if (indexes.Count() == 0)
yield return new SqlRuleProblem(string.Format("The anchor table {0} has no index", name), table);
//Ensure that one of them is effecively not a clustered index
var nonClusturedIndexes = indexes.Where(i => !i.GetProperty<bool>(Index.Clustered) && i.GetProperty<bool>(Index.Unique));
if (nonClusturedIndexes == null)
yield return new SqlRuleProblem(string.Format("No existing non-clustered unique index for the anchor table {0}", name), table);
else
{
//Ensure that at least one of them is name BK
var bkIndexes = nonClusturedIndexes.Where(i => i.Name.Parts.Last().StartsWith(Configuration.Anchor.BusinessKeyPrefix));
if (bkIndexes.Count()==0)
yield return new SqlRuleProblem(string.Format("None of the non-clustered unique indexes for the anchor table {0} are starting by BK_", name), table);
else
{
foreach (var bkIndex in bkIndexes)
{
//Ensure that the unique index is not active on the identity column
var columns = bkIndex.GetReferenced(Index.Columns).Where(c => c.GetProperty<bool>(Column.IsIdentity));
if (columns.Count()>0)
yield return new SqlRuleProblem(string.Format("The business key (non-clustered unique index) {1} for the anchor table {0} contains the identity column.", name, bkIndex.Name), table);
//By default SQL Server will include the indentity column (because this column should be the clustered index)
var includedColumns = bkIndex.GetReferenced(Index.IncludedColumns).Where(c => c.GetProperty<bool>(Column.IsIdentity));
if (includedColumns.Count() > 0)
yield return new SqlRuleProblem(string.Format("The business key (non-clustered unique index) {1} for the anchor table {0} includes the identity column.", name, bkIndex.Name), table);
}
}
}
}
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);
}
protected override IEnumerable<SqlRuleProblem> OnAnalyze(string name, TSqlObject table)
{
//Get the indexes of the table
var indexes = table.GetReferencing(Index.IndexedObject);
if (indexes.Count() == 0)
yield return new SqlRuleProblem(string.Format("The info table {0} has no index", name), table);
//Ensure that one of them is effecively a clustered index
var clusteredIndex = indexes.FirstOrDefault(i => i.GetProperty<bool>(Index.Clustered));
if (clusteredIndex == null)
yield return new SqlRuleProblem(string.Format("No clustered index for the info table {0}", name), table);
else
{
//Ensure that this index is effectively unique
var uniqueClusturedIndex = indexes.FirstOrDefault(i => i.GetProperty<bool>(Index.Clustered) && i.GetProperty<bool>(Index.Unique));
if (uniqueClusturedIndex == null)
yield return new SqlRuleProblem(string.Format("Clustured index for the info table {0} is not unique ", name), table);
else
{
//Ensure that the clustered index is active only on the identity column
var columns = uniqueClusturedIndex.GetReferenced(Index.Columns);
if (columns.Count() > 1)
yield return new SqlRuleProblem(string.Format("The info table {0} has a clustered index but this index has more than one column.", name), table);
if (columns.Count(c => c.GetProperty<bool>(Column.IsIdentity)) == 0)
yield return new SqlRuleProblem(string.Format("The info table {0} has a clustered index but this index doesn't include the identity column.", name), table);
}
}
}
private static string GetScript(TSqlObject procedure)
{
var script = "";
if (procedure.TryGetScript(out script))
return script;
return ""; //could throw an exception or logged this if we care??
}
private static void DumpScript(TSqlObject parent)
{
var script = "";
if (parent.TryGetScript(out script))
{
Console.WriteLine(script);
}
}
/// <summary>
/// Gets a formatted element name
/// </summary>
public static string GetElementName(TSqlObject modelElement, SqlRuleExecutionContext ruleExecutionContext, ElementNameStyle style)
{
// Get the element name using the built in DisplayServices. This provides a number of useful formatting options to
// make a name user-readable
var displayServices = ruleExecutionContext.SchemaModel.DisplayServices;
string elementName = displayServices.GetElementName(modelElement, style);
return elementName;
}
protected bool IsAnchor(TSqlObject table)
{
return
table.Name.HasName
&& table.Name.Parts.Reverse().Take(2).Last().Equals(Configuration.Anchor.Schema, StringComparison.OrdinalIgnoreCase)
&& table.Name.Parts.Last().StartsWith(Configuration.Anchor.Prefix, StringComparison.OrdinalIgnoreCase)
&& table.Name.Parts.Last().EndsWith(Configuration.Anchor.Suffix, StringComparison.OrdinalIgnoreCase);
}
private static string GetElementName(SqlRuleExecutionContext ruleExecutionContext, TSqlObject modelElement)
{
// Get the element name using the built in DisplayServices. This provides a number of
// useful formatting options to
// make a name user-readable
var displayServices = ruleExecutionContext.SchemaModel.DisplayServices;
string elementName = displayServices.GetElementName(modelElement, ElementNameStyle.EscapedFullyQualifiedName);
return elementName;
}
private bool IsDateTime2WithExcessiveScale(TSqlObject column)
{
var dataType = GetDataType(column);
var scale = column.GetProperty<int>(Column.Scale);
return (dataType == SqlDataType.DateTime2 && scale > 2);
}
private static bool HasParameterBeenRemoved(ModelRelationshipInstance parameter, TSqlObject oldProcedure)
{
/*
A parameter does not exist in the old model
*/
return
oldProcedure.GetReferencedRelationshipInstances(Procedure.Parameters)
.FirstOrDefault(p => p.ObjectName.Parts.Last() == parameter.ObjectName.Parts.Last()) == null;
}
private static void ShowColumnsDataType(TSqlObject table)
{
foreach (var child in table.GetReferencedRelationshipInstances(Table.Columns))
{
var type = child.Object.GetReferenced(Column.DataType).FirstOrDefault();
var isNullable = type.GetProperty<bool?> (DataType.UddtNullable);
var length = type.GetProperty<int?>(DataType.UddtLength);
//do something useful with this information!
}
}
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);
}
private IList<SqlRuleProblem> DoSmells(TSqlObject SqlObject)
{
List<SqlRuleProblem> problems = new List<SqlRuleProblem>();
Smells smellprocess = new Smells();
int iRule = int.Parse(_ruleID.Substring(_ruleID.Length - 3));
return (smellprocess.ProcessObject(SqlObject, iRule));
}
static void DumpChildren(TSqlObject parent, int depth)
{
DumpScript(parent);
foreach (var property in parent.ObjectType.Properties)
{
DumpProperty(property, parent);
}
foreach (var child in parent.GetChildren())
{
DumpChildren(child, depth + 1);
}
}
public bool Add(string root, string name, TSqlObject item)
{
if (!SeemItems.ContainsKey(root))
{
SeemItems[root] = new Dictionary<string, TSqlObject>();
}
if (SeemItems[root].ContainsKey(name))
return false;
SeemItems[root][name] = item;
return true;
}
private void AddPropertiesForIndex(Panel panel, TSqlObject index)
{
foreach (var reference in index.GetReferencedRelationshipInstances(Index.Columns))
{
panel.Children.Add(GetPropertyLabel("Column: ", reference.ObjectName + " " + (reference.GetProperty<bool>(Index.ColumnsRelationship.Ascending) ? "ASC" : "DESC")));
}
if (index.GetReferencedRelationshipInstances(Index.IncludedColumns).Any())
{
foreach (var reference in index.GetReferencedRelationshipInstances(Index.IncludedColumns))
{
panel.Children.Add(GetPropertyLabel("Included Column: ", reference.ObjectName.ToString()));
}
}
}
protected override IEnumerable<SqlRuleProblem> OnAnalyze(string name, TSqlObject table)
{
var indexes = table.GetReferencing(Index.IndexedObject);
var firstTimeIndexes = indexes.Where(i => i.GetReferenced(Index.Columns).First().Name.Parts.Last()
== Configuration.Link.IsFirst);
if (firstTimeIndexes.Count() == 0)
yield return new SqlRuleProblem(string.Format("No index where is-first column is '{0}' for link table {1}", Configuration.TimeBased.Key, name), table);
foreach (var fIndex in firstTimeIndexes)
{
var indexColumns = fIndex.GetReferenced(Index.Columns).Where(c => c.Name.Parts.Last() != Configuration.TimeBased.Key);
var includedColumns = fIndex.GetReferenced(Index.IncludedColumns);
var allColumns = indexColumns.Union(includedColumns);
if (allColumns.Count() == 0)
yield return new SqlRuleProblem(string.Format("The time-based index {0} for link table {1} has no additional or included columns", fIndex.Name, name), table);
yield return new SqlRuleProblem(string.Format("The time-based index {0} for link table {1} has no additional or included columns", fIndex.Name, name), table);
}
}
private bool HasDefaultValueBeenRemoved(ModelRelationshipInstance parameter, TSqlObject oldProcedure)
{
var newParameterHasDefault = parameter.Object.GetProperty(Parameter.DefaultExpression) != null;
var parameterInOldModel =
oldProcedure.GetReferencedRelationshipInstances(Procedure.Parameters)
.FirstOrDefault(p => p.ObjectName.Parts.Last() == parameter.ObjectName.Parts.Last());
/*
A parameter in the new model has no default but it had a default in the old model...
*/
if (parameterInOldModel != null && !newParameterHasDefault && (parameterInOldModel.Object.GetProperty(Parameter.DefaultExpression) != null))
{
return true;
}
return false;
}
private static bool HasParameterBeenAddedWithoutDefaultValue(ModelRelationshipInstance parameter,
TSqlObject newProcedure)
{
var hasDefault = parameter.Object.GetProperty(Parameter.DefaultExpression) != null;
var parameterInOldModel =
newProcedure.GetReferencedRelationshipInstances(Procedure.Parameters)
.FirstOrDefault(p => p.ObjectName.Parts.Last() == parameter.ObjectName.Parts.Last());
/*
A parameter did not exist in the old model and has no default
*/
if (parameterInOldModel == null && !hasDefault)
{
return true;
}
return false;
}
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);
}
}
}
public UIElement GetPropertiesDisplay(TSqlObject item)
{
var panel = GetPropertiesDisplayPanel(item.Name.ToString());
AddCustomProperties(item, panel);
foreach (var property in item.ObjectType.Properties.OrderBy(p => p.Name))
{
var val = property.GetValue<object>(item);
if (val == null)
{
val = "NULL";
}
var label = GetPropertyLabel(property.Name, val.ToString());
panel.Children.Add(label);
}
return panel;
}
private void AddCustomProperties(TSqlObject item, StackPanel panel)
{
if (item.ObjectType == ModelSchema.Index)
{
AddPropertiesForIndex(panel, item);
return;
}
if (item.ObjectType == ModelSchema.Column)
{
AddPropertiesForColumn(panel, item);
return;
}
if (item.ObjectType == ModelSchema.PrimaryKeyConstraint)
{
AddPropertiesForPrimaryKey(panel, item);
return;
}
if (item.ObjectType == ModelSchema.ForeignKeyConstraint)
{
AddPropertiesForForeignKey(panel, item);
return;
}
if (item.ObjectType == ModelSchema.DefaultConstraint)
{
AddPropertiesForDefaultConstraint(panel, item);
return;
}
if (item.ObjectType == ModelSchema.DmlTrigger)
{
AddPropertiesForDmlTrigger(panel, item);
return;
}
}
protected IEnumerable<SqlRuleProblem> OnAnalyze(string name, TSqlObject table, string columnName)
{
var indexes = table.GetReferencing(Index.IndexedObject);
var lastIndexes = indexes.Where(i => i.GetReferenced(Index.Columns).Last().Name.Parts.Last()
== columnName);
if (lastIndexes.Count() == 0)
yield return new SqlRuleProblem(string.Format("No index on the column '{0}' for link table {1}", columnName, name), table);
var filteredIndexes = lastIndexes.Where(i => i.GetProperty<bool>(Index.FilterPredicate));
if (filteredIndexes.Count() == 0)
yield return new SqlRuleProblem(string.Format("An index exists on the column '{0}' for link table {0} but this index is not filtered.", columnName, name), table);
foreach (var lastIndex in lastIndexes)
{
var indexColumns = lastIndex.GetReferenced(Index.Columns).Where(c => c.Name.Parts.Last() != columnName);
var includedColumns = lastIndex.GetReferenced(Index.IncludedColumns);
var allColumns = indexColumns.Union(includedColumns);
if (allColumns.Count() == 0)
yield return new SqlRuleProblem(string.Format("The index {0} for link table {1} has no additional column or included column.", lastIndex.Name, name), table);
}
}
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 void DumpIndex(TSqlObject index)
{
//Each TSqlObject has a name property:
ObjectIdentifier indexName = index.Name;
//Top level objects like tables, procedures and indexes will let you get the underlying script to generate them, doing this on things like columns fails
string script = "";
if (!index.TryGetScript(out script))
{
script = "Can only script top level objects";
}
//To get to individual properties we need to use the static schema container classes, each property can be called directly or you can ask an object for all it's child properties
var allowPageLocks = index.GetProperty<bool?>(Index.AllowPageLocks);
var isClustered = index.GetProperty<bool?>(Index.Clustered);
Console.WriteLine("Index: " + indexName);
Console.WriteLine("Properties: Is Clustered: {0}, Allow Page Locks: {1}", isClustered, allowPageLocks);
//To get the columns we need to ask for the relationships of the index and then enumerate through them
foreach (ModelRelationshipInstance column in index.GetReferencedRelationshipInstances(Index.Columns))
{
DumpColumn(column, "Column");
}
//Included columns are referenced using the relationships but are a slightly different class
foreach (ModelRelationshipInstance column in index.GetReferencedRelationshipInstances(Index.IncludedColumns))
{
DumpColumn(column, "Included");
}
Console.WriteLine("Script:");
Console.WriteLine(script);
Console.WriteLine("===============================");
}
protected override IEnumerable<SqlRuleProblem> OnAnalyze(string name, TSqlObject table)
{
return OnAnalyze(name, table, Configuration.Link.IsFirst);
}
/// <summary>
/// Helper method to generate properties for an article. Separated out to permit creation of schema options by only specifying by hand
/// those options which are true and defaulting the rest to false. This was not possible using collection initializers.
/// </summary>
private static List<Property> GenerateArticleProperties(TSqlObject sqlTable)
{
SqlPublicationSchemaOptions[] defaultActiveSchemaOptions =
{
SqlPublicationSchemaOptions.ConvertTimestampToBinary,
SqlPublicationSchemaOptions.CopyClusteredIndex,
SqlPublicationSchemaOptions.CopyPrimaryKey,
SqlPublicationSchemaOptions.CreateSchemasAtSubscriber
};
var result = new List<Property>();
result.AddRange(new [] {
new Property { Name = "Description", Value = string.Empty},
new Property { Name = "DestinationOwner", Value = sqlTable.Name.Parts[0] },
new Property { Name = "DestinationTable", Value = sqlTable.Name.Parts[1] },
new Property { Name = "Name", Value = sqlTable.Name.Parts[1] }
});
foreach (var schemaOptionName in Enum.GetNames(typeof(SqlPublicationSchemaOptions)))
{
var value = defaultActiveSchemaOptions.Contains(schemaOptionName.AsEnum<SqlPublicationSchemaOptions>()) ? "True" : "False";
result.Add(new Property { Name = "SchemaOption" + schemaOptionName, Value = value });
}
return result;
}
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 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 void UpdatePrimaryKeysInformation(TableInfo[] tables, TSqlObject[] keys)
{
// Get schema for all primary keys
foreach (var key in keys)
{
string keyName = key.Name.Parts[1];
string tableName = keyName.Substring(3);
var columns = key.GetReferenced(PrimaryKeyConstraint.Columns).ToArray();
var primaryKeyColumns = new ColumnInfo[columns.Length];
for (int i = 0; i < primaryKeyColumns.Length; i++)
{
primaryKeyColumns[i] = GetSchemaForColumn(columns[i]);
}
var targetTable = tables.FirstOrDefault(t => t.ShortName == tableName);
if (null == targetTable)
{
throw new InvalidOperationException(
"Could not find target table for primary key " + key.Name);
}
targetTable.PrimaryKey = primaryKeyColumns;
}
}
