public void GetForeignKeys(SqlTable table)
{
var rows = _queryResult
.Where(r => DataDefinitionReaderHelper.SchemaAndTableNameEquals(r, table, "OWNER", "TABLE_NAME") && r.GetAs <string>("CONSTRAINT_TYPE") == "R");
foreach (var row in rows)
{
var fkColumn = table.Columns[row.GetAs <string>("COLUMN_NAME")];
// TODO how to query reference in a nother schema?
var referencedSchema = row.GetAs <string>("R_OWNER");
var referencedTable = row.GetAs <string>("REF_TABLE_NAME");
var referencedSchemaAndTableName = new SchemaAndTableName(referencedSchema, referencedTable);
var referencedColumn = row.GetAs <string>("REF_COLUMN_NAME");
var fkName = row.GetAs <string>("CONSTRAINT_NAME");
var referencedSqlTableSchemaAndTableNameAsToStore = GenericDataDefinitionReader.GetSchemaAndTableNameAsToStore(referencedSchemaAndTableName, Executer.Generator.Context);
var referencedSqlTable = table.DatabaseDefinition.GetTable(referencedSqlTableSchemaAndTableNameAsToStore);
if (!table.Properties.OfType <ForeignKey>().Any(fk => fk.SqlTable.SchemaAndTableName == table.SchemaAndTableName && fk.ReferredTable.SchemaAndTableName == referencedSqlTableSchemaAndTableNameAsToStore && fk.Name == fkName))
{
table.Properties.Add(new ForeignKey(table, referencedSqlTable, fkName));
}
var referencedSqlColumn = referencedSqlTable[referencedColumn];
var fk = table.Properties.OfType <ForeignKey>().First(fk1 => fk1.ReferredTable.SchemaAndTableName == referencedSqlTableSchemaAndTableNameAsToStore && fk1.Name == fkName);
fk.ForeignKeyColumns.Add(new ForeignKeyColumnMap(fkColumn, referencedSqlColumn));
}
}
public void GetForeignKeys(SqlTable table)
{
var rows = QueryResult
.Where(r => DataDefinitionReaderHelper.SchemaAndTableNameEquals(r, table, "FK_CONSTRAINT_SCHEMA", "FK_TABLE_NAME"))
.OrderBy(row => row.GetAs <string>("FK_CONSTRAINT_NAME"))
.ThenBy(row => row.GetAs <int>("FK_ORDINAL_POSITION"));
foreach (var row in rows)
{
var fkColumn = table.Columns[row.GetAs <string>("FK_COLUMN_NAME")];
var referencedSchema = row.GetAs <string>("REFERENCED_CONSTRAINT_SCHEMA");
var referencedTable = row.GetAs <string>("REFERENCED_TABLE_NAME");
var referencedSchemaAndTableName = new SchemaAndTableName(referencedSchema, referencedTable);
var referencedColumn = row.GetAs <string>("REFERENCED_COLUMN_NAME");
var fkName = row.GetAs <string>("FK_CONSTRAINT_NAME");
var referencedSqlTableSchemaAndTableNameAsToStore = GenericDataDefinitionReader.GetSchemaAndTableNameAsToStore(referencedSchemaAndTableName, Executer.Generator.Context);
var referencedSqlTable = table.DatabaseDefinition.GetTable(referencedSqlTableSchemaAndTableNameAsToStore);
ForeignKey fk;
if (row.GetAs <int>("FK_ORDINAL_POSITION") == 1)
{
fk = new ForeignKey(table, referencedSqlTable, fkName);
table.Properties.Add(fk);
if (row.GetAs <int>("IsNotTrusted") == 1)
{
fk.SqlEngineVersionSpecificProperties.Add(new SqlEngineVersionSpecificProperty(MsSqlVersion.MsSql2016, "Nocheck", "true"));
}
else if (row.GetAs <int>("IsNotTrusted") == 0)
{
fk.SqlEngineVersionSpecificProperties.Add(new SqlEngineVersionSpecificProperty(MsSqlVersion.MsSql2016, "Nocheck", "false"));
}
}
else
{
fk = table.Properties.OfType <ForeignKey>().First(fk1 => fk1.ReferredTable.SchemaAndTableName == referencedSqlTableSchemaAndTableNameAsToStore && fk1.Name == fkName);
}
var referencedSqlColumn = referencedSqlTable[referencedColumn];
fk.ForeignKeyColumns.Add(new ForeignKeyColumnMap(fkColumn, referencedSqlColumn));
}
}