Correction for Firebird ForeignKeyCoumns schema
-----------------------------------------------
Key: DNET-295
URL: http://tracker.firebirdsql.org/browse/DNET-295
Project: .NET Data provider
Issue Type: Bug
Components: ADO.NET Provider
Affects Versions: 2.5.1
Reporter: Jiri Cincura
Assignee: Jiri Cincura
Priority: Minor
Attachments: DatabaseCreation.sql
Reported to me, privately by Van Den Berghe, Vincent.
===
I have been thinking about a way to illustrate the bug, such that the method
might be included in a future test.
After some thinking, I came up with the function below:
public void CheckForeignKey(FbConnection connection)
{
foreach (DataRow row in connection.GetSchema("ForeignKeys").Rows)
{
var constraintName = (string)row["CONSTRAINT_NAME"];
var tableName = (string)row["TABLE_NAME"];
var referencedTableName = (string)row["REFERENCED_TABLE_NAME"];
#if true
// this fails:
foreach (DataRow pr in
connection.GetSchema("ForeignKeyColumns", new string[] { null, null, tableName,
constraintName }).Rows)
{
Debug.Assert(constraintName ==
(string)pr["CONSTRAINT_NAME"]);
Debug.Assert(tableName == (string)pr["TABLE_NAME"]);
Debug.Assert(referencedTableName ==
(string)pr["REFERENCED_TABLE_NAME"]);
}
#else
// use this instead of
connection.GetSchema("ForeignKeyColumns") until the bug has been corrected
string commandText = @"
SELECT null AS CONSTRAINT_CATALOG,null AS
CONSTRAINT_SCHEMA,co.rdb$constraint_name AS CONSTRAINT_NAME,
null AS TABLE_CATALOG,null AS TABLE_SCHEMA,co.rdb$relation_name AS TABLE_NAME,
coidxseg.rdb$field_name AS COLUMN_NAME,
null as REFERENCED_TABLE_CATALOG,null as
REFERENCED_TABLE_SCHEMA,refco.rdb$relation_name AS REFERENCED_TABLE_NAME,
refidxseg.rdb$field_name AS REFERENCED_COLUMN_NAME,
coidxseg.rdb$field_position AS ORDINAL_POSITION
FROM rdb$ref_constraints ref
INNER JOIN rdb$relation_constraints co ON
ref.rdb$constraint_name=co.rdb$constraint_name
INNER JOIN rdb$index_segments coidxseg ON
co.rdb$index_name=coidxseg.rdb$index_name
INNER JOIN rdb$relation_constraints refco ON
ref.rdb$const_name_uq=refco.rdb$constraint_name
INNER JOIN rdb$index_segments refidxseg ON
refco.rdb$index_name=refidxseg.rdb$index_name AND
coidxseg.rdb$field_position=refidxseg.rdb$field_position
WHERE co.rdb$relation_na...@p0 AND co.rdb$constraint_na...@p1
ORDER BY ref.rdb$constraint_name,coidxseg.rdb$field_position"
;
// AND coidxseg.rdb$field_name = @p3",
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = commandText;
cmd.Parameters.Add(
"@p0", FbDbType.Char, 31).Value = tableName;
cmd.Parameters.Add(
"@p1", FbDbType.Char, 31).Value = constraintName;
//cmd.Parameters.Add("@p3", FbDbType.Char, 31).Value =
fieldName;
using (var reader = cmd.ExecuteReader())
while (reader.Read())
{
Debug.Assert(constraintName == reader.GetString(2
/*"CONSTRAINT_NAME"*/).TrimEnd());
Debug.Assert(tableName == reader.GetString(5
/*"TABLE_NAME"*/).TrimEnd());
Debug.Assert(referencedTableName ==
reader.GetString(9/*"REFERENCED_TABLE_NAME"*/).TrimEnd());
}
}
#endif
}
}
This function will just loop through all foreign key definition, get the
columns and perform some simple checks on the latter (the constraint name,
table name and referenced table name must all match).
Executed as such, the function will fail, because of the bug in
ForeignKeyColumns.
If you change the #if true to #if false, and use the corrected (and compatible)
query, the function will succeed.
You can try it on the database model included in this e-mail
(DatabaseCreation.sql).
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
The Planet: dedicated and managed hosting, cloud storage, colocation
Stay online with enterprise data centers and the best network in the business
Choose flexible plans and management services without long-term contracts
Personal 24x7 support from experience hosting pros just a phone call away.
http://p.sf.net/sfu/theplanet-com
_______________________________________________
Firebird-net-provider mailing list
Firebird-net-provider@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
