static Rule CreateForPk(TableInfo t1, TableInfo t2, List <string> columns)
{
var b = new Rule.Builder(t1, t2, "auto key", 2);
foreach (var col in columns)
{
b.Add(col, col);
}
return(b.Finish());
}
List <Rule> LoadForeignKeyRules(string database)
{
var rules = new List <Rule>();
Database db = _server.Databases[database];
var dataSet = db.ExecuteWithResults(@"
select fk.object_id as fk_id,
SCHEMA_NAME(fk.schema_id) as fk_schema,
fk.name as fk_name,
OBJECT_SCHEMA_NAME(fk.parent_object_id) as base_schema,
OBJECT_NAME(fk.parent_object_id) as base_name,
basecol.name as base_column,
OBJECT_SCHEMA_NAME(fk.referenced_object_id) as ref_schema,
OBJECT_NAME(fk.referenced_object_id) as ref_name,
refcol.name as ref_column
from sys.foreign_keys fk
join sys.foreign_key_columns fkc
on fkc.constraint_object_id = fk.object_id
join sys.columns basecol
on basecol.object_id = fkc.parent_object_id
and basecol.column_id = fkc.parent_column_id
join sys.columns refcol
on refcol.object_id = fkc.referenced_object_id
and refcol.column_id = fkc.referenced_column_id
");
var table = dataSet.Tables[0];
int? lastFk = null;
Rule.Builder builder = null;
TableInfo baseTable = null;
TableInfo refTable = null;
foreach (DataRow row in table.Rows)
{
int? fkId = row["fk_id"] as int?;
string fkSchema = row["fk_schema"] as string;
string fkName = row["fk_name"] as string;
string baseSchema = row["base_schema"] as string;
string baseName = row["base_name"] as string;
string baseColumn = row["base_column"] as string;
string refSchema = row["ref_schema"] as string;
string refName = row["ref_name"] as string;
string refColumn = row["ref_column"] as string;
if (lastFk != fkId)
{
if (builder != null)
{
rules.Add(builder.Finish());
}
lastFk = fkId;
baseTable = CreateTableInfo(database, FindTable(db, baseSchema, baseName));
refTable = CreateTableInfo(database, FindTable(db, refSchema, refName));
builder = new Rule.Builder(baseTable, refTable, "foreign key", 3);
}
builder.Add(baseColumn, refColumn);
}
if (builder != null)
{
rules.Add(builder.Finish());
}
return(rules);
}