protected virtual DropForeignKeyOperation Drop(ForeignKeyModel foreignKey)
=> new DropForeignKeyOperation
{
Name = foreignKey.Name,
Table = foreignKey.Table.Name,
Schema = foreignKey.Table.SchemaName
};
private void BtnOK_OnClick(object sender, RoutedEventArgs e)
{
int i = 0;
foreach (var VARIABLE in MainPanel.Children)
{
Type mType = VARIABLE.GetType();
switch (mType.Name)
{
case "TextBox":
obj.GetType().GetProperties()[i].SetValue(obj, ((TextBox)VARIABLE).Text);
i++;
break;
case "ComboBox":
var value = ((DataRowView)((ComboBox)VARIABLE).SelectedItem).Row.ItemArray[0].ToString();
ForeignKeyModel foreignKeyModel = new ForeignKeyModel()
{
name = value,
nameForeignColumn = ((ForeignKeyModel)obj.GetType().GetProperties()[i].GetValue(obj)).nameForeignColumn,
nameForeignTable = ((ForeignKeyModel)obj.GetType().GetProperties()[i].GetValue(obj)).nameForeignTable
};
obj.GetType().GetProperties()[i].SetValue(obj, foreignKeyModel);
break;
}
}
WriterData writerData = new WriterData();
writerData.WriteInDb(obj, WriteMode.INSERTMODE, null);
this.DialogResult = true;
}
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(4) }`"))
{
var fkInfo = new ForeignKeyModel
{
Name = reader.GetString(1),
OnDelete = ConvertToReferentialAction(reader.GetString(6)),
Table = x.Value,
PrincipalTable = _tables[$"`{ reader.GetString(4) }`"]
};
fkInfo.Columns.Add(new ForeignKeyColumnModel
{
Column = x.Value.Columns.Single(y => y.Name == reader.GetString(3)),
PrincipalColumn = _tables[$"`{ reader.GetString(4) }`"].Columns.Single(y => y.Name == reader.GetString(5))
});
x.Value.ForeignKeys.Add(fkInfo);
}
else
{
Logger.LogWarning($"Referenced table `{ reader.GetString(4) }` is not in dictionary.");
}
}
}
}
private void CreateGUI()
{
var mType = obj.GetType();
int i = 0;
foreach (var prop in mType.GetProperties())
{
try
{
Label label = new Label();
label.Content = listDescription[i];
i++;
MainPanel.Children.Add(label);
switch (prop.PropertyType.ToString())
{
case "System.String":
TextBox textBox = new TextBox();
MainPanel.Children.Add(textBox);
break;
case "System.DateTime":
DatePicker datePicker = new DatePicker();
datePicker.SelectedDate = DateTime.Today;
MainPanel.Children.Add(datePicker);
break;
case "System.Int32":
break;
case "CMS.Core.ForeignKeyModel":
ForeignKeyModel foreignKeyModel = (ForeignKeyModel)prop.GetValue(obj);
ComboBox comboBox = new ComboBox();
InteractionsDB interactionsDb = new InteractionsDB();
comboBox.ItemsSource =
interactionsDb.DbExecuteWithReturn(String.Format("select * from {0}", foreignKeyModel.nameForeignTable));
comboBox.DisplayMemberPath = foreignKeyModel.nameForeignColumn;
MainPanel.Children.Add(comboBox);
break;
default:
Console.WriteLine(prop.PropertyType.ToString());
break;
}
}
catch (Exception exception)
{
LoggerHelper.logger.startLog(String.Format("Во время создания запроса произошла ошибка. \n" +
"---------\n" +
"Сообщение: {0}\n" +
"Подробно: {1}\n" +
"Трассировка стека: {2}\n" +
"---------", exception.Message, exception.InnerException, exception.StackTrace));
}
}
}
private void GetForeignKeys()
{
foreach (var dependentTable in _databaseModel.Tables)
{
var fkList = _connection.CreateCommand();
fkList.CommandText = $"PRAGMA foreign_key_list(\"{dependentTable.Name.Replace("\"", "\"\"")}\");";
var tableForeignKeys = new Dictionary <int, ForeignKeyModel>();
using (var reader = fkList.ExecuteReader())
{
while (reader.Read())
{
var id = reader.GetInt32((int)ForeignKeyList.Id);
var principalTableName = reader.GetString((int)ForeignKeyList.Table);
ForeignKeyModel foreignKey;
if (!tableForeignKeys.TryGetValue(id, out foreignKey))
{
TableModel principalTable;
_tables.TryGetValue(principalTableName, out principalTable);
foreignKey = new ForeignKeyModel
{
Table = dependentTable,
PrincipalTable = principalTable
};
tableForeignKeys.Add(id, foreignKey);
}
var fromColumnName = reader.GetString((int)ForeignKeyList.From);
foreignKey.Columns.Add(_tableColumns[ColumnKey(dependentTable, fromColumnName)]);
if (foreignKey.PrincipalTable != null)
{
var toColumnName = reader.GetString((int)ForeignKeyList.To);
ColumnModel toColumn;
if (!_tableColumns.TryGetValue(ColumnKey(foreignKey.PrincipalTable, toColumnName), out toColumn))
{
toColumn = new ColumnModel {
Name = toColumnName
};
}
foreignKey.PrincipalColumns.Add(toColumn);
}
foreignKey.OnDelete = ConvertToReferentialAction(
reader.GetString((int)ForeignKeyList.OnDelete));
}
}
foreach (var foreignKey in tableForeignKeys)
{
dependentTable.ForeignKeys.Add(foreignKey.Value);
}
}
}
protected override ForeignKeyModel MapForeignKey(DataRow row)
{
var foreignKey = new ForeignKeyModel();
foreignKey.TableCatalog = row.GetString("TABLE_CATALOG");
foreignKey.TableSchema = row.GetString("TABLE_SCHEMA");
foreignKey.TableName = row.GetString("TABLE_NAME");
foreignKey.ConstraintName = row.GetString("CONSTRAINT_NAME");
foreignKey.ReferencedTableCatalog = null;
foreignKey.ReferencedTableSchema = null;
foreignKey.ReferencedTableName = null;
foreignKey.ReferencedColumnName = null;
return(foreignKey);
}
protected override ForeignKeyModel MapForeignKey(DataRow row)
{
// todo: this probably needs to be adjusted after correcting SelectForeignKeys.sql
var foreignKey = new ForeignKeyModel();
foreignKey.TableCatalog = row.GetString("table_catalog");
foreignKey.TableSchema = row.GetString("table_schema");
foreignKey.TableName = row.GetString("table_name");
foreignKey.ConstraintName = row.GetString("constraint_name");
foreignKey.ReferencedTableCatalog = row.GetString("foreign_table_catalog");
foreignKey.ReferencedTableSchema = row.GetString("foreign_table_schema");
foreignKey.ReferencedTableName = row.GetString("foreign_table_name");
foreignKey.ReferencedColumnName = row.GetString("foreign_column_name");
return(foreignKey);
}
private static void AssignOnDeleteAction(
[NotNull] ForeignKeyModel fkModel, [NotNull] IMutableForeignKey foreignKey)
{
Check.NotNull(fkModel, nameof(fkModel));
Check.NotNull(foreignKey, nameof(foreignKey));
switch (fkModel.OnDelete)
{
case ReferentialAction.Cascade:
foreignKey.DeleteBehavior = DeleteBehavior.Cascade;
break;
case ReferentialAction.SetNull:
foreignKey.DeleteBehavior = DeleteBehavior.SetNull;
break;
default:
foreignKey.DeleteBehavior = DeleteBehavior.Restrict;
break;
}
}
protected virtual IMutableForeignKey VisitForeignKey([NotNull] ModelBuilder modelBuilder, [NotNull] ForeignKeyModel foreignKey)
{
Check.NotNull(modelBuilder, nameof(modelBuilder));
Check.NotNull(foreignKey, nameof(foreignKey));
if (foreignKey.PrincipalTable == null)
{
Logger.LogWarning(
RelationalDesignEventId.ForeignKeyReferencesMissingTableWarning,
() => RelationalDesignStrings.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 foreignKeyColumns = foreignKey.Columns.OrderBy(fc => fc.Ordinal);
var unmappedDependentColumns = foreignKeyColumns
.Select(fc => fc.Column)
.Where(c => _unmappedColumns.Contains(c))
.Select(c => c.Name)
.ToList();
if (unmappedDependentColumns.Any())
{
Logger.LogWarning(
RelationalDesignEventId.ForeignKeyColumnsNotMappedWarning,
() => RelationalDesignStrings.ForeignKeyScaffoldErrorPropertyNotFound(
foreignKey.DisplayName,
string.Join(CultureInfo.CurrentCulture.TextInfo.ListSeparator, unmappedDependentColumns)));
return(null);
}
var dependentProperties = foreignKeyColumns
.Select(fc => GetPropertyName(fc.Column))
.Select(name => dependentEntityType.FindProperty(name))
.ToList()
.AsReadOnly();
var principalEntityType = modelBuilder.Model.FindEntityType(GetEntityTypeName(foreignKey.PrincipalTable));
if (principalEntityType == null)
{
Logger.LogWarning(
RelationalDesignEventId.ForeignKeyReferencesMissingTableWarning,
() => RelationalDesignStrings.ForeignKeyScaffoldErrorPrincipalTableScaffoldingError(
foreignKey.DisplayName, foreignKey.PrincipalTable.DisplayName));
return(null);
}
var unmappedPrincipalColumns = foreignKeyColumns
.Select(fc => fc.PrincipalColumn)
.Where(pc => principalEntityType.FindProperty(GetPropertyName(pc)) == null)
.Select(pc => pc.Name)
.ToList();
if (unmappedPrincipalColumns.Any())
{
Logger.LogWarning(
RelationalDesignEventId.ForeignKeyColumnsNotMappedWarning,
() => RelationalDesignStrings.ForeignKeyScaffoldErrorPropertyNotFound(
foreignKey.DisplayName,
string.Join(CultureInfo.CurrentCulture.TextInfo.ListSeparator, unmappedPrincipalColumns)));
return(null);
}
var principalProperties = foreignKeyColumns
.Select(fc => GetPropertyName(fc.PrincipalColumn))
.Select(name => principalEntityType.FindProperty(name))
.ToList()
.AsReadOnly();
var principalKey = principalEntityType.FindKey(principalProperties);
if (principalKey == null)
{
var index = principalEntityType.FindIndex(principalProperties);
if (index != null &&
index.IsUnique)
{
principalKey = principalEntityType.AddKey(principalProperties);
}
else
{
var principalColumns = foreignKeyColumns
.Select(c => c.PrincipalColumn.Name)
.Aggregate((a, b) => a + "," + b);
Logger.LogWarning(
RelationalDesignEventId.ForeignKeyReferencesMissingPrincipalKeyWarning,
() => RelationalDesignStrings.ForeignKeyScaffoldErrorPrincipalKeyNotFound(
foreignKey.DisplayName, principalColumns, principalEntityType.DisplayName()));
return(null);
}
}
var key = dependentEntityType.GetOrAddForeignKey(
dependentProperties, principalKey, principalEntityType);
key.IsUnique = dependentEntityType.FindKey(dependentProperties) != null;
key.Relational().Name = foreignKey.Name;
AssignOnDeleteAction(foreignKey, key);
return(key);
}
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 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 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 = "";
ForeignKeyModel fkInfo = null;
while (reader.Read())
{
var fkName = reader.GetValueOrDefault <string>("FK_CONSTRAINT_NAME");
var tableName = reader.GetValueOrDefault <string>("FK_TABLE_NAME");
if (!_tableSelectionSet.Allows(tableName))
{
continue;
}
if ((fkInfo == null) ||
(lastFkName != fkName))
{
lastFkName = fkName;
var principalTableName = reader.GetValueOrDefault <string>("UQ_TABLE_NAME");
var table = _tables[TableKey(tableName)];
TableModel principalTable;
_tables.TryGetValue(TableKey(principalTableName), out principalTable);
fkInfo = new ForeignKeyModel
{
Name = fkName,
Table = table,
PrincipalTable = principalTable,
OnDelete = ConvertToReferentialAction(reader.GetValueOrDefault <string>("DELETE_RULE"))
};
table.ForeignKeys.Add(fkInfo);
}
var fkColumn = new ForeignKeyColumnModel
{
Ordinal = reader.GetValueOrDefault <int>("ORDINAL_POSITION")
};
var fromColumnName = reader.GetValueOrDefault <string>("FK_COLUMN_NAME");
ColumnModel fromColumn;
if ((fromColumn = FindColumnForForeignKey(fromColumnName, fkInfo.Table, fkName)) != null)
{
fkColumn.Column = fromColumn;
}
if (fkInfo.PrincipalTable != null)
{
var toColumnName = reader.GetValueOrDefault <string>("UQ_COLUMN_NAME");
ColumnModel toColumn;
if ((toColumn = FindColumnForForeignKey(toColumnName, fkInfo.PrincipalTable, fkName)) != null)
{
fkColumn.PrincipalColumn = toColumn;
}
}
fkInfo.Columns.Add(fkColumn);
}
}
}
protected override bool AcceptForeignKey(ForeignKeyModel foreignKey) => false;
protected virtual bool AcceptForeignKey(ForeignKeyModel foreignKey) => true;
private void GetForeignKeys()
{
foreach (var dependentTable in _databaseModel.Tables)
{
using (var fkList = _connection.CreateCommand())
{
fkList.CommandText = $"PRAGMA foreign_key_list(\"{dependentTable.Name.Replace("\"", "\"\"")}\");"; // Interpolation okay; strings
var tableForeignKeys = new Dictionary <int, ForeignKeyModel>();
using (var reader = fkList.ExecuteReader())
{
while (reader.Read())
{
var id = reader.GetValueOrDefault <int>("id");
var principalTableName = reader.GetValueOrDefault <string>("table");
var fromColumnName = reader.GetValueOrDefault <string>("from");
var toColumnName = reader.GetValueOrDefault <string>("to");
var deleteAction = reader.GetValueOrDefault <string>("on_delete");
var fkOrdinal = reader.GetValueOrDefault <int>("seq");
Logger.LogTrace(SqliteDesignStrings.FoundForeignKeyColumn(
dependentTable.Name, id, principalTableName, fromColumnName,
toColumnName, deleteAction, fkOrdinal));
ForeignKeyModel foreignKey;
if (!tableForeignKeys.TryGetValue(id, out foreignKey))
{
TableModel principalTable;
if (!_tables.TryGetValue(principalTableName, out principalTable))
{
Logger.LogTrace(SqliteDesignStrings.PrincipalTableNotFound(
id, dependentTable.Name, principalTableName));
continue;
}
foreignKey = new ForeignKeyModel
{
Table = dependentTable,
PrincipalTable = principalTable,
OnDelete = ConvertToReferentialAction(deleteAction)
};
}
var fkColumn = new ForeignKeyColumnModel
{
Ordinal = fkOrdinal,
Column = _tableColumns[ColumnKey(dependentTable, fromColumnName)]
};
ColumnModel toColumn;
if (!_tableColumns.TryGetValue(ColumnKey(foreignKey.PrincipalTable, toColumnName), out toColumn))
{
Logger.LogTrace(SqliteDesignStrings.PrincipalColumnNotFound(
id, dependentTable.Name, toColumnName, principalTableName));
continue;
}
fkColumn.PrincipalColumn = toColumn;
foreignKey.Columns.Add(fkColumn);
if (!tableForeignKeys.ContainsKey(id))
{
tableForeignKeys.Add(id, foreignKey);
}
}
}
foreach (var foreignKey in tableForeignKeys)
{
dependentTable.ForeignKeys.Add(foreignKey.Value);
}
}
}
}
private void GetForeignKeys()
{
var command = _connection.CreateCommand();
command.CommandText = @"SELECT
f.name AS foreign_key_name,
schema_name(f.schema_id) AS [schema_name],
object_name(f.parent_object_id) AS table_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
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.object_id = fc.constraint_object_id
ORDER BY f.name, fc.constraint_column_id";
using (var reader = command.ExecuteReader())
{
var lastFkName = "";
ForeignKeyModel fkInfo = null;
while (reader.Read())
{
var fkName = reader.GetString(0);
var schemaName = reader.GetString(1);
var tableName = reader.GetString(2);
if (!_tableSelectionSet.Allows(schemaName, tableName))
{
continue;
}
if (fkInfo == null ||
lastFkName != fkName)
{
lastFkName = fkName;
var principalSchemaTableName = reader.GetString(3);
var principalTableName = reader.GetString(4);
var table = _tables[TableKey(tableName, schemaName)];
TableModel principalTable;
_tables.TryGetValue(TableKey(principalTableName, principalSchemaTableName), out principalTable);
fkInfo = new ForeignKeyModel
{
Table = table,
PrincipalTable = principalTable
};
table.ForeignKeys.Add(fkInfo);
}
var fromColumnName = reader.GetString(5);
var fromColumn = _tableColumns[ColumnKey(fkInfo.Table, fromColumnName)];
fkInfo.Columns.Add(fromColumn);
if (fkInfo.PrincipalTable != null)
{
var toColumnName = reader.GetString(6);
var toColumn = _tableColumns[ColumnKey(fkInfo.PrincipalTable, toColumnName)];
fkInfo.PrincipalColumns.Add(toColumn);
}
fkInfo.OnDelete = ConvertToReferentialAction(reader.GetString(8));
}
}
}
protected override DropForeignKeyOperation Drop(ForeignKeyModel foreignKey) => AddMemoryOptimizedAnnotation(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 kc.table_schema IN ({_schemaList})
AND kc.table_name <> '{HistoryRepository.DefaultTableName}'";
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
ForeignKeyModel fkModel = 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)];
TableModel 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 });
}
fkModel = new ForeignKeyModel
{
Name = constraintName,
Table = table,
PrincipalTable = principalTable,
OnDelete = ConvertToReferentialAction(deleteRule)
};
var fkColumn = new ForeignKeyColumnModel
{
Ordinal = (int)ordinal
};
ColumnModel fromColumn = FindColumnForForeignKey(columnName, fkModel.Table, constraintName);
if (fromColumn != null)
{
fkColumn.Column = fromColumn;
}
if (fkModel.PrincipalTable != null)
{
ColumnModel toColumn = FindColumnForForeignKey(referencedColumnName, fkModel.PrincipalTable, constraintName);
if (toColumn != null)
{
fkColumn.PrincipalColumn = toColumn;
}
}
fkModel.Columns.Add(fkColumn);
table.ForeignKeys.Add(fkModel);
}
}
}
void GetConstraints()
{
using (var command = new NpgsqlCommand(GetConstraintsQuery, _connection))
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var schemaName = reader.GetString(0);
var tableName = reader.GetString(1);
if (!_tableSelectionSet.Allows(schemaName, tableName))
{
continue;
}
var table = _tables[TableKey(tableName, schemaName)];
var columns = (List <ColumnModel>)table.Columns;
var constraintName = reader.GetString(2);
var constraintType = reader.GetChar(3);
switch (constraintType)
{
case 'p':
var pkColumnIndices = reader.GetFieldValue <short[]>(4);
for (var i = 0; i < pkColumnIndices.Length; i++)
{
columns[pkColumnIndices[i] - 1].PrimaryKeyOrdinal = i + 1;
}
continue;
case 'f':
var foreignSchemaName = reader.GetString(5);
var foreignTableName = reader.GetString(6);
TableModel principalTable;
if (!_tables.TryGetValue(TableKey(foreignTableName, foreignSchemaName), out principalTable))
{
continue;
}
var fkInfo = new ForeignKeyModel
{
Name = constraintName,
Table = table,
PrincipalTable = principalTable,
OnDelete = ConvertToReferentialAction(reader.GetChar(8))
};
var columnIndices = reader.GetFieldValue <short[]>(4);
var principalColumnIndices = reader.GetFieldValue <short[]>(7);
if (columnIndices.Length != principalColumnIndices.Length)
{
throw new Exception("Got varying lengths for column and principal column indices");
}
var principalColumns = (List <ColumnModel>)principalTable.Columns;
for (var ordinal = 0; ordinal < columnIndices.Length; ordinal++)
{
fkInfo.Columns.Add(new ForeignKeyColumnModel {
Column = columns[columnIndices[ordinal] - 1],
PrincipalColumn = principalColumns[principalColumnIndices[ordinal] - 1],
Ordinal = ordinal
});
}
table.ForeignKeys.Add(fkInfo);
break;
default:
throw new NotSupportedException($"Unknown constraint type code {constraintType} for constraint {constraintName}");
}
}
}
}
private void GetForeignKeys()
{
foreach (var dependentTable in _databaseModel.Tables)
{
var fkList = _connection.CreateCommand();
fkList.CommandText = $"PRAGMA foreign_key_list(\"{dependentTable.Name.Replace("\"", "\"\"")}\");";
var tableForeignKeys = new Dictionary <int, ForeignKeyModel>();
using (var reader = fkList.ExecuteReader())
{
while (reader.Read())
{
var id = reader.GetValueOrDefault <int>("id");
var fkOrdinal = reader.GetValueOrDefault <int>("seq");
var principalTableName = reader.GetValueOrDefault <string>("table");
ForeignKeyModel foreignKey;
if (!tableForeignKeys.TryGetValue(id, out foreignKey))
{
TableModel principalTable;
_tables.TryGetValue(principalTableName, out principalTable);
foreignKey = new ForeignKeyModel
{
Table = dependentTable,
PrincipalTable = principalTable,
OnDelete = ConvertToReferentialAction(reader.GetValueOrDefault <string>("on_delete"))
};
tableForeignKeys.Add(id, foreignKey);
}
var fromColumnName = reader.GetValueOrDefault <string>("from");
var fkColumn = new ForeignKeyColumnModel
{
Ordinal = fkOrdinal
};
fkColumn.Column = _tableColumns[ColumnKey(dependentTable, fromColumnName)];
if (foreignKey.PrincipalTable != null)
{
var toColumnName = reader.GetValueOrDefault <string>("to");
ColumnModel toColumn;
if (!_tableColumns.TryGetValue(ColumnKey(foreignKey.PrincipalTable, toColumnName), out toColumn))
{
toColumn = new ColumnModel {
Name = toColumnName
};
}
fkColumn.PrincipalColumn = toColumn;
}
foreignKey.Columns.Add(fkColumn);
}
}
foreach (var foreignKey in tableForeignKeys)
{
dependentTable.ForeignKeys.Add(foreignKey.Value);
}
}
}
protected virtual IMutableForeignKey VisitForeignKey([NotNull] ModelBuilder modelBuilder, [NotNull] ForeignKeyModel foreignKey)
{
Check.NotNull(modelBuilder, nameof(modelBuilder));
Check.NotNull(foreignKey, nameof(foreignKey));
if (foreignKey.PrincipalTable == null)
{
Logger.LogWarning(
RelationalDesignEventId.ForeignKeyReferencesMissingTableWarning,
() => RelationalDesignStrings.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 foreignKeyColumns = foreignKey.Columns.OrderBy(fc => fc.Ordinal);
var unmappedDependentColumns = foreignKeyColumns
.Select(fc => fc.Column)
.Where(c => _unmappedColumns.Contains(c))
.Select(c => c.Name)
.ToList();
if (unmappedDependentColumns.Any())
{
Logger.LogWarning(
RelationalDesignEventId.ForeignKeyColumnsNotMappedWarning,
() => RelationalDesignStrings.ForeignKeyScaffoldErrorPropertyNotFound(
foreignKey.DisplayName,
string.Join(CultureInfo.CurrentCulture.TextInfo.ListSeparator, unmappedDependentColumns)));
return(null);
}
var dependentProperties = foreignKeyColumns
.Select(fc => GetPropertyName(fc.Column))
.Select(name => dependentEntityType.FindProperty(name))
.ToList()
.AsReadOnly();
var principalEntityType = modelBuilder.Model.FindEntityType(GetEntityTypeName(foreignKey.PrincipalTable));
if (principalEntityType == null)
{
Logger.LogWarning(
RelationalDesignEventId.ForeignKeyReferencesMissingTableWarning,
() => RelationalDesignStrings.ForeignKeyScaffoldErrorPrincipalTableScaffoldingError(
foreignKey.DisplayName, foreignKey.PrincipalTable.DisplayName));
return(null);
}
var unmappedPrincipalColumns = foreignKeyColumns
.Select(fc => fc.PrincipalColumn)
.Where(pc => principalEntityType.FindProperty(GetPropertyName(pc)) == null)
.Select(pc => pc.Name)
.ToList();
if (unmappedPrincipalColumns.Any())
{
Logger.LogWarning(
RelationalDesignEventId.ForeignKeyColumnsNotMappedWarning,
() => RelationalDesignStrings.ForeignKeyScaffoldErrorPropertyNotFound(
foreignKey.DisplayName,
string.Join(CultureInfo.CurrentCulture.TextInfo.ListSeparator, unmappedPrincipalColumns)));
return(null);
}
var principalPropertiesMap = foreignKeyColumns
.Select(fc => new Tuple <IMutableProperty, ColumnModel>(
principalEntityType.FindProperty(GetPropertyName(fc.PrincipalColumn)),
fc.PrincipalColumn));
var principalProperties = principalPropertiesMap
.Select(tuple => tuple.Item1).ToList();
var principalKey = principalEntityType.FindKey(principalProperties);
if (principalKey == null)
{
var index = principalEntityType.FindIndex(principalProperties.AsReadOnly());
if (index != null &&
index.IsUnique)
{
// 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.Item1.IsNullable);
if (nullablePrincipalProperties.Any())
{
Logger.LogWarning(
RelationalDesignEventId.ForeignKeyPrincipalEndContainsNullableColumns,
() => RelationalDesignStrings.ForeignKeyPrincipalEndContainsNullableColumns(
foreignKey.DisplayName,
index.Relational().Name,
nullablePrincipalProperties
.Select(tuple => tuple.Item2.DisplayName)
.Aggregate((a, b) => a + "," + b)));
nullablePrincipalProperties
.ToList().ForEach(tuple => tuple.Item1.IsNullable = false);
}
principalKey = principalEntityType.AddKey(principalProperties);
}
else
{
var principalColumns = foreignKeyColumns
.Select(c => c.PrincipalColumn.Name)
.Aggregate((a, b) => a + "," + b);
Logger.LogWarning(
RelationalDesignEventId.ForeignKeyReferencesMissingPrincipalKeyWarning,
() => RelationalDesignStrings.ForeignKeyScaffoldErrorPrincipalKeyNotFound(
foreignKey.DisplayName, principalColumns, principalEntityType.DisplayName()));
return(null);
}
}
var key = dependentEntityType.GetOrAddForeignKey(
dependentProperties, principalKey, principalEntityType);
var dependentKey = dependentEntityType.FindKey(dependentProperties);
var dependentIndex = dependentEntityType.FindIndex(dependentProperties);
key.IsUnique = dependentKey != null ||
(dependentIndex != null && dependentIndex.IsUnique);
key.Relational().Name = foreignKey.Name;
AssignOnDeleteAction(foreignKey, key);
return(key);
}
public static ColumnModel GetPrincipalColumn(this ForeignKeyModel fk)
{
return(fk.Columns.Single().PrincipalColumn);
}
protected virtual IMutableForeignKey VisitForeignKey([NotNull] ModelBuilder modelBuilder, [NotNull] ForeignKeyModel foreignKey)
{
Check.NotNull(modelBuilder, nameof(modelBuilder));
Check.NotNull(foreignKey, nameof(foreignKey));
if (foreignKey.PrincipalTable == null)
{
Logger.LogWarning(RelationalDesignStrings.ForeignKeyScaffoldErrorPrincipalTableNotFound(foreignKey.DisplayName));
return(null);
}
var dependentEntityType = modelBuilder.Model.FindEntityType(GetEntityTypeName(foreignKey.Table));
if (dependentEntityType == null)
{
return(null);
}
var depProps = foreignKey.Columns
.Select(GetPropertyName)
.Select(@from => dependentEntityType.FindProperty(@from))
.ToList()
.AsReadOnly();
if (depProps.Any(p => p == null))
{
// TODO log which column was not found
Logger.LogWarning(RelationalDesignStrings.ForeignKeyScaffoldErrorPropertyNotFound(foreignKey.DisplayName));
return(null);
}
var principalEntityType = modelBuilder.Model.FindEntityType(GetEntityTypeName(foreignKey.PrincipalTable));
if (principalEntityType == null)
{
Logger.LogWarning(RelationalDesignStrings.ForeignKeyScaffoldErrorPrincipalTableScaffoldingError(foreignKey.DisplayName, foreignKey.PrincipalTable.DisplayName));
return(null);
}
var principalProps = foreignKey.PrincipalColumns
.Select(GetPropertyName)
.Select(to => principalEntityType.FindProperty(to))
.ToList()
.AsReadOnly();
if (principalProps.Any(p => p == null))
{
Logger.LogWarning(RelationalDesignStrings.ForeignKeyScaffoldErrorPropertyNotFound(foreignKey.DisplayName));
return(null);
}
var principalKey = principalEntityType.FindKey(principalProps);
if (principalKey == null)
{
var index = principalEntityType.FindIndex(principalProps);
if (index != null &&
index.IsUnique == true)
{
principalKey = principalEntityType.AddKey(principalProps);
}
else
{
var principalColumns = foreignKey.PrincipalColumns.Select(c => c.Name).Aggregate((a, b) => a + "," + b);
Logger.LogWarning(
RelationalDesignStrings.ForeignKeyScaffoldErrorPrincipalKeyNotFound(
foreignKey.DisplayName, principalColumns, principalEntityType.DisplayName()));
return(null);
}
}
var key = dependentEntityType.GetOrAddForeignKey(depProps, principalKey, principalEntityType);
key.IsUnique = dependentEntityType.FindKey(depProps) != null;
AssignOnDeleteAction(foreignKey, key);
return(key);
}
