private void GetForeignKeys()
{
foreach (var dependentTable in _databaseModel.Tables)
{
using (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 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
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.LogTrace(SqlServerDesignStrings.FoundForeignKeyColumn(
schemaName, tableName, fkName, principalTableSchemaName, principalTableName,
fromColumnName, toColumnName, updateAction, deleteAction, ordinal));
if (string.IsNullOrEmpty(fkName))
{
Logger.LogWarning(SqlServerDesignStrings.ForeignKeyNameEmpty(schemaName, tableName));
continue;
}
if (!_tableSelectionSet.Allows(schemaName, tableName))
{
Logger.LogTrace(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.LogTrace(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);
}
}
}