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