/// <summary>
/// Converts a string of the form 'There''s a double single quote in here'
/// or, for unicode strings, N'There''s a double single quote in here'
/// (including the optional N and the outer single quotes) to the string literal
/// "There's a double single quote in here" (not including the double quotes).
/// </summary>
/// <param name="sqlServerStringLiteral"> the string to convert </param>
/// <returns> the converted string, or null if it cannot convert </returns>
public virtual string ConvertSqlServerStringLiteral([NotNull] string sqlServerStringLiteral)
{
Check.NotEmpty(sqlServerStringLiteral, nameof(sqlServerStringLiteral));
if (sqlServerStringLiteral[0] == 'N')
{
sqlServerStringLiteral = sqlServerStringLiteral.Substring(1);
}
var sqlServerStringLiteralLength = sqlServerStringLiteral.Length;
if (sqlServerStringLiteralLength < 2)
{
Logger.LogWarning(
SqlServerDesignStrings.CannotInterpretSqlServerStringLiteral(sqlServerStringLiteral));
return(null);
}
if (sqlServerStringLiteral[0] != '\'' ||
sqlServerStringLiteral[sqlServerStringLiteralLength - 1] != '\'')
{
Logger.LogWarning(
SqlServerDesignStrings.CannotInterpretSqlServerStringLiteral(sqlServerStringLiteral));
return(null);
}
return(sqlServerStringLiteral.Substring(1, sqlServerStringLiteralLength - 2)
.Replace("''", "'"));
}
private PropertyBuilder VisitDefaultValue(ColumnModel column, PropertyBuilder propertyBuilder)
{
if (column.DefaultValue != null)
{
// unset default
propertyBuilder.Metadata.ValueGenerated = null;
propertyBuilder.Metadata.Relational().GeneratedValueSql = null;
var property = propertyBuilder.Metadata;
var defaultExpressionOrValue =
_sqlServerLiteralUtilities
.ConvertSqlServerDefaultValue(
property.ClrType, column.DefaultValue);
if (defaultExpressionOrValue?.DefaultExpression != null)
{
propertyBuilder.HasDefaultValueSql(defaultExpressionOrValue.DefaultExpression);
}
else if (defaultExpressionOrValue != null)
{
// Note: defaultExpressionOrValue.DefaultValue == null is valid
propertyBuilder.HasDefaultValue(defaultExpressionOrValue.DefaultValue);
}
else
{
Logger.LogWarning(
SqlServerDesignStrings.UnableToConvertDefaultValue(
column.DisplayName, column.DefaultValue,
property.ClrType, property.Name, property.DeclaringEntityType.Name));
}
}
return(propertyBuilder);
}
private PropertyBuilder VisitDefaultValue(ColumnModel column, PropertyBuilder propertyBuilder)
{
if (column.DefaultValue != null)
{
((Property)propertyBuilder.Metadata).SetValueGenerated(null, ConfigurationSource.Explicit);
propertyBuilder.Metadata.Relational().GeneratedValueSql = null;
var defaultExpression = ConvertSqlServerDefaultValue(column.DefaultValue);
if (defaultExpression != null)
{
if (!(defaultExpression == "NULL" &&
propertyBuilder.Metadata.ClrType.IsNullableType()))
{
propertyBuilder.HasDefaultValueSql(defaultExpression);
}
}
else
{
Logger.LogWarning(
SqlServerDesignStrings.CannotInterpretDefaultValue(
column.DisplayName,
column.DefaultValue,
propertyBuilder.Metadata.Name,
propertyBuilder.Metadata.DeclaringEntityType.Name));
}
}
return(propertyBuilder);
}
private void GetTables()
{
var command = _connection.CreateCommand();
command.CommandText =
"SELECT schema_name(t.schema_id) AS [schema], t.name FROM sys.tables AS t " +
$"WHERE t.name <> '{HistoryRepository.DefaultTableName}'" +
TemporalTableWhereClause;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var table = new TableModel
{
Database = _databaseModel,
SchemaName = reader.GetValueOrDefault <string>("schema"),
Name = reader.GetValueOrDefault <string>("name")
};
Logger.LogTrace(SqlServerDesignStrings.FoundTable(table.SchemaName, table.Name));
if (_tableSelectionSet.Allows(table.SchemaName, table.Name))
{
_databaseModel.Tables.Add(table);
_tables[TableKey(table)] = table;
}
else
{
Logger.LogTrace(
SqlServerDesignStrings.TableNotInSelectionSet(table.SchemaName, table.Name));
}
}
}
}
private PropertyBuilder VisitTypeMapping(PropertyBuilder propertyBuilder, ColumnModel column)
{
if (column.IsIdentity == true)
{
if (typeof(byte) == propertyBuilder.Metadata.ClrType)
{
Logger.LogWarning(
SqlServerDesignStrings.DataTypeDoesNotAllowSqlServerIdentityStrategy(
column.DisplayName, column.DataType));
}
else
{
propertyBuilder
.ValueGeneratedOnAdd()
.UseSqlServerIdentityColumn();
}
}
if (_dateTimePrecisionTypes.Contains(column.DataType) &&
column.Scale.HasValue &&
column.Scale != DefaultDateTimePrecision)
{
propertyBuilder.Metadata.SetMaxLength(null);
propertyBuilder.HasColumnType($"{column.DataType}({column.Scale})"); //not a typo: .Scale is the right property for datetime precision
}
// undo quirk in reverse type mapping to litters code with unnecessary nvarchar annotations
if (typeof(string) == propertyBuilder.Metadata.ClrType &&
propertyBuilder.Metadata.Relational().ColumnType == "nvarchar")
{
propertyBuilder.Metadata.Relational().ColumnType = null;
}
return(propertyBuilder);
}
private void VisitComputedValue(PropertyBuilder propertyBuilder, ColumnModel column)
{
if (column.ComputedValue != null)
{
propertyBuilder.Metadata.Relational().ComputedColumnSql = null;
var computedExpression = ConvertSqlServerDefaultValue(column.ComputedValue);
if (computedExpression != null)
{
if (!(computedExpression == "NULL" &&
propertyBuilder.Metadata.ClrType.IsNullableType()))
{
propertyBuilder.HasComputedColumnSql(computedExpression);
}
else
{
((Property)propertyBuilder.Metadata).SetValueGenerated(null, ConfigurationSource.Explicit);
}
}
else
{
Logger.LogWarning(
SqlServerDesignEventId.CannotInterpretComputedValueWarning,
() => SqlServerDesignStrings.CannotInterpretComputedValue(
column.DisplayName,
column.ComputedValue,
propertyBuilder.Metadata.Name,
propertyBuilder.Metadata.DeclaringEntityType.DisplayName()));
}
}
}
private ColumnModel FindColumnForForeignKey(
string columnName, TableModel table, string fkName)
{
ColumnModel column;
if (string.IsNullOrEmpty(columnName))
{
Logger.LogWarning(
SqlServerDesignEventId.ColumnNameEmptyOnForeignKey,
() => SqlServerDesignStrings.ColumnNameEmptyOnForeignKey(
table.SchemaName, table.Name, fkName));
return(null);
}
if (!_tableColumns.TryGetValue(
ColumnKey(table, columnName), out column))
{
Logger.LogWarning(
RelationalDesignEventId.ForeignKeyColumnsNotMappedWarning,
() => SqlServerDesignStrings.UnableToFindColumnForForeignKey(
fkName, columnName, table.SchemaName, table.Name));
return(null);
}
return(column);
}
private void GetSequences()
{
var command = _connection.CreateCommand();
command.CommandText = @"SELECT name,
is_cycling,
CAST(minimum_value AS bigint) as [minimum_value],
CAST(maximum_value AS bigint) as [maximum_value],
CAST(start_value AS bigint) as [start_value],
CAST(increment AS int) as [increment],
TYPE_NAME(user_type_id) as [type_name],
OBJECT_SCHEMA_NAME(object_id) AS [schema_name]
FROM sys.sequences";
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var sequence = new SequenceModel
{
Database = _databaseModel,
SchemaName = reader.GetValueOrDefault <string>("schema_name"),
Name = reader.GetValueOrDefault <string>("name"),
DataType = reader.GetValueOrDefault <string>("type_name"),
IsCyclic = reader.GetValueOrDefault <bool?>("is_cycling"),
IncrementBy = reader.GetValueOrDefault <int?>("increment"),
Start = reader.GetValueOrDefault <long?>("start_value"),
Min = reader.GetValueOrDefault <long?>("minimum_value"),
Max = reader.GetValueOrDefault <long?>("maximum_value")
};
Logger.LogDebug(
RelationalDesignEventId.FoundSequence,
() => SqlServerDesignStrings.FoundSequence(
sequence.SchemaName, sequence.Name, sequence.DataType, sequence.IsCyclic,
sequence.IncrementBy, sequence.Start, sequence.Min, sequence.Max));
if (string.IsNullOrEmpty(sequence.Name))
{
Logger.LogWarning(
RelationalDesignEventId.SequenceMustBeNamedWarning,
() => RelationalDesignStrings.SequencesRequireName);
continue;
}
if (_defaultSequenceMinMax.ContainsKey(sequence.DataType))
{
var defaultMin = _defaultSequenceMinMax[sequence.DataType][0];
sequence.Min = sequence.Min == defaultMin ? null : sequence.Min;
sequence.Start = sequence.Start == defaultMin ? null : sequence.Start;
var defaultMax = _defaultSequenceMinMax[sequence.DataType][1];
sequence.Max = sequence.Max == defaultMax ? null : sequence.Max;
}
_databaseModel.Sequences.Add(sequence);
}
}
}
private void GetSequences()
{
var command = _connection.CreateCommand();
command.CommandText = @"SELECT name,
is_cycling,
CAST(minimum_value AS bigint) as [minimum_value],
CAST(maximum_value AS bigint) as [maximum_value],
CAST(start_value AS bigint) as [start_value],
CAST(increment AS int) as [increment],
TYPE_NAME(user_type_id) as [type_name],
OBJECT_SCHEMA_NAME(object_id) AS [schema_name]
FROM sys.sequences";
using (var reader = command.ExecuteReader())
{
var dboIdx = reader.GetOrdinal("schema_name");
var typeIdx = reader.GetOrdinal("type_name");
var nameIdx = reader.GetOrdinal("name");
var cycleIdx = reader.GetOrdinal("is_cycling");
var minIdx = reader.GetOrdinal("minimum_value");
var maxIdx = reader.GetOrdinal("maximum_value");
var startIdx = reader.GetOrdinal("start_value");
var incrIdx = reader.GetOrdinal("increment");
while (reader.Read())
{
var sequence = new SequenceModel
{
SchemaName = reader.GetStringOrNull(dboIdx),
Name = reader.GetStringOrNull(nameIdx),
DataType = reader.GetStringOrNull(typeIdx),
IsCyclic = reader.GetBoolean(cycleIdx),
IncrementBy = reader.GetInt32(incrIdx),
Start = reader.GetInt64(startIdx),
Min = reader.GetInt64(minIdx),
Max = reader.GetInt64(maxIdx)
};
if (string.IsNullOrEmpty(sequence.Name))
{
Logger.LogWarning(SqlServerDesignStrings.SequenceNameEmpty(sequence.SchemaName));
continue;
}
if (_defaultSequenceMinMax.ContainsKey(sequence.DataType))
{
var defaultMin = _defaultSequenceMinMax[sequence.DataType][0];
sequence.Min = sequence.Min == defaultMin ? null : sequence.Min;
sequence.Start = sequence.Start == defaultMin ? null : sequence.Start;
var defaultMax = _defaultSequenceMinMax[sequence.DataType][1];
sequence.Max = sequence.Max == defaultMax ? null : sequence.Max;
}
_databaseModel.Sequences.Add(sequence);
}
}
}
public virtual void AddKeysToModel([NotNull] Entity.Metadata.Model relationalModel)
{
Check.NotNull(relationalModel, nameof(relationalModel));
var entityTypeToForeignKeyConstraintsMap =
new Dictionary <EntityType, Dictionary <string, List <Property> > >(); // string is ConstraintId
foreach (var entityType in relationalModel.EntityTypes)
{
var primaryKeyProperties = new List <Property>();
var constraints = new Dictionary <string, List <Property> >();
entityTypeToForeignKeyConstraintsMap[entityType] = constraints;
foreach (var property in entityType.Properties)
{
int primaryKeyOrdinal;
if (_primaryKeyOrdinals.TryGetValue(property.Name, out primaryKeyOrdinal))
{
primaryKeyProperties.Add(property);
}
Dictionary <string, int> foreignKeyConstraintIdOrdinalMap;
if (_foreignKeyOrdinals.TryGetValue(property.Name, out foreignKeyConstraintIdOrdinalMap))
{
// relationalProperty represents (part of) a foreign key
foreach (var constraintId in foreignKeyConstraintIdOrdinalMap.Keys)
{
List <Property> constraintProperties;
if (!constraints.TryGetValue(constraintId, out constraintProperties))
{
constraintProperties = new List <Property>();
constraints.Add(constraintId, constraintProperties);
}
constraintProperties.Add(property);
}
}
}
if (primaryKeyProperties.Count() > 0)
{
entityType.SetPrimaryKey(
primaryKeyProperties
.OrderBy(p => _primaryKeyOrdinals[p.Name]) // note: for relational property p.Name is its columnId
.ToList());
}
else
{
var errorMessage = SqlServerDesignStrings.NoPrimaryKeyColumns(
entityType.Relational().Schema,
entityType.Relational().TableName);
entityType.AddAnnotation(AnnotationNameEntityTypeError, errorMessage);
Logger.LogWarning(errorMessage);
}
}
AddForeignKeysToModel(relationalModel, entityTypeToForeignKeyConstraintsMap);
}
private void GetDefaultSchema()
{
var command = _connection.CreateCommand();
command.CommandText = "SELECT SCHEMA_NAME()";
var schema = command.ExecuteScalar() as string ?? "dbo";
Logger.LogTrace(SqlServerDesignStrings.FoundDefaultSchema(schema));
_databaseModel.DefaultSchemaName = schema;
}
private void GetTables()
{
var command = _connection.CreateCommand();
// for origin of the sys.extended_properties SELECT statement
// below see https://github.com/aspnet/EntityFramework/issues/5126
command.CommandText =
@"SELECT
schema_name(t.schema_id) AS [schema],
t.name" + MemoryOptimizedTableColumn + @"
FROM sys.tables AS t
WHERE t.is_ms_shipped = 0
AND NOT EXISTS (SELECT *
FROM sys.extended_properties
WHERE major_id = t.object_id
AND minor_id = 0
AND class = 1
AND name = N'microsoft_database_tools_support') " +
$"AND t.name <> '{HistoryRepository.DefaultTableName}'" + TemporalTableWhereClause; // Interpolation okay; strings
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var table = new TableModel
{
Database = _databaseModel,
SchemaName = reader.GetValueOrDefault <string>("schema"),
Name = reader.GetValueOrDefault <string>("name")
};
if (!string.IsNullOrEmpty(MemoryOptimizedTableColumn))
{
table[SqlServerFullAnnotationNames.Instance.MemoryOptimized] = reader.GetValueOrDefault <bool?>("is_memory_optimized");
}
Logger.LogDebug(
RelationalDesignEventId.FoundTable,
() => SqlServerDesignStrings.FoundTable(table.SchemaName, table.Name));
if (_tableSelectionSet.Allows(table.SchemaName, table.Name))
{
_databaseModel.Tables.Add(table);
_tables[TableKey(table)] = table;
}
else
{
Logger.LogDebug(
RelationalDesignEventId.TableSkipped,
() => SqlServerDesignStrings.TableNotInSelectionSet(table.SchemaName, table.Name));
}
}
}
}
public void E2ETest_UseAttributesInsteadOfFluentApi()
{
var configuration = new ReverseEngineeringConfiguration
{
ConnectionString = _connectionString,
ContextClassName = "AttributesContext",
ProjectPath = TestProjectDir + Path.DirectorySeparatorChar, // tests that ending DirectorySeparatorChar does not affect namespace
ProjectRootNamespace = TestNamespace,
OutputPath = TestSubDir,
TableSelectionSet = Filter
};
var filePaths = Generator.GenerateAsync(configuration).GetAwaiter().GetResult();
var actualFileSet = new FileSet(InMemoryFiles, Path.GetFullPath(Path.Combine(TestProjectDir, TestSubDir)))
{
Files = Enumerable.Repeat(filePaths.ContextFile, 1).Concat(filePaths.EntityTypeFiles).Select(Path.GetFileName).ToList()
};
var expectedFileSet = new FileSet(new FileSystemFileService(),
Path.Combine("ReverseEngineering", "ExpectedResults", "E2E_UseAttributesInsteadOfFluentApi"),
contents => contents.Replace("namespace " + TestNamespace, "namespace " + TestNamespace + "." + TestSubDir)
.Replace("{{connectionString}}", _connectionString))
{
Files = new List <string> {
"AttributesContext.expected"
}
.Concat(_expectedEntityTypeFiles).ToList()
};
AssertLog(new LoggerMessages
{
Warn =
{
RelationalDesignStrings.CannotFindTypeMappingForColumn("dbo.AllDataTypes.geographyColumn", "geography"),
RelationalDesignStrings.CannotFindTypeMappingForColumn("dbo.AllDataTypes.geometryColumn", "geometry"),
RelationalDesignStrings.CannotFindTypeMappingForColumn("dbo.AllDataTypes.hierarchyidColumn", "hierarchyid"),
RelationalDesignStrings.CannotFindTypeMappingForColumn("dbo.AllDataTypes.sql_variantColumn", "sql_variant"),
RelationalDesignStrings.CannotFindTypeMappingForColumn("dbo.AllDataTypes.xmlColumn", "xml"),
RelationalDesignStrings.UnableToScaffoldIndexMissingProperty("IX_UnscaffoldableIndex"),
SqlServerDesignStrings.DataTypeDoesNotAllowSqlServerIdentityStrategy("dbo.PropertyConfiguration.PropertyConfigurationID", "tinyint"),
RelationalDesignStrings.CannotFindTypeMappingForColumn("dbo.TableWithUnmappablePrimaryKeyColumn.TableWithUnmappablePrimaryKeyColumnID", "hierarchyid"),
RelationalDesignStrings.PrimaryKeyErrorPropertyNotFound("dbo.TableWithUnmappablePrimaryKeyColumn"),
RelationalDesignStrings.UnableToGenerateEntityType("dbo.TableWithUnmappablePrimaryKeyColumn")
}
});
AssertEqualFileContents(expectedFileSet, actualFileSet);
AssertCompile(actualFileSet);
}
public void E2ETest_AllFluentApi()
{
var configuration = new ReverseEngineeringConfiguration
{
ConnectionString = _connectionString,
ProjectPath = TestProjectDir,
ProjectRootNamespace = TestNamespace,
OutputPath = null, // not used for this test
UseFluentApiOnly = true,
TableSelectionSet = Filter,
};
var filePaths = Generator.GenerateAsync(configuration).GetAwaiter().GetResult();
var actualFileSet = new FileSet(InMemoryFiles, Path.GetFullPath(TestProjectDir))
{
Files = Enumerable.Repeat(filePaths.ContextFile, 1).Concat(filePaths.EntityTypeFiles).Select(Path.GetFileName).ToList()
};
var expectedFileSet = new FileSet(new FileSystemFileService(),
Path.Combine("ReverseEngineering", "ExpectedResults", "E2E_AllFluentApi"),
inputFile => inputFile.Replace("{{connectionString}}", _connectionString))
{
Files = (new List <string> {
"SqlServerReverseEngineerTestE2EContext.expected"
})
.Concat(_expectedEntityTypeFiles).ToList()
};
AssertLog(new LoggerMessages
{
Warn =
{
RelationalDesignStrings.CannotFindTypeMappingForColumn("dbo.AllDataTypes.geographyColumn", "geography"),
RelationalDesignStrings.CannotFindTypeMappingForColumn("dbo.AllDataTypes.geometryColumn", "geometry"),
RelationalDesignStrings.CannotFindTypeMappingForColumn("dbo.AllDataTypes.hierarchyidColumn", "hierarchyid"),
RelationalDesignStrings.CannotFindTypeMappingForColumn("dbo.AllDataTypes.sql_variantColumn", "sql_variant"),
RelationalDesignStrings.CannotFindTypeMappingForColumn("dbo.AllDataTypes.xmlColumn", "xml"),
RelationalDesignStrings.UnableToScaffoldIndexMissingProperty("IX_UnscaffoldableIndex"),
SqlServerDesignStrings.DataTypeDoesNotAllowSqlServerIdentityStrategy("dbo.PropertyConfiguration.PropertyConfigurationID", "tinyint"),
RelationalDesignStrings.CannotFindTypeMappingForColumn("dbo.TableWithUnmappablePrimaryKeyColumn.TableWithUnmappablePrimaryKeyColumnID", "hierarchyid"),
RelationalDesignStrings.PrimaryKeyErrorPropertyNotFound("dbo.TableWithUnmappablePrimaryKeyColumn"),
RelationalDesignStrings.UnableToGenerateEntityType("dbo.TableWithUnmappablePrimaryKeyColumn"),
}
});
AssertEqualFileContents(expectedFileSet, actualFileSet);
AssertCompile(actualFileSet);
}
private void VisitTypeMapping(PropertyBuilder propertyBuilder, ColumnModel column)
{
if (column.SqlServer().IsIdentity)
{
if (typeof(byte) == propertyBuilder.Metadata.ClrType)
{
Logger.LogWarning(
SqlServerDesignEventId.DataTypeDoesNotAllowSqlServerIdentityStrategyWarning,
() => SqlServerDesignStrings.DataTypeDoesNotAllowSqlServerIdentityStrategy(
column.DisplayName, column.DataType));
}
else
{
propertyBuilder
.ValueGeneratedOnAdd()
.UseSqlServerIdentityColumn();
}
}
var dateTimePrecision = column.SqlServer().DateTimePrecision;
if (dateTimePrecision.HasValue &&
dateTimePrecision.Value != DefaultTimeTimePrecision)
{
propertyBuilder.Metadata.SetMaxLength(null);
propertyBuilder.HasColumnType(
string.Format(CultureInfo.InvariantCulture, "{0}({1})", column.DataType, dateTimePrecision.Value));
}
else if (!HasTypeAlias(column))
{
var qualifiedColumnTypeAndMaxLength =
MaxLengthQualifiedDataType(column.DataType, column.MaxLength);
if (qualifiedColumnTypeAndMaxLength != null)
{
propertyBuilder.HasColumnType(qualifiedColumnTypeAndMaxLength.Item1);
propertyBuilder.Metadata.SetMaxLength(qualifiedColumnTypeAndMaxLength.Item2);
}
}
var columnType = propertyBuilder.Metadata.SqlServer().ColumnType;
if (columnType != null)
{
TypeMapper.ValidateTypeName(columnType);
}
}
private void GetTypeAliases()
{
var command = _connection.CreateCommand();
command.CommandText = @"SELECT
[schema_name],
[type_name],
[underlying_system_type]
FROM
(SELECT
s1.[name] as [schema_name],
t1.[name] as [type_name],
( CASE WHEN t1.[xusertype] = t1.[xtype] THEN NULL
ELSE
( SELECT t2.[name]
FROM [sys].[systypes] AS t2
WHERE t2.[xusertype] = t2.[xtype]
AND t2.[xusertype] = t1.[xtype] )
END) as [underlying_system_type]
FROM [sys].[systypes] AS t1
LEFT JOIN [sys].[types] AS t3
ON t1.[xusertype] = t3.[user_type_id] AND t1.[xtype] = t3.[system_type_id]
LEFT JOIN [sys].[schemas] AS s1
ON t3.[schema_id] = s1.[schema_id]
) AS t
WHERE [underlying_system_type] IS NOT NULL";
var typeAliasMap = new Dictionary <string, string>(StringComparer.OrdinalIgnoreCase);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var aliasSchema = reader.GetValueOrDefault <string>("schema_name");
var alias = reader.GetValueOrDefault <string>("type_name");
var underlyingSystemType = reader.GetValueOrDefault <string>("underlying_system_type");
Logger.LogDebug(
SqlServerDesignEventId.FoundTypeAlias,
() => SqlServerDesignStrings.FoundTypeAlias(aliasSchema, alias, underlyingSystemType));
typeAliasMap.Add(SchemaQualifiedKey(alias, aliasSchema), underlyingSystemType);
}
}
_databaseModel.SqlServer().TypeAliases = typeAliasMap;
}
private void GetTables()
{
var command = _connection.CreateCommand();
// for origin of the sys.extended_properties SELECT statement
// below see https://github.com/aspnet/EntityFramework/issues/5126
command.CommandText =
@"SELECT
schema_name(t.schema_id) AS [schema],
t.name
FROM sys.tables AS t
WHERE t.is_ms_shipped = 0
AND NOT EXISTS (SELECT *
FROM sys.extended_properties
WHERE major_id = t.object_id
AND minor_id = 0
AND class = 1
AND name = N'microsoft_database_tools_support') " +
$"AND t.name <> '{HistoryRepository.DefaultTableName}'" + TemporalTableWhereClause;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var table = new TableModel
{
Database = _databaseModel,
SchemaName = reader.GetValueOrDefault <string>("schema"),
Name = reader.GetValueOrDefault <string>("name")
};
Logger.LogTrace(SqlServerDesignStrings.FoundTable(table.SchemaName, table.Name));
if (_tableSelectionSet.Allows(table.SchemaName, table.Name))
{
_databaseModel.Tables.Add(table);
_tables[TableKey(table)] = table;
}
else
{
Logger.LogTrace(
SqlServerDesignStrings.TableNotInSelectionSet(table.SchemaName, table.Name));
}
}
}
}
public virtual void AddPropertiesToModel([NotNull] Entity.Metadata.Model relationalModel)
{
Check.NotNull(relationalModel, nameof(relationalModel));
foreach (var tc in _tableColumns.Values)
{
var table = _tables[tc.TableId];
if (!_tableSelectionSet.Allows(table.SchemaName, table.TableName))
{
continue;
}
EntityType entityType;
if (!_tableIdToEntityType.TryGetValue(tc.TableId, out entityType))
{
Logger.LogWarning(
SqlServerDesignStrings.CannotFindTableForColumn(tc.Id, tc.TableId));
continue;
}
// If we come across a column with a SQL Server type which we can't map we will ignore it.
// Note: foreign key properties appear just like any other property in the relational model.
Type clrPropertyType;
if (!SqlServerTypeMapping._sqlTypeToClrTypeMap.TryGetValue(tc.DataType, out clrPropertyType))
{
Logger.LogWarning(
SqlServerDesignStrings.CannotFindTypeMappingForColumn(tc.Id, tc.DataType));
continue;
}
if (tc.IsNullable)
{
clrPropertyType = clrPropertyType.MakeNullable();
}
var property = entityType.AddProperty(tc.Id, clrPropertyType);
property.Relational().ColumnName = _tableColumns[tc.Id].ColumnName;
_columnIdToProperty[tc.Id] = property;
AddFacetsOnProperty(property, _tableColumns[tc.Id]);
}
}
public virtual Property FindTargetProperty(
[NotNull] string foreignKeyConstraintId, [NotNull] string fromColumnId)
{
Check.NotEmpty(foreignKeyConstraintId, nameof(foreignKeyConstraintId));
Check.NotEmpty(fromColumnId, nameof(fromColumnId));
ForeignKeyColumnMapping foreignKeyColumnMapping;
if (!_foreignKeyColumnMappings.TryGetValue(
foreignKeyConstraintId + fromColumnId, out foreignKeyColumnMapping))
{
Logger.LogWarning(
SqlServerDesignStrings.CannotFindForeignKeyMappingForConstraintId(
foreignKeyConstraintId, fromColumnId));
return(null);
}
var toTable = _tables[_tableColumns[foreignKeyColumnMapping.ToColumnId].TableId];
if (!_tableSelectionSet.Allows(toTable.SchemaName, toTable.TableName))
{
// target property belongs to a table which was excluded by the TableSelectionSet
return(null);
}
Property toColumnRelationalProperty;
if (!_columnIdToProperty.TryGetValue(
foreignKeyColumnMapping.ToColumnId, out toColumnRelationalProperty))
{
Logger.LogWarning(
SqlServerDesignStrings.CannotFindRelationalPropertyForColumnId(
foreignKeyConstraintId, foreignKeyColumnMapping.ToColumnId));
return(null);
}
return(toColumnRelationalProperty);
}
private void GetTypeAliases()
{
var command = _connection.CreateCommand();
command.CommandText = @"SELECT
[type_name],
[underlying_system_type]
FROM
(SELECT
t1.[name] as [type_name],
( CASE WHEN t1.[xusertype] = t1.[xtype] THEN NULL
ELSE
( SELECT t2.[name]
FROM [sys].[systypes] AS t2
WHERE t2.[xusertype] = t2.[xtype]
AND t2.[xusertype] = t1.[xtype] )
END) as [underlying_system_type]
FROM [sys].[systypes] AS t1
) AS t
WHERE [underlying_system_type] IS NOT NULL";
var typeAliasMap = new Dictionary <string, string>(StringComparer.OrdinalIgnoreCase);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var alias = reader.GetValueOrDefault <string>("type_name");
var underlyingSystemType = reader.GetValueOrDefault <string>("underlying_system_type");
Logger.LogTrace(SqlServerDesignStrings.FoundTypeAlias(alias, underlyingSystemType));
typeAliasMap.Add(alias, underlyingSystemType);
}
}
_databaseModel.SqlServer().TypeAliases = typeAliasMap;
}
public virtual void AddFacetsOnProperty(
[NotNull] Property property, [NotNull] TableColumn tableColumn)
{
Check.NotNull(property, nameof(property));
Check.NotNull(tableColumn, nameof(tableColumn));
property.IsNullable = tableColumn.IsNullable;
if (property.Name != tableColumn.ColumnName)
{
property.Relational().ColumnName = tableColumn.ColumnName;
}
string typeName = null;
if (DataTypesForNumericPrecisionAndScale.Contains(tableColumn.DataType))
{
if (tableColumn.NumericPrecision.HasValue)
{
if (tableColumn.Scale.HasValue)
{
typeName = tableColumn.DataType + "(" + tableColumn.NumericPrecision.Value + ", " + tableColumn.Scale.Value + ")";
}
else
{
typeName = tableColumn.DataType + "(" + tableColumn.NumericPrecision.Value + ")";
}
}
}
else if (DataTypesForDateTimePrecisionAndScale.Contains(tableColumn.DataType))
{
if (tableColumn.DateTimePrecision.HasValue)
{
if (tableColumn.Scale.HasValue)
{
typeName = tableColumn.DataType + "(" + tableColumn.DateTimePrecision.Value + ", " + tableColumn.Scale.Value + ")";
}
else
{
typeName = tableColumn.DataType + "(" + tableColumn.DateTimePrecision.Value + ")";
}
}
}
if (typeName != null)
{
property.Relational().ColumnType = typeName;
}
if (tableColumn.IsIdentity)
{
property.ValueGenerated = ValueGenerated.OnAdd;
if (typeof(byte) == SqlServerTypeMapping._sqlTypeToClrTypeMap[tableColumn.DataType])
{
Logger.LogWarning(
SqlServerDesignStrings.DataTypeDoesNotAllowSqlServerIdentityStrategy(tableColumn.Id, tableColumn.DataType));
}
else
{
property.SqlServer().ValueGenerationStrategy = SqlServerValueGenerationStrategy.IdentityColumn;
}
}
if (tableColumn.IsStoreGenerated ||
tableColumn.DataType == "timestamp")
{
// timestamp columns should always be treated as store generated
// (rowversion columns are stored as data type 'timestamp')
property.ValueGenerated = ValueGenerated.OnAddOrUpdate;
}
if (tableColumn.DefaultValue != null)
{
var defaultExpressionOrValue =
_sqlServerLiteralUtilities
.ConvertSqlServerDefaultValue(
((IProperty)property).ClrType, tableColumn.DefaultValue);
if (defaultExpressionOrValue != null &&
defaultExpressionOrValue.DefaultExpression != null)
{
property.Relational().GeneratedValueSql = defaultExpressionOrValue.DefaultExpression;
}
else if (defaultExpressionOrValue != null &&
defaultExpressionOrValue.DefaultValue != null)
{
property.Relational().DefaultValue = defaultExpressionOrValue.DefaultValue;
}
else
{
Logger.LogWarning(
SqlServerDesignStrings.UnableToConvertDefaultValue(
tableColumn.Id, tableColumn.DefaultValue,
((IProperty)property).ClrType, property.Name, property.DeclaringEntityType.Name));
}
}
}
private void GetForeignKeys()
{
var command = _connection.CreateCommand();
command.CommandText = @"SELECT
schema_name(f.schema_id) AS [schema_name],
object_name(f.parent_object_id) AS table_name,
f.name AS foreign_key_name,
object_schema_name(f.referenced_object_id) AS principal_table_schema_name,
object_name(f.referenced_object_id) AS principal_table_name,
col_name(fc.parent_object_id, fc.parent_column_id) AS constraint_column_name,
col_name(fc.referenced_object_id, fc.referenced_column_id) AS referenced_column_name,
is_disabled,
delete_referential_action_desc,
update_referential_action_desc,
fc.constraint_column_id
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.object_id = fc.constraint_object_id
ORDER BY schema_name(f.schema_id), object_name(f.parent_object_id), f.name";
using (var reader = command.ExecuteReader())
{
var lastFkName = string.Empty;
var lastFkSchemaName = string.Empty;
var lastFkTableName = string.Empty;
ForeignKeyModel fkInfo = null;
while (reader.Read())
{
var schemaName = reader.GetString(0);
var tableName = reader.GetString(1);
var fkName = reader.GetStringOrNull(2);
if (string.IsNullOrEmpty(fkName))
{
Logger.LogWarning(SqlServerDesignStrings.ForeignKeyNameEmpty(schemaName, tableName));
continue;
}
if (!_tableSelectionSet.Allows(schemaName, tableName))
{
continue;
}
if (fkInfo == null ||
lastFkSchemaName != schemaName ||
lastFkTableName != tableName ||
lastFkName != fkName)
{
lastFkName = fkName;
lastFkSchemaName = schemaName;
lastFkTableName = tableName;
var table = _tables[TableKey(tableName, schemaName)];
var principalSchemaTableName = reader.GetStringOrNull(3);
var principalTableName = reader.GetStringOrNull(4);
TableModel principalTable = null;
if (!string.IsNullOrEmpty(principalSchemaTableName) &&
!string.IsNullOrEmpty(principalTableName))
{
_tables.TryGetValue(TableKey(principalTableName, principalSchemaTableName), out principalTable);
}
fkInfo = new ForeignKeyModel
{
Name = fkName,
Table = table,
PrincipalTable = principalTable,
OnDelete = ConvertToReferentialAction(reader.GetStringOrNull(8))
};
table.ForeignKeys.Add(fkInfo);
}
var fkColumn = new ForeignKeyColumnModel
{
Ordinal = reader.GetInt32(10)
};
var fromColumnName = reader.GetStringOrNull(5);
ColumnModel fromColumn;
if ((fromColumn = FindColumnForForeignKey(fromColumnName, fkInfo.Table, fkName)) != null)
{
fkColumn.Column = fromColumn;
}
if (fkInfo.PrincipalTable != null)
{
var toColumnName = reader.GetString(6);
ColumnModel toColumn;
if ((toColumn = FindColumnForForeignKey(toColumnName, fkInfo.PrincipalTable, fkName)) != null)
{
fkColumn.PrincipalColumn = toColumn;
}
}
fkInfo.Columns.Add(fkColumn);
}
}
}
private void GetColumns()
{
var command = _connection.CreateCommand();
command.CommandText = @"SELECT DISTINCT
schema_name(t.schema_id) AS [schema],
t.name AS [table],
type_name(c.user_type_id) AS [typename],
c.name AS [column_name],
c.column_id AS [ordinal],
c.is_nullable AS [nullable],
CAST(ic.key_ordinal AS int) AS [primary_key_ordinal],
object_definition(c.default_object_id) AS [default_sql],
CAST(CASE WHEN c.precision <> tp.precision
THEN c.precision
ELSE null
END AS int) AS [precision],
CAST(CASE WHEN c.scale <> tp.scale
THEN c.scale
ELSE null
END AS int) AS [scale],
CAST(CASE WHEN c.max_length <> tp.max_length
THEN c.max_length
ELSE null
END AS int) AS [max_length],
c.is_identity,
c.is_computed
FROM sys.index_columns ic
RIGHT JOIN (SELECT * FROM sys.indexes WHERE is_primary_key = 1) AS i ON i.object_id = ic.object_id AND i.index_id = ic.index_id
RIGHT JOIN sys.columns c ON ic.object_id = c.object_id AND c.column_id = ic.column_id
RIGHT JOIN sys.types tp ON tp.user_type_id = c.user_type_id
JOIN sys.tables AS t ON t.object_id = c.object_id
WHERE t.name <> '" + HistoryRepository.DefaultTableName + "'";
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var schemaName = reader.GetString(0);
var tableName = reader.GetString(1);
var columnName = reader.GetStringOrNull(3);
if (!_tableSelectionSet.Allows(schemaName, tableName))
{
continue;
}
if (string.IsNullOrEmpty(columnName))
{
Logger.LogWarning(SqlServerDesignStrings.ColumnNameEmptyOnTable(schemaName, tableName));
continue;
}
TableModel table;
if (!_tables.TryGetValue(TableKey(tableName, schemaName), out table))
{
Logger.LogWarning(
SqlServerDesignStrings.UnableToFindTableForColumn(columnName, schemaName, tableName));
continue;
}
var dataTypeName = reader.GetString(2);
var nullable = reader.IsDBNull(5) ? false : reader.GetBoolean(5);
var scale = reader.IsDBNull(9) ? default(int?) : reader.GetInt32(9);
var maxLength = reader.IsDBNull(10) ? default(int?) : reader.GetInt32(10);
var dateTimePrecision = default(int?);
if (dataTypeName == "nvarchar" ||
dataTypeName == "nchar")
{
maxLength /= 2;
}
if (dataTypeName == "decimal" ||
dataTypeName == "numeric")
{
// maxlength here represents storage bytes. The server determines this, not the client.
maxLength = null;
}
if (_dateTimePrecisionTypes.Contains(dataTypeName))
{
dateTimePrecision = scale ?? DefaultDateTimePrecision;
scale = null;
}
var isIdentity = !reader.IsDBNull(11) && reader.GetBoolean(11);
var isComputed = reader.GetBoolean(12) || dataTypeName == "timestamp";
var column = new ColumnModel
{
Table = table,
DataType = dataTypeName,
Name = columnName,
Ordinal = reader.GetInt32(4) - 1,
IsNullable = nullable,
PrimaryKeyOrdinal = reader.IsDBNull(6) ? default(int?) : reader.GetInt32(6),
DefaultValue = reader.IsDBNull(7) ? null : reader.GetString(7),
Precision = reader.IsDBNull(8) ? default(int?) : reader.GetInt32(8),
Scale = scale,
MaxLength = maxLength <= 0 ? default(int?) : maxLength,
ValueGenerated = isIdentity ?
ValueGenerated.OnAdd :
isComputed ?
ValueGenerated.OnAddOrUpdate : default(ValueGenerated?)
};
column.SqlServer().IsIdentity = isIdentity;
column.SqlServer().DateTimePrecision = dateTimePrecision;
table.Columns.Add(column);
_tableColumns.Add(ColumnKey(table, column.Name), column);
}
}
}
private void GetIndexes()
{
var command = _connection.CreateCommand();
command.CommandText = @"SELECT
object_schema_name(i.object_id) AS [schema_name],
object_name(i.object_id) AS [table_name],
i.name AS [index_name],
i.is_unique,
c.name AS [column_name],
i.type_desc,
ic.key_ordinal
FROM sys.indexes i
INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN sys.columns c ON ic.object_id = c.object_id AND c.column_id = ic.column_id
WHERE object_schema_name(i.object_id) <> 'sys'
AND object_name(i.object_id) <> '" + HistoryRepository.DefaultTableName + @"'
ORDER BY object_schema_name(i.object_id), object_name(i.object_id), i.name, ic.key_ordinal";
using (var reader = command.ExecuteReader())
{
IndexModel index = null;
while (reader.Read())
{
var schemaName = reader.GetString(0);
var tableName = reader.GetString(1);
var indexName = reader.GetStringOrNull(2);
if (!_tableSelectionSet.Allows(schemaName, tableName))
{
continue;
}
if (string.IsNullOrEmpty(indexName))
{
Logger.LogWarning(SqlServerDesignStrings.IndexNameEmpty(schemaName, tableName));
continue;
}
if (index == null ||
index.Name != indexName ||
index.Table.Name != tableName ||
index.Table.SchemaName != schemaName)
{
TableModel table;
if (!_tables.TryGetValue(TableKey(tableName, schemaName), out table))
{
Logger.LogWarning(
SqlServerDesignStrings.UnableToFindTableForIndex(indexName, schemaName, tableName));
continue;
}
index = new IndexModel
{
Table = table,
Name = indexName,
IsUnique = reader.IsDBNull(3) ? false : reader.GetBoolean(3),
};
index.SqlServer().IsClustered = reader.GetStringOrNull(5) == "CLUSTERED";
table.Indexes.Add(index);
}
var columnName = reader.GetStringOrNull(4);
var indexOrdinal = reader.GetByte(6);
ColumnModel column = null;
if (string.IsNullOrEmpty(columnName))
{
Logger.LogWarning(
SqlServerDesignStrings.ColumnNameEmptyOnIndex(
schemaName, tableName, indexName));
}
else if (!_tableColumns.TryGetValue(ColumnKey(index.Table, columnName), out column))
{
Logger.LogWarning(
SqlServerDesignStrings.UnableToFindColumnForIndex(
indexName, columnName, schemaName, tableName));
}
else
{
var indexColumn = new IndexColumnModel
{
Index = index,
Column = column,
Ordinal = indexOrdinal
};
index.IndexColumns.Add(indexColumn);
}
}
}
}
private void GetColumns()
{
var command = _connection.CreateCommand();
command.CommandText = @"SELECT DISTINCT
schema_name(t.schema_id) AS [schema],
t.name AS [table],
type_name(c.user_type_id) AS [typename],
c.name AS [column_name],
c.column_id AS [ordinal],
c.is_nullable AS [nullable],
CAST(ic.key_ordinal AS int) AS [primary_key_ordinal],
object_definition(c.default_object_id) AS [default_sql],
cc.definition AS [computed_sql],
CAST(CASE WHEN c.precision <> tp.precision
THEN c.precision
ELSE null
END AS int) AS [precision],
CAST(CASE WHEN c.scale <> tp.scale
THEN c.scale
ELSE null
END AS int) AS [scale],
CAST(CASE WHEN c.max_length <> tp.max_length
THEN c.max_length
ELSE null
END AS int) AS [max_length],
c.is_identity,
c.is_computed
FROM sys.index_columns ic
RIGHT JOIN (SELECT * FROM sys.indexes WHERE is_primary_key = 1) AS i ON i.object_id = ic.object_id AND i.index_id = ic.index_id
RIGHT JOIN sys.columns c ON ic.object_id = c.object_id AND c.column_id = ic.column_id
RIGHT JOIN sys.types tp ON tp.user_type_id = c.user_type_id
LEFT JOIN sys.computed_columns cc ON cc.object_id = c.object_id AND cc.column_id = c.column_id
JOIN sys.tables AS t ON t.object_id = c.object_id
WHERE t.name <> '" + HistoryRepository.DefaultTableName + "'" +
TemporalTableWhereClause;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var schemaName = reader.GetValueOrDefault <string>("schema");
var tableName = reader.GetValueOrDefault <string>("table");
var columnName = reader.GetValueOrDefault <string>("column_name");
var dataTypeName = reader.GetValueOrDefault <string>("typename");
var ordinal = reader.GetValueOrDefault <int>("ordinal");
var nullable = reader.GetValueOrDefault <bool>("nullable");
var primaryKeyOrdinal = reader.GetValueOrDefault <int?>("primary_key_ordinal");
var defaultValue = reader.GetValueOrDefault <string>("default_sql");
var computedValue = reader.GetValueOrDefault <string>("computed_sql");
var precision = reader.GetValueOrDefault <int?>("precision");
var scale = reader.GetValueOrDefault <int?>("scale");
var maxLength = reader.GetValueOrDefault <int?>("max_length");
var isIdentity = reader.GetValueOrDefault <bool>("is_identity");
var isComputed = reader.GetValueOrDefault <bool>("is_computed");
Logger.LogDebug(
RelationalDesignEventId.FoundColumn,
() => SqlServerDesignStrings.FoundColumn(
schemaName, tableName, columnName, dataTypeName, ordinal, nullable,
primaryKeyOrdinal, defaultValue, computedValue, precision, scale, maxLength, isIdentity, isComputed));
if (!_tableSelectionSet.Allows(schemaName, tableName))
{
Logger.LogDebug(
RelationalDesignEventId.ColumnSkipped,
() => SqlServerDesignStrings.ColumnNotInSelectionSet(columnName, schemaName, tableName));
continue;
}
if (string.IsNullOrEmpty(columnName))
{
Logger.LogWarning(
SqlServerDesignEventId.ColumnMustBeNamedWarning,
() => SqlServerDesignStrings.ColumnNameEmptyOnTable(schemaName, tableName));
continue;
}
TableModel table;
if (!_tables.TryGetValue(TableKey(tableName, schemaName), out table))
{
Logger.LogWarning(
RelationalDesignEventId.MissingTableWarning,
() => SqlServerDesignStrings.UnableToFindTableForColumn(columnName, schemaName, tableName));
continue;
}
if (dataTypeName == "nvarchar" ||
dataTypeName == "nchar")
{
maxLength /= 2;
}
if (dataTypeName == "decimal" ||
dataTypeName == "numeric")
{
// maxlength here represents storage bytes. The server determines this, not the client.
maxLength = null;
}
var dateTimePrecision = default(int?);
if (_dateTimePrecisionTypes.Contains(dataTypeName))
{
dateTimePrecision = scale ?? DefaultDateTimePrecision;
scale = null;
}
var column = new ColumnModel
{
Table = table,
DataType = dataTypeName,
Name = columnName,
Ordinal = ordinal - 1,
IsNullable = nullable,
PrimaryKeyOrdinal = primaryKeyOrdinal,
DefaultValue = defaultValue,
ComputedValue = computedValue,
Precision = precision,
Scale = scale,
MaxLength = maxLength <= 0 ? default(int?) : maxLength,
ValueGenerated = isIdentity
? ValueGenerated.OnAdd
: isComputed || dataTypeName == "timestamp"
? ValueGenerated.OnAddOrUpdate
: default(ValueGenerated?)
};
column.SqlServer().IsIdentity = isIdentity;
column.SqlServer().DateTimePrecision = dateTimePrecision;
table.Columns.Add(column);
_tableColumns.Add(ColumnKey(table, column.Name), column);
}
}
}
private void GetForeignKeys()
{
var command = _connection.CreateCommand();
command.CommandText = @"SELECT
schema_name(f.schema_id) AS [schema_name],
object_name(f.parent_object_id) AS table_name,
f.name AS foreign_key_name,
object_schema_name(f.referenced_object_id) AS principal_table_schema_name,
object_name(f.referenced_object_id) AS principal_table_name,
col_name(fc.parent_object_id, fc.parent_column_id) AS constraint_column_name,
col_name(fc.referenced_object_id, fc.referenced_column_id) AS referenced_column_name,
is_disabled,
delete_referential_action_desc,
update_referential_action_desc,
fc.constraint_column_id
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.object_id = fc.constraint_object_id
ORDER BY schema_name(f.schema_id), object_name(f.parent_object_id), f.name";
using (var reader = command.ExecuteReader())
{
var lastFkName = string.Empty;
var lastFkSchemaName = string.Empty;
var lastFkTableName = string.Empty;
ForeignKeyModel fkInfo = null;
while (reader.Read())
{
var schemaName = reader.GetValueOrDefault <string>("schema_name");
var tableName = reader.GetValueOrDefault <string>("table_name");
var fkName = reader.GetValueOrDefault <string>("foreign_key_name");
var principalTableSchemaName = reader.GetValueOrDefault <string>("principal_table_schema_name");
var principalTableName = reader.GetValueOrDefault <string>("principal_table_name");
var fromColumnName = reader.GetValueOrDefault <string>("constraint_column_name");
var toColumnName = reader.GetValueOrDefault <string>("referenced_column_name");
var updateAction = reader.GetValueOrDefault <string>("update_referential_action_desc");
var deleteAction = reader.GetValueOrDefault <string>("delete_referential_action_desc");
var ordinal = reader.GetValueOrDefault <int>("constraint_column_id");
Logger.LogDebug(
RelationalDesignEventId.FoundForeignKeyColumn,
() => SqlServerDesignStrings.FoundForeignKeyColumn(
schemaName, tableName, fkName, principalTableSchemaName, principalTableName,
fromColumnName, toColumnName, updateAction, deleteAction, ordinal));
if (string.IsNullOrEmpty(fkName))
{
Logger.LogWarning(
SqlServerDesignEventId.ForeignKeyMustBeNamedWarning,
() => SqlServerDesignStrings.ForeignKeyNameEmpty(schemaName, tableName));
continue;
}
if (!_tableSelectionSet.Allows(schemaName, tableName))
{
Logger.LogDebug(
SqlServerDesignEventId.ForeignKeyColumnSkipped,
() => SqlServerDesignStrings.ForeignKeyColumnNotInSelectionSet(
fromColumnName, fkName, schemaName, tableName));
continue;
}
if (fkInfo == null ||
lastFkSchemaName != schemaName ||
lastFkTableName != tableName ||
lastFkName != fkName)
{
lastFkName = fkName;
lastFkSchemaName = schemaName;
lastFkTableName = tableName;
var table = _tables[TableKey(tableName, schemaName)];
TableModel principalTable = null;
if (!string.IsNullOrEmpty(principalTableSchemaName) &&
!string.IsNullOrEmpty(principalTableName))
{
_tables.TryGetValue(TableKey(principalTableName, principalTableSchemaName), out principalTable);
}
if (principalTable == null)
{
Logger.LogDebug(
RelationalDesignEventId.ForeignKeyReferencesMissingTable,
() => SqlServerDesignStrings.PrincipalTableNotInSelectionSet(
fkName, schemaName, tableName, principalTableSchemaName, principalTableName));
}
fkInfo = new ForeignKeyModel
{
Name = fkName,
Table = table,
PrincipalTable = principalTable,
OnDelete = ConvertToReferentialAction(deleteAction)
};
table.ForeignKeys.Add(fkInfo);
}
var fkColumn = new ForeignKeyColumnModel
{
Ordinal = ordinal
};
ColumnModel fromColumn;
if ((fromColumn = FindColumnForForeignKey(fromColumnName, fkInfo.Table, fkName)) != null)
{
fkColumn.Column = fromColumn;
}
if (fkInfo.PrincipalTable != null)
{
ColumnModel toColumn;
if ((toColumn = FindColumnForForeignKey(toColumnName, fkInfo.PrincipalTable, fkName)) != null)
{
fkColumn.PrincipalColumn = toColumn;
}
}
fkInfo.Columns.Add(fkColumn);
}
}
}
private void GetIndexes()
{
var command = _connection.CreateCommand();
command.CommandText = @"SELECT
object_schema_name(i.object_id) AS [schema_name],
object_name(i.object_id) AS [table_name],
i.name AS [index_name],
i.is_unique,
c.name AS [column_name],
i.type_desc,
ic.key_ordinal
FROM sys.indexes i
INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN sys.columns c ON ic.object_id = c.object_id AND c.column_id = ic.column_id
INNER JOIN sys.tables t ON t.object_id = i.object_id
WHERE object_schema_name(i.object_id) <> 'sys'
AND object_name(i.object_id) <> '" + HistoryRepository.DefaultTableName + @"'" +
TemporalTableWhereClause + @"
ORDER BY object_schema_name(i.object_id), object_name(i.object_id), i.name, ic.key_ordinal";
using (var reader = command.ExecuteReader())
{
IndexModel index = null;
while (reader.Read())
{
var schemaName = reader.GetValueOrDefault <string>("schema_name");
var tableName = reader.GetValueOrDefault <string>("table_name");
var indexName = reader.GetValueOrDefault <string>("index_name");
var isUnique = reader.GetValueOrDefault <bool>("is_unique");
var typeDesc = reader.GetValueOrDefault <string>("type_desc");
var columnName = reader.GetValueOrDefault <string>("column_name");
var indexOrdinal = reader.GetValueOrDefault <byte>("key_ordinal");
Logger.LogDebug(
RelationalDesignEventId.FoundIndexColumn,
() => SqlServerDesignStrings.FoundIndexColumn(
schemaName, tableName, indexName, isUnique, typeDesc, columnName, indexOrdinal));
if (!_tableSelectionSet.Allows(schemaName, tableName))
{
Logger.LogDebug(
RelationalDesignEventId.IndexColumnSkipped,
() => SqlServerDesignStrings.IndexColumnNotInSelectionSet(
columnName, indexName, schemaName, tableName));
continue;
}
if (string.IsNullOrEmpty(indexName))
{
Logger.LogWarning(
SqlServerDesignEventId.IndexMustBeNamedWarning,
() => SqlServerDesignStrings.IndexNameEmpty(schemaName, tableName));
continue;
}
Debug.Assert(index == null || index.Table != null);
if (index == null ||
index.Name != indexName ||
index.Table.Name != tableName ||
index.Table.SchemaName != schemaName)
{
TableModel table;
if (!_tables.TryGetValue(TableKey(tableName, schemaName), out table))
{
Logger.LogWarning(
SqlServerDesignEventId.IndexTableMissingWarning,
() => SqlServerDesignStrings.UnableToFindTableForIndex(indexName, schemaName, tableName));
continue;
}
index = new IndexModel
{
Table = table,
Name = indexName,
IsUnique = isUnique
};
index.SqlServer().IsClustered = typeDesc == "CLUSTERED";
table.Indexes.Add(index);
}
ColumnModel column;
if (string.IsNullOrEmpty(columnName))
{
Logger.LogWarning(
SqlServerDesignEventId.IndexColumnMustBeNamedWarning,
() => SqlServerDesignStrings.ColumnNameEmptyOnIndex(
schemaName, tableName, indexName));
}
else if (!_tableColumns.TryGetValue(ColumnKey(index.Table, columnName), out column))
{
Logger.LogWarning(
RelationalDesignEventId.IndexColumnsNotMappedWarning,
() => SqlServerDesignStrings.UnableToFindColumnForIndex(
indexName, columnName, schemaName, tableName));
}
else
{
var indexColumn = new IndexColumnModel
{
Index = index,
Column = column,
Ordinal = indexOrdinal
};
index.IndexColumns.Add(indexColumn);
}
}
}
}
