/// <summary>
/// TODO: The extraction will ignore foreign-keys between schemas
/// </summary>
/// <param name="schemaName"></param>
/// <returns></returns>
public override List <ForeignKey> GetForeignKeys(string schemaName)
{
if (this._columns == null)
{
this.GetColumns(schemaName);
}
var stmt = String.Format(
@"select
conname as key_name,
condeferrable as is_deferrable,
condeferred as initially_deferred,
confupdtype as update_rule,
confdeltype as delete_rule,
conkey as f_column_position,
confkey as p_column_position,
f.relname as table_name,
p.relname as pri_table_name
from (select * from pg_catalog.pg_constraint where connamespace = (select oid from pg_namespace where nspname = '{0}') and contype = 'f') as fky
inner join pg_class as f on fky.conrelid = f.relfilenode
inner join pg_class as p on fky.confrelid = p.relfilenode", schemaName);
string f_table, p_table;
var ds = this.ExecuteSelect(stmt);
var result = new List <ForeignKey>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
f_table = dr["table_name"].ToString();
p_table = dr["pri_table_name"].ToString();
if (!this._columns.ContainsKey(p_table))
{
continue;
}
var fk = new ForeignKey(schemaName, f_table, dr["key_name"].ToString(), schemaName, p_table);
fk.UpdateRule = this.ParseUpdateRule(dr["update_rule"].ToString());
fk.DeleteRule = this.ParseDeleteRule(dr["delete_rule"].ToString());
fk.Deferrability = (dr["is_deferrable"].ToString().ToLower() == "t") ? Deferrability.Deferrable : Deferrability.NotDeferrable;
fk.InitialMode = (dr["initially_deferred"].ToString().ToLower() == "t") ? InitialMode.InitiallyDeferred : InitialMode.InitiallyImmediate;
var fk_columns = dr["f_column_position"] as Int16[];
var pk_columns = dr["p_column_position"] as Int16[];
for (int i = 0; i < fk_columns.Length; i++)
{
fk.AddColumn(i,
this._columns[f_table].Single(c => c.OrdinalPosition == fk_columns[i]).ColumnName,
this._columns[p_table].Single(c => c.OrdinalPosition == pk_columns[i]).ColumnName);
}
result.Add(fk);
}
return(result);
}
public void UnmatchingColumns()
{
Table primaryTable = new Table("pktable");
primaryTable.PrimaryKey = new PrimaryKey();
SimpleValue sv = new SimpleValue();
sv.TypeName = NHibernateUtil.Int16.Name;
Column pkColumn = new Column("pk_column");
pkColumn.Value = sv;
primaryTable.PrimaryKey.AddColumn(pkColumn);
Table fkTable = new Table("fktable");
ForeignKey fk = new ForeignKey();
sv = new SimpleValue();
sv.TypeName = NHibernateUtil.Int16.Name;
Column fkColumn1 = new Column("col1");
fkColumn1.Value = sv;
sv = new SimpleValue();
sv.TypeName = NHibernateUtil.Int16.Name;
Column fkColumn2 = new Column("col2");
fkColumn2.Value = sv;
fk.AddColumn(fkColumn1);
fk.AddColumn(fkColumn2);
fk.Table = fkTable;
fk.ReferencedTable = primaryTable;
Assert.Throws <FKUnmatchingColumnsException>(() => fk.AlignColumns());
}
public override List <ForeignKey> GetForeignKeys(string schemaName)
{
var stmt = String.Format(
@"select
fky.table_schema as schema_name,
fky.table_name,
fky.constraint_name as key_name,
fky.column_name,
pky.table_schema as pri_schema_name,
pky.table_name as pri_table_name,
pky.column_name as pri_column_name,
fky.ordinal_position as ordinal_position,
rco.update_rule,
rco.delete_rule,
co.is_deferrable,
co.initially_deferred
from information_schema.key_column_usage as fky
inner join information_schema.table_constraints as co
on fky.constraint_name = co.constraint_name
inner join information_schema.referential_constraints as rco
on rco.constraint_name = co.constraint_name
inner join information_schema.key_column_usage as pky
on pky.constraint_name = rco.unique_constraint_name
and fky.ordinal_position = pky.ordinal_position
where co.constraint_schema = '{0}'
and co.constraint_type = 'FOREIGN KEY'
order by table_name, key_name, ordinal_position",
schemaName);
var ds = this.ExecuteSelect(stmt);
var cList = new Utils.ChainedList <ForeignKey>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
var fk = new ForeignKey(dr["schema_name"].ToString(), dr["table_name"].ToString(), dr["key_name"].ToString(),
dr["pri_schema_name"].ToString(), dr["pri_table_name"].ToString());
fk.UpdateRule = this.ParseUpdateRule(dr["update_rule"].ToString());
fk.DeleteRule = this.ParseDeleteRule(dr["delete_rule"].ToString());
fk.Deferrability = this.ParseBoolean(dr["is_deferrable"].ToString()) ? Deferrability.Deferrable : Deferrability.NotDeferrable;
fk.InitialMode = this.ParseBoolean(dr["initially_deferred"].ToString()) ? InitialMode.InitiallyDeferred : InitialMode.InitiallyImmediate;
fk.AddColumn(Convert.ToInt32(dr["ordinal_position"]), dr["column_name"].ToString(), dr["pri_column_name"].ToString());
ForeignKey tmp;
if (!cList.Add(fk, out tmp))
{
tmp.AddColumns(fk.IndexOrderdColumnNames);
}
}
return(cList.ToList());
}
public override List <ForeignKey> GetForeignKeys(string schemaName)
{
var stmt = String.Format(
@"select
c.owner as schema_name,
c.constraint_name as key_name,
c.delete_rule,
c.deferrable,
c.deferred,
source.table_name as table_name,
source.column_name as column_name,
source.position,
target.owner as pri_schema_name,
target.table_name as pri_table_name,
target.column_name as pri_column_name
from
all_constraints c,
all_cons_columns source,
all_cons_columns target
where c.owner = '{0}'
and c.constraint_type = 'R'
and c.constraint_name = source.constraint_name
and c.r_constraint_name = target.constraint_name
and source.position = target.position
and source.owner = '{0}'
and target.owner = '{0}'", schemaName);
using (var ds = this.ExecuteSelect(stmt))
{
var cList = new Utils.ChainedList <ForeignKey>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
var fk = new ForeignKey(dr["schema_name"].ToString(), dr["table_name"].ToString(), dr["key_name"].ToString(),
dr["pri_schema_name"].ToString(), dr["pri_table_name"].ToString());
// TODO missing update rule
fk.DeleteRule = this.ParseDeleteRule(dr["delete_rule"].ToString());
fk.Deferrability = this.ParseDeferrability(dr["deferrable"].ToString());
fk.InitialMode = this.ParseInitialMode(dr["deferred"].ToString());
fk.AddColumn(Convert.ToInt32(dr["position"]), dr["column_name"].ToString(), dr["pri_column_name"].ToString());
ForeignKey tmp;
if (!cList.Add(fk, out tmp))
{
tmp.AddColumns(fk.IndexOrderdColumnNames);
}
}
return(cList.ToList());
}
}
public override List <DBObjects.ForeignKey> GetForeignKeys(string schemaName)
{
var stmt = String.Format(
@"select
ccol.constname as key_name,
ccol.tabschema as child_schema_name,
ccol.tabname as child_table_name,
ccol.colname as child_column_name,
pcol.tabschema as parent_schema_name,
pcol.tabname as parent_table_name,
pcol.colname as parent_column_name,
ccol.colseq as ordinal_position,
fk.deleterule as delete_rule, -- A = NO ACTION, C = CASCADE, N = SET NULL, R = RESTRICT
fk.updaterule as update_rule -- A = NO ACTION, C = CASCADE, N = SET NULL, R = RESTRICT
from
syscat.references as fk,
syscat.keycoluse as pcol, -- The parent columns
syscat.keycoluse as ccol -- The child columns
where
fk.tabschema = '{0}'
and ccol.tabschema = fk.tabschema
and ccol.tabname = fk.tabname
and ccol.constname = fk.constname
and pcol.tabschema = fk.tabschema
and pcol.tabname = fk.reftabname
and pcol.constname = fk.refkeyname
and ccol.colseq = pcol.colseq
order by ccol.tabname, ccol.constname, ccol.colseq" , schemaName);
var ds = this.ExecuteSelect(stmt);
var cList = new Utils.ChainedList <ForeignKey>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
var fk = new ForeignKey(dr["child_schema_name"].ToString(), dr["child_table_name"].ToString(), dr["key_name"].ToString(),
dr["parent_schema_name"].ToString(), dr["parent_table_name"].ToString());
fk.UpdateRule = this.ParseUpdateRule(dr["update_rule"].ToString());
fk.DeleteRule = this.ParseDeleteRule(dr["delete_rule"].ToString());
fk.AddColumn(Convert.ToInt32(dr["ordinal_position"]), dr["child_column_name"].ToString(), dr["parent_column_name"].ToString());
ForeignKey tmp;
if (!cList.Add(fk, out tmp))
{
tmp.AddColumns(fk.IndexOrderdColumnNames);
}
}
return(cList.ToList());
}
public void ToStringDoesNotThrow()
{
var key = new ForeignKey
{
Table = new Table("TestTable"),
Name = "TestForeignKey"
};
key.AddColumn(new Column("TestColumn"));
key.AddReferencedColumns(new[] { new Column("ReferencedColumn") });
string toString = null;
Assert.DoesNotThrow(() =>
{
toString = key.ToString();
});
Assert.That(toString, Is.EqualTo("NHibernate.Mapping.ForeignKey(TestTableNHibernate.Mapping.Column(TestColumn) ref-columns:(NHibernate.Mapping.Column(ReferencedColumn)) as TestForeignKey"));
}
public override List <DBObjects.ForeignKey> GetForeignKeys(string schemaName)
{
var stmt = String.Format(
@"SELECT
rc.rdb$constraint_name AS FkName,
rcc.rdb$relation_name AS ChildTable,
isc.rdb$field_name AS ChildColumn,
isc.rdb$field_position+1 AS ChildPosition,
rcp.rdb$relation_name AS ParentTable,
isp.rdb$field_name AS ParentColumn,
rc.rdb$update_rule AS UpdateRule,
rc.rdb$delete_rule AS DeleteRule,
rcc.rdb$deferrable AS Deferrable,
rcc.rdb$initially_deferred AS InitiallyDeferred
FROM
rdb$ref_constraints rc
INNER JOIN rdb$relation_constraints rcc on rc.rdb$constraint_name = rcc.rdb$constraint_name
INNER JOIN rdb$index_segments isc on rcc.rdb$index_name = isc.rdb$index_name
INNER JOIN rdb$relation_constraints rcp on rc.rdb$const_name_uq = rcp.rdb$constraint_name
INNER JOIN rdb$index_segments isp on rcp.rdb$index_name = isp.rdb$index_name;");
using (var ds = this.ExecuteSelect(stmt))
{
var cList = new Utils.ChainedList <ForeignKey>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
var fk = new ForeignKey(this._schemaName, dr["ChildTable"].ToString().Trim(), dr["FkName"].ToString().Trim(), this._schemaName, dr["ParentTable"].ToString().Trim());
fk.DeleteRule = this.ParseDeleteRule(dr["DeleteRule"].ToString().Trim());
fk.UpdateRule = this.ParseUpdateRule(dr["UpdateRule"].ToString().Trim());
fk.InitialMode = (this.ParseBoolean(dr["InitiallyDeferred"].ToString().Trim())) ? InitialMode.InitiallyDeferred : InitialMode.InitiallyImmediate;
fk.Deferrability = (this.ParseBoolean(dr["Deferrable"].ToString().Trim())) ? Deferrability.Deferrable : Deferrability.NotDeferrable;
fk.AddColumn(Convert.ToInt32(dr["ChildPosition"]), dr["ChildColumn"].ToString().Trim(), dr["ParentColumn"].ToString().Trim());
ForeignKey tmp;
if (!cList.Add(fk, out tmp))
{
tmp.AddColumns(fk.IndexOrderdColumnNames);
}
}
return(cList.ToList());
}
}
public override List <ForeignKey> GetForeignKeys(string schemaName)
{
var stmt = String.Format(
@"select
fky.constraint_schema as schema_name,
fky.table_name,
fky.constraint_name as key_name,
fky.column_name,
fky.referenced_table_schema as pri_schema_name,
fky.referenced_table_name as pri_table_name,
fky.referenced_column_name as pri_column_name,
fky.ordinal_position,
rco.update_rule,
rco.delete_rule
from (select * from information_schema.key_column_usage where table_schema = '{0}') as fky
inner join (select constraint_schema as table_schema,
constraint_name,
table_name,
referenced_table_name,
unique_constraint_name,
update_rule,
delete_rule
from information_schema.referential_constraints
WHERE CONSTRAINT_SCHEMA = '{0}'
) as rco
using (table_schema, constraint_name, table_name, referenced_table_name)",
schemaName);
// Hack, to avoide the missing referential_constraints table on old MySQL Versions, default to "Set Default" rules
if (this._runningVersion != null && this._runningVersion.Major == 5 && this._runningVersion.Minor == 0)
{
stmt = String.Format(@"select
constraint_schema as schema_name,
table_name,
constraint_name as key_name,
column_name,
referenced_table_schema as pri_schema_name,
referenced_table_name as pri_table_name,
referenced_column_name as pri_column_name,
ordinal_position,
'RESTRICT' as update_rule,
'CASCADE' as delete_rule
from information_schema.key_column_usage where table_schema = '{0}'
and constraint_name <> 'PRIMARY'",
schemaName);
}
var constraints = from c in GetConstraints(schemaName)
where c.ConstraintType == "FOREIGN KEY"
select c;
var ds = this.ExecuteSelect(stmt);
var rows = from dr in ds.Tables[0].Rows.Cast <DataRow>()
join c in constraints on dr["table_name"].ToString() equals c.TableName
where c.ConstraintName == dr["key_name"].ToString()
select dr;
var cList = new Utils.ChainedList <ForeignKey>();
foreach (DataRow dr in rows)
{
var fk = new ForeignKey(dr["schema_name"].ToString(), dr["table_name"].ToString(), dr["key_name"].ToString(),
dr["pri_schema_name"].ToString(), dr["pri_table_name"].ToString());
fk.UpdateRule = this.ParseUpdateRule(dr["update_rule"].ToString());
fk.DeleteRule = this.ParseDeleteRule(dr["delete_rule"].ToString());
// fk.IsDeferrable = this.ParseBoolean(dr["is_deferrable"].ToString()); // TODO NOT EXTRACTED FROM MYSQL
fk.AddColumn(Convert.ToInt32(dr["ordinal_position"]), dr["column_name"].ToString(), dr["pri_column_name"].ToString());
ForeignKey tmp;
if (!cList.Add(fk, out tmp))
{
tmp.AddColumns(fk.IndexOrderdColumnNames);
}
}
return(cList.ToList());
}