protected virtual DropForeignKeyOperation Drop(DatabaseForeignKey foreignKey)
=> new DropForeignKeyOperation
{
Name = foreignKey.Name,
Table = foreignKey.Table.Name,
Schema = foreignKey.Table.Schema
};
void GetConstraints()
{
foreach (var x in _tables)
{
using (var command = new MySqlCommand(string.Format(GetConstraintsQuery, _connection.Database, x.Key.Replace("`", "")), _connection))
using (var reader = command.ExecuteReader())
while (reader.Read())
{
if (_tables.ContainsKey($"`{ reader.GetString(2) }`"))
{
var fkInfo = new DatabaseForeignKey
{
Name = reader.GetString(0),
OnDelete = ConvertToReferentialAction(reader.GetString(4)),
Table = x.Value,
PrincipalTable = _tables[$"`{ reader.GetString(2) }`"]
};
foreach (var pair in reader.GetString(3).Split(','))
{
fkInfo.Columns.Add(x.Value.Columns.Single(y => y.Name == pair.Split('|')[0]));
fkInfo.PrincipalColumns.Add(fkInfo.PrincipalTable.Columns.Single(y => y.Name == pair.Split('|')[1]));
}
x.Value.ForeignKeys.Add(fkInfo);
}
else
{
Logger.LogWarning($"Referenced table `{ reader.GetString(2) }` is not in dictionary.");
}
}
}
}
private void GetConstraints(DbConnection connection, IReadOnlyList <DatabaseTable> tables)
{
foreach (var table in tables)
{
using (var command = connection.CreateCommand())
{
command.CommandText = string.Format(GetConstraintsQuery, table.Name);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var referencedTableName = reader.GetString(2);
var referencedTable = tables.First(t => t.Name == referencedTableName);
var fkInfo = new DatabaseForeignKey {
Name = reader.GetString(0), OnDelete = ConvertToReferentialAction(reader.GetString(4)), Table = table, PrincipalTable = referencedTable
};
foreach (var pair in reader.GetString(3).Split(','))
{
fkInfo.Columns.Add(table.Columns.Single(y =>
string.Equals(y.Name, pair.Split('|')[0], StringComparison.OrdinalIgnoreCase)));
fkInfo.PrincipalColumns.Add(fkInfo.PrincipalTable.Columns.Single(y =>
string.Equals(y.Name, pair.Split('|')[1], StringComparison.OrdinalIgnoreCase)));
}
table.ForeignKeys.Add(fkInfo);
}
}
}
}
}
private static void AssignOnDeleteAction(
DatabaseForeignKey databaseForeignKey, IMutableForeignKey foreignKey)
{
if (databaseForeignKey == null)
{
throw new ArgumentNullException(nameof(databaseForeignKey));
}
if (foreignKey == null)
{
throw new ArgumentNullException(nameof(foreignKey));
}
switch (databaseForeignKey.OnDelete)
{
case ReferentialAction.Cascade:
foreignKey.DeleteBehavior = DeleteBehavior.Cascade;
break;
case ReferentialAction.SetNull:
foreignKey.DeleteBehavior = DeleteBehavior.SetNull;
break;
default:
foreignKey.DeleteBehavior = DeleteBehavior.ClientSetNull;
break;
}
}
public ForeignKey(DatabaseForeignKey source)
{
Name = source.Name;
PrincipalTable = source.PrincipalTable.Name;
PrincipalColumns = source.PrincipalColumns.Select(x => x.Name).ToList();
Columns = source.Columns.Select(x => x.Name).ToList();
OnDelete = source.OnDelete;
}
private void GetForeignKeys(TSqlTable table, DatabaseModel dbModel)
{
var dbTable = dbModel.Tables
.Single(t => t.Name == table.Name.Parts[1] &&
t.Schema == table.Name.Parts[0]);
var fks = table.ForeignKeyConstraints.ToList();
foreach (var fk in fks)
{
var foreignTable = dbModel.Tables
.SingleOrDefault(t => t.Name == fk.ForeignTable.First().Name.Parts[1] &&
t.Schema == fk.ForeignTable.First().Name.Parts[0]);
if (foreignTable == null)
{
continue;
}
var foreignKey = new DatabaseForeignKey
{
Name = fk.Name.HasName ? fk.Name.Parts[1] : null,
Table = dbTable,
PrincipalTable = foreignTable,
OnDelete = ConvertToReferentialAction(fk.DeleteAction)
};
foreach (var fkCol in fk.Columns)
{
var dbCol = dbTable.Columns
.Single(c => c.Name == fkCol.Name.Parts[2]);
foreignKey.Columns.Add(dbCol);
}
foreach (var fkCol in fk.ForeignColumns)
{
var dbCol = foreignTable.Columns
.SingleOrDefault(c => c.Name == fkCol.Name.Parts[2]);
if (dbCol != null)
{
foreignKey.PrincipalColumns.Add(dbCol);
}
}
if (foreignKey.PrincipalColumns.Count > 0)
{
dbTable.ForeignKeys.Add(foreignKey);
}
}
}
protected virtual void GetConstraints(
DbConnection connection,
IReadOnlyList <DatabaseTable> tables)
{
foreach (var table in tables)
{
using (var command = connection.CreateCommand())
{
command.CommandText = string.Format(GetConstraintsQuery, connection.Database, table.Name);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var referencedTableName = reader.GetString(2);
var referencedTable = tables.FirstOrDefault(t => t.Name == referencedTableName);
if (referencedTable == null)
{
// On operation systems with insensitive file name handling, the saved reference table name might have a
// different casing than the actual table name. (#1017)
// In the unlikely event that there are multiple tables with the same spelling, differing only in casing,
// we can't be certain which is the right match, so rather fail to be safe.
referencedTable = tables.Single(t => string.Equals(t.Name, referencedTableName, StringComparison.OrdinalIgnoreCase));
}
if (referencedTable != null)
{
var fkInfo = new DatabaseForeignKey {
Name = reader.GetString(0), OnDelete = ConvertToReferentialAction(reader.GetString(4)), Table = table, PrincipalTable = referencedTable
};
foreach (var pair in reader.GetString(3).Split(','))
{
fkInfo.Columns.Add(table.Columns.Single(y =>
string.Equals(y.Name, pair.Split('|')[0], StringComparison.OrdinalIgnoreCase)));
fkInfo.PrincipalColumns.Add(fkInfo.PrincipalTable.Columns.Single(y =>
string.Equals(y.Name, pair.Split('|')[1], StringComparison.OrdinalIgnoreCase)));
}
table.ForeignKeys.Add(fkInfo);
}
else
{
_logger.Logger.LogWarning($"Referenced table `{referencedTableName}` is not in dictionary.");
}
}
}
}
}
}
private bool IsOneToOne(DatabaseForeignKey tableKeySchema, Relationship foreignRelationship)
{
var foreignColumn = foreignRelationship.Properties
.Select(p => p.ColumnName)
.FirstOrDefault();
bool isFkeyPkey = tableKeySchema.PrincipalTable.PrimaryKey != null &&
tableKeySchema.Table.PrimaryKey != null &&
tableKeySchema.Table.PrimaryKey.Columns.Count == 1 &&
tableKeySchema.Table.PrimaryKey.Columns.Any(c => c.Name == foreignColumn);
if (isFkeyPkey)
{
return(true);
}
return(false);
}
private static void AssignOnDeleteAction(
[NotNull] DatabaseForeignKey databaseForeignKey, [NotNull] IMutableForeignKey foreignKey)
{
Check.NotNull(databaseForeignKey, nameof(databaseForeignKey));
Check.NotNull(foreignKey, nameof(foreignKey));
switch (databaseForeignKey.OnDelete)
{
case ReferentialAction.Cascade:
foreignKey.DeleteBehavior = DeleteBehavior.Cascade;
break;
case ReferentialAction.SetNull:
foreignKey.DeleteBehavior = DeleteBehavior.SetNull;
break;
default:
foreignKey.DeleteBehavior = DeleteBehavior.ClientSetNull;
break;
}
}
private void GetConstraints()
{
foreach (var x in _tables)
{
using (var command =
new MySqlCommand(string.Format(GetConstraintsQuery, _connection.Database, x.Key),
_connection))
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var referencedTableName = reader.GetString(2);
if (_tables.TryGetValue(referencedTableName, out var referencedTable))
{
var fkInfo = new DatabaseForeignKey
{
Name = reader.GetString(0),
OnDelete = ConvertToReferentialAction(reader.GetString(4)),
Table = x.Value,
PrincipalTable = referencedTable
};
foreach (var pair in reader.GetString(3).Split(','))
{
fkInfo.Columns.Add(x.Value.Columns.Single(y =>
string.Equals(y.Name, pair.Split('|')[0], StringComparison.OrdinalIgnoreCase)));
fkInfo.PrincipalColumns.Add(fkInfo.PrincipalTable.Columns.Single(y =>
string.Equals(y.Name, pair.Split('|')[1], StringComparison.OrdinalIgnoreCase)));
}
x.Value.ForeignKeys.Add(fkInfo);
}
else
{
Logger.LogWarning($"Referenced table `{referencedTableName}` is not in dictionary.");
}
}
}
}
}
private IEnumerable <DatabaseForeignKey> GetForeignKeys(DbConnection connection, DatabaseTable table, IList <DatabaseTable> tables)
{
using (var command1 = connection.CreateCommand())
{
command1.CommandText = new StringBuilder()
.AppendLine("SELECT DISTINCT \"id\", \"table\", \"on_delete\"")
.AppendLine("FROM pragma_foreign_key_list(@table)")
.AppendLine("ORDER BY \"id\";")
.ToString();
var parameter1 = command1.CreateParameter();
parameter1.ParameterName = "@table";
parameter1.Value = table.Name;
command1.Parameters.Add(parameter1);
using (var reader1 = command1.ExecuteReader())
{
while (reader1.Read())
{
var id = reader1.GetInt64(0);
var principalTableName = reader1.GetString(1);
var onDelete = reader1.GetString(2);
var foreignKey = new DatabaseForeignKey
{
PrincipalTable = tables.FirstOrDefault(t => t.Name == principalTableName)
?? tables.FirstOrDefault(
t => t.Name.Equals(principalTableName, StringComparison.OrdinalIgnoreCase)),
OnDelete = ConvertToReferentialAction(onDelete)
};
_logger.ForeignKeyFound(table.Name, id, principalTableName, onDelete);
if (foreignKey.PrincipalTable == null)
{
_logger.ForeignKeyReferencesMissingTableWarning(id.ToString());
continue;
}
using (var command2 = connection.CreateCommand())
{
command2.CommandText = new StringBuilder()
.AppendLine("SELECT \"from\", \"to\"")
.AppendLine("FROM pragma_foreign_key_list(@table)")
.AppendLine("WHERE \"id\" = @id")
.AppendLine("ORDER BY \"seq\";")
.ToString();
var parameter2 = command2.CreateParameter();
parameter2.ParameterName = "@table";
parameter2.Value = table.Name;
command2.Parameters.Add(parameter2);
var parameter3 = command2.CreateParameter();
parameter3.ParameterName = "@id";
parameter3.Value = id;
command2.Parameters.Add(parameter3);
var invalid = false;
using (var reader2 = command2.ExecuteReader())
{
while (reader2.Read())
{
var columnName = reader2.GetString(0);
var column = table.Columns.FirstOrDefault(c => c.Name == columnName)
?? table.Columns.FirstOrDefault(
c => c.Name.Equals(columnName, StringComparison.OrdinalIgnoreCase));
Debug.Assert(column != null, "column is null.");
var principalColumnName = reader2.GetString(1);
var principalColumn =
foreignKey.PrincipalTable.Columns.FirstOrDefault(c => c.Name == principalColumnName)
?? foreignKey.PrincipalTable.Columns.FirstOrDefault(
c => c.Name.Equals(principalColumnName, StringComparison.OrdinalIgnoreCase));
if (principalColumn == null)
{
invalid = true;
_logger.ForeignKeyPrincipalColumnMissingWarning(
id.ToString(), table.Name, principalColumnName, principalTableName);
break;
}
foreignKey.Columns.Add(column);
foreignKey.PrincipalColumns.Add(principalColumn);
}
}
if (!invalid)
{
yield return(foreignKey);
}
}
}
}
}
}
void GetConstraints()
{
using (var command = new NpgsqlCommand(GetConstraintsQuery, _connection))
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var schemaName = reader.GetValueOrDefault <string>("nspname");
var tableName = reader.GetValueOrDefault <string>("relname");
if (!_tableSelectionSet.Allows(schemaName, tableName))
{
continue;
}
var table = _tables[TableKey(tableName, schemaName)];
var columns = (List <DatabaseColumn>)table.Columns;
var constraintName = reader.GetValueOrDefault <string>("conname");
var constraintType = reader.GetValueOrDefault <char>("contype");
switch (constraintType)
{
case 'p':
var primaryKey = new DatabasePrimaryKey
{
Table = table,
Name = constraintName
};
var pkColumnIndices = reader.GetValueOrDefault <short[]>("conkey");
foreach (var pkColumnIndex in pkColumnIndices)
{
primaryKey.Columns.Add(columns[pkColumnIndex - 1]);
}
Debug.Assert(table.PrimaryKey == null);
table.PrimaryKey = primaryKey;
continue;
case 'f':
var foreignSchemaName = reader.GetValueOrDefault <string>("fr_nspname");
var foreignTableName = reader.GetValueOrDefault <string>("fr_relname");
if (!_tables.TryGetValue(TableKey(foreignTableName, foreignSchemaName), out var principalTable))
{
continue;
}
var foreignKey = new DatabaseForeignKey
{
Name = constraintName,
Table = table,
PrincipalTable = principalTable,
OnDelete = ConvertToReferentialAction(reader.GetValueOrDefault <char>("confdeltype"))
};
var columnIndices = reader.GetValueOrDefault <short[]>("conkey");
var principalColumnIndices = reader.GetValueOrDefault <short[]>("confkey");
if (columnIndices.Length != principalColumnIndices.Length)
{
throw new Exception("Got varying lengths for column and principal column indices");
}
var principalColumns = (List <DatabaseColumn>)principalTable.Columns;
for (var i = 0; i < columnIndices.Length; i++)
{
foreignKey.Columns.Add(columns[columnIndices[i] - 1]);
foreignKey.PrincipalColumns.Add(principalColumns[principalColumnIndices[i] - 1]);
}
table.ForeignKeys.Add(foreignKey);
break;
default:
throw new NotSupportedException($"Unknown constraint type code {constraintType} for constraint {constraintName}");
}
}
}
}
protected override bool AcceptForeignKey(DatabaseForeignKey foreignKey) => false;
/// <summary>
/// This is an internal API that supports the Entity Framework Core infrastructure and not subject to
/// the same compatibility standards as public APIs. It may be changed or removed without notice in
/// any release. You should only use it directly in your code with extreme caution and knowing that
/// doing so can result in application failures when updating to a new Entity Framework Core release.
/// </summary>
public static string DisplayName([NotNull] this DatabaseForeignKey foreignKey)
=> foreignKey.Table?.DisplayName() + "(" + string.Join(",", foreignKey.Columns.Select(f => f.Name)) + ")";
private void CreateRelationship(EntityContext entityContext, Entity foreignEntity, DatabaseForeignKey tableKeySchema)
{
Entity primaryEntity = GetEntity(entityContext, tableKeySchema.PrincipalTable, false, false);
string primaryName = primaryEntity.EntityClass;
string foreignName = foreignEntity.EntityClass;
string relationshipName = tableKeySchema.Name;
relationshipName = _namer.UniqueRelationshipName(relationshipName);
var foreignMembers = GetKeyMembers(foreignEntity, tableKeySchema.Columns, tableKeySchema.Name);
bool foreignMembersRequired = foreignMembers.Any(c => c.IsRequired);
var primaryMembers = GetKeyMembers(primaryEntity, tableKeySchema.PrincipalColumns, tableKeySchema.Name);
bool primaryMembersRequired = primaryMembers.Any(c => c.IsRequired);
// skip invalid fkeys
if (foreignMembers.Count == 0 || primaryMembers.Count == 0)
{
return;
}
Relationship foreignRelationship = foreignEntity.Relationships
.FirstOrDefault(r => r.RelationshipName == relationshipName && r.IsForeignKey);
if (foreignRelationship == null)
{
foreignRelationship = new Relationship
{
RelationshipName = relationshipName
};
foreignEntity.Relationships.Add(foreignRelationship);
}
foreignRelationship.IsMapped = true;
foreignRelationship.IsForeignKey = true;
foreignRelationship.Cardinality = foreignMembersRequired ? Cardinality.One : Cardinality.ZeroOrOne;
foreignRelationship.PrimaryEntity = primaryEntity;
foreignRelationship.PrimaryProperties = new PropertyCollection(primaryMembers);
foreignRelationship.Entity = foreignEntity;
foreignRelationship.Properties = new PropertyCollection(foreignMembers);
string prefix = GetMemberPrefix(foreignRelationship, primaryName, foreignName);
string foreignPropertyName = ToPropertyName(foreignEntity.EntityClass, prefix + primaryName);
foreignPropertyName = _namer.UniqueName(foreignEntity.EntityClass, foreignPropertyName);
foreignRelationship.PropertyName = foreignPropertyName;
// add reverse
Relationship primaryRelationship = primaryEntity.Relationships
.FirstOrDefault(r => r.RelationshipName == relationshipName && r.IsForeignKey == false);
if (primaryRelationship == null)
{
primaryRelationship = new Relationship {
RelationshipName = relationshipName
};
primaryEntity.Relationships.Add(primaryRelationship);
}
primaryRelationship.IsMapped = false;
primaryRelationship.IsForeignKey = false;
primaryRelationship.PrimaryEntity = foreignEntity;
primaryRelationship.PrimaryProperties = new PropertyCollection(foreignMembers);
primaryRelationship.Entity = primaryEntity;
primaryRelationship.Properties = new PropertyCollection(primaryMembers);
bool isOneToOne = IsOneToOne(tableKeySchema, foreignRelationship);
if (isOneToOne)
{
primaryRelationship.Cardinality = primaryMembersRequired ? Cardinality.One : Cardinality.ZeroOrOne;
}
else
{
primaryRelationship.Cardinality = Cardinality.Many;
}
string primaryPropertyName = prefix + foreignName;
if (!isOneToOne)
{
primaryPropertyName = RelationshipName(primaryPropertyName);
}
primaryPropertyName = ToPropertyName(primaryEntity.EntityClass, primaryPropertyName);
primaryPropertyName = _namer.UniqueName(primaryEntity.EntityClass, primaryPropertyName);
primaryRelationship.PropertyName = primaryPropertyName;
foreignRelationship.PrimaryPropertyName = primaryRelationship.PropertyName;
foreignRelationship.PrimaryCardinality = primaryRelationship.Cardinality;
primaryRelationship.PrimaryPropertyName = foreignRelationship.PropertyName;
primaryRelationship.PrimaryCardinality = foreignRelationship.Cardinality;
foreignRelationship.IsProcessed = true;
primaryRelationship.IsProcessed = true;
}
protected override DropForeignKeyOperation Drop(DatabaseForeignKey foreignKey) => AdDOptimizedAnnotation(base.Drop(foreignKey), foreignKey.Table);
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, fc.constraint_column_id";
using (var reader = command.ExecuteReader())
{
var lastFkName = string.Empty;
var lastFkSchemaName = string.Empty;
var lastFkTableName = string.Empty;
DatabaseForeignKey foreignKey = 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.ForeignKeyColumnFound(
DisplayName(schemaName, tableName), fkName, DisplayName(principalTableSchemaName, principalTableName),
fromColumnName, toColumnName, updateAction, deleteAction, ordinal);
if (string.IsNullOrEmpty(fkName))
{
Logger.ForeignKeyNotNamedWarning(DisplayName(schemaName, tableName));
continue;
}
if (!_tableSelectionSet.Allows(schemaName, tableName))
{
Logger.ForeignKeyColumnMissingWarning(fromColumnName, fkName, DisplayName(schemaName, tableName));
continue;
}
if (foreignKey == null ||
lastFkSchemaName != schemaName ||
lastFkTableName != tableName ||
lastFkName != fkName)
{
lastFkName = fkName;
lastFkSchemaName = schemaName;
lastFkTableName = tableName;
var table = _tables[SchemaQualifiedKey(tableName, schemaName)];
DatabaseTable principalTable = null;
if (!string.IsNullOrEmpty(principalTableSchemaName) &&
!string.IsNullOrEmpty(principalTableName))
{
_tables.TryGetValue(SchemaQualifiedKey(principalTableName, principalTableSchemaName), out principalTable);
}
if (principalTable == null)
{
Logger.ForeignKeyReferencesMissingPrincipalTableWarning(
fkName, DisplayName(schemaName, tableName), DisplayName(principalTableSchemaName, principalTableName));
}
foreignKey = new DatabaseForeignKey
{
Name = fkName,
Table = table,
PrincipalTable = principalTable,
OnDelete = ConvertToReferentialAction(deleteAction)
};
table.ForeignKeys.Add(foreignKey);
}
DatabaseColumn fromColumn;
if ((fromColumn = FindColumnForForeignKey(fromColumnName, foreignKey.Table, fkName)) != null)
{
foreignKey.Columns.Add(fromColumn);
}
if (foreignKey.PrincipalTable != null)
{
DatabaseColumn toColumn;
if ((toColumn = FindColumnForForeignKey(toColumnName, foreignKey.PrincipalTable, fkName)) != null)
{
foreignKey.PrincipalColumns.Add(toColumn);
}
}
}
}
}
public void GenerateRelationships(int expectedRelationships, bool processRelationships)
{
var generatorOptions = new GeneratorOptions();
generatorOptions.Database.ProcessRelationships = processRelationships;
var databaseModel = new DatabaseModel
{
DatabaseName = "TestDatabase",
DefaultSchema = "dbo"
};
var principalTable = new DatabaseTable
{
Database = databaseModel,
Name = "PrincipalTable",
Schema = "dbo",
};
var idPrincipalTable = new DatabaseColumn
{
Table = principalTable,
Name = "Id",
IsNullable = false,
StoreType = "int"
};
principalTable.Columns.Add(idPrincipalTable);
var table = new DatabaseTable
{
Database = databaseModel,
Name = "RelatedTable",
Schema = "dbo",
};
var idTable = new DatabaseColumn
{
Table = table,
Name = "Id",
IsNullable = false,
StoreType = "int"
};
table.Columns.Add(idTable);
var fk = new DatabaseForeignKey
{
PrincipalTable = principalTable,
Table = table
};
fk.PrincipalColumns.Add(idPrincipalTable);
fk.Columns.Add(idTable);
table.ForeignKeys.Add(fk);
databaseModel.Tables.Add(principalTable);
databaseModel.Tables.Add(table);
var generator = new ModelGenerator(NullLoggerFactory.Instance);
var typeMappingSource = CreateTypeMappingSource();
var entityContext = generator.Generate(generatorOptions, databaseModel, typeMappingSource);
var principalEntity = entityContext.Entities[0];
var entity = entityContext.Entities[1];
principalEntity.Relationships.Count.Should().Be(expectedRelationships);
entity.Relationships.Count.Should().Be(expectedRelationships);
}
private void GetForeignKeys(
DbConnection connection,
string tableFilter,
DatabaseModel databaseModel)
{
using (var command = connection.CreateCommand())
{
command.CommandText = new StringBuilder()
.AppendLine("SELECT t.tablespace_name,")
.AppendLine(" x.table_name,")
.AppendLine(" x.column_name,")
.AppendLine(" c.table_name principal_table_name,")
.AppendLine(" r.delete_rule,")
.AppendLine(" r.constraint_name,")
.AppendLine(" c.column_name principal_column_name")
.AppendLine("FROM all_cons_columns x,")
.AppendLine(" all_cons_columns c,")
.AppendLine(" all_constraints r,")
.AppendLine(" user_tables t")
.AppendLine(tableFilter)
.AppendLine(" AND x.constraint_name = r.constraint_name")
.AppendLine(" AND t.table_name = x.table_name")
.AppendLine(" AND c.constraint_name = r.r_constraint_name")
.AppendLine(" AND c.owner = r.r_owner")
.AppendLine(" AND r.constraint_type = 'R'")
.ToString();
using (var reader = command.ExecuteReader())
{
var tableForeignKeyGroups = reader.Cast <DbDataRecord>()
.GroupBy(
ddr => (tableSchema: ddr.GetValueOrDefault <string>("tablespace_name"),
tableName: ddr.GetValueOrDefault <string>("table_name")));
foreach (var tableForeignKeyGroup in tableForeignKeyGroups)
{
var tableSchema = tableForeignKeyGroup.Key.tableSchema;
var tableName = tableForeignKeyGroup.Key.tableName;
var table = databaseModel.Tables.Single(t => t.Schema == tableSchema && t.Name == tableName);
var foreignKeyGroups = tableForeignKeyGroup
.GroupBy(
c => (Name: c.GetValueOrDefault <string>("constraint_name"),
PrincipalTableSchema: c.GetValueOrDefault <string>("tablespace_name"),
PrincipalTableName: c.GetValueOrDefault <string>("principal_table_name"),
OnDeleteAction: c.GetValueOrDefault <string>("delete_rule")));
foreach (var foreignKeyGroup in foreignKeyGroups)
{
var fkName = foreignKeyGroup.Key.Name;
var principalTableSchema = foreignKeyGroup.Key.PrincipalTableSchema;
var principalTableName = foreignKeyGroup.Key.PrincipalTableName;
var onDeleteAction = foreignKeyGroup.Key.OnDeleteAction;
_logger.ForeignKeyFound(
fkName,
DisplayName(table.Schema, table.Name),
DisplayName(principalTableSchema, principalTableName),
onDeleteAction);
var principalTable = databaseModel.Tables.FirstOrDefault(
t => t.Schema == principalTableSchema &&
t.Name == principalTableName)
?? databaseModel.Tables.FirstOrDefault(
t => t.Schema.Equals(principalTableSchema, StringComparison.OrdinalIgnoreCase) &&
t.Name.Equals(principalTableName, StringComparison.OrdinalIgnoreCase));
if (principalTable == null)
{
_logger.ForeignKeyReferencesMissingPrincipalTableWarning(
fkName,
DisplayName(table.Schema, table.Name),
DisplayName(principalTableSchema, principalTableName));
continue;
}
var foreignKey = new DatabaseForeignKey
{
Name = fkName,
Table = table,
PrincipalTable = principalTable,
OnDelete = ConvertToReferentialAction(onDeleteAction)
};
var invalid = false;
foreach (var dataRecord in foreignKeyGroup)
{
var columnName = dataRecord.GetValueOrDefault <string>("column_name");
var column = table.Columns.FirstOrDefault(c => c.Name == columnName)
?? table.Columns.FirstOrDefault(c => c.Name.Equals(columnName, StringComparison.OrdinalIgnoreCase));
var principalColumnName = dataRecord.GetValueOrDefault <string>("principal_column_name");
var principalColumn = foreignKey.PrincipalTable.Columns.FirstOrDefault(c => c.Name == principalColumnName)
?? foreignKey.PrincipalTable.Columns.FirstOrDefault(c => c.Name.Equals(principalColumnName, StringComparison.OrdinalIgnoreCase));
if (principalColumn == null)
{
invalid = true;
_logger.ForeignKeyPrincipalColumnMissingWarning(
fkName,
DisplayName(table.Schema, table.Name),
principalColumnName,
DisplayName(principalTableSchema, principalTableName));
break;
}
foreignKey.Columns.Add(column);
foreignKey.PrincipalColumns.Add(principalColumn);
}
if (!invalid)
{
table.ForeignKeys.Add(foreignKey);
}
}
}
}
}
}
protected override MigrationOperation Drop(DatabaseForeignKey foreignKey) => AddSqlServerSpecificAnnotations(base.Drop(foreignKey), foreignKey.Table);
private void GetForeignKeys()
{
var command = _connection.CreateCommand();
var dbName = _connection.Database;
command.CommandText = $@"SELECT
kc.constraint_name,
kc.table_schema,
kc.table_name,
kc.column_name,
kc.referenced_table_schema,
kc.referenced_table_name,
kc.referenced_column_name,
kc.ordinal_position,
rc.update_rule,
rc.delete_rule
FROM information_schema.key_column_usage as kc
INNER JOIN information_schema.referential_constraints as rc
ON kc.constraint_catalog = rc.constraint_catalog
AND kc.constraint_schema = rc.constraint_schema
AND kc.constraint_name = rc.constraint_name
WHERE kc.referenced_table_name IS NOT NULL
AND LOWER(kc.table_schema) IN ({_schemaList.ToLowerInvariant()})
AND kc.table_name NOT LIKE '{HistoryRepository.DefaultTableName}'";
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
DatabaseForeignKey foreignKey = null;
var tableSchema = reader.GetValueOrDefault <string>("table_schema");
var constraintName = reader.GetValueOrDefault <string>("constraint_name");
var tableName = reader.GetValueOrDefault <string>("table_name");
var columnName = reader.GetValueOrDefault <string>("column_name");
var referencedTableSchema = reader.GetValueOrDefault <string>("referenced_table_schema");
var referencedTableName = reader.GetValueOrDefault <string>("referenced_table_name");
var referencedColumnName = reader.GetValueOrDefault <string>("referenced_column_name");
var updateRule = reader.GetValueOrDefault <string>("update_rule");
var deleteRule = reader.GetValueOrDefault <string>("delete_rule");
var ordinal = reader.GetInt32("ordinal_position");
if (string.IsNullOrEmpty(constraintName))
{
Logger.LogWarning("Foreign key must be named warning", tableSchema, tableName);
continue;
}
if (!_tableSelectionSet.Allows(tableSchema, tableName))
{
Logger.LogDebug("Foreign key column skipped", new string[] { referencedColumnName, constraintName, tableSchema, tableName });
continue;
}
var table = _tables[TableKey(tableName, tableSchema)];
DatabaseTable principalTable = null;
if (!string.IsNullOrEmpty(tableSchema) &&
!string.IsNullOrEmpty(referencedTableName))
{
_tables.TryGetValue(TableKey(referencedTableName, tableSchema), out principalTable);
}
if (principalTable == null)
{
Logger.LogDebug("Foreign key references missing table", new string[] { constraintName, tableName, tableSchema });
}
foreignKey = new DatabaseForeignKey
{
Name = constraintName,
Table = table,
PrincipalTable = principalTable,
OnDelete = ConvertToReferentialAction(deleteRule)
};
table.ForeignKeys.Add(foreignKey);
DatabaseColumn fromColumn = FindColumnForForeignKey(columnName, foreignKey.Table, constraintName);
if (fromColumn != null)
{
foreignKey.Columns.Add(fromColumn);
}
if (foreignKey.PrincipalTable != null)
{
DatabaseColumn toColumn = FindColumnForForeignKey(referencedColumnName, foreignKey.PrincipalTable, constraintName);
if (toColumn != null)
{
foreignKey.PrincipalColumns.Add(toColumn);
}
}
}
}
}
void GetConstraints(
NpgsqlConnection connection,
IReadOnlyList <DatabaseTable> tables,
string tableFilter,
out List <uint> constraintIndexes)
{
constraintIndexes = new List <uint>();
var getConstraints = @"
SELECT
ns.nspname, cls.relname, conname, contype, conkey, conindid,
frnns.nspname AS fr_nspname, frncls.relname AS fr_relname, confkey, confdeltype
FROM pg_class AS cls
JOIN pg_namespace AS ns ON ns.oid = cls.relnamespace
JOIN pg_constraint as con ON con.conrelid = cls.oid
LEFT OUTER JOIN pg_class AS frncls ON frncls.oid = con.confrelid
LEFT OUTER JOIN pg_namespace as frnns ON frnns.oid = frncls.relnamespace
WHERE
cls.relkind = 'r' AND
ns.nspname NOT IN ('pg_catalog', 'information_schema')
AND
con.contype IN ('p', 'f', 'u')
" + tableFilter;
var command = connection.CreateCommand();
command.CommandText = getConstraints;
using (var reader = command.ExecuteReader())
{
var tableGroups = reader.Cast <DbDataRecord>()
.GroupBy(
ddr => (tableSchema: ddr.GetValueOrDefault <string>("nspname"),
tableName: ddr.GetValueOrDefault <string>("relname")));
foreach (var tableGroup in tableGroups)
{
var tableSchema = tableGroup.Key.tableSchema;
var tableName = tableGroup.Key.tableName;
var table = tables.Single(t => t.Schema == tableSchema && t.Name == tableName);
// Primary keys
foreach (var primaryKeyRecord in tableGroup
.Where(ddr => ddr.GetValueOrDefault <char>("contype") == 'p'))
{
var primaryKey = new DatabasePrimaryKey
{
Table = table,
Name = primaryKeyRecord.GetValueOrDefault <string>("conname")
};
var pkColumnIndices = primaryKeyRecord.GetValueOrDefault <short[]>("conkey");
foreach (var pkColumnIndex in pkColumnIndices)
{
primaryKey.Columns.Add(table.Columns[pkColumnIndex - 1]);
}
table.PrimaryKey = primaryKey;
}
// Foreign keys
foreach (var foreignKeyRecord in tableGroup
.Where(ddr => ddr.GetValueOrDefault <char>("contype") == 'f'))
{
var fkName = foreignKeyRecord.GetValueOrDefault <string>("conname");
var principalTableSchema = foreignKeyRecord.GetValueOrDefault <string>("fr_nspname");
var principalTableName = foreignKeyRecord.GetValueOrDefault <string>("fr_relname");
var onDeleteAction = foreignKeyRecord.GetValueOrDefault <char>("confdeltype");
var principalTable = tables.FirstOrDefault(
t => t.Schema == principalTableSchema &&
t.Name == principalTableName)
?? tables.FirstOrDefault(
t => t.Schema.Equals(principalTableSchema, StringComparison.OrdinalIgnoreCase) &&
t.Name.Equals(principalTableName, StringComparison.OrdinalIgnoreCase));
if (principalTable == null)
{
_logger.ForeignKeyReferencesMissingPrincipalTableWarning(
fkName,
DisplayName(table.Schema, table.Name),
DisplayName(principalTableSchema, principalTableName));
continue;
}
var foreignKey = new DatabaseForeignKey
{
Name = fkName,
Table = table,
PrincipalTable = principalTable,
OnDelete = ConvertToReferentialAction(onDeleteAction)
};
var columnIndices = foreignKeyRecord.GetValueOrDefault <short[]>("conkey");
var principalColumnIndices = foreignKeyRecord.GetValueOrDefault <short[]>("confkey");
if (columnIndices.Length != principalColumnIndices.Length)
{
throw new Exception("Got varying lengths for column and principal column indices");
}
var principalColumns = (List <DatabaseColumn>)principalTable.Columns;
for (var i = 0; i < columnIndices.Length; i++)
{
foreignKey.Columns.Add(table.Columns[columnIndices[i] - 1]);
foreignKey.PrincipalColumns.Add(principalColumns[principalColumnIndices[i] - 1]);
}
table.ForeignKeys.Add(foreignKey);
}
// Unique constraints
foreach (var record in tableGroup
.Where(ddr => ddr.GetValueOrDefault <char>("contype") == 'u')
.ToArray())
{
var name = record.GetValueOrDefault <string>("conname");
_logger.UniqueConstraintFound(name, DisplayName(tableSchema, tableName));
var uniqueConstraint = new DatabaseUniqueConstraint
{
Table = table,
Name = name
};
var columnIndices = record.GetValueOrDefault <short[]>("conkey");
foreach (var t in columnIndices)
{
uniqueConstraint.Columns.Add(table.Columns[t - 1]);
}
table.UniqueConstraints.Add(uniqueConstraint);
constraintIndexes.Add(record.GetValueOrDefault <uint>("conindid"));
}
}
}
}
private void GetForeignKeys()
{
var command = _connection.CreateCommand();
command.CommandText = @"SELECT
KCU1.CONSTRAINT_NAME AS FK_CONSTRAINT_NAME,
--KCU1.TABLE_NAME + '_' + KCU1.CONSTRAINT_NAME AS FK_CONSTRAINT_NAME,
NULL AS [SCHEMA_NAME],
KCU1.TABLE_NAME AS FK_TABLE_NAME,
NULL AS [UQ_SCHEMA_NAME],
KCU2.TABLE_NAME AS UQ_TABLE_NAME,
KCU1.COLUMN_NAME AS FK_COLUMN_NAME,
KCU2.COLUMN_NAME AS UQ_COLUMN_NAME,
0 AS [IS_DISABLED],
RC.DELETE_RULE,
RC.UPDATE_RULE,
KCU1.ORDINAL_POSITION
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU1 ON KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
AND KCU1.TABLE_NAME = RC.CONSTRAINT_TABLE_NAME
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2 ON KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME
AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION
AND KCU2.TABLE_NAME = RC.UNIQUE_CONSTRAINT_TABLE_NAME
ORDER BY FK_TABLE_NAME, FK_CONSTRAINT_NAME, KCU1.ORDINAL_POSITION;";
using (var reader = command.ExecuteReader())
{
var lastFkName = "";
DatabaseForeignKey fkInfo = null;
while (reader.Read())
{
var fkName = reader.GetValueOrDefault <string>("FK_CONSTRAINT_NAME");
var tableName = reader.GetValueOrDefault <string>("FK_TABLE_NAME");
var principalTableName = reader.GetValueOrDefault <string>("UQ_TABLE_NAME");
var fromColumnName = reader.GetValueOrDefault <string>("FK_COLUMN_NAME");
var toColumnName = reader.GetValueOrDefault <string>("UQ_COLUMN_NAME");
if (!_tableSelectionSet.Allows(tableName))
{
continue;
}
if ((fkInfo == null) ||
(lastFkName != fkName))
{
lastFkName = fkName;
var table = _tables[TableKey(tableName)];
DatabaseTable principalTable;
_tables.TryGetValue(TableKey(principalTableName), out principalTable);
fkInfo = new DatabaseForeignKey
{
Name = fkName,
Table = table,
PrincipalTable = principalTable,
OnDelete = ConvertToReferentialAction(reader.GetValueOrDefault <string>("DELETE_RULE"))
};
table.ForeignKeys.Add(fkInfo);
}
DatabaseColumn fromColumn;
if ((fromColumn = FindColumnForForeignKey(fromColumnName, fkInfo.Table, fkName)) != null)
{
fkInfo.Columns.Add(fromColumn);
}
if (fkInfo.PrincipalTable != null)
{
DatabaseColumn toColumn;
if ((toColumn = FindColumnForForeignKey(toColumnName, fkInfo.PrincipalTable, fkName)) != null)
{
fkInfo.PrincipalColumns.Add(toColumn);
}
}
}
}
}
private void GetForeignKeys(DbConnection connection, IReadOnlyList <DatabaseTable> tables)
{
using var command = connection.CreateCommand();
var commandText =
"SELECT * FROM ("
+ "SELECT FK.TABLE_CATALOG AS FK_CATALOG, FK.TABLE_SCHEMA AS FK_SCHEMA, FK.TABLE_NAME AS FK_TABLE, FK.CONSTRAINT_NAME, FK_COLS.COLUMN_NAME AS FK_COL_NAME, PK_COLS.TABLE_NAME AS PK_TABLE, PK_COLS.COLUMN_NAME AS PK_COL_NAME, FK_COLS.POSITION_IN_UNIQUE_CONSTRAINT AS ORDINAL_POSITION "
+ "from INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK "
+ "inner join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC ON "
+ " FK.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG AND "
+ " FK.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA AND "
+ " FK.CONSTRAINT_NAME = RC.CONSTRAINT_NAME AND "
+ " FK.CONSTRAINT_TYPE = 'FOREIGN KEY' "
+ "inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE FK_COLS ON "
+ " FK.CONSTRAINT_CATALOG = FK_COLS.CONSTRAINT_CATALOG AND "
+ " FK.CONSTRAINT_SCHEMA = FK_COLS.CONSTRAINT_SCHEMA AND "
+ " FK.CONSTRAINT_NAME = FK_COLS.CONSTRAINT_NAME "
+ "inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE PK_COLS ON "
+ " RC.UNIQUE_CONSTRAINT_CATALOG = PK_COLS.CONSTRAINT_CATALOG AND "
+ " RC.UNIQUE_CONSTRAINT_SCHEMA = PK_COLS.CONSTRAINT_SCHEMA AND "
+ " RC.UNIQUE_CONSTRAINT_NAME = PK_COLS.CONSTRAINT_NAME AND "
+ " FK_COLS.POSITION_IN_UNIQUE_CONSTRAINT = PK_COLS.ORDINAL_POSITION "
+ "where FK.CONSTRAINT_CATALOG = '' AND FK.CONSTRAINT_SCHEMA = '' "
+ "UNION ALL "
+ "SELECT CHILD.TABLE_CATALOG AS FK_CATALOG, CHILD.TABLE_SCHEMA AS FK_SCHEMA, CHILD.TABLE_NAME AS FK_TABLE, PK_PARENT.CONSTRAINT_NAME AS CONSTRAINT_NAME, FK_COLS.COLUMN_NAME AS FK_COL_NAME, PARENT.TABLE_NAME AS PK_TABLE, FK_COLS.COLUMN_NAME AS PK_COL_NAME, FK_COLS.ORDINAL_POSITION "
+ "FROM INFORMATION_SCHEMA.TABLES CHILD "
+ "INNER JOIN INFORMATION_SCHEMA.TABLES PARENT ON "
+ " CHILD.TABLE_CATALOG = PARENT.TABLE_CATALOG AND "
+ " CHILD.TABLE_SCHEMA = PARENT.TABLE_SCHEMA AND "
+ " CHILD.PARENT_TABLE_NAME = PARENT.TABLE_NAME "
+ "INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK_CHILD ON "
+ " CHILD.TABLE_CATALOG = PK_CHILD.TABLE_CATALOG AND "
+ " CHILD.TABLE_SCHEMA = PK_CHILD.TABLE_SCHEMA AND "
+ " CHILD.TABLE_NAME = PK_CHILD.TABLE_NAME AND "
+ " PK_CHILD.CONSTRAINT_TYPE = 'PRIMARY KEY' "
+ "INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK_PARENT ON "
+ " PARENT.TABLE_CATALOG = PK_PARENT.TABLE_CATALOG AND "
+ " PARENT.TABLE_SCHEMA = PK_PARENT.TABLE_SCHEMA AND "
+ " PARENT.TABLE_NAME = PK_PARENT.TABLE_NAME AND "
+ " PK_PARENT.CONSTRAINT_TYPE = 'PRIMARY KEY' "
+ "INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE FK_COLS ON "
+ " PK_PARENT.CONSTRAINT_CATALOG = FK_COLS.CONSTRAINT_CATALOG AND "
+ " PK_PARENT.CONSTRAINT_SCHEMA = FK_COLS.CONSTRAINT_SCHEMA AND "
+ " PK_PARENT.CONSTRAINT_NAME = FK_COLS.CONSTRAINT_NAME "
+ "WHERE CHILD.TABLE_CATALOG = '' AND CHILD.TABLE_SCHEMA = '' "
+ ") FK "
+ "ORDER BY FK_CATALOG, FK_SCHEMA, FK_TABLE, CONSTRAINT_NAME, ORDINAL_POSITION ";
command.CommandText = commandText;
using var reader = command.ExecuteReader();
var tableFkGroups = reader.Cast <DbDataRecord>()
.GroupBy(ddr => (
FkTable: ddr.GetValueOrDefault <string>("FK_TABLE"),
PkTable: ddr.GetValueOrDefault <string>("PK_TABLE"),
Name: ddr.GetValueOrDefault <string>("CONSTRAINT_NAME")
));
foreach (var tableFkGroup in tableFkGroups)
{
var fkTableName = tableFkGroup.Key.FkTable;
var fkTable = tables.Single(t => t.Name == fkTableName);
var pkTableName = tableFkGroup.Key.PkTable;
var pkTable = tables.Single(t => t.Name == pkTableName);
var fk = new DatabaseForeignKey {
Name = tableFkGroup.Key.Name, Table = fkTable, PrincipalTable = pkTable, OnDelete = ReferentialAction.Restrict
};
foreach (var col in tableFkGroup)
{
var fkCol = fkTable.Columns.FirstOrDefault(c => c.Name == col.GetValueOrDefault <string>("FK_COL_NAME"));
fk.Columns.Add(fkCol);
var pkCol = pkTable.Columns.FirstOrDefault(c => c.Name == col.GetValueOrDefault <string>("PK_COL_NAME"));
fk.PrincipalColumns.Add(pkCol);
}
fkTable.ForeignKeys.Add(fk);
}
}
protected override MigrationOperation Drop(DatabaseForeignKey foreignKey) => AddMemoryOptimizedAnnotation(base.Drop(foreignKey), foreignKey.Table);
private void GetRelations(DbConnection connection, IReadOnlyList <DatabaseTable> tables)
{
var relationTable = new DataTable();
using (var command = connection.CreateCommand())
{
command.CommandText = $@"SELECT * FROM `INFORMATION_SCHEMA.RELATIONS` ORDER BY RELATION_NAME, REFERENCING_TABLE_NAME, PRINCIPAL_TABLE_NAME";
using var reader = command.ExecuteReader();
relationTable.Load(reader);
}
var relationColumnsTable = new DataTable();
using (var command = connection.CreateCommand())
{
command.CommandText = "SELECT * FROM `INFORMATION_SCHEMA.RELATION_COLUMNS` ORDER BY RELATION_NAME, REFERENCING_COLUMN_NAME, PRINCIPAL_COLUMN_NAME";
using var reader = command.ExecuteReader();
relationColumnsTable.Load(reader);
}
var groupedRelationColumns = relationColumnsTable.Rows.Cast <DataRow>()
.GroupBy(r => r.GetValueOrDefault <string>("RELATION_NAME"))
.ToList();
foreach (DataRow relationRow in relationTable.Rows)
{
var relationName = relationRow.GetValueOrDefault <string>("RELATION_NAME");
var referencingTableName = relationRow.GetValueOrDefault <string>("REFERENCING_TABLE_NAME");
var principalTableName = relationRow.GetValueOrDefault <string>("PRINCIPAL_TABLE_NAME");
var relationType = relationRow.GetValueOrDefault <string>("RELATION_TYPE");
var onDelete = relationRow.GetValueOrDefault <string>("ON_DELETE");
var onUpdate = relationRow.GetValueOrDefault <string>("ON_UPDATE");
var enforced = relationRow.GetValueOrDefault <bool>("IS_ENFORCED", true);
var inherited = relationRow.GetValueOrDefault <bool>("IS_INHERITED", true);
var referencingTable = tables.FirstOrDefault(t => string.Equals(t.Name, referencingTableName)) ??
tables.FirstOrDefault(t => string.Equals(t.Name, referencingTableName, StringComparison.OrdinalIgnoreCase));
if (referencingTable != null)
{
var relationColumns = groupedRelationColumns.FirstOrDefault(g => g.Key == relationName);
if (relationColumns?.Any() ?? false)
{
_logger.ForeignKeyFound(
relationName,
referencingTableName,
principalTableName,
onDelete);
var principalTable = tables.FirstOrDefault(t => string.Equals(t.Name, principalTableName)) ??
tables.FirstOrDefault(t => string.Equals(t.Name, principalTableName, StringComparison.OrdinalIgnoreCase));
if (principalTable == null)
{
_logger.ForeignKeyReferencesMissingPrincipalTableWarning(
relationName,
referencingTableName,
principalTableName);
continue;
}
var foreignKey = new DatabaseForeignKey
{
Name = relationName,
Table = referencingTable,
PrincipalTable = principalTable,
OnDelete = ConvertToReferentialAction(onDelete),
};
var invalid = false;
foreach (var relationColumn in relationColumns)
{
var referencingColumnName = relationColumn.GetValueOrDefault <string>("REFERENCING_COLUMN_NAME");
var referencingColumn = referencingTable.Columns.FirstOrDefault(c => c.Name == referencingColumnName) ??
referencingTable.Columns.FirstOrDefault(c => string.Equals(c.Name, referencingColumnName, StringComparison.OrdinalIgnoreCase));
Debug.Assert(referencingColumn != null, "referencingColumn is null.");
var principalColumnName = relationColumn.GetValueOrDefault <string>("PRINCIPAL_COLUMN_NAME");
var principalColumn = principalTable.Columns.FirstOrDefault(c => c.Name == principalColumnName) ??
principalTable.Columns.FirstOrDefault(c => string.Equals(c.Name, principalColumnName, StringComparison.OrdinalIgnoreCase));
if (principalColumn == null)
{
invalid = true;
_logger.ForeignKeyPrincipalColumnMissingWarning(
relationName,
referencingTableName,
principalColumnName,
principalTableName);
break;
}
foreignKey.Columns.Add(referencingColumn);
foreignKey.PrincipalColumns.Add(principalColumn);
}
if (invalid)
{
continue;
}
if (foreignKey.Columns.SequenceEqual(foreignKey.PrincipalColumns))
{
_logger.ReflexiveConstraintIgnored(
foreignKey.Name,
referencingTableName);
}
else
{
referencingTable.ForeignKeys.Add(foreignKey);
}
}
}
}
}
/// <summary>
/// This is an internal API that supports the Entity Framework Core infrastructure and not subject to
/// the same compatibility standards as public APIs. It may be changed or removed without notice in
/// any release. You should only use it directly in your code with extreme caution and knowing that
/// doing so can result in application failures when updating to a new Entity Framework Core release.
/// </summary>
protected virtual IMutableForeignKey VisitForeignKey([NotNull] ModelBuilder modelBuilder, [NotNull] DatabaseForeignKey foreignKey)
{
Check.NotNull(modelBuilder, nameof(modelBuilder));
Check.NotNull(foreignKey, nameof(foreignKey));
if (foreignKey.PrincipalTable == null)
{
_reporter.WriteWarning(
DesignStrings.ForeignKeyScaffoldErrorPrincipalTableNotFound(foreignKey.DisplayName()));
return(null);
}
if (foreignKey.Table == null)
{
return(null);
}
var dependentEntityType = modelBuilder.Model.FindEntityType(GetEntityTypeName(foreignKey.Table));
if (dependentEntityType == null)
{
return(null);
}
var unmappedDependentColumns = foreignKey.Columns
.Where(c => _unmappedColumns.Contains(c))
.Select(c => c.Name)
.ToList();
if (unmappedDependentColumns.Count > 0)
{
_reporter.WriteWarning(
DesignStrings.ForeignKeyScaffoldErrorPropertyNotFound(
foreignKey.DisplayName(),
string.Join(CultureInfo.CurrentCulture.TextInfo.ListSeparator, unmappedDependentColumns)));
return(null);
}
var dependentProperties = foreignKey.Columns
.Select(GetPropertyName)
.Select(name => dependentEntityType.FindProperty(name))
.ToList()
.AsReadOnly();
var principalEntityType = modelBuilder.Model.FindEntityType(GetEntityTypeName(foreignKey.PrincipalTable));
if (principalEntityType == null)
{
_reporter.WriteWarning(
DesignStrings.ForeignKeyScaffoldErrorPrincipalTableScaffoldingError(
foreignKey.DisplayName(),
foreignKey.PrincipalTable.DisplayName()));
return(null);
}
var unmappedPrincipalColumns = foreignKey.PrincipalColumns
.Where(pc => principalEntityType.FindProperty(GetPropertyName(pc)) == null)
.Select(pc => pc.Name)
.ToList();
if (unmappedPrincipalColumns.Count > 0)
{
_reporter.WriteWarning(
DesignStrings.ForeignKeyScaffoldErrorPropertyNotFound(
foreignKey.DisplayName(),
string.Join(CultureInfo.CurrentCulture.TextInfo.ListSeparator, unmappedPrincipalColumns)));
return(null);
}
var principalPropertiesMap = foreignKey.PrincipalColumns
.Select(
fc => (property: principalEntityType.FindProperty(GetPropertyName(fc)), column: fc)).ToList();
var principalProperties = principalPropertiesMap
.Select(tuple => tuple.property)
.ToList();
var principalKey = principalEntityType.FindKey(principalProperties);
if (principalKey == null)
{
var index = principalEntityType.GetIndexes()
.Where(i => i.Properties.SequenceEqual(principalProperties) && i.IsUnique)
.FirstOrDefault();
if (index != null)
{
// ensure all principal properties are non-nullable even if the columns
// are nullable on the database. EF's concept of a key requires this.
var nullablePrincipalProperties =
principalPropertiesMap.Where(tuple => tuple.property.IsNullable).ToList();
if (nullablePrincipalProperties.Count > 0)
{
_reporter.WriteWarning(
DesignStrings.ForeignKeyPrincipalEndContainsNullableColumns(
foreignKey.DisplayName(),
index.GetDatabaseName(),
nullablePrincipalProperties.Select(tuple => tuple.column.DisplayName()).ToList()
.Aggregate((a, b) => a + "," + b)));
nullablePrincipalProperties
.ToList()
.ForEach(tuple => tuple.property.IsNullable = false);
}
principalKey = principalEntityType.AddKey(principalProperties);
}
else
{
var principalColumns = foreignKey.PrincipalColumns.Select(c => c.Name).ToList();
_reporter.WriteWarning(
DesignStrings.ForeignKeyScaffoldErrorPrincipalKeyNotFound(
foreignKey.DisplayName(),
string.Join(CultureInfo.CurrentCulture.TextInfo.ListSeparator, principalColumns),
principalEntityType.DisplayName()));
return(null);
}
}
var newForeignKey = dependentEntityType.AddForeignKey(
dependentProperties, principalKey, principalEntityType);
var dependentKey = dependentEntityType.FindKey(dependentProperties);
var dependentIndexes = dependentEntityType.GetIndexes()
.Where(i => i.Properties.SequenceEqual(dependentProperties));
newForeignKey.IsUnique = dependentKey != null ||
dependentIndexes.Any(i => i.IsUnique);
if (!string.IsNullOrEmpty(foreignKey.Name) &&
foreignKey.Name != newForeignKey.GetDefaultName())
{
newForeignKey.SetConstraintName(foreignKey.Name);
}
AssignOnDeleteAction(foreignKey, newForeignKey);
newForeignKey.AddAnnotations(foreignKey.GetAnnotations());
return(newForeignKey);
}
public static DatabaseColumn GetPrincipalColumn(this DatabaseForeignKey fk)
{
return(fk.PrincipalColumns.Single());
}
protected virtual bool AcceptForeignKey(DatabaseForeignKey foreignKey) => true;
private void GetForeignKeys()
{
var command = _connection.CreateCommand();
command.CommandText =
"SHOW " +
" ForeignKeys " +
"ORDER BY " +
" ToTable, " +
" ConstraintId, " +
" Ordinal";
using (var reader = command.ExecuteReader())
{
var lastFkName = string.Empty;
var lastFkSchemaName = string.Empty;
var lastFkTableName = string.Empty;
DatabaseForeignKey foreignKey = null;
while (reader.Read())
{
var schemaName = "Jet";
var tableName = reader.GetValueOrDefault <string>("FromTable");
var constraintName = reader.GetValueOrDefault <string>("ConstraintId");
var principalTableSchemaName = "Jet";
var principalTableName = reader.GetValueOrDefault <string>("ToTable");
var fromColumnName = reader.GetValueOrDefault <string>("FromColumn");
var toColumnName = reader.GetValueOrDefault <string>("ToColumn");
var updateAction = reader.GetValueOrDefault <string>("UpdateRule");
var deleteAction = reader.GetValueOrDefault <string>("DeleteRule");
var ordinal = reader.GetValueOrDefault <int>("Ordinal");
Logger.ForeignKeyColumnFound(
DisplayName(schemaName, tableName), constraintName, DisplayName(principalTableSchemaName, principalTableName),
fromColumnName, toColumnName, updateAction, deleteAction, ordinal);
if (foreignKey == null ||
lastFkSchemaName != schemaName ||
lastFkTableName != tableName ||
lastFkName != constraintName)
{
lastFkName = constraintName;
lastFkSchemaName = schemaName;
lastFkTableName = tableName;
if (!_tables.TryGetValue(SchemaQualifiedKey(tableName, schemaName), out var table))
{
Logger.ForeignKeyTableMissingWarning(constraintName, DisplayName(schemaName, tableName));
continue;
}
DatabaseTable principalTable = null;
if (!string.IsNullOrEmpty(principalTableSchemaName) &&
!string.IsNullOrEmpty(principalTableName))
{
_tables.TryGetValue(SchemaQualifiedKey(principalTableName, principalTableSchemaName), out principalTable);
}
if (principalTable == null)
{
Logger.ForeignKeyReferencesMissingPrincipalTableWarning(
constraintName, DisplayName(schemaName, tableName), DisplayName(principalTableSchemaName, principalTableName));
}
foreignKey = new DatabaseForeignKey
{
Name = constraintName,
Table = table,
PrincipalTable = principalTable,
OnDelete = ConvertToReferentialAction(deleteAction)
};
table.ForeignKeys.Add(foreignKey);
}
if (_tableColumns.TryGetValue(
ColumnKey(foreignKey.Table, fromColumnName), out var fromColumn))
{
foreignKey.Columns.Add(fromColumn);
}
if (foreignKey.PrincipalTable != null)
{
if (_tableColumns.TryGetValue(
ColumnKey(foreignKey.PrincipalTable, toColumnName), out var toColumn))
{
foreignKey.PrincipalColumns.Add(toColumn);
}
}
}
}
}
