/// <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);
int precision = column.GetProperty <int>(Column.Precision);
int scale = column.GetProperty <int>(Column.Scale);
return(precision != 0
? sdt.ToString().ToUpper() + $"({precision},{scale})"
: length == 0 && !isMax
? sdt.ToString().ToUpper()
: length == 0
? sdt.ToString().ToUpper() + "(MAX)"
: sdt.ToString().ToUpper() + "(" + length + ")");
}
else
{
return(sdt.ToString().ToUpper());
}
}
private bool IsClustered(TSqlObject i)
{
if (i.ObjectType == ModelSchema.Index)
{
return(Convert.ToBoolean(i.GetProperty(Index.Clustered)));
}
else
{
return(Convert.ToBoolean(i.GetProperty(PrimaryKeyConstraint.Clustered)));
}
}
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));
}
public override IList <SqlRuleProblem> Analyze(SqlRuleExecutionContext context)
{
IList <SqlRuleProblem> problems = new List <SqlRuleProblem>();
TSqlObject index = context.ModelElement;
if (!IsIndexOnMemoryOptimizedTable(index))
{
// Only examining memory optimized tables, anything else can be ignored
// Note the call to the Table.MemoryOptimized property: this is where we verify the in-memory behavior
return(problems);
}
TSqlObject databaseOptions = context.SchemaModel
.GetObjects(DacQueryScopes.UserDefined, DatabaseOptions.TypeClass).SingleOrDefault();
if (databaseOptions == null)
{
// This should never happen as the database options are automatically created
return(problems);
}
string defaultCollation = databaseOptions.GetProperty <string>(DatabaseOptions.Collation);
AnalyzeColumns(context, index, defaultCollation, problems);
return(problems);
}
public void ReadModel(string srcConString)
{
var decFilePath = GetDacFileName(srcConString) + ".bacpac";
using (TSqlModel model = new TSqlModel(decFilePath))
{
// This will get all tables. Note the use of Table.TypeClass!
var tables = model.GetObjects(DacQueryScopes.Default, Table.TypeClass).ToList();
// Look up a specific table by ID. Note that if no schema is defined when creating
// an element the default "dbo" schema is used
var t1 = model.GetObjects(Table.TypeClass, new ObjectIdentifier("dbo", "t1"), DacQueryScopes.Default).FirstOrDefault();
// Get a the column referenced by this table, and query its length
TSqlObject column = t1.GetReferenced(Table.Columns).First(col => col.Name.Parts[2].Equals("c1"));
int columnLength = column.GetProperty <int>(Column.Length);
Console.WriteLine("Column c1 has length {0}", columnLength);
// Verify the ColumnType of this column. This can help indicate which
// properties will return meaningful values.
// For instance since Column.Collation is only available on a simple column,
// and Column.Persisted is only on computed columns
ColumnType columnType = column.GetMetadata <ColumnType>(Column.ColumnType);
Console.WriteLine("Column c1 is of type '{0}'", columnType);
}
}
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 bool IsIndexOnMemoryOptimizedTable(TSqlObject index)
{
TSqlObject targetTable = index.GetReferenced(Index.IndexedObject).SingleOrDefault();
return(targetTable != null &&
Table.TypeClass.Equals(targetTable.ObjectType) &&
targetTable.GetProperty <bool>(Table.MemoryOptimized));
}
private bool IsDateTime2WithExcessiveScale(TSqlObject column)
{
var dataType = GetDataType(column);
var scale = column.GetProperty<int>(Column.Scale);
return (dataType == SqlDataType.DateTime2 && scale > 2);
}
/// <summary>
/// Check if a column is identity
/// </summary>
/// <param name="column"></param>
/// <returns></returns>
public static bool IsColumnIdentity(this TSqlObject column)
{
if (column == null)
{
throw new ArgumentNullException(nameof(column));
}
bool result = column.GetProperty <bool>(Column.IsIdentity);
return(result);
}
/// <summary>
// Views must be schema bound if they reference a memory optimized table
/// </summary>
private static void ValidateViewHasSchemaBinding(SqlRuleExecutionContext context, TSqlObject view, TSqlObject table,
IList <SqlRuleProblem> problems)
{
if (!view.GetProperty <bool>(View.WithSchemaBinding))
{
string description = string.Format(CultureInfo.CurrentCulture,
RuleResources.ViewsOnMemoryOptimizedTable_SchemaBindingProblemDescription,
RuleUtils.GetElementName(context, view),
RuleUtils.GetElementName(context, table));
TSqlFragment nameFragment = TsqlScriptDomUtils.LookupSchemaObjectName(view);
problems.Add(new SqlRuleProblem(description, view, nameFragment));
}
}
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 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("===============================");
}
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("===============================");
}
public override IList <SqlRuleProblem> Analyze(SqlRuleExecutionContext context)
{
IList <SqlRuleProblem> problems = new List <SqlRuleProblem>();
TSqlObject table = context.ModelElement;
if (table.GetProperty <bool>(Table.MemoryOptimized))
{
// In this case we look up "Referencing" relationships. This is a way to iterate
// over the objects that reference the current object. Note how the actual relationship
// that we care about is defined on the View class rather than on the table.
foreach (TSqlObject view in table.GetReferencing(View.BodyDependencies))
{
ValidateViewHasSchemaBinding(context, view, table, problems);
ValidateViewHasNoIndexes(context, view, table, problems);
}
}
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;
}
}
/// <summary>
/// Retrieve the database schema provider for the
/// model and the collation of that model.
/// Results are output to the console and added to the XML
/// being constructed.
/// </summary>
private static void SummarizeModelInfo(TSqlModel model, XElement xContainer, IList <ExtensibilityError> errors)
{
// use a Dictionary to accumulate the information
// that will later be output.
var info = new Dictionary <string, string>();
// Two things of interest: the database schema
// provider for the model, and the language id and
// case sensitivity of the collation of that
// model
info.Add("Version", model.Version.ToString());
TSqlObject options = model.GetObjects(DacQueryScopes.UserDefined, DatabaseOptions.TypeClass).FirstOrDefault();
if (options != null)
{
info.Add("Collation", options.GetProperty <string>(DatabaseOptions.Collation));
}
// Output the accumulated information and add it to
// the XML.
OutputResult("Basic model info", info, xContainer, errors);
}
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>
/// Copies the <see cref="DatabaseOptions"/> for the model to a <see cref="TSqlModelOptions"/> object.
/// This is useful if you wish to duplicate the options for a model when creating a new model.
/// Note that this method may be included in the public model framework in the future.
/// </summary>
public static TSqlModelOptions CloneModelOptions(this TSqlModel model)
{
TSqlModelOptions clonedOptions = new TSqlModelOptions();
TSqlObject options = model.GetObjects(DacQueryScopes.All, DatabaseOptions.TypeClass).FirstOrDefault();
if (options == null)
{
return(clonedOptions);
}
clonedOptions.Collation = options.GetProperty <string>(DatabaseOptions.Collation);
clonedOptions.AllowSnapshotIsolation = options.GetProperty <bool>(DatabaseOptions.AllowSnapshotIsolation);
clonedOptions.TransactionIsolationReadCommittedSnapshot = options.GetProperty <bool>(DatabaseOptions.TransactionIsolationReadCommittedSnapshot);
clonedOptions.AnsiNullDefaultOn = options.GetProperty <bool>(DatabaseOptions.AnsiNullDefaultOn);
clonedOptions.AnsiNullsOn = options.GetProperty <bool>(DatabaseOptions.AnsiNullsOn);
clonedOptions.AnsiPaddingOn = options.GetProperty <bool>(DatabaseOptions.AnsiPaddingOn);
clonedOptions.AnsiWarningsOn = options.GetProperty <bool>(DatabaseOptions.AnsiWarningsOn);
clonedOptions.ArithAbortOn = options.GetProperty <bool>(DatabaseOptions.ArithAbortOn);
clonedOptions.AutoClose = options.GetProperty <bool>(DatabaseOptions.AutoClose);
clonedOptions.AutoCreateStatistics = options.GetProperty <bool>(DatabaseOptions.AutoCreateStatistics);
clonedOptions.AutoShrink = options.GetProperty <bool>(DatabaseOptions.AutoShrink);
clonedOptions.AutoUpdateStatistics = options.GetProperty <bool>(DatabaseOptions.AutoUpdateStatistics);
clonedOptions.AutoUpdateStatisticsAsync = options.GetProperty <bool>(DatabaseOptions.AutoUpdateStatisticsAsync);
clonedOptions.ChangeTrackingAutoCleanup = options.GetProperty <bool>(DatabaseOptions.ChangeTrackingAutoCleanup);
clonedOptions.ChangeTrackingEnabled = options.GetProperty <bool>(DatabaseOptions.ChangeTrackingEnabled);
clonedOptions.ChangeTrackingRetentionPeriod = options.GetProperty <int>(DatabaseOptions.ChangeTrackingRetentionPeriod);
clonedOptions.ChangeTrackingRetentionUnit = options.GetProperty <TimeUnit>(DatabaseOptions.ChangeTrackingRetentionUnit);
clonedOptions.CompatibilityLevel = options.GetProperty <int>(DatabaseOptions.CompatibilityLevel);
clonedOptions.ConcatNullYieldsNull = options.GetProperty <bool>(DatabaseOptions.ConcatNullYieldsNull);
clonedOptions.Containment = options.GetProperty <Containment>(DatabaseOptions.Containment);
clonedOptions.CursorCloseOnCommit = options.GetProperty <bool>(DatabaseOptions.CursorCloseOnCommit);
clonedOptions.CursorDefaultGlobalScope = options.GetProperty <bool>(DatabaseOptions.CursorDefaultGlobalScope);
clonedOptions.DatabaseStateOffline = options.GetProperty <bool>(DatabaseOptions.DatabaseStateOffline);
clonedOptions.DateCorrelationOptimizationOn = options.GetProperty <bool>(DatabaseOptions.DateCorrelationOptimizationOn);
clonedOptions.DefaultFullTextLanguage = options.GetProperty <string>(DatabaseOptions.DefaultFullTextLanguage);
clonedOptions.DefaultLanguage = options.GetProperty <string>(DatabaseOptions.DefaultLanguage);
clonedOptions.DBChainingOn = options.GetProperty <bool>(DatabaseOptions.DBChainingOn);
clonedOptions.FileStreamDirectoryName = options.GetProperty <string>(DatabaseOptions.FileStreamDirectoryName);
clonedOptions.FullTextEnabled = options.GetProperty <bool>(DatabaseOptions.FullTextEnabled);
clonedOptions.HonorBrokerPriority = options.GetProperty <bool>(DatabaseOptions.HonorBrokerPriority);
clonedOptions.NestedTriggersOn = options.GetProperty <bool>(DatabaseOptions.NestedTriggersOn);
clonedOptions.NonTransactedFileStreamAccess = options.GetProperty <NonTransactedFileStreamAccess>(DatabaseOptions.NonTransactedFileStreamAccess);
clonedOptions.NumericRoundAbortOn = options.GetProperty <bool>(DatabaseOptions.NumericRoundAbortOn);
clonedOptions.PageVerifyMode = options.GetProperty <PageVerifyMode>(DatabaseOptions.PageVerifyMode);
clonedOptions.ParameterizationOption = options.GetProperty <ParameterizationOption>(DatabaseOptions.ParameterizationOption);
clonedOptions.QuotedIdentifierOn = options.GetProperty <bool>(DatabaseOptions.QuotedIdentifierOn);
clonedOptions.ReadOnly = options.GetProperty <bool>(DatabaseOptions.ReadOnly);
clonedOptions.RecoveryMode = options.GetProperty <RecoveryMode>(DatabaseOptions.RecoveryMode);
clonedOptions.RecursiveTriggersOn = options.GetProperty <bool>(DatabaseOptions.RecursiveTriggersOn);
clonedOptions.ServiceBrokerOption = options.GetProperty <ServiceBrokerOption>(DatabaseOptions.ServiceBrokerOption);
clonedOptions.SupplementalLoggingOn = options.GetProperty <bool>(DatabaseOptions.SupplementalLoggingOn);
clonedOptions.TargetRecoveryTimePeriod = options.GetProperty <int>(DatabaseOptions.TargetRecoveryTimePeriod);
clonedOptions.TargetRecoveryTimeUnit = options.GetProperty <TimeUnit>(DatabaseOptions.TargetRecoveryTimeUnit);
clonedOptions.TornPageProtectionOn = options.GetProperty <bool>(DatabaseOptions.TornPageProtectionOn);
clonedOptions.TransformNoiseWords = options.GetProperty <bool>(DatabaseOptions.TransformNoiseWords);
clonedOptions.Trustworthy = options.GetProperty <bool>(DatabaseOptions.Trustworthy);
clonedOptions.TwoDigitYearCutoff = options.GetProperty <short>(DatabaseOptions.TwoDigitYearCutoff);
clonedOptions.VardecimalStorageFormatOn = options.GetProperty <bool>(DatabaseOptions.VardecimalStorageFormatOn);
clonedOptions.UserAccessOption = options.GetProperty <UserAccessOption>(DatabaseOptions.UserAccessOption);
clonedOptions.WithEncryption = options.GetProperty <bool>(DatabaseOptions.WithEncryption);
return(clonedOptions);
}
/// <summary>
// Views must be schema bound if they reference a memory optimized table
/// </summary>
private static void ValidateViewHasSchemaBinding(SqlRuleExecutionContext context, TSqlObject view, TSqlObject table,
IList<SqlRuleProblem> problems)
{
if (!view.GetProperty<bool>(View.WithSchemaBinding))
{
string description = string.Format(CultureInfo.CurrentCulture,
RuleResources.ViewsOnMemoryOptimizedTable_SchemaBindingProblemDescription,
RuleUtils.GetElementName(context, view),
RuleUtils.GetElementName(context, table));
TSqlFragment nameFragment = TsqlScriptDomUtils.LookupSchemaObjectName(view);
problems.Add(new SqlRuleProblem(description, view, nameFragment));
}
}