public void It_logs_warning_for_bad_foreign_key_column()
{
// will fail because the referenced column, Id, is not found
var sql = @"CREATE TABLE Parent ( Name PRIMARY KEY);
CREATE TABLE Children (
Id INT PRIMARY KEY,
ParentId INT,
FOREIGN KEY (ParentId) REFERENCES Parent (Id)
);";
GetModel(sql);
Assert.Contains("Debug: " +
SqliteDesignStrings.PrincipalColumnNotFound(0, "Children", "Id", "Parent"),
_logger.FullLog);
}
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);
}
}
}
}
