/// <summary>
/// Internal API constructor intended for Implementation classes, instead use <see cref="DiscoveredTable.DiscoverRelationships"/> instead.
/// </summary>
/// <param name="fkName"></param>
/// <param name="pkTable"></param>
/// <param name="fkTable"></param>
/// <param name="deleteRule"></param>
public DiscoveredRelationship(string fkName, DiscoveredTable pkTable, DiscoveredTable fkTable, CascadeRule deleteRule)
{
Name = fkName;
PrimaryKeyTable = pkTable;
ForeignKeyTable = fkTable;
CascadeDelete = deleteRule;
Keys = new Dictionary <DiscoveredColumn, DiscoveredColumn>();
}
public OperationBuilderSurface <CreateForeignKeyConstraintOperation> ForeignKey(string name,
Func <TColumns, IEnumerable <ConstraintColumnDefinition> > dependentColumns, string principalTable,
string principalSchema = null, string principalCatalog = null,
IEnumerable <string> principalColumns = null,
CascadeRule onUpdate = CascadeRule.None,
CascadeRule onDelete = CascadeRule.None)
{
var constraintColumnDefinitions = dependentColumns(_instance).ToList();
principalColumns = principalColumns ?? constraintColumnDefinitions.Select(x => x.Column);
var op = new CreateForeignKeyConstraintOperation
{
Name = _operation.Name.MakeSubObjectName(name),
PrincipalTable = new ObjectName {
Catalog = principalCatalog ?? _operation.Name.Catalog, Schema = principalSchema ?? _operation.Name.Schema, Name = principalTable
},
OnDelete = onDelete,
OnUpdate = onUpdate,
DependentColumns = constraintColumnDefinitions,
PrincipalColumns = principalColumns.Select(x => new ConstraintColumnDefinition {
Column = x
}).ToList(),
};
_operation.Constraints.Add(op);
return(new OperationBuilderSurface <CreateForeignKeyConstraintOperation>(op));
}
public override DiscoveredRelationship[] DiscoverRelationships(DiscoveredTable table, DbConnection connection,
IManagedTransaction transaction = null)
{
string sql = $@"select c.constraint_name
, x.table_schema as foreign_table_schema
, x.table_name as foreign_table_name
, x.column_name as foreign_column_name
, y.table_schema
, y.table_name
, y.column_name
, delete_rule
from information_schema.referential_constraints c
join information_schema.key_column_usage x
on x.constraint_name = c.constraint_name
join information_schema.key_column_usage y
on y.ordinal_position = x.position_in_unique_constraint
and y.constraint_name = c.unique_constraint_name
where
y.table_name='{table.GetRuntimeName()}' AND
y.table_schema='{table.Schema??PostgreSqlSyntaxHelper.DefaultPostgresSchema}'
order by c.constraint_name, x.ordinal_position";
Dictionary <string, DiscoveredRelationship> toReturn = new Dictionary <string, DiscoveredRelationship>();
using (var cmd = table.GetCommand(sql, connection, transaction?.Transaction))
{
//fill data table to avoid multiple active readers
using (DataTable dt = new DataTable())
{
using (var da = new NpgsqlDataAdapter((NpgsqlCommand)cmd))
da.Fill(dt);
foreach (DataRow r in dt.Rows)
{
var fkName = r["constraint_name"].ToString();
DiscoveredRelationship current;
//could be a 2+ columns foreign key?
if (toReturn.ContainsKey(fkName))
{
current = toReturn[fkName];
}
else
{
var pkDb = table.Database.GetRuntimeName();
var pkSchema = r["table_schema"].ToString();
var pkTableName = r["table_name"].ToString();
var fkDb = pkDb;
var fkSchema = r["foreign_table_schema"].ToString();
var fkTableName = r["foreign_table_name"].ToString();
var pktable = table.Database.Server.ExpectDatabase(pkDb).ExpectTable(pkTableName, pkSchema);
var fktable = table.Database.Server.ExpectDatabase(fkDb).ExpectTable(fkTableName, fkSchema);
CascadeRule deleteRule = CascadeRule.Unknown;
var deleteRuleString = r["delete_rule"].ToString();
if (deleteRuleString == "CASCADE")
{
deleteRule = CascadeRule.Delete;
}
else if (deleteRuleString == "NO ACTION")
{
deleteRule = CascadeRule.NoAction;
}
else if (deleteRuleString == "RESTRICT")
{
deleteRule = CascadeRule.NoAction;
}
else if (deleteRuleString == "SET NULL")
{
deleteRule = CascadeRule.SetNull;
}
else if (deleteRuleString == "SET DEFAULT")
{
deleteRule = CascadeRule.SetDefault;
}
current = new DiscoveredRelationship(fkName, pktable, fktable, deleteRule);
toReturn.Add(current.Name, current);
}
current.AddKeys(r["column_name"].ToString(), r["foreign_column_name"].ToString(), transaction);
}
}
}
return(toReturn.Values.ToArray());
}
public override DiscoveredRelationship[] DiscoverRelationships(DiscoveredTable table, DbConnection connection, IManagedTransaction transaction = null)
{
var toReturn = new Dictionary <string, DiscoveredRelationship>();
string sql = "exec sp_fkeys @pktable_name = @table, @pktable_qualifier=@database, @pktable_owner=@schema";
using (DbCommand cmd = table.GetCommand(sql, connection))
{
if (transaction != null)
{
cmd.Transaction = transaction.Transaction;
}
var p = cmd.CreateParameter();
p.ParameterName = "@table";
p.Value = table.GetRuntimeName();
p.DbType = DbType.String;
cmd.Parameters.Add(p);
p = cmd.CreateParameter();
p.ParameterName = "@schema";
p.Value = table.Schema ?? "dbo";
p.DbType = DbType.String;
cmd.Parameters.Add(p);
p = cmd.CreateParameter();
p.ParameterName = "@database";
p.Value = table.Database.GetRuntimeName();
p.DbType = DbType.String;
cmd.Parameters.Add(p);
using (DataTable dt = new DataTable())
{
var da = table.Database.Server.GetDataAdapter(cmd);
da.Fill(dt);
foreach (DataRow r in dt.Rows)
{
var fkName = r["FK_NAME"].ToString();
DiscoveredRelationship current;
//could be a 2+ columns foreign key?
if (toReturn.ContainsKey(fkName))
{
current = toReturn[fkName];
}
else
{
var pkdb = r["PKTABLE_QUALIFIER"].ToString();
var pkschema = r["PKTABLE_OWNER"].ToString();
var pktableName = r["PKTABLE_NAME"].ToString();
var pktable = table.Database.Server.ExpectDatabase(pkdb).ExpectTable(pktableName, pkschema);
var fkdb = r["FKTABLE_QUALIFIER"].ToString();
var fkschema = r["FKTABLE_OWNER"].ToString();
var fktableName = r["FKTABLE_NAME"].ToString();
var fktable = table.Database.Server.ExpectDatabase(fkdb).ExpectTable(fktableName, fkschema);
var deleteRuleInt = Convert.ToInt32(r["DELETE_RULE"]);
CascadeRule deleteRule = CascadeRule.Unknown;
if (deleteRuleInt == 0)
{
deleteRule = CascadeRule.Delete;
}
else if (deleteRuleInt == 1)
{
deleteRule = CascadeRule.NoAction;
}
else if (deleteRuleInt == 2)
{
deleteRule = CascadeRule.SetNull;
}
else if (deleteRuleInt == 3)
{
deleteRule = CascadeRule.SetDefault;
}
/*
* https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-fkeys-transact-sql?view=sql-server-2017
*
* 0=CASCADE changes to foreign key.
* 1=NO ACTION changes if foreign key is present.
* 2 = set null
* 3 = set default*/
current = new DiscoveredRelationship(fkName, pktable, fktable, deleteRule);
toReturn.Add(current.Name, current);
}
current.AddKeys(r["PKCOLUMN_NAME"].ToString(), r["FKCOLUMN_NAME"].ToString(), transaction);
}
}
}
return(toReturn.Values.ToArray());
}
public override DiscoveredRelationship[] DiscoverRelationships(DiscoveredTable table, DbConnection connection, IManagedTransaction transaction = null)
{
string sql = @"SELECT
u.CONSTRAINT_NAME,
u.TABLE_SCHEMA,
u.TABLE_NAME,
u.COLUMN_NAME,
u.REFERENCED_TABLE_SCHEMA,
u.REFERENCED_TABLE_NAME,
u.REFERENCED_COLUMN_NAME,
c.DELETE_RULE
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE u
INNER JOIN
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS c ON c.CONSTRAINT_NAME = u.CONSTRAINT_NAME
WHERE
u.REFERENCED_TABLE_SCHEMA = @db AND
u.REFERENCED_TABLE_NAME = @tbl";
var cmd = new MySqlCommand(sql, (MySqlConnection)connection);
var p = new MySqlParameter("@db", MySqlDbType.String);
p.Value = table.Database.GetRuntimeName();
cmd.Parameters.Add(p);
p = new MySqlParameter("@tbl", MySqlDbType.String);
p.Value = table.GetRuntimeName();
cmd.Parameters.Add(p);
Dictionary <string, DiscoveredRelationship> toReturn = new Dictionary <string, DiscoveredRelationship>();
using (var r = cmd.ExecuteReader())
{
while (r.Read())
{
var fkName = r["CONSTRAINT_NAME"].ToString();
DiscoveredRelationship current;
//could be a 2+ columns foreign key?
if (toReturn.ContainsKey(fkName))
{
current = toReturn[fkName];
}
else
{
var pkDb = r["REFERENCED_TABLE_SCHEMA"].ToString();
var pkTableName = r["REFERENCED_TABLE_NAME"].ToString();
var fkDb = r["TABLE_SCHEMA"].ToString();
var fkTableName = r["TABLE_NAME"].ToString();
var pktable = table.Database.Server.ExpectDatabase(pkDb).ExpectTable(pkTableName);
var fktable = table.Database.Server.ExpectDatabase(fkDb).ExpectTable(fkTableName);
//https://dev.mysql.com/doc/refman/8.0/en/referential-constraints-table.html
var deleteRuleString = r["DELETE_RULE"].ToString();
CascadeRule deleteRule = CascadeRule.Unknown;
if (deleteRuleString == "CASCADE")
{
deleteRule = CascadeRule.Delete;
}
else if (deleteRuleString == "NO ACTION")
{
deleteRule = CascadeRule.NoAction;
}
else if (deleteRuleString == "RESTRICT")
{
deleteRule = CascadeRule.NoAction;
}
else if (deleteRuleString == "SET NULL")
{
deleteRule = CascadeRule.SetNull;
}
else if (deleteRuleString == "SET DEFAULT")
{
deleteRule = CascadeRule.SetDefault;
}
current = new DiscoveredRelationship(fkName, pktable, fktable, deleteRule);
toReturn.Add(current.Name, current);
}
current.AddKeys(r["REFERENCED_COLUMN_NAME"].ToString(), r["COLUMN_NAME"].ToString(), transaction);
}
}
return(toReturn.Values.ToArray());
}
public override DiscoveredRelationship[] DiscoverRelationships(DiscoveredTable table, DbConnection connection,
IManagedTransaction transaction = null)
{
string sql = @"
SELECT a.table_name
, a.column_name
, a.constraint_name
, c.owner
, c.delete_rule
, c.r_owner
, c_pk.table_name r_table_name
, c_pk.constraint_name r_pk
, cc_pk.column_name r_column_name
FROM all_cons_columns a
JOIN all_constraints c ON (a.owner = c.owner AND a.constraint_name = c.constraint_name )
JOIN all_constraints c_pk ON (c.r_owner = c_pk.owner AND c.r_constraint_name = c_pk.constraint_name )
JOIN all_cons_columns cc_pk on (cc_pk.constraint_name = c_pk.constraint_name AND cc_pk.owner = c_pk.owner AND cc_pk.position = a.position)
WHERE c.constraint_type = 'R'
AND UPPER(c.r_owner) = UPPER(:DatabaseName)
AND UPPER(c_pk.table_name) = UPPER(:TableName)";
var cmd = new OracleCommand(sql, (OracleConnection)connection);
var p = new OracleParameter(":DatabaseName", OracleDbType.Varchar2);
p.Value = table.Database.GetRuntimeName();
cmd.Parameters.Add(p);
p = new OracleParameter(":TableName", OracleDbType.Varchar2);
p.Value = table.GetRuntimeName();
cmd.Parameters.Add(p);
Dictionary <string, DiscoveredRelationship> toReturn = new Dictionary <string, DiscoveredRelationship>();
using (var r = cmd.ExecuteReader())
{
while (r.Read())
{
var fkName = r["constraint_name"].ToString();
DiscoveredRelationship current;
//could be a 2+ columns foreign key?
if (toReturn.ContainsKey(fkName))
{
current = toReturn[fkName];
}
else
{
var pkDb = r["r_owner"].ToString();
var pkTableName = r["r_table_name"].ToString();
var fkDb = r["owner"].ToString();
var fkTableName = r["table_name"].ToString();
var pktable = table.Database.Server.ExpectDatabase(pkDb).ExpectTable(pkTableName);
var fktable = table.Database.Server.ExpectDatabase(fkDb).ExpectTable(fkTableName);
//https://dev.mysql.com/doc/refman/8.0/en/referential-constraints-table.html
var deleteRuleString = r["delete_rule"].ToString();
CascadeRule deleteRule = CascadeRule.Unknown;
if (deleteRuleString == "CASCADE")
{
deleteRule = CascadeRule.Delete;
}
else if (deleteRuleString == "NO ACTION")
{
deleteRule = CascadeRule.NoAction;
}
else if (deleteRuleString == "RESTRICT")
{
deleteRule = CascadeRule.NoAction;
}
else if (deleteRuleString == "SET NULL")
{
deleteRule = CascadeRule.SetNull;
}
else if (deleteRuleString == "SET DEFAULT")
{
deleteRule = CascadeRule.SetDefault;
}
current = new DiscoveredRelationship(fkName, pktable, fktable, deleteRule);
toReturn.Add(current.Name, current);
}
current.AddKeys(r["r_column_name"].ToString(), r["column_name"].ToString(), transaction);
}
}
return(toReturn.Values.ToArray());
}
public static OperationBuilderSurface <CreateForeignKeyConstraintOperation> AddForeignKey(this IMigrationBuilder builder,
string keyName,
string dependentTable,
string dependentColumn,
string principalTable,
string dependentSchema = null,
string dependentCatalog = null,
string principalCatalog = null,
string principalSchema = null,
string principalColumn = null,
CascadeRule onUpdate = CascadeRule.None,
CascadeRule onDelete = CascadeRule.None)
{
return(AddForeignKey(builder, keyName, dependentTable, new[] { dependentColumn }, principalTable, new [] { principalColumn }, dependentSchema, dependentCatalog, principalCatalog, principalSchema));
}
public static OperationBuilderSurface <CreateForeignKeyConstraintOperation> AddForeignKey(this IMigrationBuilder builder,
string keyName,
string dependentTable,
IEnumerable <string> dependentColumns,
string principalTable,
IEnumerable <string> principalColumns,
string dependentSchema = null,
string dependentCatalog = null,
string principalCatalog = null,
string principalSchema = null,
CascadeRule onUpdate = CascadeRule.None,
CascadeRule onDelete = CascadeRule.None)
{
var op = new CreateForeignKeyConstraintOperation
{
Name = new SubObjectName(dependentCatalog, dependentSchema, dependentTable, keyName),
PrincipalTable = new ObjectName {
Catalog = principalCatalog ?? dependentCatalog, Schema = principalSchema ?? dependentSchema, Name = principalTable
},
OnDelete = onDelete,
OnUpdate = onUpdate,
DependentColumns = dependentColumns.Select(x => new ConstraintColumnDefinition {
Column = x
}).ToList(),
PrincipalColumns = principalColumns.Select(x => new ConstraintColumnDefinition {
Column = x
}).ToList(),
};
builder.AddOperation(op);
return(new OperationBuilderSurface <CreateForeignKeyConstraintOperation>(op));
}