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); }